Three New SQL Server Best Practices Articles On TechNet

Predeployment I/O Best Practices

The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.
On This Page

Overview

Determining I/O Capacity

Disk Configuration Best Practices & Common Pitfalls

SQLIO

Monitoring I/O Performance Using System Monitor

Conclusion

Resources



Partial Database Availability

This white paper outlines the fundamental recovery and design patterns involving the use of filegroups in implementing partial database availability in SQL Server 2005. As databases become larger and larger, the infrastructure assets and technology that provide availability become more and more important.

The database filegroups feature introduced in previous versions of SQL Server enables the use of multiple database files in order to host very large databases (VLDB) and minimize backup time. With data spanning multiple filegroups, it is possible to construct a database layout whereby failure of certain data resources do not render the entire solution unavailable. This increases the availability of solutions that use SQL Server and further reduces the surface area of failure that would render the database totally unavailable.



Comparing Tables Organized with Clustered Indexes versus Heaps

In SQL Server 2005, any table can have either clustered indexes or be organized as a heap (without a clustered index.) This white paper summarizes the advantages and disadvantages, the difference in performance characteristics, and other behaviors of tables that are ordered as lists (clustered indexes) or heaps. The performance for six distinct scenarios where DML operations are performed on these tables are measured and detailed observations presented. This white paper provides best practice recommendations on the merits of the two types of table organization, along with examples of when you might want to use one or the other.
On This Page

Introduction

Clustered Indexes and Heaps

Test Objectives

Test Methodology

Test Results and Observations

Recommendations

Appendix: Test Environment

SQL Teaser NULL vs COALESCE

Without running the code, try to guess the output

DECLARE @v1 VARCHAR(3)
DECLARE @i1 INT


SELECT ISNULL(@i1, 15.00) /2,
COALESCE(@i1 , 15.00) /2,
ISNULL(@v1, 'Teaser #2'),
COALESCE(@v1, 'Teaser #2')

I hope you will use COALESCE instead of ISNULL from now on ;-)

Cross-posted from SQLBlog! - http://www.sqlblog.com/

How To Protect Yourself From Fat-Finger Sally, Crazy Bosses and Other SQL Villains

You all have been through this at least once in your life. In your shop there is this one person who likes to use Enterprise Manager as their Rapid Data Entry Application. We all know how these people operate; they delete rows, drop tables and all kinds of other funky stuff. SQL Server 2005 has DDL triggers to help you protect against these scoundrels. What about if you are still running that piece of software from the late Triassic period known as SQL Server 2000, what can help you in that case? Don’t worry I will show you a way but first I will tell you a story. About 6 years ago I worked in New York City as a consultant on a project for a nonprofit organization. I looked in the database and found this table which was named YesNoTable. I was curious I opened the table and noticed it had only 2 rows. Here is what was stored in the table.

0 no
1 yes

I dropped it immediately. 5 minutes went by and suddenly the CRM application was broken. They ran the debugger and found out a table was missing. Luckily for me it was very easy to recreate this table. And yes, we did get rid of it soon after. Now had the table be used by a view which had been created with schemabinding I would not be able to drop the table without dropping the view first. You see even I became a SQL villain one time.

What the code below does is it will loop through all the user created tables then union them all, I created a where 1 =0 WHERE clause just in case someone decides to open the view. Since a union can only have 250 selects or so, I have created the code so that you can specify how many tables per view you would like, you do that with the @UnionCount variable.

The code does print statements it does not create the views

If you run the code in the msdb database and you specify 5 as the @UnionCount your output will be this

-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_1 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[log_shipping_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_monitor]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_history]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plans]
WHERE 1=0
GO


-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_2 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[RTblClassDefs]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDatabaseVersion]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBMProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBXProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDTMProps]
WHERE 1=0
GO


The code is not very complex if there are more tables in the DB than you specify in the @UnionCount variable then it will do them in chunks of whatever you specified, if there are less then it will do all of them in 1 view.
Below is the code, if you have any questions then feel free to leave a comment.

USE msdb
SET NOCOUNT ON

DECLARE @UnionCount int
SELECT @UnionCount = 20

IF @UnionCount > 250 OR @UnionCount <1
BEGIN
RAISERROR ('@UnionCount has to be between 1 and 250', 16, 1)
RETURN
END

