T-SQL and SQL Server Administration Articles Link List – February 2011

Here’s a quick wrap up of the articles published on http://www.sqlservercurry.com/ in the month of February 2011

SQL Server Administration Articles

Fastest Way to Update Rows in a Large Table in SQL Server - Many a times, you come across a requirement to update a large table in SQL Server that has millions of rows (say more than 5 millions) in it. In this article I will demonstrate a fast way to update rows in a large table

View Object Dependencies in SQL Server 2008 - Viewing object dependencies within a database as well as between databases and servers has become easier in SQL Server 2008. SQL Server 2008 introduces a catalog view (sys.sql_expression_dependencies) and dynamic management functions (sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities) that can help in dependency tracking.

SQL Server: Count Rows in Tables and its Size - In this post we will see how to count rows in all the tables of a database using SQL Server

T-SQL Articles

Calculate Median in SQL Server - Median is the numeric value that separates higher half of the list from the lower half. Let us see how to calculate Median in SQL Server

Sort Alphanumeric Data in SQL Server - This post shows how to sort alphanumeric data in SQL Server. You need to use a different approach to get an alphanumeric sort.

SQL Server: Return Multiple Values from a Function - A SQL Server function can return a single value or multiple values. To return multiple values, the return type of the the function should be a table.

SQL Server: Store and Retrieve IP Address - We can store IP addresses in SQL Server in a varchar column. However to retrieve IP address for a specific range, we need to split each part and compare it.

SQL Server: Group By Year, Month and Day - I have seen some confusion in developers as how to do a Group by Year, Month or Day, the right way. Well to start, here’s a thumb rule to follow.

SQL Server: First and Last Sunday of Each Month - This post shows you how to find the First and Last Sunday for each month of a given year, in SQL Server

SQL Server: Storing Images and Other BLOB types - In this post, we will see how to store BLOB (Binary Large Objects) such as files or images in SQL Server.

SQL Server: Highest and Lowest Values in a Row - Calculate both the highest and lowest values in a row without using an UNPIVOT operator.

SQL Server: First Day of Previous Month - A user asked me how to find the first day of the previous and next month in SQL Server

SQL Server: Convert to DateTime from other Datatypes - In this post, we will see how to convert data of different datatypes to a DateTime datatype, in SQL Server.

Feedback:

SQL Server: Convert to DateTime from other Datatypes

In this post, we will see how to convert data of different datatypes to a DateTime datatype, in SQL Server.

Sometimes date values that come from disparate sources may be of a different datatype other than DateTime such as int, varchar etc. In such cases we often need to convert the valuee back to DateTime. We will take two common scenarios of converting Int and Varchar datatype to DateTime

Consider the following examples:

Method 1 : Big Integer to Datetime

Assume that the date value along with time part is stored in Bigint datatype, use this query

Here’s the same query for you to try out:

declare @date bigint
set @date=20101219201119

select
cast(left(date,8)+' '+stuff(stuff(substring(date,9,6),5,0,':'),3,0,':') as datetime) from
(
select cast(@date as varchar(20)) as date
) as t

In the above example, the first 8 numbers denote a date and rest of numbers denote time values in the format HHMMSS. In the above code, left(date,8) extracts the date value. In order to have a proper date format we need to add ‘:’ in each of the time parts (hour, minute and second). The stuff function is used to add ‘:’ in the 3rd and 5th position and the entire string is converted to DateTime.

Method 2 : Varchar to Datetime

Assume that date value along with time part is stored in a Varchar datatype and a space seperates the date value from time values. Use this query:

Here’s the same query for you to try out:

declare @date varchar(20)
set @date='20101219 201119'

select
cast(left(@date,8)+ ' ' +
stuff(stuff(substring(@date,10,6),5,0,':'),3,0,':') as datetime)

OUTPUT

Feedback:

SQL Server: Count Rows in Tables and its Size

In this post we will see how to count rows in all the tables of a database using SQL Server. A couple of months ago I had written a similar query Count Rows in all the Tables of a SQL Server Database using DBCC UPDATEUSAGE and the undocumented stored procedure sp_msForEachTable. However the rows returned using this approach could be inaccurate at times.

I consulted my DBA friend Yogesh who told me about another approach which works accurately and counts both the rows as well as space taken by a table. The code shown here works well for SQL Server 2005 and above.

Here’s the same query for you to try out:

USE ADVENTUREWORKS
GO
-- Count All Rows and Size of Table by SQLServerCurry.com
SELECT
TableName = obj.name,
TotalRows = prt.rows,
[SpaceUsed(KB)] = SUM(alloc.used_pages)*8
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id = idx.object_id
JOIN sys.partitions prt on obj.object_id = prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
WHERE
obj.type = 'U' AND idx.index_id IN (0, 1)
GROUP BY obj.name, prt.rows
ORDER BY TableName

