Update with join example

UPDATE Cv
SET cv.Id = CT.Id
FROM XYZ.[dbo].[tblP] CV
INNER JOIN ABC.[dbo].[tblP]  CT
ON CV. Id = CT. Id

Alter Database Mirroring endpoints

Hi , i was doing databases  mirroring with Witness set up in few  SQL server 2008 instances

I used to see lot of times Mirror in disconnected state., so when i went and saw at the SQL Error log on primary server i saw the message

"Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://wedcb469:5023'.
 I  was  able to connect Primary ,Witness and Mirror servers from all 3 servers but still mirror is in disconnected state.

Resolution to the above error is 
 

use master
GO
alter endpoint endpoint_name state = stopped;
GO
alter endpoint endpoint_name state = started;
GO

This solved my problem lot of times ...which also could help you in solving ..


Thanks

Find size of MDF and LDF file of all Databases

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files


This will give you size of MDF and LDF  in MB

Thanks

how to ressed identity

USE [tempdb]
GO
CREATE TABLE [dbo].[Test](
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Name] [varchar](10) NULL
)



INSERT INTO Test (Name)
VALUES('JIM'),('NICK'),('Thomas')

SELECT * FROM test

DELETE FROM   test

INSERT INTO Test (Name)
VALUES('Ray'),('Andrew'),('TIM')


SELECT * FROM Test
you would see the id starts FROM 4 ,so do a reseed to get identity back to what ever position we need , i need to start ID with 1 so

DELETE FROM test

DBCC CHECKIDENT ('Test ', RESEED, 0)
GO
INSERT INTO Test (Name)
VALUES('Ray'),('Andrew'),('TIM')

SELECT * FROM test

Thanks

find max length of a column

  hi , i  used to create VARCHAR(MAX) for columns when ever i see comments , text , one of my colleague told me its not always good idea to use VARCHAR(MAX) data type on columns like that ...
so i thought of finding the max length of the column and then decide how much length should i have to provide , so please find below
 
USE [tempdb]
GO
CREATE TABLE [dbo].[Test](
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Name] [varchar](10) NULL
)

INSERT INTO Test (Name)
VALUES('NJ')


SELECT * FROM test

--Results
--ID  NAME
--1      NJ

 
  SELECT MAX(LEN(Name) )FROM test ---2
 
 
  INSERT INTO Test (Name)
VALUES('NewJersey')

 
    SELECT MAX(LEN(Name) )FROM test ---9

Based on the length we can decide how much max length we can give on a column ..
by doing this it could reduce space in  the memory allocation and beside  there are some cons also in this when we try top insert a row  with more than specified length it might get truncated, so please be careful to implement this

Thanks

 

find index fragmentation

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO
---o is heap
---1 is Clustured index
----2 Non clustured index


based on fragmentation you can either rebuild or re organize indexes , please do it after hours as it is an resource intensive operation and might block users who uses Tables containing indexes


Thanks

send email on specific uses

Hi, i am going to show you how you can email when a table has more records than it should be or you can create the custome code what ever you needed and send an email when you need

DECLARE @test VARCHAR(1000)
SET @test = 'Select top 10 * from sys.messages'
EXEC (@test)
BEGIN
IF @@ROWCOUNT > 20

DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XYZ',
---select * from dbo.sysmail_profile
@recipients = 'name@XYZ.com',--to send group send developers@abc.com--this will send email to all people in group  developers@abc.com
@body = @alertMessage,
@importance = 'High',
@subject = 'testr';
END


ELSE
PRINT' Less '


Thanks

find information on missing indexes

hi,

i use the below DMV to find where i need to add indexes to get performance gain..
Please consult DBA or any senior person before creating any indexes , too many indexes take more space and inserts, updates, deletes take more time and may lead to poor performance 

SELECT * FROM sys.dm_db_missing_index_details

find the Database name and object name
SELECT DB_NAME(11)
SELECT OBJECT_NAME(58483287)

Thanks

Database mail profiles information

USE msdb
GO
SELECT * FROM dbo.sysmail_profile
GO


