Poor (Wo)Man’s Graph
Lary shared this poor (wo)man’s graph with me today, and I thought it was pretty awesome:
SELECT OrderDate , COUNT(*) AS 'orders' , REPLICATE('=', COUNT(*)) AS 'orderGraph' , SUM(TotalDue) AS 'revenue' , REPLICATE('$', SUM(TotalDue)/1000) AS 'revenueGraph' FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate Between '2003-07-15' And '2003-07-31' GROUP BY OrderDate ORDER BY OrderDate;
This will return a simple but effective “graph” for you:
orderDate orders orderGraph revenue revenueGraph ---------- ------ ------------------------------ -------- ---------------------------------------- 2003-07-15 19 =================== 34025.24 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-16 14 ============== 26687.65 $$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-17 16 ================ 32411.93 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-18 9 ========= 18634.91 $$$$$$$$$$$$$$$$$$$ 2003-07-19 13 ============= 19603.23 $$$$$$$$$$$$$$$$$$$$ 2003-07-20 24 ======================== 47522.80 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-21 9 ========= 11781.62 $$$$$$$$$$$$ 2003-07-22 17 ================= 32322.50 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-23 15 =============== 30906.44 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-24 28 ============================ 51107.90 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-25 15 =============== 27058.10 $$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-26 18 ================== 41076.49 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-27 15 =============== 22169.88 $$$$$$$$$$$$$$$$$$$$$$ 2003-07-28 16 ================ 23945.80 $$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-29 25 ========================= 51122.95 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-30 12 ============ 23476.44 $$$$$$$$$$$$$$$$$$$$$$$ 2003-07-31 18 ================== 36266.76 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Who needs Reporting Services when you’ve got REPLICATE? 
Random Number Generator in T-SQL
Filed under: Performance & Tuning, SQL Tips, Syndication
Ever need to generate a random number in T-SQL? I have, on a couple of different occasions. I’m pretty sure that there’s several different ways of doing this in T-SQL, but here’s what I use:
DECLARE @maxRandomValue TINYINT = 100 , @minRandomValue TINYINT = 0; SELECT CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND() + @minRandomValue AS TINYINT) AS 'randomNumber';
This approach uses the RAND() function to generate a random seed; it also ensures that the value returned is between the specified min and max value. I’ve been using this method in one stored procedure that’s called a couple of hundred times per second, and it seems to perform pretty well.
What method do YOU use to generate a random number? Is it faster than this method?
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;
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;
Just replace [Sales.vStoreWithDemographics] with a table of your choice, and replace “sales.” with the appropriate alias.This will return a list of nicely formatted columns for you. Best of all, no potential for column typos! Just don’t forget to remove the very last comma, otherwise you’ll get a syntax error.
CustomerID = sales.CustomerID, Name = sales.Name, ContactType = sales.ContactType, (etc.)
I know, nothing earth shattering, but definitely one of those “huh, why didn’t I think of that?” moments. So, thanks, Dave! 
Source: http://sqlfool.com/2009/03/generate-columns-for-update-statements
Registered Servers in SSMS
Filed under: Miscellaneous, SQL 2008, SQL Tips, Syndication
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.
New Server Registration
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.
Pick a color
Open a new query window via Registered Servers.
New Query Window
Registered Server Query Window
Here’s what happens when I connect to the same server using both Registered Servers (left) and another window using the Query menu (right).
SSMS - Same Server, Different Colors
For anyone who’s using both the Query menu and Registered Servers to connect to servers, then you should walk through the process of connecting to each server via both means and changing the colors to ensure consistency. I did this for 22 servers and it took me less than 10 minutes.
I hope that helps clear up some of the confusion.
Source: http://sqlfool.com/2009/03/registered-servers-in-ssms/
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:
Opening a new connection
Click on Options >>
Connection Properties
Select Use custom color and click on Select…
Pick a color
Choose the color you prefer, then click on OK
Open a new query
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…
Multi-Server Rainbow
Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once.
Source: http://sqlfool.com/2009/03/ssms-server-settings/
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', 420 UNION All SELECT 'Barry', 'Sales', 360 UNION All SELECT 'Chris', 'Marketing', 398 UNION All SELECT 'David', 'Sales', 371 UNION All SELECT 'Ethan', 'Customer Support', 123 UNION All SELECT 'Faith', 'Sales', 206 UNION All SELECT 'Gavin', 'Marketing', 396 UNION All SELECT 'Heather', 'Marketing', 51 UNION All SELECT 'Iris', 'Customer Support', 79 UNION All SELECT 'Jamie', 'Customer Support', 242; /* Examine what values are returned for each record */ SELECT ROW_NUMBER() OVER(Partition BY department ORDER BY total DESC) AS 'salesRank' , salesPerson , department , total FROM dbo.sales; /* Let's grab just the top sales performer in each department */ WITH myCTE AS ( SELECT ROW_NUMBER() OVER(Partition BY department ORDER BY total DESC) AS 'salesRank' , salesPerson , department , total FROM dbo.sales ) SELECT salesPerson , department , total FROM myCTE WHERE salesRank = 1 ORDER BY total DESC;
Let’s take a look at the options we’ve specified for Row_Number(). Since we want to know who has the top sales, we’re going to order by [total] in descending order. We also want to assign each department its own rank, so we’re going to group (partition) by the [department] column. If we did not include the “Partition By” clause, then we’d get only 1 record returned, which would be the top overall sales person (in this case, Amanda).
Now let’s do the same thing, but this time we want to return the top 2 sales person in each department.
/* Now grab the top TWO sales performer in each department */ WITH myCTE AS ( SELECT ROW_NUMBER() OVER(Partition BY department ORDER BY total DESC) AS 'salesRank' , salesPerson , department , total FROM dbo.sales ) SELECT salesPerson , department , total FROM myCTE WHERE salesRank <= 2 -- this is the only difference ORDER BY department , salesRank; /* Clean-Up! */ DROP TABLE dbo.sales;
That’s all there is to it! Pretty cool, huh? I haven’t gotten around to performance testing on large data sets yet, but I definitely like the simplicity of the approach.
Thanks, Dave!
Update: Aaron The Hobt has already done some performance testing on this very subject. The results? Not as good as I was hoping. 
As an aside, I’m going to be participating in the Pain of the Week webcast tomorrow at 11 AM ET. This free webcast will be on index fragmentation: what is it, how to find it, and how to fix it. If you’re interested, you can register here: http://www.quest.com/events/ListDetails.aspx?ContentID=8857.
This will be only my second time speaking to an audience (the first time was yesterday at our first PASS Chapter meeting!). So if nothing else, it may be good for a few laughs.
Source: http://sqlfool.com/2009/03/easy-way-to-return-top-records/
SQL Tweaks and Tools That Make My Life Easier
It still surprises me how many people don’t know about some of the very things that make my job so much easier. So this next post is dedicated to sharing some of the tweaks and tools I’ve run across that will help anyone who works with SQL:
Indexes
Anyone who uses included columns is probably well aware of the frustrations that can come from having to look up information on which columns are included. I wrote a stored procedure, dba_indexLookup_sp, to help me with this, before discovering sp_helpindex2. If you haven’t heard of sp_helpindex2, it’s a re-write of sp_helpindex by Kimberly Tripp. You can find it on Kimberly’s blog. The main difference is Kimberly’s is a system stored procedure (mine is not) and my version returns partitioning information (Kimberly’s does not). Check both out and use whichever one meets your needs best.
KeyBoard ShortCuts
In SQL Server Management Studio (SSMS), click on:
Tools –> Options… –> Environment –> Keyboard
Keyboard Shortcuts
For your copying convenience:
Ctrl+3 Select Top 100 * From
Ctrl+4 sp_tables @table_owner = ‘dbo’
Ctrl+5 sp_columns
Ctrl+6 sp_stored_procedures @sp_owner = ‘dbo’
Ctrl+7 sp_spaceused
Ctrl+8 sp_helptext
Ctrl+9 dba_indexLookup_sp or sp_helpindex2
Please note that these settings will not take effect until you open a new query window. Here’s an example of how you could use this: use Ctrl+4 to find a list of tables, then copy one into your query window; to view a sample of that table’s data, highlight the table name (I usually double-click on it) and press Ctrl+3. It’s a thing of beauty. Oh, and you may want to remove/change the schema filters if you use schemas other than dbo.
Query Execution Settings
After having one too many issues arise from non-DBA’s connecting to the production environment to run a devastating ad hoc, I’ve had all of our developers and analysts adopt the following settings. The only thing difference between my setting and theirs is that I have “Set Statistics IO” selected. FYI – you can also make these same setting changes in Visual Studio.
In SQL Server Management Studio (SSMS), click on:
Tools –> Options… –> Query Execution –> SQL Server –> Advanced
Query Execution Settings
Copy Behavior
This next tip actually has nothing to do with SQL Server, and can be done with any Microsoft product. However, I just learned about it a few weeks ago and already I use it quite frequently.
Holding down “Alt” while you drag your mouse will change your selection behavior to block selection.
Block Selection
Please note: The following tools requires SQL 2008 Management Studio. These tools will also work when you connect SQL 2008 SSMS to a 2005 instance.
Object Detail Explorer
Finally, there’s a reason to use the Object Detail Explorer! My favorite use is to quickly find the table size and row counts of all the tables in a database. If these options are not currently available, you may just need to right click on the column headers and add it to the display.
Object Detail Explorer
Missing Indexes
And lastly, when using SSMS 2008 to execute Display Estimated Query Plan (Ctrl+L), it will show you if you’re missing any indexes. This will even work if you connect SSMS 2008 to SQL 2005!
Missing Index
That pretty much covers it for now. HTH!
Michelle
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
