Exercises

Question 1

Create a Computed Column in a table

Answer 1

CREATE TABLE #TEMPTBL (
     FIRSTNAME VARCHAR(50)
    ,LASTNAME VARCHAR(50)
    ,FULLNAME AS (FIRSTNAME + ' ' + LASTNAME) PERSISTED
)

ADD PERSISTED IF YOU WANT THE DATA ALWAYS THERE, OTHERWISE IT WILL ALWAYS BE COMPUTED

Question 2

Alter an existing computed column in a table

Answer 2

You can’t. You have to drop the column and recreate it.

Question 3

Create a FileGroup

Answer 3

ALTER DATABASE [DBNAME] ADD FILEGROUP [FILEGROUPNAME]

Question 4

Add a file to a filegroup

Answer 4

USE MASTER
GO
ALTER DATABASE [DBNAME] ADD FILE (NAME N’FILENAME’, FILENAME = N’C:\PATH\FILENAME.NDF’, SIZE = 1000KB, FILEGROWTH = 600 KB ) TO FILEGROUP [FILEGROUP]

Question 5

Create a calculated column

Answer 5

create table #calccol (
	firstname varchar(10)
	, lastname varchar (10)
        --Calculated Column
	, fullname as (firstname + ' ' + lastname) 
)

Question 6

Create a calculated Column that becomes a permanent column

Answer 6

create table #calccol (
	firstname varchar(10)
	, lastname varchar (10)
        --Calculated Column - Persisted
	, fullname as (firstname + ' ' + lastname) PERSISTED
)

Question 7

Create a Memory-Optimized Table

Answer 7

CREATE TABLE [TABLENAME] (
COL1 INT PRIMARY KEY NONCLUSTERED
, COL2 VARCHAR(5)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA)

Question 8

Create a natively compiled stored procedure

Answer 8

CREATE PROCEDURE TestProc 
    @var1 int
    WITH NATIVE_COMPILATION
              , SCHEMABINDING
AS 
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
return @var1
END

Question 9

Create a clustered (or non clustered) columnstore index on a table

Answer 9

CREATE (NON)CLUSTERED COLUMNSTORE INDEX [INDEXNAME] ON [TABLENAME] WITH (DROP_EXISTING=OFF[ON], COMPRESSION_DELAY = 0) ON [FILEGROUP]

Question 10

Create a table that has a sparse column

Answer 10

CREATE TABLE [TABLENAME] (
FIRSTNAME VARCHAR(50)
.MIDDLENAME VARCHAR(50) SPARSE NULL
)

Question 11

Create CLR Stored Procedure

Answer 11

CREATE PROCEDURE dbo.CLRTest

as external name CLRTest.StoredProcedures.SqlStoredProcedure1

Question 12

Create CLR Assembly

Answer 12

CREATE ASSEMBLY CLRTest FROM N’C:\FILEPATH\Assembly.dll’ WITH PERMISSION_SET = [SAFE,UNSAFE,EXTERNAL_ACCESS]