Archive

Posts Tagged ‘MSDN TSQL forum’

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

MERGE data from source Table to target Table across Databases with a SP – MSDN TSQL forum

December 18, 2013 Leave a comment

–> Question:

How do I merge data from Table1 on Server-1 to final table on Server-2 with a Stored Procedure to execute every so many hours?
 

–> My Answer:

A basic approach would be to create a Linked Server in Server2 that will point to Server1.

On Server2 you can create a Stored Procedure, which will read the table via Linked Server from Server1. You can use MERGE statement to merge (or INSET/UPDATE/DELETE) records in final table on Server2 form table1 in Server1.

If you are dealing with millions of records then you can go with Incrememtal/Delta load approach, here you will have to store the timestamp of previous load in some meta-data table.

To keep executing every 2 Hrs, you will need to create a SQL Agent Job, and schedule it to run for ever 2 Hrs.

You can also use SSIS or other ETL tool as a better approach to setup this load.
 

Ref Link.

Categories: SQL Server Questions Tags: , , ,