Most Popular SQL Server Posts in 2010 - SQLServerCurry.com

With 2011 fast approaching and 2010 drawing to an end, I've put together a list of the Most Popular posts on SQLServerCurry.com this year. Along with me, I had SQL Server MVP Madhivanan joining the author panel and sharing his knowledge via posts and answering your comments. Many thanks to you Madhivanan for the useful articles.

A big thank you to everybody who has contributed in one or more of the many ways: by writing a post, by commenting, by sharing the articles on social media, by Subscribing to the RSS feed or by adding me on Twitter.

So here is – a list of 2010's Most Popular Posts on SQLServerCurry.com.

January

Quickly determine the DataType and Nullability of a Column in a query in SQL Server Management Studio 2008

Moving the Tempdb Database to another location

Restore SQL Server 2008 backup in SQL Server 2005

Using SQLCMD to export SQL Server Data as Comma Separated Values

February

When was my SQL Server Installed?

Mapping SQL Server 2000 System Tables to SQL Server 2005/2008 System Views

Identify Tables that have Clustered index but no NonClustered indexes

March

Update a SQL Server Column with Random Numbers within a Range

Temporary Tables VS Table Variables

Profiler for SQL Server 2005/2008 Express Edition

Some useful Addins for SQL Server Management Studio 2005 and 2008

April

Find the Nth Maximum and Minimum Value in a Column

Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls

List of Undocumented Stored Procedures in SQL Server

May

SQL Query to find out who attended office on Saturday

Create Date Ranges for Consecutive dates in a SQL Server Table

Taking BackUp of SQL Server Database on a USB Drive

Group and Count Records in SQL Server

June

How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable

Calculate Average Time Interval in SQL Server

Find the Closest Number using SQL Server

July

GROUPBY, HAVING and ORDER BY Usage in SQL Server

Calculate Age from Date Of Birth using SQL Server

NVARCHAR(MAX) VS NTEXT in SQL Server

Find the Most Time Consuming Code in your SQL Server Database

August

Different ways to get Identity of New Inserted Rows in SQL Server

Working with Different Time Zones in SQL Server 2008

When to use STUFF instead of REPLACE – SQL Server

September

Display DML Triggers in your SQL Server Database

Make a T-SQL Query Sleep for a certain amount of time

Email Alerts when something goes wrong with SQL Server

Find Unused Objects in your SQL Server Database

Save SQL Snippets in SQL Server Management Studio (SSMS)

Check if Database Exists In SQL Server – Different ways

Read Environment Variables in T-SQL

October

Check if Temp Table Exists and Drop it in SQL Server

Find and Delete Duplicate Rows From Tables without Primary Key

Calculate Running Total and Row Total in SQL Server

Execute T-SQL Code Only on Certain Days

November

Format Phone Numbers in SQL Server

Count SubTotals and Totals using ROLLUP in SQL Server

Useful SQL Server System Stored Procedures You Should Know

Dynamic Management Views (DMV) for SQL Server Performance and Tuning

Change the Default Language for SQL Server

December

List Dynamic Management Views (DMV) by Category in SQL Server 2008

Select Random Rows from a Table – SQL Server

Does SQL Server support Regular Expressions

Filtered Index in SQL Server 2008

Insert Rows in Temporary Table from Stored Procedure – SQL Server

Which is your best post?

So which was the most useful SQLServerCurry.com post this year. Also let us know the new SQL Server topics or areas you would like us to write in 2011. Your feedback will help us plan better articles for the year 2011.

So here’s wishing you all a very Happy New Year. Have a great time ahead :)

Filtered Index Vs Indexed Views in SQL Server 2008

A couple of days ago, I had written an article on Filtered Index in SQL Server 2008. If you ever desired to create an index only on some rows, make sure you read about filtered indexes.

A SqlServerCurry.com reader ‘Shogunpoma’ sometime back had written to me asking if Filtered indexes were a better option than Indexed Views. I have listed some differences between Filtered Index and Indexed View that will help understand the differences between the two:

