Update different rows on different conditions in SQL Server

Here I will show you how to write an UPDATE statement in SQL Server to update different rows on different conditions.

Following UPDATE sql statement updates salaries of the Employee table, giving 20% raise to the employees having salary less than or equal to 5000, 10% raise to the employees having salary between 5000 and 10000, 5% raise to the employees having salary between 10000 and 20000, and no raise for the employees with salary greater then or equal to 30000.

UPDATE EMPLOYEE
SET Salary = CASE
                WHEN Salary <= 5000 THEN Salary* 1.20
                WHEN Salary <= 10000 THEN Salary * 1.10
                WHEN Salary <= 20000 THEN Salary * 1.05
                ELSE Salary
            END

Generate database scripts in SQL Server

SQL Server gives a very useful tool ‘Database Publish Wizard’ that can be used to script and publish SQL Server database. For SQL Server 2005 installation, this tool is usually located at [Program Files]\Microsoft SQL Server\90\Tools\Publishing\SqlPubWiz.exe. This tool has both command line as well as GUI interface.

Here I am going to show some sample command line queries that can be used to generate database scripts. For the sake of example, let us assume that database name is “MyDatabase” which is hosted on SQL Server instance “SQLEXPRESS” on the local machine.
Following command will give you script for schema (not data) of the database. The script is output to a text file C:\Script.txt.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -schemaonly -f C:\Script.txt

Option “-S” specifies the SQL Server instance. Options “-d” specifies the database to script. Options “-schemaonly” specifies that we want to script only schema. Options “-f” specifies the output file.
SqlPubWiz by default works in windows authentication mode. So if your Server does not allow for windows authentication, you will also need to specify user name and password with “-U” and “-P” option respectively.

In the same way as above command, following command will generate script for all the data of the database. It will not contain any schema script.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -dataonly -f C:\Script.txt

We can even use Management Studio to generate database scripts (Right click database, select Task and then select Generate Scripts). However, there is no options in management studio to generate “dataonly” scripts in which case this tool comes handy.

Get all the Primary Keys of a table in SQL Server

Following SQL query can be used to list all the primary keys of a table. In this given example, we assume that table name is ‘Employee’.

SELECT SysColumns.Name
FROM SysIndexes SysIndexes
JOIN SysObjects SysObjects ON SysIndexes.Id = SysObjects.Id
JOIN SysObjects PrimaryKey ON SysIndexes.Name = PrimaryKey.Name
AND PrimaryKey.Parent_Obj = SysIndexes.Id
AND PrimaryKey.Xtype = 'PK'
JOIN SysIndexKeys SysIndexKeys on SysIndexes.Id = SysIndexKeys.Id
AND SysIndexes.IndId = SysIndexKeys.IndId
JOIN SysColumns SysColumns ON SysIndexKeys.Id = SysColumns.Id
AND SysIndexKeys.ColId = SysColumns.ColId
WHERE SysObjects.name = 'Employee'
ORDER BY SysIndexKeys.keyno

List all Default constraints of a table in SQL Server

Following SQL query can be used to list all the default constraints of a tabled named ‘Department’:

SELECT DefaultConstraints.Name 'Constraint Name', DefaultConstraints.Definition 'Default Value' 
FROM Sys.Default_Constraints DefaultConstraints INNER JOIN Sys.Objects SystemObjects 
On SystemObjects.Object_Id = DefaultConstraints.Parent_object_Id
Where SystemObjects.Name = 'Department'

Check if given table or stored procedure exists in SQL Server database

Following SQL statement can be used to check if stored procedure named “SelectEmployee” exists in the active database and drop it if exists.

IF EXISTS (SELECT SystemObjects.Name
FROM SYS.OBJECTS SystemObjects 
WHERE SystemObjects.type = 'p' AND SystemObjects.Name = 'SelectEmployee')
BEGIN
DROP PROCEDURE SelectEmployee
END

Following SQL statement can be used to check if table name “Employee” exists in the database and drop it if exists.

IF EXISTS (SELECT * FROM Sys.Tables
WHERE name = 'Employee')
BEGIN
DROP TABLE Employee;
END

Check if given column exists in a table in SQL Server database

Following SQL statement checks to see if column “Address” exists in the “Employee” table. If the column does not exist it adds that column.

If Not Exists (Select * From Information_Schema.Columns 
               Where Table_Name = 'Employee' And Column_Name = 'Address')
Begin
    Alter Table Employee Add Address nvarchar(200)
End

 

Get all foreign key constraints in SQL Server database

Often we need to get all the Foreign Key constraints so that you can drop them by making dynamic queries and then re-add them. This is usually needed when you are writing some custom data migration tool in which case you drop all the foreign key constraints first, then populate data, and then you add the constraints again.

Whatever be the use case, following SQL query will give you details of all the foreign key constraints in the active database:

SELECT OBJECT_NAME(ForeignKeyColumn.Constraint_Object_ID) AS 'ForeignKey', OBJECT_NAME(ForeignKeyColumn.Parent_Object_ID) AS 'ForeighKeyTable', ParentColumn.name AS 'ForeighKeyColumn', OBJECT_NAME(ForeignKeyColumn.Referenced_Object_ID) AS 'PrimaryKeyTable', ReferencedColumn.Name AS 'PrimaryKeyColumn' 
FROM Sys.Foreign_Key_Columns ForeignKeyColumn
INNER JOIN Sys.All_Columns ParentColumn 
ON ForeignKeyColumn.Parent_Object_ID = ParentColumn.Object_ID
AND ForeignKeyColumn.Parent_Column_ID = ParentColumn.Column_ID
INNER JOIN Sys.All_Columns ReferencedColumn 
ON ForeignKeyColumn.Referenced_Object_ID = ReferencedColumn.Object_ID
AND ForeignKeyColumn.Referenced_Column_ID = ReferencedColumn.Column_ID 
ORDER BY OBJECT_NAME(ForeignKeyColumn.Referenced_Object_ID); 

Get all the stored procedures and their definitions in SQL Server

Following SQL query will list all the stored procedures and their definition in the active database:

SELECT SysObject.Name, SysModule.Definition
FROM Sys.Objects SysObject INNER JOIN Sys.Sql_Modules SysModule
ON SysObject.Object_ID = SysModule.Object_ID
WHERE SysObject.Type = 'p'
ORDER BY SysObject.Name;

List all stored procedures in SQL Server database

Following SQL query gives you names of all the stored procedures in the active database:

SELECT SystemObjects.Name
FROM SYS.OBJECTS SystemObjects 
WHERE SystemObjects.type = 'p' 
ORDER BY SystemObjects.Name;

List all tables in SQL Server database

Following SQL query gives you names of all the tables in the currently active database:

SELECT Name FROM Sys.Tables

SQL Server - Windows Installer Service Error (Error Code 1601)

Sometimes, you get this error when installing SQL Server 2005. Following is what has fixed the issue for me:

• Go to windows services (Start->Run->services.msc) and start Windows Installer service if it is not already started.
• If you are not able to start Windows Installer service, run msiexec /regserver command from command prompt.

SQL Server Setup cannot install files to compressed or encrypted folder

This SQL Server installation error occurs when you have applied compression on the installation folder. To fix this issue do the following:

1. Right click [Program Files]\Microsoft SQL Server
2. Go to properties.
3. Uncheck ‘Compress contents to save disk space’. This option is at different places on XP and Vista (Win 7) machines. But you should be able to find this from this screen.

SQL Server Express – Error Installing MSXML 6.0

This is one of the most common errors occurring when installing SQL Server Express 2005. This error occurs in different scenarios on different machine configurations.
One solution that works in all or most of the cases is to uninstall MSXML 6.0 and then install SQL Server. But this solution is not trivial either. Because, you cannot uninstall MSXML 6.0 from add/remove programs. Microsoft had published an utility called (msicuu2.exe) but they have recently removed this utility and I could not find any reliable source to download it from.
There is however a tool msizap that you can download and then use the following command to uninstall MSXML 6.0.

MsiZap T! {1A528690-6A2D-4BC5-B143-8C4AE8D19D96}


If you package SQL Server with you application, then you can put this command in the batch file and even automate this process.
There is also scenario where above command does not work. In that case i would suggest using some Install Cleanup utility. One of the tools that i have used and works quite well is Your Uninstaller.

Embedded Databases For Windows

An embedded database is a database management system (DBMS) which is tightly integrated with an application software. They do not require any separate installation and are hidden from the end user.
I recently consulted on a project to replace SQL Server with a lightweight embedded database. We had however kept our option open and were also looking at databases with less installation hiccups – the main motivation to get rid of SQL Server.
There are many embedded databases available today and to filter out the one you need its important to have some criteria. Following are the list of things we were looking for in the target database:

• No Installation Required (Preferably)
• SQL based.
• Performance should be as good as possible to SQL Server.
• RDBMS (So that we have less overhead when migrating from SQL Server)
• Can work in Server as well as embedded mode.
• Supports Stored Procedures (Not mandatory but good to have)
• Has .NET data access API.
• Costs reasonable for desktop applications with unlimited installation.
• Some renowned companies using them. This is to judge the stability.
• Easy migration path from SQL Server

Many of the requirements as you can see are for the sake of less overhead when we migrate from SQL Server. I went through many databases to find the one we needed. Many of the databases did not fit into our criteria just because they were not RDBMS or they didn’t have .NET APIs. Following databases are either non RDBMS or don’t have .NET APIs:

• MongoDB
• Casandra
• CouchDB
• Apache Derby
• Raima Database Manager
• SolidDB
• Oracle Berkley DB
• Valentina DB (ORDBMS)
• Informix Dynamic Server

Now, i will discuss the ones i really looked into.

• Firebird
• Both embedded and server flavors.
• RDBMS
• Has .NET data provider APIs.
• Stable. Built on the InterBase source code.
• Used by many companies. Lots of resources available on the web.
• Switching between server and embedded version mostly require change of connection string only.
• Supports stored procedures.
• Supports standard SQL.
• Performance is close to SQL Server.
• Open Source. Free for development and distribution.
• Scimore DB
• Both embedded and server flavors. In the embedded version, they can even be configured to run in-process and out-of-process.
• RDBMS.
• Has .NET data provider APIs.
• Not very stable.
• No big name associated. Tough to find resources on the web.
• Switching between server and embedded mostly require change of connection string only.
• Supports stored procedures.
• Supports standard SQL.
• Performance is close to SQL Server.
• Free for development and distribution. Source code is also available at reasonable cost.
• Elevate DB
• Both embedded and server flavors.
• RDBMS.
• Has .NET APIs.
• Supports stored procedures.
• Support standard SQL.
• No big names associated.
• Performance is slower than SQL Server.
• ITTIA DB
• Embedded and server flavors.
• RDBMS
• Has .NET APIs.
• No stored procedures (It was supposed to be coming soon, i haven’t checked)
• Supports standard SQL.
• No big names associated.
• Performance is slower than SQL Server.
• Effiproz
• Only embedded.
• RDBMS
• Has .NET APIs.
• Supports stored procedures.
• Free (MSPL)
• SQL CE
• Only embedded
• RDBMS
• Has .NET APIs
• No stored procedures
• Free for distribution
• Supports standard SQL
• Performance of SELECT queries is considerably slower than SQL Server for large databases.
• SQLite
• Only embedded
• RDBMS
• Has .NET APIs
• No stored procedures.
• Free for development and distribution
• Supports standard SQL
• Many big companies using it. Stable.
• Performance of SELECT queries is slower than SQL Server for large databases.
• Vista DB
• Only embedded
• RDBMS
• Has .NET API. In fact, the database itself is in managed code.
• Supports stored procedures.
• Supports standard SQL.
• Performance is slower than SQL Server.
• Empress Embedded Database
• Only embedded.
• RDBMS.
• Has .NET APIs.
• Supports stored procedure.
• Supports standard SQL
• Performance is slower than SQL Server.
• InterBase
• Embedded edition only ideal for < 3 MB databases
• RDBMS
• Has .NET APIs.
• MySQL
• Not embedded
• RDBMS
• Has .NET API
• Supports stored procedures
• Supports standard SQL
• Performance is close to SQL Server.
• Not free when your application is not open.

Note that some databases might have enhanced their features so i would also suggest to check their latest set of supported features for any enhancements they might have come up with.

Remove ReadOnly attribute of a file

string fileName = @"C:\ReadOnlyFile.txt";
File.SetAttributes(fileName, File.GetAttributes(fileName) & ~FileAttributes.ReadOnly);

Above C# code ensures that fileName does not have ReadOnly attribute set. SetAttribute static method takes fileName as the first parameter and FileAttributes enumeration as the second parameter. FileAttributes is a Flags enumeration meaning that we can combine multiple FlagAttributes values and set that as attribute of the file.
In the above code, we derive the FileAttributes to set by getting actual file attribute and doing a bitwise AND (operator &) with complement (operator ~) of ReadOnly attribute. This ensures that ReadOnly bit is set to zero leaving all the other attributes of the file as they were.
If you are unclear about logical operators, look at the following link for details:

http://msdn.microsoft.com/en-us/library/6a71f45d(VS.71).aspx

Forcefully delete a service

I needed that when i saw that even after i had uninstalled SQL Server from my machine, the service was still showing in Windows services. This is a sign that SQL was not uninstalled correctly and some of the pieces are still lying around. In this case you have to manually delete the service and do the file system and registry cleanup.

Windows has a command line utility called “sc” (I assume it stands for Service Controller) that gives us the APIs to talk to Windows services. Following command deletes SQLExpress service from the computer, for example.

sc delete MSSQL$SQLExpress

To know the actual name of the service, you can go to the properties (Right click and select properties) of that service from Windows Services window (Start—>Run—>Services.msc) and check the service name.

Finding permutations

Here, I am going to present a very compact recursive c# code to compute all the permutations of a given string. Look at the following c# method:

public void PrintPermutations(string stringToPermute, string prefix)
{
   if (stringToPermute.Length <= 0)
   {
       Debug.WriteLine(prefix);
   }
   else
   {
       for(int i=0;i<stringToPermute.Length;i++)
           PrintPermutations(stringToPermute.Replace(stringToPermute[i].ToString(), ""), prefix + stringToPermute[i]);
   }
}

To find all the permutations of the string “ABC”, for example, you will call the method as follows:

PrintPermutations("ABC", "");

Also note that, I assume that stringToPermute parameter is a string containing non-repeating characters.

Location of executing batch file

Often in the batch script we need to change to the directory from where the batch file is executing. Mainly when you are shipping the batch file with your application, you may want to launch some executable from the batch file, for example. So, if you put batch file where the executable is, following batch script will do the job for you:

cd %~dp0

MyApplication.exe

First statement changes current directory to the location of batch file and then second statement launches the application.

Execute SQL statement from command prompt

Many a time we run into requirement of executing simple SQL scripts using command prompt. Microsoft gives us a utility called "sqlcmd" just for that.

Following is a very simple query executed using this tool:

sqlcmd -S .\SQLEXPRESS -E -Q "Drop Database SampleDB"

Where SampleDB is the database name i want to drop, -S switch specifies the server instance, -E switch specifies that i want to use trusted connection and -Q switch specifies the SQL query i want to execute.

This tool is quite powerful and there are several switches that you can use to meet different requirements. You can open command prompt and see help (sqlcmd /?) for all the different switches that it supports.