As you can see, we are using the sys.partitions catalog view which contains a row for each partition of all the tables and most types of indexes in the database. We are also using the sys.allocation_units catalog view to calculate the number of total pages actually in use.

Index_id 0 and 1 are for Heap and Clustered indexes respectively. Object Type ‘U’ is for User-defined Tables

OUTPUT (Partial)

Feedback:

SQL Server: First Day of Previous Month

A user asked me how to find the first day of the previous and next month in SQL Server. It’s quite simple as explained in Itzik’s excellent book Inside Microsoft SQL Server 2008: T-SQL Programming (Pro-Developer).

Here’s the query

Here’s the same query to try out:

-- To Get First Day of Previous Month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101')
as [First Day Previous Month];
GO

-- To Get First Day of Next Month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101')
as [First Day Next Month];
GO

To understand what we just did, first execute the following query:

SELECT DATEDIFF(MONTH, '19000101', GETDATE())

This query returns 1333 (as of this writing) which is the number of months since 1/1/1900. Since we want to calculate a day of the previous month, subtract 1. To calculate a day of the next month, add 1. That’s it, now use the DATEADD function to return a specified date with the number interval (1333) subtracted or added to the datepart (month) of 1/1/1900.

OUTPUT

You may also want to check

SQL Server: First and Last Sunday of Each Month

Find First and Last Day of the Current Quarter in SQL Server

Feedback:

SQL Server: Highest and Lowest Values in a Row

Some time back, I had written a query to find Find Maximum Value in each Row – SQL Server where I used UNPIVOT to find the highest value in a row or across multiple columns. A SQLServerCurry.com reader D. Taylor wrote back asking if the same example could be written without using an UNPIVOT operator, to calculate both the highest and lowest values in a row. Well here’s another way to do it.

First create a sample table with some values

Now write the following query to use CROSS APPLY and get the highest and lowest value in a row

SELECT t.id, tt.maxValue, tt.minValue
FROM @t as t
CROSS APPLY
(
SELECT
MAX(col) as maxValue, MIN(col) as minValue
FROM
(
SELECT col1 UNION ALL
SELECT col2 UNION ALL
SELECT col3
) as temp(col)
) as tt

If you are wondering why did I use a CROSS APPLY instead of a simple correlated sub-query, then the reason is that I can work with multiple rows here. Moreover CROSS APPLY can return multiple columns too (like a derived table). At the end, we are referencing these values in our outer SELECT statement and the output is as shown below:

OUTPUT

Feedback:

SQL Server: Storing Images and Other BLOB types

In this post, we will see how to store BLOB (Binary Large Objects) such as files or images in SQL Server. In versions prior to SQL Server 2005, the image datatype was used to store files or images in a table. From SQL Server 2005 and later versions, the image datatype is replaced with varbinary(max) datatype.

To make it easier to demonstrate, I will show how to save a text file in SQL Server.

Create a text file named ‘test.txt’ in your drive with the following data

1,500
2,1000
3,834
4,578
5,290

Create a table variable with varbinary(max) datatype and insert this text file into the table. We have two options

Option 1 : single_blob

Here’s the same query for you to try out:

declare @file table(file_path varchar(150), file_storage varbinary(max))
insert into @file (file_path, file_storage)
select 'f:\test.txt' ,
* from openrowset(bulk n'f:\test.txt', single_blob) as document
select * from @file

The openrowset function with the bulk option will convert the entire file content to binary values. The option SINGLE_BLOB is used to store the file content as a single row and return a single column rowset.

Note: In order to retrieve the file content, FileStream method should be used in your front end application

Option 2 : single_clob

Here’s the same query for you to try out:

declare @file table(file_path varchar(150), file_storage varchar(max))
insert into @file (file_path, file_storage)
select 'f:\test.txt' ,
* from openrowset(bulk 'f:\test.txt', single_clob) as document
select * from @file
GO

Note that the datatype of the column file_storage is varchar. single_clob option is used to store the file content as characters.

Feedback:

SQL Server: First and Last Sunday of Each Month

This post shows you how to find the First and Last Sunday for each month of a given year, in SQL Server. Let us see the t-sql code first

Here’s the same code for you to try out:

declare @year int
set @year =2011

-- First and Last Sunday by SqlServerCurry.com
select min(dates) as first_sunday,max(dates) as last_sunday from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)='sunday'
group by DATEADD(month,datediff(month,0,dates),0)

For this example, we are using the table master..spt_values which is a system table and contains a number column that has numbers from 0 to 2047. We can make this as a number table. Alternatively you can also create a large number table and use it in the query.

Based on the the year value given (in our case 2011), the first day of that year should be calculated. The code DATEADD(year,@year-1900,0) will give us the First day of the year @year.