SELECT identity(int,1,1) AS id,QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS tablename
INTO #Tables
FROM information_schema.tables
WHERE table_type ='base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0
ORDER BY table_name

DECLARE @maxloop int
DECLARE @loop int
DECLARE @tablename varchar(200)




SELECT @maxloop = MAX(id)
FROM #Tables

BEGIN
DECLARE @OuterLoopCount int, @OuterLoop int

SELECT @OuterLoopCount = COUNT(*) FROM #Tables
WHERE id %@UnionCount =0

SELECT @OuterLoopCount = COALESCE(NULLIF(@OuterLoopCount,0),1)



IF (SELECT COUNT(*) FROM #Tables) % 10 <> 0
SELECT @OuterLoopCount = @OuterLoopCount +1

SELECT @OuterLoop =1

SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop

WHILE @OuterLoop <=@OuterLoopCount
BEGIN
SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop
AND id > (@UnionCount * @OuterLoop) - @UnionCount


PRINT'-- **************************** '
PRINT'-- **** View Starts Here ***** '
PRINT'-- **************************** '
PRINT 'CREATE VIEW DoNotDropMe_' + CONVERT(VARCHAR(10),@OuterLoop) + ' WITH SCHEMABINDING'+ char(10) + 'AS'
WHILE @Loop <= @maxloop
BEGIN
SELECT @tablename = tablename
FROM #Tables
WHERE id = @Loop
PRINT 'SELECT 1 As Col1 FROM ' + @tablename + char(10) + 'WHERE 1=0'
IF @Loop < @maxloop
PRINT UNION ALL'
SET @Loop = @Loop + 1

END
SET @OuterLoop = @OuterLoop + 1
PRINT 'GO'
PRINT ''
PRINT ''

END
END


DROP table #Tables


Cross-posted from SQLBlog! - http://www.sqlblog.com/

SQL Server 2008 Is RTM (According to SERVERPROPERTY('productlevel'))

SELECT @@VERSION,
SERVERPROPERTY('productversion'),
SERVERPROPERTY('productlevel')

Microsoft SQL Server code name "Katmai" - 10.0.1019.17 (Intel X86)
May 24 2007 15:26:55 Copyright (c) 1988-2007 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

10.0.1019.17
RTM (???)

Also interesting is that the tools (SSMS) are the same as with SQL Server 2005, If you have SQL Server 2005 already installed it will skip installing those.

Okay, this is the last Katmai post....for today.....I promise.....


Cross-posted from SQLBlog! - http://www.sqlblog.com/

SQL Server 2008 (Katmai) Cannot Be Installed On A PC With SQL Server 2000 On It

I tried installing the SQL Server 2008 – June CTP on one of my machines which had SQL Server 2000 installed; it does not let you do that. I guess this is the time to uninstall SQL Server 2000. That is just what I did. First thing I noticed is that MERGE statement is back. IIRC the MERGE statement was also in SQL Server 2005 Beta2 but got pull out of the RTM

Here is a small example of using MERGE from the Katmai Books On Line

MERGE FactBuyingHabits AS fbh
USING (SELECT CustomerID, ProductID, PurchaseDate FROM PurchaseRecords) AS src
ON (fbh.ProductID = src.ProductID AND fbh.CustomerID = src.CustomerID)
WHEN MATCHED THEN
    UPDATE SET fbh.LastPurchaseDate = src.PurchaseDate
WHEN NOT MATCHED THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
        VALUES (src.CustomerID, src.ProductID, src.PurchaseDate);

Cross-posted from SQLBlog! - http://www.sqlblog.com/

SQL Server 2008 June CTP now available!

From the connect site

Preview upcoming Releases: SQL Server 2008 June CTP now available!
We are always working on something new to make SQL Server even better. We now have SQL Server 2008 June CTP available for testing.

Link doesn't work yet, check back later here: https://connect.microsoft.com/SQLServer

SQL Server 2008/Katmai Webcast

TechNet Webcast: The Next Release of Microsoft SQL Server: Overview (Level 200)

Simulcast from Microsoft Tech·Ed 2007 in Orlando, FL.
This session provides an overview of the next release of Microsoft SQL Server that is currently under development. We cover the core value proposition, major themes and scenarios, and some specific improvements. We also discuss the new development processes Microsoft is using to build this release, the release timeline, and the disclosure calendar.

More details here: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032341071&EventCategory=2&culture=en-US&CountryCode=US

Did You Know SQL Server Has A Black Box Like An Airplane?

Paul Randal writes:

"Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an
aircraft flight-recorder, which I'd never heard of. It's an internal trace that
has the last 5MB of various trace events and it's dumped to a file when SQL
Server crashes. This can be really useful if you're troubleshooting an issue
that causing SQL Server to crash or someone or something is telling SQL Server
to shutdown and its unclear who or what is doing it."

Read here how to turn it on: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx

Cross-posted from SQLBlog! - http://www.sqlblog.com/

Excel 2007 Files Are WinZip/WinRAR Files In Disguise

I am not going to say anything about the ribbon or the diamond, this not a post about that. Create a new file in Excel 2007 with 250000 (that is a quarter million) rows. Just have 1 column with numbers from 1 to 250000. Save the file but keep it open. I applied a currency format to mine, see pic below.




Just for fun save it also in Excel 2003 format, you will get a warning and the file will only contain 65536 rows. I also captured that as a screenshot, the pic is below.




Now look at the files, do you see that the Excel 2003 file is bigger in size than the Excel 2007 file even though it has only one fourth the rows. Now locate the file and rename the xlsx extension of the Excel 2007 file to rar or zip. That is right the file is compressed, open the file up with WinZip or WinRAR. Open the Worksheets folder inside the xl folder, you will see a worksheet there which is about 17MB unpacked and about 2 MB packed. The pic of the RAR file is below.



Interesting stuff right?


Cross-posted from SQLBlog! - http://www.sqlblog.com/

Sam Gentile Reviews RubyMicrosoft Essay By Martin Fowler

Martin Fowler asked Sam Gentile to review and contribute thoughts to his very important new essay RubyMicrosoft. This piece presents the view that Microsoft is at a crossroads, an important time in its life where Microsoft can make good of opportunities or choose an altogether different road.

Read the article here: http://codebetter.com/blogs/sam.gentile/archive/2007/05/31/microsoft-at-the-crossroads.aspx

Here is the most interesting comment

On the alpha geek side, I fear all is lost already. All of my peers onCodeBetter.com and the "Agile .NET" community have already moved ontoCastle/Windsor, NUNit, NAnt, MonoRail, Spring.NET, NHibernate, etcinstead of Microsoft solutions. It’s virtually over already. For twoyears now, I have talked about our Agile team and how we can't useVisual Studio Team System and instead have to use CruiseControl.NET,NUnit, NAnt, etc to work in an Agile fashion. Not only does Microsoftnot understand this, but the majority of Microsoft programmers don't.They have been weaned on being "Morts" and having wizards, storedprocedures, drag & drop forced on them and not required to learn thesolid skills that make up what we think of as a developer


What is your opinion? We are using Subversion and currently testing with CruiseControl, NAnt and NUnit

Cross-posted from SQLBlog! - http://www.sqlblog.com

Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic

You have all seen websites where you can pick a bunch of categories by selection a bunch of check boxes. usually what you do is store those in a lookup table and then you create another table where you store all the categories for each customer.
What if I tell you that you can store all that info in 1 row instead of 10 rows if a customer picked 10 categories.
Take a look at this

1 Classic Rock
2 Hard Rock
4 Speed/Trash Metal
You will store a value of 1 + 2 + 4 = 7(you just sum the values)

Now run this to check, the result will be 7 for a match and some other value otherwise

select 7 | 1,
7 | 2,
7 |3,
7 |4,
7 |5,
7 |6,
7 |7,
7 |8,
7 |20

What is this |(pipe symbol)?
From Books on line
The bitwise operator performs a bitwise logical OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either or both bits (for the current bit being resolved) in the input expressions have a value of 1; if neither bit in the input expressions is 1, the bit in the result is set to 0.
The bitwise operator requires two expressions, and it can be used on expressions of only the integer data type category.

Here is how you would typically use this, first create this table

CREATE TABLE NumbersTable (Num int)
INSERT NumbersTable VALUES(1)
INSERT NumbersTable VALUES(2)
INSERT NumbersTable VALUES(3)
INSERT NumbersTable VALUES(4)
INSERT NumbersTable VALUES(5)
INSERT NumbersTable VALUES(6)
INSERT NumbersTable VALUES(7)
INSERT NumbersTable VALUES(8)
INSERT NumbersTable VALUES(9)
INSERT NumbersTable VALUES(10)
INSERT NumbersTable VALUES(11)
INSERT NumbersTable VALUES(12)
GO

Now run this

SELECT Num,
CASE 7 |Num WHEN 7 THEN 'Yes' ELSE 'No' END AS COL
FROM NumbersTable

Here is the output

Num COL
---- ---
1 Yes
2 Yes
3 Yes
4 Yes
5 Yes
6 Yes
7 Yes
8 No
9 No
10 No
11 No
12 No

Okay enough theory let's start with some SQL code. First create this table which will hold all the categories

CREATE TABLE MusicChoice (ID INT PRIMARY KEY,
ChoiceDescription VARCHAR(100))

INSERT MusicChoice VALUES(1,'Classic Rock')
INSERT MusicChoice VALUES(2,'Hard Rock')
INSERT MusicChoice VALUES(3,'Speed/Trash Metal')
INSERT MusicChoice VALUES(4,'Classical')
INSERT MusicChoice VALUES(5,'Rap')
INSERT MusicChoice VALUES(6,'Blues')
INSERT MusicChoice VALUES(7,'Jazz')
INSERT MusicChoice VALUES(8,'Alternative Rock')
INSERT MusicChoice VALUES(9,'Easy Listening')
INSERT MusicChoice VALUES(10,'Progressive Rock')
INSERT MusicChoice VALUES(11,'Punk Rock')
INSERT MusicChoice VALUES(12,'Swing')
INSERT MusicChoice VALUES(13,'Techno')
INSERT MusicChoice VALUES(14,'Pop')
INSERT MusicChoice VALUES(15,'Disco')
INSERT MusicChoice VALUES(16,'Big Band')
INSERT MusicChoice VALUES(17,'Gospel')
INSERT MusicChoice VALUES(18,'Heavy Metal')
INSERT MusicChoice VALUES(19,'House')
INSERT MusicChoice VALUES(20,'Celtic')
Now create the Bitwise table

CREATE TABLE BitwiseMusicChoice (ID INT PRIMARY KEY,
ChoiceDescription VARCHAR(100))

We will use the POWER function to create the correct values
run this

SELECT id,POWER(2,id-1)BitID,ChoiceDescription
FROM MusicChoice

Here is the output
id BitID ChoiceDescription
1 1 Classic Rock
2 2 Hard Rock
3 4 Speed/Trash Metal
4 8 Classical
5 16 Rap
6 32 Blues
7 64 Jazz
8 128 Alternative Rock
9 256 Easy Listening
10 512 Progressive Rock
11 1024 Punk Rock
12 2048 Swing
13 4096 Techno
14 8192 Pop
15 16384 Disco
16 32768 Big Band
17 65536 Gospel
18 131072 Heavy Metal
19 262144 House
20 524288 Celtic

Now insert it into the BitwiseMusicChoice table

INSERT BitwiseMusicChoice
SELECT POWER(2,id-1)BitID,ChoiceDescription
FROM MusicChoice

Now create this customer table


CREATE TABLE Customer (CustomerID int identity, CustomerCode uniqueidentifier not null)

Insert these 5 values first, we will use these to compare performance later

INSERT Customer VALUES('1DAB5C03-BC23-4FB5-AC3D-A46489459FE9')
INSERT Customer VALUES('F7DDCDBC-F646-493A-B872-4E2E82EA8E14')
INSERT Customer VALUES('E8A4C3D2-AEB0-4821-A49D-3BF085354448')
INSERT Customer VALUES('52581088-C427-4D2F-A782-250564D44D8C')
INSERT Customer VALUES('1B2622C4-6C17-4E74-99D6-336197FBBCFF')

Now we will insert a total of 10000 customers

SET NOCOUNT ON
BEGIN TRAN
DECLARE @LoopCounter INT
SET @LoopCounter = 6
WHILE @LoopCounter <= 10000
BEGIN
INSERT Customer VALUES(NEWID())
SET @LoopCounter = @LoopCounter + 1
END
COMMIT WORK
GO

Now add the primary key

ALTER TABLE Customer ADD CONSTRAINT pk_Customer PRIMARY KEY (CustomerCode)

Create another table to hold the choices

CREATE TABLE CustomerMusicChoice (id INT identity, MusicChoiceID int, CustomerCode uniqueidentifier)

ALTER TABLE CustomerMusicChoice ADD CONSTRAINT fk_MusicChoice_ID FOREIGN KEY (MusicChoiceID) REFERENCES MusicChoice(ID)

ALTER TABLE CustomerMusicChoice ADD CONSTRAINT fk_CustomerCode FOREIGN KEY (CustomerCode)REFERENCES Customer(CustomerCode)

For each customer insert 10 random choices, this should run less than a minute

SET NOCOUNT ON
BEGIN TRAN
DECLARE @LoopCounter INT
DECLARE @CustID uniqueidentifier
SET @LoopCounter = 1
WHILE @LoopCounter <= 10000
BEGIN
SELECT @CustID = CustomerCode
FROM Customer
WHERE CustomerID = @LoopCounter
INSERT Customer VALUES(NEWID())
INSERT CustomerMusicChoice(MusicChoiceID,CustomerCode)
SELECT TOP 10 id,@CustID
FROM MusicChoice
ORDER BY NEWID()
SET @LoopCounter = @LoopCounter + 1
END
COMMIT WORK
GO

Now add these indexes
CREATE INDEX ix_CustomerMusicChoice_Cust On CustomerMusicChoice(CustomerCode)

CREATE INDEX ix_CustomerMusicChoice_ID On CustomerMusicChoice(MusicChoiceID)

Create the BitwiseCustomerMusicChoice which will hold the Bitwise values

CREATE TABLE BitwiseCustomerMusicChoice (id INT identity, MusicChoiceID int, CustomerCode uniqueidentifier not null)

This will populate the BitwiseCustomerMusicChoice table

INSERT INTO BitwiseCustomerMusicChoice
SELECT SUM(POWER(2,MusicChoiceID-1)) as MusicChoiceID,CustomerCode
FROM CustomerMusicChoice
GROUP BY CustomerCode

Add the index and foreign key

ALTER TABLE BitwiseCustomerMusicChoice ADD CONSTRAINT pk_BitwiseCustomerMusicChoice PRIMARY KEY (CustomerCode)

ALTER TABLE BitwiseCustomerMusicChoice ADD CONSTRAINT fk_BitwiseCustomerCode FOREIGN KEY (CustomerCode)REFERENCES Customer(CustomerCode)

Now let's test performance. Hit CTRL + K (SQL 2000) or CTRL + M (SQL 2005)

These 2 queries will return something like this

ID ChoiceDescription Picked
8 Alternative Rock No
16 Big Band  No
6 Blues   No
20 Celtic   No
1 Classic Rock  No
4 Classical  Yes
15 Disco   Yes
9 Easy Listening  Yes
17 Gospel   No
2 Hard Rock  No
18 Heavy Metal  Yes
19 House   Yes
7 Jazz   Yes
14 Pop   Yes
10 Progressive  Rock Yes
11 Punk Rock  No
5 Rap   No
3 Speed/Trash Metal Yes
12 Swing   Yes
13 Techno   No

SELECT mc.ID,ChoiceDescription,CASE WHEN CustomerCode IS NULL THEN 'No' ELSE 'Yes' END Picked
FROM CustomerMusicChoice cmc
RIGHT JOIN MusicChoice mc on cmc.MusicChoiceID = mc.id
AND CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
ORDER BY ChoiceDescription

SELECT bmc.ID,ChoiceDescription,
CASE WHEN bmc.ID |MusicChoiceID =MusicChoiceID THEN 'Yes'
ELSE 'No'
END AS Picked
FROM BitwiseCustomerMusicChoice cmc
CROSS JOIN BitwiseMusicChoice bmc
WHERE CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
ORDER BY ChoiceDescription

Look at the execution plan
67.60% against 32.40% not bad right?

Now run this, we will add AND bmc.ID > 0 to both queries. This will change an index scan to an index seek in the bottom query

SELECT mc.ID,ChoiceDescription,CASE WHEN CustomerCode IS NULL THEN 'No' ELSE 'Yes' END Picked
FROM CustomerMusicChoice cmc
RIGHT JOIN MusicChoice mc on cmc.MusicChoiceID = mc.id
AND CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
AND mc.ID > 0
ORDER BY ChoiceDescription

SELECT bmc.ID,ChoiceDescription,
CASE WHEN bmc.ID |MusicChoiceID =MusicChoiceID THEN 'Yes'
ELSE 'No'
END AS Picked
FROM BitwiseCustomerMusicChoice cmc
CROSS JOIN BitwiseMusicChoice bmc
WHERE CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
AND bmc.ID > 0
ORDER BY ChoiceDescription

That improved the performance a little. 82.75% against 17.25%

Now look at the tables, after running dbcc showcontig you can see that the BitwiseCustomerMusicChoice is about 1/10th the size of the CustomerMusicChoice table which is as expected.


dbcc showcontig ('BitwiseCustomerMusicChoice')
---------------------------------------------------------------------------
DBCC SHOWCONTIG scanning 'BitwiseCustomerMusicChoice' table...
Table: 'BitwiseCustomerMusicChoice' (772197801); index ID: 1, database ID: 26
TABLE level scan performed.
- Pages Scanned................................: 41
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 48.0
- Avg. Page Density (full).....................: 99.41%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

dbcc showcontig ('CustomerMusicChoice')
---------------------------------------------------------------------------
DBCC SHOWCONTIG scanning 'CustomerMusicChoice' table...
Table: 'CustomerMusicChoice' (724197630); index ID: 0, database ID: 26
TABLE level scan performed.
- Pages Scanned................................: 428
- Extents Scanned..............................: 55
- Extent Switches..............................: 54
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 98.18% [54:55]
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 386.5
- Avg. Page Density (full).....................: 95.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


What happens if you want to get the total count of for example Classical?

SELECT COUNT(*)
FROM CustomerMusicChoice cmc
JOIN MusicChoice mc on cmc.MusicChoiceID = mc.id
WHERE mc.ChoiceDescription ='Classical'

SELECT COUNT(*)
FROM BitwiseCustomerMusicChoice cmc
JOIN BitwiseMusicChoice bmc ON bmc.ID |MusicChoiceID =MusicChoiceID
WHERE bmc.ChoiceDescription ='Classical'

Here are execution plans for SQl Server 2000 and 2005

As you can see SQL Server 2005 has a bigger difference than SQL Server 2000

Now let's look at the overal picture, on a busy system you will have the customer queries running many times an hour/day. The report queries will run maybe a couple a times a day. I think this trade off is perfectly acceptable because overall your system will perform better. Another thing to keep in mind is that instead of 10 inserts you only have to do 1, same with updates, all these little things add up to a lot eventualy.

So as you can see using bitwise logic is a great way to accomplish a couple of things
Reduce table size
Speed up backup and recovery because your table is much smaller
Improve performance

Of course you have to do some testing for yourself because it might not be appropriate for your design. If your system is more of an OLAP than OLTP type of system then don't bother implementing this since it won't help you.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Blogging On sqlblog.com

Adam Machanic invited me to start blogging on sqlblog.com, I accepted and you can find posts from now on here and there. Of course you will find the goofy posts only here ;-)
Here is the link to my blog on sqlblog.com:
http://sqlblog.com/blogs/denis_gobo/default.aspx

