SQLServerPedia

Any IT pro with more than a year or two of experience will have faced the challenges of version control for an application, but what if you have to implement version control for data?

The most common way to tackle this problem is implementing something called “bitemporal data”. Under this method, each row in a table includes the current valid time and the transaction time. Since two distinct time values are stored, we get the term bitemporal.

A great place to start is Adam Machanic’s excellent article at http://www.simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/. In addition, I encourage you to check out Adam’s book “Expert SQL Server 2005 Development” at http://www.apress.com/book/view/9781590597293.

In addition, Dejan Sarka pointed out that you can get a very comprehensive information on supporting temporal data from a book entitled “Developing Time-Oriented Database Applications in SQL” by Richard T. Snodgrass. Mr. Snodgrass was so kind to publish the book in PDF format and make it available for free download at http://www.cs.arizona.edu/~rts/publications.html. (Note that the book is quite old now (year 1999), so the T-SQL code does not include any SQL Server 2005 or 2008 enhancements. But the basic SQL is still solid.

Cheers!

-Kevin

kekline @ twitter

P.S. Check out my new site – http://kevinekline.com

One of the fundamentals of loop optimization is that you should move stable operations outside of the loop. What I want to know is – if this is such a fundamental rule, why do so many people break it?!?

If you are familiar with other programming languages, then you are probably aware of loop optimization techniques. You should try to put all operations outside of the loop if they don’t need to change within the loop. This reduces the amount of unnecessary repetitive work. SQL Server optimizer doesn’t automatically recognize such inefficiencies and clean the code for you (compilers of some other languages do). You have to write efficient loops yourself as in the following example.

These scripts print a table of square roots for all numbers from 1 to 100. (My apologies but Wordpress won’t seem to keep my indents in place in the code.) Notice the boldfaced code below:

-- Loop with code inside = inefficient

SET NOCOUNT ON

DECLARE @message VARCHAR(25),  @counter SMALLINT

SELECT  @counter = 0

WHILE @counter < 100

BEGIN

SET @counter = @counter + 1

SET @message = REPLICATE( '-', 25 )

PRINT  @message

SET @message = str( @counter, 10 ) + str( SQRT( CONVERT( FLOAT, @counter ) ), 10, 4 )

PRINT  @message

END

-- Elapsed time: 376 ms

Compare the above Transact-SQL script to the one below, where the boldfaced code is moved outside of the loop:

-- Loop with code outside = efficient

SET NOCOUNT ON

DECLARE @separator VARCHAR(25), @message   VARCHAR(25), @counter   SMALLINT

SELECT  @counter = 0, @separator = REPLICATE( '-', 25 )

WHILE @counter < 100

BEGIN

SET @counter = @counter + 1

PRINT  @separator

SET @message = Str( @counter, 10 ) + Str( SQRT( CONVERT( FLOAT, @counter ) ), 10, 4 )

PRINT  @message

END

-- Elapsed time: 36 ms

The second script executes the REPLICATE( ‘-‘, 25 ) function only once, compared to 100 times in the first script. Results produced by both scripts are identical:

-------------------------

1    1.0000

-------------------------

2    1.4142

-------------------------

3    1.7321

-------------------------

4    2.0000

. . .

. . .

. . .

-------------------------

99    9.9499

-------------------------

100   10.0000

Of course, there are a million and one ways to perform any such algorithm. But I’m still surprised that otherwise experienced and competent database programmers are still embedding very stable elements of their code inside of extensive looping operations rather than outside of them. Thoughts?

Cheers,

-Kev

I like to collect useful database administration queries that leverage the SQL Server 2005 and 2008 DMVs.  Heck, I’m still interested in SQL Server 2000 queries too.  I thought I’d make my search public so that a) you can share your favorite queries here or great reference queries written by others and publicly posted on the Internet, and b) everyone can benefit from this collaborative approach to DMV queries.  If you’re aware of collections of scripts, for example like those available from the SQLCAT team, please post the location of the collections or libraries.

The intent is to provide ourselves with a set of scripts they can use to perform tasks that would otherwise require them to hit BOL heavily to research what DMVs or system catalog views they need to access to get what they want. These types of activities are not easily performed from within the query tool user interface.

I’m requesting everyone to post or reference your favorite queries in any of the following categories below. The queries could be in your notes, from web sites like MSDN or TechNet or SQLServerPedia.com, from our great SQL Server bloggers and MVPs.  (Be sure to give credit to the originator when you post it here.)  Speaking of favorite scripts, you might want to check out SQLServerPedia.com, if you haven’t already done so.  The wiki is getting quite large and there’s lots of new information popping up daily.  Looking for more good query samples?  If you didn’t already know it, be sure to check the Samples folder in your SQL Server installation.  Microsoft has a lot of examples in their SQL Server 2005 Script Library.

When posting, please:

·         Describe briefly what the snippet does

·         Describe if this is a 2005/2008 query or just 2000

·         Provide the SQL / Script and indicate if there are any parameters or if the SQL can be run without modification

Here are some categories I’m looking for, but if you have something not addressed here, please post it:

·         Object Sizes – a list of objects in a database with their sizes

·         Missing Indexes

·         Index Utilization – all indexes

