Top 20 Programming Lessons

20/20: Top 20 Programming Lessons I’ve Learned in 20 Years – DCS Media.

Above is my contribution to the echo chamber.

The author sums up quite nicely a lot of what I’ve thought about coding… simple is better, don’t over analyze to the point of paralysis – and sometimes you just have to jump in and see where it gets you.

Posted in SQL Server Development | Tagged , | Leave a comment

Indexes, Dynamic Views, and you.

I was trying to re-invent the wheel this week by writing an index creation script / procedure using the dynamic views provided by Microsoft in my 2k5 database.

After looking in BoL and other sources for what the columns really meant in the dataset, I decided to search SQLServerCentral.com and see if anyone had already done this. (Note to self: Do this step first next time)

I stumbled upon this script, which promptly made me throw out my script and give this procedure a whirl. Take a look.

http://www.sqlservercentral.com/scripts/Index+Management/63937/

Posted in SQL Server Performance | Tagged , | Leave a comment

SQL Server Bug – Synonyns

The below bug was driving me crazy a few weeks ago until I figured it out via the power of Google.

In short, when your target is on the same server, don’t include the server name.

USE [master]
GO

--Create a database to test with
CREATE DATABASE Test_A
GO

--Create a second database to test with
CREATE DATABASE Test_B
GO

--Start in the first database
USE Test_A
GO

--Create a Procedure to play with
CREATE PROCEDURE TestMe
(
@INT INT
)
AS
SELECT @INT
GO

--Quick test to ensure that everything works as intended
EXEC TestMe @INT = 5
GO

--Switch databases to show the problem we're having
USE Test_B
GO

--Run the procedure using the four part name. Note that with the server name set appropriately everything works...
EXEC DB1.Test_A.dbo.TestMe @INT = 5
GO

--Create a synonym that won't work
CREATE SYNONYM dbo.Fail_SYNONYM FOR DB1.Test_A.dbo.TestMe
GO

--See? Crazy.
EXEC dbo.Fail_SYNONYM @INT = 5
GO

--Create a synonym that will work!
CREATE SYNONYM dbo.Win_SYNONYM FOR Test_A.dbo.TestMe
GO

--See?
EXEC dbo.Win_SYNONYM @INT = 5
GO

/*
USE [Master]
GO

DROP DATABASE Test_A
GO

DROP Database Test_B
GO
*/
Posted in SQL Server Development | Tagged , | 1 Comment

INSERT INTO & Temp Table Creation

At my previous employer, my old boss and I would debate the merits of using “INSERT INTO” to create temp tables. He was against it in all cases, where I would use it when I was exploring a problem, or exploring a solution. I had always tried to not use it in production code, but apparently he recently found evidence of my code used in production.

As proof of my good intentions for never using “INSERT INTO” in production code, yet seeing the value in being able to write a query to return a dataset many columns wide, and then be able to turn it into a table in a few keystrokes, I share this stored procedure with the world.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[UTIL].[CreateStatementForTempTable]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
DROP PROCEDURE [UTIL].[CreateStatementForTempTable]
GO

CREATE PROCEDURE [UTIL].[CreateStatementForTempTable]
(
@TempTableName VARCHAR(1000)
)
AS
SET NOCOUNT ON

-- Purpose: Pass in temp table, return create table statement
-- Date: 10/5/2009
-- Version: 1.00.000
-- Author: Matt Silva

/* Notes:
Laziness is the mother of all invention.
*/


-------------------------------------------------------------------------------

DECLARE @SQLToRun VARCHAR(MAX)

-------------------------------------------------------------------------------

IF @TempTableName IS NULL
RETURN

-------------------------------------------------------------------------------

