How to find the Number of records in a Table ?

-- To fetch the number of records in a table
SELECT ss.name            AS [schema],
       Object_name(si.id) AS TableName,
       Max(si.rowcnt)     AS EstimatedRows
FROM   dbo.sysindexes si
       INNER JOIN sys.tables st
               ON st.object_id = si.id
       INNER JOIN sys.schemas ss
               ON ss.schema_id = st.schema_id
WHERE  indid < 2 -- clustered index or table entry
       AND Objectproperty(si.id, 'IsUserTable') = 1
       AND Objectproperty(si.id, 'IsMSShipped') = 0
       AND Permissions(id) != 0 -- User must have at least some privileges on the object.
       AND Object_name(si.id) LIKE '%Table Name%'
GROUP  BY Object_name(si.id),
          ss.name

ORDER  BY 2

How to fetch the database restore Information ?

-- DB last restore Date

WITH RestoreInformation AS
(
SELECT
    d.name "Database Name",
    d.create_date ,
    d.compatibility_level ,
    d.collation_name ,
    rh.*,
     ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY rh.restore_date DESC)  "RowNumber"
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = d.Name where  d.name ='Database Name'
)
SELECT *
FROM RestoreInformation
WHERE RowNumber = 1


--To fetch the DB restore History
SELECT
    d.name "Database Name",
    d.create_date ,
    d.compatibility_level ,
    d.collation_name ,
    rh.*,
     ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY rh.restore_date DESC)  "RowNumber"
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = d.Name
where d.name ='Database Name' order by RowNumber asc

What is the difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT ?

@@IDENTITY ==> Is a system function that returns the last-inserted identity value for any table in the current session, across all scopes.

SCOPE_IDENTITY() ==> Returns the last identity value inserted into an identity column for any table in the current session and in the same scope.

IDENT_CURRENT ==> Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

GO

-- create a main table

