sqlserver笔记

1 怎样打开clr

exec sp_configure 'show advanced options', '1'  

RECONFIGURE

go

exec sp_configure 'clr enabled','1'

RECONFIGURE

go

2 how to store text, ntext, or image strings in the data row

sp_tableoption N'MyTable', 'text in row', 'ON';

sp_tableoption N'MyTable', 'text in row', '1000';

  sp_tableoption N'MyTable', 'text in row', 'OFF';

3 Some database setting
ANSI_NULLS

When ON is specified, all comparisons to a null value evaluate to UNKNOWN.

When OFF is specified, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

ANSI_PADDING

When set to ON, trailing blanks in character values inserted into varchar or nvarchar columns and trailing zeros in binary values inserted into varbinarycolumns are not trimmed. Values are not padded to the length of the column.

When set to OFF, the trailing blanks (for varchar or nvarchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

char and binary columns that allow nulls are padded to the length of the column when ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. char and binary columns that do not allow nulls are always padded to the length of the column.  

ANSI_WARNINGS

When ON is specified, errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.

When OFF is specified, no warnings are raised and null values are returned when conditions such as divide-by-zero occur.

CONCAT_NULL_YIELDS_NULL

When ON is specified, the result of a concatenation operation is NULL when either operand is NULL.

When OFF is specified, the null value is treated as an empty character string.

QUOTED_IDENTIFIER

When ON is specified, double quotation marks can be used to enclose delimited identifiers.

When OFF is specified, identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers.

 

4 Create login and user, delete the test data

CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO

USE [TestData];
GO

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO

CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @varPrice;
   END
GO

GRANT EXECUTE ON pr_Names TO Mary;
GO

REVOKE EXECUTE ON pr_Names FROM Mary;
GO

DROP USER Mary;
GO

DROP LOGIN [<computer_name>\Mary];
GO

DROP PROC pr_Names;
GO
4 SET TEXTSIZE
Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement

@@TEXTSIZE

SET NOCOUNT { ON | OFF }

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

@@ROWCOUNT，ROWCOUNT_BIG

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
Preserve @@ROWCOUNT from the previous statement execution.

Reset @@ROWCOUNT to 0 but do not return the value to the client.

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.