foxtricks

Multi-monitor desktop layout

Fri, 10 Jun 2011 06:54:39 -0700

Back in 1954, Paul Fitts came up with a model of human movement which is better known as Fitts's Law.

The time required to rapidly move to a target area is a function of the distance to and the size of the target.

The edges of a computer monitor give an interesting twist to Fitts's Law in the sense that the four corners of the screen create 4 buttons of unlimited size (Just throw your mouse up there, no matter how far you move it, you will quickly have a lock on that one specific pixel).

Microsoft implemented this in Windows in for example the Close button and the Start button.

Equally, the top, bottom of the screen have unlimited height and the sides of the screen have unlimited width.

This all worked great until the invention of the multi-monitor setups. The default layout in a dual monitor setup is the "Side-by-side" layout.

This side-by-side layout is not very good as it removes all the "unlimited width" controls from one side of your main monitor and one side of your secondary monitor. In my case, I moved my secondary monitor on the left of the main monitor. If I have to hit controls which are on the left of the gui, there is no longer the "unlimited width" provided by the screen edge. It is no longer possible to quickly move my mouse in the bottom left corner and hit the start menu, clicking the controls on the left hand side of the main monitor and on the right hand side of the secondary monitor requires precision clicks.

Staggered Screen layout

My improvement on the "default layout" of side-by-side monitors is arranging them in a staggered fashion. The effect of this arrangement is that transition to the alternative monitor only happens via one pixel. In the beginning it requires a bit of getting used to, but after a few days it feels like second nature. The result of this setup is the loss of only 1 endless pixel on the main monitor and 1 pixel on the secondary monitor (instead of 1200 pixels on each screens). The best staggered arrangement is by putting the secondary screen on the top left or bottom right of your main monitor. This retains the easy access to the close button and the start menu.

Setting this layout on Windows 7 is done by right clicking on the desktop and selecting "Screen Resolution". On that screen you can drag the screen pictogram with the 1 and drag it above the screen with nr 2. Click OK and try it out.

I understand why this is not the default layout as the way to transition to the second screen does not feel very natural at first, but Windows should at least give a hint so people can discover that it's possible.

Permalink | Leave a comment »

Debugging TSQL Code on SQL Server 2008

Sun, 05 Jun 2011 13:03:00 -0700

SQL Server 2008 once again comes with a TSQL Debugger (which is really quite good). The only condition for using it is that the code is available in a non-encrypted form on the database. To assure this, you have 2 options:

Deploy your scripts without the "WITH ENCRYPTION" statement.
Use a decryption tool to decrypt the objects on your database.

You can check if your code is decrypted by typing sp_helptext "p_myproc" where the code of your procedure should appear in the results window.

Setting up your PC for Debugging

The first time you start debugging a TSQL statement, Management studio pops up a request to alter your firewall settings to allow debugging. Unfortunately this did not work at all in my case. So I had to dive in and make the required changes.

A number of Firewall settings have to be done to enable TSQL Debugging on your pc.

Start the Firewall tool:

In the firewall application a number of changes need to be made:

Exclude TCP port 135
Exclude sqlservr.exe
To future proof for IpSec add UDP Ports 4500 and 500
Exclude ssms.exe
Exclude msvsmon.exe

Exclude TCP Port 135

Right click on the Inbound rules node and select "New Rule.."

Then select "Port" and click Next

Now enter the TCP Port 135 as excluded and click Next

In the next dialog you make sure "Allow the Connection" is selected and click Next

On next pane you specify which networks can access this port. For security reasons it is best to disable "Public" networks. Select the options as shown below and click next.

In the Naming dialog enter something like "TSQL Remote Debugging: Allow DCOM" and click finish:

Exclude sqlservr.exe

Again right click on the "Inbound rules" node and select New Rule. This time we take the option "Program" and click next.

In the next dialog we will select the path where sqlservr.exe is installed (This depends on the name of your SQL Server instance. You can search for sqlservr.exe in the "C:\Program Files" folder). Click next when Done.

Again select the "Allow the connection" option and click Next. In the next Dialog once again disable "Public" networks. Click next and enter as name "TSQL Remote Debugging: sqlservr.exe"

Exclude UDP Ports 4500 and 500

Follow the same process as for "Exclude TCP Port 135" but enter the ports "500, 4500" in the "Protocols and ports" dialog

Proceed the same as in "Exclude TCP Port 135" and enter "TSQL Remote Debugging: IpSec" as name

Exclude ssms.exe (SQL Server Management Studio)

The next application to exclude is management studio. Follow the same approach as "Exclude sqlservr.exe" but search for ssms.exe (C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe) . Proceed identical as "Exclude sqlservr.exe" but enter "TSQL Remote Debugging: ssms.exe" as name

Exclude msvsmon.exe

This one is a bit special because it exists in a 32-bit and 64-bit flavor. For convenience we will exclude them both.

Proceed the same as for sqlservr.exe and ssms.exe and exclude the following 2 applications

Path:C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Remote Debugger\x86\msvsmon.exe
Name:TSQL Remote Debugging: msvsmon.exe x86

And the second rule

Path:C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Remote Debugger\x64\msvsmon.exe
Name:TSQL Remote Debugging: msvsmon.exe x64

This concludes the firewall configuration required to perform TSQL Debugging.

Using the TSQL Debugger

Open management studio and enter the SQL Statement you wish to debug and click on the "Start Debugging" option under the "Debug" menu. (or press Alt-F5)

This will start your Query and stop processing on the first statement it encounters. This you can see from the yellow arrow in the left margin.

To continue we have a number of new options in the debug menu

To stop debugging (like you would press the "stop query" button) you press Shift-F5
To continue running until the next "Breakpoint" you press Alt-F5
To “Step into” the procedure or function press F11. This will open a new window with the code of the procedure you stepped into and put the yellow arrow at the first executable line. Here you can continue line by line by pressing F11.
If you have a piece of code you do not want to step into, press F10 to execute the code and stop at the next line.
Step Out means you run the rest of the procedure to the end and go one level up to the calling procedure.

