SQL Server Tips & Tricks

SQL Server 2005 SP3 now available for download

noreply@blogger.com (Aneesh) — Wed, 17 Dec 2008 01:37:15 PST

SQL Server 2005 SP3 is now available for download and includes the following updates: Roll-up of Quick Fix Engineering (QFE) updates completed since SQL Server 2005 SP2, as well as fixes to...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

New Features SQL Server 2008 Part 1

noreply@blogger.com (Aneesh) — Mon, 15 Dec 2008 07:40:18 PST

In Microsoft SQL Server 2008, Microsoft introduces some new features and enhanced some of the existing ones. These changes can be classified into several groups via the database engine, t-sql,...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Query to return list of Dynamic Management Views

noreply@blogger.com (Aneesh) — Wed, 03 Dec 2008 04:18:45 PST

SELECT * FROM sys.all_objectsWHERE [name] LIKE '%dm_%' AND [type] IN ('V', 'TF', 'IF') AND [schema_id] = 4ORDER BY [name]

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

xType on syscolumns

noreply@blogger.com (Aneesh) — Fri, 08 Aug 2008 12:13:07 PDT

Unlike 'xType' in sysObjects, you wont be able to find what exactly those values mean in syscolumns table as in the BOL it is written mentioned as 'For Internal purpose'. The sysstem table which...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Tables without Primary Keys

noreply@blogger.com (Aneesh) — Thu, 31 Jul 2008 00:35:34 PDT

this script will be useful when you are configuring the trans. replicationSELECT *FROM information_schema.tablesWHERE table_type = 'base table' AND OBJECTPROPERTY(OBJECT_ID(table_name),...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Wien [Webshots]

Fri, 27 Jun 2008 07:48:31 PDT

my Wien trip

by aneeshattingal
see more photos from this album (156) ...

Deleting duplicate records

noreply@blogger.com (Aneesh) — Thu, 07 Feb 2008 23:41:29 PST

This is one of the common stuffs that every developer has to face / faced. In this post, I will explain three different methods of idenifying and deleting the duplicate records; make sure that you...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

"Black-Box" capability of SQL Server

noreply@blogger.com (Aneesh) — Fri, 14 Dec 2007 09:57:23 PST

This is one of the hidden features of SQL Server, by enabling this feature, sql server records all the traces running behind the scenes and in case, the server crashes, will record it into a file....

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

SELECTing the NUMERIC Values from a Column

noreply@blogger.com (Aneesh) — Wed, 31 Oct 2007 03:00:34 PDT

I saw several people asking this question in several forums. I will explain three different methods of doing this. 1. ISNUMERIC() The first method uses the built in 'ISNUMERIC()' function....

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Exception Handling using Try..Catch

noreply@blogger.com (Aneesh) — Fri, 03 Aug 2007 00:03:08 PDT

Even though @@Error is good for handling errors, it cannot handle exceptions, indeed in sql server 2000, there are no ways to do this. In SQL server 2005, Microsoft introduced a new feature “try...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Error Handling in SQL Server

noreply@blogger.com (Aneesh) — Fri, 03 Aug 2007 00:03:08 PDT

Error Handling is one of the most important topics in SQL Server. The traditional way of handling errors in SQL Server is by checking the value of @@Error. When SQL Server completes the execution of...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

An Introduction to Transactions

noreply@blogger.com (Aneesh) — Fri, 03 Aug 2007 00:03:08 PDT

Transaction is a single unit of work. Usually a transaction contains, more than one T-SQL statements; the basic concept of transaction is “Either all the statements are successful or none”. This is...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

An Introduction to Stored Procedures

noreply@blogger.com (Aneesh) — Sun, 22 Jul 2007 08:33:47 PDT

There are mainly two ways by which you can store a batch of statements in SQL Server, Procedures and functions. Both are almost similar in structure, with few differences. In this section, I am...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component

noreply@blogger.com (Aneesh) — Sun, 22 Jul 2007 08:34:08 PDT

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Number of words in a string

noreply@blogger.com (Aneesh) — Sun, 22 Jul 2007 08:34:08 PDT

In sql server there is not any built-in function available for finding the No. of words in a String. Here I show you two different approaches for doing this, the first one is the easiest one, and is...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Setup Default value from another table

noreply@blogger.com (Aneesh) — Sun, 22 Jul 2007 08:34:08 PDT

By default, SQL server won't allows you to set default value from another table, and I got few queries on how to perform this. You can do this with the help of Triggers. Although I won't suggest...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Moving the Database files from One location to another

noreply@blogger.com (Aneesh) — Wed, 27 Jun 2007 09:16:52 PDT

In Sql server 2005, you can do this easily using the "ALTER DATABASE .. MODIFY FILE" statement For testing purpose, I am creating a Database Named "DBtest2" and changing the Database log file 1:...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

SQL Server 2005 Installation Step by Step

noreply@blogger.com (Aneesh) — Mon, 25 Jun 2007 07:53:54 PDT

I found lots of queries in different forums regarding the installation of SQL Server 2005. If you don't have any other instances of SQL server running on the machine, the usual "Next -> next ... "...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Under the default settings SQL Server does not allow remote connections

noreply@blogger.com (Aneesh) — Mon, 25 Jun 2007 07:54:03 PDT

It is quite often that when you try to connect to an instance of SQL Server 2005 form a remote machine, you may receive the following error message. "An error has occurred while establishing a...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Katmai - CTP released

noreply@blogger.com (Aneesh) — Thu, 14 Jun 2007 07:04:38 PDT

Microsoft has released its much-awaited SQL Server 2008 (known popularly as “Katmai”) CTP version that is designed to meet the Data Platform vision of the Microsoft. The SQL Server 2008 capabilities...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

My first .Net Stored Procedure

noreply@blogger.com (Aneesh) — Sat, 26 Jul 2008 05:22:46 PDT

This is my first attempt to use the .Net feature of Yukon. I tried this on the CTP versions of both SQL server 2005 and Visual Studio 2005; both are available on microsoft websiteThis program will...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

New Features - Pivot and Unpivot

noreply@blogger.com (Aneesh) — Thu, 14 Jun 2007 06:40:13 PDT

PIVOT And UNPIVOT If you have ever worked with Microsoft Access, you might have used the TRANSFORM statement to create a crosstab query. Even though the similar functionality was missed in sql...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

New Features - Common Table Expressions (CTEs)

noreply@blogger.com (Aneesh) — Thu, 14 Jun 2007 06:40:13 PDT

This is one of the most exciting features introduced in SQL Server 2005. A CTE is nothing but a “temporary result set" which is derived from a simple query and defined within the execution scope...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Enhancements of existing features in 2005

noreply@blogger.com (Aneesh) — Thu, 14 Jun 2007 06:40:13 PDT

The Microsoft has made some significant improvements for some of the existing features of SQL server 2000 in SQL Server 2005. One of the most important enhancements was done for the ‘TOP ‘Clause....

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Searching Unicode values in a Non-Unicode column

noreply@blogger.com (Aneesh) — Tue, 10 Jul 2007 01:24:51 PDT

Take care when using Unicode data in your queries, as it can affect query performance. A classic problem is related to an application passing in Unicode literals, while the column searched in the...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to Move a Database Diagram in SQL Server 2000

noreply@blogger.com (Aneesh) — Tue, 10 Jul 2007 01:24:51 PDT

Method 1 : Using Enterprise manager1. Expand the Databases folder under Enterprise Manager.2. Right-click the database containing the diagram you wish to copy and select "All Tasks" and "Export...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]