Month: Feb 2012
Wednesday, 29 February 2012
Friday, 24 February 2012
INCLUDE clause WITH NON CLUSTERED INDEX
This article is dedicated to one of my colleague, who want to know about the INCLUDE clause of NON CLUSTERED Index. I try to cover all the important points related to it in short.
In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index.
By including non key columns we create non clustered index that covers more query and increase performance.
The features of Adding non key columns are mentioned bellow:
1. Index has the limitation of 16 key columns and maximum index key size of 900 bytes. But if we include non key columns the index not care about the index key size and 123 includes non key columns is allowed.
So a non clustered index contains:
16 key Columns + 123 Includes non Key columns.
2. Columns cannot be repeated in the include list and Dropping of non-key columns is possible after dropping the non-key index first.
To DROP Non-KEY Columns à DROP the NON-KEY INDEX First
3. The database Engine not considers any non-key columns to calculate the number of index key columns or the size of the index.
Size of Index Not Related to Non-Key Columns
4. The data types of non-key columns can be anything evens the data type not supported by the non clustered Index Key-columns.
5. You should keep those columns in "Include" clause which generally comes under "SELECT" clause and not being used much in "WHERE", "GROUPBY" or "ON" clause of "JOIN".
The syntax of creating NON CLUSTERED INDEX with INCLUDE clause is mentioned bellow.
CREATE NONCLUSTERED INDEX [Ind-1] ON [dbo].[tab_exmaple]
(
[sroll] ASC
)
INCLUDE ( [sSec],[sName])
Architecture of INCLUDE on NON CLUSTERED Index:
The architecture of "Included Column" concerns, whatever columns you have defined under "Include" clause under the index, those will be stored on the Leaf pages, and it won't get stored on the Root page or Intermediate page of the index.
Hope the article is quite interesting and thanking you to provide your valuable time on it.
Posted by: MR. JOYDEEP DAS
Thursday, 23 February 2012
Query Solving (Database Restore Related)
I have a Restore related question from one of my DBA Friends mentioned bellow
Query:
Hi, I tried restoring DB with below query
RESTORE DATABASE sirwar
FROM DISK = 'F:\15.02\15.02.12 backup'
WITH REPLACE,
MOVE 'LogicalDatafileName' TO 'f:\sirwar.mdf',
MOVE 'LogicalLogfilename' TO 'f:\sirwar.ldf'
but this came with an error like this..........
Msg 3234, Level 16, State 2, Line 1 < '
Logical file 'LogicalDatafileName' is not part of database 'sirwar'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Solutions mentioned bellow:
1) sp_helpdb on the source database. It's the first column in the
second result set, just copy and paste into the single quotes.
2) RESTIRE FILELISTONLY on the back-up file. Again, it's the first
column.
Posted by: MR. JOYDEEP DAS
Performance increased with Multiport SQL Server
In this article I am trying to demonstrate you how the default instant of the SQL server listens on multiple TCP port.
By default when we are installing the default instance of the SQL server the TCP port 1433 is allocated. If the requested comes from multiple terminals the default port must be overloaded. So if we configure the default instance of SQL server as multi port TCP the load will be balanced. It will increase the performance of the SQL Server.
If the default port of SQL server is overloaded, then the client's connections are reset or forced to be reset to new port settings of the SQL Server.
How we configure the multiport TC in default instance of the SQL Server:
1. In SQL Server network utilities select the TCP properties and added the new port separated by coma like this. 1433, 5000 etc.
2. Stop and restart the SQL server and retrieve the error log
SQL server listening on TCP, Shared Memory, Named Pipes.<BR/>
SQL server listening on 157.54.178.42:1433, 157.54.178.42:5000, 127.0.0.1:1433, 127.0.0.1:5000.
3. In the SQL server client network utility modify your clients to spade load across TCP port.
For a general example, suppose we have two webs server named "webServ1" and "webServ2" the both use the same port 1433 of default SQL server instance, so the load is always high. Now it is distributed among port 1433 and port 5000 so the load is balanced and performance is increased.
Hope that the article is quite interesting and thanking you to provide valuable time in it.
Posted by: MR. JOYDEEP DAS
Wednesday, 22 February 2012
GROUP BY Clause And Performance Factors
In this article I am trying to describe the frequently used GROUP BY Clause and the Performance factor related to it.
A short description of GROUP BY Clause
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns. The aggregate function columns are not included in GROUP BY cluse, I mean to say the columns that used the aggregate function is not included in the GROUP BY cluse.
The HAVING cluse is used to filters the GROUP BY cluse.
Example:
SELECT columns1, columns2, SUM(columns3)
FROM Mytable
GROUP BY columns1, columns2
HAVING columns1>0
Is the GROUP BY is the Performance killer?
NO the GROUP BY itself is not the performance killer.
In many cases the GROUP BY clause dramatically decreases the performance of the Query.
Here are the some points to take care:
1. To make the performance better, use the COMPOUND INDEXES for the GROUP BY fields.
2. Don't use unnecessary fields or Unnatural Grouping options.
3. I personally preferred the same sequence of columns in SELECT as well as GROUP BY. For Example.
Like this:
SELECT columns1, columns2, SUM(columns3)
FROM Mytable
GROUP BY columns1, columns2
Not Like this:
SELECT columns1, columns2, SUM(columns3)
FROM Mytable
GROUP BY columns2, columns1
This is a small article, but I hope it is quite informative and thanking you to provide your valuable time on it.
Posted by: MR. JOYDEEP DAS
Question asked by one of my DBA friends.
One of my DBA friends asks me some SQL related Question. Here I am trying to explain it in this article
What are necessary steps should be taken for query execution time faster, suppose a Query when it's executing taking time 3min now I want to execute this query within 30secs how?
Answer:
There are lots of factors related to make quay execution faster. My strong suggestion is to understand the execution plan to execute your query in better ways.
Some common factors that we all knows, related to SQL query executions are mentioned bellow.
a. Don't use "SELECT * " in a SQL query. That means use the proper columns name that you needed not overload the query by using *. That added extra expenses to data retrieval.
b. Don't use extra table join that you don't needed in your SQL statement.
c. Don't use COUNT(*) in Sub query, instead use EXIST or NOT EXIST Clause.
-- Do not Use
SELECT column_list
FROM table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)
-- Use This
SELECT column_list
FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)
d. Avoided joining between two types of columns like "INT" and "FLOAT" and
don't try to use CONVERT or CAST.
e. Try to avoid dynamic SQL
f. Try to avoid Temporary table
g. Don't use IN or NOT IN clause in SQL statement, instead use EXISTS or
NOT EXISTS
h. Avoid LIKE instead you can use full text search.
i. Try to use UNION or UNION ALL to implement OR operators.
j. Don't calls any function in SELET statements, I mean try to avoid.
k. Try to avoid correlated sub query
l. Try to use stored procedure to execute all your T-SQL statement.
m. Use VIEWs but always use WITH SCHEMA BINDING options
n. Last but most important, solid idea related to Index. If necessary use
guided index in your SQL join operations.
What is the difference between 'SET' and 'SELECT' in SQL?
Answer:
DECLARE @i INT
--Type1
SELECT @i=Roll FROM MyTab WHERE Name='RAJA'
--Type2
SET @i=(SELECT Roll FROM MyTab WHERE Name='RAJA')
Consider the above two SQL statements, the variable @i have the same value. So, what is the difference between two statements?
1. From SQL server 7.0 Microsoft recommends to use the SET statement only, in such kind of above operations.
2. SET is ANSI standard way to assign the value of a variable.
3. BY SELECT we can assigned multiple value to multiple variable within a single statements. Like this.
DECLARE @i INT,
@k VARCHAR(MAX)
SELECT @i=Roll, @k=SName FROM MyTab WHERE SName='RAJA'
4. SELECT has some difficulties like, if the above statements return more than one rows it not return any error. But if you use SET in the above example it give you error like "Sub query returns more than one values".
So at the conclusions, I recommended you to use SET and not to go at SELECT when assigning variables.
What are the basic differences between SSMS 2005 & SSMS 2008?
Answer:
As per me, there are no differences; the difference is the database engine Limitations of SQL 2005 and SQL 2008. SQL 2008 gives you the better functionality and performance then SQL 2005.
Posted by: MR. JOYDEEP DAS
Tuesday, 21 February 2012
Case Sensitive SQL Query Search and Joining
This article gives you a total overall idea related to collation. It not only contains definition but also contains SQL comparisons and joins with different type collation table in different database.
So let's start
Collation is defined by a set of rules that determines how the data is stored and compare. There are for types of collation and they are
1. Case sensitive: if "A" and "a" is treated as same way, then it is case in-sensitive. And id "A" and "a" are treated as different way than it is case sensitive. As the ASCII code of both letters are different. Capital "A" takes 65 and small "a" takes 97.
2. Accent sensitivity: if "a" and "á" treated as same way then it is accent in-sensitive and if treated differently than it is accent sensitive. The ASCII code of "a" is 97 and "á" is 225.
3. Kana Sensitivity: When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
4. 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 than it is width sensitive.
Example of database creation, with different collations is mentioned bellow
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
A table objects creation example 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)
As a SQL server developer you have to face a situation to compare the two columns of different table having different collation. For Example you are joining two table which support different collations. If you are not careful it gives you an error msg.
To illustrate my points here I give u a simple example of case sensitive collation.
CREATE TABLE Mytable
(Sname CHAR(100) NOT NULL)
INSERT INTO Mytable (Sname)
VALUES ('JOYDEEP'),('TUHIN'),('PALASH'),('SANGRAM')
-- Get the result
SELECT * FROM Mytable WHERE Sname='joydeep'
To make the query case sensitive uses this.
-- To make the query case sensitive
SELECT * FROM Mytable WHERE Sname COLLATE Latin1_General_CS_AS='joydeep'
-- Now
SELECT * FROM Mytable WHERE Sname COLLATE Latin1_General_CS_AS='JOYDEEP'
To find the collation of any table use this
EXEC sp_help Mytable
Now I am giving an example to join two tables from different database with different collations.
If we perform this join it gives an error
SELECT ProductCode, ProductResalePrice
FROM [database1].[dbo].[PRODUCTS]
INNER JOIN [database2].[dbo].[items] ON ProductCode = [items].itemCode
Error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
We have to first know what type of collation both of the databases used to further precede the joining operations.
SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;
Now we can
SELECT ProductCode, ProductResalePrice
FROM [database1].[dbo].[PRODUCTS]
INNER JOIN [database2].[dbo].[items]
ON ProductCode COLLATE SQL_Latin1_General_CP1_CI_AS = [items].itemCode
I think that the article is quite informative and thanking you to provide your valuable time on it.
Posted by: JOYDEEP DAS
Subscribe to:
Posts (Atom)
About Me
- MR. JOYDEEP DAS (MCDBA) for SQL Server/BI and MR. PALASH PAUL for .NET
- This web hosting contains the critical information related to SQL server and .NET. All this information is taken from day to day development life cycle. If you need any critical information related to SQL server or .NET, feel free to mail us at(for All) sqlserver.asp.net@gmail.com For SQL Server and Business Intelligent (BI) das.joydeep2@gmail.com and for .NET palashpaul.net@gamil.com
Popular Posts
-
I am a mail form one of my DBA friend to request about: "Please advice that how I can restore the database from backup file plac...
-
We have common doubts when to update statistics, before index or after index. To resolve this query we must understand, what is the stati...
-
SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on...
-
The most important thing of a table is defining the primary key; it could be single column based or the composite primary key. In my...
-
In this article we have to learn the covered index. As the name suggest the cover index "COVER" all the columns in the selec...
-
JOIN HINT in SQL, What and When: Joint hint specifies that the query optimizer enforced the join strategy before joining two tables. ...
-
This article gives you a total overall idea related to collation. It not only contains definition but also contains SQL comparisons an...
-
Transaction is a very important part of SQL developer. In this article I am taking about the SAVEPOINT of MS SQL Server and how we use th...
-
Once again, I find something that can hamper the performance of my SQL query, please have a look, it may be quite informative. Some time ...
-
In my previous post, I am giving a short note related to Log Shipping. Some of my reader asks me to write some details related to it. So...