(Implementing the Base Table Structures).sql

SELECT   len(
            cast(replicate('a',8000) as varchar(8000))
            + cast(replicate('a',8000) as varchar(8000))
          )
GO
SELECT    len(
            cast(replicate('a',8000) as varchar(max))
            + cast(replicate('a',8000) as varchar(8000))
          )
GO
SELECT  table_name
FROM    AdventureWorks2008.information_schema.tables
WHERE   table_schema = 'Purchasing'
ORDER   BY table_name
GO

----------------------------------
-- Set up database
----------------------------------
CREATE DATABASE MovieRental
Go
USE MovieRental
GO
SELECT physical_name
FROM   sys.database_files
GO
SELECT  name, suser_sname(sid) as [login]
FROM    sys.database_principals
WHERE   name = 'dbo'
GO
ALTER AUTHORIZATION ON Database::MovieRental to SA
GO
----------------------------------
-- Schemas
----------------------------------

SELECT name,
       SCHEMA_NAME(schema_id) as schemaName,
       USER_NAME(principal_id) as principal
FROM   AdventureWorks2008.sys.schemas
ORDER BY Name
GO
SELECT  table_name
FROM    AdventureWorks2008.information_schema.tables
WHERE   table_schema = 'Purchasing'
ORDER BY table_name
GO

CREATE SCHEMA Inventory --tables pertaining to the videos to be rented
GO
CREATE SCHEMA People --tables pertaining to people (nonspecific)
GO
CREATE SCHEMA Rentals --tables pertaining to rentals to customers
GO
CREATE SCHEMA Alt
GO

----------------------------------
-- Columns and Base Datatypes;Nulls
----------------------------------

CREATE TABLE Alt.NullTest
(
   NullColumn varchar(10) NULL,
   NotNullColumn varchar(10) NOT NULL
)
GO
SELECT   name, is_ansi_null_default_on
FROM     sys.databases
WHERE    name = 'MovieRental'
GO
ALTER DATABASE MovieRental
      SET ANSI_NULL_DEFAULT OFF
GO
--turn off default NULLs
SET ANSI_NULL_DFLT_ON OFF

--create test table
CREATE TABLE Alt.testNULL
(
   id   int
)

--check the values
EXEC sp_help 'Alt.testNULL'
GO

CREATE TABLE Inventory.Movie
(
       MovieId              int NOT NULL,
       Name                 varchar(20) NOT NULL,
       ReleaseDate          date NULL,
       Description          varchar(200) NULL,
       GenreId              int NOT NULL,
       MovieRatingId        int NOT NULL
)
GO

------------------------------------------------------------------------------------------------------
-- Columns and Base Datatypes;Surrogate Keys;Manually Managed
------------------------------------------------------------------------------------------------------

CREATE TABLE Inventory.MovieRating (
       MovieRatingId        int NOT NULL,
       Code                 varchar(20) NOT NULL,
       Description          varchar(200) NULL,
       AllowYouthRentalFlag bit NOT NULL
)
GO
INSERT INTO Inventory.MovieRating
            (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES     (0, 'UR','Unrated',1),
        (1, 'G','General Audiences',1),
        (2, 'PG','Parental Guidance',1),
        (3, 'PG-13','Parental Guidance for Children Under 13',1),
        (4, 'R','Restricted, No Children Under 17 without Parent',0)
GO
CREATE TABLE Inventory.Genre (
       GenreId              int NOT NULL,
       Name                 varchar(20) NOT NULL
)
GO
INSERT INTO Inventory.Genre (GenreId, Name)
VALUES (1,'Comedy'),
       (2,'Drama'),
       (3,'Thriller'),
       (4,'Documentary')
GO

------------------------------------------------------------------------------------------------------
-- Columns and Base Datatypes;Surrogate Keys;Generation Using the IDENTITY Property
------------------------------------------------------------------------------------------------------

DROP TABLE Inventory.Movie
GO
CREATE TABLE Inventory.Movie
(
       MovieId              int NOT NULL IDENTITY(1,2),
       Name                 varchar(20) NOT NULL,
       ReleaseDate          date NULL,
       Description          varchar(200) NULL,
       GenreId              int NOT NULL,
       MovieRatingId        int NOT NULL
)
GO
--Genre and Ratings values create as literal values because
--they are built with explicit values
INSERT INTO Inventory.Movie (Name, ReleaseDate,
                             Description, GenreId, MovieRatingId)
VALUES ('The Maltese Falcon','19411003',
        'A private detective finds himself surrounded by strange people ' +
        'looking for a statue filled with jewels',2,0),
        
       ('Arsenic and Old Lace','19440923',
        'A man learns a disturbing secret about his aunt''s methods ' +
        'for treating gentleman callers',1,0)
GO
SELECT  MovieId, Name, ReleaseDate
FROM    Inventory.Movie
GO
INSERT INTO Inventory.Movie (Name, ReleaseDate,
                             Description, GenreId, MovieRatingId)
VALUES ('Arsenic and Old Lace','19440923',
        'A man learns a disturbing secret about his aunt''s methods ' +
        'for treating gentleman callers',1,0)
GO
SELECT  MovieId, Name, ReleaseDate
FROM    Inventory.Movie
GO
--add a numbering column to the set, partitioned by the duplicate names.
--order by the MovieId, to keep the lowest key (not that it really matters)
WITH numberedRows as (
SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY MovieId) AS RowNumber,
       MovieId
FROM   Inventory.Movie )
--only keep one row per unique name
DELETE FROM numberedRows
WHERE  RowNumber <> 1
GO
--------------------------------------------------------------------
-- Adding Uniqueness Constraints; Adding Primary Key Constraints
--------------------------------------------------------------------

CREATE TABLE Inventory.MovieFormat (
       MovieFormatId        int NOT NULL
          CONSTRAINT PKInventory_MovieFormat PRIMARY KEY CLUSTERED,
       Name                 varchar(20) NOT NULL
)
GO
INSERT INTO Inventory.MovieFormat(MovieFormatId, Name)
VALUES  (1,'Video Tape')
       ,(1,'DVD')
GO
INSERT INTO Inventory.MovieFormat(MovieFormatId, Name)
VALUES  (1,'Video Tape')
       ,(2,'DVD')
GO

CREATE TABLE Alt.Product
(
   Manufacturer varchar(30) NOT NULL,
   ModelNumber varchar(30) NOT NULL,
   CONSTRAINT PKAlt_Product PRIMARY KEY NONCLUSTERED (Manufacturer, ModelNumber)
)
DROP TABLE Alt.Product
GO

ALTER TABLE Inventory.MovieRating
   ADD CONSTRAINT PKInventory_MovieRating PRIMARY KEY CLUSTERED (MovieRatingId)

ALTER TABLE Inventory.Genre
   ADD CONSTRAINT PKInventory_Genre PRIMARY KEY CLUSTERED (GenreId)

ALTER TABLE Inventory.Movie
   ADD CONSTRAINT PKInventory_Movie PRIMARY KEY CLUSTERED (MovieId)
GO