When you meet a piece of Dynamic SQL to be executed the debugger will open a new window where you can step through the generated SQL Code (which is super nice)

To set an extra breakpoint (ie: to press on with Alt-F5 to a certain line of code you click in the margin of the code window or press F9 which will display a red dot. If you then hit Alt-F5 the execution will stop at this red dot as shown with the yellow arrow:

Another convenient way to "Catch up" to the current line is by right clicking and selecting the "Run to cursor" option. This will advance the debugger to your current line.

Watch windows

To help you debug your code there are a number of help windows available.

The first one is the "Locals" window, this window displays the content of the parameters and variables currently in the scope of the code.

The next dialog is the Quick Watch window. If you have a "complex" expression for which you want to see the results of an extract you can do this with a quick watch. Select the variables to evaluate and right click. Select the quick watch option which will open the following window where you can enter variables, parameters, and the system functions that have names that start with @@.

Viewing the content of Temp tables

The TSQL debugger does not provide an "out of the box" way to view the content of "local" temp tables. For this I built a stored procedure which you can run from any thread. The code is available on https://github.com/FilipDeVos/sp_select.

Notes:

Something you should not forget is that the output of your "Original" query is still being printed to the results window of the window where you started debugging. An easy layout to work with the debugger is by making a "Vertical Split" which enables you to see the output together with the debugged statements

Permalink | Leave a comment »

One-on-One Meetings

Wed, 25 May 2011 04:00:00 -0700

All kinds of people work in a company, you have Frank who likes to continuously curse at his computer and there is Tom who quietly works all day. We all know somebody like Andy, who will stand up and defend his opinion tooth and nail. And there is Sophie, who is so shy that she never dares voice concern. Finally, are all the people with a character falling in between the extremes.

The good thing is, no matter what kind of people you have in your team, an easy way to build a trust relationship while offering a forum to express thoughts and worries is organizing regular One-on-One meetings.

One on One - One2One - 1to1

Many team leads have these meetings informal, over a beer or at the coffee machine, but in my experience, the best way to ensure they happen consistently and to show that they are taken seriously is to have a formal meeting. There is nothing which communicates more "I take your opinion seriously" than a regular timeslot where all you will do is listen and be available to solve someone's issues.

An important part of the One on One meeting is explaining the purpose.

Offer a moment where you will listen exclusively to your team member
A monthly recap in preparation of the yearly performance evaluation
A moment to make sure we both agree on what the team member will be working on

Practical

My One-on-One meetings are organized on a monthly basis. I book a recurring 1 hour slot during the first week of the month. Potentially they can be arranged more frequently, but in a sizable team this could quickly eat away a large chunk of your time and restricting the meetings to 30 minutes or less makes them too short to really get to the difficult topics. Monthly works best for me.

I will reserve a private office somewhere, and I'll sit next to the person so we can agree on what I am typing as meeting notes (Disconnect network & smart phones. I consider it extremely disrespectful to interrupt the conversation).

All my notes are tracked in OneNote Notebook with a section per person and a page per meeting.

At the end of the meeting we quickly review the notes and I will send them to the person with my line manager in CC.

Agenda

I break the meeting down in 4 parts.

What have you done last month?
What will you do next month?
Where are you in achieving your yearly objectives?
Any open topics, any issues to raise, or worries?

What have you done last month?

This part of the meeting has a double purpose, it puts your team member at ease; he can talk about stuff he knows; and you get the chance to validate your project progress information. Let the person speak and ask specific questions to get them to talk. For people who didn't have the best month you will have to stress that it's not about blaming or attacking them, it's just to make sure that you have the right information and to make sure that they spend the time to check if they are on track or not.

(Sometimes it is not obvious to somebody that he is behind on his work.)

What will you do next month?

Now we are reaching the meat of the meeting. It is the point where you get to see if they have enough information on the upcoming projects. (Sometimes it get painful when I didn't do the best job in letting people know what is upcoming, but still very useful). It also lines up goals for the short term, which give a sense of purpose for the work to be achieved.

Where are you in achieving your yearly objectives?

This one I only added recently. Some people were complaining that they were taken by surprise at their yearly performance evaluation. "I didn't know that was an objective!", "You never gave me time to do this!". Going over the list of objectives on a monthly basis gives people advanced warning and allows them to ask me additional questions on how to achieve these objectives. It's also handy to plan in extra time in their planning.

I have a page in OneNote with the objectives listed as a check box list and we can simply tick off the things which have been achieved. This is very satisfying for both of us.

Any open topics, any issues to raise, or worries?

Here I push people to tell what's on their mind. Very often it is easy since you already talked about a lot of things and you have gotten the time to re-establish the trust needed to hear the complaints and critics. Most of what I do here is shut up and take notes. It is useful to stop people from descending into the "complaint-mode" by asking "What could I do for you?" or "How could we have prevented this."

Results

Initially about half of the team members were reluctant to go to these meetings, or tried to push them back, but after 3-4 sessions everybody is happy they happen and they even insist on the meetings happening on time. My impression is that they feel better since the meetings were established, and for me it gives a wealth of information on how they are doing, how they feel in their job and if I can do something for them to make things better. If doing these meetings prevents only one person from searching for another job, it is time well spent.

If you have never tried doing One-on-One meetings, give it a try for a few months, the initial investment is small, and the results are potentially big.

Permalink | Leave a comment »

Query the content of a temp table from another connection in SQL Server

Mon, 16 May 2011 13:49:00 -0700

The TSQL Debugger in SQL Server has become more mature over time (after a little dip in SQL Server 2005) and it is now quite easy to debug stored procedures and functions. The one feature still missing though is the lack of a view on the content of temp tables while stepping through the code.

Some older ways of viewing the content is by peppering the code with select statements, or by replacing the temp table by a permanent table.

I have long dreamed of a way to query the content of a temp table from another connection. This way I can step through a Stored procedure, and check the content of the table while the procedure is waiting at a breakpoint. Since I never managed to find a proper solution I decided to build something on my own.

This code would not have been possible without the blog posts from Fabiano Neves and Jonathan Kehayias.

The result of this work is the “sp_select” project on github (https://github.com/FilipDeVos/sp_select).

This project contains 2 stored procedures:

sp_select
sp_selectpages

They will use the output of the “DBCC PAGE” command to build up the content of a temp table.

Usage

The procedures can be used as follows. First open a new Query window and run the following statement without closing the query window:

1
2
3

CREATE TABLE #temp (id int, name varchar(200))
    INSERT INTO #temp VALUES (1, 'Filip')
    INSERT INTO #temp VALUES (2, 'Sam')

Open a second query window and run the following statement to see the content of the temp table:

1	exec sp_select 'tempdb..#temp'

This query will return the following result set:

Id	Name
1	Filip
2	Sam

How does it work

The procedure sp_select will try to pinpoint the object_id of the table you are trying to get the data from. When specifying a permanent table this is quite easy the function object_id() will return the correct value. When the target is a temp table this is quite difficult as SQL Server does not store a link between the temp table in tempdb and the session in an easily accessible way.

There are 3 scenarios implemented in the procedures.

There is only 1 temp table with the name you are looking for. ==> get the object_id from tempdb.sys.tables
There are more than 1 temp table with the name you are looking for and you did not specify a @spid. ==> find the first temp table matching the database name of the database you are running the procedure on
There are more than 1 temp table with the name you are looking for and you did specify a @spid ==> match the temp table with the spid by mining the default trace file log.trc.

Once the object_id is determined the procedure sp_selectpages will be used to return the content.

Use DBCC IND to return the list of pages to look at with DBCC PAGE
Loop over all the pages and store the page content with DBCC PAGE
use the PIVOT statement to pivot the key/value results to the original table layout

Known issues

There are a few outstanding issues to fix, but they will only extend the use of this procedure.

All the fields in the result set will have the type VARCHAR(6000)
The content of VARCHAR(MAX), NVARCHAR(MAX), TEXT, NTEXT, VARBINARY cannot be returned
No permissions are checked, if you cannot run DBCC PAGE you will end up with a nasty error at execution

Permalink | Leave a comment »

Going beyond sp_helpindex

Sun, 28 Jun 2009 19:43:00 -0700

Every SQL Server developer and DBA has to use the sp_helpindex from time to time to get some information on the indexes on a table.

I have 2 major problems with this stored procedure:

All information is concatenated in 1 string
Not much info on partitioned indexes

If I run the following statement:

1	exec sp_helpindex 'Customer'

The following information will be returned:

index_name	index_description	index_keys
pk_entity	clustered, unique, primary key located on PRIMARY	entity

To improve the way to work with this information I built the following function

CREATE FUNCTION dbo.fn_helpindex(@table_name sysname)
RETURNS @result table(table_name            sysname, 
                      index_name            sysname, 
                      is_unique             bit, 
                      ignore_dup_key        bit, 
                      is_primary_key        bit, 
                      is_unique_constraint  bit, 
                      fill_factor           tinyint, 
                      is_padded             bit, 
                      is_disabled           bit, 
                      is_hypothetical       bit, 
                      allow_row_locks       bit, 
                      allow_page_locks      bit, 
                      data_space_type       nvarchar(2),
                      data_space            nvarchar(1024))
AS
  BEGIN
    
    WITH partitionfields ([object_id], [index_id], [name]) AS 
    (SELECT ic.[object_id] ,ic.index_id, c.name 
       FROM sys.index_columns ic
       JOIN sys.columns c 
         ON  ic.[object_id] = c.[object_id] 
        AND ic.column_id = c.column_id 
      WHERE ic.partition_ordinal = 1)
    INSERT INTO @result
    SELECT table_name = object_name(i.[object_id])
         , i.name
         , i.is_unique
         , i.ignore_dup_key
         , i.is_primary_key
         , i.is_unique_constraint
         , i.fill_factor
         , i.is_padded
         , i.is_disabled
         , i.is_hypothetical
         , i.allow_row_locks
         , i.allow_page_locks
         , data_space_type = ds.type
         , data_space = coalesce(ds.name + N'(' + pf.name + N')', ds.name)
    FROM sys.indexes i 
      INNER JOIN sys.data_spaces ds
      ON  i.data_space_id = ds.data_space_id
      LEFT JOIN partitionfields pf
      ON i.[object_id] = pf.[object_id] AND
         i.index_id = pf.index_id
    WHERE i.name IS NOT NULL 
      AND object_name(i.[object_id]) = @table_name

    -- Return the result of the function
    RETURN 
  END

If I run:

1	select * from dbo.fn_helpindex('Customer')

The following information will be returned:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
Customer	pk_entity	1	1	0	1	0	0	0	0	0	1	1	FG	PRIMARY

If we use the function on a table with a partitioned index (like the script below) the function will return more information about the data space as well.

CREATE PARTITION FUNCTION pfn_maturity_bucket (int)
AS RANGE RIGHT FOR VALUES (3, 6, 12);

CREATE PARTITION SCHEME psc_maturity_bucket
AS PARTITION pfn_maturity_bucket
ALL TO ([PRIMARY])

CREATE TABLE LoanDetails(
         code            nvarchar(50)
       , amount          numeric(23,3)
       , maturity_bucket int
       )

CREATE INDEX ixLoanDetails 
          ON LoanDetails(maturity_bucket) 
          ON psc_maturity_bucket(maturity_bucket)

So, let's run the following statement on the partitioned table:

1	select * from dbo.fn_helpindex('LoanDetails')

The output will be:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
LoanDetails	ixLoanDetails	0	0	0	0	0	0	0	0	0	1	1	PS	psc_maturity_bucket(maturity_bucket)

Permalink | Leave a comment »

How to determine the default database path in all SQL Server Versions

Fri, 12 Jun 2009 16:04:00 -0700

When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations.

Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right click on the server and select "Server Properties", then go to the "Database Settings" tab and near the bottom you will see a section with "Database Default locations")

For example, suppose I execute the following statement:

1	CREATE DATABASE DefaultLocationDB

On my machine this means 2 files will be created:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.ldf

This is nice, you don't have to remember a path to pass, but when you want to restore a database from another server (where a different path is used) it can be a pain.

To restore a database with a specific location for the files we use the following syntax:

RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N'c:\backups\DemoDB.bak' WITH  FILE = 1,  
MOVE N'demo_data_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
MOVE N'demo_log_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
NOUNLOAD, REPLACE

To make it easy to restore a database using a script I designed a function to figure out the location for the default Data path (and another one for the default log path).

The locations of these paths I got from http://msdn.microsoft.com/en-us/library/ms143547.aspx/

The output can be stored in a 260 character variable (260 as specified MAX_PATH in http://msdn.microsoft.com/en-us/library/aa365247.aspx).

Edit: Rudi Larno posted a very nice refactored version of the code I posted. (especially note the nice recursion in it.)

IF object_id('dbo.fn_get_default_path') is not null 
  DROP FUNCTION dbo.fn_get_default_path 
GO 
CREATE FUNCTION dbo.fn_get_default_path(@log bit) 
  RETURNS nvarchar(260) 
AS 
  BEGIN 
    DECLARE @instance_name nvarchar(200)
          , @system_instance_name nvarchar(200)
          , @registry_key nvarchar(512)
          , @path nvarchar(260)
          , @value_name nvarchar(20); 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER'); -- sql 2005/2008 with instance 
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output; 
    
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer'; 
    SET @value_name = N'DefaultData' 
    IF @log = 1 
      BEGIN 
        SET @value_name = N'DefaultLog'; 
      END 
    
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output; 
    
    IF @log = 0 AND @path is null -- sql 2005/2008 default instance 
      BEGIN 
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup'; 
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path output; 
        SET @path = @path + '\Data'; 
      END 
    
    IF @path IS NULL -- sql 2000 with instance 
      BEGIN 
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer'; 
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output; 
      END 
      
    IF @path IS NULL -- sql 2000 default instance 
      BEGIN 
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer'; 
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output; 
      END 
      
    IF @log = 1 AND @path is null -- sql 2005/2008 default instance 
      BEGIN 
        SELECT @path = dbo.fn_get_default_path(0) 
      END 
    
    RETURN @path; 
  END 
GO

The functions can then be used as follows (or you can just copy paste the output):

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_path(0),
       @log = dbo.fn_get_default_path(1)

SELECT @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)

Permalink | Leave a comment »

How to create SQL Server temp tables without collation problems

Tue, 08 Jul 2008 07:35:00 -0700

In SQL Server it is possible to create temporary tables which are special tables that are automatically cleaned up when they go out of scope. There are two types of temporary tables, global and local temporary tables. Global Temporary Tables are visible in all sessions currently connected to the SQL Server instance and only go out of scope when the last session disconnects from the server. It is easy to recognise global temporary tables because their name starts with 2 #-signs. (for example "##customers") Local Temporary Tables are visible only in the current session and go out of scope when this session ends. Their name always starts with a single #-sign. (for example "#customer") Traditionally people create temporary tables the same way as they create normal tables

CREATE TABLE #RockAndRoll(
    ID           int     primary key 
  , SongTitle    nvarchar(20)
  , Genre        nvarchar(200)
  , Sales        int
)

This seems like a perfectly reasonable way to create a temp table. Unfortunately SQL Server has one big flaw.

Presume we have the following situation. My SQL Server is installed with a Latin1_General_Bin collation and we create a database with a different collation.

1
2
3

CREATE DATABASE strangeCollationDb COLLATE Latin1_General_CI_AI_KS_WS
GO
USE strangeCollationDb

Then we create a table in this database with some details about Rock Stars and a temp table with additional info.

CREATE TABLE RockStars (id int, name varchar(50), location varchar(400))
INSERT INTO RockStars VALUES (1, 'Mick Jagger', 'USA')
INSERT INTO RockStars VALUES (2, 'Jim Morrison', 'Heaven or Hell')

CREATE TABLE #RockStarInfo (name varchar(50), information varchar(400))
INSERT INTO #RockStarInfo VALUES ('Mick Jagger', 'Alive and kicking')
INSERT INTO #RockStarInfo VALUES ('Jim Morrison', 'Passed away')

This looks like a perfectly valid situation, but when we run the following query the problems start:

SELECT * 
FROM Rockstars s
  LEFT JOIN #RockStarInfo i
  ON s.name = i.name

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "Latin1_General_BIN" and "Latin1_General_CI_AI_KS_WS" in the equal to operation.

When we inspect the metadata closely we find the following:

SELECT db_name() as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   = 'RockStars' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'
 
SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

db	TABLE_NAME	COLUMN_NAME	COLLATION_NAME
strangeCollationDb	RockStars	name	Latin1_General_CI_AI_KS_WS
tempdb	#RockStarInfo...	name	Latin1_General_BIN

For some reason SQL Server will create temporary tables using the collation of tempdb instead of inheriting the collation of the current db in the connection context.

But all is not lost. If we simply use a different approach to creating our temporary table we can solve all collation problems without adding collate statements to our code. Besides using the create table statement it is also possible to use SELECT INTO to create a table.

SELECT cast(null as varchar(50)) as name, cast(null as varchar(400)) as information
INTO #RockStarInfo2 
WHERE 1=2
INSERT INTO #RockStarInfo2 VALUES ('Mick Jagger', 'Alive and kicking')
INSERT INTO #RockStarInfo2 VALUES ('Jim Morrison', 'Passed away')

SELECT * 
FROM Rockstars s
  LEFT JOIN #RockStarInfo2 i
  ON s.name = i.name

id	name	location	name	information
1	Mick Jagger	USA	Mick Jagger	Alive and kicking
2	Jim Morrison	Heaven or Hell	Jim Morrison	Passed away

This time no errors were raised and if we check the metadata in tempdb we can see that the collation is the same as the collation in the source db.

SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo2%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

db	TABLE_NAME	COLUMN_NAME	COLLATION_NAME
tempdb	#RockStarInfo2...	name	Latin1_General_CI_AI_KS_WS

The End... or not completely...

When all you use are the base system datatypes the problems are solved, unfortunately when you want to use alias data types the syntax I put in the example does not work since you can only use CAST() with system data types.

Fortunately a (bit more convulated) solution exists for this as well.

CREATE TYPE NameType FROM varchar(50)
CREATE TYPE LocationType FROM varchar(50)

CREATE TABLE MoreRockStars (id int, name NameType, location LocationType)
INSERT INTO MoreRockStars VALUES (1, 'Jeff Buckley', 'Heaven')
INSERT INTO MoreRockStars VALUES (2, 'Neil Young', 'USA')

DECLARE @Name     NameType, 
        @Location LocationType

SELECT @Name as name, @Location as information into #RockStarInfo3 
INSERT INTO #RockStarInfo3 VALUES ('Jeff Buckley', 'Drowned sadly')
INSERT INTO #RockStarInfo3 VALUES ('Neil Young', 'Keeps on rocking')

SELECT * 
FROM MoreRockstars s
  LEFT JOIN #RockStarInfo3 i
  ON s.name = i.name

id	name	location	name	information
1	Jeff Buckley	Heaven	Jeff Buckley	Drowned sadly
2	Neil Young	USA	Neil Young	Keeps on rocking

As you can see, it is not possible to use the alias data type directly in the SELECT INTO statement, but it is possible to declare a variable with the datatype and then use the variable in the SELECT INTO statement.

And again, when we check the table layout in tempdb, we can see the collation has been inherited.

SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo3%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

db	TABLE_NAME	COLUMN_NAME	COLLATION_NAME
tempdb	#RockStarInfo3...	name	Latin1_General_CI_AI_KS_WS

When you start supporting the internationalization features of SQL Server, many of the development practices you have learned and applied for years become invalid. And it is important to test your software in "non standard" installations like databases deployed with different collations.

Permalink | Leave a comment »

How to strip trailing zeros

Fri, 09 May 2008 12:28:00 -0700

A client came to me with another string formatting problem. I would never recommend formatting strings in T-SQL, but rather to do formatting on the client side. The question was to strip trailing zeros from a numeric value stored in a string but retain the decimals when they are not zero.


1.0501	needs to show	1.0501
1.0500	needs to show	1.05
1.0000	needs to show	1

This is easy to do by replacing all zeroes with a space and then using RTRIM() to remove the right spaces. Finally replace the spaces again with zeroes and you have the correct result. Except when all decimals were zero, in that case the decimal point needs to be suppressed as well. I poured the code in a function that is easier to use.

Note: The function contains some addition logic to determine if the decimal separator is a point or a comma.

CREATE FUNCTION dbo.fn_strip_zeros(@number numeric(38,10))
RETURNS varchar(38)
WITH ENCRYPTION
AS
  BEGIN
    DECLARE @result varchar(38)
    DECLARE @decimal varchar(3)

    SET @decimal = substring(convert(varchar(38), convert(numeric(38,10),1)/5 ), 2,1)

    set @result = rtrim(replace(replace(rtrim(replace(@number,'0',' ')),' ','0') + ' ', @decimal + ' ', ''))

    RETURN @result
  END

GO

Permalink | Leave a comment »

How to make a thread safe insert/update procedure on SQL Server

Fri, 23 Nov 2007 09:17:00 -0800

When developing an application that does a lot of inserts and updates, it is sometimes easier to wrap both statements in one stored procedure. This results in a simple procedure that will check if a certain record exists. When no record is found a new one is inserted, when a record is found the values are updated.

We will use the following table script as example to explain the problem:

create table updatetest 
  (
   id      int primary key, 
   name    varchar(20), 
   amount  numeric
  )

insert into updatetest values (1, 'first', 100)
insert into updatetest values (2, 'second', 500)
insert into updatetest values (3, 'third', 30)
insert into updatetest values (4, 'fourth', 200)

When making an insert/update procedure most people write something like this:

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

IF EXISTS(SELECT * FROM updatetest WHERE id = @id)
  BEGIN
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id
  END
ELSE
  BEGIN
    INSERT INTO updatetest VALUES (@id, @name, @amount)
  END

This looks like the perfect solution to our problem except for one detail. When this procedure is executed from several threads at the same time to update the same record, it will crash.

Let's alter our procedure script a bit to make it easier to reproduce the problem.

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

IF EXISTS(SELECT * FROM updatetest WHERE id = @id)
  BEGIN
    waitfor delay '00:00:10'
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id
  END
ELSE
  BEGIN
    waitfor delay '00:00:10'
    INSERT INTO updatetest VALUES (@id, @name, @amount)
  END

Compile this script and then run the following statements (at the same time) in 2 query windows.

1	exec updatevalues 7, 'Seven', 777

Surprise surprise, look at the error we get in the second query window...

"Msg 2627, Level 14, State 1, Procedure updatevalues, Line 17
Violation of PRIMARY KEY constraint 'PK__updatetest__29AC2CE0'. Cannot insert duplicate key in object 'updatetest'.
The statement has been terminated.
(0 row(s) affected)

Why does this happen? Simple, the first execution checks if the record exists and decides to insert a new record because the if exists returns false. The second thread is executing the same query at the same time and of course the existance check returns false as well. They both try to insert and a primary key error happens.

Some people think that this will never happen, but they are wrong, from the moment you are working with large amounts of data and users, this conflict will happen very often.

Luckily there is an easy way to solve this (very annoying) problem by using Lock hints in our advantage.

We will update our test procedure to include a transaction and an Exclusive Lock:

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

BEGIN TRANSACTION
IF EXISTS(SELECT * FROM updatetest (XLOCK, SERIALIZABLE) WHERE id = @id)
  BEGIN
    waitfor delay '00:00:10'
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id
  END
ELSE
  BEGIN
    waitfor delay '00:00:10'
    INSERT INTO updatetest VALUES (@id, @name, @amount)
  END
COMMIT TRANSACTION
RETURN(0)

The updated code has as effect that the second thread will wait until the first thread has completed it's operation. It is no longer possible that another operation on the same record is "sneaking" in between the check and the insert or update.

Since I simplified the procedure script for the example you can see a complete version of the code below.

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'updatetest' and type = 'U')
 BEGIN
   PRINT 'Dropping table updatetest...'
   DROP TABLE dbo.updatetest
 END
PRINT 'Creating table updatetest...'
GO
CREATE TABLE updatetest 
  (
   id     int PRIMARY KEY, 
   name   varchar(20), 
   amount numeric
  )
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'updatetest' AND type = 'U')
  BEGIN
    PRINT 'TABLE updatetest has been created...'
  END
ELSE
  BEGIN
    PRINT 'TABLE updatetest has NOT been created due to errors...'
  END

GO

insert into updatetest values (1, 'first', 100)
insert into updatetest values (2, 'second', 500)
insert into updatetest values (3, 'third', 30)
insert into updatetest values (4, 'fourth', 200)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'updatevalues'  and type = 'P')
 BEGIN
   PRINT 'Dropping procedure updatevalues...'
   DROP PROCEDURE dbo.updatevalues
 END
PRINT 'Creating procedure updatevalues...'
GO
CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

DECLARE @error int

BEGIN TRANSACTION
IF EXISTS(SELECT * FROM updatetest (XLOCK, SERIALIZABLE) WHERE id = @id)
  BEGIN
    
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id

    IF @@error <> 0 or @@rowcount = 0 
      BEGIN 
        RAISERROR('Failed to update the record', 16, 1)
        ROLLBACK TRANSACTION
        RETURN (1)
      END
  END
ELSE
  BEGIN

    INSERT INTO updatetest VALUES (@id, @name, @amount)
    IF @@error <> 0 
      BEGIN 
        ROLLBACK TRANSACTION
        RETURN (1)
      END

  END
