Generate Columns for Update Statements

I’m not a fan of most CRUD generators. The formatting doesn’t match my style, and I usually spend about as much time modifying the generated code as I would spend just writing it from scratch. But there’s been times when I’ve considered using CRUD generators, mainly when I’m writing updates on wide tables. If you’ve never written an update for a table with many columns, it’s not sexy. You’re wasting valuable time on a tedious task that you could instead spend reading SQL Server 2008 Internals or chewing the cud with the SQL Twitterati. Fortunately, Dave Carlile shared another tip with me that helps with this and has made it’s way into my little bag of tricks. Let’s assume you having the following outline: Update sales Set [’insert really long column list’] From Sales.vStoreWithDemographics As sales Join myTempTable As mtt On sales.someColumn = mtt.someColumn;Update sales Set [‘insert really long column list’] From Sales.vStoreWithDemographics As sales Join myTempTable As mtt On sales.someColumn = mtt.someColumn; You could use the following code to generate a list of columns for you: Select name + ‘ = sales.’ + name + ‘,’ From sys.columns Where object_id = object_id(’Sales.vStoreWithDemographics’) Order by column_id;Select name + ‘ = … Continue reading

Iowa Code Camp – Call for Speakers

This is a little late, but I want to mention that the Iowa Code Camp has put out a call for speakers. I’ve spoken with Greg, and he told me they’re still looking for 2 or 3 more SQL-related sessions. If you have a topic you’d like to speak on, please send him an e-mail! I’ll also be speaking at this year’s Code Camp on May 2nd. Here’s a brief overview of my planned topic: SQL Server for the .NET Developer Most .NET developers will inevitably work with SQL Server in some form or another. Many will even be asked to create tables and stored procedures to support their applications. This session will focus on what every .NET developer should understand about SQL Server. We’ll cover the basics, such as good table design and efficient queries, and we’ll touch on some indexing concepts, too. ألعاب لربح المال الحقيقي 2024 في مصر Then we’ll wrap up with some strategies for developing high volume SQL Server applications. جدول اليورو 2024 Lately I’ve been trying to work on my speaking skills. So if you’re within a 4-5 hour radius of east Iowa (Chicago, Milwaukee, Minneapolis, etc. روما الإيطالي ) and would like me … Continue reading

Forget SQL Server, Excel is the Answer

I had the strangest dream last night. It started with me being chased around the Microsoft campus in Redmond (which I’ve never been to) by Godfather-esque midgets with machine guns. Yeah, I know… I don’t know what to make of it, either. But when I woke, it hit me like birdshot from Dick Cheney’s hunting rifle: Forget SQL Server, Excel’s the answer. The more I thought about it, the more sense it made. I mean, really, what can SQL Server do that Excel cannot? Store Large Amounts of Data Sure, SQL Server may be able to hold billions upon billions of rows of data, but who really stores that much data? And even if you ARE storing that much data, then chances are you aren’t really *using* all of it. Scale down, and use Excel. Did you know Excel 2007 can now hold 1 million rows per worksheet? It’s true. And if you have 2 million rows to store, you can always just create another worksheet. Manage Relational Objects Pfft, have you ever tried to get data out of a normalized table? You have to do all of these annoying joins. But now that Excel supports up to 16,000 columns, … Continue reading

Registered Servers in SSMS

In my last blog post, I discussed changing the color of the status bar in SSMS 2008. I received a couple of comments and even an e-mail discussing how this doesn’t seem to always work. افلام السيارات After playing with it for a little bit, I’ve found that the status bar color needs to be set in both Query->Connection->Connect/Change Connection… (here-in referred to as simply the Query menu) and Registered Servers. Let’s run through this. First, connect to an instance with any color using the Query menu. Now, create a new registered server. Make sure to use the same server. إحالة صديق Pick a color, but make sure that it’s different than the previous color. This is just for demonstration purposes only. Since the whole point is to have a consistent color, you would normally use the same color in both connection methods for the same server. Open a new query window via Registered Servers. Here’s what happens when I connect to the same server using both Registered Servers (left) and another window using the Query menu (right). For anyone who’s using both the Query menu and Registered Servers to connect to servers, then you should walk through the process … Continue reading

SSMS Server Settings

I think this has been discussed before on better blogs than mine, but it’s just so darn cool that I want to help spread the word. In SSMS 2008, you can change the color of the status bar for servers. This gives you a nice visual reminder as to which server you’re currently connecting to. Since I’ve made the DEV/PROD mistake before, this is something I’m a big fan of. So let’s walk through how you can set this up: Click on Options >> Select Use custom color and click on Select… Choose the color you prefer, then click on OK Click on Connect. That’s all there is to it. Pretty easy, huh? Now let’s see what happens when we connect to multiple servers… Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once. Source:

Automated Index Defrag Script

UPDATE: This script has been significantly updated. Please view the latest version at So after much delay, here’s the latest and greatest version of my index defrag script. A summary of the changes: Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases. Consolidated Enterprise and Standard versions of the script with new @editionCheck variable. Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server. Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations. Limited defrags to objects with more than 8 pages. Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting. Updated logic for handling partitions and LOBs. And a couple of notes and explanations: Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID? Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead. How long will this take? It depends. I don’t necessarily recommend running it without specifying a database; … Continue reading

Index Fragmentation PotW Webcast – Now Available

The Pain-of-the-Week webcast I did last Thursday is now online! You can find it here: The topic of the webcast is index fragmentation: what is it, how to identify it, and how to fix it. You can find the materials for this webcast in my previous blog post. I’ll also be updating SQLServerPedia with the materials, hopefully tonight. Thanks to the great feedback I’ve received, I’ll be doing another Pain-of-the-Week webcast, this time with Brent Ozar. More info to follow as we hammer out the specifics. In a related note, I have updated my index defrag script. I’m just waiting for my awesome beta testers to let me know everything works for them. It works just fine on my servers (lol) but I don’t have access to any Standard editions, etc., so I just want to be extra sure before I unleash it on the SQL world. I should have it online tomorrow. Source:

PoTW Webcast Materials

For those interested, here’s the links I mentioned during my presentation: Index Defrag Script for 2005 / 2008 Viewing Page Data SQLServerPedia Capacity Manager … and here’s the scripts that I used during my presentation: Use sandbox; Go   /* Create a table to promote fragmentation */ Create Table dbo.fragmented ( myGuid uniqueidentifier Not Null   Constraint PK_fragmented Primary Key Clustered (myGuid) );     /* Populate our table */ Set NoCount On; Declare @iterations int = 0;   While @iterations < 250000 Begin   Insert Into dbo.fragmented Select NewID();   Set @iterations += 1;   End;     /* Verify the number of records in our table */ Execute sp_spaceused ‘dbo.fragmented’;     /* Let’s take a look at sys.dm_db_index_physical_stats */ Select Object_Name(ddips.object_id) As ‘tableName’ , As indexName , ddips.* From sys.dm_db_index_physical_stats (DB_ID(), Object_ID(N’fragmented’), NULL, NULL , ‘DETAILED’) As ddips Join sys.indexes As i On ddips.object_id = i.object_id And ddips.index_id = i.index_id;     Select * From sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’fragmented’), NULL, NULL , ‘LIMITED’);     /* ShowContig should return us the same information */ DBCC ShowContig (’fragmented’);     /* ExtentInfo is an undocumented command that will show us the number of allocated extents */ DBCC ExtentInfo … Continue reading

Easy Way To Return Top Records

Okay, so that title may suck. I accept that. It’s late and I can’t think of anything better at the moment. 🙂 Bad blog title aside, let’s take a pretty common data request. You need to return the top sales performer in each department. If you’ve ever had this type of request, then you know there’s a few different ways of handling this, and it can be a little complicated. Today, Dave Carlile shared with me a new and pretty simple way of handling this with Row_Number(). The syntax for Row_Number is a little different than what you may be used to: ROW_NUMBER ( ) OVER ( [ PARTITION BY yourColumn ] ORDER BY yourColumn ) PARTITION BY is what you want to group by. This is optional. ORDER BY is how you want to order your data before assigning a row number. This is required. Let’s take a look at an example. /* Create a table to play with */ Create Table dbo.sales ( order_id int Identity(1,1) , salesPerson varchar(20) , department varchar(20) , total money   Constraint PK_sales Primary Key Clustered(order_id) );   /* Load it up with some bogus records */ Insert Into dbo.sales Select ‘Amanda’, ‘Sales’, … Continue reading

380PASS Meeting – Success!

Our first meeting was a success by all accounts! We had a great turnout with 30 people showing up. As an ice breaker activity, we had folks pair up and try to identify something related to SQL Server, i.e. commands or terms, for every letter of the alphabet. There was a 5 minute time limit, and with only 20 seconds to spare, Randy Vandergaast and Dave Brosseau turned their sheet in to each win a $25 gift card to Barnes & Noble. Chris Leonard gave the group a great overview of PASS and the PASS Summit in Seattle. Brian Duhn then gave a comprehensive overview of how to set up Server-to-Server Service Broker. The entire slide show can be found at, under Resources. Thanks to our awesome sponsors, we had some great prizes to give away, and everyone walked away with something. Thanks to everyone who attended for helping us have a great launch meeting! 🙂 Michelle