What is the use of 'SET TEXTSIZE'
Specifies the size of text and ntext data returned with a SELECT statement.
Syntax:- SET TEXTSIZE { number }
Arguments
---------
number:-
Is the size (an integer) of text data, in bytes. The maximum setting for SET TEXTSIZE is 2 gigabytes (GB), specified in bytes. A setting of 0 resets the size to the default (4 KB).
Remarks:- Setting SET TEXTSIZE affects the @@TEXTSIZE function.
*************************************************************************************
Why 'DATALENGTH' is used?
DATALENGTH
Returns the number of bytes used to represent any expression.
Syntax:- DATALENGTH ( expression )
Arguments
---------
expression:-
Is an expression of any type.
Return Types:- int
Remarks:-
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
The DATALENGTH of NULL is NULL.
Example
-------
This example finds the length of the pub_name column in the publishers table.
USE pubs
GO
SELECT length = DATALENGTH(pub_name), pub_name
FROM publishers
ORDER BY pub_name
GO
Here is the result set:
length pub_name
----------- ----------------------------------------
20 Algodata Infosystems
16 Binnet & Hardley
21 Five Lakes Publishing
5 GGG&G
18 Lucerne Publishing
14 New Moon Books
17 Ramona Publishers
14 Scootney Books
(8 row(s) affected)
*************************************************************************************
How can you define a user defined data type?
User-defined data types are based on the system data types in Microsoft® SQL Server™ 2000. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability. For example, a user-defined data type called postal_code could be created based on the char data type.
When a user-defined data type is created, you must supply these parameters:
Name:-
System data type upon which the new data type is based:-
Nullability (whether the data type allows null values):-
When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.
Note:- If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user- defined database, the data type exists only in that user-defined database.
You can create a user defined data type using any of the following methods:-
i) Transact-SQL :-
------------
Syntax:-
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
Arguments:-
---------
[@typename =] type
Is the name of the user-defined data type. Data type names must follow the rules for identifiers and must be unique in each database. type is sysname, with no default.
[@phystype =] system_data_type
Is the physical, or Microsoft® SQL Server™-supplied, data type (decimal, int, and so on) on which the user-defined data type is based. system_data_type is sysname, with no default, and can be one of these values:
'binary( n )', image, smalldatetime, Bit, int, smallint, 'char( n )', 'nchar( n )', text, Datetime, ntext, tinyint, Decimal, numeric, uniqueidentifier, 'decimal[ ( p [, s ] ) ]', 'numeric[ ( p [ , s ] ) ]', 'varbinary( n )', Float, 'nvarchar( n )', 'varchar( n )', 'float( n )', real
Quotation marks are required around all parameters that have embedded blank spaces or punctuation marks. For more information about available data types, see Data Types.
n
Is a nonnegative integer indicating the length for the chosen data type.
p
Is a nonnegative integer indicating the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. For more information, see decimal and numeric.
s
Is a nonnegative integer indicating the maximum number of decimal digits that can be stored to the right of the decimal point, and it must be less than or equal to the precision. For more information, see "decimal and numeric" in this volume.
[@nulltype =] 'null_type'
Indicates the way the user-defined data type handles null values. null_type is varchar(8), with a default of NULL, and must be enclosed in single quotation marks ('NULL', 'NOT NULL', or 'NONULL'). If null_type is not explicitly defined by sp_addtype, it is set to the current default nullability. Use the GETANSINULL system function to determine the current default nullability, which can be adjusted by using the SET statement or sp_dboption. Nullability should be explicitly defined.
Note:- The null_type parameter only defines the default nullability for this data type. If nullability is explicitly defined when the user-defined data type is used during table creation, it takes precedence over the defined nullability. For more information, see ALTER TABLE and CREATE TABLE.
[@owner =] 'owner_name'
Specifies the owner or creator of the new data type. owner_name is sysname. When not specified, owner_name is the current user.
Return Code Values:- 0 (success) or 1 (failure)
Remarks
-------
A user-defined data type name must be unique in the database, but user-defined data types with different names can have the same definition.
Executing sp_addtype creates a user-defined data type and adds it to the systypes system table for a specific database, unless sp_addtype is executed with master as the current database. If the user-defined data type must be available in all new user-defined databases, add it to model. After a user datatype is created, you can use it in CREATE TABLE or ALTER TABLE, as well as bind defaults and rules to the user-defined data type.
User-defined data types cannot be defined using the SQL Server timestamp data type
Examples
--------
A. Create a user-defined data type that does not allow null values
This example creates a user-defined data type named ssn (social security number) that is based on the SQL Server-supplied varchar data type. The ssn data type is used for columns holding 11-digit social security numbers (999-99-9999). The column cannot be NULL.
Notice that varchar(11) is enclosed in single quotation marks because it contains punctuation (parentheses).
USE master
EXEC sp_addtype ssn, 'VARCHAR(11)', 'NOT NULL'
B. Create a user-defined data type that allows null values
This example creates a user-defined data type (based on datetime) named birthday that allows null values.
USE master
EXEC sp_addtype birthday, datetime, 'NULL'
C. Create additional user-defined data types
This example creates two additional user-defined data types, telephone and fax, for both domestic and international telephone and fax numbers.
USE master
EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'
EXEC sp_addtype fax, 'varchar(24)', 'NULL'
*************************************************************************************
What are 'Rules' in SQL Server?
Rules are a backward-compatibility feature that perform some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column. CHECK constraints are also more concise than rules; there can only be one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are specified as part of the CREATE TABLE statement, while rules are created as separate objects and then bound to the column.
This example creates a rule that performs the same function as the CHECK constraint example in the preceding topic. The CHECK constraint is the preferred method to use in Microsoft® SQL Server™ 2000.
CREATE RULE id_chk AS @id BETWEEN 0 and 10000
GO
CREATE TABLE cust_sample
(
cust_id int
PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
)
GO
sp_bindrule id_chk, 'cust_sample.cust_id'
GO
CREATE RULE Statement
---------------------
Creates an object called a rule. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column. Rules, a backward compatibility feature, perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred, standard way to restrict the values in a column (multiple constraints can be defined on one or multiple columns). A column or user-defined data type can have only one rule bound to it. However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated.
Syntax :- CREATE RULE rule
AS condition_expression
Arguments
---------
rule :-
Is the name of the new rule. Rule names must conform to the rules for identifiers. Specifying the rule owner name is optional.
condition_expression:-
Is the condition(s) defining the rule. A rule can be any expression valid in a WHERE clause and can include such elements as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). A rule cannot reference columns or other database objects. Built-in functions that do not reference database objects can be included.
condition_expression includes one variable. The at sign (@) precedes each local variable. The expression refers to the value entered with the UPDATE or INSERT statement. Any name or symbol can be used to represent the value when creating the rule, but the first character must be the at sign (@).
Remarks
-------
The CREATE RULE statement cannot be combined with other Transact-SQL statements in a single batch. Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types. A rule can be created only in the current database. After creating a rule, execute sp_bindrule to bind the rule to a column or to a user-defined data type.
The rule must be compatible with the column data type. A rule cannot be bound to a text, image, or timestamp column. Be sure to enclose character and date constants with single quotation marks (') and to precede binary constants with 0x. For example, "@value LIKE A%" cannot be used as a rule for a numeric column. If the rule is not compatible with the column to which it is bound, Microsoft® SQL Server™ returns an error message when inserting a value, but not when the rule is bound.
A rule bound to a user-defined data type is activated only when you attempt to insert a value into, or to update, a database column of the user-defined data ype. Because rules do not test variables, do not assign a value to a user-defined data type variable that would be rejected by a rule bound to a column of the same data type.
To get a report on a rule, use sp_help. To display the text of a rule, execute sp_helptext with the rule name as the parameter. To rename a rule, use sp_rename.
A rule must be dropped (using DROP RULE) before a new one with the same name is created, and the rule must be unbound (using sp_unbindrule) before it is dropped. Use sp_unbindrule to unbind a rule from a column.
You can bind a new rule to a column or data type without unbinding the previous one; the new rule overrides the previous one. Rules bound to columns always take precedence over rules bound to user-defined data types. Binding a rule to a column replaces a rule already bound to the user-defined data type of that column. But binding a rule to a data type does not replace a rule bound to a column of that user-defined data type. The table shows the precedence in effect when binding rules to columns and to user-defined data types where rules already exist.
If a column has both a default and a rule associated with it, the default must fall within the domain defined by the rule. A default that conflicts with a rule is never inserted. SQL Server generates an error message each time it attempts to insert such a default.
Examples
--------
A. Rule with a range
This example creates a rule that restricts the range of integers inserted into the column(s) to which this rule is bound.
CREATE RULE range_rule
AS
@range >= $1000 AND @range < $20000
B. Rule with a list
This example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule.
CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877')
C. Rule with a pattern
This example creates a rule to follow a pattern of any two characters followed by a hyphen, any number of characters (or no characters), and ending with an integer from 0 through 9.
CREATE RULE pattern_rule
AS
@value LIKE '_ _-%[0-9]'
*************************************************************************************
What are deterministic and non deterministic functions?
All functions are deterministic or nondeterministic:
Deterministic functions always return the same result any time they are called with a specific set of input values.
Nondeterministic functions may return different results each time they are called with a specific set of input values. Whether a function is deterministic or nondeterministic is called the determinism of the function.
For example, the DATEADD built-in function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.
Earlier versions of Microsoft® SQL Server™ have no functionality that is dependent on the determinism of functions. In Microsoft SQL Server 2000, nondeterministic functions cannot be specified in two types of Transact-SQL expressions:
An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions.
A clustered index cannot be created on a view if the view references any nondeterministic functions.
One of the properties SQL Server records for user-defined functions is whether the function is deterministic. A nondeterministic user-defined function cannot be invoked by either a view or computed column if you want to create an index on the view or computed column.
*************************************************************************************
Size of a page in SQL 7/2000?
SQL Server 2000 stores data in a special structure called data pages that are 8Kb (8192 bytes) in size. Some space on the data pages is used to store system information, which leaves 8060 bytes to store user's data. So, if the table's row size is 4040 bytes, then only one row will be placed on each data page.
*************************************************************************************
