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)

T-SQL and SQL Server Administration Articles Link List – November 2010

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of November

T-SQL Articles

Format Phone Numbers in SQL Server - Formatting should be done using your front end application. However if that option does not exist due to some reason, use the following T-SQL code to format Phone Numbers in SQL Server

Count SubTotals and Totals using ROLLUP in SQL Server - The SQL Server ROLLUP operator is useful in generating reports that contain subtotals and totals

Find Column Default Value using T-SQL – SQL Server - Here’s how to find the default value of a Column using T-SQL. We will find the default value of the UnitPrice column in the Products table of the Northwind database

List all Default Values in a SQL Server Database - I had earlier written a query to Find a Column Default value using T-SQL. Here’s how to find the default value of all columns in all tables of a database

Get the Current Stored Procedure Name in SQL Server - I see a lot of developers asking how to get the currently executing Stored Procedure name. This is probably for logging the stored procedures when they are executed.

SQL Server Administration Articles

Search all Stored Procedures where a Table is referenced - A couple of months ago, I wrote a post on how to Search Inside a SQL Server Stored Procedure Definition. A user wrote back asking if it was possible to do the same using an Add-in in SQL Server Management Studio (SSMS).

Filter Bad Words in a SQL Server 2008 Database - If you are using SQL Server 2008, you can use Full-Text Search to filter bad words in a database. The procedure to do so is by creating Stopwords and Stoplists

Drop all Connections to SQL Server Database – Here’s a simple script to drop all active connections to the SQL Server Database. You usually drop connections when you are planning to take the database offline or need to immediately deal with a maintenance issue.

Take a SQL Server Database Offline - This post shows how to take your database offline if there are no current users.

Could not Allocate space for Object because Primary Filegroup Is Full Error - There multiple ways to resolve this error, as suggested above, in the error message itself. Here are some steps that I thought of sharing with my sqlservercurry.com readers

Dynamic Management Views (DMV) for SQL Server Performance and Tuning - In the earlier versions of SQL Server, it was a daunting task to diagnose and resolve performance issues. With DMV’s being introduced in SQL Server 2005 and enhanced in SQL Server 2008, this task has become much easier now.

Change the Default Language for SQL Server - I sometimes get questions like – How do I change the default language of SQL Server 2005/2008 from English to Spanish, so as to display the SQL Server system messages in that language.

Other SQL Server Articles

Next Version of SQL Server ‘Denali’ - If by any chance you missed out on the big announcement made recently during the SQL Pass Summit 2010, then here it is. The SQL Server team announced the next version of SQL Server, code-named ‘Denali’. The current version of SQL Server as of this writing is SQL Server 2008 R2.

Change the Default Language for SQL Server

I sometimes get questions like – How do I change the default language of SQL Server 2005/2008 from English to Spanish, so as to display the SQL Server system messages in that language.

The answer is that if you have installed a Localized version of SQL Server (say in Spanish), then you get the system messages in two languages – US English and Spanish. In this case, you just have to make sure that the Default Language selected is Spanish.

There are 3 common ways to do so: Using SSMS, T-SQL and PowerShell. We will use SSMS and T-SQL in this article.

USING SSMS

To do so, open SQL Server Management Studio (SSMS) > Right click Server in Object Explorer > Properties > Advanced > Check the ‘Default Language’ property and make sure it is set to Spanish.

Select the language from the DropDown and click OK. Then Stop and Start SQL Server for the new language settings to take effect.

USING T-SQL

If you want to change the default language of SQL Server to a language of your choice using T-SQL, follow these steps:

1. Look up the sys.messages catalog view to check if SQL Server supports a message in your local language.

SELECT msg.language_id, lang.langid, alias 
FROM
sys.messages AS msg
JOIN
syslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY  msg.language_id, lang.langid, alias

You get only 12 rows, which means SQL Server supports system messages and user-defined messages in only 12 local languages. (FEEDBACK: SQL Server should support more local languages!! By the way, check the sp_addmessage which offers some respite)

2. Once you know the LanguageID for your local language, for Spanish it is 5, just use this command to change the default language of SQL Server

EXEC sp_configure "default language", 5
RECONFIGURE WITH OVERRIDE

Start and Stop SQL Server. You can now verify if the local language has changed using the following command