CREATE TABLE Test (TestId int PRIMARY KEY)
GO
SELECT constraint_name
FROM   information_schema.table_constraints
WHERE  table_schema = 'dbo'
  and  table_name = 'test'
GO

--------------------------------------------------------------------
-- Adding Uniqueness Constraints; Adding Alternate Keys Constraints
--------------------------------------------------------------------

CREATE TABLE Inventory.Personality
(
       PersonalityId        int NOT NULL IDENTITY(1,1)
            CONSTRAINT PKInventory_Personality PRIMARY KEY,
       FirstName            varchar(20) NOT NULL,
       LastName             varchar(20) NOT NULL,
       NameUniqueifier      varchar(5) NOT NULL,
            CONSTRAINT AKInventory_Personality_PersonalityName
                UNIQUE NONCLUSTERED (FirstName, LastName, NameUniqueifier)
)

ALTER TABLE Inventory.Genre
  ADD CONSTRAINT AKInventory_Genre_Name UNIQUE NONCLUSTERED (Name)

ALTER TABLE Inventory.MovieRating
  ADD CONSTRAINT AKInventory_MovieRating_Code UNIQUE NONCLUSTERED (Code)

ALTER TABLE Inventory.Movie
  ADD CONSTRAINT AKInventory_Movie_NameAndDate
      UNIQUE NONCLUSTERED (Name, ReleaseDate)
GO

--------------------------------------------------------------------
-- Adding Uniqueness Constraints; Implementing Selective Uniqueness
--------------------------------------------------------------------
CREATE TABLE alt.employee
(
    EmployeeId int identity(1,1) constraint PKalt_employee primary key,
    EmployeeNumber char(5) not null
           CONSTRAINT AKalt_employee_employeeNummer UNIQUE,
    --skipping other columns you would likely have
    InsurancePolicyNumber char(10) null
)
go
--Filtered Alternate Key (AKF)
CREATE UNIQUE INDEX AKFalt_employee_InsurancePolicyNumber ON
                                    alt.employee(InsurancePolicyNumber)
WHERE InsurancePolicyNumber is not null
GO
INSERT INTO Alt.Employee (EmployeeNumber, InsurancePolicyNumber)
VALUES ('A0001','1111111111')
GO
INSERT INTO Alt.Employee (EmployeeNumber, InsurancePolicyNumber)
VALUES ('A0002','1111111111')
GO
INSERT INTO Alt.Employee (EmployeeNumber, InsurancePolicyNumber)
VALUES ('A0003','2222222222'),
       ('A0004',NULL),
       ('A0005',NULL)
GO
CREATE TABLE Alt.AccountContact
(
    ContactId   varchar(10) not null,
    AccountNumber   char(5) not null, --would be FK
    PrimaryContactFlag bit not null,
    CONSTRAINT PKalt_accountContact
        PRIMARY KEY(ContactId, AccountNumber)
)
GO
CREATE UNIQUE INDEX
    AKFAlt_AccountContact_PrimaryContact
            ON Alt.AccountContact(AccountNumber)
            WHERE PrimaryContactFlag = 1
GO
INSERT INTO Alt.AccountContact
SELECT 'bob','11111',1
go
INSERT INTO Alt.AccountContact
SELECT 'fred','11111',1
GO
BEGIN TRANSACTION
 
UPDATE Alt.AccountContact
SET primaryContactFlag = 0
WHERE  accountNumber = '11111'
 
INSERT Alt.AccountContact
SELECT 'fred','11111',1
 
COMMIT TRANSACTION
GO
CREATE VIEW Alt.Employee_InsurancePolicyNumberUniqueness
WITH SCHEMABINDING
AS
    SELECT  InsurancePolicyNumber
    FROM    Alt.Employee
    WHERE   InsurancePolicyNumber is not null
GO
CREATE UNIQUE CLUSTERED INDEX
    AKalt_Employee_InsurancePolicyNumberUniqueness
    ON alt.Employee_InsurancePolicyNumberUniqueness(InsurancePolicyNumber)
GO

--------------------------------------------------------------------
-- Adding Uniqueness Constraints; Viewing the Constraints
--------------------------------------------------------------------
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM   INFORMATION_SCHEMA.table_constraints
WHERE  CONSTRAINT_SCHEMA = 'Inventory'
ORDER  BY  CONSTRAINT_SCHEMA, TABLE_NAME
GO

--------------------------------------------------------------------
-- Building Default Constraints; Literals
--------------------------------------------------------------------

CREATE TABLE People.Person (
       PersonId             int NOT NULL IDENTITY(1,1)
          CONSTRAINT PKPerson PRIMARY KEY,
       FirstName            varchar(20) NOT NULL,
       MiddleName           varchar(20) NULL,
       LastName             varchar(20) NOT NULL,
       SocialSecurityNumber char(11) --will be redefined using CLR later
          CONSTRAINT AKPeople_Person_SSN UNIQUE
)

CREATE TABLE Rentals.Customer (
       CustomerId           int NOT NULL
          CONSTRAINT PKRentals_Customer PRIMARY KEY,
       CustomerNumber       char(10)
          CONSTRAINT AKRentals_Customer_CustomerNumber UNIQUE,
       PrimaryCustomerId    int NULL,
       Picture              varbinary(max) NULL,
       YouthRentalsOnlyFlag bit NOT NULL
          CONSTRAINT People_Person$can_be_a$Rentals_Customer
                FOREIGN KEY (CustomerId)
                             REFERENCES People.Person  (PersonId)
                             ON DELETE CASCADE  --cascade delete on SubType
                             ON UPDATE NO ACTION,
          CONSTRAINT   
              Rentals_Customer$can_rent_on_the_account_of$Rentals_Customer
                FOREIGN KEY (PrimaryCustomerId)
                             REFERENCES Rentals.Customer  (CustomerId)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
)
Go
ALTER TABLE Rentals.Customer
   ADD CONSTRAINT DfltRentals_Customer_YouthRentalsOnlyFlag DEFAULT (0)
        FOR YouthRentalsOnlyFlag
GO
INSERT INTO People.Person(FirstName, MiddleName, LastName, SocialSecurityNumber)
VALUES ('Doe','','Maign','111-11-1111')
--skipping several of the columns that are either nullable or have defaults
INSERT INTO Rentals.Customer(CustomerId, CustomerNumber)
SELECT Person.PersonId, '1111111111'
FROM   People.Person
WHERE  SocialSecurityNumber = '111-11-1111'
GO
SELECT CustomerNumber, YouthRentalsOnlyFlag
FROM   Rentals.Customer
GO


--Using the Alt schema for alternative examples
CREATE TABLE Alt.url
(
       scheme        varchar(10) NOT NULL, --http, ftp
       computerName  varchar(50) NOT NULL, --www, or whatever
                         --base domain name (microsoft, amazon, etc.)
       domainName varchar(50) NOT NULL,
       siteType varchar(5) NOT NULL, --net, com, org
       filePath varchar(255) NOT NULL,
       fileName varchar(20) NOT NULL,
       parameter varchar(255) NOT NULL,
              CONSTRAINT PKAlt_Url  PRIMARY KEY (scheme, computerName,
                                            domainName, siteType,
                                            filePath, fileName, parameter)
)
GO
INSERT INTO alt.url (scheme, computerName, domainName, siteType,
                            filePath, filename, parameter)