Check Your SQL For SQL Injection Vulnerabilities By Using These 15 Free SQL Injection Scanners

Memorial day is over, back to work. Your first task is to check for SQL Injections
Securiry Hacks has listed 15 of them, that list is here:

SQLIer
SQLbftools
SQL Injection Brute-forcer - SQLibf
SQLBrute
BobCat
SQLMap
Absinthe
SQL Injection Pen-testing Tool
SQID
Blind SQL Injection Perl Tool
SQL Power Injection Injector
FJ-Injector Framwork
SQLNinja
Automagic SQL Injector
NGSS SQL Injector

Read the details and get the download links here: http://www.security-hacks.com/2007/05/18/top-15-free-sql-injection-scanners

If you are using a lot of dynamic SQL then don't forget to read The Curse and Blessings of Dynamic SQL written by Erland Sommarskog, SQL Server MVP.

Top 10 .NET Framework Technologies to Learn in 2007. What do you think?

help.net list the following 10 .NET Framework Technologies to Learn in 2007. What do you think? I think that you can not known all of these as well if you picked only a handful. I marked the ones that I think are absolutely needed in red and the maybe ones in blue. As you can see I marked only 4 ([edit]that was initially, I added threading since it shouldn't take you that long to master that and I added some other ones that are essential for a developer[/edit]) and yes SQL Server is one of them. Even to master SQL Server 2005 is very difficult if not impossible. Do you know all of the following good enough that you can work with it without a problem?

SSIS
SSAS
ServiceBroker
Notification Services
Replication
SQL CLR
T-SQL

You don't and you really can't unless you don't have a life and sit in front of a PC 24/7 but even then it is doubtful
What I have noticed is that a lot of .NET developers didn't do Ajax until MS released ASP.NET Ajax, it is like they are oblivious to the fact that there are other frameworks out there like prototype for example. It is kind of sad that the technology that was first developed by Microsoft and made its way into Outlook Web Access took sooooooo long to get into Visual Studio. Same thing with Nant, Subversion etc etc. I mean how many of you are still using SourceSafe? I am not using it because Subversion is so much better and it's also free (not that that matters because we get it with our MSDN subscriptions anyway).

So open your eyes check CodePlex regularly for new things. Do you know there is a NHibernate.Spatial project? No, did you know that MbUnit 2.4 has been released?
Download it here: http://mb-unit.googlecode.com/files/MbUnit-2.4.197.exe



Here is the list (from help.net)


1) WCF (Windows Communication Foundation): While WCF is certainly less "sexy" than say WPF / Silverlight, it is going to represent the backbone of solving business problems with .NET going forward from here. Big organizations are starting to embrace it, and it provides a cohesive framework for solving business problems in a distributed, integrated way. WCF integrates Remoting, WebServices, EnterpriseServices and Transactions, WSE, MSMQ, and much more into a cohesive programming framework. If you intend to make it your business to study just one new .NET technology this year, make it WCF.

