DB Snapshot

A database snapshot is a read-only, static view of a database, called the source database. In Microsoft SQL Server 2005, you use SQL Server 2005 statements for creating, reverting to, and deleting a database snapshot. However, you can use SQL Server Management Studio to view existing database snapshots.

Example
CREATE DATABASE DAT_SS ON
( NAME = DAT, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DAT_SS.ss' )
AS SNAPSHOT OF DAT;

Connecting to a named instance by using SQLCMD command prompt in Windows Authentication and specifying input and output files

Connecting to a named instance by using SQLCMD command prompt in Windows Authentication and specifying input and output files:

sqlcmd -S \ -i -o

we can execute following command

sqlcmd -S 2282-h9-ing\RAj_LOCAL_SERVER -i SQLQuery1.sql -o result.rpt

where result.rpt , SQLQuery1.sql , SQLCMD utility all are at following location

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

Following link is useful
http://msdn.microsoft.com/en-us/library/ms180944.aspx

Miscellaneous SQL Tips

1.different types of alter command
adding columns in table from query analyzer
alter table Gillette add RptGenerated varchar(200)

Drop column from table
alter table [p9039998 data append] DROP COLUMN COLUMN_Name
alter table [p9039998 data append] DROP COLUMN panelist_inst,status

change datatype
ALTER TABLE table ALTER COLUMN column_name new_data_type Eg. ALTER TABLE MyTable ALTER COLUMN MyColumn NVARCHAR(20)

2.Using Variable as Table name
create proc prc_BdayList
@BdayTableName as varchar(200)
as
begin
set nocount on
if exists(select * from sysobjects where name like @BdayTableName)
begin
exec ('select *,1 as returnvalue from ' + @BdayTableName)
end
end

3. setting ‘select into’ option true through sp_dboption procedure
EXEC sp_dboption 'hhwebsite', 'select into/bulkcopy','true'

4.Clearing DB Log
BACKUP LOG [HH_inventory] WITH TRUNCATE_ONLY

5.DB Shrinking queries
DBCC SHRINKFILE (hhwebsite_data, 700)
DBCC SHRINKDATABASE (hhwebsite, 800)
ALTER DATABASE hhwebsite
MODIFY FILE (NAME = hhwebsite_log, SIZE = 90MB)

6. dbcc shrinkdatabase (hhwebsite)
sp_helpdb hhwebsite

7. getting count without using group by , summing the resultant 1
Sum( case when pd.income in (6,7,8,9,10,11) then 1 Else 0 End) '$35k-$75k',
Sum( case when pd.income in (12,13,14) then 1 Else 0 End) '$75k-$150k',
Sum( case when pd.income in (15,16) then 1 Else 0 End) '$150k+'

8. shortcut to insert comment ctrl+shift+c
shortcut to remove comment ctrl+shift+r

9.to get the field N other details of table
sp_help tablename
Keyboard shortcut for the same select table name alt+f1

10. Using NEWID to Randomly Sort Records
select top 1 panelist_inst from dbarea.dbo.winner_panelist order by newid()
newid() returns a unique record each time it is selected
eg.
declare @i int
set @i=0
while @i<2 i="@i+1">11. delete from one table using join on the basis of criteria match from some other table
delete table2 from table2 (nolock)
Inner join table1(nolock)
on table2.DMA= table1.DMA and table2.gender=5

12. SELECT TABLE NAME BASED ON THE OWNER OF THE TABLE
USE DBNAME (DB WHERE TABLES ARE)
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA like '%ASIA\rkrai%'

13. updating table using update lock hint
update dbarea..raj set resp_id='test' from dbarea..raj (updlock)

14. ‘ mystry is ‘’’’ will be printed as ‘ because first will string starter , second string escape equence third is strinf itself and last is termination

15. select @@servername gives current server name

16. method to avoid error while using an index and if it is not in DBSET NOCOUNT OFFUSE pubsIF EXISTS (SELECT name FROM sysindexes WHERE name = 'zip_ind') DROP INDEX authors.zip_indGOUSE pubsGOCREATE NONCLUSTERED INDEX zip_ind ON authors (zip) WITH FILLFACTOR = 100

17.substring
functionsubstring(expression,start,length)select top 1 substring(resp_id,0,len(resp_id)-1),resp_id from panelist(nolock)

18. statistics commands
DBCC SHOW_STATISTICS (UPSPanelisttest ,IND_PID)
update statistics UPSPanelisttest [IND_PID]

19. to get whether a temp table has been created or not
if exists (select top 1 1 from tempdb.dbo.sysobjects (nolock) where type='u' and name like '%#temp_First%')
Drop Table #temp_First

20 Removing time from datetime field
Select CAST( FLOOR( CAST( last_invite_Date AS FLOAT ) ) AS DATETIME)
From panelist

21. Adding identity column through query
alter table dbarea..final
add no int identity

22.CHARINDEX ( expression1 , expression2 [ , start_location ] )
Where expression1 is the string of characters to be found in expression2,
and start_location is the position where the CHARINDEX function will start looking for expression1 in expression2.

23. To remove time from datetime field
Select top 10 convert(varchar(50),CreateDate, 101) from invitepanelist (nolock)

24. Rename a table
This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'

25.SET (Transact-SQL)
The Transact-SQL programming language provides several SET statements that change the current session handling of specific information. The SET statements are grouped into the categories shown in the following table.
SQL-92 Settings statements
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS

To Get Version of SSIS

run DTEXEC /Ver from the command prompt it will give you Version
remember version gets increamented when service pack is applied on it

Calling functions using Linked Server on SQL 2005 (Openquery)

instead of the following query
Select * from Linked_Server.northwind.dbo.square_value(10)
run a query with the Openquery function:

Select * from Openquery(Linked_Server,'select northwind.dbo.square_ value(10)')

If the user-defined function takes variable or scalar parameters, you can use the sp_executesql stored procedure to avoid this behavior. For example:
exec Linked_Server.northwind.dbo.sp_executesql N'SELECT northwind.dbo.square_value(@input)',N'@input int',@input=10

Getting SQL Server Details

To Get Service Pack Of SQL Server

SELECT SERVERPROPERTY('productversion') as 'Version Number', SERVERPROPERTY ('productlevel') as 'Service Pack', SERVERPROPERTY ('edition') as 'Edition'

To Get bit version of SQL Server

SELECT @@VERSION

MY SQL LEARNING

1. Changing object Ownership
EXEC sp_changeobjectowner 'CurrentOwner.ObjectName, 'NewOwner';
eg
EXEC sp_changeobjectowner 'IAM\vnaraya.RPT_HLD_PROF, 'DBO';

***********************************************

2.Steps to rename a Database
sp_renameDB
detach DB
rename mdf ldf
attach DB

script is following
--renaming database
use master
sp_dboption 'OLDDBNAME', 'single user', 'TRUE'
go
sp_renamedb 'OLDDBNAME', 'NEWDBNAME'
go

--Detaching database
EXEC sp_detach_db 'NEWDBNAME', 'true';
go

--Rename MDF and LDF file manually

--Attach Database
EXEC sp_attach_db @dbname = N'NEWDBNAME',
@filename1 = N'E:\Program Files\Microsoft SQL Server\MSSQL\Data\NEWDBNAME_Data.MDF',
@filename2 = N'F:\Program Files\Microsoft SQL Server\MSSQL\log\NEWDBNAME_Log.LDF'

--Take full backup of new database
use dba_perform
sp_dba_backup_full 'NEWDBNAME'
sp_dboption 'NEWDBNAME', 'single user', 'False'
go

******************************************