To find status of your DB in your SQL Server.

A simple script that is enable me to save lot of time, to find out which database is in runnable status in my server. you can use SQL Profiler but i find this one is very simple for me.hope it helps you friends!!!!

EXEC sp_who

Difference b/w Normal / NoCompressed Backup Files in SQL

An SQL Query that gives me 3-4 times better performance and less size than Normal / NoCompressed Backup

Compressed Backup

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseCompression.BAK' WITH COMPRESSION ,INIT

--BACKUP DATABASE successfully processed 172926 pages in 40.806 seconds (34.715 MB/sec).

Not Compressed Backup

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseNoCompression.BAK' 

--BACKUP DATABASE successfully processed 172926 pages in 111.007 seconds (12.761 MB/sec). 

See the time taken; its almost 1/3 of NoCompression Backup. The size of the backup files Compressed is 197 MB and Uncompressed is 1386 MB.

Finding if Current Week is Odd or Even – Script

To Finding if Current Week is Odd or Even – Script

DECLARE @CurDate DATETIME
SET @CurDate = GETDATE()
SELECT
WeekOfMonth = DATEPART(wk, @CurDate)
- DATEPART(wk,DATEADD(m, DATEDIFF(M, 0, @CurDate), 0)) + 1,
CASE WHEN (DATEPART(wk, @CurDate)
- DATEPART(wk,DATEADD(m, DATEDIFF(M, 0, @CurDate), 0)) + 1) % 2 = 1
THEN 'Odd' ELSE 'Even' END EvenorOdd

How to Disable and Enable All Constraint for Table / Database

To enable or disable all the constraint for single table or database.

-- Disable all table constraints

ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE YourTableName CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint

-- Enable single constraint

ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint

-- Disable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

MS SQL 2005 : To Check the size of the Database

Use the Below Query to Check the Database Size with all *.mdf & log.*ldf file sizes

****QUERY****

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

Size of DB - shyamb4u

Generating CSV file using SQL Server 2005

A lot of times it is our inability to learn some of the simple techniques present in the tools that we use make us even more productive.

Here are the steps to achieve the same. 

Go to SQL Server Management Studio ---> Choose Tools from Menu Bar 
--> Click on Options.
 Go to “Query Results” ---> “SQL Server” ---> Click on “Results to Text”

shyamb4u

Learn Access 2007 in 30 Days.

Access 2007 is the database software in the Microsoft 2007 Office suite that allows you to order, manage, search, and report large amounts of information.

The below link provides complete source and learning process of Access 2007

CLICK TO DOWNLOAD - Access 2007