sp_configure 'default language'

If the config_value shows 5, then you are good to go!

Alternatively, if you do not have a localized version of SQL Server, then you can use the SELECT @@language command

SET Language Spanish
SELECT @@language, @@langid

Strangely, if you do not explicitly say ‘SET Language Spanish’ and just run the @@language, @@langid command, the message still shows US_English, even if you have followed the steps I had shown above. That’s why i have explicitly used SET Language Spanish option.

Update: Please note that the contents of this post is valid if you have installed a Localized version of SQL Server, in our case Spanish.

If you have not installed the localized version of SQL Server, the default language is US English. If you need to change the default language on this machine, then you will have to change the default language for individual logins, as doing it on a server level won't work. Also as Geri Reshef points out in the comments section, you will have to use SET LANGUAGE to change the language to Spanish.

Get the Current Stored Procedure Name in SQL Server

I see a lot of developers asking how to get the currently executing Stored Procedure name. This is probably for logging the stored procedures when they are executed.

Here’s a very simple way. I have added comments to explain the code

Observe the built-in object_name(@@procid) function which returns the Stored Procedure name

OUTPUT

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

In the earlier versions of SQL Server, it was a daunting task to diagnose and resolve performance issues. With DMV’s being introduced in SQL Server 2005 and enhanced in SQL Server 2008, this task has become much easier now.

Sometime back I had written on Important DMVs to monitor CPU – SQL Server. Here are 6 important DMV’s that in my opinion are very useful to diagnose and tune the performance of your SQL Server Database.

sys.dm_os_performance_counters - Returns a row per performance counter maintained by the server

sys.dm_db_index_usage_stats - Returns counts of different types of index operations and the time each type of operation was last performed

sys.dm_db_index_physical_stats - Returns size and fragmentation information for the data and indexes of the specified table or view

sys.dm_db_index_operational_stats - Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

sys.dm_os_waiting_tasks - Returns information about the wait queue of tasks that are waiting on some resource

sys.dm_exec_query_stats - Returns aggregate performance statistics for cached query plans.

Feel free to share the DMV’s that you think are helpful in tuning a Database performance.

You can also read SQL Azure: Troubleshoot and Optimize Queries using DMV’s – Free Whitepaper

Next Version of SQL Server ‘Denali’

If by any chance you missed out on the big announcement made recently during the SQL Pass Summit 2010, then here it is. The SQL Server team announced the next version of SQL Server, code-named ‘Denali’. The current version of SQL Server as of this writing is SQL Server 2008 R2.

Note: For your information, Denali or Mount McKinley is the highest mountain peak in the United States and is also called 'the great one'. After it's final realease, 'SQL Server code-names Denali' may be called 'SQL Server 2011'.

To go with the announcement, the team even released the first CTP of Denali (wow!). Here are some important links:

What's New in SQL Server "Denali"

SQL Server Code-Named "Denali" CTP1 Release Notes

Download the SQL Server code-named 'Denali' - Community Technology Preview 1 (CTP1)

More Information on Denali

Could not Allocate space for Object because Primary Filegroup Is Full Error – SQL Server

One of my clients mailed me today about a SQL Server error.

---------------------------
SQL Error
---------------------------
Could not allocate space for object 'dbo.TableName' in database 'SomeDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Now there multiple ways to resolve this error, as suggested above, in the error message itself. Here are some steps that I thought of sharing with my sqlservercurry.com readers:

1. Check the disk space to see if there is sufficient space. If not, delete some files to create space.

2. Check if you have restricted the growth of your data and log file. Right click the database in Management Studio > Properties. Go to the ‘Files’ tab and you will see a similar screen

Change it to unrestricted growth for your data and log file. Remember that Autogrowth does not guarantee that your files will not run out of space. Also make sure to use a static increment as opposed to a percentage increment. Here’s a very good article Considerations for the "autogrow" and "autoshrink" settings in SQL Server

3. Lastly you can shrink the database (DBCC SHRINKDATABASE) or shrink the files (DBCC SHRINKFILE). Do not set AutoShrink on Production databases.

Note: I have seen this error occurs on hard disks formatted with the FAT32 filesystem. Remember that FAT32 has a 4GB file size limit. Changing the file system to NTFS sometimes resolves the issue.

