Exercises Flashcards Preview

70-464 Certification > Exercises > Flashcards

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

Create a Computed Column in a table

A
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
Q

Alter an existing computed column in a table

A

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

3
Q

Create a FileGroup

A

ALTER DATABASE [DBNAME] ADD FILEGROUP [FILEGROUPNAME]

4
Q

Add a file to a filegroup

A

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
Q

Create a calculated column

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

Create a calculated Column that becomes a permanent column

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

Create a Memory-Optimized Table

A

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

8
Q

Create a natively compiled stored procedure

A
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
Q

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

A

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

10
Q

Create a table that has a sparse column

A

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

11
Q

Create CLR Stored Procedure

A

CREATE PROCEDURE dbo.CLRTest

as external name CLRTest.StoredProcedures.SqlStoredProcedure1

12
Q

Create CLR Assembly

A

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