Gregg Stark on SQL Server

Determine SQL Server Version

If you need to know the version you are currently running of SQL Server you can easily get it by running the following query. 

 SELECT 'SQL Server ' + 
 CONVERT(varchar(100),SERVERPROPERTY('productversion')) + ' - ' +
 CONVERT(varchar(100),SERVERPROPERTY('productlevel')) + ' - ' +
 CONVERT(varchar(100),SERVERPROPERTY('edition'))

Arithabort Option Affects Stored Procedure Performance

I have noticed that at times I will have a stored procedure start to take an unusual amount of time to complete.  In trying to debug this I grab the SQL that is being called from the application and paste it into SQL Server Management Studio only to have it come back in a second.  So I try again from the application, and it takes well over a minute.  After hunting around and pulling out my hair as to why this would be I discovered that the connection from the application has the Arithabort option off and in SQL Server Management Studio, by default the Arithabort option is on.  So I went ahead and ran a set Arithabort off and then ran the stored procedure within SQL Server Management Studio and sure enough it ran forever.  From what I can tell having this option off was causing the query optimizer to pick some horrible query plan.  I know SQL Server caches query plans by the connection options, but this just doesn't make much sense to me.  The best fix I have come up with for this is to compile the proc with the "with recompile" option.  I don't really like this option as SQL Server won't cache the query plan, but I haven't found any other solutions that work consistently.  You can do this as such.

Create Procedure MyStoredProcedure
  @MyParameter varchar(10)
with Recompile
as
   select 1

I am interested in what others have found for a solution to this problem or any other insight into this as it is quite annoying and if you haven't run into it before it can be quite time consuming and annoying.

Off Topic: Strange VMWare Contest Rules

This has to go down as one of the strangest requirements for a contest ever.  Check out the official rules for VMWare's contest to win an iPhone.  In section 4 "Selection of Winner" it states

"Canadian residents, if selected as a winner, must correctly answer a mathematical skill-testing question in order to receive a prize."

Apparently VMWare wants to make sure they are giving the iPhone to "educated" Canadians.

Here is a link to the actual pdf on their site for those that want to see it with their own eyes.

http://campaign.vmware.com/docs/Legal_Copy_1939.pdf

I have to believe that this was a joke and someone forgot to remove it...regardless it is definitely odd and very funny.

SQL Server 2008 November CTP Released

Microsoft has released the latest SQL Server 2008 CTP.  This release has tons of new features including Intellisense for SSMS.  This one also includes the spatial datatypes which I know a lot of people have been waiting for.  You can go get the newest CTP here.  For a list of all new features included in this CTP, you can go here.

Increase Animation Speed in SQL Server Management Studio

I set all my tool bars to be fly outs and it is fairly annoying how slow the animation is.  I found this link and decided to try it out and it works great! 

http://blogs.msdn.com/euanga/archive/2007/10/01/ssms-window-animation-speed.aspx

I would be interested in knowing other configuration settings you can change in that file as well.  Let me know if you know of any others.

Change Login's Default Database in SQL Server

If you have ever dropped a database and found yourself not able to login because your default database no longer exists you know it can be quite annoying.  You can still connect through query analyzer by connecting to a different database, but you won't be able to do anything through the object explorer as it always defaults to your default database.  The sql below will solve your problems.

alter login  MyLogin
with default_database = master

If you need to alter a login that is using windows authentication use the following code.  The only difference is you just have to specify the domain and the directory login and put it in brackets. 

alter login  [MyDomain\MyLogin]
with default_database = master

I hope this helps and if not at least I will be able to easily find the solution next time I run into it. 

Retrieve Index Details and Columns in Sql Server 2005

