Thursday 26 April 2012

Temporary table vs Table variable vs derived Table


Four options available for temporary matrix storage: Derived Table and Temporary Table are the oldest, followed by Table Variable and the latest is CTE which can also be recursive.




Temp tables,
          IO Operation - HIGH
          Explicit Indexing is allowed
          Constraints are allowed
          Need not create or declare, we can use the INTO clause to create this object.        
          Reusable across the batch but with current session
          Recommended when the data is huge & if there is any manipulation on the result
          Supports concurrency users
          Bit slower than table variables.


Table variables,
          IO Operation – LOW or NA
          Explicit Indexing not allowed – (Primary/Unique key can create index)
          Constraints are allowed
          Have to declare the variable
          Only used by the current batch scope
          Recommended when the data is less & if there is any manipulation on the result
          Supports concurrency users
Faster when the number of records is less


Derived tables,
          IO Operation – NA
          Declaration – NA
          Explicit Indexing – NA
          Constraints - NA
          Only on the current query(line)
          If it is not reusable data then we can go for it
          Recommended for inline quires
          Supports concurrency users

What is Derived Tables in SQL


The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I've yet done with SQL Server is using derived tables.
If you've used a VIEW before, you've used a more formal, more correct form of a derived table. For example, we could do the following:
 CREATE VIEW vwEmployeesFromNewYork AS
  SELECT * FROM Employee
  WHERE State = "NY"
 GO
Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:
 SELECT LastName, FirstName
 FROM vwEmployeesFromNewYork
 WHERE LastName = "Smith"
 ORDER BY FirstName
However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what's important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:
 SELECT LastName, FirstName
 FROM
  (SELECT * FROM Employee
   WHERE State = "NY") AS EmployeeDerivedTable
 WHERE LastName = "Smith"
 ORDER BY FirstName
Isn't that neat? What we are doing is first getting the result set from our derived table (the SELECT statement in the FROM clause). Once we have that resultset, it is as though it was a table in itself. We then perform the SELECT on the derived table, returning our results! 

Friday 20 April 2012

Pivots with Dynamic Columns in SQL Server 2005




Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:
PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):
CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300 

Suppose we wanted to convert the above into this:
 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:
SELECT  [January]
      [February]
      [March]
FROM    SELECT    [Month]
                  SaleAmount
          FROM      Sales
        p PIVOT SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:
In the first table I have the column names I want to use:
CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:
CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:
SELECT  tID
      [Country]
      [Day]
      [Month]
FROM    SELECT    t2.tID
                  t1.ColName
                  t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId t2.ColID
        p PIVOT MAX([Txt])
                    FOR ColName IN [Country][Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.
In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].
Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:
DECLARE @cols NVARCHAR(2000)
SELECT  @cols COALESCE(@cols ',[' colName ']',
                         '[' colName ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.
DECLARE @cols NVARCHAR(2000)
SELECT  @cols STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' t2.ColName
                        FOR XML PATH('')
                      ), 12'') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.
Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:
DECLARE @query NVARCHAR(4000)
SET @query N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'
Executing this with
EXECUTE(@query)
will give us the expected result: a table that is pivoted and shows columns that were specified in a table:
tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

Wednesday 11 April 2012

What are master, msdb, tempdb databases in Sql Server?

Microsoft® SQL Server 2000 systems have four system databases: 

• master - The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. 
• tempdb - tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. 
By default, tempdb autogrows as needed while SQL Server is running. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb. 
• model - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. 
• msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
 

Monday 9 April 2012

what is collations in sql server


What is collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
Case sensitivity
If A and aand b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while is 97. The ASCII value of B is66 and is 98.
Accent sensitivity
If a and áo and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of ois 111 and ó is 243.
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Database, Tables and columns with different collation

SQL Server 2000 allows the users to create databases, tables and columns in different collations.

Databases with different collation

use master
go
create database BIN collate Latin1_General_BIN
go
create database CI_AI_KS collate Latin1_General_CI_AI_KS
go
create database CS_AS_KS_WS collate Latin1_General_CS_AS_KS_WS
go