Filtered Index	Indexed Views
Created on one or more columns of one table	You can index across a view containing multiple tables
Can be created and used in any edition of SQL Server	Can be created in all editions of SQL Server 2008, but only be used in the Developer, Enterprise and DataCenter Editions. For other editions, you have to use ‘NOEXPAND’ option to explicitly tell SQL Server to use the indexed view
You can create Non-unique Filtered Indexes	You can create only Unique Indexed Views
Reduced index maintenance costs. The query processor uses fewer CPU resources to update a filtered index	Since Indexed Views are more complex, the index can be larger and will consume more CPU resources while updating an Indexed View.
You can only use simple operators (IS IS NOT = <> != > >= !> < <= !<)) in the WHERE clause while creating Filtered Index. Clauses such as ‘LIKE’ cannot be used	No such limitation
You can do online index rebuilds for filtered indexes	Online index rebuilds are not supported for indexed views
Better query compilation of Filtered Index over Indexed View	The query compilation of Indexed Views is not as good as compared to Filtered Index. Query optimizer uses a filtered index in many situations than the equivalent indexed view.

For the reasons listed above, I would recommend using a filtered index instead of an indexed view, depending on your requirement and whenever possible.

References: Filtered Index Design Guidelines

First Sunday of Every Month in SQL Server

I have often seen questions about finding the First Sunday or Monday of every month in SQL Server. Here’s a query that lists the first Sunday of every month in a given duration

Note: You can also check List all the Weekends of the Current Year using SQL Server and First weekday of a month in SQL Server

DECLARE @dow int,
@StartDate DATETIME,
@EndDate DATETIME
SELECT @dow = 1, -- Sunday
@StartDate = '01 Jan 2010',
@EndDate = '31 Dec 2011'

-- Populate CTE with the 1st date of every month
;WITH CTE(mth) AS (
SELECT @StartDate mth UNION ALL
SELECT DATEADD(month,1,mth) FROM CTE
WHERE DATEADD(month,1,mth) <= @EndDate
)

-- SQLServerCurry.com Query to List First Sunday of all Months
SELECT DATEADD(DAY, @dow +
CASE WHEN DATEPART(dw,mth) > @dow THEN 7
ELSE 0
END
- DATEPART(dw, mth), mth) as 'First Sunday'
FROM CTE

OUTPUT

Update: Here's a better query SQL Server: First and Last Sunday of Each Month

Insert Rows in Temporary Table from Stored Procedure – SQL Server

We can populate Temporary tables from Stored procedures using two methods.

Let us first create a Sample table and Stored Procedure as shown below:

Method 1: Create Temp Table and Populate it using Stored Procedure

CREATE TABLE #tests(
id int,
names varchar(100)
)
GO

INSERT INTO #tests
EXEC proc_tests
GO

SELECT * FROM #tests
GO
DROP TABLE #tests

OUTPUT

Method 2: Use OPENROWSET function