2) ADO.NET (and LINQ): ADO.NET is how you talk to a data store, and databases are such a ubiquitous part of what you will do as a developer that you have NO CHOICE but to become extremely competent in this area. The next big thing in how to talk to data is LINQ with language extensions and entity objects that "represent" mappings of data and its relationships. If you don't become at least familiar with all this stuff, somebody else is going to eat your lunch.

3) WPF (Windows Presentation Foundation): Everything you learned about Windows Forms, pages in a browser, and UI elements is going to go out the window, because Microsoft has already declared that WPF is the new way we're gonna do this stuff. Its already built in to Windows Vista, and the XPS (XML Paper Specification) is already built into the printer subsystem. Don't hang on to the old- get with the new just as fast as you can get your little tushy in gear! And besides that, WPF is just so friggin' cool, it will knock your socks off in hi-def streaming video!

4) SQL Server 2005 (and on): I understand this isn't really .NET, but then again it really is. SQL Server 2005 hosts CLR integration of managed code. That's not only revolutionary, it provides a power to the programmer that you cannot get on other platforms. You have to learn everything you can about SQL Server 2005 including Service Broker, because it will help you to be a better programmer and problem - solver.

5) ASP.NET 2.0: Even non "web programmers" need to understand how this works. The feature set has grown and matured, and you are looking at dynamic languages, LINQ, AJAX, and much more being integrated into the ASP.NET Framework (not to forget Silverlight - the sexiest technology of all of them!).

6) Security. Developers are notoriously weak on security ("Who cares about permission sets - I'm just a Code Monkey"). Unfortunately, we do not live in a perfect world and there are evil people out there who jump with glee when they can mess up your day. The more you become an expert about security, the higher your pay will be: expert security consultants make upwards of $300 / hr.

7) TDD (Test Driven Development): Unit, regression and integration testing aren't a luxury - if you want to develop robust systems then you must have a test protocol. And to do testing right, you need to study how to write tests and what tests to write. There are several excellent books on TDD and at least one that specifically focuses on .NET.

8) Networking (System.Net and related): Networking -- TCP, UDP, HTTP, FTP, and on -- are an integral part of what you need to know how to do in order to glue programs together and make them be able to talk with each other and your data. The more you know about this area, the better equipped you'll be to make the right kind of choices when you are tasked with creating business logic through code.

9) Threading: When asked to develop a multi-threaded object or to use a ThreadPool, 95 out of 100 programmers who claim to be professional .NET developers fall flat on their faces! You need to study all the threading primitives, know how they are used, be able to use the .NET or a custom ThreadPool, and manage threads in your applications.

10) Learning. That's right, I classify learning as a technology. People need to become smarter about how to learn, and especially, WHAT to learn. Just as you become a better programmer when you learn to use the Google or other search engines more effectively, you become a better programmer when you can detail for yourself what you need to learn to be better at your craft, and write down a plan for implementing that learning process.


