SQL SERVER : Difference between SQL Server Temporary table and Table Variable

Temporary Table :

Temporary tables are created in TEMP DB. A temporary table is easy to create and back up data. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE,INSERT and DELETE statements.If temporary table created inside a store procedure they are destroyed upon completion of the stored procedure.Temporary tables are real tables so you can do things like CREATE Indexes etc. The scope of any temporary table is the session in which it is created.You can create a temporary table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temporary table structure upfront.

There are two different temporary table types : Local and Global 

Local temporary tables are prefixed with a single # sign.Local temporary table tables are available for use by the current user connection that created them.Multiple connections can create the same named temporary table for local temporary tables without encountering conflicts.The internal representation of the local table is given a unique name,so as not to conflict with other temporary tables with the same name created by the other connections in the TEMP database. Local temporary tables are dropped by using the DROP statement or are automatically removed from memory when the user connection is closed.

Global temporary tables are prefixed with a double ## sign. Global temporary table have different scope from local temporary tables.Once connection creates a global temporary table, any user with proper permission to the current database he is in, can access the table.Unlike local temporary tables, you can not create simultaneous versions of a global temporary tables, as this will produce a naming conflict.Global temporary tables are removed from sql server if explicitly dropped by DROP Table. They are also automatically removed after the connection that created it disconnects and the global temporary is no longer referenced by the other connections. 