SELECT * INTO #tests
FROM
OPENROWSET('SQLOLEDB','Data Source=server_name;Trusted_Connection=yes;
Integrated Security=SSPI','Execute test..proc_tests')
SELECT * FROM #tests

Read more about the OPENROWSET function here

Note: By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET. So when you execute the query, you may get the message

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

If ad hoc distributed queries are not enabled on your machine, you can enable it using the following query:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Then re-run the query shown in method 2.

Filtered Index in SQL Server 2008

Filtered indexes are a new feature of SQL Server 2008. They are optimized non-clustered indexes that can be created on a subset of rows.

When can Filtered Index be used?

Let us say you have a large table that contains order details, however the most frequently run query on that table is for all orders belonging to the year 2010. In this case, you can create a filtered index that includes only dates of the year 2010.

Another scenario is if you have a table that contains order details and want to index only those rows whose Shipping Status is ‘Pending’ or if a table contains a large amount of null values but you want to query only the non-null values in that table. In such a case, creating a filtered index that fetches non-null values will improve query performance.

Understanding your tables and data is a key requirement to building efficient filtered indexes. Use the SQL Server Profiler to help you determine which columns or category or type of data is queried the most, and then take a decision accordingly.

Note: Filtered Indexes cannot be created on Views.

Advantages of Filtered Index

Here are some advantages of using Filtered Indexes

• Allows you to create an index on a subset of data
• Improves data storage by using less disk space for storing the index
• Reduces time taken to rebuild index
• Reduces time taken to look for data and thus increases the query performance and execution plan quality
• Reduces index maintenance overhead

• Filtered indexes lead to filtered statistics which cover only the rows in the
  filtered index. Thus they are more efficient than full table statistics.

Creating a Filtered Index

Filtered Index can be created by adding the WHERE clause to the normal CREATE INDEX statement. Here’s how to create a Filtered Index on the Northwind Orders table for orders placed since 1/1/1998

Once the filtered index is created, you can fire queries and look at the execution plan to see if the filtered index is used. Note that even when a query qualifies the filtered index criteria, SQL Server may not choose to use the filtered index if the I/O cost of using a Filtered index, is more than the I/O cost of performing a Clustered index scan.

In order to modify a filtered index expression, just use the CREATE INDEX WITH DROP_EXISTING query.

Note: Also make sure to check my article Filtered Index Vs Indexed Views in SQL Server 2008

SQL Server 2005 SP4 (Service Pack 4) RTM Available

Microsoft recently released the SQL Server 2005 Service Pack 4 (SP4) RTM - Release to Manufacturing. Download Service Pack 4 for Microsoft SQL Server 2005.

You can also check the list of the bugs that are fixed in SQL Server 2005 Service Pack 4.

Along with the release, updates were also released for SQL Server 2005 Express Editions and Data Mining Add-ins for Office 2007

Download SQL Server 2005 Express Edition SP4

Download SQL Server 2005 SP4 Data Mining Add-ins

Download SQL Server Management Studio Express SP4

Download Feature Pack for SQL Server 2005 SP4

Calculate Percentage in SQL Server

The following T-SQL script shows a simple example to calculate Percentage in SQL Server. We will be using a table which contains Grades and calculate the percentage weight of each grade, for the entire classroom

SAMPLE DATA

CREATE TABLE #ClassRoom(ID INT IDENTITY(1,1), Grade char(2) NULL);
GO
-- Code by SqlServerCurry.com
INSERT INTO #ClassRoom Values ('A');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('B+');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('A');
INSERT INTO #ClassRoom Values ('A+');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('A+');
INSERT INTO #ClassRoom Values ('C');

QUERY

SELECT Grade,
CONVERT(varchar, 100 * count(*) / tot,1) + '%' as 'Percent'
FROM #ClassRoom,
(SELECT COUNT(*) as tot FROM #ClassRoom) x
GROUP BY Grade, tot

The CONVERT function formats the percentage figure so that you get the result in a percentage format.

OUTPUT

Remove Newline Tab Character in Data using SQL Server

While working on an application, I came across a small column with some Newline and Tab characters in it. The requirement was to replace these characters. Here’s how to remove/replace the Newline characters in your data. I will demonstrate this example with a string, but you can easily replace it with a column name

DECLARE @str as nvarchar(70)
SET @str = 'APPT 5212
NORTH BLOCK, 7th Av
MUMBAI,MAH'

SELECT @str AS 'Orginal String'

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) + CHAR(10); -- CarriageReturn + LineFeed

SELECT
REPLACE(REPLACE(SUBSTRING(@str,1,DATALENGTH(@str)),@CrLf,'-'), CHAR(9), '')
AS 'String with No Newlines or TABS'

The CHAR() string function converts an ascii code to character and can be used to insert control characters into strings. Over here we are using three types of control character

CHAR(9) - Tab

CHAR(10) – LineFeed

Char(13) - CarriageReturn

A newline or line break is CHAR(13) + CHAR(10). In the code shown above, we look for all Newline and Tab characters and replace it with a ‘-‘ and ‘’ respectively.

OUTPUT

Get Password Expiration Date in SQL Server 2008

SQL Server 2008 introduces a new argument called DaysUntilExpiration in the LOGINPROPERTY function, which returns the number of days until the password expires. You can use this property using the following code:

SELECT LOGINPROPERTY('sa', 'DaysUntilExpiration')

Note: If you get a null while executing the query, then either the login is not a valid login or your Operating System does not support password policies. You should run this query on a SQL Server box installed on Windows Server 2003 or later.

Similarly you can get additional login information using the following query:

SELECT name,
create_date,
modify_date,
LOGINPROPERTY(name, 'DaysUntilExpiration') DaysUntilExpiration,
LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime,
LOGINPROPERTY(name, 'IsExpired') IsExpired,
LOGINPROPERTY(name, 'IsMustChange') IsMustChange
From sys.sql_logins ;

SQL Server 2008 R2 System Views Poster

Microsoft recently released a very useful poster containing the System Views Map which shows the key system views included in SQL Server 2008 and 2008 R2, and the relationships between them.

SQL Server System views exposes database metadata and are divided into five collections - Catalog Views, Information Schema Views, Compatibility Views, Replication Views and Dynamic Management Views and Functions

Download the free SQL Server 2008 R2 System Views Poster

Does SQL Server support Regular Expressions

I saw an interesting discussion between devs, the other day on twitter. The topic was ‘Are Regular Expressions supported in SQL Server?’

The answer is yes and no. SQL Server does not support Regular expressions directly. So when you are using T-SQL, the support for Regular Expression ‘Syntax’ in SQL Server is very limited. For example, you can do some pattern matching using some expression operators, with the LIKE or PATINDEX operators. Here are some examples:

Character	Description	Usage
%	Matches a string of zero or more characters. Eg: Match the word 'Curry' anywhere in the Name column	SELECT … FROM TABLE WHERE Name LIKE '%Curry%'
Underscore (_)	Matches a single characters (newslines included) Eg: Match all five letter names that ends with ‘urry’	SELECT … FROM TABLE WHERE Name LIKE '_urry'
[ …]	Matches any character within a specific set Eg: Match names which end with ‘urry’ and start with any single character between A and D – like Aurry, Burry, Curry	SELECT … FROM TABLE WHERE Name LIKE '[A-D]urry'
[^…]	Matches any character not within a specific set. Eg: Match names which end with ‘Curr’ and following letter is not between M and T.	SELECT … FROM TABLE WHERE Name LIKE 'Curr[^M-T]'

Since Regular expressions were not supported directly in SQL Server, developers working on SQL Server 2000 have used less efficient techniques like using a UDF with VBScript.RegExp library or use extended stored procedures.

However with the SQL Server 2005/2008 support for hosting the common language runtime (CLR), it is now possible to write CLR regular expressions. There is an excellent article that shows how to do it and I strongly recommend you to read it

Regular Expressions Make Pattern Matching And Data Extraction Easier

Select Random Rows from a Table – SQL Server

There are multiple ways to randomly select rows from a table. In this blog post, I will show two ways of doing so:

Method 1: Random Number of Rows

DECLARE @n int
SET @n=RAND()*10
SELECT TOP (@n) * FROM sysobjects

Method 2: Random Number of Rows as well as Data

DECLARE @n int
SET @n=RAND()*10
SELECT TOP (@n) * FROM sysobjects
ORDER BY NEWID()

The second method is easy to use and fetches data more randomly.

The media family on device '%ls' is incorrectly formed – Troubleshooting SQL Server Backup Restore error

A www.sqlservercurry.com reader mailed me about an error he encountered while restoring a database from a .bak (backup) file using SSMS. The error said ‘Microsoft SQL Server, Error: 3241 - The media family on device '%ls' is incorrectly formed.SQL Server cannot process this media family’.

Here are some steps to resolve the error:

1. Before doing a backup, run the command SELECT @@version on both the source and destination servers. This is to make sure that the server where you are restoring the backup (destination server), has a higher version than the source server.

2. One of the primary reasons of this error is the backup going corrupt. You can use the RESTORE HEADERONLY statement to read the backup header information and check for errors. If the backup is corrupted, create a back up again and try.

3. If you are transferring the backup from one location to the other using FTP, transfer the file in binary mode. [original source]

Hopefully following the three steps shown above should resolve the error!

Count Unique Values Per Column using SQL Server

I was working on a query where unique values in columns had to be counted in SQL Server 2005/2008. Here’s how to do it if you have a similar requirement

Sample Data

CREATE TABLE #TT (ID int, Name varchar(20), AreaCode int)

INSERT INTO #TT (ID, Name, AreaCode)
SELECT 1, 'Mahesh', 2354 UNION ALL
SELECT 2, 'Annie', 2387 UNION ALL
SELECT 3, 'Mahesh', 2354 UNION ALL
SELECT 4, 'Timothy', 2752 UNION ALL
SELECT 5, 'Annie', 2387 UNION ALL
SELECT 6, 'Barrymore', 2354 UNION ALL
SELECT 7, 'Raunak', 2976 UNION ALL
SELECT 8, 'Suprotim', 2998

Count Distinct Values in One Column

SELECT COUNT(DISTINCT AreaCode) as DistinctValues
FROM #TT

OUTPUT

Count Distinct Values in Multiple Columns

-- Count Distinct Values in Multiple Columns
;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER(PARTITION by Name order by Name) AS ColName,
ROW_NUMBER() OVER(PARTITION by AreaCode order by AreaCode) AS ColArea
FROM #TT
)

