Archive for the ‘SQL’ Category

Microsoft has penetrated in banking sector and joined hands with the largest core banking software called Temenos, and tested its availability on SQL Server 2008 R2 so now you can run core banking software on SQL Server. Microsoft has tested theT24 environment with windows server with SQL Server 2008 R2 and done successfully this testing using 25 million accounts with 15 million customers over 2000 branches.
Microsoft team test results:
Task                     Average     SQL-T24
Funds transfers     500,000     697,920
Security trades     240,000     323,534


Read Full Post »

Collation precedence conflict in SQL Server 2000

The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
link to article on msdn
A simple example is as follows

create table #TempRO
Id  int Identity (1,1),
App_No     NVarchar (100)
— insert some values in this table
Select *
from #TempRO T
Inner join App AO
On T.App_No = AO.App_No

We are having the App table using a different collation then the default that is “Arabic_CI_AS”

So the above select will give the following collation error, to remove it we just add the following statement while defining the temp table

create table #TempRO
Id  int Identity (1,1),
App_No     NVarchar (100)    collate Arabic_CI_AS

This not only happens in temp tables but also in the normal tables or at times if we are comparing columns so its better to use sp_help TableName or the syscolumns to find the collation of the two.

Happy Collating 😉

Read Full Post »

A lot of you might have come across this problem just like me.


Firstly we create a temporary table.

create table #temp1


            id Int Identity(1,1),

            invoice varchar (20)


insert into #temp1 values (‘CR’)

insert into #temp1 values (‘CR ‘)

insert into #temp1 values (‘ CR ‘)

insert into #temp1 values (‘ CR’)


Select *  From #temp1

            where DATALENGTH(invoice) <> len(invoice)


The len function in SQL gives you the length of the characters including the spaces white DataLength excludes the trailing not preceding spaces.


Here if you don’t have the column defined as varchar rather you have an nvarchar field then you simply convert then to varchar before calling the respective functions like this.


Select *  From #temp1

            where DATALENGTH(convert(varchar(20),invoice)) <> len(convert(varchar(20),invoice))



Happy searching trailing spaces in your db, please share with us if you have better options.

Read Full Post »

If you would like to find some charater/s in a column in the table and replace it here is a simple querry.

Note: For beginner please make sure you know what an update statement does. This query is going to update the column values in your table. So if you are just testing please use the Select statement instead. I have added it to the bottome of this article.

Approach 1:
Update T
       Set T.Col1 = Replace (T.Col1, “@” , ‘at’)
       From MyTable T

But If we just want to make it a bit faster depending on the size of your table.
Approach 2:
Update T
       Set T.Col1 = Replace (T.Col1,”@”,’at’)
       From MyTable T
       Where T.Col1 like (“@”)

You can have a hang of what the Replace functions does from the following link. But I would give you a little idea over here too. The Replace function look for the string that you have provided as the second parameter in the table column or string expression  that you have provided as the first parameter and simply replaces that string with the third parameter.

*Select Replace(T.Col1,”@”, ‘at’) As [ColName]
      From MyTable T


Read Full Post »

This is one of the oldest topics that one could write on but I am trying to just relight the flame. Open to comments from all.

The table:

I am using the Products table present in Northwind Database to continue with this example. If you don’t have the above database you can download it from here:

Products Table structure:

Column Name Column Type
ProductID                    int (IDENTITY)
ProductName               nvarchar(40)
SupplierID                    int
CategoryID                  int
QuantityPerUnit            nvarchar(20)
UnitPrice                      money
UnitsInStock                smallint
UnitsOnOrder              smallint
ReorderLevel               smallint
Discontinued                 bit

Initial data in table:
Select CategoryId, ProductName  From Products

CategoryId ProductName
1          Chai
1          Chang
2          Aniseed Syrup
2          Chef Anton’s Cajun Seasoning
2          Chef Anton’s Gumbo Mix
1          Lakkalikööri
2          Original Frankfurter grüne Soße
            (77 Rows returned)

The Requirement to Display data:

CategoryId ProductsList
1                      Chai,Chang,Guaraná Fantástica,Sasquatch …
2                      Aniseed Syrup,Chef Anton’s Cajun Se …
3                      Pavlova,Teatime Chocolate Biscuits, …
4                      Queso Cabrales,Queso Manchego La Pa …
5                      Gustaf’s Knäckebröd,Tunnbröd,Singap…
6                      Mishi Kobe Niku,Alice Mutton,…
7                      Uncle Bob’s Organic Dried Pears,Tof…
8                      Ikura,Konbu,Carnarvon Tigers,Nord-O…

The solution I like the most:

I like using this user defined function named fn_GetProductNameList.

CREATE FUNCTION dbo.fn_GetProductNameList
            @CatId                        int
RETURNS VarChar(8000)
            Declare @buffer VarChar(8000)
            Select   @buffer = IsNull(@buffer + ‘,’, ”) + ProductName
                        From    Products
                        Where CategoryId = @CatId
            RETURN @buffer


Select Distinct CategoryId , dbo.fn_GetProductNameList (CategoryId)
From Products

To better understand this approach and how this select statement work you need to go through this knowledge base article.

So to produce the values in a column as a comma separated values from the same table you can have the following multiple ways as well.

1.      Another User Defined function: (fn_GetProductNameList2)

CREATE FUNCTION dbo.fn_GetProductNameList2
            @CatId Int
RETURNS VarChar(8000)
            Declare            @t Table
                        p          VarChar(40)
            Declare            @r VarChar(8000)
            Set @r = SPACE(0)
            Insert @t ( p ) select ProductName From Products
                        Where CategoryId = @CatId
            IF @@ROWCOUNT > 0
                        Update @t
                                                SET @r = @r + p + ‘,’


Select CategoryId, dbo.fn_GetProductNameList2(CategoryId)
From Products Group By CategoryId

These functions above fn_GetProductNameList and fn_GetProductNameList2 are widely used but are not recommended approach because of the fact that they rely on the physical implementation. Any changes in the indexing, statistics or else can make them unreliable.

2.      The Cursor:
CREATE FUNCTION dbo.fn_GetProductNameList3 
            @CatId                        Int
RETURNS VarChar(4000)
            Declare @ProductName Varchar(4000)
            Set @ProductName = ”
            Declare @CurVar        Varchar(255)  
            Declare Cur_ProductName Cursor
                        Select   Distinct ProductName
                        From    Products
                        Where  CategoryId = @CatId
            Open Cur_ProductName
            Fetch Cur_ProductName Into @CurVar
            While (@@Fetch_Status <> -1)
                        Set @ProductName = @ProductName + @CurVar + ‘, ‘
            Fetch Next From Cur_ProductName Into @CurVar
            Close Cur_ProductName
            Deallocate Cur_ProductName
            If(LTrim(RTrim(@ProductName)) <> ”)
                        Return Substring(LTrim(RTrim(@ProductName)), 0, Len(RTrim(@ProductName)) -1)
            Return LTrim(RTrim(@ProductName))


Select CategoryId, dbo.fn_GetProductNameList3(CategoryId)
From Products Group By CategoryId

This user defined function contains a cursor to do the magic. Its more reliable but the drawbacks of cursor on performance are know to all so they should be avoided and loops used instead.

3.      Loop inside a user defined function: (Linda Wierzbecki)
CREATE FUNCTION dbo.fn_GetProductNameList4()
RETURNS @tempProducts TABLE
            CategoryId                   Int,
            Product                        VarChar           (40),
            ListProductNames        VarChar           (8000)
            Insert @tempProducts (CategoryId, Product, ListProductNames)
            Select CategoryId, MIN(ProductName),  MIN(ProductName)
                        From Products
                        Group By CategoryId
            While ( Select COUNT(Product) From @tempProducts ) > 0
            Update TP
                        Set ListProductNames = ListProductNames + COALESCE(
                                    ( Select ‘, ‘ + MIN( ProductName )
                                                            From Products
                                                Where Products.CategoryId = TP.CategoryId
                                                                        AND Products.ProductName > TP.Product), ”),
                                        Product = ( Select MIN(ProductName)
                                                            From Products
                                                                        Where Products.CategoryId = TP.CategoryId
                                                                        AND Products.ProductName > TP.Product )
            From @tempProducts TP


Select CategoryId, ListProductNames AS Products From dbo.fn_GetProductNameList4()

This approach is also widely used.

4.      Recursive User Defined Function:
CREATE FUNCTION dbo.fn_GetProductNameList5
            @CatId Int,
            @i Int
RETURNS VarChar    (8000)
            Declare @r Varchar     (8000)
            Declare @l Varchar      (8000)
        Select @i = @i – 1,  @r = ProductName + ‘, ‘
                        From Products p1
                        Where CategoryId = @CatId
                        AND @i = ( Select COUNT(*) From Products p2
                                                Where p2.CategoryId = p1.CategoryId
                                                            AND p2.ProductName <= p1.ProductName )
        If @i > 0
              Exec @l = dbo.fn_GetProductNameList5 @CatId, @i
              Set @r =  @l + @r


Select CategoryId, dbo.fn_GetProductNameList5 ( CategoryId, COUNT(ProductName) )

From Products Group By CategoryId ;

This approach uses recursion so before applying it keep in mind that the maximum nest level in T-SQL is 32.

5.      Pivoting Method:

            Select CategoryId,
            MAX( CASE seq When 1 Then ProductName Else ” END ) + ‘, ‘ +
                    MAX( CASE seq When 2 Then ProductName Else ” END ) + ‘, ‘ +
            MAX( CASE seq When 3 Then ProductName Else ” END ) + ‘, ‘ +
                    MAX( CASE seq When 4 Then ProductName Else ” END )
            From ( Select p1.CategoryId, p1.ProductName,
                        ( Select COUNT(*)
                                    From Products p2
                                    Where p2.CategoryId = p1.CategoryId
                                    AND p2.ProductName <= p1.ProductName )
               From Products p1 ) D ( CategoryId, ProductName, seq )
     Group By CategoryId ;

Well I tried this method in my production environment and it fails or shows some abnormal behavior as the size of the table increases. It’s best to use with smaller datasets only.

So here you are with the solutions.
Which one would you use and why?


Read Full Post »

Well Microsoft has come up with really handy versions of SQl-Server. The choice of using one over the other is quite a wise decision that one should make. Here I would specify some features of SQL-Server CE and SQL-Server EE which should be considered while you make your choice between the two.

I would like to mention here that both these editions are free to download and deploy :).

SQL Server 2005 Compact Edition SQL Server Express Edition
Reasons to use SQL Server Compact Edition:
  • When you want essential relational database functionality in a compact footprint
  • Ideal for mobile and desktop applications, including occasionally-connected
  • Embeddable in applications
  • Free to download, develop, deploy, and redistribute
  • Ideal for small server deployments as well as desktop applications with more rich requirements such as full text search and powerful XML query processing.
  • When you want a seamless upgrade to SQL Workgroup, Standard, and Enterprise editions
  • Free to download, develop, deploy, and redistribute
  • Reasons not to use
  • When you want to run as a service
  • When you need a multi-user database server
  • When you need the full functionality of SQL Server
  • When the footprint and memory requirements need to be compact
  • When you want to run in process
  • When you want to target devices
  • You can get the complete document here.


    Read Full Post »

    What if you have to transfer DTS to SQL-Server 2005 running on a 64-bit machine?

    Well its a good question :).

    Most of us would simply say rewrite the DTS. Well thats too simple, isn’t it???

    I have another simpler suggestion re-write the DTS using SSIS (SQL Server Integration Services) on a 32-bit computer and compile it for 64-bit execution.

    The following link can help you install SSIS and information to use the Package Migration Wizard to leaving the original DTS packages intact can be found here.

    Happy Migrating!

    Read Full Post »

    Older Posts »