Advertisements

Archive

Archive for the ‘Misconception’ Category

Difference between Decimal and Numeric datatypes

June 8, 2012 4 comments

Decimal vs Numeric: None… both are same!!!

This is a misconception among many developers that Decimal and Numeric are different data types. And lot of people do not agree to understand that they are same unless I redirect them to this MSDN BOL [link].

Few days back while designing a data model for one our support enhancement project this question was raised and following was the conversation:

Person A: Ok, this column is defined as Numeric, can’t we make it a Decimal?
Me: What difference will it make?
Person A: Decimal is more precise than Numeric (according to him).
Me: No, both are same.
Person B, C and some others: There is slight difference, Decimal holds more accurate precision values than Numeric.

To my surprise none of them were aware that both are same.

So, when I redirected them to MSDN decimal and numeric (Transact-SQL) and show them the line which says: “numeric is functionally equivalent to decimal.” they agreed.

So, not to confuse between Decimal & Numeric, they both are synonyms to each other.

Advertisements

Table variables are not stored in memory, but in tempdb

July 20, 2010 2 comments

Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.

–> To prove this I executed following code:

-- Batch #1
SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

-- Batch #2
CREATE TABLE #tempTab (j INT)
INSERT INTO #tempTab VALUES (1)
SELECT * FROM #tempTab

SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

-- Batch #3
DECLARE @tabVar TABLE (i INT)
INSERT INTO @tabVar VALUES (1)
SELECT * FROM @tabVar

SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

DROP TABLE #tempTab
GO

Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.

Batch #2: The second batch of stmt gives me 1 row with following table name:

#tempTab_____________________________________________________________000000019D39

Batch #3: and the third batch of stmt gives me 2 rows with following table name:

#tempTab_____________________________________________________________000000019D39
#0C4F413A

This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.

–> Check the full demo here:

SQL Server - Table Variable - YouTube