CREATE TABLE test_main(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- insert some records

INSERT INTO test_main(name) VALUES('A'),('B'),('C')

GO

-- create child table

CREATE TABLE test_child(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- create a trigger on main table to insert the reccords into child table

CREATE TRIGGER test_trigger ON test_main FOR INSERT

AS

BEGIN

INSERT INTO test_child SELECT name FROM INSERTED

END

GO

-- check the identity on main table

SELECT * FROM test_main

-- result

1 A

2 B

3   C

GO

-- now insert records into main table and check the identity

INSERT INTO test_main(name) SELECT 'D'

-- now check the below result

select @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT('test_main')

-- result

1 4 4

Useful System Procedures :

sp_help ==> To get a table / object definition

Example :   EXEC sp_help object_name

sp_helptext ==> To get the procedure / view text.

Example :  EXEC sp_helptext Proc_name

sp_helpindex ==> To get the index information on a table

Example :  EXEC sp_helpindex Proc_name

sp_helpconstraint ==> To get the constarints on a table

Example :  EXEC sp_helpconstraint Proc_name

sp_depends ==> To get the dependencies on table / procedure / view.

Example :  EXEC sp_depends object_name

sp_spaceused ==> To get the size of a table or database, if you didn`t pass any input it will show the size of the current database.

Example :  EXEC sp_spaceused table_name

sp_helptrigger  ==> To get the triggers on a table

Example :  EXEC sp_helptrigger table_name

sp_rename ==> To rename database objects and columns.

Example : EXEC sp_rename 'table_name' , 'new_table_name'
     EXEC sp_rename 'table_name.column_name' , 'new_column_name'

sp_tables ==> To get the tables with the name like a given syntax / pattern.

Example : EXEC sp_tables '%user%'

sp_stored_procedures ==> To get the stored procedure names with the name like a given syntax / pattern.

Example : EXEC sp_stored_procedures '%user%'

ACID Properties :

When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics.These characteristics are known as the ACID properties. ACID is an acronym for atomicity, consistency, isolation, and durability.

Atomicity : The atomicity property identifies that the transaction is atomic. An atomic transaction is either fully completed, or is not begun at all.

Consistency : A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state.

Isolation : When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time.

Durability : A transaction is durable in that once it has been successfully completed, all of the changes it made to the system are permanent.

Collation :

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish. Collation determines how your data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width. It's very often important with regards to internazionalization.

It defines how to deal with,
1. Accents (äàa etc)
2. Case (Aa)
3. The language context:
In a French collation, cote < côte < coté < côté.
In the SQL Server Latin1 default , cote < coté < côte < côté
4. ASCII sorts (a binary collation)

SPARSE Columns :

Introduced in MSSQL 2008, Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values.

The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.

Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.


Limitations :
1. Geography, geometry, image, ntext, text, timestamp and user-defined data types cannot be specified as SPARSE.
2. Sparse column cannot have a default value.
3. Computed column can contain a sparse column, but computed column cannot be marked as SPARSE.
4. Sparse column cannot be part of a clustered index or a unique primary key index. However, both persisted and non persisted computed columns that are defined on sparse columns can be part of a clustered key.

Example :
--  Using create table statement

CREATE TABLE user_sparse 
  ( 
     userid   INT sparse, 
     username VARCHAR(10) sparse, 
     gender   CHAR(1) 
  ) 

-- Using alter table statement

ALTER TABLE user_sparse 
ALTER COLUMN gender  CHAR(1) sparse

-- Check the sparse column status in sys.columns table

SELECT is_sparse, 
       * 
FROM   sys.columns 
WHERE  object_id = Object_id('user_sparse') 

MERGE :

Merge statement introduced in MSSQL server 2008. Using merge statement we can include multiple DML operations logic in one statement. It Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

MERGE syntax consists of five primary clauses :

 1. MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
 2. USING clause specifies the data source being joined with the target.
 3. ON clause specifies the join conditions that determine where the target and source match.
 4. WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
 5. OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.

Example : If records are exists in users table then we have to update the records otherwise we have to insert into users table.

-- creating temp table as source
CREATE TABLE #testmerge 
  ( 
     username VARCHAR(10), 
     limitamt NUMERIC(20, 6) 
  ) 

-- populating the source table
INSERT INTO #testmerge 
VALUES     ('satya', 
            20000.000000), 
            ('jyothi', 
             22000.000000), 
            ('suresh', 
             30000.000000), 
            ('gopal', 
             44000.000000), 
            ('ram', 
             20000.000000) 

-- updating / inserting the users table records using MERGE statement
MERGE users AS target 
using #testmerge AS source 
ON target.username = source.username 
WHEN matched THEN 
  UPDATE SET limitamt = source.limitamt 
WHEN NOT matched THEN 
  INSERT (username, 
          limitamt) 
  VALUES (source.username, 
          source.limitamt); 

Table-Valued Parameter :

Table-Valued Parameters are a new parameter type in MSSQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued

parameters to send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters.

Steps to write a procedure using Table-Valued Parameter :

1. Create a table data type and define the table structure.
2. Create a procedure with Table-Valued Parameter.
3. Declare a varaible of table data type and insert the data into table daa type.
4. Pass the table data type to procedure.

Benefits :

1. Table-Valued Parameter give better performance than pass list of parameters to stored procedure or passing values using while loop / Cursors.
2. Do not acquire locks for the initial population of data from a client.
3. Enable you to include complex business logic in a single stored procedure.

Limitations :

1. Table-Valued Parameters can only be indexed to support Unique or Primary Key Constraints.
2. Table-Valued Parameters are read only in stored procedure code. Cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in stored procedures.
3. Table-Valued Parameters design can not be modify using Alter Table statement.
4. Table-Valued Parameters can not pass to CLR user-defined functions.
5. MSSQL Server does not maintain statistics on columns of table-valued parameters.
6. Cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO.

Example :

GO

CREATE TYPE UserTableType AS TABLE
( Username VARCHAR(100)
, is_active bit)

GO

-- Creating Stored Procedure
CREATE PROCEDURE Update_Isactive_Users
    @TVP UserTableType READONLY
    AS
Begin
    SET NOCOUNT ON
Update u set is_active = t.is_active from users u join @TVP t on u.username = t.username
END

GO

-- Defing Table Valued Parameters
DECLARE @UserTVP
AS UserTableType

GO

-- Populating data into @UserTVP
INSERT INTO @UserTVP (Username, is_active)
    SELECT 'satya', 1 union all
SELECT 'jyothi', 1 union all
SELECT 'ravi', 1 union all
SELECT 'naveen', 0 union all
SELECT 'suresh', 0 union all
SELECT 'ganesh', 1 union all
SELECT 'sasi', 0
 
GO

-- Executing Procedure by passing the Table Valued Parameters
EXEC Update_Isactive_Users @UserTVP

GO

QUOTENAME :

Quotename() is a SQL Server String function. It Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

Syntax :

QUOTENAME ( 'character_string' [ , 'quote_character' ] ) 

If the character_string or column value is greater than the 128 characters then quotename function will returns NULL.

Example`s :

SELECT Quotename('satya') 

Result : [satya]

Note : If quote_character is not specified, by default it take brackets.

SELECT Quotename('satya', '(') 

Result : (satya)

SELECT Quotename('satya', '{') 

Result : {satya}

SELECT Quotename('satya', '[') 

Result : [satya]

SELECT Quotename('satya', '''') 

Result : 'satya'

SELECT Quotename('satya', '"') 

Result : "satya"

SELECT Quotename('satya', '<') 

Result : <satya>

Applying QUOTENAME Function to Column :

CREATE TABLE table_quotename 
  ( 
     info VARCHAR(250) 
  ) 

INSERT INTO table_quotename 
SELECT Replicate('satya mssql', 2) 
UNION ALL 
SELECT Replicate('satya mssql', 4) 
UNION ALL 
SELECT Replicate('satya mssql', 13) 

SELECT Quotename(info), 
       Quotename(info, '['), 
       Quotename(info, '{'), 
       Quotename(info, ''''), 
       Quotename(info, '(') 
FROM   table_quotename 

In the above query result last column values are showing as NULL, because values in the third row is greater than 128 characters that`s why result is NULL.