SELECT
SUM(CASE WHEN ColName = 1 THEN 1 ELSE 0 END) AS DistinctNames,
SUM(CASE WHEN ColArea = 1 THEN 1 ELSE 0 END) AS DistinctAreaCodes
FROM CTE

OUTPUT

List Dynamic Management Views (DMV) by Category in SQL Server 2008

Dynamic management views and functions have been organized into the different categories which can be viewed over here.

If you want to list the DMV and DMF by category, then here’s a T-SQL query that will help you do so:

SELECT
name as [DMV/DMF Name],
type_desc as [Type],
[DMV Category] =
CASE
WHEN name LIKE 'dm_audit%' THEN 'Auditing Related DMV'
WHEN name LIKE 'dm_cdc%' THEN 'Change Data Capture Related DMV'
WHEN name LIKE 'dm_clr%' THEN 'CLR Related DMV'
WHEN name LIKE 'dm_db%' THEN 'Database&Objects Related DMV and DMF'
WHEN name LIKE 'dm_exec%' THEN 'Execution Related DMV and DMF'
WHEN name LIKE 'dm_xe%' THEN 'Extended Events Related DMV'
WHEN name LIKE 'dm_fts%' THEN 'Full-Text Search Related DMV and DMF'
WHEN name LIKE 'dm_filestream%' THEN 'FileStream Related DMV'
WHEN name LIKE 'dm_io%' THEN 'I/O Related DMV and DMF'
WHEN name LIKE 'dm_sql%' THEN 'Object Ref Related DMV and DMF'
WHEN name LIKE 'dm_provider%' THEN 'Provider Related DMV and DMF'
WHEN name LIKE 'dm_qn%' THEN 'Query Notifications Related DMV'
WHEN name LIKE 'dm_repl%' THEN 'Replication Related DMV'
WHEN name LIKE 'dm_resource%' THEN 'Resource Governor Related DMV'
WHEN name LIKE 'dm_broker%' THEN 'Service Broker Related DMV'
WHEN name LIKE 'dm_os%' THEN 'SQL Server OS Related DMV'
WHEN name LIKE 'dm_server%' THEN 'Server Audit Related DMV and DMF'
WHEN name LIKE 'dm_tran%' THEN 'Transactions Related DMV'
WHEN name LIKE 'dm_cryp%' THEN 'Security Related DMV and DMF'
WHEN name LIKE 'dm_cdc%' THEN 'Change Data Capture Related DMV'
WHEN name LIKE 'dm_database%' THEN 'Transparent Data Encryption Related DMV'
ELSE 'Other DMV'
END
FROM sys.system_objects
WHERE name LIKE 'dm[_]%'
ORDER BY [DMV Category]

As you can see in the query, all we are doing is using a CASE statement and a LIKE operator on the ‘name’ column in the sys.system_objects catalog view and listing the DMV’s and DMF’s in categories.

OUTPUT (Partial)