Rajib Bahar's Blog

Rajib blogs here about topics of his interest.

Is a table variable always the answer?

August 14
by Rajib Bahar 14. August 2012 00:27

I usually got good results with table variables over cursors or derived queries. I wanted to take advantage of available memories. In one of my previous project, I tried to implement an alternative to cursor via table variable. Unfortunately, it caused the SSIS process to run out of memory after running for 8+ hours. I was surprised to see it perform slower than a derived query version of that same query. It was doing a very basic data retrieval function such as selecting the data. After working with our DBA, we discovered that the server didn't have enough memory available during the time of execution (due to high user traffic). The query would slow down after processing few thousands of rows of data. It took some time to work with our colleagues to figure out the most optimal # rows the table variable can handle at any given time. Anyways, we resorted to the derived queries approach for bigger datasets.

Tags: ,

SQL

Set Operation trick to generate date data #TSQL2sDay

December 08
by Rajib Bahar 8. December 2009 17:52

Two things that happened today, which motivated me to write this post: 

1. I learned a new trick using set-operation

2. Also today is the day some of my SQL Server colleagues having #TSQL2sDay party

 

Here is the entry where I learned the trick [http://ask.sqlteam.com/questions/1206/insert-date-value-for-1-year]. 

 

I have been participating at AskSqlTeam.com lately. One of the recent question was about how to generate date for 1 year. TG had an interesting solution and I did not realize it was set-operation until Kristen set me straight. :) As usual, I was coming up with a iterative solution as opposed to set-based one. Here is the snippet I modified off of TG's code.

create table #myTable 
(dateCol datetime)
go
declare @i int
SET @i = 0
WHILE 
(
 datediff(year, dateadd(day, @i,'2010-01-01'),'2010-01-01')=0
)
begin
    insert #myTable (dateCol)
    select dateadd(day, @i, '2010-01-01')
    SET @i = @i + 1
    continue
end
go
select * from #myTable
go


The script above will create about 365 entries containing everyday of the year 2010. 

TG's answer to that problem was:

create table #myTable 
(dateCol datetime)
go
insert #myTable (dateCol)
select dateadd(day, number, '2010-01-01')
from   master..spt_values
where  type = 'P'and    number < 365
order by number
go
select * from #myTable
go

 

Anyways, my approach above is not the most ideal solution and it will be slow because of the looping. TG had the right idea. Many DB professional create a reference database for their tasks. In this database one may have scripts that can be applied on scheduled jobs and other artifacts that don't belong anywhere else. The solution above can be improved if we create a reference table containing all integers. Let's say we call that table IntValues. I started building that table today and it took more than 6 hours to enter 2 billion numbers. Here is the final draft of what that script would look like.

 

create table #myTable 
(dateCol datetime)
go
insert #myTable (dateCol)
select dateadd(day, number, '2010-01-01')
from   DbReference.dbo.IntValues
where  number < 365
order by number
go
select * from #myTable
go

Combining SMO and Powershell to Generate SQL Database Schema

December 08
by Rajib Bahar 8. December 2009 10:49

There are times we find the need to generate the database schema. In SQL Server, it can be easily done using the graphical wizards in the Management Studio. I haven't found a way to script it to this day.

 

However, one alternative solution to this is to combine .NET programmability feature in powershell, and SMO. With this approach you can setup a powershell script job to automate your team's database build process.

 

Here are some basic assumptions before reading this post:

1. SQL Server 2008 is installed

2. Powershell is installed

3. SMO is in the GAC (Global Assembly Cache) or you know how to register it there

4. AdventureWorks is loaded in the database

 

Here are the steps I took to generate script against AdventureWorks database:

 

First of all, I went to management studio and right clicked on the AdventureWorks database to "Start Powershell"

 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\1...

 

At this point, the SMO object was loaded using .NET reflection technology. Next we declare $srv variable and assign the local SQL Server 2008 instance to it. Then we assign the AdventureWorks database to the $db variable. The database object has an overloaded method namely Script(). We need to invoke that method to generate the database script. See the output below as the script is run.

 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $srv = new-object("Microsoft.SqlServer.Management.Smo.Server") "(local)\sql2k8"
PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db = $srv.Databases["AdventureWorks"]

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db

WARNING: column "Owner" does not fit into the display and was removed.

Name                        Status          Recovery Model   CompatLvl        Collation
----                           ------          --------------           ---------            ---------
AdventureWorks       Normal          Simple                 100            SQL_Latin1_General_CP1_CI_AS


PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db.Script()

CREATE DATABASE [AdventureWorks] ON  PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH =16384KB )
 LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 18432KB , MAXSIZE = 2048GB , FILEGROWTH = 1638
4KB ) COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
    EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [AdventureWorks] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [AdventureWorks] SET ANSI_NULLS ON
ALTER DATABASE [AdventureWorks] SET ANSI_PADDING ON
.
.
.

 

The limitation we run with the above approach is that it doesn't include the objects such as tables, stored procedures, and functions in the script. That's why we have to write a loop to iterate all the tables, procedures, checks, primary, functions, etc. Each of those classes have the Script() method and we can invoke them as we need it. 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> for ($i=0; $i -lt $db.Tables.Count; $i++) {$db.Tables[$i].Script()}
.
.
.
 

Yes, there are more gotchas. :(

 

So far, we have looked into resolving this issue using 1 of the overloaded Script() method. The 2nd version of the overloaded method expects ScriptingOptions as one of the parameter. Here is how we would declare them and the options they give us.

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $sc = new-object("Microsoft.SqlServer.Management.Smo.ScriptingOptions")

Here is a quick list of properties we can set on the $sc (ScriptingOptions) object.


            $sc.AppendToFile = 0;
            $sc.Bindings = 1;
            $sc.Default = 1;
            $sc.DdlBodyOnly = 1;
            $sc.DriAll = 1;
            $sc.DriAllConstraints = 1;
            $sc.DriAllKeys = 1;
            $sc.DriPrimaryKey = 1;
            $sc.IncludeDatabaseContext = 1;
            $sc.IncludeDatabaseRoleMemberships = 1;
            $sc.IncludeHeaders = 1;
            $sc.IncludeIfNotExists = 1;
            $sc.Indexes = 1;
            $sc.LoginSid = 1;
            $sc.PrimaryObject = 1;
            $sc.Permissions = 1;

 

 Depending upon your need, you can set the target server version, and the output file properties as well.

Search for data on any given database and on any column

November 18
by Rajib Bahar 18. November 2009 15:47
In some of my previous project, I had to find out whether a particular data existed on anywhere in any database. By that I mean, if I wanted to find out whether a particular data, for example, "manager" made in to any table. It's a trivial issue if you know the database table structure and column names. What if you did not have that benefit and wanted to do a massive manhunt for the data? I have written similar script such as the one below, and forgot to keep track of it in my library. Finally, my slowly deteriorating and dull memory served as a motivation for this post. The way it works is a) by getting list of all available databases, b) using dynamic sql to capture tables and column information, c) comparing sought after data against any column that can be converted to varchar type. If you need more explanation then comment please. 
 
Here is the snippet:
 

SET NOCOUNT ON

 

DECLARE @SoughtAfterValue VARCHAR(8000)

 

SET @SoughtAfterValue ='Manager'

 

DECLARE @Tmp TABLE

(

      ID INT IDENTITY(1,1)

    , DBName VARCHAR(255)

)

 

INSERT INTO @Tmp(DBName)

SELECT name

FROM sys.sysdatabases

 

DECLARE @i INT

DECLARE @total INT

 

SET @i = 1

 

SELECT @total = COUNT(*)

FROM @Tmp

 

DECLARE @sql Nvarchar(max)

 

WHILE @i <= @total

BEGIN

 

            set @sql= N'

            use ' +(

                SELECTDBName FROM @Tmp WHEREID = @i

            ) + '

           

            DECLARE @TmpQry TABLE

            (

                    ID INT IDENTITY(1, 1)

                  , Qry VARCHAR(8000)

            )

           

       

        if exists

                  ( SELECT

                  *

        FROM INFORMATION_SCHEMA.COLUMNS

            WHERE TABLE_CATALOG = '''

        +

            (

                SELECTDBName FROM @Tmp WHEREID = @i

            )

        + '''

            )

            BEGIN

 

            INSERT INTO @TmpQry (Qry)

        SELECT

                  ''SELECT CAST('' +COLUMN_NAME + '' AS VARCHAR(8000))[Result] '' +

                ''FROM '

            +

            (

                SELECTDBName FROM @Tmp WHEREID = @i

            )

                  + '..' + ''' + TABLE_NAME  

                  + '' WHERE CAST('' +COLUMN_NAME + '' AS VARCHAR(8000)) like ''''%' +@SoughtAfterValue + '%''''''

                   

        FROM INFORMATION_SCHEMA.COLUMNS

            WHERE DATA_TYPE NOT IN

            (

                  ''binary'',''varbinary'', ''image'', ''geography'', ''geometry'', ''timestamp'',

                  ''xml'',''hierarchyid'', ''sql_variant''

            )

 

            END

 

            DECLARE @i INT

            DECLARE @Total INT

            DECLARE @CurrentQryVARCHAR(8000)

            DECLARE @CurrentQry2VARCHAR(8000)

           

            SET @i = 1

 

            SELECT @Total = COUNT(*) FROM@TmpQry

           

            WHILE @i <= @Total

            BEGIN

                 

                  SELECT @CurrentQry = Qry     

                  FROM @TmpQry WHERE ID =@i

           

                  SET @CurrentQry2 = ''ifexists('' + @CurrentQry +  '') begin select ''''''+ REPLACE(@CurrentQry,'''''''', '''''''''''') + '''''' [Query Ran] '' + @CurrentQry + '' end''

 

                  exec(@CurrentQry2)

 

                  SET @i = @i + 1

 

                  CONTINUE

            END

           

      '

 

exec(@sql)

 

      SET @i = @i + 1

      CONTINUE   

END

  

ConnectionStrings - a tribute

September 29
by Rajib Bahar 29. September 2009 07:25

I use the ConnectionStrings.com site many times. Whehter it's MS SQL server or Oracle or MySql, they have the useful snippet of code for the connection string. It usually saves me time from looking up the manual. I thought I would dedicate this entry just for them.

 

If you don't know what connection string is then you are lucky to be reading this entry. In short, connection string is what defines how we connect to data sources and destinations. It enables application to talk to the database so that information is persisted in the RDBMS or target data structure. 

 

A sample connection string may look like this... (Yes... I borrowed the connection string below from their site :) )

 

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

TSQL Cheatsheet - Need more to build on this list

September 01
by Rajib Bahar 1. September 2009 07:31

I use these cheatsheets related  to TSQL and/or SQL server from time to time.

 

I can not count on my memory, but, it would be nice to have a photographic one.

 

Enough of my rambling, here is the list:

TSQL Cheatsheet found at Scribd - http://www.scribd.com/doc/399147/TSQL-Cheat-Sheet

SQL Server Cheatsheet found at Scribd - http://www.scribd.com/doc/15235350/SQL-Server-TSQL-Cheatsheet

Dave Pinal or SQL Authority's Cheatsheet - http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdf 

 

Did I miss any good ones? I'll update this 

Tags: , ,

SQL

Kevin Kline's blog entry on code of ethics for DBAs

April 24
by Rajib Bahar 24. April 2009 09:52

I noticed an interesting blog entry by Kevin Kline. He is talking about code of ethics for DBA professionals. I thought some of us may find it relevant.

 Enjoy the dialogue and debate at [http://sqlblog.com/blogs/kevin_kline/archive/2009/04/21/is-it-time-for-a-professional-code-of-ethics-for-dbas.aspx]

useful blog entries on Joins... using join in update or delete statement

March 26
by Rajib Bahar 26. March 2009 07:00

CSV Customization in SSRS Subscription

February 20
by Rajib Bahar 20. February 2009 04:47

I will appreciate if my colleagues and peers would consider looking at this thread I opened at SQL Server Central [http://www.sqlservercentral.com/Forums/Topic661376-1063-1.aspx]. Thanks and have a good day!

 

Update [10/01/2009]:

Thanks to Luke at [SQLServerCentral.com] forum I was able to find the answer. He pointed me to a MSDN article, which in turn pointed to this article [http://msdn.microsoft.com/en-us/library/ms156281.aspx]. That helped me figure out how to do it. If I have time then I'll produce steps on how to reproduce it later.

 

 

SQL Server Interview questions - Submit yours

January 26
by Rajib Bahar 26. January 2009 06:44
I get a lot of questions from my peers about finding a good place to get SQL server interview questions. I have had the privilege of being both the interviewor and interviewee. So, I thought I'd put together a collection on my blog. I look forward to hearing your comments. Please post your favorite or most challenging questions on SQL server.

Here is the list of links to blogs and/or websites that I found useful: