Ms SQL Useful Stored Procedures

DBCC CHECKDB ('DB_Name')
DBCC CHECKALLOC ('DB_Name')
DBCC updateusage ('DB_Name')
DBCC CHECKIDENT ('dbo.tablename',reseed,0) 
- '0' by default,Seed Value Last Incremented value can be added

EXEC SP_help

EXEC SP_helptext

EXEC sp_renamedb 'oldname' , 'newname';

xp_fixeddrives  - To get HD available in the Server / Disk Space

To Get List of Jobs running in SQL Agent Job

How to find the SQL Agent Jobs  running in your DB Instance and its Steps, query used for the results

 SELECT J.NAME AS 'JOB NAME',

S.STEP_ID AS 'STEP',

S.STEP_NAME AS 'STEP NAME',

S.COMMAND AS 'QUERY',

DATABASE_NAME AS 'DATABASE'

FROM MSDB.DBO.SYSJOBS J

INNER JOIN MSDB.DBO.SYSJOBSTEPS S ON S.JOB_ID = J.JOB_ID

WHERE J.ENABLED = 1

ORDER BY J.NAME,S.STEP_ID

 

How to Enable Automatic Statistic Update on Database

After putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database

Display All IP address used in a LAN

A single line of a command and its arguments that can help display all computer IP addresses (those that are being used) on a LAN,

arp -a

ipconfig /all

net view /all

nmap -sn 192.168.0.0/24 (replacing the sub-net with the appropriate one for your LAN)

MS SQL Server Main Replication Models

Replication Publisher and Subscriber :

To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.

MS SQL : Use of GUID function

If you want to generate a new Guid (Unique Identifier) in SQL server the you can simply use the NEWID() function.

Example

SELECT NEWID( )
GO
-- This will return a new random uniqueidentifier e.g.
E75B92A3-3299-4407-A913-C5CA196B3CAB

To select this Guid in in a variable

--To Assign uniqueidentifier in a variable
DECLARE @EmployeeID uniqueidentifier
SET @EmployeeID = NEWID( )

You can directly use this with INSERT statement to insert new row in table.

-- Inserting data in Employees table.
INSERT INTO Employees
(EmployeeID, Name, Phone)
VALUES
(NEWID( ), 'Shyam Sundar', '123-4567')

MS SQL : Function to Convert INR Value

Function (numberVar Amount)

numbervar RmVal:=0;

numbervar Amt:=0;

numbervar pAmt:=0;

stringvar InWords :="";

stringvar ckAmt:="";

Amt := ABS(Amount);

ckAmt := CSTR(ABS(Amount));

MS SQL Server Recovery Models

One of the first things that needs to be set in order to create the correct backups is to set the proper recovery model for each database.  The recovery model basically tells SQL Server what data to keep in the transaction log file and for how long.  
Based on the recovery model that is selected, this will also determine what types of backups you can perform and also what types of database restores can be performed.

The 3 types of recovery models that you can choose from are:

• Full
• Simple
• Bulk-Logged

What are OLTP and OLAP , its difference?

OLTP vs OLAP

OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).