Exercises Flashcards Preview

70-464 Certification > Exercises > Flashcards

Flashcards in Exercises Deck (12)
Loading flashcards...
1

Create a Computed Column in a table

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

2

Alter an existing computed column in a table

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

3

Create a FileGroup

ALTER DATABASE [DBNAME] ADD FILEGROUP [FILEGROUPNAME]

4

Add a file to a filegroup

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]

5

Create a calculated column

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

6

Create a calculated Column that becomes a permanent column

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

7

Create a Memory-Optimized Table

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

8

Create a natively compiled stored procedure

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

9

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

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

10

Create a table that has a sparse column

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

11

Create CLR Stored Procedure

CREATE PROCEDURE dbo.CLRTest
as external name CLRTest.StoredProcedures.SqlStoredProcedure1

12

Create CLR Assembly

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