Tables and columns with different collation

Create table Mytable (
[colu] char(10) COLLATE Albanian_CI_AI_KS_WS NULL,
[Maydate] [char] (8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[Risk_Rating] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)

Comparing characters on the databases with different collation

When we run the below code in CI_AI_KS and CS_AS_KS_WS the results will be completely different.
declare @Accentvar1 char(1)
declare @Accentvar2 char(1)
declare @Casevar1 char(1)
declare @Casevar2 char(1)
set @casevar1 ='A'
set @casevar2 ='a'
set @Accentvar1 ='a'
set @Accentvar2 ='á'

if @casevar1 = @casevar2
 begin
  print "A and a are treated same"
 end
 else
 begin
  print "A and a are not treated same"
 end

if @Accentvar1 = @Accentvar2 
 begin
  print "A and á are treated same"
 end
 else
 begin
  print "A and á are not  treated same"
 end
When we execute these statements on a CI_AI_KS database, the results are similar to those shown below.

 A and a are treated same
 A and á are treated same

When we execute these statements on a CS_AS_KS_WS database, the results are similar to those shown below.

 A and a are not treated same
 A and á are not treated same

Simulating case sensitivity in a case in-sensitive database

It is often necessary to simulate case sensitivity in a case insensitive database. The example below shows how you can achieve that.
Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 ='a'
if ASCII(@var1) = ASCII(@var2)
print "A and a are treated same"
else
print "A and a are not same"
However, the function ASCII cannot be used for words. In order to achieve the same functionality of simulating case sensitiveness, we can use the varbinary data type.
Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Good'
set @var2 ='gooD'
if cast(@var1 as varbinary) = cast(@var2 as varbinary) 
print "Good and gooD are treated same"
else
print "Good and gooD are not treated same"

Simulating case insensitivity in a case sensitive database

It is often necessary to simulate case insensitivity in a case sensitive database. The example below displays how you can achieve that.
Use CS_AS_KS_WS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 ='a'
if upper(@var1) = upper(@var2)
print "A and a are treated same"
else
print "A and a are not same"
go
Use CS_AS_KS_WS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Good'
set @var2 ='gooD'
if upper(@var1) = upper(@var2 )
print "Good and gooD are treated same"
else
print "Good and gooD are not treated same"

Simulating Accent sensitivity in a Accent insensitive database

It is often necessary to simulate case sensitivity in a case insensitive database:
Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 = 'á'
if ASCII(@var1) = ASCII(@var2)
print "A and á are treated same"
else
print "A and á are not treated same"
Again, the function ASCII cannot be used for words. In order to achieve the same functionality of simulating case sensitiveness, we can use the varbinary data type:
Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Gold'
set @var2 ='Góld'
if cast(@var1 as varbinary) = cast(@var2 as varbinary) 
print " Gold  and Góld  are treated same"
else
print " Gold  and Góld  are not treated same"

Change the collation

In SQL Server 7.0, Collation can be setup only on the server level during installation. In order to change the collation, you have to rebuild the master. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In SQL Sever 2000, you can change collation on the database level and column level just by using alter statements.
Create database testNorwegian collate Danish_Norwegian_CI_AI_KS
go
Alter database testNorwegian collate Danish_Norwegian_CI_AI
go

Create table Mytable (
[colu] char(10) COLLATE Albanian_CI_AI_KS_WS NULL,
[Maydate] [char] (8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[Risk_Rating] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)
go
Alter table Mytable alter column [colu] char(10) COLLATE Korean_Wansung_Unicode_CS_AS_KS NULL
go

Find the collation of the current server

select SERVERPROPERTY ('collation')

Find the collation of a particular database

select convert(sysname,DatabasePropertyEx('CS_AS_KS_WS','Collation'))

Find the collation of the current database

select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))

Find collation of all columns in a table

select name, collation from syscolumns where [id]=object_id('Mytable')

Find all collation available in SQL Server

