CREATE FULLTEXT CATALOG

Create a full-text catalog for the database.

Syntax
CREATE FULLTEXT CATALOG catalog
[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH ACCENT_SENSITIVITY = {ON|OFF}]
[AS DEFAULT] [AUTHORIZATION owner ]

Key:
catalog Name of the catalog to create
rootpath The local root directory for the catalog.
AS DEFAULT Specify that this is the default catalog.

After creating a catalog, one or more full-text indexes may be associated with it.

Example

CREATE FULLTEXT CATALOG MyCatalog AS DEFAULT;

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER FULLTEXT CATALOG

Create a full-text catalog for the database.

Syntax
ALTER FULLTEXT CATALOG catalog
{REBUILD [ WITH ACCENT_SENSITIVITY = {ON | OFF} ]
| REORGANIZE
| AS DEFAULT
}
Key:
catalog Name of the catalog to create
AS DEFAULT Specify that this is the default catalog.

Example

ALTER FULLTEXT CATALOG MyCatalog AS DEFAULT;

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP FULLTEXT CATALOG

Remove a full-text catalog from a database.

Syntax
DROP FULLTEXT CATALOG catalog

Key:
catalog Name of the catalog to remove

Drop all full-text indexes associated with the catalog before dropping the catalog.

Example

DROP FULLTEXT CATALOG cata

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE FULLTEXT INDEX

Create a full-text index on a table. One full-text index with one or more columns is allowed per table.

Syntax
CREATE FULLTEXT INDEX ON table
[(column [TYPE COLUMN type_column_name] [LANGUAGE language_term]
[,...n])]
KEY INDEX index
[ON fulltext_catalog]
[WITH CHANGE_TRACKING tracking_option]

tracking_option:
MANUAL
AUTO
OFF [, NO POPULATION]

Key:
type_column_name The column in table that holds the document type of column.
language_term The locale identifier (LCID)
index The unique key index on table
fulltext_catalog The full-text catalog, must already exist.
CHANGE_TRACKING Maintain a list of changes to the indexed data.

Example

CREATE FULLTEXT INDEX ON Shemaname.test(Column1) KEY INDEX ui_identity_column;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER FULLTEXT INDEX

Change properties of a full-text index.

Syntax
ALTER FULLTEXT INDEX ON table option

Options:
ENABLE
DISABLE
SET CHANGE_TRACKING { MANUAL | AUTO | OFF }
ADD ( column [TYPE COLUMN type_column ]
[LANGUAGE language_term ] [,...n] )
[WITH NO POPULATION ]
DROP ( column [,...n] )
[WITH NO POPULATION ]
START { FULL | INCREMENTAL | UPDATE } POPULATION
STOP POPULATION
Key:
ENABLE Activate the full-text index
DISABLE Stop gathering full-text index data.
CHANGE_TRACKING Maintain a list of changes to the indexed data.
ADD/DROP Add or remove columns from the index
language_term The locale identifier (LCID)

Example

ALTER FULLTEXT INDEX ON MySchema.MyTable ENABLE;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP FULLTEXT INDEX

Remove a full-text catalog from a database.

Syntax
DROP FULLTEXT INDEX ON table_name

Key:
table_name Table containing the Full Table Index

Example

DROP FULLTEXT INDEX ON TestSchema.SalesTable;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE FUNCTION

Create a user-defined function.

Syntax
CREATE FUNCTION [schema.] function
( [@parameter [ AS ][type_schema.] parameter_data_type [= default ]
[ ,...n ]
])
RETURNS
return_clause...
[;]


Key:
function Name of the function(s) to add.
schema The schema to which the user-defined function belongs.
parameter_data_type The data type of the function (not timestamp)
CLR functions, accept a limited range of data types.

The details of the return clause will vary for Scalar, Inline/Multistatement Table-valued, or CLR functions.
See SQL Server books online or the resources on the SQL Links page

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER FUNCTION

Alter a user-defined function.

Syntax
ALTER FUNCTION [schema.] function
( [@parameter [ AS ][type_schema.] parameter_data_type [= default ]
[ ,...n ]
])
RETURNS
return_clause...
[;]

Key:
function Name of the function(s) to add.
schema The schema to which the user-defined function belongs.
parameter_data_type The data type of the function (not timestamp)
CLR functions, accept a limited range of data types.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP FUNCTION

Remove a full-text catalog from a database.

Syntax
DROP FUNCTION [schema.]function [,...n ]

Key:
schema The schema to which the user-defined function belongs.

function Name of the function(s) to remove.

To run this command first remove any CHECK constraints, or DEFAULT constraints that reference the function.

Also an indexed computed column that references the function will prevent dropping the function.

Example

DROP FUNCTION MySchema.fn_myfunction;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

Create an index on a table or view. Also XML indexes.

Syntax
-- Relational Index
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index
ON object (column [ASC | DESC] [,...n ] )
[INCLUDE (column [ ,...n] ) ]
[WITH (option [ ,...n] ) ]
[ON { partition_scheme ( column )
| filegroup
| default
}
]
[;]

-- XML Index
CREATE [ PRIMARY ] XML INDEX index
ON object ( xml_column )
[USING XML INDEX xml_index
[FOR { VALUE | PATH | PROPERTY } ] ]
[WITH ( option [ ,...n ] ) ]
[;]

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER INDEX

Modify an existing index on a table or view.

