-
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(...
-
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...
-
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...
-
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...
-
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...
-
I think that there's a tendency among DBAs and SQL Server wonks to be a bit dismissive of Full Text Indexing. I think that in a lot of cases people tend to think of it in terms of adding significant overhead. For a long time I actually looked at it as...
-
I'm with Stephen Wynkoop on this one - I'm not sure I'm too hip on Microsoft's emerging direction to divide SQL Server Tools into Developer and Admin Roles. Stephen just posted an editorial based on an interview that he had with Matt Nunn, and points...
-
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...
-
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...
-
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...
-
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...
-
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...
-
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....
-
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...
-
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...
-
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...
-
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...
-
I have found the SQL Server 2005 templates to be extremely useful. I have created custom templates for creating a new stored procedure, scalar functions, table-value functions and views. The benefit of these is that it creates a standard for creating...
-
This is a template that I have been using in SQL server for when I do have to write cursors. Notice that there is only one fetch statement in there which is much slicker than the typical method of initially doing a fetch, with a while condition of while...
-
Since I can never remember this off the top of my head I figured I would post on my blog. To enable the use of CLR on SQL Server run the following.... EXEC sp_configure ' show advanced options ' , ' 1 ' ; go reconfigure ; go EXEC sp_configure ' clr enabled...
-
This is probably the best explanation around on the different dynamic Sql approaches and the performance and benefits of each of them. If you are using Sql Server 2005 basically you should be using sp_executesql due to how it can cache the query plan...
-
One of the most important maintenance tasks for a database is making sure you have the right indexes. Sql Server 2005 makes this a lot easier than in the past by the creation of the dynamic management views. I recently went on a cleaning spree in our...
-
If you do a lot of scripting, SP2 for SS 2005 is going to be a must-have. Luckily it was just released . Here are some of the BIG improvements for people who like to script a lot: Generate Script Wizard . You can now specify that the scripted objects...
-
One of my clients recently had a problem where a query with a semi-measly execution cost of .3 was taking 25 seconds to return. Worse, forcing the query to use a suitable index (instead of the PK with accompanying bookmarks) had a cost of 4.8 and returned...
-
Just an FYI that my latest product review for SQL Server Magazine is in the February Edition. (My February edition only just BARELY showed up in the mail...). In this review I take a look at Quest Software's Spotlight on SQL Server 5.7 . It's a decent...