COMMIT TRANSACTION

RETURN(0)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'updatevalues' AND type = 'P')
  BEGIN
    PRINT 'PROCEDURE updatevalues has been created...'
  END
ELSE
  BEGIN
    PRINT 'PROCEDURE updatevalues has NOT been created due to errors...'
  END

GO

Addendum in 2011:What could be the consequence of this script? Deadlocks Since SQL Server will do "Lock escalation" it is possible to encounter deadlocks with this pattern. It is up to you to decide which error you prefer catching. The procedure below shows an approach to catch the duplicate key errors in TSQL and retry.

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

DECLARE @error int
      , @message sysname
      , @severity int
      , @state int
      , @retry bit = 0
      
BEGIN TRANSACTION

        WHILE @retry = 0
          begin
              BEGIN TRY
                SET @retry = 1
                IF EXISTS(SELECT * FROM updatetest WHERE id = @id)
                  BEGIN
    
                    UPDATE updatetest 
                    SET name = @name,
                        amount= @amount
                    WHERE id = @id

                    IF @@error <> 0 or @@rowcount = 0 
                      BEGIN 
                        RAISERROR('Failed to update the record', 16, 1)
                        ROLLBACK TRANSACTION
                        RETURN (1)
                      END
                  END
                ELSE
                  BEGIN

                    INSERT INTO updatetest VALUES (@id, @name, @amount)
                    IF @@error <> 0 
                      BEGIN 
                        ROLLBACK TRANSACTION
                        RETURN (1)
                      END

                  END
            END TRY
            BEGIN CATCH
               SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE()
                IF @error in (2627 /* unique or primary key constraint */, 2601 /* unique index */)
                  SET @retry = 0
                ELSE
                  BEGIN
                    RAISERROR (@message, @severity, @state)
                    ROLLBACK TRAN
                    RETURN (-1)
                  END
            END CATCH
       END
