Some days back i was asked about Temp Tables and Table Variables the basic difference why one is better than another one and all, and i am not able to provide a satisfactory reason\answer at that time so this is the time when i studied about a lots about it and i go through several topics, it takes one whole day and night to me to understand some aspects of this and here i am writing a detailed post about Temp Tables and Table Variables which may help some of peoples like me to understand in a better way.
So going through very basic details let talk about Temporary Tables first. So there are two types of temporary tables
A.) Local declared with one # Sign (#)
B.) Global declared with two # Sign (##)
Both of these tables created by simple CREATE TABLE (BOL) syntax except that # is used for Local Temporary Tables declaration and ## is used for Global Temporary Tables.
The Major difference between these two are Local Temporary tables (#) are visible in current connection and Global Temporary Table (##) are visible to all sessions or to the entire instance. You should always check for existence of global temporary table before creating it.
Let see with an example..
Open SSMS connect it and now create a Local Temporary table like this.
-- Creating Local Temporary Table
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))
This will create a temporary table named LOCALTEST now open other SSMS connect it and again retry to create another table with same name but using the new connection and check if it created or not so here it is ….
-- Create Temporary Table with same name
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))
-- Try to find out table with 'LOCALTEST' name in TempDb
USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like '%LOCALTEST%'
GO
So here is the result set for the above query
So its very clear from the result set that there are two Local Temporary Tables with the same name.
In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number, you can see that in this image.
But when you try the same thing with Global Temporary Tables then it will result into an error.
So here is some points about Temporary tables..
- Can be create by simple Create Table Syntax use # for Local Temporary Table and ## for Global Temporary table.
- Local temporary tables will be dropped at the end of current session but if it is created inside a store procedure, it will be dropped when store procedure is finished.
- Its name is limited to 116 characters.
- Foreign Key constraints can’t be applied to primary tables.
Now we know a little about Table Variables :
Table Variables (@) introduced first in SQL Server 2000 and onwards as name suggest its a variable of type table. Its definition’s include column definition, names, data type and constraint(Foreign Key Constraint are not allowed). They are more flexible and and always stay in memory well this is not true that table Variables stay always in memory this is a MYTH as table variables may be stored as in same way in TempDb as temporary tables we will look at some example for this. They are created with the Declare @variable syntax. There are some properties for Table Variables
- They have well defined limited scope and are not part of persistent database.
- Transactional rollbacks doesn’t affect on them.
- They have very limited scope like current batch of statements so they can be deleted automatically after the completion of statements.
- You don’t use them in nested procedures.
- You not able to truncate a table variable.
So these are the some of basics for Table Variables and Temporary Tables but our Aim is to be find out which one is best in terms of performance, Advantages & Disadvantages for each and some considerations so we try to understand things why one is better then another and why not so
A.) Table Variables have a limited scope and are not part of persistent database, transaction rollback do not affect them.
Let’s understand what does it mean by transaction rollback do not affect them just take a simple example for this..
-- Create Tempoarary Table
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))
-- Declare Table Variable
DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(50))
-- Insert Values into both
Insert into #LOCALTEST SELECT 1 ,'Ashish'
Insert into @LOCALTEST SELECT 1 ,'Ashish'
-- Begin Transaction (Update both Temp table & Table Var)
BEGIN TRAN
UPDATE #LOCALTEST SET C2='ASHISH GILHOTRA'
UPDATE @LOCALTEST SET C2='ASHISH GILHOTRA'
-- Rollback Tran
ROLLBACK TRAN
-- Check data
SELECT * FROM #LOCALTEST
SELECT * FROM @LOCALTEST
and here is the result set
From this it’s very clear that Table Variables doesn’t take part in rollback transactions.So it may be or may not be useful for developers depends on the developers.
B.) There is less locking and logging on table variables then temporary tables so what does it means so we take an another example to understand this ..
USE tempdb
GO
-- Drop table #LOCALTEST
-- Drop table DUMMYDATA
-- Create a dummy table
CREATE TABLE DUMMYDATA (A int, B varchar(100))
-- Populate Data in it
Declare @i int
set @i=1
while @i<1001
BEGIN
INSERT into DUMMYDATA SELECT @i,REPLICATE('A',100)
SET @i=@i+1
END
--select * from DUMMYDATA
--Using #LOCALTEST
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))
GO
BEGIN TRAN
INSERT INTO #LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- Using @LOCALTEST
DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))
BEGIN TRAN
INSERT INTO @LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- Now check for locks
select request_session_id, resource_type, db_name(resource_database_id),(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description,
request_mode,
request_status
from sys.dm_tran_locks
--Rollback tran
So if you going to run this query batch you will see lock acquire by “#LOCALTEST” not by @LOCALTEST so yes that true that Table Variables takes less locking then temporary tables.
for logging purpose just populate temp Table and Table Variable with same data as shown above and now use fn_dblog(Un Documented) to see what happen like this..
So here is a way to find logging
First of all see logging for Table Variables
-- Table Variable
DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))
INSERT INTO @LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- update all the rows
update @LOCALTEST set C2 = replicate ('AB', 50)
-- Look at the top 10 log records.
select top 10 operation, AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%LOCALTEST%'
order by [Log Record Length] Desc
For this i don’t get any records of update log in this and try same for Temporary Tables you will get update logs in this..
-- Temp Table
--drop table #localtest
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))
GO
INSERT INTO #LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- update all the rows
update #LOCALTEST set C2 = replicate ('ab', 50)
-- Look at the log records. Here you get log records for update
select operation,AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%LOCALTEST%'
order by [Log Record Length] Desc
And at last about the performance and IO usage for temporary table and table variables i use that example so the values vary for user to user so you can try the same on your systems and see what results you have with this…i am still working on this and as soon as possible i will be back with some results valid and good results to this till then you can try this at your end…
USE AdventureWorks
-----------------------------------------------------------------------
DECLARE @TEMP TABLE
(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)
,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)
INSERT INTO @TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)
SELECT TOP 100 ADDRESSID,ADDRESSLINE1
,ADDRESSLINE2,CITY,StateProvinceID FROM PERSON.ADDRESS
--SELECT * FROM @TEMP
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.*
FROM @TEMP A INNER JOIN PERSON.STATEPROVINCE B
ON A.StateProvinceID=B.StateProvinceID
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------
CREATE TABLE #TEMP(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)
,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)
INSERT INTO #TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)
SELECT TOP 100 ADDRESSID,ADDRESSLINE1
,ADDRESSLINE2,CITY,StateProvinceID FROM PERSON.ADDRESS
--SELECT * FROM @TEMP
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.*
FROM #TEMP A INNER JOIN PERSON.STATEPROVINCE B
ON A.StateProvinceID=B.StateProvinceID
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DROP TABLE #TEMP
----------------------------------------------------------------
There are some more things like You can’t Alter Table Variable definition no DDL is applied on Table Variables and no statistics is maintained on table variable and statistics are used by query optimizer so there is a performance issue with this.
So after that experiments i think that this is totally depends on the developer that what he wants to use and what are the requirements. i will made a follow up post on this topic again as i may miss many of things on this vast topic so i really appreciate your suggestions and reviews on this if you found anything which i miss or wrong in this then drop a comment here we will try to make it clear as soon as possible and soon as i got more information about this i will write a follow up post soon.
Thanks
This time its all about a script a backup script.Today early morning i asked to get BackUp details for some of DB’s we have, usually they ask me about the last BackUp taken so its easy for me to tell him. But this time he needs all the history of BackUp’s for some reason.So i know that iformation is stored somewhere is System databases so started goggling for it and i found it on mssqltips.com and the script is written by Thomas LaRock(aka SQLRockStar) and i modified this script according to my use by taking his prior permission i modified his script and add the details about the restoration of BackUp’s and location of BackUp’s basically i added information about the Restorations of BackUp’s, so here is my modified script about BackUp status which returns some very useful information about BackUp’s and restore.
-- Script Returns BackUp Status and corresponding Restore Status for all Databases BackUps
DECLARE @DBNAME VARCHAR(100)
SET @DBNAME=NULL -- Default NULL(All Databses)
select 'BackUp Name'=BS.name,
'User Name'=BS.user_name,
'Start Date'=BS.backup_start_date,
'Finish Date'=BS.backup_finish_date,
'Backup Type'=Case when BS.type='D' then 'FULL Backup'
when BS.type='L' then 'Transaction Log Backup'
when BS.type='I' then 'Differential Backup' end
,'Backup Size MB'=floor(((BS.backup_size/1024)/1024))
,'DbName'=BS.database_name
,'Server Name'=BS.server_name
,MF.physical_device_name
,'IS Ever Restored'=case when BS.backup_set_id in
(select backup_set_id from msdb.dbo.restorehistory)
then 'Yes' else 'No' end
,'Destination Db'
=isnull(RH.destination_database_name,'Yet Not Restored From This BackUpSet')
,'Restore Path'
=isnull(min(RF.destination_phys_name),'Yet Not Restored From This BackUpSet')
,'restore Type'=isnull(CASE WHEN RH.restore_type = 'D' THEN 'Database'
WHEN RH.restore_type = 'F' THEN 'File'
WHEN RH.restore_type = 'G' THEN 'Filegroup'
WHEN RH.restore_type = 'I' THEN 'Differential'
WHEN RH.restore_type = 'L' THEN 'Log'
WHEN RH.restore_type = 'V' THEN 'Verifyonly'
WHEN RH.restore_type = 'R' THEN 'Revert'
ELSE RH.restore_type
END ,'Yet Not')
,Rh.restore_date,'Restore By'=isnull(RH.user_name,'No One')
,'Time Taken'
=cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/3600 as varchar(10))
+' Hours, ' +
cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/60 as varchar(10))
+ ' Minutes, ' +
cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)%60 as varchar(10))
+' Seconds'
from msdb..backupset BS
JOIN msdb..backupmediafamily MF
on BS.media_set_id=MF.media_set_id
left outer join msdb..restorehistory RH
on BS.backup_set_id =RH.backup_set_id
left outer join msdb..restorefile RF
on RF.restore_history_id=Rh.restore_history_id
where BS.database_name = isnull(@DBNAME,BS.database_name)
group by BS.name,BS.user_name,BS.backup_start_date,BS.backup_finish_date,
BS.TYPE,BS.backup_size,BS.database_name,BS.server_name
,MF.physical_device_name,BS.backup_set_id,RH.destination_database_name
,RH.restore_type,Rh.restore_date,RH.user_name
Here this scripts take a Database name as parameter and is you don’t supply that parameter that by default it shoes information about all databases
Here what this script returns :
- BS_Name = Name of BackUp
- BS_UserName = Name of User from which BackUp had taken.
- backup_start_date = Start Date Time of BackUp
- backup_end_date = End Date Time of BackUp
- BackUp Type = Type of BackUp
- BackUp Size = Size of BackUp File
- Dbname = Name of database
- ServerName = Name of Server
- Physical_Device_Name = name of Drive or Device where backup stored
- Is Ever Restored = is BackUp Ever Restore(Yes/No)
- Destination Db = Name of Database Restored By BackUp
- Restore Path = Path from database restored
- Restore type = Type of restoration
- restore_date = Date when Database Restored
- restore by = User by which database in restored
- Time Taken = Total time taken to take Database BackUp
So in this way this script return some useful information regarding BackUp’s and Restoration of backup’s its really make sense if you schedule this script to send through mail to get updates about your BackUp’s and Restore information so its really a good idea if we send this information in mail so in next blog post i will write how to send this information using
e-mail to direct in your inbox through Database nail in SQL Server.
SO if you have any suggestions regarding this script and do let me now or if you found any other method then do post here any kind of suggestions are welcomed
Thanks….
Hi All …
You all must here about T-SQL Challenges, these challenges are ideal to learn Set Based Queries using TSql. The most beneficial part of these challenges is that, through these challenges , you get a chance to see better solution to solve a given problem, T-SQL Challenges is mainly focused on writing Set Based Queries using T SQL. You can find previous challenges, winner of all challenges, and the best possible for all solutions by T SQL Challenges team as well as all the solutions provided by the winners with explanation of their solution’s here (ie TSQL Challenge #8)on TSQL Challenges Blog at beyondrelational.com ; and TSQL challenge team also provided a winning certificate for TSQL Challenges winners.Here is my certificate for my winning entry of TSQL Challenge #1.
The most wonderful effort put in to these challenges by TSQL Team is that they provide a free training webcast for those peoples who interesting in challenges , but not able to solve it.The training webcast helps us to write complex set based queries in simple manner and enhance our T SQL Set Based Quering Skills. So if you able to solve the challenge then send your entries else join the training webcast and learn how to write/solve challenges.
So hope to see you all having fun solving these challenges.
Thanks
Hi all this post is about the new features in SQL Server 2008 ..
SQL Server 2008 has a power full feature called DDL triggers which uses for Auditing changes in database and this is very useful too. You will find a blog entry about auditing using transaction log here and find a example of LOGON Trigger here. You can find several ways to do that. So if you want to who create or Delete a table from your database and when then here is a way to do so with the help of “EVENTDATA” function.
”EVENTDATA” function returns a XML value.So here we go and see how it can be done…
First Create a Database to test …
USE MASTER
GO
-- First Create a DATABASE
CREATE DATABASE TestDDLTriggers ON PRIMARY
(NAME=N'TestDDLTriggers' ,FILENAME=N'D:\TestDDLTriggers.mdf')
LOG ON
(NAME=N'TestDDLTriggers_LOG' ,FILENAME=N'D:\TestDDLTriggers.ldf')
You can change you path for .mdf & .ldf file
Now you create a table to hold result for you… and made a DDL Trigger..like this..
USE TestDDLTriggers
GO
-- Create a table to hold results
CREATE TABLE info_ddl (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100));
GO
-- Now create a Trigger
CREATE TRIGGER ddl_test
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT info_ddl
(PostTime, DB_User, Event)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')) ;
GO
So here we create a table to hold the results and a trigger on “DATABASE LEVEL EVENTS” you can see events using this
select * from sys.trigger_events
So after Creating a Trigger time to test the Trigger….
So we try first we try to create a table in the database and then after this we try to Drop that table ..so here it is…
-- Test Trigger
-- Create a table in Database
CREATE TABLE TestDDlTrigger (a int,b int,c int)
GO
-- Now Check our table which holds the data
select * from info_ddl
GO
-- Now we drop the table from Database
DROP TABLE TestDDlTrigger;
GO
-- Again check the data
select * from info_ddl
GO
And here are the results
So in this you can track on the DDL operations like Create and Drop Table in your database.
Additionally you can store T-SQL fired at that time for this you can add a column in your table using alter table …
-- Adding additional column to table to store T-SQL
ALTER TABLE info_ddl ADD TSQL VARCHAR(2000);
GO
then you have to Drop the existing trigger and create a new one here it is…
-- Drop Trigger
DROP TRIGGER ddl_test
ON DATABASE;
GO
-- Again Creating a Trigger
CREATE TRIGGER ddl_test
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT info_ddl
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
So in this you get T-SQL in your table..
You can also look at the Events for triggers in SQL Server 2008 by..
-- Exploring Trigger Events
select * from sys.trigger_events
Thanks And Happy Querying..
Hi all me again here with a post about Encryption & Decryption in Sql Server 2005.I have received a couple of questions regarding my article Encryption & Decryption in Sql Server 2005 , so i started responding through mail but because i found it really useful so here i decided to post it….
So here we go…
Q: Is it possible to pull data from table using the Encrypted column in where clause and if yes then how?
A: Yes it is possible to fetch data from Table using Encrypted column in where clause.. and this how it works…
referring to the last post Table and data is same….
-- Try like this
select * from users where DecryptBykey(UPassword)='MYPASSWORD'
So this throw you records matching your password field
Q: How i Encrypt My Store Procedure i don’t want to show my procedure to anyone?
A: Yes you can Encrypt Store procedure at the time of creating or Altering the procedure….
You can achieve it by just adding a simple “WITH ENCRYPTION” clause when Creating or Altering your procedure like this..
-- Creating a procedure
CREATE procedure [dbo].[TestEncrypt]
WITH ENCRYPTION AS
SELECT 'Hi This is An Encrypted Procedure'
-- Execute Procedure
EXEC TestEncrypt
So it will give you results but when you tried to modify it through SSMS then you find that “MODIFY” option is not open at all and you tried to “Create Script then it also gives you error” here the attached images for this…
Blocked Modify Option
And Error when tried to Crete Script for this…
So in this we can Encrypt our Store Procedures but you have to remember some thing when Encrypting ..
- When you create a Procedure or Function with “WITH ENCRYPTION” so it is not possible to decrypt it back using SQL.
- CLR SP or UDF can not be encrypted.
- Replication doesn’t support encrypted Procedures and Functions
So thanks and Happy Quering…
So after a long i get a chance to work on SQL Server 2008 and understand it in better way. So today we look into a new storage feature in SQL Server 2008 as Sparse Column. Sparse column takes no physical usage for a column which may have “NULL” data. This means when you declare a column as Sparse and any time a “NULL” value is entered in the column it will not use any space, the trade-off is that non-null sparse columns take an extra 4 – bytes of space over regular columns. Sparse column are also applicable on fixed width columns as we take an example of small int data type::
- Sparse Null Columns :: 2 Bytes
- Sparse Non-Null Columns :: 6 Bytes
- Regular Null Columns :: 2 Bytes
- Regular Non Null Columns :: 2 Bytes
There are many Advantages and Disadvantages of Sparse Columns As Stated ::
Advantages ::
- Storing Null value takes up no space at all.
- Sparse columns work really well with filtered indexes as you will only want to create an index to deal with non-null values.
- You can add 30,000 columns as sparse column in table well regular columns has limit of 1024.
On the other side(Disadvantages)
- It will take 4 extra bytes space to store non null values in it.
- Sparse column can’t be associated with the data types CHAR,NCHAR,IMAGE,TIME STAMP,GEOMETRY,GEOGRAPHY and USER DEFINES DATA TYPE.
- Data compression doesn’t work.
- You can’t apply rules.
- Sparse doesn’t have default values.
Now we start with a little example and see how sparse columns work and how they can save storage space for us, so start with creating a dummy table which have sparse columns and one without have sparse columns…
Table which have sparse columns::
-- Table with Sparse Columns
CREATE TABLE [Tbl_Sparsed](
[ID] [int] IDENTITY(1,1) NOT NULL,
[C1] [varchar](100) SPARSE NULL,
[C2] [varchar](100) SPARSE NULL,
[C3] [varchar](100) SPARSE NULL,
[C4] [varchar](100) SPARSE NULL,
[C5] [varchar](100) SPARSE NULL,
[C6] [varchar](100) SPARSE NULL,
[C7] [varchar](100) SPARSE NULL,
[C8] [varchar](100) SPARSE NULL,
[C9] [varchar](100) SPARSE NULL,
[C10] [varchar](100) SPARSE NULL,
) ON [PRIMARY]
Table with no sparse columns but having same number and datatype ::
-- Table with no Sparse Columns
CREATE TABLE [Tbl_UnSparsed](
[ID] [int] IDENTITY(1,1) NOT NULL,
[C1] [varchar](100) NULL,
[C2] [varchar](100) NULL,
[C3] [varchar](100) NULL,
[C4] [varchar](100) NULL,
[C5] [varchar](100) NULL,
[C6] [varchar](100) NULL,
[C7] [varchar](100) NULL,
[C8] [varchar](100) NULL,
[C9] [varchar](100) NULL,
[C10] [varchar](100) NULL,
) ON [PRIMARY]
Now populate some data in tables to play with
-- Populate Data in Tables
DECLARE @i INT = 0
WHILE @i <=10000
BEGIN
INSERT INTO Tbl_Sparsed VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO Tbl_UnSparsed VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
SET @i = @i+1
END
GO
-- Check the Data
SELECT * FROM Tbl_Sparsed
SELECT * FROM Tbl_UnSparsed
Now we use one on the DMV to check the pages used by both tables, so here it is
-- For Sparsed Table
SELECT 'Pages Used' = page_count FROM sys.dm_db_index_physical_stats (
DB_ID ('Ashu'), OBJECT_ID ('TbL_Sparsed'), NULL, NULL, N'LIMITED');
-- For UnSparsed Table
SELECT 'Pages Used' = page_count FROM sys.dm_db_index_physical_stats (
DB_ID ('Ashu'), OBJECT_ID ('TbL_UnSparsed'), NULL, NULL, N'LIMITED');
So here are the Results…
and if you want to see the Size Used by these Tables then…. just execute a system procedure like this…
-- For Sparsed Table
-- For Sparsed Table
SP_SPACEUSED 'TbL_Sparsed';
GO
-- For UnSParsed table
SP_SPACEUSED 'TbL_UnSparsed';
GO
and here the results are..
So here you can see by using Sparse columns in table we can save Space and Data Pages…
There is some recommendations on using sparse columns as in Percentage of “NULL” value columns.
Thanks & Happy Querying ….
Hi all …
This time a great news for all the technology enthusiast and want to meet great peoples in technology and you missed TechEd India 2009 so you can attend now the same kind of event in Ahmedabad, India on June 20, 2009 Saturday(TechEd on Road) by 1:30 PM.
You will get a chance to meet Two MVP’S here Jacob Sebastian and Pinal Dave , who presents numerous technical sessions on SQL Server, Exchange Server 2010, Windows Server 2008 and Virtualization.
Anyone who is interested in technology can attend this event .This is free event no charges will be taken to attend this event. You can get detailed information about this event on Pinal’s Blog and Jacob’s Blog .
So mark your calendar for this grand event in Ahmedabad here are the details of this event ::
Location ::
Hotel Rock Regency
C.G. Road
Ahmedabad, India.
Date & Time::
June 20, 2009 Saturday
1:30 PM
Hope to see you there on time.
Hi all,
Here is a very powerful feature of SQL Server 2005 is Data Encryption & Decryption, in Sql Server 2000 if we like to Encrypt Data then we have to write our own functions to encrypt the data. In SQL Server 2005 and SQL Server 2008, these functions are available by default.
SQL Server provides the following method to encrypt your data….
- ENCRYPTION by passphrase
- ENCRYPTION by symmetric key
- ENCRYPTION by asymmetric key
- ENCRYPTION by certificate
So here we try to encrypt the data by using Symmetric keys.
So in this we firstly create a database and create master key ,certificate ,symmetric key table and do encryption so here we go…
Create Database ..
USE MASTER
GO
-- First Create a DATABASE
CREATE DATABASE ENCRYPTDECRYPTTEST ON PRIMARY
(NAME=N'ENCRYPTDECRYPTTEST' ,FILENAME=N'D:\ENCRYPTDECRYPTTEST.mdf')
LOG ON
(NAME=N'ENCRYPTDECRYPTTEST_LOG' ,FILENAME=N'D:\ENCRYPTDECRYPTTEST.ldf')
Use database and Create master key, certificate and symmetric key
-- Use database
USE ENCRYPTDECRYPTTEST
GO
-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY
PASSWORD ='ASHISH'
GO
-- Create Certificate
CREATE CERTIFICATE ENCDECTEST
WITH SUBJECT='ASHISH'
GO
-- Check it
SELECT * FROM SYS.CERTIFICATES
-- Create Symmetric Key
CREATE SYMMETRIC KEY TESTKEY
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE ENCDECTEST
GO
In the above create symmetric key T-Sql statement the algorithm parameter is the most important part…
SQL SERVER can use the following algorithms in encryption:
DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.
Now create a table …
-- Create a Sample Table
CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),UPASSWORD VARBINARY(256))
So now its turn to insert data in USERS table and we encrypt the UPASSWORD field using our symmetric key this can be done by a Store Procedure so we create a Insert Procedure for this.. so here is the procedure….
-- Create a Procedure to Insert Data in Table
CREATE PROC [InsertUSER]
@UFNAME VARCHAR(100),
@ULNAME VARCHAR(100),
@ULOGINDI VARCHAR(12),
@UPASSWORD VARCHAR(20)
AS
BEGIN
-- you must open the key as it is not already
OPEN SYMMETRIC KEY TESTKEY
DECRYPTION BY CERTIFICATE ENCDECTEST;
-- Insert statement
INSERT INTO [USERS]
(UFNAME, ULNAME, ULOGINDI, UPASSWORD)
VALUES
(@UFNAME, @ULNAME, @ULOGINDI,
EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
END;
So after creating procedure you can run these procedures by following parameters and check the data in table…
-- Run Insert Procedure
EXEC INSERTUSER 'ASHISH','GILHOTRA','ASHISH','MYPASSWORD'
-- Check the Data
SELECT * FROM USERS
and the results will be like this…
Now we create a Store Procedure to extract the decrypted data so here we go and create a store procedure to extract data in Decrypted form…
and run that procedure…
-- Procedure to Retrive Users
CREATE PROC [RetrieveUSER]
@UID INT
AS
BEGIN
-- you must open the key as it is not already
OPEN SYMMETRIC KEY TESTKEY
DECRYPTION BY CERTIFICATE ENCDECTEST;
-- Select statement
SELECT UID, UFNAME, ULNAME, ULOGINDI, CONVERT(VARCHAR(20),DECRYPTBYKEY(UPASSWORD)) AS PASSWORD
FROM USERS
WHERE UID = @UID;
END;
-- Execute Retrive Procedure
EXEC RetrieveUSER 100
and the results will be like this….
So in this way you can protect your sensitive data like passwords, credit cars numbers and many more,,,,
there also some others way to do encryption in SQL Server 2005 and SQL Server 2008 .
Thanks & Happy Querying…
Hi all,
Today i encounter a issue with my instance as i am using the table SPT_VALUES from the master database available in our Sql Server, so when i tried to fetch some records from master..spt_values then it shows me error…
Here is a example of query and the error thrown by SQL is..
SELECT * FROM orders WHERE order_id in (SELECT number FROM master..spt_values WHERE type='P')
and Error is..
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master..spt_values'.
So it is very uncommon thing to me to loose table from Master Database in Sql Server 2005. But i found that it is not a very uncommon thing because if you can right click on the table present in Master Database and choose the ‘DELETE’ option then table would be deleted from your server.
Now what happen next when tables get deleted from your Master Database, there are some information which stored in Tables in Master Database…
So i googled a lot about this and here is the solution…
There is a script file named “u_tables.sql” in “installation directory >MSSQL>Install” folder .
Execute this script against the instance that has these missing tables, and you will get the tables again.
But this script has limitations because its only able to recreate /reproduce only two System Tables “dbo.spt_monitor and dbo.spt_values” .
So in this way you can get your tables back in Master Database..But it is highly recommended that you don’t delete tables present in Master Database.
Thanks.
Being a .Net developer, the most useful feature i found was the Error Handling or Exception Handling technique.When it comes to programming languages , all popular OOP languages have TRY()…Catch() method to handle an exception.
In Sql Server 2005, error handling was made easy . This article demonstrates various methods of handling errors using
Try()..Catch() in T-SQL.
Here you try something like that…
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
When you try to run this you will get a error like this…
-- Error
Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 1812.
(1 row(s) affected)
Now try to run it something like this….
begin try
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
end try
begin catch
select
error_line() as errornumber
end catch
you will get result like
errornumber
------------------
4
so now this time it throws the number of error.
You can also get more parameters like Error_Line from this like,,,
begin try
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
end try
begin catch
select
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
like this……
Now you can also make a Store Procedure like this which throws error and use this in your try()..catch() statement like this…
CREATE PROCEDURE Sp_ThrowErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO
then try like this..
BEGIN TRY
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
END TRY
BEGIN CATCH
Exec Sp_ThrowErrorInfo
END CATCH;
This gives you same results as above.
You can also use it with a procedure like if you have some kind of procedure than you want to associate with it
Try()…Catch() block then do something like that
First create a Procedure which contains SQL statements to do a specific task, i made a Store procedure which select all the data from a table which doesn’t present in the database
CREATE PROCEDURE Sp_ErrorTest
AS
SELECT * FROM NonexistentTable;
GO
then do like this….
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
EXECUTE Sp_ThrowErrorInfo
END CATCH;
This will show you error,, so in this way you can use Store Procedures in Error Handling
There are many kind of errors which you able to trace with Try()…Catch() statements in T-SQL
The following system functions can be used to obtain information about the error that CATCH block to be executed:
- ERROR_NUMBER() returns the number of the error.
- ERROR_SEVERITY() returns the severity.
- ERROR_STATE() returns the error state number.
- ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
- ERROR_LINE() returns the line number inside the routine that caused the error.
- ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
If these functions are used outside the CATCH block, then all of them will return NULL value.
You can also use Try()..Catch() statement to handle errors that occur inside a transaction, by checking the value of XACT_STATE . The XACT_STATE function determines whether the transaction should be committed or rolled back.
You can use XACT_STATE_ABORT is on, this makes transaction uncommittable when error occurs..
If XACT_STATE =1 , transaction is committable.
If XACT_STATE=-1, the transaction is uncommittable and should be rolled back.
If XACT_STATE=0, means that there is no transaction and a commit or rollback operation would generate an error.
In this way there is a lot more to do in error handling, and there are commands which give you very fine-grained control over small set of errors.
Thanks..
Hi…
In this post , I showed a way to generate 8 characters numeric and alpha numeric values.This can be useful in situations where one has to allocate passwords or unique id to users.
Here is a way in SQL to generate Random, Unique and Reliable Alpha Numeric Values..
This always give you unique, random values but as the length of the data as a result of this is too large so its not easy to remember that.
So if you want to Generate 8 characters Numeric or Alpha Numeric values then here is the script for that.
By this script you generate 8 characters Numeric values
SELECT number/nullif(power(10,len(number)-8),0) AS number FROM
(
SELECT TOP 1 ABS(CHECKSUM(NEWID())) AS number
FROM sysobjects s1 cross join sysobjects s2
) AS T
and here is the Script for generating 8 characters Alpha Numeric values .
DECLARE @alpha_numeric VARCHAR(8)
SET @alpha_numeric=''
SELECT @alpha_numeric=@alpha_numeric+CHAR(n) FROM
(
SELECT TOP 8 number AS n FROM master..spt_values
WHERE TYPE='p' and (number between 48 and 57 or number between 65 and 90)
ORDER BY NEWID()
) AS t
SELECT @alpha_numeric
So in this way you generate 8 characters Numeric and Alpha Numeric values…
You can also change the length of your String by Alter some values in above script.
Thanks…
Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. It helps us to split the larger tables and data in to smaller parts.
In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed.
For data/table partitioning you have to create a partition function first.
When a table is partitioned, it is broken horizontally into smaller table partitions that are used together to assimilate the whole table. To do this, SQL Server has to know how to break the table into smaller parts. This is where a Partition Function comes into play.
Here is a example of partition function
Create Partition Function [PARTITONRANGE] (int)
AS RANGE LEFT FOR VALUES (100000)
Here we use left or right
Left for infinity to VALUE
Right for VALUE to infinity
Now associate partition function with a partition scheme
In a Partition Scheme, you can define that all partitions are mapped to the same filegroup, or you can use the scheme to split up the partitions across filegroups.
so here is our partition scheme..
CREATE PARTITION SCHEME [PartitionScheme]
AS PARTITION [PARTITONRANGE]
All TO ([PRIMARY]);
Now we create a partitioned table. You simply add an “ON” clause to the table creation statement specifying the partition scheme and the table column which it applies.
For example, in this we wanted to create a MyPartitionTable using our partition scheme , you would use the following
T-SQL statement:
CREATE TABLE MyPartitionTable
(ID INT NOT NULL,
Date DATETIME,
Cost money)
ON [PartitionScheme] (ID);
Now you populate some data in your table on which partitioning is done…
declare @count int
set @count =1
while @count <=100
begin
insert into MyPartitionTable select @count,getdate(),100.00
set @count=@count+1
end
set @count =100002
while @count <=100202
begin
insert into MyPartitionTable select @count,getdate(),200.00
set @count=@count+1
end
you can also view how many partitions we did by this..
select * from sys.partitions where object_id = object_id('MyPartitionTable')
By this you can create partitions on your table .
In next post we will se how to make indexes on Partitioned data and how will indexes help us in Performance Tuning.
Thanks
Hi this is first post for INDEXES in SQL Server. In this we learn about indexes , how SQL use indexes and all.
If you have much experience with indexes at all, you are probably already familiar with the difference between clustered and non-clustered indexes. So we look at that whether or not SQL SERVER Query Optimizer will use your indexes or not.
Lets start with an table say IndexTest which has many columns, but we are interested in only “reg_no”.
This table has more 10,000 records for testing purpose now we create a non-clustered index on “reg_no” now we run this query on table and INCLUDE THE ACTUAL EXECUTION PLAN
1: select * from IndexTest where reg_no >='20040510377'
Now we notice that “reg_no” column used in where clause of the query has a non-clustered index on it so it supposed that the Query Optimizer would use the index to produce the requested results but its not and query does a Table Scan operation. This is because Query Analyzer always evaluates whether or not an index is useful before it is used. So “How you would know whether your index is useful or not?”. Its depends on the selectivity of your query “Selectivity” refers to how many rows in a table that are returned by your query so if the number of rows is that are returned back is less than ,it is considered to have high selectivity ,and the index will be used. In my case its only 300 rows , mean if my query returns 300 rows than it uses the index otherwise not here.
Now if you want to find the selectivity of an index then run this.
1: DBCC SHOW_STATISTICS (IndexTest, IX_NonTest)
This will show the statistics of your index and there is a All Density Column which shows average percentage of duplicate rows in an Index.
AND IN CASE OF a CLUSTERED INDEX lets see what happen when we use clustered index on our table.
for this on the same table we first drop that Non-Clustered index and make a new unique clustered index on it
1: -- Drop Index
2: drop index IndexTest.IX_NonTest
3: -- Now Create a New Unique Clustered index on same column of the table
4: CREATE UNIQUE CLUSTERED INDEX IX_UniTest on IndexTest(reg_no)
You can Check your indexes and statistics by this
1: --Check Index and Statistics Date
2: SELECT
3: 'Index Name' = ind.name,
4: 'Index Type'= ind.type_desc,
5: 'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
6: FROM
7: SYS.INDEXES ind
8: WHERE
9: OBJECT_NAME(ind.object_id) = 'IndexTest'
So now you have a Unique Clustered Index on your table on column “reg_no”. In this when we run query like this
query on table and Include Actual Execution Plan
1:
2: select * from IndexTest where reg_no >='20030100451'
Then this query returns ‘9992’ records and this uses the index to requested results and do a Scan Seek operation on this.
But when we run this query
1: select * from IndexTest
then this query don't use any index and return all the records(10,000) and do a Clustered Index Scan operation.
Try one more query which is..
1: select * from IndexTest where reg_no >='-1'
this query also returns all the records(10,000) but do a Clustered Index Seek operation on this.
So here we a lots of things about indexes about non-clustered and Unique Clustered indexes .
There are lot more about indexes in Sql Server 2008 there is a feature is called filtered indexes which is very useful to improve the performance .
Thanks.