Teaching Pronunciation Workshop

SQLServer Date examples

select year(getdate())-1 as lastyear
select year(getdate()) as currentYear
select day(getdate()) as currentDay
select month(getdate()) as currentMonth

http://msdn.microsoft.com/en-us/library/ms186313.aspx

the magic in 1=1 SQL server

WHERE 1 =
(
CASE
WHEN @Status = 'ALL'
AND 0=0 THEN 1
WHEN @Status = 'updated'
AND KareoId >= 0 THEN 1
WHEN @Status = 'not updated'
AND KareoId is null THEN 1
ELSE 0
END
)

SQL Server 2008 where clause and case ,CASE IN where Clause,1=1,

WHERE 1 =
(
CASE
WHEN @Status = 'ALL'
AND 0=0 THEN 1
WHEN @Status = 'updated'
AND KareoId >= 0 THEN 1
WHEN @Status = 'not updated'
AND KareoId is null THEN 1
ELSE 0
END
)

Rename column name in SQL server 2008

Rename Column
Syntax
sp_RENAME 'table.Column','New ColumnName','column'
EG.
sp_RENAME 'PatientDetail.MRN' ,'KareoMRN', 'column'

to Change Data Type.

alter table PatientDetail alter column MRN varchar(200)

SQL SERVER – Find Current Location of Data and Log File of All the Database

SQL SERVER – Find Current Location of Data and Log File of All the Database

SELECT name, physical_name AS current_file_location
FROM sys.master_files


SELECT name, physical_name AS current_file_location
FROM sys.master_files

SELECT DB_NAME(mf.database_id) AS databaseName
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY 3 DESC

Shrink Whole Database

USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
GO

USE AdventureWorks
EXEC sp_helpfile
GO
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
GO