SQL Server 2008

How to insert records to a table with an identity column

If you have a table with an identity column, you can’t just do a simple insert.  That identity column needs to be excluded from your insert statement. There are times when you need to keep the ID values during your insert.  Like moving data between environments. Here is the SQL you need to allow your inserts to work. First turn on identity_insert SET IDENTITY_INSERT <TABLENAME> ON; Then do your insert. Then turn off identity_insert SET IDENTITY_INSERT <TABLENAME> OFF;

Undocumented stored procedure for looping through all databases on a server

If need to do something to all your databases there is a stored proc for you.  Its called sp_msforeachdb. This will loop though all your databases and perform a command for you.  It’s similar to the foreach table sp I wrote about here. There is one parameter @command. Let’s get right to an example. EXECUTE sp_msforeachdb 'USE ? IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'') BACKUP DATABASE ? TO DISK = ''G:?.bak, WITH INIT''' The ? gets replaced with the database name. So that...

Simple SQL best practices

It’s real easy to go crazy with best practices and then have them become so big no one follows them. Here are a just a couple of quick best practices I follow. 1.Whenever you need to recreate a database object use DROP/ CREATE rather than ALTER.  The reason is that ALTER doesn’t modify the object’s create date time stamp. This makes it harder to trouble shoot when you can’t figure out what has changed recently. ...

Undocumented stored procedure for looping through all tables in a database

I got tired of writing code to loop through all tables in a database.  I found out about an undocumented stored procedure that does most of that for me. The stored procedure name is sp_MSforeachtable it takes one input parameter (@command). This simplifies so much code (no more looping through sysobjects). Here are some examples of how to use it. This command will return a row count for each table in your database. ...

SQL Server 2008, can’t save changes to tables

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes. You will get an error stating, “You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.” What a pesky problem.  It prevents you from making progress when you making database changes. It’s a good thing you can turn it off. This...

What's new in SQL 2008

I talk about SQL server every chance I get because of some the great features available (SSRS, SSIS, SSAS).  So I looked into SQL 2008 the first chance I got.  More than once I have been asked about the new features of SQL server 2008.  So here is a list I complied (not 100% of the features, just the ones I thought they were easy to sell) Top new features: T-SQL Intellisense – Intellisense is now available for T-SQL. This allows for easier and quicker development. Date/Time data types – There is a new...

SQL Server 2008, can't save changes to tables

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes. You will get an error stating, “You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.” What a pesky problem.  It prevents you from making progress when you making database changes. It’s a good thing you can turn it off. In Management Studio, go to Tools –> Options –> Designers...

SQL Server 2008 sp1 released

So SP1 for SQL 2008 has been released by Microsoft.  You can download it here.  There is not much info about what is included.  It appears to be a cumulative update.  You can slipstream it with SQL server install image.  You can uninstall the service pack separate from the DB engine.

SQL Server 2008 intellisense not correct

Sometimes when you writing TSQL in sever 2008 you will get the red squiggles (indicating you typed a table or field name incorrectly).    Yet everything will compile and run correctly, so what’s the deal. To fix this problem you just need to press ctrl+shift+r, which will rebuild the sql cache responsible for intellisense.’

Track Changes to objects in your database

Ever have the need to be able to track changes to the objects in your database? This code sample was provided by a friend, Josh Shilling. It provides you with a change log on your database. This will be helpful in multi developer environments when you think someone made a change to your database, but you don’t know what changes were made. So you can run Select * from dbversion.ChangeLog to see what changes have been to your db objects. I had a hard time getting the image of the results to show. GO /****** Object: Table [DBVersion].[ChangeLog] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING...

Full SQL Server 2008 Archive