I frequently need to look at what indexes are on a table and I am really not a fan of using object explorer in SSMS as I like to stay in the query window.  Typically if I want information about a table I simply highlight the table name in the query window and hit Alt-F1.  If you aren't familiar with keyboard shortcuts in SSMS see my post here.  While this does give you the indexes that are on the table and it will show you the equality and inequality columns on the index, it won't show you the included columns on the index.  So I decided to dig into the Sql Server Dynamic Management Views (DMVs) and write a query to return all the information about an index or even all indexes on a table.  Here is what I came up with.  This will return all indexes for a table. 

select object_name(i.object_id) as TableName, i.name as IndexName, c.name as ColumnName,
       i.Type_Desc, ic.Is_Included_Column, key_ordinal
  from sys.indexes i 
  join sys.Index_columns ic on ic.object_id = i.object_id and  ic.index_id = i.index_id
  join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
  where object_name(i.object_id) = 'MyTable'
  order by i.type_desc, i.name, Is_Included_Column asc, ic.key_ordinal

Let me know if you have any questions or improvements. 

Archive and Compress Data from SQL Server

I recently blogged about deleting off data from a table by looping through one day at a time.  Strangely enough I had a similar need pop up a few days later although this time I wanted to keep the data around just in case I need it later.  I really didn't want to put the data into another table so I decided it might be nice to dump the data to a file and then zip it up into a zip file per day.  This way if I ever need the data again I can easily bcp the data back into the original table.  On my server I actually

This actually was pretty straightforward to do.  Basically we can just loop through the data by a given interval (in my case I chose a day), BCP the data to a file, zip the file, and finally delete the data.  Here is what my code looks like.  Once again I used a numbers table as in my previous post.  One item to note is that you will need some executable to do the zipping of the file.  In this case I just used zip.exe.  Also if you are on 2005 you will need to enable xp_cmdshell as well.

declare @startdate datetime
declare @EndDate datetime
declare @period datetime
declare @QueueVendorOrderActivityID uniqueIdentifier 
declare @cmd varchar(1000)

set @startdate = '9/01/2007'
set @enddate = '9/15/2007'

declare datecursor cursor forward_only read_only
    for Select dateadd(d,Number,@startdate) from Numbers
          where Number <= datediff(d, @startdate, @enddate)
          order by Number
open datecursor

