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