Add Constraint

Posted on July 25, 2009 by Derek Dieter
3

To add a constraint to an existing table use the alter table statement with the add constraint command. There are four different types of constraints: Primary Key Constraints – Enforces unique values for specified column, can be referenced. Foreign Key Constraints – Enforces a reference to a primary key Unique Constraints – Ensures unique values within a column Check Constraints – Limits values acceptable for a column based on an evaluation Add unique constraint [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique UNIQUE (President) [/cc] Add constraint to test value [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique CHECK (YearsInOffice >= 0 AND YearsInOffice < 13) [/cc]

Create Index Syntax

Posted on July 25, 2009 by Derek Dieter
2

To add an index in SQL Server use the CREATE INDEX statements. When adding indexes remember there can only be one clustered index per table. The main options when creating an index are clutered or nonclustered or unique vs non unique. Using SQL 2005+ you can also specify columns to include at the leaf level of the index. Create a single nonclustered index [cc lang=”sql”] CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name [/cc] Create a multi-column (composite) nonclustered index [cc lang=”sql”] CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber_PresidentName — specify index name ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names [/cc] Create a multi-column (composite) clustered index [cc lang=”sql”] CREATE UNIQUE CLUSTERED INDEX IX_C_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names [/cc] Create a non clustered index with included columns [cc lang=”sql”] CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name INCLUDE (President,YearsInOffice,RatingPoints) — specify included columns [/cc] Create index with fill factor [cc lang=”sql”] CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name WITH (FILLFACTOR = 80) — specify the fill factor [/cc] SQL Server 2008 options SQL Server 2008 provides additional index options. Most notably it provides the ability to filter an index. This can help decrease the size of the index for very large or partitioned table to only include the records that are normally […]

Add Primary Key

Posted on July 25, 2009 by Derek Dieter
27

In order to add a primary key to an existing table we need to use the Alter Table command. Add a primary key for a new column [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD PresidentID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_PresidentID PRIMARY KEY NONCLUSTERED [/cc] Note however that the ordering of the identity field will not be predictable for the records that already exist in the table. For newly inserted records, the identity field will increment properly. Add a primary key for an existing column [cc lang=”sql”] ALTER TABLE dbo.Presidents WITH NOCHECK ADD CONSTRAINT PK_PresidentNumber PRIMARY KEY NONCLUSTERED (PresidentNumber) [/cc]

Add User Role

Posted on July 25, 2009 by Derek Dieter
No

Roles can be created inside a database that encapsulate permissions. These permissions can be granular (as in denying read permissions to one table), or they can be global (as in being able to write to all tables). Using the CREATE ROLE state we will walk through some typical examples. First of all, we need to create an empty shell of a role that will not have any securables [cc lang=”sql”] CREATE ROLE [ExecDBO] AUTHORIZATION [dbo] [/cc] The code above did not define any permissions, it only created an empty role. The authorization keyword defines the dbo user as being the “owner” of this role. A securable is an object-security relationship Now we will define some securables for this role [cc lang=”sql”] GRANT EXECUTE ON SCHEMA::dbo TO [ExecDBO]; GRANT SELECT ON SCHEMA::dbo TO [ExecDBO]; [/cc] Above we have just granted read and execute on the entire ‘dbo’ schema.

Add Users to Database Script

Posted on July 25, 2009 by Derek Dieter
20

The following script will add users to a database by first creating a logon for the Database Server then add user entries for each of the databases you want to give access to. Also specified are the role members for each database. Add User Using Windows Authentication [cc lang=”sql”] — Create user windows Authentication CREATE LOGIN [YourDomainNameJohnJacobs] FROM WINDOWS WITH DEFAULT_DATABASE = [YourDatabaseHere]; GO — Add User to first database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ — Add User to second database USE YourSecondDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ [/cc] Add User Using SQL Authentication [cc lang=”sql”] — Create user for SQL Authentication CREATE LOGIN JohnJacobs WITH PASSWORD = ‘JinGleHeimerSchmidt’ ,DEFAULT_DATABASE = [YourDatabaseHere] GO — Add User to first database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN JohnJacobs; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ GO — Add User to second database USE YourSecondDatabaseHere; CREATE USER JohnJacobs FOR LOGIN JohnJacobs; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ [/cc] To automate the creation use the script below. It will print out the statements so you can copy and paste them into SSMS. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @UserString VARCHAR(8000) DECLARE @DatabaseString VARCHAR(8000) DECLARE @DefaultDatabase VARCHAR(255) DECLARE @RolesString VARCHAR(8000) DECLARE @delimiter CHAR(1) SET @UserString = ‘[yourdomainyouruser],[yourdomainyouruser2]’ SET @DatabaseString = ‘[AdventureWorks2012],[ReportServer]’ SET @DefaultDatabase = ‘[AdventureWorks2012]’ SET @RolesString = ‘db_datareader,db_datawriter’ SET @delimiter = ‘,’ BEGIN TRY DROP TABLE #Users DROP TABLE #Databases […]

Add Constraint

Create Index Syntax

Add Primary Key

Add User Role

Add Users to Database Script

Featured Articles

Site Author

Resources