while (1=1)
begin
  fetch next from datecursor into @period
  if @@fetch_status <> 0
    break;
    -- build cmd string to execute for bcping out the data  
    set @cmd = 'bcp "select * from MyTable where createdate between ''' + 
        convert(varchar(50), @period, 112) + ''' and dateadd(d, 1, ''' + 
        convert(varchar(50), @period, 112) + ''')" queryout c:\MyTable' + 
        convert(varchar(50), convert(varchar(50), @period, 112), 112) + '.dat -Umyuser -Pmypass -c'
    
    exec xp_cmdshell @cmd
    -- Now zip the file that we created.
    set @cmd = 'c:\zip c:\MyTable' + convert(varchar(50), @period, 112) + '.zip c:\MyTable' + 
        convert(varchar(50), @period, 112) + '.dat'
    exec xp_cmdshell @cmd
    
    -- Delete the raw file
    set @cmd = 'del c:\MyTable' + convert(varchar(50), @period, 112) + '.dat'
    exec xp_cmdshell @cmd
    
    -- delete all the data that we just archived out from the table.
    delete from MyTable where createdate between @period and dateadd(d, 1, @period)
end

close datecursor
deallocate datecursor

So far this seems to be working fairly well.  Let me know if you see any issues with this or have any other tips. 

Enable XP_CMDSHELL in SQL Server 2005

I needed to use xp_cmdshell recently and discovered that it isn't on by default on SQL Server 2005.  I had to dig a bit to find out how to enable it.  The code is very simple.  Figured I would blog it here so I can easily find it next time.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Iterate Over a List of Dates using a Numbers table in SQL

Recently I was helping Steve archive off a bunch of data from one of his tables. Unfortunately he didn't have a ton of diskspace to use some of the techniques I normally would.  Steve recently posted a solution using powershell to do this.  I told him about an easy way to do this using a cursor and a numbers table and he told me to blog it.  This is an excellent use of a numbers table which if you don't have one of these in your database, you should create one. 

Here is a script to create a numbers table.

CREATE TABLE dbo.Numbers
(
    Number int IDENTITY(1, 1) PRIMARY KEY
)
GO

declare @MaxNumber int
set @MaxNumber = 65535
WHILE 1 = 1
BEGIN
    INSERT INTO dbo.Numbers DEFAULT VALUES
    
    IF scope_identity() = @MaxNumber 
    BEGIN
        BREAK
    END
END
GO

So basically Steve wanted to delete all data out of a table one day at a time for a date range and then shrink down the log to avoid .  The easiest way I could think of to do this is to create a cursor of all the dates in that date range and then loop through them.  Notice the join to the number table for all Numbers between 0 and the datediff of the start and end date.  Here is the code...

declare @startdate datetime
declare @enddate datetime
-- Declare the dates 
set @startdate = '01/01/2007'
set @enddate = '10/01/2007'

declare @period datetime

declare datecursor cursor forward_only 
    for Select dateadd(d,Number,@startdate) from Numbers
          where Number <= datediff(d, @startdate, @enddate)
          order by Number
open datecursor

while (1=1)
begin
  fetch next from datecursor into @period
  if @@fetch_status <> 0
    break;
  DELETE stats WHERE period = @Period

  BACKUP LOG stats WITH NO_LOG
  DBCC SHRINKFILE (stats_log, 2)        

end
close datecursor
deallocate datecursor

Here is a link to a good post on other uses of a numbers table.  As always feel free to comment on any improvements or ask any questions.

Remove Extra Spaces when Pasting from Outlook to Sql Server Management Studio

I seem to have this problem that when I paste sql code from my email into Sql Server Management Studio that it doubles all the line breaks and I have to go and remove them all.  Most of the time this isn't a big deal because the Sql is only a couple lines.  However if you get a ton of Sql emailed to you it can be quite annoying.  I finally found a way to quickly get rid of them.  Simply bring up the find and replace window and change the find to use Regular Expressions.  Then do a find on \n\n and replace that with \n. 

If anyone knows of a way to change a setting in Outlook to avoid this please let me know.

Retrieve File Contents using SQL Server 2005 SQL CLR

I recently had a need to load a bunch of pdf files into my database.  The problem was that the directory the files were in contained a bunch of other pdf files that I didn't want or that were no longer needed so I couldn't just upload every PDF in the directory.  I did have a database table that contained all the file names that were still valid.  I was wishing for a way to simply join from that table to the file system and load all those files.  I certainly could have just written a quick exe that did this by retrieving the list of file names from the database and then grabbing the file and uploading back into the database.  Instead I decided to write a CLR table value function that took in a directory path and a file search criteria and returned a table with the filename and the binary of the file.  This way I could simply do an insert into my table all from t-sql.  This ended up being a lot easier than what I thought and it worked quite well.  Below is the code for the CLR function.  Simply compile this code and then load it into your database.  This will create a function called FileReader.   You simply pass in a directory path and a search criteria (i.e. *.pdf).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;

public partial class FileReader
{
  [SqlFunction(FillRowMethodName = "FillRow", 
              TableDefinition = "FileName nvarchar(200),FileContent varbinary(max)", 
              Name = "FileReader")]
  public static IEnumerable InitMethod(String Directory, String FileCriteria)
  {

    DirectoryInfo d = new DirectoryInfo(Directory);
    return d.GetFiles(FileCriteria);
  }
  public static void FillRow(Object obj, out SqlString FileName, out SqlBytes FileContent)
  {
    FileInfo fi = (FileInfo)obj;
    FileContent = new SqlBytes(File.ReadAllBytes(fi.FullName));
    FileName = fi.Name; 
  }
}

Here is an example of how to use the function.

select * from dbo.FileReader('d:\', '*.txt')

and what the result was....

FileName             FileContent
-------------------- ----------------------------------------------------
Test.txt             0x6173646673646661736466...
Test2.txt           0x61736466736466617364660D0A0D0A6173626C61736661666C...

Here is some example t-sql to show how I used this to insert the files into a table. 

Create table _FilesToGrab (FileName varchar(50))
Create table _Files (FileName varchar(50), FileContent varbinary(max))

Insert into _FilesToGrab VALUES ('test.txt')

Insert Into _Files (FileName, FileContent)
select f.FileName, fc.FileContent
  from _FilesToGrab f
  Cross Apply dbo.FileReader('d:\', f.FileName) fc

select * from _Files 

Drop Table _FilesToGrab   
Drop Table _Files

So my final solution consisted of this function and then one insert into statement with a select. 

Hopefully this function comes in handy for someone else.  If any has any suggestions on how to improve on it please let me know and I will see what I can do. 

Move TempDB Sql Server 2005

I recently needed to move the TempDB on my Sql Server so I looked in Books Online and my initial thought based on what it said was that there is no way that will work.  Basically it says to find where the files are, and then move them to the new location followed by an alter statement to point the database at the new location.  Well of course this didn't work because the tempdb files are in use.  After thinking about it I just decided to try to alter the master database and point it at where I wanted the tempdb files to be and then just restart since Sql Server creates the tempdb again when you restart it.  Sure enough it worked.  Here are the steps I took.

1.  Find out where the TempDB files are (not that you really need to know for anything since you can't move them without stopping SQL Server).

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

2. Alter the master database and point the tempdb to the new location.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SqlServer\Data\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'D:\SqlServer\Data\templog.ldf');
GO

3. Restart SQL Server

Upon restarting you will see new TempDB files created.  Alternatively I suppose you could do steps, 1 and 2 above and then stop SQL Server, move the TempDB files to the new location, and then start SQL Server.  I suppose that would be slightly better since then you wouldn't have to wait for the TempDB files to auto-grow which can be a performance hit.  Either way if you try to follow books online you won't have any luck.

Recover Suspect SQL Server Database

Where I work we have about 160 instances of SQL Server Express running on laptops and as you can imagine these database instances tend to become corrupted from time to time.  One of the most frequent issues is that a database becomes suspect.  Obviously your first choice should be to restore a backup and the transaction logs since the last backup.  However if you don't have this (as I never do on my SQL Server Express instances) this is usually pretty simple to take care of by putting the database into single user mode and then emergency mode and then running a dbcc checkdb.  Here is the T-SQL you should use.  NOTE: You should be in the master database when you run this.

1) Alter database mydb set Single_User

2) Alter database mydb set Emergency

3) DBCC CheckDB ('mydb') -- This will tell you the Repair level to use

4) DBCC CheckDB ('mydb', { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD })

5) Alter database mydb set Multi_User

After a successful DBCC CheckDB the database is back in a consistent state and it's status is now online.  For more information on DBCC CheckDB go here.

I normally run the REPAIR_ALLOW_DATA_LOSS as the users running the express instance generally don't have much critical data so if we lose some data it isn't that much of an issue.  NOTE: Running DBCC CheckDB can take a long time to run and you may want to consider running it with the "noindex" option. 

Let me know if you have any other strategies for recovering databases in suspect mode.

SQL Server Management Studio Query Shortcuts

I have always hated typing and using the mouse so I always try to find shortcuts and hotkeys for every development environment I am in.  Having said that I thought that I would share what I have custom setup for SQL Server Management Studio.  Until I started playing around with them I really didn't realize what all was possible.  Basically you just highlight some text in query analyzer and then hit the hotkey and it passes the highlighted text into whatever the code is behdind the hotkey.  So here is what I use currently

ALT+ F1 = sp_help - This is useful to highlight a  table or proc and then it will return all the input parameters or columns on the table

CTRL+ F1 = sp_helptext - This will give the compiled version of a proc or function and is much easier than finding it in object explorer.  I usually do a ctrl - t before running this to put the results in text mode.

CTRL+ 1 = sp_who2 - This returns all spids currently active

CTRL+ 2 = sp_block_info - This will show all blocks on the database and is helpful if you are currently experiencing blocking as it shows the statement being blocked and the blocking statement.  This is a custom proc taken from the Microsoft SQL Server Development Customer Advisory Team Blog.  Here is the code for it.  NOTE: this only works on databases running SQL Server 2005 as it uses Dynamic Management Views.

CREATE proc sp_block_info
as
select getdate() as BlockDate,
      db_name(resource_database_id) as [database],
      t1.resource_associated_entity_id as [blk object],
      t1.resource_type as [lock type],
      t1.request_mode as [lock req],   --- lock requested
      t1.request_session_id as [waiter sid], --- spid of waiter
      t2.wait_duration_ms as [wait time],         
      (select text from sys.dm_exec_requests as r  --- get sql for waiter
        cross apply sys.dm_exec_sql_text(r.sql_handle) 
        where r.session_id = t1.request_session_id) as waiter_batch,
      (select substring(qt.text,r.statement_start_offset/2, 
        (case when r.statement_end_offset = -1 
        then len(convert(nvarchar(max), qt.text)) * 2 
        else r.statement_end_offset end - r.statement_start_offset)/2) 
        from sys.dm_exec_requests as r
        cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
          where r.session_id = t1.request_session_id) as waiter_stmt,    --- statement blocked
             t2.blocking_session_id as [blocker sid], -- spid of blocker 
        (select text from sys.sysprocesses as p --- get sql for blocker
          cross apply sys.dm_exec_sql_text(p.sql_handle) 
          where p.spid = t2.blocking_session_id) as blocker_stmt
      from 
      sys.dm_tran_locks as t1, 
      sys.dm_os_waiting_tasks as t2
      where 
      t1.lock_owner_address = t2.resource_address

CTRL+ 3 = sp_findtext - This is a custom proc I have that searches all procs, functions, views for whatever you have highlighted.  This is great for finding all the places a column is used or a table is referenced.  Here is the code for it.  Let me know if you have anything different that works better.

CREATE  procedure sp_FindText
@in_text varchar(8000)
as

-- exec sqldev..sp_findtext 'customer'

set @in_text = '%' + @in_text + '%'

select [Database], [Name], [Type]
from (
    select       'mydb' as [Database], o.Name, o.Type
    from        mydb..syscomments c (nolock)
    join         mydb..sysobjects o  (nolock) on o.id = c.id
    where     c.text like @in_text
    union
    select       'mydb2' as [Database], o.Name, o.Type
    from        mydb2..syscomments c (nolock)
    join         mydb2..sysobjects o  (nolock) on o.id = c.id
    where     c.text like @in_text
) x

order by [Database], name, Type

ctrl + 4 = select top 100 * from - This is one of my favorites....no more typing select * from mytable to see what the data looks like for a given table.  Simply highlight the tablename in the query window and hit ctrl + 4 and you will get the first 100 rows.  Handy to say the least!!

The rest of the ones I have setup do a select from specific tables.  For example say you have a customer table and you always need to lookup a customer by customer number when doing ad hoc queries in query analyzer.  You can setup a hotkey for something like this "select * from Customer where CustomerNumber = ".  Then simply type a customer number in query analyzer (say 123456), highlight it and hit the hotkey for it.  I have 4 or 5 hotkeys setup like this and it really saves a lot of typing and increases productivity quite a bit. 

Also here is a link to all of the editor hotkeys for SQL Server Management Studio. 

http://msdn2.microsoft.com/en-us/library/ms174205.aspx

Lastly, if anyone has any other hotkeys setup that are different than what I have, please post a comment.