Syntax
ALTER INDEX {index | ALL} ON object DISABLE [;]

ALTER INDEX {index | ALL} ON object REORGANIZE
[PARTITION = partition_number ]
[WITH ( LOB_COMPACTION = {ON | OFF} ) ] [;]

ALTER INDEX {index | ALL} ON object REBUILD
[ [WITH ( rebuild_index_option [ ,...n ] ) ]
| [PARTITION = partition_number
[ WITH ( single_ptn_rebuild_index_option
[ ,...n ] )
]
]
] [;]

ALTER INDEX {index | ALL} ON object SET ( set_index_option [ ,...n ] )

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP INDEX

Remove one or more relational or XML indexes from the current database.

Syntax
DROP INDEX index ON object [WITH ( options [ ,...n ] ) ]

DROP INDEX [owner.] table_or_view.index
This syntax for backwards compatibility only (pre 2005)

Options:
MAXDOP = max_degree_of_parallelism
ONLINE = { ON | OFF }
MOVE TO { partition_scheme_name (column)
filegroup_name
"default"

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE ASYMMETRIC KEY

Create an asymmetric key in the database.

Syntax:
CREATE ASYMMETRIC KEY Asym_Key_Name
[AUTHORIZATION database_principal_name]
FROM <Asym_Key_Source>
[ENCRYPTION BY PASSWORD = 'password']

CREATE ASYMMETRIC KEY Asym_Key_Name
[AUTHORIZATION database_principal_name]
WITH ALGORITHM = { RSA_512 | RSA_1024 | RSA_2048 }
[ENCRYPTION BY PASSWORD = 'password']
Key:

Asym_Key_Source: The source from which to load the asymmetric key pair:
FILE = 'path_to_strong-name_file'
EXECUTABLE FILE = 'path_to_executable_file'
ASSEMBLY Assembly_Name

database_principal_name
The owner of the asymmetric key. The owner cannot be a role or a group.
default = current user.

'path_to_strong-name_file'
Path of a strong-name file from which to load the key pair.

'path_to_executable_file'
An assembly file from which to load the public key.

Assembly_Name
Name of an assembly from which to load the public key.

'password'
Password with which to encrypt the private key.
default = encrypt using the database master key.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER ASYMMETRIC KEY

Change properties of an asymmetric key.

Syntax:
ALTER ASYMMETRIC KEY Asym_Key option

options
WITH PRIVATE KEY ( password_option [, password_option])
REMOVE PRIVATE KEY
ATTESTED BY = 'path_to_dll'
REMOVE ATTESTED OPTION

password_options
ENCRYPTION BY PASSWORD = 'password'
DECRYPTION BY PASSWORD = 'old_password'

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE SYMMETRIC KEY

Create a symmetric key in the database.

Syntax:
CREATE SYMMETRIC KEY Sym_key
[AUTHORIZATION owner]
WITH key_options [, ... n]
ENCRYPTION BY crypt [ , ... n ]

Options
crypt:
CERTIFICATE certificate
PASSWORD = 'password'
SYMMETRIC KEY symmetric_key_name
ASYMMETRIC KEY asym_key_name

key_options:
KEY_SOURCE = 'pass_phrase'
ALGORITHM = algorithm
IDENTITY_VALUE = 'identity_phrase'

algorithm:
DES | TRIPLE_DES | RC2 | RC4 | RC4_128 |
DESX | AES_128 | AES_192 | AES_256

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

OPEN SYMMETRIC KEY

Decrypt a symmetric key and makes it available for use.

Syntax:
OPEN SYMMETRIC KEY Key DECRYPTION BY decrypt_option

decrypt_options:
CERTIFICATE certificate [WITH PASSWORD = 'password']
ASYMMETRIC KEY asym_key [WITH PASSWORD = 'password']
SYMMETRIC KEY decrypting_Key
PASSWORD = 'decryption_password'

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CLOSE SYMMETRIC KEY

Close a symmetric key, or close all the symmetric keys open in the current session.

Syntax:
CLOSE {SYMMETRIC KEY key | ALL SYMMETRIC KEYS}

Open symmetric keys are bound to the session not to the security context.

__________________
The MOSS
Master of Solution Service

ALTER SYMMETRIC KEY

Change the properties of a symmetric key.

Syntax:
ALTER SYMMETRIC KEY Sym_key key_options

key_options ::=
ADD ENCRYPTION BY crypt [, ... n]
DROP ENCRYPTION BY crypt [, ... n]

crypt:
CERTIFICATE certificate
PASSWORD = 'password'
SYMMETRIC KEY symmetric_key_name
ASYMMETRIC KEY asym_key_name

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP SYMMETRIC KEY

DROP a symmetric key from the database.

Syntax:
DROP SYMMETRIC KEY key_name

key_name
Name of the asymmetric key to be dropped

If the key is open in the current session the drop will fail.

Requires CONTROL permission on the symmetric key.

Examples

__________________
The MOSS
Master of Solution Service

KILL

Terminate a user process

Syntax
KILL {session_ID | UOW} [WITH STATUSONLY]

Key
session_ID The session ID of the process to terminate.Int)
UOW The Unit of Work ID of a distributed transaction. (GUID)
STATUSONLY Generate a rollback progress report (for an earlier KILL statement)

The KILL statement may take some time to roll back a long transaction.

KILL WITH STATUSONLY will not terminate or roll back any transactions.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)