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 )

SQL Server Global Variables

Global variables represent a special type of variable. You do not need to declare them, since the server constantly maintains them. Global Variable names begin with a @@prefix. Users can not create Global variables and can not update the value of global variables directly in a select statement. The server always maintain the values of these variables.


Global variables were system-supplied, predefined variables that were distinguished from local variables by having two at symbols (@@) preceding their names.  
All the global variables represent information specific to the server or a current session.


Global Variables with their scope : 

1. @@CONNECTIONS

Return : The total number of connections since SQL Server started

Scope : Server

2.@@CPU_BUSY

Return : The Total amount of CPU time, in milliseconds since SQL Server started

Scope : Server

3.@@CURSOR_ROWS

Return : The number of rows returned by the last cursor to be opened

Scope : Connection

4.@@DATEFIRST

Return : The day of week currently set as first day of week. 1 represent Monday, 2 as tuesday and so on , for example : if sunday is the first day of week @@DATEFIRST returns 7

Scope : Connection

5.@@IDENTITY

Return : The last identity value generated for the current connection 

Scope : Connection

6.@@DBTS

Return : Current Database wide time stamp value 

Scope : Database

7.@@Error

Return : The error value for the last T-SQL statement executed 

Scope : Connection

8.@@FETCH_STATUS 

Return : The row status from the last curser fetch command

Scope : Connection

9.@@IDLE

Return : The total number of milliseconds has been idle since it was started

Scopr : Server

10.@@IO_BUSY

Return : The total number of milliseconds SQL Server has been performing disk operations since it was started

Scope : Server

11.@@LANGID

Return : The language ID used by the current connection

Scope : Connection

12.@@LANGUAGE

Return : The language , by name used by the current connection

Scope :  Connection

13.@@Lock_Timeout

Return : The lock timeout setting for current connection 

Scope : Connection

14.@@Max_Connection

Return : The current maximum number of concurrent connection for SQL Server 

Scope : Server

15. @@MAX_PRECISION
Return : The decimal and Numeric Maximum precision setting
Scope : Server
16.@@Options
Return : A binary representation of all the current connection options
Scope : Connection
17.@@Nestlevel
Return : The current number of nested stored procedure
Scope : Connection
18.@@Pack_Received
Return : The total number of network communication packets received by the sql server since it was started
Scope : Server
19.@@Version
Return : The sql server version, edition and service pack
Scope : Server
20.@@TRANCOUNT
Return : The number of Active Transactions for the current connection 
Scope : Connection
21.@@TOTAL_READ
Return : The total number of disk reads by the sql server since it was started
Scope : Server 
22.@@TOTAL_WRITE 
Return : The total number of disk writes by the sql server since it was started
Scope : Server 
23.@@Pack_Sent
Return : The total number of network-communication packets sent by the sql server since it was started 
Scope : Server
24.@@Packet_Errors
Return : The total number of network communication packets recognized by the sql server since it was started
Scope : Server
25.@@PROCID
Return : The store procedure identifier for the current store procedure. This can be used with SYSOBJECTS to determine the name of current store procedure, as example 
Select NAME From SYSOBJECTS where id ==@@PROCID
Scope :  Connection
26.@@REMSERVER
Return : The name of the Login server when the running remote stored procedures
Scope : Connection
27.@@ROW_COUNT
Return : The number of rows returned by the LAST T-SQL Statement
Scope : Connection
28.@@SERVERNAME 
Return : The name of the current server 
Scope : Server 
29.@@SERVICENAME 
Return : The sql server's windows service name 
Scope : Server
30.@@SPID
Return : The current connection's server process identifier - the id for the connection 
Scope : Connection
31.@@TEXTSIZE
Return : The current Maximum size of BLOB data (Text, nText or image )
Scope : Connection
32.@@Timeticks
Return : The number of milliseconds per tick
Scope : Server 
33.@@TOTAL_ERRORS
Return : The total number of disk errors committed by the sql server since it was started
Scope : Server
                  Many of the global variables can be found by the examining the configuration functions , system functions and system statistical functions folder of the object browser, which is the part of the query analyzer.