select * from ::fn_helpcollations()

Conclusion

This article explains the basic concept of collation and its uses. You can create functions for simulating accent insensitive in an accent sensitive database by mapping a for á. Kana sensitivity and Width sensitivity are focused mainly on Asian languages.

Wednesday 4 April 2012

Improving SQL Performance


Twelve Tips For Optimizing Sql Server 2005 Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level



To increase the speed of SQL SELECT query, you can analyze the following issues: 

·RequestLive property value  
·Available indexes for conditions from WHERE clause  
·Rewriting a query with OR conditions as a UNION  
·Available indexes for JOIN conditions  
·Available indexes for ORDER BY clause  
·Available indexes for GROUP BY clause  
·Select from in-memory tables  
·SELECT INTO vs INSERT SELECT
RequestLive property value 
The first important option which can be used for a speed optimization is the TABSQuery.
RequestLive property.
If selection query is simple and involves a single table only, try to set 
RequestLive value to True and to False before opening a query. Some queries run faster with theRequestLive=True, others will perform much better with the RequestLive=False.
Available indexes for conditions from WHERE clause

It is recommended to make sure that optimal indexes for conditions from WHERE clause are available.
See the topic "
Speeding up Searches and Filters" for more details on how to check search conditions and create appropriate indexes.
For example if you would like to get a better performance for the query: 


SELECT * FROM customer WHERE City='Kapaa Kauai' AND State='HI'  

the best way to speed it up is to create the following case-sensitive index: 
ABSTable1.AddIndex('idxCityState''City;State', []);  

If you need to get a better performance for the query: 

SELECT * FROM customer WHERE Upper(City)='KAPAA KAUAI'  

the best way to speed it up is to create the following case-insensitive index: 
ABSTable1.AddIndex('idxCity_nocase''City', [ixCaseInsensitive]);  

Available indexes for JOIN conditions

To improve a JOIN query, please check that each field from JOIN conditions has an index.
For example if you would like to improve the performance of the query: 

SELECT Event_Name,Venue FROM Events e JOIN Venues v ON (e.VenueNo = v.VenueNo)  

you can create the following indexes: 
VenuesTable.AddIndex('idxVenueNo''VenueNo', [ixPrimary]);  
EventsTable.AddIndex('idxVenueNo''VenueNo', []);  

Rewriting query with OR conditions as a UNION

Absolute DB cannot use indexes to improve performance of a query with OR conditions. You can speedup your
query 

SELECT * FROM table WHERE (Field1 = 'Value1') OR (Field2 = 'Value2')  

by creating indexes on each field in the above conditions and by using a UNION operator instead
of using OR:

SELECT ... WHERE Field1 = 'Value1'  
UNION  
SELECT ... WHERE Field2 = 'Value2'  

Available indexes for ORDER BY clause

If you want to speed up a "live" SELECT from a single table with ORDER BY clause, you can create a compound index for ORDER BY fields.
For example if you would like to increase the speed of the query: 

SELECT * FROM Employee ORDER BY LastName, FirstName  

you can do it by creating the following compound index: 
ABSTable1.AddIndex('idxLastNameFirstName', 'LastName;FirstName', []);  

Available indexes for GROUP BY clauseTo get a better performance for SELECT from a single table with GROUP BY clause, you can create a compound index for GROUP BY fields.
For example if you want to speed up the query: 

SELECT * FROM Employee GROUP BY FirstName  

you can create the following index: 
ABSTable1.AddIndex('idxFirstName', 'FirstName', []);  

Select from in-memory tablesYour query perofrmance could be increased also if you will move all data from disk tables to in-memory tables and you will perform a query using in-memory copies of the disk tables (Set TABSQuery.InMemory property to True before query execution). 
SELECT INTO vs INSERT SELECT 

In some cases 
SELECT ... INTO some_table query runs faster than INSERT INTO some_table (SELECT ...)in another cases INSERT INTO is faster. Please note that the RequestLive property could have an impact on a performance of these queries.