I would like to have all of them in red but it is time to start specializing just like doctors do, you have to become a specialist. I rather have a lung doctor, an eye doctor and an orthopedic surgeon on my team than 3 physicians who know a little about everything but not a lot about one thing.

Object Databases Suck RDMBS's Rock

Gavin King (Mr Hibernate) trashes what we call 'Object Databases'. This is excellent reading material and full of information why RDBMS's are still around after all those claims of object database vendors that their product is so incredibly more efficient

Read the article here: http://blog.hibernate.org/cgi-bin/blosxom.cgi/2007/05/23#in-defence

And remember the latest version of Nhibernate DOES support stored procedures! No more wizard generated SQL that is hard to debug because you don't know what it looks like.

Book Review: Pro SQL Server 2005 Database Design and Optimization

Pro SQL Server 2005 Database Design and Optimization
By Louis Davidson, Kevin Kline, Kurt Windisch
ISBN: 1590595297
672 pp.
Published: May 2006

Before I start with the book review here is a little story that took place in 1995. A former colleague wanted to open a bar in the East Village (New York City). He looked and looked and found this place somewhere on 9th street. He looked around the place and liked what he saw. Then he went to the back and saw about seven wooden beams against the wall. When he asked about them the owner said that is to keep the wall from collapsing. Anyway my friend settled on another place and opened his bar in a former parking garage, the name of the place is Liquids and it is located on east 10 Street between First Avenue and Avenue A.

