MaasSql

CTE, Invalid Object, Swallowed Error - Is this a bug in MS Sql Server? You decide.

Jeff Maass — Sat, 26 Jun 2010 15:42:45 GMT

I didn't discover this, a person on StackOverflow did. Just read the question and did some thinking. If you are interested, then please read the question and potentially comment @ StackOverflow.

I've never submitted anything to Microsoft Connect. Do you think that this qualifies? It would certainly seem if not a bug, then a severe annoyance if it ever happened in my code.

PASS 2008 inexpensive room

Jeff Maass — Sun, 21 Sep 2008 22:17:44 GMT

FYI, if you are attending 2008 Pass, and are on your own dime or saving your employer money, it may be possible to get rooms at the La Quinta right next to the convention center right at $100.

La Quinta Inn and Suites Seattle Downtown
2224 8th Avenue
SEATTLE, WA 98121 US

If you don't know about the PASS Community summit, you should. Visit http://summit2008.sqlpass.org/ for details.

Milliseconds to Detail

Jeff Maass — Mon, 25 Feb 2008 13:03:39 GMT

I use this function for testing. It takes in milliseconds and spits out the whole number value in various other rulers (seconds, minutes, etc).

USE MaasSql

GO

USE [MaasSql]
GO
/****** Object:  UserDefinedFunction [cCode].[DateTime_GetUtcOffset]    Script Date: 02/25/2008 07:01:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cCode].[MilliSeconds_ToDetail]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    BEGIN
        DROP FUNCTION cCode.MilliSeconds_ToDetail
    END

GO

CREATE FUNCTION cCode.MilliSeconds_ToDetail ( @Milliseconds BIGINT )
RETURNS @Results TABLE
    (
      ID INTEGER IDENTITY(1 , 1)
                 PRIMARY KEY
    , milliseconds BIGINT
    , Seconds REAL
    , Minutes REAL
    , Hours REAL
    , Days REAL
    , Weeks REAL
    )
AS 
BEGIN

    DECLARE @MilliSecondsREAL AS REAL ;

    SET @MilliSecondsREAL = @Milliseconds ;

    DECLARE @SecondsDivisor AS REAL ;

    SET @SecondsDivisor = 1000 ;

    DECLARE @MinutesDivisor AS REAL ;

    SET @MinutesDivisor = @SecondsDivisor * 60 ;

    DECLARE @HoursDivisor AS REAL ;

    SET @HoursDivisor = @MinutesDivisor * 60 ;

    DECLARE @DaysDivisor AS REAL ;

    SET @DaysDivisor = @HoursDivisor * 24 ;

    DECLARE @WeeksDivisor AS REAL ;

    SET @WeeksDivisor = @DaysDivisor * 7 ;

    INSERT INTO
        @Results
        (
          milliseconds
        , Seconds
        , Minutes
        , Hours
        , Days
        , Weeks

        )
        SELECT
            @MilliSecondsREAL
        ,   @MilliSecondsREAL / @SecondsDivisor seconds
        ,   @MilliSecondsREAL / @MinutesDivisor minutes
        ,   @MilliSecondsREAL / @HoursDivisor hours
        ,   @MilliSecondsREAL / @DaysDivisor days
        ,   @MilliSecondsREAL / @WeeksDivisor weeks

    RETURN

END

GO

DTS Script out - SQL Server 2000

Jeff Maass — Mon, 25 Feb 2008 12:55:56 GMT

My preferred method of persisting a DTS package is by saving it to the server and backing up MSDB. At one of my employers, I found many filepaths and other should be variables hardcoded in. Not one week passed before someone said, what’s making this file? To find the creator of the file, I needed a way to GREP their DTS packages. I found this neat article on SQL Server Central. http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1613.

I run it as a .vbs file, and start an instance of it for every server, using a quick and easy batch file such as:
“\\Fileserver-4\DTS\Script Out DTS Packages-CmdArgs.vbs” /server:SQLSVR2 /backupfolder:”\\Fileserver\DTS”

“\\Fileserver-4\DTS\Script Out DTS Packages-CmdArgs.vbs” /server:SQLSVR2 /backupfolder:”\\Fileserver\DTS”

One possible use is for easy change control via a text file diffing.

Cleaning a Phone Number with a Function

Jeff Maass — Mon, 25 Feb 2008 12:54:36 GMT

Not much more to say other than, taking the resources - CPU, Memory, Time to loop through the characters in a phone number and retrieving all #’s is better than crap. Don’t blame me, blame the designer who allowed a phone number to be stored as varchar with no constraints.

Oh, and maybe since we’re talking varchar, you can’t get crazy and replace | with 1 and Oo with 0 and all that other stuff we DBA’s LOVE to do. Crap is crap.

When I looked on the web, I found this post and liked the last posted script the best: http://sqljunkies.com/Forums/ShowPost.aspx?PostID=11222

DTS Logging - SQL Server 2000

Jeff Maass — Mon, 25 Feb 2008 12:54:01 GMT

It’s been a while since I used DTS. I was trying to remember the right way to do logging in DTS. First, I just said - hey, I’ll do a File System Object File. Then, I said to myself, no way, that’s bogus, there has got to be an easier way…… So I found this:DTSPackageLog. What’s funny is that the DTSPackageLog may or may not be available. You have to test it for null or nothing prior to attempting to use it. I soo hate that crap. It makes no sense to me. I used to feel bad about not understanding these sorts of ideas, until a good friend told me to take it easy on myself. I think what he said was - “It’s not you, it just doesn’t make sense.” Long story short, DTSPackageLog = sounds neat, but long term plan = fso writing to a reliable ( existing ) temp folder.

http://msdn2.microsoft.com/en-US/library/aa936769(SQL.80).aspx

http://www.ureader.com/message/1200664.aspx

Sql Server Management Studio Slow

Jeff Maass — Mon, 25 Feb 2008 12:53:07 GMT

I really dug this explanation of the “slowness” in Management Studio. http://blogs.msdn.com/euanga/archive/2006/07/11/662053.aspx

This is the “official” kb article:

http://support.microsoft.com/kb/555686

Easy Fix - Low Hanging Fruit - Get Em 'for they're hot.

Jeff Maass — Wed, 20 Feb 2008 14:21:24 GMT

I am of the opinion that every table should have a clustered index. Also, I recently saw somewhere ( maybe SqlTeam, I don't remember ) a quote from Joe Celko, "You don't have a table unless you have a key." Which totally sounds like something he would say. BTW, I got to meet him @ Pass one year. Dude is wicked smart. Wicked.

Done with the name dropping.

====================================
Basic idea:
You have a poorly performing database. You can't / don't have time to re-write all of the stored procedures or many of them or whatever. Code can't change right now. Run this script, find the tables without Clustered indexes and primary keys and start planning.

Clustered indexes allow SQL Server to work more efficiently. Primary keys support the use of foreign keys. Data integrity is sooo sexy. Do say this to your boss. They will love it and promote you.

Things to keep in mind:

Creating / changing a clustered index on a table means that EVERY row in the table must be moved. Bad news: a BIG table will take a LONG time. Good news: a BIG table will show a HUGEr performance improvement.
read up on clustered indexes and primary keys before beginning. While they are often set on the same column or set of columns, they are not the same thing.
Primary keys by definition are on unique columns. Consider this: If you aren't sure, I mean 100% positive, that the columns you have chosen for your primary key will be unique, you are probably going to break something. If there is data in the table, then the creation of the key will not be successful. But perhaps you are placing the key on a table which at the moment is empty. Then the application which has been rolling along for the last 2.5 years goes cablooey trying to insert. Be careful. ( Yet another good example of why designing first, then coding saves lives ).

====================================
Script:

USE master

go

IF NOT EXISTS ( SELECT TOP 1
                    1
                FROM
                    sys.databases
                WHERE
                    name = 'MaasSqlTest' ) 
    BEGIN
        CREATE DATABASE MaasSqlTest
    END

GO

USE MaasSqlTest

GO

IF ( OBJECT_ID('dbo.TablesWithoutPKorCL') IS NULL ) 
    BEGIN
        --YEUP, if the script is successful, this table WILL show up in itself
        create TABLE [dbo].[TablesWithoutPKorCL]
            (
              [DBName] [nvarchar](128) NULL
            , [SchemaName] [sysname] NOT NULL
            , [TableName] [sysname] NOT NULL
            , [HasUniqueCnst] [int] NULL
            , [HasClustIndex] [int] NULL
            , [HasPrimaryKey] [int] NULL
            , IFixedThis BIT DEFAULT(0)
            , ImNotAllowedToFixThis BIT DEFAULT(0)
            , ThisCantBeFixedNow BIT DEFAULT(0)
            , UTCDateEntered DATETIME DEFAULT ( GETUTCDATE() )
            )
        ON  [PRIMARY]

    END
GO


GO

DECLARE @SQL NVARCHAR(MAX) ;

SET @SQL = '
INSERT INTO [MaasSqlTest].[dbo].[TablesWithoutPKorCL]
           (
            [DBName]
           ,[SchemaName]
           ,[TableName]
           ,[HasUniqueCnst]
           ,[HasClustIndex]
           ,[HasPrimaryKey]
            )
select
DB_Name() DBName
, S.name SchemaName
, T.name TableName
, OBJECTPROPERTY( object_id, ''TableHasUniqueCnst'') HasUniqueCnst
, OBJECTPROPERTY( object_id, ''TableHasClustIndex'') HasClustIndex
, OBJECTPROPERTY( object_id, ''TableHasPrimaryKey'') HasPrimaryKey
from sys.tables T
JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE OBJECTPROPERTY( object_id, ''TableHasPrimaryKey'') = 0
OR OBJECTPROPERTY( object_id, ''TableHasClustIndex'') = 0

'
print @SQL
DECLARE @ExecuteWhere NVARCHAR(MAX) ;
SET @ExecuteWhere = '{@DBName}.dbo.sp_executeSQL @stmt = N''{@SQL}''' ;
SET @SQL = REPLACE(@SQL , '''' , '''''') ;
SET @ExecuteWhere = REPLACE(@ExecuteWhere , '{@SQL}' , @SQL) ;

DECLARE @dbs TABLE ( DBName sysname )
INSERT INTO
    @dbs ( DBName )
    select
        name
    from
        sys.databases
    where
        name not like 'Staging%'
DECLARE @DBName sysname ;
DECLARE @ExecuteWhere_DBName NVARCHAR(MAX) ;
while exists ( select top 1
                1
               from
                @dbs )
    BEGIN
        SELECT TOP 1
            @DBName = DBName
        FROM
            @dbs
        SET @ExecuteWhere_DBName = REPLACE(@ExecuteWhere , '{@DBName}' , @DBName) ;

        PRINT @ExecuteWhere_DBName ;

        EXECUTE ( @ExecuteWhere_DBName ) ;

        DELETE
            @dbs
        WHERE
            DBName = @DBName
    END

select *
from [MaasSqlTest].[dbo].[TablesWithoutPKorCL]

Why Views are evil.

Jeff Maass — Tue, 12 Feb 2008 00:28:54 GMT

Synopsis: Views are evil, bad, buggy, temperamental, tortuous, and should be avoided.

Years ago, when I started out as a wee developer graduating from Microsoft Access, I tried and did port my favorite functionality of Access to Sql Server 2000. The idea was simple, take some complicated table joins and hide all of those complicated relationships. In MS Access, that's called a Query. In Sql Server, it is called a View.

In my enthusiasm, I told the report developer, "No more duplicating the same joins across all those stupid stored procedures! Do it the right way, centralize that code up into a view. See, how easy this is?"

I was sooooooo smart, everyone admitted it, except the CIO at raise time ;). Um, until about a year later, when that same report writer came to me saying that the reports with views weren't running so well. The why, I still don't know. I honestly didn't have time to find out. Though to be honest with myself and you, I had noticed an trend with the views which was already making me queasy. As time progressed, tables were added. Lots of tables. And since, surprise, surprise, the database was reasonably well de-normalized, and we were just making complicated things simple, we added LOTS of tables to those darned views.

So, here is why I say views are evil:

I've historically been burned by them. Is that not reason enough?
sp_refreshview --- http://technet.microsoft.com/en-us/library/ms187821.aspx, google terms: SQL2005 BOL sp_RefreshView. Can I have an Amen to anyone who learned this the hard way?
Views hide complexity by hiding joins. It is as simple as that. Joins are a major cause of performance problems. That hidden simplicity encourages poor T-SQL programming practices. What I frequently have seen is select X_column1 from viewXY group by X_column1. Looks fine to me, to you, to everyone. Wrong! The view says: Select X_column1....Y_column50 from tableX X join tableY Y on X.xID = Y.XID. A simple select from the tableX which has a unique index on column1 would have done the trick and done the trick efficiently!
One must work especially hard to make a view updatable/insertable.

What about indexed views you ask? Well, I have an answer!

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx . Follow along with me under:

Benefits of Using Indexed Views

"Analyze your database workload before implementing indexed views."

Hmmmm. A little further down.

"Applications that benefit from the implementation of indexed views include:

• Decision support workloads

• Data marts

• Data warehouses

• Online analytical processing (OLAP) stores and sources

• Data mining workloads"

Whew, finally!

"On the contrary, online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables."

oh, oh, here comes my point:

"Identifying an appropriate set of indexes for a database system can be complex."

So, my answer(s). A) Views and indexed views are not the same thing. B) Indexed views may help, but ONLY if you are willing to spend quite a bit of time helping them help you. In my experience, sadly in most development shops, time isn't given for such labor to occur. C) I work primarily on OLTP systems.

=====================

More reading on views:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx Not all editions of support Indexed views the same.

http://sql-server-performance.com/Community/forums/p/7447/43319.aspx Adrian's explanation was wonderful.

http://www.sql-server-performance.com/article_print.aspx?id=154&type=tip Tips on creating effective indexed views.

http://www.sql-server-performance.com/tips/views_general_p1.aspx Oh look, someone already beat me to it. There is nothing new under the sun. I read this AFTER I wrote most of this blog. AFTER.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220_tax301334,00.html?adg=301324&bucket=ETA#views Nuff said.

=====================

Conclusion:

Honestly, after writing this post, I'm starting to think maybe I should reevaluate just where I could use indexed views. Since I live an ignorant life, I had written them off without investigating them more thoroughly. I'm thinking, investigate, recommend, implement, get noticed by the CIO, get a raise.....

=====================
PS
2008-02-20

=====================

Scripts:
I was asked to show what was said in above post was accurate in SQL 2005. Feel free to copy, paste, read, and then run the code below.

/**==============================================================================================================================================================================================
--#region Setup Test Schema**/