VALUES ('http','www','microsoft','com','','','')

--then display the data
SELECT   scheme + '://' + computerName +
                 case when len(rtrim(computerName)) > 0 then '.' else '' end +
                 domainName + '.'
         + siteType
         + case when len(filePath) > 0 then '/' else '' end + filePath
         + case when len(fileName) > 0 then '/' else '' end + fileName
         + parameter as display
FROM alt.url
GO

ALTER TABLE Alt.url
   ADD CONSTRAINT DFLTAlt_Url_scheme
   DEFAULT ('http') FOR scheme

ALTER TABLE alt.url
   ADD CONSTRAINT DFLTAlt_Url_computerName
   DEFAULT ('www') FOR computerName

ALTER TABLE alt.url
   ADD CONSTRAINT DFLTAlt_Url_siteType
   DEFAULT ('com') FOR siteType

ALTER TABLE alt.url
   ADD CONSTRAINT DFLTAlt_Url_filePath
   DEFAULT ('') FOR filePath

ALTER TABLE alt.url
   ADD CONSTRAINT DFLTAlt_Url_fileName
   DEFAULT ('') FOR fileName

ALTER TABLE alt.url
   ADD CONSTRAINT DFLTAlt_Url_parameter
   DEFAULT ('') FOR parameter
Go

INSERT INTO alt.url (domainName)
VALUES ('usatoday')
GO
--then display the data
SELECT   scheme + '://' + computerName +
                 case when len(rtrim(computerName)) > 0 then '.' else '' end +
                 domainName + '.'
         + siteType
         + case when len(filePath) > 0 then '/' else '' end + filePath
         + case when len(fileName) > 0 then '/' else '' end + fileName
         + parameter as display
FROM alt.url
GO

SELECT cast(column_name as varchaR(20)) as column_name, column_default
FROM   information_schema.columns
WHERE  table_schema = 'Alt'
  AND  table_name  = 'url'

--------------------------------------------------------------------
-- Building Default Constraints; Rich Expressions
--------------------------------------------------------------------
CREATE TABLE Rentals.MovieRental (
       MovieRentalId        int NOT NULL IDENTITY(1,1)
             CONSTRAINT PKRentals_MovieRental PRIMARY KEY,
       ReturnDate           date NOT NULL,
       ActualReturnDate     date NULL,
       MovieRentalInventoryItemId int NOT NULL,
       CustomerId           int NOT NULL,
       RentalTime           smalldatetime NOT NULL,
       RentedByEmployeeId   int NOT NULL,
       AmountPaid           decimal(4,2) NOT NULL,
       CONSTRAINT AKRentals_MovieRental_RentalItemCustomer UNIQUE
             (RentalTime, MovieRentalInventoryItemId, CustomerId)
)
GO
ALTER TABLE Rentals.MovieRental
    ADD CONSTRAINT DFLTMovieRental_RentalTime
           DEFAULT (GETDATE()) FOR RentalTime

ALTER TABLE Rentals.MovieRental
    ADD CONSTRAINT DFLTMovieRental_ReturnDate
           --Default to fourth days later
           DEFAULT (DATEADD(Day,4,GETDATE()))
                  FOR ReturnDate
GO
INSERT  Rentals.MovieRental (MovieRentalInventoryItemId, CustomerId,
        RentedByEmployeeId, AmountPaid)
VALUES (0,0,0,0.00)
GO
SELECT  RentalTime, ReturnDate
FROM    Rentals.MovieRental
GO


--------------------------------------------------------------------
-- Adding Relationships (Foreign Keys)
--------------------------------------------------------------------

ALTER TABLE Inventory.Movie
       ADD CONSTRAINT
           Inventory_MovieRating$defines_age_appropriateness_of$Inventory_Movie
              FOREIGN KEY (MovieRatingId)
                             REFERENCES Inventory.MovieRating  (MovieRatingId)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
ALTER TABLE Inventory.Movie
       ADD CONSTRAINT Inventory_Genre$categorizes$Inventory_Movie
                FOREIGN KEY (GenreId)
                             REFERENCES Inventory.Genre (GenreId)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
GO
INSERT INTO Inventory.Movie (Name, ReleaseDate,
                             Description, GenreId, MovieRatingId)
VALUES ('Stripes','19810626',
        'A loser joins the Army, though the Army is not really '+
        'ready for him',-1,-1)
GO
INSERT INTO Inventory.Movie (Name, ReleaseDate,
                             Description, GenreId, MovieRatingId)
SELECT 'Stripes','19810626',
        'A loser joins the Army, though the Army is not really '+
        'ready for him',
        (SELECT Genre.GenreId
         FROM   Inventory.Genre as Genre
         WHERE  Genre.Name = 'Comedy') as GenreId,
        (SELECT MovieRating.MovieRatingId
         FROM   Inventory.MovieRating as MovieRating
         WHERE  MovieRating.Code = 'R') as MovieRatingId
GO
DELETE FROM Inventory.Genre
WHERE  Name = 'Comedy'

----------------------------------------------------------------------------------------
-- Adding Relationships (Foreign Keys); Automated Relationship Options; Cascade
----------------------------------------------------------------------------------------
CREATE TABLE Inventory.MoviePersonality (
       MoviePersonalityId   int NOT NULL IDENTITY (1,1)
       CONSTRAINT PKInventory_MoviePersonality PRIMARY KEY,
       MovieId              int NOT NULL,
       PersonalityId        int NOT NULL,
       CONSTRAINT AKInventory_MoviePersonality_MoviePersonality
            UNIQUE (PersonalityId,MovieId)
)
GO
ALTER TABLE Inventory.MoviePersonality
       ADD CONSTRAINT
           Inventory_Personality$is_linked_to_movies_via$Inventory_MoviePersonality
                FOREIGN KEY (MovieId)
                             REFERENCES Inventory.Movie  (MovieId)
                             ON DELETE CASCADE
                             ON UPDATE NO ACTION

ALTER TABLE Inventory.MoviePersonality
       ADD CONSTRAINT
        Inventory_Movie$is_linked_to_important_people_via$Inventory_MoviePersonality
                FOREIGN KEY (PersonalityId)
                             REFERENCES Inventory.Personality  (PersonalityId)
                             ON DELETE CASCADE
                             ON UPDATE NO ACTION
GO

INSERT INTO Inventory.Personality (FirstName, LastName, NameUniqueifier)
VALUES ('Cary','Grant',''),
       ('Humphrey','Bogart','')
GO
INSERT INTO Inventory.MoviePersonality (MovieId, PersonalityId)
SELECT  (SELECT  Movie.MovieId
         FROM    Inventory.Movie as Movie
         WHERE   Movie.Name = 'The Maltese Falcon') as MovieId,
        (SELECT  Personality.PersonalityId
         FROM    Inventory.Personality as Personality  
         WHERE   Personality.FirstName = 'Humphrey'
           AND   Personality.LastName = 'Bogart'
           AND   Personality.NameUniqueifier = '')
                                              as PersonalityId