Now why am I telling you this story? This story happens in the database world all the time, think of the data model as the foundation of your house if your foundation is weak you need kludges (wooden beams) to prevent your structure from collapsing. How many times do you see on newsgroups questions relating to split string because someone stored a comma delimited string in a column instead of having a lookup table? You have to start with the proper design, once you have that everything else becomes so much easier. It is easy to change a proc or some code somewhere. Once you have to redo your design then you have to also go through all the code and make changes and then also pray that you didn’t break anything. So this is what this book is all about; to teach you how to properly design a data model for speed, concurrency, security, interoperability, integrity and much more. There is no need for triggers when you can use check constraints which in turn will perform much better.

I got my copy of the book the day after our twins were born, that is a little more than 10 months ago. I have read the book many times but I did not read it in the order that it is written in. However I do recommend reading at least the first 5 chapters in the correct order, this is especially true if you are new to databases.

So what do I think of the book? I think that this book is excellent, it is written in a way that is not dry, and you will actually laugh a couple of times when reading this book. The book is written in such a way that it goes into as much detail as necessary to explain the concept and also provides links where needed for material that is not in the scope of the book. Most chapters end with a best practices and a summary section which is very useful if you want to find out what the chapter is about without reading the whole chapter first.

The book has 11 Chapter and 2 appendixes which are listed below