Useful SQL Server System Stored Procedures You Should Know

System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Here’s a bunch of System Stored Procedures that are used on a frequent basis (in no particular order):

System Stored Procedure	Description
sp_help	Reports information about a database object, a user-defined data type, or a data type
sp_helpdb	Reports information about a specified database or all databases
sp_helptext	Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure
sp_helpfile	Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server
sp_spaceused	Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database
sp_who	Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session
sp_lock	Reports information about locks. This stored procedure will be removed in a future version of Microsoft SQL Server. Use the sys.dm_tran_locks dynamic management view instead.
sp_configure	Displays or changes global configuration settings for the current server
sp_tables	Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects.
sp_columns	Returns column information for the specified tables or views that can be queried in the current environment
sp_depends	Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported

These were some System Stored Procedures in SQL Server that come in very handy. If you have been using any other system stored procedures (not listed here) on a frequent basis, feel free to share them via the comments section.

List all Default Values in a SQL Server Database

I had earlier written a query to Find a Column Default value using T-SQL. Here’s how to find the default value of all columns in all tables of a database

SELECT obj.name as 'Table', col.name as 'Column',
object_definition(default_object_id) AS [DefaultValue]
FROM   sys.objects obj INNER JOIN sys.columns col
ON obj.object_id = col.object_id
where obj.type = 'U'

The sys.objects and sys.columns provides us with the metadata needed to find the default values of all columns in a database.

OUTPUT

Find Column Default Value using T-SQL – SQL Server

Here’s how to find the default value of a Column using T-SQL. We will find the default value of the UnitPrice column in the Products table of the Northwind database

SELECT object_definition(default_object_id) AS DefaultValue
FROM   sys.columns
WHERE  name  = 'UnitPrice'
AND    object_id = object_id('Products')

OUTPUT

Take a SQL Server Database Offline

This post shows how to take your database offline if there are no current users. Before you read this post, I strongly recommend you read my previous post Drop All Connections to SQL Server Database

Take a Database Offline using SQL Server Management Studio

Using SSMS, you can right click the database > Tasks > Take Offline

Take a Database Offline using T-SQL and wait for existing connections to close

ALTER DATABASE AdventureWorks SET OFFLINE

The command waits for existing connections to close and also does not accept any new connections. Use at discretion!

Take a Database Offline Immediately using T-SQL

ALTER DATABASE AdventureWorks
SET OFFLINE WITH ROLLBACK IMMEDIATE

Bring back the Database Online

ALTER DATABASE AdventureWorks
SET ONLINE

Note: I have seen users still using the sp_dboption to take a database offline. Note that sp_dboption feature will be removed in the next version of Microsoft SQL Server. It’s best to use ALTER DATABASE

Note: You may also want to read View Active connections for each Database in SQL Server

Drop all Connections to SQL Server Database

Here’s a simple script to drop all active connections to the SQL Server Database. You usually drop connections when you are planning to take the database offline or need to immediately deal with a maintenance issue.

If you want to view the active connections on a database, read my post View active connections for each Database in SQL Server

Drop all connections and allow database access to few users

ALTER DATABASE AdventureWorks SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

Note 1: The RESTRICTED_USER option allows the database to be accessed by only members of the db_owner, dbcreator or sysadmin roles

Note 2: The ROLLBACK IMMEDIATE option forcefully disconnects all users without allowing any work in progress to complete. If you do not plan to disconnect the users immediately, but disconnect say after 1 minute, use this option

ALTER DATABASE AdventureWorks
SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS

Drop all connections and allow database access to only one user

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The SINGLE_USER option allows the database to be accessed only by one user, who can be anyone.

Return Access to the Database as it was earlier

ALTER DATABASE AdventureWorks SET MULTI_USER

Filter Bad Words in a SQL Server 2008 Database

If you are using SQL Server 2008, you can use Full-Text Search to filter bad words in a database. The procedure to do so is by creating Stopwords and Stoplists.

Stopwords - Commonly occurring strings that do not help the search and are discarded by SQL Server.

Stoplists - A stoplist is a list of stopwords that, when associated with a full-text index, is applied to full-text queries on that index.

Note: Full-Text Search must be enabled on your SQL Server for this to work (How to Detect If Full-Text Search is Enabled on a SQL Server Database)