UNION ALL
SELECT  (SELECT  Movie.MovieId
         FROM    Inventory.Movie as Movie
         WHERE   Movie.Name = 'Arsenic and Old Lace') as MovieId,
        (SELECT  Personality.PersonalityId
         FROM    Inventory.Personality as Personality  
         WHERE   Personality.FirstName = 'Cary'
           AND   Personality.LastName = 'Grant'
           AND   Personality.NameUniqueifier = '')
                                               as PersonalityId
GO
SELECT Movie.Name as Movie,
       Personality.FirstName + ' '+ Personality.LastName as Personality
FROM   Inventory.MoviePersonality as MoviePersonality
         JOIN Inventory.Personality as Personality
              On MoviePersonality.PersonalityId = Personality.PersonalityId
         JOIN Inventory.Movie as Movie
              ON Movie.MovieId = MoviePersonality.MovieId
GO
DELETE FROM Inventory.Movie
WHERE  Name = 'Arsenic and Old Lace'
GO
SELECT Movie.Name as Movie,
       Personality.FirstName + ' '+ Personality.LastName as Personality
FROM   Inventory.MoviePersonality as MoviePersonality
         JOIN Inventory.Personality as Personality
              On MoviePersonality.PersonalityId = Personality.PersonalityId
         JOIN Inventory.Movie as Movie
              ON Movie.MovieId = MoviePersonality.MovieId
GO



CREATE TABLE Alt.Movie
(
    MovieCode   varchar(20)
        CONSTRAINT PKAlt_Movie PRIMARY KEY,
    MovieName   varchar(200)
)
CREATE TABLE Alt.MovieRentalPackage
(
    MovieRentalPackageCode varchar(25)
        CONSTRAINT PKAlt_MovieRentalPackage PRIMARY KEY,
    MovieCode   varchar(20)
        CONSTRAINT Alt_Movie$is_rented_as$Alt_MovieRentalPackage
                FOREIGN KEY References Alt.Movie(MovieCode)
                ON DELETE CASCADE
                ON UPDATE CASCADE
)

INSERT INTO Alt.Movie (MovieCode, MovieName)
VALUES ('ArseOldLace','Arsenic and Old Lace')
INSERT INTO Alt.MovieRentalPackage (MovieRentalPackageCode, MovieCode)
VALUES ('ArsenicOldLaceDVD','ArseOldLace')
GO

UPDATE Alt.Movie
SET    MovieCode = 'ArsenicOldLace'
WHERE  MovieCode = 'ArseOldLace'
GO

SELECT *
FROM   Alt.Movie
SELECT *
FROM   Alt.MovieRentalPackage
GO

----------------------------------------------------------------------------------------
-- Adding Relationships (Foreign Keys); Automated Relationship Options; Set Null
----------------------------------------------------------------------------------------

ALTER TABLE Rentals.Customer
    ADD FavoriteMovieId INT NULL --allow nulls or SET NULL will be invalid

--Next define the foreign key constraint with SET NULL:
ALTER TABLE Rentals.Customer
    ADD CONSTRAINT Inventory_Movie$DefinesFavoriteFor$Rentals_Customer
         FOREIGN KEY (FavoriteMovieId)
                     REFERENCES Inventory.Movie  (MovieId)
                     ON DELETE SET NULL
                     ON UPDATE NO ACTION
GO

INSERT INTO People.Person(FirstName, MiddleName, LastName, SocialSecurityNumber)
VALUES ('Doe','M','Aigne','222-22-2222')

INSERT INTO Rentals.Customer(CustomerId, CustomerNumber,
                            PrimaryCustomerId, Picture, YouthRentalsOnlyFlag,
                            FavoriteMovieId)
SELECT Person.PersonId, '2222222222',NULL, NULL, 0, NULL
FROM   People.Person
WHERE  SocialSecurityNumber = '222-22-2222'

GO

SELECT MovieId, ReleaseDate
FROM   Inventory.Movie
WHERE   Name  = 'Stripes'
GO

UPDATE  Rentals.Customer
SET     FavoriteMovieId = 7
WHERE   CustomerNumber = '2222222222'
GO
UPDATE  Rentals.Customer
SET     FavoriteMovieId =  (SELECT MovieId
                            FROM   Inventory.Movie
                            WHERE   Name  = 'Stripes')
WHERE   CustomerNumber = '2222222222'

GO

SELECT  Customer.CustomerNumber, Movie.Name AS FavoriteMovie
FROM    Rentals.Customer AS Customer
          LEFT OUTER JOIN Inventory.Movie AS Movie
            ON Movie.MovieId = Customer.FavoriteMovieId
WHERE   Customer.CustomerNumber = '2222222222'
GO
DELETE  Inventory.Movie
WHERE   Name = 'Stripes'
  AND   ReleaseDate = '19810626'
GO
SELECT  Customer.CustomerNumber, Movie.Name AS FavoriteMovie
FROM    Rentals.Customer AS Customer
          LEFT OUTER JOIN Inventory.Movie AS Movie
            ON Movie.MovieId = Customer.FavoriteMovieId
WHERE   Customer.CustomerNumber = '2222222222'
GO

----------------------------------------------------------------------------------------
-- Adding Relationships (Foreign Keys); Automated Relationship Options; Set Default
----------------------------------------------------------------------------------------
INSERT INTO Inventory.MovieFormat(MovieFormatId, Name)
VALUES (3, 'Playstation Portable')
GO
ALTER TABLE Rentals.Customer
   ADD DefaultMovieFormatId INT NOT NULL
          CONSTRAINT DFLTRentals_Customer_DefaultMovieFormatId
               DEFAULT (2) --DVD (Can hard code because surrogate key
                           --hand created, just make sure to document
                           --usage)
GO
ALTER TABLE Rentals.Customer
    ADD CONSTRAINT
       Inventory_MovieFormat$DefinesDefaultFormatFor$Rentals_Customer
         FOREIGN KEY (DefaultMovieFormatId)
            REFERENCES Inventory.MovieFormat  (MovieFormatId)
               ON DELETE SET DEFAULT
               ON UPDATE NO ACTION
GO
UPDATE Rentals.Customer
SET    DefaultMovieFormatId = 3
WHERE  CustomerNumber = '2222222222'
GO
SELECT  MovieFormat.Name
FROM    Inventory.MovieFormat as MovieFormat
           JOIN Rentals.Customer
               ON MovieFormat.MovieFormatId = Customer.DefaultMovieFormatId
WHERE   Customer.CustomerNumber = '2222222222'
GO
DELETE FROM Inventory.MovieFormat
WHERE  Name = 'Playstation Portable'
GO
SELECT  MovieFormat.Name
FROM    Inventory.MovieFormat as MovieFormat
           JOIN Rentals.Customer
               ON MovieFormat.MovieFormatId = Customer.DefaultMovieFormatId
