The properties and behaviour of each session to a SQL Server instance can be handled individually via use of various SET options. As we multi-task during the day, we tend to forget the exact SET options that we choose for a particular user session. The focus of the day is how to retrieve this information programmatically, i.e. through T-SQL.

Retrieving SET options for a given session

To quickly review the SET options for a given session, we have two methods:

1. Properties Window
   • The Properties window is available if we are in a SSMS session – this method is not applicable when attempting to fetch the SET options via code
   • I described this method in my previous post, August 01, 2011 (http://beyondrelational.com/blogs/nakul/archive/2011/08/01/ssms-properties-window-know-your-query-options-ansi-nulls-quoted-identifier-arithabort-and-other-set-options.aspx)
2. Using DBCC commands
   • For those would be interested to get these SET options via code, the DBCC comes to our rescue
   • Let’s take a quick look at this method to get the user options

Demo

Using the Properties window to get the user options involves executing a query workload and retrieving the actual execution plan. DBCC, on the other hand allows us to fetch the user options without any workload and without retrieving the execution plan.

For this demonstration, let’s make a small change to the default user options, and run the DBCC statement in the query below.

--Change the connection option
SET ANSI_NULLS OFF

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Now, let’s roll back the change (or launch a new session by creating a new query editor window), and run the following statement.

--Change the connection option
SET ANSI_NULLS ON

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Comparing the outputs:

Comparing the output from the two sessions, we can see that the user options that are turned OFF do not appear on the list.

Reference

Books On line page for DBCC USEROPTIONS – http://msdn.microsoft.com/en-us/library/ms180065.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Performance tuning & analysis during new development has it’s own unique challenge – on one hand as database developers, we need a clean procedure cache and on the other hand, we do not want to impact the performance of other databases on the same instance.

To start from a cold procedure cache, we know that we can use the DBCC FREEPROCCACHE. However, this would completely clear out the procedure cache – impacting others who may be working on the same instance, but on other databases. We can supply the plan/query handle or pool name to reduce the impact of the DBCC FREEPROCCACHE, but because the plan handle changes each time the plan is regenerated, a lot more manual steps become involved in the process.

To restrict the clearing of the procedure cache to the current database only, we can use the DBCC FLUSHPROCINDB command. The DBCC FLUSHPROCINDB command only accepts one parameter – the database ID.

For example, the following command will flush the procedures in cache for the AdventureWorks2008R2 database on my SQL Server instance.

/*
!!!!WARNING!!!!
This script is provided AS-IS and without warranty.
The author, Nakul Vachhrajani; the website, BeyondRelational.com
and Microsoft Corproation are not responsible for any damage caused
by misuse of this script
*/

--Fetch the DB_ID for the required database
USE AdventureWorks2008R2
GO
SELECT DB_ID() AS AdventureWorks2008R2DBID

--Clear out the procedure cache for the AdventureWorks2008R2 database ONLY
DBCC FLUSHPROCINDB (5)

References

• Plan caching in SQL Server 2008 – http://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx

NOTE: To the best of my knowledge, this is an undocumented DBCC command. Please use it with utmost caution. If you find the official Books On Line documentation, please let me know and I will update the post to reflect the same.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

I work with academia frequently, and therefore, get a chance to interact with students and experience the issues they face first hand. I recently had a very interesting experience during one of these visits. I will try to present the experience as a story.

The Problem

One of the students had developed a system which involved some database interaction. As I was reviewing the system, I noticed an issue with the table design. The design that the student had come up with was something like the following:

USE tempdb
GO
--Create the table, with fixed length columns
CREATE TABLE CityMaster (CityId      INT IDENTITY (1,1),
                         CityName    CHAR(50),
                         StateName   CHAR(50),
                         CountryName CHAR(50)
                        )
GO

(The design above is not exactly what he had, but you get the general idea.)

The problem that I had was with his choice of data types. He had chosen a fixed-length data type – CHAR.

I explained that CHAR, being a fixed-length data type, pads a string with trailing blanks when the data is stored to achieve the fixed-length. Variable length data types (e.g. VARCHAR), on the other hand, do not pad trailing blanks, and therefore, have a variable space requirement.

To explain this with an example, the simplest thing for me to do was to insert some test values in his database. Once the test data was ready, I ran the following query, which uses the DATALENGTH() and LEN() functions. For those who came in late,

• DATALENGTH = Returns the number of bytes used to represent an expression
  • This can be used to estimate the storage space that SQL Server has taken up for an expression
• LEN = Returns the number of characters of the specified string expression, excluding trailing blanks

USE tempdb
GO                        
--Insert test data
INSERT INTO CityMaster (CityName, StateName, CountryName)
VALUES ('Ahmedabad','Gujarat',    'India'),
       ('Mumbai'   ,'Maharashtra','India'),
       ('New Delhi','Delhi'      ,'India'),
       ('Bengaluru','Karnataka'  ,'India')
GO

USE tempdb
GO
--View the length and the data length for the data in the table
SELECT CityMaster.CityId,
       CityMaster.CityName,    LEN(CityMaster.CityName)    AS CityNameLength,    DATALENGTH(CityMaster.CityName)  AS CityNameDataLength,
       CityMaster.StateName,   LEN(CityMaster.StateName)   AS StateNameLength,   DATALENGTH(CityMaster.StateName) AS StateNameDataLength,
       CityMaster.CountryName, LEN(CityMaster.CountryName) AS CountryNameLength, DATALENGTH(CityMaster.CountryName) AS CountryNameDataLength
FROM CityMaster
GO

Using the output of the above query, it became easy for me to explain that because CHAR consumes the full 50 characters, the SQL Server consumes a space of 50 + 50 + 50 = 150 bytes irrespective of the amount of data actually being requested for storage.

The solution

As a solution, I recreated the table with the following design:

USE tempdb
GO
--Create the table, with variable length columns
CREATE TABLE CityMaster_v2 (CityId      INT IDENTITY (1,1),
                            CityName    VARCHAR(50),
                            StateName   VARCHAR(50),
                            CountryName VARCHAR(50)
                           )
GO

Again, I inserted the test data and ran the length determination queries.

USE tempdb
GO                          
--Insert test data
INSERT INTO CityMaster_v2 (CityName, StateName, CountryName)
VALUES ('Ahmedabad','Gujarat',    'India'),
       ('Mumbai'   ,'Maharashtra','India'),
       ('New Delhi','Delhi'      ,'India'),
       ('Bengaluru','Karnataka'  ,'India')
GO

USE tempdb
GO
--View the length and the data length for the data in the table
SELECT CityMaster_v2.CityId,
       CityMaster_v2.CityName,    LEN(CityMaster_v2.CityName)    AS CityNameLength,    DATALENGTH(CityMaster_v2.CityName)  AS CityNameDataLength,
       CityMaster_v2.StateName,   LEN(CityMaster_v2.StateName)   AS StateNameLength,   DATALENGTH(CityMaster_v2.StateName) AS StateNameDataLength,
       CityMaster_v2.CountryName, LEN(CityMaster_v2.CountryName) AS CountryNameLength, DATALENGTH(CityMaster_v2.CountryName) AS CountryNameDataLength
FROM CityMaster_v2
GO

The results spoke for themselves, and he quickly grasped the concept that I was trying to put forward, i.e. variable length datatypes have varying storage requirements, making them more efficient from a storage perspective.

ALTER TABLE…ALTER COLUMN will not help in releasing space from fixed-length data-types

Because the concept was clear, he proceeded with converting his existing table designs to use variable-length data types using the ALTER TABLE…ALTER COLUMN statement.

USE tempdb
GO
--Changing CHAR to VARCHAR will NOT release space back once the allocations have been done
ALTER TABLE CityMaster
    ALTER COLUMN CityName VARCHAR(50)

ALTER TABLE CityMaster
    ALTER COLUMN StateName VARCHAR(50)
    
ALTER TABLE CityMaster
    ALTER COLUMN CountryName VARCHAR(50)
GO

However, he found that the ALTER TABLE…ALTER COLUMN statement did not make a difference to the storage requirements.

The reason, I explained, was that once SQL Server has added trailing spaces, it is no longer able to distinguish between an intentionally padded and an automatically padded version of the same string (e.g. ‘BeyondRelational.com ‘ (3 spaces) from ‘BeyondRelational.com’ (no spaces)) and therefore, SQL Server will not be able to release the space allocated to the trailing spaces back.

The only option for him was to drop-and-recreate the tables and then re-insert all his test data.

Now that we were all set with the concept, I pointed him to one of Vinod Kumar’s (blog|twitter) posts: http://blogs.extremeexperts.com/2003/10/10/using-varchars/

Conclusion

There are not one, but 2 very good reasons why I shared this experience with the community.

1. The prime reason being that this post might be helpful to students trying to understand and explore databases, and inculcate in them a habit of choosing the correct data-types
2. I have seen production code with such issues, such as the incorrect choice of data-types. For us in the industry, this is a capital mistake that should not be made, ever

Reference

• CHAR & VARCHAR Datatypes: http://msdn.microsoft.com/en-us/library/ms176089.aspx
• DATALENGTH: http://msdn.microsoft.com/en-us/library/ms173486.aspx
• LEN: http://msdn.microsoft.com/en-us/library/ms190329.aspx
• http://blogs.extremeexperts.com/2003/10/10/using-varchars/

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

Recently, someone in the team faced a fairly common requirement – to generate a comma-separated string from values stored in a table. This being the last post of the year, I thought of sharing the 2 most-commonly used methods I know of implementing this requirement.

Do you know any other? If you share it on this post, I will publish it with due credit on my blog.

----------------------------------------------------
--WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
--         WARRANTY.
--         FOR DEMONSTRATION PURPOSES ONLY       
----------------------------------------------------
--Step 01: Generate Temp table to store source data
DECLARE  @NamesTable TABLE (Id INT,
                            Name NVARCHAR(50))

--Step 02: Generate test data
INSERT INTO @NamesTable VALUES (1,'A'),
                               (2,'D'),
                               (2,'C'),
                               (3,'E'),
                               (3,'H'),
                               (3,'G')

--Option 01: My favourite
DECLARE @listStr VARCHAR(MAX) --DO NOT initialize this one!

SELECT @listStr = COALESCE(@listStr + ',' ,'') + nt.Name
FROM @NamesTable nt

SELECT @listStr

--Option 02: Using XML
; WITH CommaSeparatedXML (CommaSeparatedXML)
AS (SELECT CAST((SELECT (',' + nt.Name)
                 FROM @NamesTable nt
                 FOR XML PATH('')) AS NVARCHAR(MAX))
   )
SELECT SUBSTRING(CommaSeparatedXML, 2, LEN(CommaSeparatedXML))
FROM CommaSeparatedXML
GO

Until we meet next time,

Be courteous. Drive responsibly.