USE master

go

IF NOT EXISTS ( SELECT TOP 1
                    1
                FROM
                    sys.databases
                WHERE
                    name = 'MaasSqlTest' ) 
    BEGIN
        CREATE DATABASE MaasSqlTest
    END

GO

USE MaasSqlTest

GO

IF ( OBJECT_ID('dbo.ViewTest_Detail') IS NOT NULL ) 
    BEGIN
        DROP TABLE dbo.ViewTest_Detail
    END
GO

IF ( OBJECT_ID('dbo.ViewTest_Group') IS NOT NULL ) 
    BEGIN
        DROP TABLE dbo.ViewTest_Group
    END
GO

CREATE TABLE dbo.ViewTest_Group
    (
      ViewTest_Group_ID INT IDENTITY(1 , 1)
                            PRIMARY KEY CLUSTERED
    , name NVARCHAR(50) UNIQUE
    )

GO

CREATE TABLE dbo.ViewTest_Detail
    (
      ID INTEGER IDENTITY(1 , 1)
                 PRIMARY KEY CLUSTERED
    , ViewTest_Group_ID INTEGER
    , DateEntered DATETIME DEFAULT ( GETUTCDATE() )
    , name NVARCHAR(50) 
      FOREIGN KEY ( ViewTest_Group_ID ) REFERENCES dbo.ViewTest_Group ( ViewTest_Group_ID )
    )

