Archive

Archive for the ‘SQL Server Questions’ Category

What is the use of SQL server table compression – MSDN TSQL forum

September 30, 2014 Leave a comment

–> Question:

1. What is the use of the table compression?

2. When do we need to compress the table ?

3. If i compress the table what will be the performance impact?

 

–> My Answer:

1. What is the use of the table compression?
Reduction in DiskSpace as well as reduction in I/O happening across memory and CPU.

2. when do we need to compress the table ?
If your table column contains lot of wide character strings (char/varchar) columns, then you will get the best compression. Can go for Row/Page level compression, Page level has highest level of compression.

3. If i compress the table what will be the performance impact?
No, in most of the cases. But you will get good perf because of reduction in I/O, because many times I/O is the main culprit. CPUs being more powerful can compress/uncompress data within no time compared to the time taken by I/O.

 

–> Another Answer by Erland:

There are two levels of compression: ROW and PAGE. ROW is basically a different storage format, which gives a more compact format for most data profiles. Not the least if you have plenty of fixed-length columns that are often NULL. ROW compression has a fairly low CPU overhead. Since compression means that the data takes up less space, this means a scan of the full table will be faster. This is why you may gain performance.

Page compression is more aggressive and uses a dictionary. You can make a bigger gain in disk space, but the CPU overhead is fairly considerable, so it is less likely that you will make a net gain.

To find out how your system is affected, there is a stored procedure, of which I don’t recall the name right now, which can give you estimated space savings. But if you also want to see the performance effects, you will need to run a test with your workload.

There is also columnstore, which also is a form a compression, and which for data warehouses can give enormous performance gains.
 

Ref Link.

Categories: SQL Server Questions Tags: ,

Difference between Temporary Table and Table Variable, which one is better in performance? – MSDN TSQL forum

September 15, 2014 Leave a comment

–> Question:

Anyone could you explain What is difference between Temp Table (#, ##) and Table Variable (DECLARE @V TABLE (EMP_ID INT)) ?

Which one is recommended to use for better performance?

Also is it possible to create CLUSTER and NONCLUSTER Index on Table Variables?

In my case: 1-2 days transnational data are more than 3-4 Millions. I tried using both # and table variable and found table variable is faster.

Is that Table variable using Memory or Disk space?
 

–> My Answer:

Check this link to see differences b/w Temp Table & Table Variable.

TempTables & TableVariables both use memory & tempDB in similar manner, check this blog post.

Performance wise if you are dealing with millions of records then Temp Table is ideal, as you can create explicit indexes on top of them. But if there are less records then Table Variables are good suited.

On Tables Variable explicit index are not allowed, if you define a PK column, then a Clustered Index will be created automatically.
 

Ref Link.

Categories: Differences, SQL Server Questions Tags: , , ,

Most effective way to write SQL Statement to Catch and Reverse errors during Query execution – MSDN TSQL forum

September 11, 2014 Leave a comment

–> Question:

I am wondering what is the most effective way to deal with errors, specifically in a Stored Procedure.

I wrote something like this:

BEGIN TRY
BEGIN TRANSACTION

/*My statements goes in here*/

IF ERROR_NUMBER() = 0 -- Do I need this line?

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF ERROR_NUMBER() > 0 --Do I need this line?

ROLLBACK TRANSACTION;

END CATCH;

It would make sense using the if Statement when attempting to log errors.
 

–> My Answer:

Checking ERROR_NUMBER() within TRY BEGIN-COMMIT TRANS block is not a good idea, because as soon as an error is reported the control will be immediately redirected to the CATCH block.

So, use it like this:

BEGIN TRY
BEGIN TRANSACTION

/*My statements goes in here*/

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

;THROW

ROLLBACK TRANSACTION;

END CATCH;

Check my other blog posts on error/exception handling.
 

Ref Link.

Categories: SQL Server Questions Tags: , ,

How to convert ‘20140620’ date to ‘DD-MM-YYYY’ format – MSDN TSQL forum

June 12, 2014 Leave a comment

–> Question:

I have data for date fields like “20140620”(YYYYMMDD) while inserting into the table it should be in the format “DD-MM-YYYY”

Can any one please help me to achive this.
 

–> My Response:

SELECT FORMAT(CAST('20140620' as DATE), 'dd-MM-yyyy', 'en-US'), CAST('20140620' as DATE)

… this will work with SQL Server 2012 and above.
 

–> Other Responses:

-- by Olaf Helper
SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY

-- by Latheesh NK
SELECT CONVERT(VARCHAR(10), '20140620', 105)

 

Ref Link.

Categories: Datatypes, SQL Server Questions Tags: ,

Cannot use Temp Table and Table Variable in Views, why? – MSDN TSQL forum

February 14, 2014 Leave a comment

–> Question:

I Know we cannot use Temp table, Table variable in View, but I want to know the reason behind it?
 

–> My Answer:

Because View is nothing but a simple stored query. A View definition just contains one single query, which can pull data from one or many tables by using JOINS. Every time a View is executed it is expanded upto the Query level and that Query is executed by the SQL Engine internally every time.

It is not like Stored Procedures where you have bunch of queries with control flow with loops, and you create temp-tables/variable for temporary storage. If you would like to add temp data use Stored Procedures instead of Views.

Views are just used to simplify complex queries usage, and saves us to re-write same code/query again and again.
 

–> Another Answer by Oalf:

a local temporary table is only available in the session where it has been created and only as long as the session exists, as soon as you close the session the temp table will be automatically dropped; so how should it work, creating a view on a temp table which exists for only a may very short period and at all, what should that be good for?
 

–> Another Answer by Erland:

There is certainly a good reason for views not supporting table variables. Table variables are only visible in the batch they are created in, and the CREATE VIEW statement must be in a batch of its own, so the table variable never exist when the CREATE VIEW statement runs.

For temp tables, it would certainly be possible. The view would become invalid as soon the scope where the temp table is creates is exited, but then again tables can be dropped to. However, there are some interesting questions.
 

Ref Link.

Categories: SQL Server Questions Tags: ,