We need to consider all the date values from January 1st of that year to December 31st.
The where condition number between 1 and DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0)) makes sure that all date values for that year are considered.

We then find whether the date is a Sunday by using DATENAME function. As we need the first and last Sunday for each month, we need to group the result by each month. The code DATEADD(month,datediff(month,0,dates),0) used in group by clause, groups the
result by each month.

OUTPUT

Feedback:

SQL Server: Group By Year, Month and Day

I have seen some confusion in developers as how to do a Group by Year, Month or Day, the right way. Well to start, here’s a thumb rule to follow:

Do not use GROUP BY MONTH or GROUP BY DAY Individually

Developers often write the following statement

The problem with the above statement is that if the table contains data spanning more than one year, a data consolidation occurs which is not what you desire. For eg: July 2007 and July 2008 are both treated as 7 (7th month) and the data is consolidated. The same occurs for all other months too and you get only 12 rows of data.

GROUP BY MONTH or DAY – Right way

The right way to GROUP BY MONTH or DAY is to add YEAR to the GROUP BY clause. This way data will not be consolidated. For eg: now July 2007 and July 2008 are treated separate as a GROUP BY YEAR has been added. Here’s the same query with the GROUP BY YEAR clause

USE Northwind
GO
SELECT COUNT(*) as TotalOrders,
YEAR(OrderDate) as OrderYear,
MONTH(OrderDate) as OrderMonth
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

OUTPUT

As you can see, the query generates 23 rows now with data for each month/year grouped separately

Note: For some reason, if you do not want to GROUP BY YEAR or MONTH separately, you can also use DATEDIFF/DATEADD operations as shown below:

USE Northwind
GO
SELECT COUNT(*) as TotalOrders,
DATEADD(month, DATEDIFF(month, 0, OrderDate),0) as OrderDate
FROM Orders
GROUP BY DATEADD(month, DATEDIFF(month, 0, OrderDate),0)
ORDER BY OrderDate

OUTPUT

The above query generates 23 rows and rounds off to the first day in each month.

Also read my article Count SubTotals and Totals using ROLLUP in SQL Server and Group and Count Records in SQL Server

Reference: Jeff Smith Blog

Feedback:

SQL Server: Store and Retrieve IP Address

We can store IP addresses in SQL Server in a varchar column. However to retrieve IP address for a specific range, we need to split each part and compare it.

Consider the following table:

Suppose we want to retrieve all the IP addresses that fall in the range from 192.168.120.120
to 192.168.200.255. The following query will retrieve these IP addresses:

Here’s the same query for you to try out:

declare @from_ip varchar(20), @to_ip varchar(20)

select @from_ip='192.168.120.120',@to_ip='192.168.200.255'

select * from @t
where
parsename(Ip_address,4)*1>= parsename(@from_ip ,4)*1 and
parsename(Ip_address,4)*1<= parsename(@to_ip ,4)*1
and
parsename(Ip_address,3)*1>= parsename(@from_ip ,3)*1 and
parsename(Ip_address,3)*1<= parsename(@to_ip ,3)*1
and
parsename(Ip_address,2)*1>= parsename(@from_ip ,2)*1 and
parsename(Ip_address,2)*1<= parsename(@to_ip ,2)*1
and
parsename(Ip_address,1)*1>= parsename(@from_ip ,1)*1 and
parsename(Ip_address,1)*1<= parsename(@to_ip ,1)*1

In the query shown above, the Parsename function is used split IP addresses based on a dot (.) . As the IP address is stored in a varchar data type, we need to convert to integer to do calculations. Multiplying it by 1 will convert the varchar number to Integer

OUTPUT

Feedback:

View Object Dependencies in SQL Server 2008

Viewing object dependencies within a database as well as between databases and servers has become easier in SQL Server 2008. SQL Server 2008 introduces a catalog view (sys.sql_expression_dependencies) and dynamic management functions (sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities) that can help in dependency tracking.

In this article, we will see how to use the catalog view sys.sql_expression_dependencies to do object dependency tracking. In a forthcoming article, we will see how to use the dynamic management functions

View Object Dependencies within a Database

Let us assume we want to see the tables and columns referenced in the view Sales.vSalesPerson

Here’s the same query to try out

USE AdventureWorks
GO
SELECT
OBJECT_NAME (referencing_id) as referencing_entity_name,
obj.type_desc AS referencing_desciption,
referenced_schema_name,
referenced_entity_name,
referenced_server_name,
referenced_database_name
FROM sys.sql_expression_dependencies AS sqled
INNER JOIN sys.objects AS obj ON sqled.referencing_id = obj.object_id
WHERE referencing_id = OBJECT_ID(N'Sales.vSalesPerson');
GO

OUTPUT

Shown above are the table and columns that the view vSalesPerson is dependent upon.