WHERE   Customer.CustomerNumber = '2222222222'
GO
-----------------------------------------------------------------------------
-- Dealing with Collations and Sorting;Viewing the Current Collation Type
-----------------------------------------------------------------------------
SELECT serverproperty('collation')
SELECT databasepropertyex('MovieRental','collation')
GO
-----------------------------------------------------------------------------
-- Dealing with Collations and Sorting;Listing Available Collations
-----------------------------------------------------------------------------
SELECT *
FROM ::fn_helpcollations()
GO
-----------------------------------------------------------------------------
-- Dealing with Collations and Sorting;Specifying a Collation Sequence
-----------------------------------------------------------------------------
CREATE TABLE alt.OtherCollate
(
   OtherCollateId integer IDENTITY
        CONSTRAINT PKAlt_OtherCollate PRIMARY KEY ,
   Name nvarchar(30) NOT NULL,
   FrenchName nvarchar(30) COLLATE French_CI_AS_WS NULL,
   SpanishName nvarchar(30) COLLATE Modern_Spanish_CI_AS_WS NULL
)
GO
-----------------------------------------------------------------------------
-- Dealing with Collations and Sorting;Overriding an Assigned Collation
-----------------------------------------------------------------------------
CREATE TABLE alt.collateTest
(
    name    VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

insert into alt.collateTest(name)
values ('BOB')
insert into alt.collateTest(name)
values ('bob')
GO
SELECT name
FROM alt.collateTest
WHERE name = 'BOB'
GO
SELECT name
FROM   alt.collateTest
WHERE  name = 'BOB' COLLATE Latin1_General_BIN
GO
SELECT name
FROM   alt.collateTest
WHERE name COLLATE Latin1_General_BIN = 'BOB' COLLATE Latin1_General_BIN
GO
-----------------------------------------------------------------------------
-- Dealing with Collations and Sorting; Searching and Sorting
-----------------------------------------------------------------------------

create table alt.TestSorting
(
     value nvarchar(1) collate Latin1_General_CI_AI --case and accent
                                                    --insensitive
)
insert into alt.TestSorting
values ('A'),('a'),(nchar(256)) /*?*/,('b'),('B')
GO
select value
from alt.TestSorting
where value like '[A-Z]%'
GO
select value
from alt.TestSorting
where value collate Latin1_General_CS_AI   
           like '[A-Z]%' collate Latin1_General_CS_AI  --case sensitive and
                                                        --accent insensitive
GO
SELECT value
FROM   alt.TestSorting
ORDER  BY value collate Latin1_General_CS_AI
GO
SELECT value
FROM   alt.TestSorting
WHERE  value collate Latin1_General_CS_AI
        --Doing case sensitive search by looking for a value that has any of
        --the capital letters in it.
        like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' collate Latin1_General_CS_AI
GO
;with
digits (i) as(--set up a set of numbers from 0-9
        select i
        from   (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as digits (i))
--builds a table from 0 to 99999
,sequence (i) as (
        SELECT D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i)
        FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3
                CROSS JOIN digits AS D4 CROSS JOIN digits AS D5)
select i, nchar(i) as character
from sequence
where i between 48 and 122 --vary to include any characters
                           --in the character set of choice
order by nchar(i) collate Latin1_General_bin --change to the collation you
                                             --are trying
GO
-----------------------------------------------------------------------------
-- Computed Columns
-----------------------------------------------------------------------------

ALTER TABLE Inventory.Personality
   ADD FullName as
          FirstName + ' ' + LastName + RTRIM(' ' + NameUniqueifier) PERSISTED
GO
INSERT INTO Inventory.Personality (FirstName, LastName, NameUniqueifier)
VALUES ('John','Smith','I'),
       ('John','Smith','II')
GO
SELECT *
FROM Inventory.Personality
GO



CREATE TABLE alt.calcColumns
(
   dateColumn   datetime2(7),
   dateSecond   AS datepart(second,dateColumn) PERSISTED -- calculated column
)
SET NOCOUNT ON
DECLARE @i int
SET @i = 1
WHILE (@i < 200)
BEGIN
   INSERT INTO alt.calcColumns (dateColumn) VALUES (sysdatetime())
   WAITFOR DELAY '00:00:00.01' --or the query runs too fast
                               --and you get duplicates
   SET @i = @i + 1
END

SELECT dateSecond, max(dateColumn) as dateColumn, count(*) AS countStar
FROM alt.calcColumns
GROUP BY dateSecond
ORDER BY dateSecond
GO

CREATE TABLE alt.testCalc
(
    value varchar(10),
    valueCalc AS UPPER(value),
    value2 varchar(10)
)
GO
INSERT INTO alt.testCalc
VALUES ('test','test2')
Go
SELECT *
FROM  alt.testCalc
GO

SELECT   scheme + '://' + computerName +
                 case when len(rtrim(computerName)) > 0 then '.' else '' end +
                 domainName + '.'
         + siteType
         + case when len(filePath) > 0 then '/' else '' end + filePath
         + case when len(fileName) > 0 then '/' else '' end + fileName
         + parameter as display
FROM alt.url
GO
ALTER TABLE alt.url
   ADD formattedUrl AS
          scheme + '://' + computerName +
                 case when len(rtrim(computerName)) > 0 then '.' else '' end +
                 domainName + '.'
         + siteType
         + case when len(filePath) > 0 then '/' else '' end + filePath
         + case when len(fileName) > 0 then '/' else '' end + fileName
         + parameter PERSISTED
GO
SELECT formattedUrl
FROM   Alt.url
GO

SELECT cast(name as varchar(20)) as name, is_computed
FROM   sys.columns
WHERE  object_id('alt.testCalc') = object_id
GO
SELECT cast(name as varchar(20)) as name, is_persisted, definition
FROM   sys.computed_columns
WHERE  object_id('alt.testCalc') = object_id
GO

-----------------------------------------------------------------------------
-- Implementing User-Defined Datatypes; Datatype Aliases
-----------------------------------------------------------------------------
CREATE TYPE SSN
         FROM char(11)
              NOT NULL
GO
CREATE TABLE alt.Person
(
      PersonId      int NOT NULL,
      FirstName      varchar(30) NOT NULL,
      LastName       varchar(30) NOT NULL,
      SSN            SSN              --no null specification to make a point
                                      --generally it is a better idea to
                                      --include a null spec.
)
GO
INSERT Alt.Person
VALUES (1,'rie',' lationship','234-43-3432')

SELECT PersonId, FirstName, LastName, SSN
FROM    Alt.Person
GO
INSERT  Alt.Person
VALUES  (2,'dee','pendency',NULL)
GO


-----------------------------------------------------------------------------
-- Implementing User-Defined Datatypes; CLR-Based Datatypes
-----------------------------------------------------------------------------

EXEC sp_configure 'clr enabled', 1
go
RECONFIGURE
Go

CREATE ASSEMBLY [Apress]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001A6F50480000000000000000E00002210B010800000E0000000C0000000000001E2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000020040850000100000100000000010000010000000000000100000000000000000000000C42B000057000000004000005008000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240C000000200000000E000000020000000000000000000000000000200000602E727372630000005008000000400000000A000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001A00000000000000000000000000004000004200000000000000000000000000000000002C0000000000004800000002000500C8210000FC09000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020014000000010000110228020000060B12017201000070280200000A2A133001000700000002000011027B020000042A002202037D020000042A000000133001000700000003000011027B010000042A001330020012000000040000111200FE15020000021200177D01000004062A00001330020051000000050000110F00280300000A2C0628050000062A0F00FE16030000016F0400000A28070000062C231201FE150200000212010F00FE16030000016F0400000A28080000062803000006072A7219000070730500000A7A000000133003000D0000000600001102723D00007016280600000A2A0000001B3003006A0000000700001102280700000A0BDE5F25280800000A0C72CE0000700D16026F0900000A17DA130513042B2F72D00000700211046F0A00000A6F0B00000A163214090211046F0A00000A280C00000A280D00000A0D110417D613041104110531CB09280700000A0B280E00000ADE00072A0000010C0000000000000909005F0900000142534A4201000100000000000C00000076322E302E35303732370000000005006C0000004C030000237E0000B8030000D803000023537472696E67730000000090070000E80000002355530078080000100000002347554944000000880800007401000023426C6F6200000000000000020000015717A2030900000000FA013300160000010000001B00000002000000020000000800000004000000010000001B0000000C000000070000000100000003000000040000000100000001000000040000000000CB03010000000000060052004B000E007D0068000E00C7006800060008014B0006000E014B00060015014B0012004C012D01120052012D01060067014B00060071014B000A00A80181010600C4014B000A00E80181010E0028020D020E0044020D0206004B024B000600810261020600A10261020600DE02BF020600EC02BF02060000034B0006002803160306004303160306005E0316030600770316030600900316030600AD0316030000000001000000000001000100092100002900300005000100010001008700130001008E001600502000000000460294001900010070200000000001089D001D0001008420000000000108A50021000100902000000000660BB30026000200A420000000001608BE002A000200C420000000001600D1002F0002002421000000001100D900360003004021000000001100E8003B00040000000100AD0000000100D70000000100E40000000100E400020009001100B3002600210094004D001900B300260029009400190031002701660039005F016B00510079013B005900B40173006100CB011D006100D60179006100E0017E006900940083006100F40188005900FB018E00710027019C0081002701DB0089002701210091002701DB00990027016600A1002701DF00A9002701DF00B10027016600B90027016600C10027016600C90027016600D10027016600D900270166002E00AB003C012E008B00E4002E009300ED002E009B000C012E00A30036012E00C3005A012E00DB005A012E00BB0042012E00B30036012E00CB0036012E00D300360143007B00A200520057005B005F005F005B009200020001000000F80040000000FC00440000000301480002000200030001000300030002000400050002000500070002000800030004800000010000000C0C0082000000000000C403000002000000000000000000000001000A00000000000800000000000000000000000A0013000000000002000000000000000000000001005C000000000002000000000000000000000001004B00000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C42617369630053736E5564740050726F53716C536572766572446174616261736544657369676E0053797374656D0056616C7565547970650053797374656D2E446174610053797374656D2E446174612E53716C547970657300494E756C6C61626C65006D5F4E756C6C006D5F73736E00546F537472696E67006765745F53736E007365745F53736E0076616C7565006765745F49734E756C6C006765745F4E756C6C0053716C537472696E67005061727365007300497353736E56616C69640073736E00436F6E7665727453534E546F496E740053736E0049734E756C6C004E756C6C00496E743332004F626A65637400417267756D656E74457863657074696F6E002E63746F720053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780052656765784F7074696F6E730049734D6174636800457863657074696F6E00436F6E7665727400546F496E743332004D6963726F736F66742E56697375616C42617369632E436F6D70696C657253657276696365730050726F6A656374446174610053657450726F6A6563744572726F7200537472696E67006765745F4C656E677468006765745F436861727300496E6465784F6600436F6E76657273696F6E7300436F6E63617400436C65617250726F6A6563744572726F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C55736572446566696E65645479706541747472696275746500466F726D61740053657269616C697A61626C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C6541747472696275746500417072657373004170726573732E646C6C00000000173000300030002D00300030002D0030003000300030000123530053004E0020006900730020006E006F0074002000760061006C00690064002E0000808F5E0028003F002100300030003000290028005B0030002D0036005D005C0064007B0032007D007C00370028005B0030002D0036005D005C0064007C0037005B003000310032005D002900290028005B0020002D005D003F00290028003F0021003000300029005C0064005C0064005C00330028003F002100300030003000300029005C0064007B0034007D0024000101001530003100320033003400350036003700380039000000006140AC94750EF14CABD9669F24C7EF6E0008B77A5C561934E08908B03F5F7F11D50A3A0206020206080320000E0320000804200101080320000204000011080600011108110D040001020E040001080E032800080328000204080011080420010E0E0407020E08030701080307010206070211081108042001010E070003020E0E1121050001011225042001030804200108030400010E030500020E0E0E03000001090706080812250E080805200101113D38010001000000030054020D4973427974654F7264657265640154020D497346697865644C656E67746801540E044E616D6506434C5253534E0320000104200101020801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002432666561663338362D353661382D343463622D386361372D653039383162643139383737000005010000000005010001000017010012436F7079726967687420C2A9202032303038000018010013436861707465722035202D2053534E20554454000000EC2B000000000000000000000E2C0000002000000000000000000000000000000000000000000000002C00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000018440000E80200000000000000000000004700002801000000000000000000002848000022000000000000000000000018410000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010000820C0C0000010000820C0C3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C0200000100300030003000300030003400620030000000500014000100460069006C0065004400650073006300720069007000740069006F006E000000000043006800610070007400650072002000350020002D002000530053004E002000550044005400000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003000380034002E00330033003200380030000000000038000B00010049006E007400650072006E0061006C004E0061006D00650000004100700072006500730073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300030003800000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004100700072006500730073002E0064006C006C0000000000480014000100500072006F0064007500630074004E0061006D0065000000000043006800610070007400650072002000350020002D002000530053004E002000550044005400000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003000380034002E00330033003200380030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003000380034002E003300330032003800300000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E802000002001010100001000400280100000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [Apress]
ADD FILE FROM 0xEFBBBF496D706F7274732053797374656D0D0A496D706F7274732053797374656D2E5265666C656374696F6E0D0A496D706F7274732053797374656D2E52756E74696D652E496E7465726F7053657276696365730D0A496D706F7274732053797374656D2E446174612E53716C0D0A0D0A272047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2720736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A27206173736F636961746564207769746820616E20617373656D626C792E0D0A0D0A2720526576696577207468652076616C756573206F662074686520617373656D626C7920617474726962757465730D0A0D0A3C417373656D626C793A20417373656D626C795469746C652822436861707465722035202D2053534E2055445422293E0D0A3C417373656D626C793A20417373656D626C794465736372697074696F6E282222293E0D0A3C417373656D626C793A20417373656D626C79436F6D70616E79282222293E0D0A3C417373656D626C793A20417373656D626C7950726F647563742822436861707465722035202D2053534E2055445422293E0D0A3C417373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A920203230303822293E0D0A3C417373656D626C793A20417373656D626C7954726164656D61726B282222293E0D0A3C417373656D626C793A20434C53436F6D706C69616E742854727565293E0D0A3C417373656D626C793A20436F6D56697369626C652846616C7365293E0D0A0D0A2754686520666F6C6C6F77696E67204755494420697320666F7220746865204944206F662074686520747970656C696220696620746869732070726F6A656374206973206578706F73656420746F20434F4D0D0A3C417373656D626C793A2047756964282232666561663338362D353661382D343463622D386361372D65303938316264313938373722293E0D0A0D0A272056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A270D0A272020202020204D616A6F722056657273696F6E0D0A272020202020204D696E6F722056657273696F6E0D0A272020202020204275696C64204E756D6265720D0A272020202020205265766973696F6E0D0A270D0A2720596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865204275696C6420616E64205265766973696F6E204E756D62657273200D0A27206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A0D0A3C417373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22293E200D0A
AS N'My Project\AssemblyInfo.vb'
GO

ALTER ASSEMBLY [Apress]
ADD FILE FROM 0xEFBBBF4F7074696F6E20537472696374204F6E0D0A0D0A496D706F7274732053797374656D0D0A496D706F7274732053797374656D2E446174610D0A496D706F7274732053797374656D2E446174612E53716C0D0A496D706F7274732053797374656D2E446174612E53716C54797065730D0A496D706F727473204D6963726F736F66742E53716C5365727665722E5365727665720D0A496D706F7274732053797374656D2E546578742E526567756C617245787072657373696F6E730D0A0D0A272D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A2720507572706F73653A20536F6369616C205365637572697479206E756D62657220757365722D646566696E65642074797065200D0A27205772697474656E3A2031322F31372F323030350D0A2720436F6D6D656E743A0D0A270D0A272053716C55736572446566696E6564547970652061747472696275746520636F6E7461696E73206461746120757365642062792053514C205365727665722032303035200D0A272061742072756E74696D6520616E64206279207468652050726F66657373696F6E616C2076657273696F6E206F662056697375616C2053747564696F200D0A2720616E642061626F7665206174206465706C6F796D656E742074696D652E205544542773206D7573742062652073657269616C697A61626C6520616E6420696D706C656D656E74200D0A2720494E756C6C61626C652E0D0A270D0A2720466F726D61742E4E6174697665202D20696E646963617465732053514C207365727665722063616E2053657269616C697A6520746865207479706520666F722075730D0A27204E616D65202D204E616D65206F6620554454207768656E206372656174656420696E2053514C20536572766572202875736564206279205653206174206465706C6F796D656E74290D0A27204973427974654F726465726564202D20696E6469636174657320696620747970652063616E206265206F7264657265642028757365642062792053514C20536572766572206174200D0A272072756E74696D65290D0A2720497346697865644C656E677468202D20696E64696361746573206966206C656E677468206F6620747970652069732066697865642028757365642062792053514C20536572766572200D0A272061742072756E74696D65290D0A272D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A3C53657269616C697A61626C6528293E205F0D0A3C4D6963726F736F66742E53716C5365727665722E5365727665722E53716C55736572446566696E65645479706528466F726D61742E4E61746976652C205F0D0A2020202020204973427974654F7264657265643A3D547275652C20497346697865644C656E6774683A3D547275652C204E616D653A3D22434C5253534E22293E205F0D0A5075626C6963205374727563747572652053736E5564740D0A20202020496D706C656D656E747320494E756C6C61626C650D0A0D0A20202020272050726976617465206D656D6265720D0A2020202050726976617465206D5F4E756C6C20417320426F6F6C65616E0D0A2020202050726976617465206D5F73736E20417320496E74656765720D0A0D0A202020205075626C6963204F76657272696465732046756E6374696F6E20546F537472696E67282920417320537472696E670D0A20202020202020202720666F726D61742053534E20666F72206F75747075740D0A202020202020202052657475726E204D652E53736E2E546F537472696E6728223030302D30302D3030303022290D0A20202020456E642046756E6374696F6E0D0A0D0A202020202720707269766174652070726F706572747920746F207365742F6765742073736E2073746F72656420617320696E746765720D0A20202020507269766174652050726F70657274792053736E282920417320496E74656765720D0A20202020202020204765740D0A20202020202020202020202052657475726E206D5F73736E0D0A2020202020202020456E64204765740D0A202020202020202053657428427956616C2076616C756520417320496E7465676572290D0A2020202020202020202020206D5F73736E203D2076616C75650D0A2020202020202020456E64205365740D0A20202020456E642050726F70657274790D0A0D0A202020205075626C696320526561644F6E6C792050726F70657274792049734E756C6C282920417320426F6F6C65616E20496D706C656D656E747320494E756C6C61626C652E49734E756C6C0D0A20202020202020204765740D0A20202020202020202020202052657475726E206D5F4E756C6C0D0A2020202020202020456E64204765740D0A20202020456E642050726F70657274790D0A0D0A20202020272072657475726E206F7572205544542061732061206E756C6C2076616C75650D0A202020205075626C69632053686172656420526561644F6E6C792050726F7065727479204E756C6C28292041732053736E5544540D0A20202020202020204765740D0A20202020202020202020202044696D20682041732053736E554454203D204E65772053736E5544540D0A202020202020202020202020682E6D5F4E756C6C203D20547275650D0A20202020202020202020202052657475726E20680D0A2020202020202020456E64204765740D0A20202020456E642050726F70657274790D0A0D0A20202020272067657420646174612066726F6D2053514C2053657276657220617320737472696E6720616E6420706172736520746F2072657475726E206F7572205544540D0A202020205075626C6963205368617265642046756E6374696F6E20506172736528427956616C20732041732053716C537472696E67292041732053736E5544540D0A2020202020202020496620732E49734E756C6C205468656E0D0A20202020202020202020202052657475726E204E756C6C0D0A2020202020202020456E642049660D0A0D0A2020202020202020272076616C69646174652076616C7565206265696E672070617373656420696E20617320612076616C69642053534E0D0A2020202020202020496620497353736E56616C696428732E546F537472696E67282929205468656E0D0A20202020202020202020202044696D20752041732053736E554454203D204E65772053736E5544540D0A202020202020202020202020752E53736E203D20436F6E7665727453534E546F496E7428732E546F537472696E672829290D0A20202020202020202020202052657475726E20750D0A2020202020202020456C73650D0A2020202020202020202020205468726F77204E657720417267756D656E74457863657074696F6E282253534E206973206E6F742076616C69642E22290D0A2020202020202020456E642049660D0A20202020456E642046756E6374696F6E0D0A0D0A20202020272076616C69646174652073736E207573696E67207265676578206D61746368696E67202D2072657475726E7320747275652069662076616C69642C2066616C7365206966206E6F740D0A2020202050726976617465205368617265642046756E6374696F6E20497353736E56616C696428427956616C2073736E20417320537472696E672920417320426F6F6C65616E0D0A202020202020202052657475726E2052656765782E49734D617463682873736E2C205F0D0A20202020202020202020202020202020225E283F2130303029285B302D365D5C647B327D7C37285B302D365D5C647C375B3031325D2929285B202D5D3F29283F213030295C645C645C33283F2130303030295C647B347D24222C205F0D0A2020202020202020202020202020202052656765784F7074696F6E732E4E6F6E65290D0A20202020456E642046756E6374696F6E0D0A0D0A202020202720707269766174652066756E6374696F6E20746F20636F6E766572742053534E206173206120737472696E6720746F20616E20696E74656765720D0A2020202050726976617465205368617265642046756E6374696F6E20436F6E7665727453534E546F496E7428427956616C2073736E20417320537472696E672920417320496E74656765720D0A202020202020202044696D2073736E4E756D6265727320417320496E74656765720D0A20202020202020205472790D0A202020202020202020202020272074727920612073696D706C6520636F6E76657273696F6E0D0A20202020202020202020202073736E4E756D62657273203D20436F6E766572742E546F496E7433322873736E290D0A2020202020202020436174636820657820417320457863657074696F6E0D0A202020202020202020202020272069662073696D706C6520636F6E76657273696F6E206661696C732C207374726970206F75742065766572797468696E670D0A2020202020202020202020202720627574206E756D6265727320616E6420636F6E7665727420746F20696E74656765720D0A20202020202020202020202044696D2073736E537472696E6720417320537472696E67203D2022220D0A202020202020202020202020466F72206920417320496E7465676572203D203020546F2073736E2E4C656E677468202D20310D0A202020202020202020202020202020204966202230313233343536373839222E496E6465784F662873736E2E436861727328692929203E3D2030205468656E0D0A202020202020202020202020202020202020202073736E537472696E67202B3D2073736E2E43686172732869290D0A20202020202020202020202020202020456E642049660D0A2020202020202020202020204E6578740D0A20202020202020202020202073736E4E756D62657273203D20436F6E766572742E546F496E7433322873736E537472696E67290D0A2020202020202020456E64205472790D0A202020202020202052657475726E2073736E4E756D626572730D0A20202020456E642046756E6374696F6E0D0A0D0A456E64205374727563747572650D0A
AS N'Type1.vb'
GO


CREATE TYPE [dbo].[CLRSSN]
EXTERNAL NAME [Apress].[ProSqlServerDatabaseDesign.SsnUdt]

GO

ALTER TABLE People.Person
   ADD SocialSecurityNumberCLR CLRSSN NULL
GO
UPDATE People.Person
   SET SocialSecurityNumberCLR = SocialSecurityNumber
GO

ALTER TABLE People.Person
   DROP CONSTRAINT AKPeople_Person_SSN
ALTER TABLE People.Person
   DROP COLUMN SocialSecurityNumber
EXEC sp_rename 'People.Person.SocialSecurityNumberCLR',
   'SocialSecurityNumber', 'COLUMN';
GO

ALTER TABLE People.Person
   ALTER COLUMN SocialSecurityNumber CLRSSN NOT NULL
ALTER TABLE People.Person
   ADD CONSTRAINT AKPeople_Person_SSN UNIQUE (SocialSecurityNumber)
GO
SELECT SocialSecurityNumber, socialSecurityNumber.ToString() as CastedVersion
FROM  People.Person

GO

-----------------------------------------------------------------------------
-- Documenting Your Database
-----------------------------------------------------------------------------

--dbo.person table description
EXEC sp_addextendedproperty @name = 'Description',
   @value = 'tables pertaining to the videos to be rented',
   @level0type = 'Schema', @level0name = 'Inventory'

--dbo.person table description
EXEC sp_addextendedproperty @name = 'Description',
   @value = 'Defines movies that will be rentable in the store',
   @level0type = 'Schema', @level0name = 'Inventory',
   @level1type = 'Table', @level1name = 'Movie'

--dbo.person.personId description
EXEC sp_addextendedproperty @name = 'Description',
   @value = 'Surrogate key of a movie instance',
   @level0type = 'Schema', @level0name = 'Inventory',
   @level1type = 'Table', @level1name = 'Movie',
   @level2type = 'Column', @level2name = 'MovieId'

--dbo.person.firstName description
EXEC sp_addextendedproperty @name = 'Description',
   @value = 'The known name of the movie',
   @level0type = 'Schema', @level0name = 'Inventory',
   @level1type = 'Table', @level1name = 'Movie',
   @level2type = 'Column', @level2name = 'Name'

--dbo.person.lastName description
EXEC sp_addextendedproperty @name = 'Description',
   @value = 'The date the movie was originally released',
   @level0type = 'Schema', @level0name = 'Inventory',
   @level1type = 'Table', @level1name = 'Movie',
   @level2type = 'Column', @level2name = 'ReleaseDate'
GO

SELECT objname, value
FROM   fn_listExtendedProperty ('Description',
                                 'Schema','Inventory',
                                 'Table','Movie',
                                 'Column',null)
GO
-----------------------------------------------------------------------------
-- Working with Dependency Information
-----------------------------------------------------------------------------
SELECT  objects.type_desc as object_type,
        OBJECT_SCHEMA_NAME(referencing_id) + '.' +
                     OBJECT_NAME(referencing_id) AS object_name,
        COL_NAME(referencing_id, referencing_minor_id) as
                    column_name,
        CASE WHEN referenced_id IS NOT NULL THEN 'Does Exist'
                   ELSE 'May Not Exist' END
             AS referencedObjectExistance,
        referenced_class_desc,
        coalesce(referenced_server_name,'<>') + '.'
            + coalesce(referenced_database_name,'<>') + '.'
            + coalesce(referenced_schema_name,'<>') + '.'
            + coalesce(referenced_entity_name,'<>') as
                                            referenced_object_name,
        COL_NAME(referenced_id, referenced_minor_id) as
                                referenced_column_name,
        is_caller_dependent,
        is_ambiguous
FROM  sys.sql_expression_dependencies
       join sys.objects
           on objects.object_id = sql_expression_dependencies.referencing_id
GO
CREATE TABLE Alt.DependencyTest
(
    DependencyTestId    int
        CONSTRAINT PKAlt_DependencyTest PRIMARY KEY,
    Value   varchar(20)
)
GO

CREATE PROCEDURE Alt.DependencyTest$Proc
(
      @DependencyTestId int
) AS
   BEGIN
        SELECT DependencyTest.DependencyTestId, DependencyTest.Value
        FROM Alt.DependencyTest
        WHERE DependencyTest.DependencyTestId = @DependencyTestId
   END
GO
DROP TABLE Alt.DependencyTest
GO
SELECT    coalesce(referenced_server_name,'<>') + '.'
            + coalesce(referenced_database_name,'<>') + '.'
            + coalesce(referenced_schema_name,'<>') + '.'
            + coalesce(referenced_entity_name,'<>') as referenced_object_name,
        referenced_minor_name
FROM   sys.dm_sql_referenced_entities ('Alt.DependencyTest$Proc','OBJECT')
GO


CREATE TABLE Alt.DependencyTest
(
    DependencyTestId    int
        CONSTRAINT PKAlt_DependencyTest PRIMARY KEY,
    Value   varchar(20)
)
GO
select coalesce(referencing_schema_name,'<>') + '.'
       + coalesce(referencing_entity_name,'<>') as referenced_object_name,
              referencing_class_desc as referencing_class
from   sys.dm_sql_referencing_entities ('Alt.DependencyTest','OBJECT')