COMMIT TRANSACTION

RETURN(0)
GO

Permalink | Leave a comment »

What to do when you can't drop an empty FileGroup in SQL Server

Mon, 06 Aug 2007 07:27:00 -0700

I was working on a system to partition tables on SQL Server 2005 which means I was creating database filegroups and database files and I needed to drop my newly created objects regularly to retest my code.

But sometimes it was not possible to drop the filegroups with complaints that the filegroup was not empty.

After a lot of googling I found that it is possible that statistics are defined on the filegroup (most likely automatic stats).

To see this you can run the following query.

SELECT object_name(id) AS TableName, * 
FROM dbo.sysindexes 
WHERE groupid = object_id('<yourfilegroup>')

After dropping the statistics I could drop the filegroup without problems.

Permalink | Leave a comment »

Zero Padding a numeric value in Transact SQL

Mon, 02 Jul 2007 14:51:00 -0700

A quick one before I go home (and just because I used it 10 minutes ago)... I zero pad a numeric value like this:

DECLARE @mynumber INT
      , @padding  INT 

SELECT @mynumber = 123
     , @padding = 8
SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')

The result of this example will be


00000123

I admit, it is not very elegant, but is string manipulation ever elegant in TSQL... Update: note that the STR function takes a float as parameter, so you might end up with rounding issues.