You can create Language specific Stopwords. For eg: you can create a Stopword in a Stoplist for Spanish.

-- Create a StopList
CREATE FULLTEXT STOPLIST sampleStoplist;
GO

-- Add a Stopword to the StopList
ALTER FULLTEXT STOPLIST sampleStoplist
ADD 'mui' LANGUAGE 'Spanish';
GO

-- View the StopWord
SELECT * FROM sys.fulltext_stopwords

If you want to view all the default Stopwords present in your database in all Languages, fire this query:

SELECT * FROM sys.fulltext_system_stopwords

OUTPUT (Sample)

Count SubTotals and Totals using ROLLUP in SQL Server

The SQL Server ROLLUP operator is useful in generating reports that contain subtotals and totals.

Here’s how to use the ROLLUP operator in the Northwind database to give a breakdown of the Orders placed by each Customer as well as the Total number of Orders placed

SELECT ISNULL(CustomerId, 'Total') Customer,
Count(OrderId) as OrdersPlaced
FROM Orders
GROUP BY CustomerId
WITH ROLLUP

OUTPUT

Note: If you are using SQL Server 2008, take a look at GROUPING SETS

The same query in SQL Server 2008 can be re-written as

SELECT ISNULL(CustomerId, 'Total') Customer,
Count(OrderId) as OrdersPlaced
FROM Orders
GROUP BY Grouping Sets(CustomerId, ())

Search all Stored Procedures where a Table is referenced – SQL Server

A couple of months ago, I wrote a post on how to Search Inside a SQL Server Stored Procedure Definition. A user wrote back asking if it was possible to do the same using an Add-in in SQL Server Management Studio (SSMS).

The answer is yes and there is a ‘Free’ SSMS add-in provided by RedGate. It is called SQL Search (check the Free download button to the right)

SQL Search finds fragments of SQL text within stored procedures, functions, views and more and once you find them, it quickly allows you to click and jump to the objects, wherever they happen to be on your servers. It’s pretty cool!

Assuming you have downloaded the add-in, let us say we search for all Stored procedures in the Northwind database that use the table ‘Customers’

The search is super quick and the results are returned dynamically as you type. You can also filter the search results by object type, database, or by server.

Note: SQL Search supports SSMS 2005, SSMS 2008, SSMS 2008 Express, R2. This example was infact run on a SQL 2008 Express R2.

Download the Free SQL Search Add-In

Format Phone Numbers in SQL Server

Formatting should be done using your front end application. However if that option does not exist due to some reason, use the following T-SQL code to format Phone Numbers in SQL Server.

Note: The code shown below formats US Phone Numbers. I have covered three scenarios, however feel free to modify the code to add additional scenarios.

The 3 possible scenarios of a US Phone number input could be:

18052224353 will be returned as 1 (805) 222-4353
8052224353 will be returned as (805) 222-4353
2224353 will be returned as 222-4353

SELECT
phno,
CASE LEN(phno)
WHEN 11 THEN LEFT(phno,1)+
    STUFF(STUFF(STUFF(phno,1,1,' ('),6,0,') '),11,0,'-')
WHEN 10 THEN
    STUFF(STUFF(STUFF(phno,1,0,' ('),6,0,') '),11,0,'-')
WHEN 7 THEN
    STUFF(phno,4,0,'-')
ELSE 'Invalid phno'
END as formatted_phno
FROM
(
SELECT
    '18052224353' as phno union all
SELECT
    '8052224353' as phno union all
SELECT
    '888052224353' as phno union all
SELECT
    '2224353' as phno
)
as t 

The SQL Server STUFF function is used to replace certain characters with other characters, based on the position. For example STUFF(phno,1,1,'(') replaces the first character with '('. Similarly STUFF(phno,6,1,') ') inserts ') ' after position 6.

OUTPUT

T-SQL and SQL Server Administration Articles Link List – October 2010

Here’s a quick wrap up of the articles published on this blog in the month of October

T-SQL Posts

Check if Temp Table Exists and Drop it in SQL Server - Check for an existing Temp Table and drop it using T-SQL

Duplicate a Column in SQL Server - A very simple but useful post that shows how to duplicate an existing column in SQL Server.