USE MSDB
GO
SELECT * FROM dbo.sysmail_server---servername
SELECT * FROM dbo.sysmail_log---gives description in detail


---to delete any old and not useful mail profile use
DELETE FROM  dbo.sysmail_profile
WHERE name = 'xyz'

Reading the error log

 hi,

I use this below stored procedure and copy and paste in text editor and search for " Error "  

EXEC xp_readerrorlog 

we can also go under- management -sql server logs and check
every time sql server is restarted a new log is created


also
EXEC sp_cycle_errorlog--- will create a new one and delete oldest one 
like wise for SQL Agent
EXEC sp_cycle_agent_errorlog ----  will create a new one and delete oldest one
----Closes the current error log file and cycles the error log extension numbers just like a server restart.




Thanks

find duplicates in a table

below is the script which will give info on duplicates in  a table

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Thanks

find information about databases

Sp_helpdb

SELECT * FROM sys.master_files

SELECT * FROM sys.database_files
SELECT name, recovery_model_desc FROM sys.databases
where recovery_model=1

Audit sql server failed JOBS

----CREATE THIS AS A JOB  and  RUN WEEKLY  and Table resides in MSDB

USE MSDB
GO

--Variable Declarations
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET     @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days can test with 100 days to make sure this is working
SET     @Year = DATEPART(yyyy, @PreviousDate) 
SELECT  @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT  @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT  @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT  @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET     @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Final Logic
----This table resides in MSDB   , DO FIREST as INSERT INTO
INSERT INTO AGENT_History
SELECT   j.[name] AS Job_name,
         s.Step_Name,
         h.Step_Id,
      CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS DATETIME), 111) RunDate,
      STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, 
         h.Sql_Severity,
         --h.run_duration StepDuration,
CASE h.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus, 
h.run_duration StepDuration,
         h.Message,
         h.Server

FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_status = 0 -- Failure
         AND h.run_date > @FinalDate
--WHERE h.run_date > @FinalDate all days jobs
ORDER BY h.instance_id DESC


GO



Check daily or when ever needed in table
SELECT * FROM AGENT_History

Auto generate numeric id in sql server table

USE tempdb
GO

CREATE TABLE Test(
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)

INSERT INTO Test VALUES ('MIKE')
INSERT INTO Test VALUES ('KELLY')
INSERT INTO Test VALUES ('NICK')
INSERT INTO Test VALUES ('James')
INSERT INTO Test VALUES ('Jones')
INSERT INTO Test VALUES ('Nick')

SELECT * FROM Test

Thanks

how to find who dropped table

make sure you test in non production environments

USE master;
GO
CREATE DATABASE  Test;
GO
USE Test;
GO

CREATE TABLE TestTable (
    c1 INT IDENTITY,
    c2 CHAR (100) DEFAULT 'a');
GO
CREATE CLUSTERED INDEX TT_CL ON TestTable (c1);
GO
INSERT INTO TestTable DEFAULT VALUES;
GO

SELECT OBJECT_ID ('TestTable');---2105058535
GO

DROP TABLE TestTable;
GO

--DROP DATABASE Test
First we need to find the transactions that drop tables in the log. The following code works for 2000, 2005, and 2008:

SELECT [Transaction Id], [Begin Time],  [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

SP_who2---find spid

you will know who dropped the table ..

Thanks

Temp tables VS Table Variables

Temp tables

Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.

Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table. 

Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

Table Variables

These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.

Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

rename stored proceedure

hi,

do not rename a stored procedure using sp_rename ,
its best idea to alter the  stored procedure, script out and save the code,then drop the stored procedure and recreate the stored procedure again  ,

ex:

CREATE PROCEDURE sp_test
AS
SELECT * FROM dbo.test
GO

Rename:

sp_rename 'SP_test', 'SP_test2'

USE DBNAME
GO
sp_helptext  sptest2

We will see the name of the stored procedure is the old name and not the new name but when we try to access the old name

 USE DBNAME
GO
sp_helptext  sptest

 there is an error that sp not found. 

NOTE:

This happens because when the store procedure is renamed,  the sys.procedures system table is not getting updated. The only solution to this is to drop the stored procedure and re-create it with the new name.

Thanks 

Index Seek vs Index Scan

 Basically,
Index Scan:Index Scan retrieves all rows from the table
Index scan touches every row in a table whether or not it qualifies the cost is proportional to the total no of rows in the table 
Index is  nothing but scanning on the data pages from the first pages to the last page , if there is an index on a table and if the query is touching larger amount of data which means query is retrieving more than 50-90% of data and optimizer would just scan all the data pages to retrieve the data rows , if there is no index, then you might see a table scan (index scan) in the execution plan....
If there are no indexes or no useful indexes on a table then sql server has to scan all the records to satisfy querey condition.
An Index scan is complete scan of all pages in a non clustered index
Scan is efficient only if the table is small .

 Index Seek:Index seeks retrieves selective rows from the table
 index seek touches only rows that qualify the pages that contain the qualifying rows , the cost is proportional to the total no of qualifying rows and pages rather than the total no of rows in a table .


Thanks

Update the statistics

    

This is very ineteresting topic and might be helpful ..
i see some people enable AUTO  create and UPDATE statistics on each database.
and some people enable AUTO create statistics but disable UPDATE statistics ..
there are pros and cons for each of them , but i want to focus on databases whose UPDATE Statistics is not enabled ....

We need to update the statistics at least 1 time  a week, as statistics provide good useful info ( road map ) for optimizer to choose which path to go ..
SQL Server uses statistics to react intelligently in its query optimization  
Statistics are automatically created for each index key we create

Check Statistics info:
  

SELECT LastTimeUpdated = STATS_DATE(si.id, si.indid)
    ,TableName = object_name(si.id)
    ,StatisticsName = RTRIM(si.name)
    ,Size = DATALENGTH (si.statblob)
    FROM sysindexes si WITH (nolock)
    WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1
    order by LastTimeUpdated, tablename
   
   
---update all ststistics each  database at a time
EXEC sp_updatestats
---- Clears the procedure cache for the entire server
DBCC FREEPROCCACHE
---
-- Update all usage in the database
DBCC UPDATEUSAGE (0);

NOTE:Update statistics after rebuilding indexes as Index rebuild will automatically Update statistics for index...

how to unlock the lockedout login

you might come across 

Login failed for user 'TEST' because the account is currently locked out. The system administrator can unlock it. 
(Microsoft SQL Server, Error: 18486)

use the below query to resolve the issue 


ALTER LOGIN TEST  WITH PASSWORD = 'ABCDE!##'  UNLOCK
















Thanks

Grant execute any procedure in a database:

hi, 

data reader, datawriter database roles  can read data from tables , write to tables , but cannot view the stored procedure

DDL admin database role : can create table , drop a table but  cannot execute stored procedure

so 

--to execute sp:

 

To grant permissions for a user or role to execute any procedure in a database:

USE MyDatabase

GRANT EXECUTE TO UserOrRoleName;

 

To grant permissions for a user or role to execute any procedure in any database:

EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName'

to execute a stored procedure a user must be granted execute permissions

Thanks

Find usage of Resources by Application

SELECT
     CPU            = SUM(cpu_time)
    ,WaitTime       = SUM(total_scheduled_time)
    ,ElapsedTime    = SUM(total_elapsed_time)
    ,Reads          = SUM(num_reads)
    ,Writes         = SUM(num_writes)
    ,Connections    = COUNT(1)
    ,Program        = program_name
    ,LoginName      = ses.login_name
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
    ON ses.session_id = con.session_id
GROUP BY program_name, ses.login_name
ORDER BY cpu DESC


Thanks

Backup history of individual Database

-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date


Thanks

Table to store 50 States IN USA

                                                   
CREATE TABLE [dbo].[tblState]

(
[StateName] [varchar](50) NULL,
[StateCode] [varchar](4) NULL
)                                



insert into dbo.tblState (StateName,StateCode)
Select 'Alabama', 'AL' UNION ALL
Select 'Alaska', 'AK' UNION ALL
Select 'Arizona', 'AZ' UNION ALL
Select 'Arkansas', 'AR' UNION ALL
Select 'California', 'CA' UNION ALL
Select 'Colorado', 'CO' UNION ALL
Select 'Connecticut', 'CT' UNION ALL
Select 'Delaware', 'DE' UNION ALL
Select 'District of Columbia', 'DC' UNION ALL
Select 'Florida', 'FL' UNION ALL
Select 'Georgia', 'GA' UNION ALL
Select 'Hawaii', 'HI' UNION ALL
Select 'Idaho', 'ID' UNION ALL
Select 'Illinois', 'IL' UNION ALL
Select 'Indiana', 'IN' UNION ALL
Select 'Iowa', 'IA' UNION ALL
Select 'Kansas', 'KS' UNION ALL
Select 'Kentucky', 'KY' UNION ALL
Select 'Louisiana', 'LA' UNION ALL
Select 'Maine', 'ME' UNION ALL
Select 'Maryland', 'MD' UNION ALL
Select 'Massachusetts', 'MA' UNION ALL
Select 'Michigan', 'MI' UNION ALL
Select 'Minnesota', 'MN' UNION ALL
Select 'Mississippi', 'MS' UNION ALL
Select 'Missouri', 'MO' UNION ALL
Select 'Montana', 'MT' UNION ALL
Select 'Nebraska', 'NE' UNION ALL
Select 'Nevada', 'NV' UNION ALL
Select 'New Hampshire', 'NH' UNION ALL
Select 'New Jersey', 'NJ' UNION ALL
Select 'New Mexico', 'NM' UNION ALL
Select 'New York', 'NY' UNION ALL
Select 'North Carolina', 'NC' UNION ALL
Select 'North Dakota', 'ND' UNION ALL
Select 'Ohio', 'OH' UNION ALL
Select 'Oklahoma', 'OK' UNION ALL
Select 'Oregon', 'OR' UNION ALL
Select 'Pennsylvania', 'PA' UNION ALL
Select 'Rhode Island', 'RI' UNION ALL
Select 'South Carolina', 'SC' UNION ALL
Select 'South Dakota', 'SD' UNION ALL
Select 'Tennessee', 'TN' UNION ALL
Select 'Texas', 'TX' UNION ALL
Select 'Utah', 'UT' UNION ALL
Select 'Vermont', 'VT' UNION ALL
Select 'Virginia', 'VA' UNION ALL
Select 'Washington', 'WA' UNION ALL
Select 'West Virginia', 'WV' UNION ALL
Select 'Wisconsin', 'WI' UNION ALL
Select 'Wyoming', 'WY'

Thanks

SELECT * into vs Insert into table from select list (table already exists )

hi today i would like to discuss on few useful tips
SELECT * into vs Insert into from select list

SELECT  into:
EX: SELECT * into TEMP_Backup   from Temp

First a table is created TEMP_Backup    and then data is inserted  into TEMP_Backup from Temp  so there are two steps performed

Where as if i already have a table existing with same structure and datatypes then :
Insert into TEMP_Backup  
SELECT * from Temp

This steps inserts the data to TEMP_Backup   table and here we don't have to create a table , so less overhead if it is with a big table

Both of them works with global, local temporary tables :

Thanks

usage of Replace and Stuff in sql server

Hi,I want to share some info on which i have worked on

EX: if i have a column  Phonenumber Varchar(20) and i have the data as below

SELECT Phonenumber  from yourTablename
Phonenumber
123-456-7890
NULL
''
(123)-456-7890



To make it appear or convert to 1234567890
use Replace function available in SQL server ..



select Phonenumber , replace(replace(replace(replace(Phonenumber ,'(',''),')',''),'-',''), space(1),'')
as CleanedPhone  FROM  yourTablename


To update this to a table:

UPDATE  yourTablename
SET Phonenumber =   replace(replace(replace(replace(Phonenumber ,'(',''),')',''),'-',''), space(1),'')
GO


to revert back or make it as before :
Syntax:

--STUFF( textData, start , length , insertTextData ) 
UPDATE yourTablename
SET Phonenumber =
(SELECT STUFF ((STUFF (phone,4,0,'-' )),8,0,'-') )
GO

Thanks