Have you ever thought of tracking the values which deleted,updated or even inserted into a specific table. Till SQL 2008 there was a standardized method called creation of trigger to log these details. Here I am going to discuss about a new feature introduced in SQL 2008R2 to capture the data, the feature name says what does it do Change Data Capture.

In this blog I wish discuss about how to enable CDC on a database.

I have created a database named CDC_Test in my Server. To enable CDC to a particular table we should enable the CDC property to the table by running the below command.

-- ================================
-- Enable Database for CDC template 
-- ================================
Use CDC_Test
EXEC sys.sp_cdc_enable_db

After execution of the above command you could see few table starts with CDC in your database.

CDC_01h

Once You have seen this tables in your database you can make sure that the database is CDC enabled.

TO disable the same We have a different system procedure.

USE CDC_Test
GO
EXEC sys.sp_cdc_disable_db
GO

Once You have run the SP in your CDC enabled database, the CDC property of the database will be disabled and all the system tables related to CDC will be dropped from the DB.

CDC_02

Before enabling CDC on a table we should enabled it in DB level.

CDC_03

the above command will help you to identify weather the database is CDC enabled or not.

Advertisements

We have discussed about Template explorer in previous post.Are you still expecting little more customization in it ??

Yes we do have it.

If you have selected a particular template then go to “Query” menu and select “Specify values for ….” or Ctrl + Shift + M.

Template_Param_01

Once you have selected the option you will get a window based on the template available in the query window.

Template_Param_02

All you have to do is to fill the values as per your need ( column name, table name, database name etc.)

Template_Param_03

Once you have done with the entering of values in newly available window just click on “OK” button to effect the changes in query.

Template_Param_04

Hope you have enjoyed to have a tip to Lazy 🙂

How many of You Searched “Google” to add a column or Drop a constraint ?.

We weren’t searching for the functionality we were searching for syntax, isn’t it ??

If the Syntax is already available in SQL, do we ever think about Google ??

Yes it is there, see the below picture.

Template _explorer

The heading shoots a very basic interview question in your mind right ??

We might answered correctly for all the time as “Union avoid duplicates but Union all doesn’t” fine :). We are not going to discuss about the difference between Union and Union All  but about a hidden scenario lies while using Union operator- which can really trap you in interviews.

“If Union remove duplicates, does it remove duplicates within the table?” Yes/ No ? Keep your Answer ready.

See the below example.

Union_unionAll

So If we Union both the tables what result are expecting ?.

1. A completely duplicate eliminated result set.

2. The duplicate inside the table would exist.

Union_unionAll_2

Yes it eliminated all the duplicate rows from the result set(either across/within the tables), so it did a DISTINCT operation on your result set.

So please do comment if you were right/ wrong while in beginning of the post.

Let us consider the same example we have discussed in the previous post.

Case1:

Char(100), Varchar(100) – If we store a value “PRODUCT_04” the net effect on each data type would be like this.

to store this 10 character word “CHAR(100)” would take 100 bytes

to store this 10 character word “VARCHAR(100)” would take 10 + 2 bytes

10 bytes to store the word and 2 bytes to store the length of the word (10 in this case).

Once we select this rows from the database the systems should read the entire 100 bytes in case of CHAR and only 12 bytes in case of VARCHAR.

so that we have seen the huge difference in Estimated Query plan – VARCHAR is faster.

Case 2 :

Char(10), Varchar(10) – If we store a value “PRODUCT_04” the net effect on each data type would be like this.

to store this 10 character word “CHAR(10)” would take 10 bytes

to store this 10 character word “VARCHAR(10)” would take 10 + 2 bytes

in this case CHAR is only considering the exact length, but in case of VARCHAR there is a overhead of 2 bytes – this causes the slight performance improvement in CHAR than VARCHAR – CHAR is faster

Conclusion :

Both are giving a slight variation in performance. Selection of proper data type make sens.

If the length of the value is fixed – Consider CHAR with that fixed length(buffer length for future is not a good practice)

If the length of the value is varying frequently – VARCHAR is the best option with maximum expected length.

Note :

Char is faster than Varchar – Answer is Yes (only if the values fit in the length of the Data type)

SQL Myth : Char vs Varchar

Posted: 23/11/2014 in T-SQL

I am not here to tell you the difference between Char and Varchar(that you will get from various websites).  I wish to prove a common answer I got while taking interviews.

That answer was “Char is faster than Varchar” – is it ??

Let us check .

/*Script to Create table and insert data*/
CREATE TABLE CHAR_VS_VARCHAR_C
(id int identity(1,1),
Product_name CHAR(100))

CREATE TABLE CHAR_VS_VARCHAR_V
(id int identity(1,1),
Product_name varchar(100))

INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_01’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_02’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_03’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_04’)
GO 500

INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_01’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_02’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_03’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_04’)
GO 500

/*Code part End*/

 

After  insertion of these tables, see the performance between two select queries.

 

2_char_vs_varchar

So Varchar is faster ??? isn’t it ?

Let us make a small change in the table definition, I have changed Product_name column length from 100 to 10.

and I have run the same Same query again …

Here is my result.

2_char_vs_varchar+c

 

So char is faster ??

We have learned that Column Length is playing an important role in performance.

Now there is question in my mind , How ??

If you have answer with you , please leave a comment.

I had a myth about SQL from the day I learned CTE and I corrected it a week back.

Did you have the same ? Let us check out.

I am not going to discus about CTE but about one of the main feature about it that can be achieved without CTE.

The Main use CTE – If you Google this the answers would be like this

1. Recursive use of tables ( referring same table multiple times like loop).

2. Delete duplicate rows – one of the most common interview question I faced delete duplicate rows using CTE.

Yes recursion can be achieved by CTE but for duplicate row delete do we really need CTE ???

Just try the below code.

01_Myth

Yes , Let us correct our answers. CTE is not required to remove duplicate rows from a table. Yes it can be achieved in a single query by using derived table.