Permalink | Leave a comment »

Recognising the SQL Server Version

Fri, 29 Jun 2007 13:41:00 -0700

Something I often need to do is recognise which SQL Server version I am working on. For example to separate SQL 2000 compatible code from SQL Server 2005 compatible code. I'll show you some of the options to get the version number... Starting from the ugliest to the most elegant... The first option is calling the extended procedure xp_msver

1	exec xp_msver 'ProductVersion'

Which give you this result:

Index	Name	Internal_Value	Character Value
2	ProductVersion	524288	8.00.2039

xp_msver can give you quite a large amount of version information about your system, but it is not very pretty to use in your code. There is also no option to get a simple version number. Another option is to call @@version

1	select @@version

Which gives you something that looks like:


Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

This is of course not very practical when you are programming. So another option is fetching the version number from the server properties.

1	select serverproperty('productversion')

And the result looks a bit like this:


9.00.3054.00

This is already more practical. But still difficult to use in your programs. My favorite option is using the @@microsoftversion function. This function returns a `varbinary` indicating the complete version of SQL Server.

1	select @@microsoftversion / 0x01000000

And the result of this query is a very simple:


9

The following query shows you the possible values for the recent SQL Server versions (I don't have a SQL 6 - 7 test environment anymore, so I don't know if it works on those.)