Chapter 1 Introduction to Database Concepts
Chapter 2 Data Modeling
Chapter 3 Conceptual Data Modeling
Chapter 4 The Normalization Process
Chapter 5 Implementing the Base Table Structures
Chapter 6 Protecting the Integrity of Your Data
Chapter 7 Securing Access to the Data
Chapter 8 Table Structures and Indexing
Chapter 9 Coding for Concurrency
Chapter 10 Code-Level Architectural Decisions
Chapter 11 Database Interoperability
APPENDIX A Codd’s 12 Rules for an RDBMS
APPENDIX B Datatype Reference
INDEX

The name of the chapter indicates what the chapter is about, I won’t go into detail by reviewing every chapter.

If you are completely new to SQL then this is the book for you because a good design is the foundation of everything. Now there are two ways to find this out the first way is the easy way. You purchase the book, read it and use the code. The second way is more painful. You have a weak database foundation, performance is bad, customers complain, your boss is breathing down your neck and the only shows you watch on TV are the late shows because you have to update statistics and reindex your database between 7PM and 10PM every single day to make it perform somewhat responsive. If you are an advanced/intermediate developer don’t think that this book is not for you. It is! I have learned a bunch of new tricks/tips which I would not have if I did not read the book. Obviously I am not the only one who praises the book; just take a look at the reviews on Amazon

If you want check out the book first here is the link to the sample chapter: http://www.apress.com/book/supplementDownload.html?bID=10005&sID=3490

Here is the link to Louis’ blog where he posts updates about the book http://drsql.spaces.live.com

I did an interview with Louis a while back, you can find that here

The nest 2 reviews will be Itzik’s and then Adam’s book. I think that Itzik’s will be next week and Adam’s the week after that.