Ashish's Blog On Sql Server

Who Created a Table – DDL Triggers in SQL SERVER 2008

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..

Encryption & Decryption Part II

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…

Sparse Columns – Introduction

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 ::

1. Storing Null value takes up no space at all.
2. Sparse columns work really well with filtered indexes as you will only want to create an index to deal with non-null values.
3. You can add 30,000 columns as sparse column in table well regular columns has limit of 1024.

On the other side(Disadvantages)

1. It will take 4 extra bytes space to store non null values in it.
2. Sparse column can’t be associated with the data types CHAR,NCHAR,IMAGE,TIME STAMP,GEOMETRY,GEOGRAPHY and USER DEFINES DATA TYPE.
3. Data compression doesn’t work.
4. You can’t apply rules.
5. 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 ….

TechEd on Road – Ahmedabad June 20, 2009

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.