Saving changes is not permitted in SQL 2008 Management Studio?

I recently started using SQL Server 2008, at first it was really nice, but then I started getting a weird error every time I tried modifying a table.

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created."


The main reason why you will see this error is if you attempted to do any of the following to the table whose design you are saving:

1. Change the Allow Nulls setting for a column
2. Reorder columns
3. Change any column’s data type
4. Add a new column

This is really annoying because you cannot add relationship to your tables once you created them!
The recommended workaround is to script out the changes to a SQL file and execute them by hand, or to simply write out your own T-SQL to make the changes Or

All you need to do is change an option in SQL Management Studio.

Go to Tools » Options » Designers and Uncheck “Prevent saving changes that require table re-creation”

This will fix the problem. No more irritating messages on SQL Management Studio 2008

DateTime Functions

To get current date

SELECT GETDATE()

To get yesterday

SELECT DATEADD(d,-1,GETDATE())

First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)

Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)

First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

Last Day of Current Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))

First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

Last Day of Last Month

SELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

Last Day of Current Year

SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

Last Day of Last Year

SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

Difference between DML DDL DCL and TCL?

DML:

DML is abbreviation of Data manipulation Language. It is useed to work with the data of the tables.

Basic Statements: SELECT, UPDATE, INSERT, delete statements.

DDL:

DDL is abbreviation of Data Defination Language. It is used to build and modify the structure of your tables and other objects in a database.

Basic Statements: CREATE, ALTER, RENAME, DROP and TRUNCATE statements.

DCL:

DCL is abbrivation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Basic Statements: GRANT, REVOKE statements.

TCL:

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Basic Statements:COMMIT, ROLLBACK statements.