SET @SQLToRun = '
;
WITH CTE_OutputSQL AS (
SELECT ''IF OBJECT_ID(''''tempdb..' + @TempTableName + ''''') IS NOT NULL'' AS [Statement], -4 AS OrderBy
UNION
SELECT ''   DROP TABLE ' + @TempTableName + ''' AS [Statement], -3 AS OrderBy
UNION
SELECT '''' AS [Statement], -2 AS OrderBy
UNION
SELECT ''CREATE TABLE ' + @TempTableName + ''' AS [Statement], -1 AS OrderBy
UNION
SELECT ''('' AS [Statement], 0 AS OrderBy
UNION
SELECT
CASE
WHEN C.ORDINAL_POSITION = 1 THEN ''''
ELSE '', ''
END
+ QUOTENAME(c.COLUMN_NAME) + SPACE(1) +
CASE
WHEN C.DATA_TYPE = ''bigint'' THEN ''bigint''
WHEN C.DATA_TYPE = ''binary'' THEN ''binary''
WHEN C.DATA_TYPE = ''bit'' THEN ''bit''
WHEN C.DATA_TYPE = ''char'' THEN ''char('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''date'' THEN ''date''
WHEN C.DATA_TYPE = ''datetime'' THEN ''datetime''
WHEN C.DATA_TYPE = ''datetimeoffset'' THEN ''datetimeoffset''
WHEN C.DATA_TYPE = ''datetime2'' THEN ''datetime2''
WHEN C.DATA_TYPE = ''decimal'' THEN ''decimal('' + CAST( C.NUMERIC_PRECISION AS VARCHAR(MAX) ) + '', '' + CAST( C.NUMERIC_SCALE AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''float'' THEN ''float''
WHEN C.DATA_TYPE = ''image'' THEN ''image''
WHEN C.DATA_TYPE = ''int'' THEN ''int''
WHEN C.DATA_TYPE = ''money'' THEN ''money''
WHEN C.DATA_TYPE = ''numeric'' THEN ''numeric('' + CAST( C.NUMERIC_PRECISION AS VARCHAR(MAX) ) + '', '' + CAST( C.NUMERIC_SCALE AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''nchar'' THEN ''nchar('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''ntext'' THEN ''ntext('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''nvarchar'' THEN ''nvarchar('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''real'' THEN ''real''
WHEN C.DATA_TYPE = ''smalldatetime'' THEN ''smalldatetime''
WHEN C.DATA_TYPE = ''smallint'' THEN ''smallint''
WHEN C.DATA_TYPE = ''smallmoney'' THEN ''smallmoney''
WHEN C.DATA_TYPE = ''text'' THEN ''text''
WHEN C.DATA_TYPE = ''time'' THEN ''time''
WHEN C.DATA_TYPE = ''timestamp'' THEN ''timestamp''
WHEN C.DATA_TYPE = ''tinyint'' THEN ''tinyint''
WHEN C.DATA_TYPE = ''uniqueidentifier'' THEN ''uniqueidentifier''
WHEN C.DATA_TYPE = ''varbinary'' THEN ''varbinary''
WHEN C.DATA_TYPE = ''varchar'' THEN ''varchar('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''sql_variant'' THEN ''sql_variant''
WHEN C.DATA_TYPE = ''xml'' THEN ''xml''
ELSE ''''
END  AS [Statement], C.ORDINAL_POSITION AS OrderBy
FROM tempdb.INFORMATION_SCHEMA.tables t
INNER JOIN tempdb.sys.tables ta ON t.TABLE_NAME = ta.NAME
INNER JOIN tempdb.INFORMATION_SCHEMA.columns c ON ta.NAME = c.TABLE_Name
WHERE ta.OBJECT_ID = OBJECT_ID(''tempdb..' + @TempTableName + ''')
UNION
SELECT '')'' AS [Statement], 99999 AS OrderBy
)
SELECT [Statement]
FROM CTE_OutputSQL
ORDER BY OrderBy
'

EXEC( @SQLToRun )

SET NOCOUNT OFF
RETURN
GO

It contains all of the datatypes for SQL Server 2005 and SQL Server 2008. Basic usage as follows:

SELECT
/* Many, Many, Many columns */
INTO #<temp_table_name>
FROM
/* A nice long series of tables */

EXEC [UTIL].[CreateStatementForTempTable] '#<temp_table_name>'

The result set will return a CREATE TABLE statement that matches the columns that were in the temp table created by the INSERT INTO statement.

You can use the result set to tweak the table if it doesn’t match the datatype you expect it to (I haven’t seen that happen) or if you wanted to add NOT NULL or other constraints.

Posted in SQL Server Development | Tagged , , , | 2 Comments

SQL_Variant – Real world research

I came across a table for custom data fields that went something like this…

Columns:

PrimaryKey int
EmployeeKey int
Datatype varchar(20)
intData int
varcharData varchar(250)
boolData bit
numericData float (I laughed as well)
dataData datetime

(Ugh.)

I’m looking at archiving this data for long-term storage in a data mart I’ve been asked to build. So, I figured I’d take a look at the SQL_Variant datatype to see if I generated any space savings.

I created two temp tables – one with the data from the table, with no alternations and the same structure as the existing table (I wanted to discount the size of the indexes).  The second was the data in this structure:

PrimaryKey int
EmployeeKey int
Data SQL_Variant

With a small data set (22k records), the SQL_Variant table outperformed the current format by 1,088kb to 1,360kb.

With a larger data set (1,807k records), the SQL_Variant table outperformed the current format by 86,864 KB to 107,760 KB.