AutoGenerate Numeric ID’s in SQL Server with No Sequence - Generate a 10 digit number but with no sequence

Display Dates in a Particular Format in SQL Server - Questions of displaying dates in either ‘mm/dd/yy’ format or ‘dd/mm/yyyy’ format and so on have been asked numerous times. Here’s a solution.

Truncate Hour, Minute, Second and MilliSecond in SQL Server - How to Truncate a DateTime in SQL Server.

Find and Delete Duplicate Rows From Tables without Primary Key - How to find and delete duplicate rows from sample tables, which do not have a Primary Key

Analyze and validate T-SQL code using SQL Enlight - How to use SQL Enlight tool to analyze and validate T-SQL Code

Calculate Running Total and Row Total in SQL Server - How to calculate Row Totals and Running Totals in SQL Server

List all System Stored Procedures in SQL Server - How to display all schema-scoped user-defined objects as well as system objects.

Generate Numbers Between two Numbers in SQL Server - Generate numbers between two numbers in the shortest possible way

Execute T-SQL Code Only on Certain Days - Simple tip using IF-ELSE to prevent my T-SQL code from running on Saturday’s

SQL Server Administration

Important DMVs to monitor CPU – SQL Server - Some important Dynamic Management Views (DMV’s) to monitor the CPU where SQL Server is installed

Get Free Disk space using SQL Server - Programmatically determine Free Hard Disk Space using T-SQL.

Miscellaneous

Enter a SQL Server Quiz to win Prizes - Jacob Sebastian of the BeyondRelational fame is hosting a SQL Server Quiz which gives you the chance to win lots of prizes

SQL Server 2008 Service Pack 2 (SP2) Released - News about Microsoft recently releasing SQL Server 2008 Service Pack 2 (SP2)

Execute T-SQL Code Only on Certain Days

I always clean up my databases manually on Sundays. But at times, if need be, I clean it up on other days as well. However I never do it on Saturdays, since the traffic is usually the most on that day.

So here’s a simple tip using IF-ELSE to prevent my T-SQL code from running on Saturday’s, even if I run the code manually

IF DATEPART(dw,GETDATE())<> 7  -- 7 is Saturday, 1 is Sunday
BEGIN
    -- YOUR T-SQL Code Comes Here   
     SELECT GETDATE()
     RETURN
END
ELSE
BEGIN
    PRINT '*** Sorry This Code will Not Execute on Saturdays'
END

OUTPUT

You may also want to read Make a T-SQL Query Sleep for a certain amount of time

Generate Numbers Between two Numbers in SQL Server

I was working on a query where I had generate numbers between two numbers in the shortest possible way. Here’s how to do it. In this example, we will be generating all the numbers between 10 and 18 (inclusive of both) using a quick and dirty way

SELECT DISTINCT number
FROM master..spt_values
WHERE number
BETWEEN 10 AND 18

OUTPUT

As I said, we needed a quick way to generate numbers. If you are looking for Production ready code, check this article here Creating a Number table in T-SQL which offers various ways of generating numbers, including a nice one from Itzik Ben-Gan.

List all System Stored Procedures in SQL Server

In order to list all the System Stored Procedures in SQL Server 2005/2008, you can query the sys.all_objects catalog view, that displays all schema-scoped user-defined objects as well as system objects.

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='P'

OUTPUT (Partial)

Similarly to find CLR Stored Procedures, use type=’PC’

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='PC'

OUTPUT

Note: You can add ‘is_ms_shipped = 1’ to the query if you intend listing only those objects created by an internal SQL Server component.

Enter a SQL Server Quiz to win Prizes

Jacob Sebastian of the BeyondRelational fame is hosting a SQL Server Quiz which gives you the chance to win lots of prizes.

Each SQL Server quiz will be a series of 31 questions, managed by 31 quiz masters who are experts in SQL Server (MVP’s).

Each quiz master will ask one question each and will moderate the discussions and answers and finally will identify the winner of each quiz. This quiz series is scheduled from the 1st to 31st of October with a new question asked every day. Each question will be open for 30 days and at the end of 30 day period, the evaluation team will review your answers/explanation and grade them.

Check out the Questions and Good luck! You can visit the T-SQL Tips page to check if any of the Tips solve a question.