SQL Tact - Blogs - SQLServerCentral

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

SQL Tact

SQL Saturday Baton Rouge 2013

SQL Saturday is a global event to bring Information Technology speakers and professionals together for a community-driven, community-attended free day of technical training. The Baton Rouge SQL Saturday 2013 event is sponsored by the Baton Rouge SQL Server and .net User Groups, and will be our fifth annual event on…

0 comments, 45 reads

Posted in SQL Tact on 11 June 2013

SQL Saturday Baton Rouge Call for Speakers closes June 6!

SQL Saturday Baton Rouge Call for Speakers is open through June 6 (closes June 7 at midnight).

This year's pre-conference banquet at Boudreaux and Thibodeaux's in energetic downtown Baton Rouge is going to be one to remember - our banquet is privately catered at 6pm and the house opens to…

0 comments, 102 reads

Posted in SQL Tact on 4 June 2013

Use the Default System Trace to Find Autogrowth Events

The SQL Server Management Studio built-in standard report for "Disk Usage" is very handy to view the performance-sucking autogrowth events for a database... until you want to see if autogrowth events have been occurring in any database on the server.

Autogrowth events are very problematic and can cause sudden, unpredictable…

0 comments, 163 reads

Posted in SQL Tact on 3 May 2013

Hunt Down Tables Without Clustered Indexes

This one's a gimme, but an underrated utility script when I enter a new environment and look for low-hanging fruit for easy improvements.

You'd be surprised how many vendors ship applications without a single index, much less properly aligned clustered indexes.

Heaps are bad. You can't defragment a heap by…

8 comments, 1,316 reads

Posted in SQL Tact on 3 May 2013

TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:

0 comments, 253 reads

Posted in SQL Tact on 21 March 2013

Conversation History: The Continuing Case Against GUIDs

We had a cooperative relational database design exercise at the office last week as part of our regular department "Lunch and Learn" series, and inevitably one topic that came up is the use of GUIDs as the unique key for a table.

And before long, I had been goaded onto…

0 comments, 642 reads

Posted in SQL Tact on 8 March 2013

YYYYMM Math

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY. Why? For one, it sorts correctly. And…

0 comments, 156 reads

Posted in SQL Tact on 1 March 2013

"An item with the same key has already been added."

In SQL Server Reporting Services, when adding a new dataset to a report, you may see an error that looks like this:

"An item with the same key has already been added."

Based on some quick googling, it looks like there are several potential causes for the error, but here's…

0 comments, 227 reads

Posted in SQL Tact on 28 February 2013

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys. But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,

--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int…

0 comments, 188 reads

Posted in SQL Tact on 18 February 2013

Using Foreign Keys to Determine Table Insertion Order

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

with cteFK (pktable, fktable) as ( 
       select              
            pktable = o1.name 
       ,    fktable = isnull(o2.name, '')        
       from sysobjects o1       
       left join sysforeignkeys fk on o1.id = fk.fkeyid        
       left…

0 comments, 143 reads

Posted in SQL Tact on 17 February 2013

Adding the Clustered Key To Your Nonclustered Index? Part 2

Of course, a rather obvious answer presents itself the next day. After reviewing the actual environment that my colleague was working in, it popped quickly into mind.

What if the table has a compound primary key? See comments for a play-by-play.

drop table dbo.testclusteredinclude

go

create table dbo

0 comments, 145 reads

Posted in SQL Tact on 5 February 2013

Adding the Clustered Key To Your Nonclustered Index?

A colleague of mine was understandably confused when using the almost-perfect missing index engine in SQL 2008. The engine recommended he include the clustered index key in a new nonclustered index. Read that again - you're right, it doesn't make a lot of sense.

From my colleague:

Why would you ever need…

1 comments, 163 reads

Posted in SQL Tact on 4 February 2013

Try a Database Design Exercise At Your Next SQL User Group

Tried something different for the January 2013 meeting of the Baton Rouge SQL Server User Group (brssug.org), which meets every second Wednesday at the Louisiana Technology Park (latechpark.com).

I think this was one of the most enjoyable meetings we've ever had, and certainly the most interactive.…

0 comments, 317 reads

Posted in SQL Tact on 10 January 2013

On the Advantages of DateTime2(n) over DateTime

Starting with SQL 2008, we database developers started becoming more familiar with datetime2. Sometimes folks need convincing though, so here goes.

Here's a brief review of how the precision of the datetime2 data type converts from a varchar representing a time value out to one ten-millionths of a second. Run…

0 comments, 594 reads

Posted in SQL Tact on 4 December 2012

Management Studio Database Diagram Owners

If you're working in an environment as a developer without sysadmin privileges, and you are creating database diagrams using Management Studio underrated diagram tool, but not the database owner or a sysadmin, you'll see your created diagrams look like this

where the diagram is owned by the developer using the sql…

0 comments, 1,169 reads

Posted in SQL Tact on 3 December 2012

PowerPivot for Excel "Not loaded. The Managed Add-in Loader failed to initialize."

Error when trying to enable the PowerPivot for Excel add-in for Office 2010 reads:
"Not loaded. The Managed Add-in Loader failed to initialize."

If the installation of the PowerPivot for Excel add-in succeeded, the add-in should be enabled in the Excel Add-ins options panel. If not, you might see the…

0 comments, 849 reads

Posted in SQL Tact on 30 November 2012

Database Design Contest at the 2012 AITP Region 3 Student Conference

Had a great Friday in Lafayette, LA on the campus of the University of Louisiana-Lafayette. 31 different teams submitted database designs on my problem statement that required an ERD and sample data set. The database design contest was sponsored by the Baton Rouge SQL Server User Group, a chapter…

0 comments, 241 reads

Posted in SQL Tact on 14 October 2012

Performance Tuning Presentation from October 2012 BRSSUG Meeting

Thanks to the 15+ folks who attended the Baton Rouge SQL Server User Group meeting last night! I hope everyone enjoyed "everyone gets a book" night!

I presented on SQL Server Performance tuning, which began with Michelle Ufford's article from GoDaddy on how she saw massive performance gains just by…

0 comments, 313 reads

Posted in SQL Tact on 11 October 2012

How BETWEEN is inclusive

This is a old and simple one, but an important subtlety that TSQL developers must be aware of.

Using the BETWEEN syntax is easy enough in a WHERE clause, and it's pretty obvious for numbers.

use testing

go

create table decbetweentesting

( id int identity(1,1

4 comments, 530 reads

Posted in SQL Tact on 26 September 2012

SQL Server Best Practices for DMV's - Houston Tech Fest 2012

It is a pleasure meeting everyone today at Houston Tech Fest! The new facility is miles away - literally and figuratively - from the previous Houston Tech Fest facility.

Here's a link below for downloading the presentation files and sample scripts from my presentation on "SQL Server Best Practices for…

0 comments, 340 reads

Posted in SQL Tact on 8 September 2012