GO

IF ( OBJECT_ID('dbo.ViewTest_GroupDetail') IS NOT NULL ) 
    BEGIN
        DROP VIEW dbo.ViewTest_GroupDetail
    END

GO

CREATE VIEW dbo.ViewTest_GroupDetail
AS  SELECT
        G.ViewTest_Group_ID
    ,   G.name GroupName
    ,   D.name DetailName
    ,   D.DateEntered DetailTime
    FROM
        MaasSqlTest.dbo.ViewTest_Group G
        JOIN MaasSqlTest.dbo.ViewTest_Detail D
            ON G.ViewTest_Group_ID = D.ViewTest_Group_ID

GO

/**
--#endregion Setup Test Schema
==============================================================================================================================================================================================**/

/**==============================================================================================================================================================================================
--#region Populate our new tables**/

GO
SET NOCOUNT ON ;

DECLARE @cntr INTEGER ;
SET @cntr = 1 ;

WHILE @cntr <= 10
    BEGIN
        INSERT INTO
            dbo.ViewTest_Group ( name )
        VALUES
            (
              'GroupID = ' + CAST(@cntr AS NVARCHAR(50))
        )
        SET @cntr = @cntr + 1 ;
    END

SET @cntr = 1 ;

WHILE @cntr <= 10
    BEGIN
        INSERT INTO
            dbo.ViewTest_Detail
            (
              ViewTest_Group_ID
            , name 
        )
            SELECT
                G.ViewTest_Group_ID
            ,   'Detail=' + G.Name
            FROM
                dbo.ViewTest_Group G

        SET @cntr = @cntr + 1 ;
    END

GO

/**
--#endregion Populate our new tables
==============================================================================================================================================================================================**/


/**==============================================================================================================================================================================================
--#region Test out various queries using use "Display Estimated Execution Path" button**/
BEGIN

    --run query plan on this one
    SELECT
        *
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail

    --now run query plan on this one
    SELECT
        ViewTest_Group_ID
    ,   GroupName
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail
    GROUP BY
        ViewTest_Group_ID
    ,   GroupName

    --maybe the group by caused both tables to be accessed, so run query plan on this one
    SELECT
        ViewTest_Group_ID
    ,   GroupName
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail

    --can we make it more efficient?
    SELECT
        ViewTest_Group_ID
    ,   GroupName
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail
    WHERE
        ViewTest_Group_ID = 1
            
    /**==============================================================================================================================================================================================
    --#region Highlight between the two ==== lines**/
    BEGIN
        --Group by via the view
        SELECT
            ViewTest_Group_ID
        ,   GroupName
        FROM
            MaasSqlTest.dbo.ViewTest_GroupDetail
        GROUP BY
            ViewTest_Group_ID
        ,   GroupName


        --Get rid of the view.  Query the underlying table explicitly..
        SELECT
            ViewTest_Group_ID
        ,   Name GroupName
        FROM
            MaasSqlTest.dbo.ViewTest_Group
        GROUP BY
            ViewTest_Group_ID
        ,   Name
    END
    /**
    --#endregion Highlight between the two ==== lines
    ==============================================================================================================================================================================================**/

END
/**
--#endregion Test out various queries using use "Display Estimated Execution Path" button
==============================================================================================================================================================================================**/

My momma always said share.

Jeff Maass — Mon, 11 Feb 2008 03:17:12 GMT

My momma always told me to share. Thankfully, sharing isn't always a two way street.

Thankfully, because, I've never been great at it. I used to feel guilty every time I used the Internet to solve a problem at work. After all, weren't they paying "me" not "them" to sweat?

Long story short, Internet Usage Guilt ( IUG ) is no longer on my medical chart. In fact, I'm hoping to not just continue my Internet usage, but to start on that two way street of giving. My mom would be proud. If she knew what the Internet is.

It's been said that as you give, so you will receive. Before even posting once on SqlTeam, I've been given some great news I longed for over many days: http://www.ssmstoolspack.com. Mladen, going by the handle Spirit1 here on SqlTeam, has RedGate-ish software that I'm jumping to try. Reading the features list makes my mouth salivate with glee! I will of course be PayPal-ing, as soon as the next PayCheck arrives....

So basically, I hope this blog will serve as my two way payback for all the years of sharing on SqlTeam.