At this point, I’m thinking to myself that this is looking like a pretty good research, and I turn to Google to look for best practices and the like. I find a lot of interesting thoughts on the topic – with the general opinion being “don’t bother.”

I tried another path, this time with a table that looks like this:

PrimaryKey int
EmployeeKey int
Data varchar(250) (250 is the largest field in the source data)

I repeated my test at the larger data set (1,807k records) and varchar(250) beat out SQL_Variant and Native formats by coming in at 75,088 KB.

I’m fairly certain that there’s a point where SQL_Variant will beat out a varchar approach – perhaps varchar(1000)? Someday I’ll take a look at it – but for the time being this meets my needs.

Posted in SQL Server Development | Tagged , , | Leave a comment

Getting back on the horse…

It’s been three weeks since I spent much time thinking about this site, mostly due to the distractions of starting a new gig and learning a whole new system.

I know I’m not the first to think or say… type… this, but it’s a true shock to the system when starting a new job. New code to read, procedures to adjust to, cultures to figure out, etc..

Before I started the new job, I bookmarked articles filled with advice on taking over a system, performance tuning, “the top 10 things you do when you inherit a system” and had my own list from previous experience of “gotchas” to look out for. I haven’t looked at any of it yet! Someday I’ll get to that list and someday I’ll get to writing about it. It might take a few years though…

Posted in Uncategorized | Tagged , , | Leave a comment

Two Weeks Notice & New Employment

I recently accepted a new position at another organization. You can check it out on my resume online. It’s been a bit of a whirlwind experience for me, since my former employer had done a lot for me over the years.

I think, in time, I’ll write up something with a bit more detail explaining my experiences – what worked for me, and what didn’t.

I’m planning on getting back on my schedule of once a week shortly.

Posted in Uncategorized | Leave a comment

Shrink Database and “free” code

I had a problem at work today resulting from running some “free” code. When I finally found the culprit I knew that I had a topic to write about, and upon arrival home I came here to write a note about Shrink Database and why it’s generally bad voodoo. As I gathered the appropriate links and accumulated my thoughts, I fired up Google Reader and noticed a post from Brent Ozar titled: Stop Shrinking Your Database Files. Seriously. Now.. Well, I have to say it took the wind out of my sails since he was able to articulate it better than me.

So, I’ll be brief. If you started testing SQLStat2005, and you’re having performance issues on the server running the software, check out this stored procedure inside the database he provides: [miracle].[uspSQLSTAT2005_Cleanup]. Edit the sproc and comment out the shrinkdb command.

Now, go read Brent’s post, and check out all the other articles he linked.

Posted in SQL Server Performance | Tagged , | Leave a comment

Tooting my own horn: Pass Blog Directory

Not that it’s the largest accomplishment in the world, but I was recently added to the PASS Blog Directory.

I have been trolling that list for tips and tricks to use in our production environment at work long before I started writing ideas here, but recently an announcement was made for a group on LinkedIn that was open to SQL bloggers only. This is what motivated me to request an addition to the list, so that I could join the group to further my networking opportunities.

If you’re reading this and have a blog and aren’t on the list already, I suggest getting it added to get your name and ideas out there.

Posted in Uncategorized | Leave a comment

Wildcard Searching

Searching for data with a less-than-desirable amount of instruction, or a vague “it ends in smith” is never fun.

However, there are of course a variety of tools that are available to the developers in the world of T-SQL.

As a self taught developer, a seemingly long time ago I found out about the % wildcard.

For example:

SELECT * FROM Person WHERE LastName LIKE '%smith'

returns every record that has a last name that ends in smith.

Another useful bit of syntax that I picked up along the way uses square brackets.

For example:

SELECT * FROM Employee WHERE SSN LIKE '[0-9]34%'

returns a list of employees who’s SSN’s start with “uh, something-three-four” (actual instructions provided to me recently).

Very recently, I was typing an instruction in and tried to find an SSN that was 334-% and ended up with a happy accident caused by typing too fast.

My code that I executed was:

SELECT * FROM Employee WHERE SSN LIKE '334-_%'

and I didn’t get an error. I expected a wacky result, but it turns out the underscore character is also a wildcard. So, my query above where I was trying to find “something-three-four” could have been written as:

SELECT * FROM Employee WHERE SSN LIKE '_34%'

and I would have gotten the same result.

I’m putting this out there as a helpful tip to any other self-taught DBA that never knew about the helpful underscore wildcard. If anyone else has any text manipulation / search tricks, feel free to leave a comment.

Posted in SQL Server Development | Tagged , | Leave a comment