Take the following sample code.    Four similar tables with an INT IDENTITY, BIGINT IDENTITY, and two with UNIQUEIDENTIFIERS, one using newid() and the other with newsequentialid(). Once the tables are created and we insert 100,000 rows into each, they are very similar, but they take up very different amounts of disk space. Using the Database Health Reports application, we …

Should I Use A Unique Identifier As A Primary Key? Read more »

Today on the tuning minute on the SQL Data Partners Podcast, we discussed duplicate indexes, which lead me to think more about and and write this post. You know there are many different ways of doing things in SQL Server, and often times you can argue that one way or the other is better, and given the right situation anything …

Duplicate Indexes, What a Waste Read more »

Database Corruption Challenge #9 has completed with 19 winning participants, you can take a look at the scoring page for exact details. The first to solve the challenge this week was Eduardo Rezende who provided the winning solution just 58 minutes after the challenge began. The second winning solution arrived less than a minute after the first, so it was a …

Database Corruption Challenge #9 Complete Read more »

The following are the steps that I went through to create a more difficult Database Corruption Challenge for Week 4. Here is how I built the database, nothing fancy, however I did add an extra FILEGROUP called [UserObjects]. Notice, that to make things slightly more challenging, the database is a Case Sensitive Accent Sensitive database. I hope this didn’t cause …

Week 4 – Building the Corrupt Database Read more »

After posting the winning solution for Corruption Challenge 1 from Brent Ozar, I realized that he and I both solved the corruption by using the REPAIR_ALLOW_DATA_LOSS option on CheckDb. A very nasty move, however it did repair the corruption.   After reading some feedback, one of the winners stated: As soon as he ran REPAIR_ALLOW_DATA_LOSS, I knew we weren’t on …

Corruption Challenge 1 – An alternative solution Read more »

Since the corruption challenge completed yesterday, I have had several request asking how I created the corrupt database. So here is the script that I used to create the Database Corruption Challenge 1. First the initial setup. Most of this I stole from a query training session that I did several weeks ago. All I really needed was a table …

Corruption Challenge 1 – how I corrupted the database Read more »

Being day seven of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKTABLE. Description: DBCC CheckTable is used to check the structure of a table to verify the integrity of every data page associated with that table, and all of the indexes associated with that table. If you have used DBCC CheckDB, and a problem has …

DBCC CheckTable Read more »

Being day six of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKIDENT. Description: DBCC CHECKIDENT is used for check on the current value in the identity column for a table.  It also reports on the largest value in that column. DBCC CheckIdent can also be used to update or set the next identity value on …

DBCC CheckIdent Read more »

Take the following sample code.    Four similar tables with an INT IDENTITY, BIGINT IDENTITY, and two with UNIQUEIDENTIFIERS, one using newid() and the other with newsequentialid(). Once the tables are created and we insert 100,000 rows into each, they are very similar, but they take up very different amounts of disk space. Using the Database Health Reports application, we …

Should I Use A Unique Identifier As A Primary Key? Read more »