Additional SQL Server Components

Question 1

What is full text indexing?

Answer 1

This allows the system to index all of the words inside of a column instead of just the whole value. It allows for faster searches, but takes more resources.

Question 2

What are the steps to use a full text index?

Answer 2

You need to create a catalog and then a full text index.

Question 3

What is a full text stoplist for full text indexing?

Answer 3

They are filler words that aren’t useful.

Question 4

What are statistical symantecs?

Answer 4

It can query based on the meaning of the words instead of just the words themselves

Question 5

Where does SSIS security live?

Answer 5

In the MSDB database

Question 6

What are the database roles that SSIS uses to define security?

Answer 6

• db_ssisadmin - Full Access
• db_ssisltduser - View and Count packages (execute own packages but not others)
• db_ssisoperator - View, export, execute, count all packages but cannot write any packages

Question 7

What is the system table that shows all of the SSIS packages that are saved?

Answer 7

SYSSISPACKAGES

Question 8

What is the system table that shows the log files for SSIS?

Answer 8

SYSSISLOG

Question 9

What is FILESTREAM?

Answer 9

This allows SQL to store unstructured documents, images, etc, on the File system

Question 10

What is the datatype of a Filestream?

Answer 10

varbinary(max)

Creates BLOBs

Question 11

How do you enable Filestream on an instance of SQL server?

Answer 11

Within SQL Server Configuration Manager -> SQL Server Services -> Instance Name -> Properties -> FILESTREAM

EXEC sp_configure filestream_access_level, 2
go
reconfigure
go

Question 12

After you configure a database to use Filestream, what else do you need to do to start using Filestream?

Answer 12

The instance needs a Filestream Filegroup set up

Question 13

What is a FileTable?

Answer 13

On a Filestream enabled database, it allows applications to access the files as if they were in the file system.