Category Archives: SQL Server

Tonight, I gave a presentation to the Springfield .NET Users Group.  I chose to present on SQL Server 2008 Development for Programmers.  I tried to keep it to a level 200 type class.

The following is my presentation in SlideShare.net:

I started a new job this week. I am working for Expedia, Inc now as a Database Development Engineer (or DB Dev, for short). So far, I am really enjoying the opportunity. The people are really great and have a real entusiasm for what they do.

It’s a quicker pace for development releases than I’m used to, but it seems like they have a really good system of checks and balances to ensure the process is flawless. I am learning that “Uptime” is the most important factor and that everything else comes in as second priority. That’s such a different mentality to me. My background has always been in the client-server world. So we could shut the system down to make our changes/updates and then give the All Clear signal to let everyone in. But not with a website that is running 24x7x365. It the site is down even a few minutes, that’s lost revenue, which is unacceptable.

The work is still similar from a database perspective, but every little change is scrutinized. Can’t update a table in a large batch, it has to be done in iterations of 100 records or less so that it doesn’t lock too many data pages.

Today, I was writing a multi-threaded query stress tool. It’s going to be something that will compare all the CPU, read/write IO, & memory metrics for a defined number of iterations and threads. We will be able to scale it up to simulate a real load on the database server. We have to be concisous of every little change we implement, because a small change can get magnified when 50,000 users hit the system.

Regardless of the details, I am LOVING my career change. It’s refreshing to change everything.

This morning, when I came into work, I discovered that Windows Update had graciously restarted my machine for me. No big deal. But wait, then I realized I had some SQL work that I hadn’t saved yet. NOOOOOO!!! I know, I know, … always save your work. But yesterday, I didn’t. So you can imagine how irritated I was to discover that all of that hard work was gone. Or so I thought.

A co-worker heard me getting mad at myself and chimed in that SQL Server Management Studio (SSMS) has an autorecover feature. WHAT!? Yep, when ever you start typing a query in SSMS, a file is automatically created and saves itself every 5 minutes. The location of this AutoRecover document is located in “\My Documents\SQL Server Management Studio\Backup Files\Solution [n]\”. The [n] represents an integer that corresponds to how many SSMS IDE windows you have open. I only had one IDE open, with two query windows open, so I had a “\Solution 1\” folder with two files in it: ~AutoRecover.~vs47E8.sql & ~AutoRecover.~vsD5FD.sql.

Try it out. Open the folder mentioned above and open a new SSMS IDE and Query window and start typing out a quick SELECT. After about 5 minutes, you will see a new file prefixed with “~AutoRecover.~vsXXXX.sql”. The X’s are alphanumeric values that change with every file. The new file will get auto saved in 5 min increments from there on. When you close SSMS or a query window and it asks you if you want to save, click No, and watch the auto-generated file delete itself.

Wow, that was a HUGE relief. SSMS 2005 and higher has this feature built in. Thank you Microsoft for saving me from my own carelessness.

Got a fun requirement today. Using SQL, the developer needed to convert an integer representing seconds into the format of dd:hh:mm:ss. So 125 seconds would be 00:00:02:05.

I put together a quick script that creates a table variable, inserts some test values, and selects back out of it.

DECLARE @ADAM TABLE(sec INT NULL)
INSERT INTO @ADAM (sec) VALUES (125)
INSERT INTO @ADAM (sec) VALUES (3600)
INSERT INTO @ADAM (sec) VALUES (3605)
INSERT INTO @ADAM (sec) VALUES (60000)
INSERT INTO @ADAM (sec) VALUES (6000)
INSERT INTO @ADAM (sec) VALUES (600)
INSERT INTO @ADAM (sec) VALUES (86400)
INSERT INTO @ADAM (sec) VALUES (86405)
INSERT INTO @ADAM (sec) VALUES (172800)
INSERT INTO @ADAM (sec) VALUES (172860)
INSERT INTO @ADAM (sec) VALUES (172865)
INSERT INTO @ADAM (sec) VALUES (1234567)
INSERT INTO @ADAM (sec) VALUES (75654)
SELECT RIGHT('0' + CONVERT(varchar(6), sec/86400),2)
+ ':' + RIGHT('0' + CONVERT(varchar(6), sec % 86400 / 3600), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sec % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), sec % 60), 2)
FROM @ADAM

Results
-----------
00:00:02:05
00:01:00:00
00:01:00:05
00:16:40:00
00:01:40:00
00:00:10:00
01:00:00:00
01:00:00:05
02:00:00:00
02:00:01:00
02:00:01:05
14:06:56:07
00:21:00:54