·         Index Utilization – on a specific table

·         Index Fragmentation – all indexes

·         Index Fragmentation – on a specific table

·         Index Defrag options – various with defrag, rebuild, online, offline, heap, etc.

·         SQL Performance – leveraging the 2005+ DMVs for worst performers, active statements

·         CPU and Optimization

·         Buffer Cache

·         Wait Stats

·         Deadlocks

·         Plan Guide Queries

·         SQL Trace

·         Backup History – or other backup related queries

Thanks in advance!

-Kevin

Larry wrote in and asked:

“Is there a TSQL command sequence that will allow SQL DELETE statements to be issued without triggering any log growth? Assume I can change the recovery model.”

Well, sort of – if you want to delete records without triggering as much log growth, there’s a different command to use instead:

TRUNCATE TABLE dbo.MyTableName

The truncate table command will empty out your table faster than you can say, “Whoops!”  Of course, that’s dangerous, but it comes in handy for things like data warehouse nightly ETL processes where you need to empty out huge work tables as quickly as possible.

The bad news: truncate table doesn’t accept a where clause.  It simply deletes all of the records in the table without logging each individual delete operation.  It’s quick, but it’s dangerous.

I had an interesting debate with a customer during a demo where I said that his assertion, “I don’t need to specify ORDER BY, I have a clustered index on that table” was problematic. You see, defining a clustered index does tell SQL Server to store data in the order stipulated by the clustering key, and many times running SELECTs against that data will return the data ordered as expected. But there are some gotchas you should keep in mind:

• When SQL Server runs a query in parallel, each stream will complete it’s set of work and return its result which could cause your clustered-index-ordered data to appear out of order.
• When another query is already scanning the data when your SELECT statement is run, the data will be read out of the order you’re expecting as your query will piggy-back on the other scan, then come back to the beginning to gather all the data requested.
• If statistics are out-of-date on your clustered index, or SQL Server otherwise chooses to use a non-clustered index to scan the data, the order of the clustered index will not be reflected in the result set.

But don’t take my word for it, check out what Conor Cunningham has to say on the topic, including some sample code to prove the point.

The full original question is:  How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?

First, thanks for the question submission!  You can detach the database, move the file(s), and reattach using the CREATE DATABASE… FOR ATTACH command.

This is a SQL Server 2005/2008 example. For SQL Server 2000, you would use sp_attach_db/sp_attach_single_file_db to perform the attach.

For example:

Detach the database during a maintenance window – make sure you have a backup just in case something goes wrong:

USE master;
GO
EXEC sp_detach_db <db_name>;
GO

Now move the database file to the D: drive and reattach:

– Execute CREATE DATABASE FOR ATTACH statement
CREATE DATABASE
ON (FILENAME = ‘D:\\’) FOR ATTACH;
GO

Keep the questions coming!  We get a large volume of submissions and love seeing what the community is thinking about. 

David

The following question was posed to me during a call today and its one that I’ve heard quite a bit so I figured it warranted a blog post.

The answer is, absolutely!  SELECT statements acquire a shared lock on the tables being accessed.  This shared lock will not affect other SELECT statements hitting the same table, but if someone tries to modify data in the table (via an UPDATE statement for example), the UPDATE statement will be blocked.  When analyzing locks using sp_lock the (S) symbol indicates a shared lock is on an object.

This default behavior can be modified by using one of many query hints.  Using the NOLOCK hint on a SELECT statement will force SQL Server to read data from the table without creating a shared lock on it.  When using this hint, you run the risk of reading uncommited data from the database but in cases where reading data with 100% accuracy is not required, NOLOCK can dramatically reduce blocking and improve the performance of your SELECT queries.  Another option is to use the READ UNCOMMITED isolation level when running your transactions, which conceptually does the same thing.

One additional caveat about both methods.  If your SELECT statement is running, and it expects to read a page that has been deleted by a transaction that is currently executing, SQL Server may deadlock this transaction.  There is an interresting post on this topic that can be found here.

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

Absolutely. You can use the sys.objects view to determine the create_date and modify_date for an object. The query would be:


select [name],[create_date],[modify_date]
from [sys].[objects]
where [type]=N'P' and [is_ms_shipped]=0


To see the full definition of the procedure you would run:


select [name],[create_date],[modify_date],[definition]
from [sys].[objects] obj
join [sys].[sql_modules] mod
on obj.[object_id]=mod.[object_id]
where [type]=N'P' and [is_ms_shipped]=0


Yes, this is bad because it can lead to your log shipped database getting out of sync and will probably result in Error 4305; error 4305 states that the log in a backup set is too late to apply, which means your log shipping jobs will fail until you can synchronize the databases. You should schedule your transaction log backup job to stop before your full backup job begins and then to restart once it has completed. See the Microsoft TechNet article on setting up Log Shipping.

When you perform a a full database backup SQL Server stores the ending log sequence number (LSN), which becomes the starting LSN for the next transaction log backup. So, if you are performing a transaction log backup while your full database backup is executing, and that transaction log backup is successful, once it is shipped and applied to the subscriber it will have a different starting LSN than the ending LSN of the full backup. This is not allowed and SQL Server will throw error 4305.