View Object Dependencies between Databases (Cross-database)

Also known as Cross-database dependencies, let us see an example to view dependencies when one database references objects from a different database

For this example, create a sample database testdb that references a table from the AdventureWorks database, as shown below:

Now in order to view cross-database dependencies between the procedures and the tables, use the catalog view sys.sql_expression_dependencies as shown below

SELECT
OBJECT_NAME (referencing_id) referencing_entity_name,
referenced_schema_name,
referenced_entity_name,
referenced_server_name,
referenced_database_name
FROM sys.sql_expression_dependencies

OUTPUT

Note 1: sys.sql_expression_dependencies can also be used to track dependencies for Filtered Index Expressions too.

Note 2: In SQL Server 2000, we have the sysdepends table.

Feedback:

SQL Server: Return Multiple Values from a Function

A SQL Server function can return a single value or multiple values. To return multiple values, the return type of the the function should be a table.

Let’s see an example where you want to pass a number to a SQL Server function and get consecutive date values

Create the following function

Here’s the same query to try out:

CREATE function dbo.test_function
(
@num int
)
returns table as
return
(
select number, dateadd(day,number-1,getdate()) as dates from master..spt_values
where type='p' and number between 1 and @num
)

Now call this function using the following statement:

select * from dbo.test_function(10)

Running the query will list out 10 consecutive dates starting from today, as shown below:

As you can see, the return type of the function test_function is a table. The table master..spt_values used in the function is a system table and the number column has numbers from 0 to 2047. We can make use of this system table as a number table, as we have done above. Alternatively you can also create a number table with large number of values and use it in the query.

Feedback:

Fastest Way to Update Rows in a Large Table in SQL Server

Many a times, you come across a requirement to update a large table in SQL Server that has millions of rows (say more than 5 millions) in it. In this article I will demonstrate a fast way to update rows in a large table

Consider a table called test which has more than 5 millions rows. Suppose you want to update a column with the value 0, if it that column contains negative value. Let us also assume that there are over 2 million row in that column that has a negative value.

The usual way to write the update method is as shown below:

UPDATE test
SET col=0
WHERE col<0

The issue with this query is that it will take a lot of time as it affects 2 million rows and also locks the table during the update.

You can improve the performance of an update operation by updating the table in smaller groups. Consider the following code:

The above code updates 10000 rows at a time and the loop continues till @@rowcount has a value greater than zero. This ensures that the table is not locked.

Best practices while updating large tables in SQL Server

1. Always use a WHERE clause to limit the data that is to be updated

2. If the table has too many indices, it is better to disable them during update and enable it again after update

3. Instead of updating the table in single shot, break it into groups as shown in the above example.

You may also want to read my article Find the Most Time Consuming Code in your SQL Server Database

Feedback:

Sort Alphanumeric Data in SQL Server

This post shows how to sort alphanumeric data in SQL Server. You need to use a different approach to get an alphanumeric sort.

Consider the following code

Suppose you want to sort the string based on the alphabets first, followed by a number and if the string does not have any numeric value, then that string should come first in the sort

The following query will sort as explained above

Here’s the same query to try at your end

select data from @t
order by case
when data like '%[0-9]%' then 1
else 0
end,
data

The logic is to see if there are numbers in the string. If the data has numbers, it will be assigned 1 otherwise 0. So the records with 0 will be sorted first, then followed by 1. At the end, the final result will be sorted by data, as shown below. Observe that the string that do not have a number in it appears first, while sorting.

Feedback:

Calculate Median in SQL Server

Median is the numeric value that separates higher half of the list from the lower half. If the list contains odd number of values, median is the number available at the position
(n+1)/2 when numbers are arranged in ascending order. Otherwise it is the average of the numbers at position (n+1)/2 and (n+2)/2. Let us see both these scenarios with examples

Scenario 1: Odd number of values

Create a testing table variable with sample data

declare @table table(number int)
insert into @table
select 3 union all
select 19 union all
select 6 union all
select 1 union all
select 48

--Find number of items in the list
declare @n int
select @n=COUNT(*) from @table

if @n%2=1
select number as median from
(
select ROW_NUMBER() over (order by number) as sno,* from @table
) as t
where sno=(@n+1)/2
else
select avg(number*1.0) as median from
(
select ROW_NUMBER() over (order by number) as sno,* from @table
) as t
where sno in ((@n+1)/2,(@n+2)/2)

OUTPUT

Scenario 2: Even Number of values

OUTPUT

Row number function is used to assign a serial number based on ascending order of the list. The condition if @n%2=1 checks if the total number of list is odd number and (@n+1)/2 will be the number which indicates the position of the median number and the query takes that number.

If @n%2<>1, the query takes the average of the numbers in the positions (@n+1)/2,(@n+2)/2

Feedback: