Copy column names along with results from MSSQL Management Stuido

Sometimes it helps in copying column names along with query results. This can be turned on/off easily in MSSQL Management Studio easily

Go to Tools -> Options page of Management Studio, then under Query Results -> SQL Server -> Results to Grid, check/ un-check the feature.

Primary key vs Unique key

SQL allows two interesting keywords to decorate a table - Primary key and Unique key. Differences or similarities are as follows,

• primary key does not allow nulls but unique key does
• primary key does clustered indexing, whereas unique key does non clustered
• both can be used for constraints
• multiple unique keys can be defined for a table

MSSQL Management Studio: Database Diagram problem.

Recently I got an error message in Management Studio with database diagram. Error message is

To solve this:

1. Right Click on interested database, choose properties
2. Goto the Files Page
3. Enter "sa" in the owner textbox.
4. Save changes.

Alternative (haven't tried yet) is to give privilege by

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

GO

Using SqlDataReader on multiple results.

Suppose we plan to execute multiple select query and how do we fetch result using SqlDataReader. Well this can be achieved by using SqlDataReader.NextResult

Example

Create test database and sample tables

create table employee (id int identity(1,1), name varchar(256), primary key(id))
create table employeeaddress (id int identity(1,1), empaddress varchar(256), primary key(id))


insert into employee values('Johan')
insert into employee values('Leon')


insert into employeeaddress values('Eindhoven, The Netherlands')

Now lets fetch results using SqlDataReader, C# program as follows

string cmdText = @"select * from employee;select * from employeeaddress";

            using (SqlConnection sqlCon = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=testDB;Integrated Security=SSPI;"))

            {

                using(SqlCommand sqlCmd = new SqlCommand(cmdText,sqlCon))

                {

                    sqlCon.Open();

                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    int ResultIndex =1;

                    do

                    {

                        Console.WriteLine("Results from table - " + ResultIndex);

                        while (reader.Read())

                        {

                            Console.WriteLine(reader[0]);

                        }

                        

                        ++ResultIndex;

                    } while (reader.NextResult());

                    reader.Close();

                }

            }

MS SQL AutoClose

Something that solved one of our big problems AutoClose.

Recently in one of our project we exposed a stateless service with SQL 2005 Express back end. And we ended up with a crazy issue.

Issue is after 5-6 minutes the performance of the service went down. Thought it was in milli seconds, when clients made 100-200 calls, performance hit was visible.

Its like SQL maintains resources for a connection or execution of query, which SQL by default will recycle when there is no activity. This is a good feature, however in our case it proved very costly.

So turned the feature OFF.
create database employees;
alter database employee set AUTO_CLOSE OFF;

MS SQL Delete vs Truncate

Both DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION (if the current session is not closed). Other differences are as follows,

Truncate:

• Truncate is faster and uses fewer system and transaction log resources than Delete.
• Truncate removes the data by de-allocating the data pages used to store the table's data, and only the page de-allocations are recorded in the transaction log.
• Truncate removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
• Truncate cannot be TABLE on a table referenced by a FOREIGN KEY constraint. Because Truncate TABLE is not logged, it cannot activate a trigger.
• Truncate cannot be rolled back.
• Truncate is DDL Command.
• Truncate Resets identity of the table

Delete:

• Delete removes rows one at a time and records an entry in the transaction log for each Deleted row.
• If you want to retain the identity counter, use Delete instead. If you want to remove table definition and its data, use the DROP TABLE statement.
• Delete Can be used with or without a WHERE clause
• Delete Activates Triggers.
• Delete can be rolled back.
• Delete is DML Command.
• Delete does not reset identity of the table.

MS SQL Primary key vs Unique Key

In MS SQL environment the main differences between a Primary key and Unique key are

• primary key does not allow nulls however unique key does allow.
• primary key does clustered indexing, whereas unique key does non clustered indexing.
• both can be used for constraints.
• multiple unique keys can be defined for a table, but only a single primary key in a table.

SQL Pattern matching

MS SQL queries allows us to play with strings (pattern matching).
Let's create an employee table and insert values,


create table employees (id int identity(1,1), name varchar(256), primary key(id))


insert into employees values('James')
insert into employees values('Leon')


//now select will give following values
select * from employees
1 James
2 Leon 

• if we need to query names starting 'J', we can use '%' to replace one or more characters. 

select name from employees where name like 'J%'

James

• '%' can replace one ore more characters, however if we want to replace one character use '_'

select name from employees where name like '_eon'

Leon

• if we want to query for names with a letter ranging between a group use '[]' 

select * from employees where name like '[h-k]%'

1 James

• if we need to query for name with a letter not in a range use '[^]'

select * from employees where name like '[^h-k]%'

2 Leon