SELECT CASE @@microsoftversion/ 0x01000000 
         WHEN 8 THEN 'sql 2000'
         WHEN 9 THEN 'sql 2005'
         WHEN 10 THEN 'sql 2008'
         WHEN 11 THEN 'sql Denali'
       END

Permalink | Leave a comment »

Using SET IDENTITY_INSERT as a normal database user in SQL Server 2005.

Tue, 26 Jun 2007 11:56:00 -0700

In earlier versions of Microsoft SQL Server it was impossible to grant "SET IDENTITY_INSERT" rights to a normal user. To have permission to do an identity_insert (even as part of a stored procedure) you needed to grant the user DDL admin rights which is quite a heavy permission to grant to a normal user. Luckily this problem can be solved in SQL Server 2005 by using Certificates and code signing.

Let's set up a demo environment.

create database identity_insert_test
GO
use identity_insert_test
exec sp_addlogin 'SimpleLogin', 'PasswordForSimpleLogin'
GO
exec sp_grantdbaccess 'SimpleLogin', 'SimpleLogin'
GO

if object_id('dbo.a') is not null
   drop table dbo.a
create table dbo.a (keyfield int identity, somevalue varchar(10))
GO

if object_id('p_a')is not null 
  drop procedure p_a
go
create procedure p_a
as
set identity_insert dbo.a on
insert into dbo.a (keyfield, somevalue) values (2,'nice')
set identity_insert dbo.a off

return (0)
go

grant exec on p_a to SimpleLogin
go

When we run the stored procedure with the simple "no rights" login we quickly see the following error:

Msg 1088, Level 16, State 11, Procedure p_a, Line 3
Cannot find the object "dbo.a" because it does not exist or you do not have permissions.

(Make sure you run the "revert;" part of the statement to revert to your login after the error.)

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

To fix this annoying problem we need to create a few more objects in the database. First of all we need a master key which will be used to encrypt the certificate. Then we create a certificate, a login based on the certificate and we grant the "ALTER ANY SCHEMA" permission to the login. And finally we will sign the stored procedure with the schema login.

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser
ADD SIGNATURE TO p_a BY CERTIFICATE SchemaCert
GO

Now when we rerun the stored procedure as our simple login we see that everything works as expected.

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

The ability to sign stored procedures gives you the power to build a much more secure SQL Server Database system. For example you can sign stored procedures that use dynamic sql and remove the need to grant select rights on the underlying tables.

I have not (yet) benchmarked the performance impact of signing stored procedures, but I suspect the overhead will not be huge.

Permalink | Leave a comment »

Using a schema collection to validate output of FOR XML RAW

Fri, 25 May 2007 07:21:00 -0700

Schema collections are used in SQL Server 2005 to create an xml datatype field that is validated with an XML Schema. Unfortunately there is an xsd feature that is not implemented very nicely is the processContents="lax" attribute. The attribute processContents is implemented, but where normal xsd supports the values strict, lax and skip. The sql server schema collection parser only supports strict and skip. And unfortunately using lax does not degrade, it just gives a compilation error. So reusing prebuild xsd files is going to create some issues that need to be resolved before you can compile them in the database. I will show an example where using the processContents="lax" technique would be usefull. The following schema can be used to create a datatype to store a result of a "for xml raw" query. When you set processContents="lax" you will get a compilation error. Although it would be useful, we have to be content with processContents="skip".

IF EXISTS(SELECT * FROM sys.schemas WHERE name ='xml_raw_schema')
    DROP XML SCHEMA COLLECTION  dbo.xml_raw_schema
GO
CREATE XML SCHEMA COLLECTION xml_raw_schema AS
 N'<?xml version="1.0"?>
 <xsd:schema xmlns:targetNamespace="http://schemas.mycompany.com/myproduct"
            elementFormDefault="qualified"
            attributeFormDefault="unqualified"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <xsd:element name="table">
     <xsd:complexType>
        <xsd:sequence>
           <xsd:element name="row" minOccurs="0" maxOccurs="unbounded">
              <xsd:complexType>
                 <xsd:anyAttribute processContents="skip" />
              </xsd:complexType>
           </xsd:element>
        </xsd:sequence>
     </xsd:complexType>
   </xsd:element>
</xsd:schema>'

You can use the schema collection like this:

1
2
3

declare @xml xml(xml_raw_schema)
SET @xml = (select * from sys.tables FOR XML RAW, TYPE, ROOT('table'))
select @xml

Which shows (in my test case) the following output:

1
2
3

<table>
  <row name="Orders" object_id="2073058421" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" create_date="2007-04-12T17:30:08.150" modify_date="2007-04-12T17:46:17.043" is_ms_shipped="0" is_published="0" is_schema_published="0" lob_data_space_id="0" max_column_id_used="3" lock_on_bulk_load="0" uses_ansi_nulls="1" is_replicated="0" has_replication_filter="0" is_merge_published="0" is_sync_tran_subscribed="0" has_unchecked_assembly_data="0" text_in_row_limit="0" large_value_types_out_of_row="0" />
</table>

Although we have to be carefull that validating all your xml does not slow down processing too much it's a valuable technique because you can achieve a basic level of type safety.

Permalink | Leave a comment »

Fix SQL Server management studio Ctrl+Tab annoyance

Tue, 22 May 2007 07:27:00 -0700

In SQL Server 2000 Query analyzer hitting Ctrl+Tab would advance you one query window in the list of open query windows. Unfortunately In Sql Server Management Studio this will display the default Visual Studio Tab overview window. This window has one huge flaw and that is that it only displays the first 25 characters of the description of the open window. Which results in the Case of SQL Server Management Studio in a list with 10 times the same item.

To revert the Ctrl+Tab and the Ctrl+Shift+Tab behaviour back to the way it was in previous versions of the SQL Server client tools you can do the following hack. Close all instances of SQL Server Management Studio and open the following xml file in a text editor:

C:\Documents and Settings\<your user name>\My Documents\SQL Server Management Studio\Settings\CurrentSettings-####-##-##.vssettings

Note that the ####-##-## characters form a date (most likely the installation/service pack installation date.) and you sometimes can have multiple versions of this file. Updating the file with the latest date should work. (I have no pc's with multiple versions of this file.)

In the file look for the following string. (The file will have a few very long lines of text in it.)

<UserShortcuts/>

Once you found it, replace it with the xml code below and when you restart Management studio using Ctrl+Tab and Ctrl+Shift+Tab will once again switch you to the following or previous code window without showing the useless dialog.

<UserShortcuts>
  <RemoveShortcut Command="Window.NextDocumentWindowNav" Scope="Global">Ctrl+Tab</RemoveShortcut>
  <Shortcut Command="Window.NextDocumentWindow" Scope="Global">Ctrl+Tab</Shortcut> 
  <Shortcut Command="Window.NextDocumentWindow" Scope="Query Designer">Ctrl+Tab</Shortcut>
  <RemoveShortcut Command="Window.PreviousDocumentWindowNav" Scope="Global">Ctrl+Shift+Tab</RemoveShortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Global">Ctrl+Shift+Tab</Shortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Query Designer">Ctrl+Shift+Tab</Shortcut>
</UserShortcuts>

Credit: I found this tip burried in a comment on this page of the Microsoft connect website. So thanks to ayeltsov for suggesting the solution to an annoying problem.

Permalink | Leave a comment »

Why is viewing a partitioned table layout so difficult

Mon, 21 May 2007 09:32:00 -0700

For some reason the Microsoft SQL Server developers decided not to provide any tools to see how a partitioned table is built. Since it is quite important information I wrote a little sp_help_partition TSQL stored procedure to give an overview. There is an optional object parameter which allows you to filter on a specific partitioned table, or shows you all the partitions in the system.

The procedure is installed on the master database, but because the name starts with sp_... you can run it in any database.

1	exec sp_help_partition 'Orders'

The result of this statement could be:

Table	Partition Scheme	Partition Function	PartitionNumber	FileGroupName	RangeValue
Orders	monthly_scheme	monthly_bucket	1	History	2006-06-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	2	Partition_02	2006-07-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	3	Partition_03	2006-08-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	4	Partition_04	2006-09-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	5	Partition_05	2006-10-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	6	Partition_06	2006-11-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	7	Partition_07	2006-12-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	8	Partition_08	2007-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	9	Partition_09	2007-02-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	10	Partition_10	2007-03-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	11	Partition_11	2007-04-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	12	Partition_12	2007-05-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	13	Partition_01	2999-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	14	Partition_99	NULL

USE master
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_help_partition')
  BEGIN
    PRINT 'Dropping procedure sp_help_partition...'
    DROP PROCEDURE sp_help_partition
  END
print 'Creating procedure sp_help_partition...'
GO
CREATE PROCEDURE sp_help_partition (@object_name sysname = NULL)
AS

DECLARE @db_name sysname
SELECT @db_name = db_name()

IF @object_name IS NULL
 BEGIN
    EXEC ('use ' + @db_name + '
    SELECT
          object_name(i.[object_id]) as [Table],
          ps.name                    as PartitionScheme,
          pf.name                    as PartitionFunction,
          dds.destination_id         as PartitionNumber,
          fg.Name                    as FileGroupName,
          prv.value                  as RangeValue
    FROM sys.indexes i
      INNER JOIN sys.partition_schemes ps on ps.data_space_id = i.data_space_id
      INNER JOIN sys.partition_functions pf on ps.function_id = pf.function_id
      INNER JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
      INNER JOIN sys.filegroups fg on fg.data_space_id = dds.data_space_id
      LEFT  JOIN sys.partition_range_values prv on prv.boundary_id = dds.destination_id
    WHERE i.type = 2 ')
  END
ELSE
  BEGIN
    EXEC ('use ' + @db_name + '
    SELECT
          object_name(i.[object_id]) as [Table],
          ps.name                    as PartitionScheme,
          pf.name                    as PartitionFunction,
          dds.destination_id         as PartitionNumber,
          fg.Name                    as FileGroupName,
          prv.value                  as RangeValue
    FROM sys.indexes i
    INNER JOIN sys.partition_schemes ps on ps.data_space_id = i.data_space_id
    INNER JOIN sys.partition_functions pf on ps.function_id = pf.function_id
    INNER JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
    INNER JOIN sys.filegroups fg on fg.data_space_id = dds.data_space_id
    LEFT  JOIN sys.partition_range_values prv on prv.boundary_id = dds.destination_id
    WHERE i.type = 2 and
          i.[object_id] = object_id(''' + @object_name + ''')')
  END
RETURN(0)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_help_partition' AND type = 'P')
  BEGIN
    PRINT 'PROCEDURE sp_help_partition has been created...'
  END
ELSE
  BEGIN
    PRINT 'PROCEDURE sp_help_partition has NOT been created due to errors...'
  END
GO

For more information about table partitioning in Microsoft SQL Server 2005 read the excellent whitepaper by Kimberley Tripp

Permalink | Leave a comment »