The Syntax to creating a temporary table is similar to creating a physical table in SQL Server with the exception of the a aforementioned pound sign(#):

CREATE TABLE #TmpTable

(

ID INT NOT NULL,

Name VARCHAR(20)

)

Table Variables:

A table variable is a data type that can be used within a Transact-SQL batch, stored procedure, or function.Table variables are good replacements of temporary tables when the data set is small. Statistics are not maintained for table variables like they are for regular or temporary tables, so using too large a table variable may cause query optimization issues.Unlike regular tables or temporary tables , table variables can't have indexes or FOREIGN KEY constraints added to them. Table variables do allow some constraints to be used in the definition (PRIMARY KEY , UNIQUE,CHECK ). Table variable performance suffers when the result set becomes too large or when column data cardinality is critical to the query optimization process.

The syntax to creating a table variables is similar to creating a table, only the DECLARE keyword is used and the table name is prefixed with an @ symbol :

DECLARE @TableName TABLE

( 

ID INT NOT NULL,

Name VARCHAR(20)

)

The main difference between temporary table and table variable is that transaction logs are not recorded for the table variables. Hence they are out of scope of the transaction mechanism. Both table variables and temporary tables are stored in TEMP database. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation.Temporary tables and table variable will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database. Depending on the situation as well as the version and service pack of SQL server that is installed, the performance metrics of table variable versus temporary tables can change. Unless the performance metrics change significantly using one or the other, your best option is to pick the one that makes the most sense in the current situation and stick with it. 

SQL SERVER : Get an accurate count of the number of records in a table

The main basic method to find the record count of table are :

1. Select * from <table_name>

2. Select Count(*) from <table_name>

3. Select  rows, rowcnt from sysindexes where id = OBJECT_ID(<table_name>) AND indid < 2

Here are some other method to get the record count of SQL Server table :

SQL SERVER : RESET Identity Column in SQL SERVER Table

The Following Statement RESET the Identity Column in Required table :

DBCC CHECKIDENT (<table_name>, RESEED, <reseed_value>)

With the help of TRUNCATE statement you can reset the value of identity column but TRUNCATE will the reset value to Identity value which is defined at the time of table creation.

NOTE : IDENTITY Column does not  Stand for uniqueness unless you don't define identity column as part of Primary key.

Here is the Sample Code :

How to Display/Remove Lines number In SQL Server -2005 Query Window of SSMS (Sql Server Management Studio)

By Default line numbers are NOT shown in Query pane in SSMS (2005) query window.

You need to follow below steps to Capture the line numbers in in Query plane. You can add / remove as per your requirement.

1. Select "Tool" from Menu bar, Now select "Options".

2. Then expand the "Text editor"  option from appearing window.

3. Expand the  "All language " option.

4. Finally Select the "General "  option and place a check in the box  "Line Number" in lower display screen.

Now when you open a new query window line numbers will appear. Reverse the steps for removing the line numbers.

SQL SERVER : Remove / Drop a column from an existing Sql server table

Remove a column from a Sql server table use the following code :

Syntax :

ALTER TABLE [table_name] 
DROP COLUMN [column_name] 

E.g. :

ALTER TABLE tbl_main
DROP COLUMN App_Id

SQL SERVER : Alter The Column Size In SQl Server table

Changing the Size of Column :

You can change the Length, Precision or Scale of a column by specifying a new size for the column datatype in the ALTER COLUMN Clause.

If Data Exist in the column , the new size can not be smaller than the maximum size of the data. Also the column can not be defined in an Index, Unless the column is a varchar, nvarchar or varbinary data type and  the Index is not the result of a  PRIMARY Key constraint.

Syntax :

ALTER table <table> alter column <coumn> nvarchar(biggernumber) [NOT] NULL

Example :

Use Val_Invoice_Data
GO
ALTER table [tblvaluation] alter column valuationtype varchar(MAX)

SQL Server String Functions

SQL Server string functions are mainly used to change the case of string,concatenate string, reverse string,extract various part of strings and perform many other types of string operations.
All functions are Deterministic or Non-deterministic :

• Deterministic functions always return the same result any time they are called with a specific set of input values.
• Non-deterministic functions may return different results each time they are with a specific set of input values.

All built-in string functions ,except for CHARINDEX and PATINDEX , are deterministic. In SQL Server there is several built-in string functions to perform string manipulations.All below functions takes string input value and return a string or numeric values.

ASCII : Function returns the ASCII code from the leftmost character specified character expression.

Syntax : ASCII (character_expression)

CHAR : Convert an int ASCII to character.

Syntax : CHAR (character_expression)

CHARINDEX : Indicates the first position of a character or a character string within another character string. Function takes two arguments. 1st arguments specifies the character whose index is to retrieved and 2nd argument takes as a string from which character index is carried out.

Syntax : CHARINDEX (expression1,expression2 [, start_location] ) 
DIFFERENCE : Returns an Integer value that indicates the difference the SOUNDAX values of two character expressions. 
Syntax : DIFFERENCE(character_expression, character_expression)
LEFT : Returns left part of a string with the specified number of characters counting from left. LEFT function is used to retrieve portions of string.
Syntax : LEFT(character_expression , Integer)
LEN : Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
Syntax : LEN(character_expression)
LOWER :Returns a character expression after converting uppercase character data to lowercase.
Syntax : LOWER(character_expression)
LTRIM : Returns a character expression after it removes leading blanks.
Syntax : LTRIM(character_expression)
NCHAR :Returns the Unicode character with the specified integer code, as defined by the Unicode standard. 
Syntax : NCHAR(Integer_Expression )
PATINDEX : Returns the starting Position of the first occurrence of the pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. 
Syntax : ('%pattern%', expression)
QUOTENAME : Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL server 2005 delimiter identifier.
Syntax : QUOTENAME( 'character_string' [,'qouted_character' ] )
REPLACE : Replaces all occurrences of the second specified string expression in the first string expression with a third expression.
Syntax : REPLACE('string_expression1', 'string_expression2', 'string_expression3')
REPLICATE : Repeats a character expression  for a specified number of times.
Syntax : REPLICATE (character_expression , Integer_expression)
REVERSE : Returns the reverse of a character expression.
Syntax : REVERSE ( character_expression )
RIGHT : Returns the right part of a character string with the specified number of characters.
Syntax : RIGHT( character_expression , integer_expression )
RTRIM : Returns the character string after truncating all trailing blanks.
Syntax : RTRIM ( character_expression )
SOUNDAX : Returns a four character code to evaluate the similarity of two strings.
Syntax : SOUNDAX( character_expression )
SPACE : Returns a string of repeated spaces.
Syntax : SPACE ( integer_expression )
STR : Returns character data converted from numeric data.
Syntax : STR ( float_expression [ , length [ , ] ] )
STUFF : Deletes a specified length of characters and inserts another set of characters at a specified starting point.
Syntax : STUFF ( character_expression , start , length , character_expression )
SUBSTRING :  Returns part of character , binary , text or image expression.
Syntax : SUBSTRING ( expression , start, length )
UNICODE : Returns the integer value, as defined by the Unicode standard , for the first character of the input string.
Syntax : UNICODE ( 'ncharacter_expression' )
UPPER : Returns a character expression with lowercase character data converted to uppercase.
Syntax : UPPER ( character_expression )