This is a quick example for CHECK constraints. Let's say you've got an Orders table with a Side column ('side' is trading speak, it just means Buy or Sell). The column is CHAR(1), and you want to allow ONLY 'B' or 'S' to be inserted. This means CHECK constraint. CHECK constraints are used to limit the values that can be written to a column. In this example, we'll create the constraint on the Side column, and prevent anything other than 'B' or 'S' from going into the table.
/* Create the table */
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[TraderID] [int] NOT NULL,
[Side] [char](1) NOT NULL,
[Quantity] [bigint] NOT NULL,
CONSTRAINT [upkcl_Orders_OrderID] PRIMARY KEY CLUSTERED
( [OrderID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MyDatabase_Data]
) ON [MyDatabase_Data]
GO
/* Add your CHECK constraint */
ALTER TABLE [dbo].[Orders] WITH CHECK
ADD CONSTRAINT [ck_Orders_Side] CHECK (([Side]=('B') OR [Side]=('S')))
GOALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [ck_Orders_Side]
GO
/* Test it out */
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'B',25000)
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'S',250)
/* See everything looks good */
SELECT * FROM dbo.Orders
/* Write something to violate the constraint */
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'X',25000)
!!!ERROR!!!!
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_Orders_Side". The conflict occurred in database "MyDatabase", table "dbo.Orders", column 'Side'.
The statement has been terminated.
As you can see, the X got caught by our CHECK constraint... exactly as we intended. Again, just a quick example for CHECK constraints. See this for more information:
http://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx
Friday, January 17, 2014
Create a CHECK Constraint to allow specific values
Thursday, January 16, 2014
Search ALL tables for a specific column
Let's say you need to search for all tables in your database that have a column named 'name'. Using AdventureWorks2012, here's a quick way to do it:
SELECT
t.name [Table],
SCHEMA_NAME(schema_id) [Schema],
c.name [Column]
FROM
sys.tables t INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE
c.name = 'name'
ORDER BY
[Table],
[Schema]
What if you want to rename the column in all tables? You can use this statement to RENAME the specified column in all of the identified tables:
SELECT
'EXEC sp_rename ''' + sc.name + '.' + tb.name + '.' + col.name + ''',''' + tb.name + col.name +''''+ ',N''COLUMN'''
FROM
sys.tables tb INNER JOIN sys.schemas sc
ON tb.schema_id = sc.schema_id INNER JOIN sys.columns col
ON tb.object_id = col.object_id
WHERE
col.name = 'name'
ORDER BY
sc.name
I am using ORDER BY purely for visibility, but you can see that I am renaming all 'name' columns to TableName+Name, like this:
EXEC sp_rename 'Production.Product.Name','ProductName',N'COLUMN'
EXEC sp_rename 'HumanResources.Shift.Name','ShiftName',N'COLUMN'
But remember, this will ONLY work on those tables without dependencies. Anything with dependencies will cause a failure. For example, try to rename the column in the Product table:
EXEC sp_rename 'Production.Product.Name' ,'ProductName'
It will fail with this:
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497
Object 'Production.Product.Name' cannot be renamed because the object participates in enforced dependencies.
That error returns because the Product.Name column is referenced in three functions, two procedures and a view. To my knowledge, there is no way to force your way around this. The dependencies will need to be dropped first, and then recreated after you rename the column.
You can use sys.dm_sql_referencing_entities to identify all Product table dependencies:
SELECT
referencing_schema_name [Schema],
referencing_entity_name [ReferencingObject]
FROM
sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT')
Those objects would need to be altered before Product.Name can be renamed.
That's it. Search for a column in all tables, or rename it... again, in all tables. Obviously, be sure you've got a backup before making any changes. :-)
See both of these for more detail:
sys.dm_sql_referencing_entities
http://technet.microsoft.com/en-us/library/bb630351.aspx
Reserved Key Words
http://technet.microsoft.com/en-us/library/ms189822.aspx
SELECT
t.name [Table],
SCHEMA_NAME(schema_id) [Schema],
c.name [Column]
FROM
sys.tables t INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE
c.name = 'name'
ORDER BY
[Table],
[Schema]
What if you want to rename the column in all tables? You can use this statement to RENAME the specified column in all of the identified tables:
SELECT
'EXEC sp_rename ''' + sc.name + '.' + tb.name + '.' + col.name + ''',''' + tb.name + col.name +''''+ ',N''COLUMN'''
FROM
sys.tables tb INNER JOIN sys.schemas sc
ON tb.schema_id = sc.schema_id INNER JOIN sys.columns col
ON tb.object_id = col.object_id
WHERE
col.name = 'name'
ORDER BY
sc.name
I am using ORDER BY purely for visibility, but you can see that I am renaming all 'name' columns to TableName+Name, like this:
EXEC sp_rename 'Production.Product.Name','ProductName',N'COLUMN'
EXEC sp_rename 'HumanResources.Shift.Name','ShiftName',N'COLUMN'
But remember, this will ONLY work on those tables without dependencies. Anything with dependencies will cause a failure. For example, try to rename the column in the Product table:
EXEC sp_rename 'Production.Product.Name' ,'ProductName'
It will fail with this:
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497
Object 'Production.Product.Name' cannot be renamed because the object participates in enforced dependencies.
That error returns because the Product.Name column is referenced in three functions, two procedures and a view. To my knowledge, there is no way to force your way around this. The dependencies will need to be dropped first, and then recreated after you rename the column.
You can use sys.dm_sql_referencing_entities to identify all Product table dependencies:
SELECT
referencing_schema_name [Schema],
referencing_entity_name [ReferencingObject]
FROM
sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT')
Those objects would need to be altered before Product.Name can be renamed.
That's it. Search for a column in all tables, or rename it... again, in all tables. Obviously, be sure you've got a backup before making any changes. :-)
See both of these for more detail:
sys.dm_sql_referencing_entities
http://technet.microsoft.com/en-us/library/bb630351.aspx
Reserved Key Words
http://technet.microsoft.com/en-us/library/ms189822.aspx
Tuesday, January 14, 2014
What is your database Recovery Model?
The SQL Server database recovery model is a property that dictates how transactions are logged, whether the database transaction log requires backups (or even allows them), as well as what types of RESTORE operations are possible. We have three recovery models: SIMPLE, BULK-LOGGED and FULL.
Typically, you will use FULL or SIMPLE. In my book, it is almost always FULL for mission critical environments with zero down time, and SIMPLE for everything else. But... you will find much more detail about that here:
http://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx
The purpose behind this post is two-fold. First, I want to note that new databases inherit their Recovery Model from the model database. Not too long ago, I found myself and my Team at our weekly meeting, asking each other 'what is the default Recovery Model?'. Regrettably, and somewhat embarrassingly, we were not sure.
Secondly, I just wanted to post a quick tip -- forget about the gui, use this to check your Recovery Model:
SELECT
name [DatabaseName],
recovery_model_desc [RecoveryModel]
FROM
sys.databases
WHERE
database_id > 4
Get rid of that database_id filter, if you want to see your sys dbs, too.
Typically, you will use FULL or SIMPLE. In my book, it is almost always FULL for mission critical environments with zero down time, and SIMPLE for everything else. But... you will find much more detail about that here:
http://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx
The purpose behind this post is two-fold. First, I want to note that new databases inherit their Recovery Model from the model database. Not too long ago, I found myself and my Team at our weekly meeting, asking each other 'what is the default Recovery Model?'. Regrettably, and somewhat embarrassingly, we were not sure.
Secondly, I just wanted to post a quick tip -- forget about the gui, use this to check your Recovery Model:
SELECT
name [DatabaseName],
recovery_model_desc [RecoveryModel]
FROM
sys.databases
WHERE
database_id > 4
Get rid of that database_id filter, if you want to see your sys dbs, too.
Subscribe to:
Posts (Atom)