Last week, one of our developers asked us in the DBA group what our standard was for currency columns. My first thought was DECIMAL(19,4), but I couldn’t give a reason why it was standard. I knew MONEY was essentially the same definition, but had heard that it was dangerous to use. Suspecting a rounding issue, I began with a simple test.

DECLARE
    @dOne DECIMAL(19,4),
    @dThree DECIMAL(19,4),
    @mOne MONEY,
    @mThree MONEY
SELECT
    @dOne = 1,
    @dThree = 3,
    @mOne = 1,
    @mThree = 3
SELECT
    @dOne / @dThree * @dThree AS DecimalResult,
    @mOne / @mThree * @mThree AS MoneyResult

--------------------------------------------------------------------
DecimalResult  MoneyResult
1.000000       0.9999

Interesting! Why did the MONEY type get truncated? So another quick query gave me the output precision and scale.

SELECT
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'basetype') AS DecimalBasetype,
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'precision') AS DecimalPrecision,
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'scale') AS DecimalScale,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'basetype') AS MoneyBasetype,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'precision') AS MoneyPrecision,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'scale') AS MoneyScale

-------------------------------------------------------------------------------------------
DecimalBasetype  DecimalPrecision  DecimalScale  MoneyBasetype  MoneyPrecision  MoneyScale
Decimal          38                6             money          19              4

The decimal is now a DECIMAL(38,6) and the MONEY is still a DECIMAL(19,4). Hmmm. Why did one change and the other didn’t change.

For an answer, I started digging into MSDN for precision & scale of data types. I came across this article. It had a fantastic table that described it.

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

So let’s expand on the first calculation, @dOne / @dThree * @dThree, which is actually two calculations, division and then multiplication.

e1 / e2 = DECIMAL(19,4) / DECIMAL(19,4)

precision = p1 – s1 + s2 + max(6, s1 + p2 + 1) = 19 – 4 + 4 + max(6, 4 + 19+ 1) = 43

scale = max(6, s1 + p2 + 1) = max(6, 4+19+1) = 24

So that leaves us with a DECIMAL (43,24) for the division portion, which is not a legal data type. So SQL subtracts 5 from the precision to get the 43 down to a 38, and a matching 5 from the scale, which leaves us with a DECIMAL(38,19).

e1 * e2 = DECIMAL(38,19) * DECIMAL(19,4)

precision = p1 + p2 + 1 = 38 + 19 + 1 = 58

scale = s1 + s2 = 19 + 4 = 23

This leaves us with a DECIMAL(58,23) which is also an illegal data type. So, again, SQL subtracts 20 from the precision to get the 58 down to a 38, and a matching 20 from the scale, which leaves us with a DECIMAL(38,3). But wait, there is an undocumented rule that if the scale is being lowered by this process, it is never lowered below 6. So we actually end up with a DECIMAL(38,6).

With the second calculation, @mOne / @mThree * @mThree, all of the results are cast back to a MONEY or DECIMAL(19,4) which truncates data and is why we lose the accuracy of our scale.

What did we learn, … Don’t use the MONEY datatype in SQL Server.

I have been really getting into RSS feeds lately and have subscribed to alot of ones that interest me.   I personally have been using the Google Reader on the iGoogle homepage.  It’s a neat way to keep tabs on my varied readings.  One that caught my eye was a challenge by Pinal Dave.   Pinal challenged his readers to solve a puzzle that would return the size of each index for a speficied table.

He started with a simple sp_spaceused [tableName] command that returned the index_size of the table and a selection from sys.indexes that returned a list of all of the indexes on said table.  But it was up to his readers to figure out how to get the index_size of each index, so that the sum of all of them would add up to teh result from sp_spaceused.  Read the article to follow along.  My answer is the 7th down (mine is simpler and suprising different from the other answers).  Pinal will post the solution with due credit on his blog.  Hopefully, I did well, seeing that he has almost 11 million readers.

Here is a quick overview of the puzzle:

My answer was:

DECLARE
@objname nvarchar(776),
@id int

SELECT
@objname = 'TableToExamine',
@id =  object_id(@objname)

SELECT
i.*,
CASE
WHEN ps.usedpages  > ps.pages  THEN (ps.usedpages - ps.pages)
ELSE 0
END  * 8 indexsize
FROM sys.indexes i
INNER JOIN (
SELECT
OBJECT_ID,
index_id,
SUM (used_page_count)  usedpages,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)pages
FROM  sys.dm_db_partition_stats
WHERE object_id  = @id
GROUP BY object_id,  index_id
) ps on i.index_id = ps.index_id
WHERE  i.object_id  = @id

Well, that wasn’t my exact answer.  I had a variable declaration that I really didn’t need, but hey, I did it in like 5 mins.  So this version is a little cleaner.

ENJOY!  And in the spirit of fun, See if you can do it better!