DATA TYPE CONVERTION IN MS-SQL SERVER

It provides two levels of data conversion

When data from one object is moved to, combined with, combined with data from another object, the data may have to be converted from the data type of one object to data type of the other.
When a data from a Transport-SQL result column, return code, or output parameter is moved into a program variable.
In MS-SQL server there are two categories of datatype conversion.

Implicit Conversion
Explicit Conversion Applied through CAST & COVERT

CAST and CONVERT

They are used to convert datatype explicitly from one type to another.
CAST and CONVERT provide similar functionality.
CAST is more preferred as it is up to the standards of SQL-92.

Syntax:

CAST (Expression As Date_Type)

--à Expression is any valid Microsoft SQL server Expression.

--àData_Type is the target system supplied datatype which includes bigint and sql_variant.

Example:

Select CAST (0*AE123 As Varchar (60) + ‘ ’ +’Binary’

Select CAST (193.57 As Varbinary (20))

Select CAST (CAST (193.57 As Varbinary (20)) As decimal (10,5))

Select ‘The Course Fee is’ + CAST (CourseFees As Varchar (17)) + ‘Rupees’ From Students.

Select CompanyName As Varchar (17)) + CAST (CompDesc As char (100)) Description From CompaniesMaster

Select StudID Identity, StudName Name, CAST (‘April 10, 2004’ As Varchar) From Students.

Select StudID Identify, StudName Name, CAST (‘April 10, 2004’ As char) From Students.

NOTE: The width or size can be specified for the varchar or char type in CAST.

ARTHIMATICAL EXPRESSION IN SELECT

A Select list can contain expressions that are limit by applying operations to one or more simple expression.
It allows result sets to contain values that do not exist in the base tables, but are calculated from the values stored in the base tables.
The Result set columns are called derived columns.
The derived columns can include

a. Calculations and computations that use arithmetic operators or functions on numeric column or constants.

b. Data type Conversion.

c. CASE Functions.

d. Subquries.

The arithmetic operations help in

a. Addition

b. Subtraction

c. Multiply

d. Divide

The supported operations are

(+) – Addition

(-) – Subtraction

(/) – Division

(*) – Multiplication

(%) – Modules

The Addition, Subtraction, Division and Multiplication can be use any Numeric column or Expression

Supported Dayebases are:

a. int

b. smallint

c. tinyint

d. decimal

e. numeric

f. float

g. real

h. money

i. smallmoney

The Modules operator can only be on int, smallint or tinyint columns or expressions.
Arithmetic operations can be performed on datetime and smalldatetime columns using the date functions or regular addition or subtraction arithmetic operations.
Arithmetic operations can be used to perform computation involving one or more columns.

Examples:

Select StudName, CourseName, CourseFees Academic, CourseFees + 2500 “With Exam Fee” from Students

Select StudID Identify, StudName Name, CourseFees, Academic, 500 + CourseFees “With Sports” From Students.

Select StudName Name, CourseFees Annual, CourseFees/12 [Monthly Installment]

From Students.

Select StudName Name, CourseFees Actual, CourseFees – 4000 “After Scholarship” From Students.

Select StudName Name, CourseFees Annual, CourseFees * 3 “Three Years” From Students.

Select StudName Name, CourseFees Annual, CourseFees/12 “Monthly Installment”, CourseFees % 12 “Value adjustable” From Students

Select CompanyName Name, CompanyAddress Address, CompanyArea Area, CompanyAre%4 Remainder From CompaniesMaster

Select 1234 Sample,

1234 * 2 Multiplication,

1234/2 Quotient,

1234-24 Subtraction,

1234 + 16 Addition,

1234%2 Remainder

RESTRICTING THE ROWS

Where Clause

It is used to supply a search condition to restrict the rows returned.

Syntax:

[Where <search_condition> | <old_outer_join>] <old_outer_join> :: =Column_Name {* = | = *} column_Name

Arguments Description

<Search_condition>

Restricts the rows returned in the result set through the use of predicates.

The number of predicates can be Unlimited.

<old_outer_join>

Specifies the Outer join

* = Specifies a left outer join

* = * Specifies a right outer join

Where clause in a SELECT statement controls the rows from the source tables that are used to build the result set.

Where can specify a series of search conditions.

The WHERE clause can be used not just to retrieve the data, but also to update the required data.

FILTERING INFORMATION FOR SPECIFIC VALUE IN A FIELD

Select * From CompaniesMaster WHERE CompanyName = ‘Naresh’

NOTE: the character strings are not case sensitive by default.

Select CompanyID, CompanyName Name, ‘The Phone Number is’ + CAST (CompanyPhoneno As Varchar (30)) “Phone Number” from CompaniesMaster”

Select * from Students WHERE StudDOB = ‘11/06/1980’

Select StudID Identity, StudName Name, StudDOB [Date of Birth] from Students WHERE StudDOB = ‘1980/11/6’

Select StudID, StudName Name, ‘ Your Birthday is on’ + CAST (StudDOB As char)

From Students WHERE StudDOB ‘1980/11/6’

Select StudID, StudName Name, CourseFees “Actual Fees” from Students WHERE CourseFees = 25000.00

Select Company_ID Identity, CompanyName Name, CompanyEmail Email_ID From CompaniesMaster WHERE CompanyEmail = ‘Naresh @ Yahoo.com’

APPLYING SEARCH CONDITIONS

Search condition is a combination of one or more predicates using the logical operator like

And --à Checks for all conditions to be TRUE.

Or --à Checks for any one condition to be TRUE.

Not --à Executes the Negation state.

When more than one logical operator is used the procedure is

Arithmetic operators and Bitwise operators are handled before logical operator.

The procedure of execution can be controlled using proper parenthesis, and it also increases readability.

SEARCH CONDITIONS ON ONE COLUMN OR CONDITION

It enables to specify search alternative values in a column.

It expands the scope of the search and may return more rows.

Select StudentName, CourseFees From Students WHERE CourseFees < 10000 or CourseFees < 20000

Select StudName Name, CourseName Course, CourseFees Fees From Students WHERE CourseFees > 10000 or CourseFees < 20000

Select StudName Name, CourseName Course From Students WHERE CourseName = ‘MCA’ Or CourseName = ‘MBA’

Select StudName Name, StudDOB “Birth Date” From Students WHERE StudDOB = ‘1980/11/06’ or StudDOB = ‘06/21/1980’

AND Condition

It enables to specify that the values in a column must meet two or more conditions.

It narrows the scope of the search.

Select * from Students WHERE CourseFees >10000 And CourseFees < 25000.

Select * from Students WHERE StudDOB > ‘1980/11/06’ And StudDOB < ‘1995/05/23’

MUTIPLE SEARCH CONDITIONS FOR MULTIPLE COLUMNS

We can expand or narrow the scope of the query by including several data columns as part of the search condition.

OR Condition

This condition is specified to create a query that searches for values in either of two or more columns.

Each separate condition is placed in a different column.

Select * from Students WHERE StudID = 1000 Or StudName = ‘Satish’

Select * from Students WHERE StudName = ‘Kumar’ or StudDOB = ‘1980/11/06’

Select * from Students WHERE StudDOB = ‘1980/11/06’ Or (StudName = ‘Kumar’ Or CourseName = ‘MCA”)

AND Condition

This condition is specified, to create q query that must meet all conditions in two or more columns.

Select * from Students WHERE StudName = ‘Satish’ And CourseFees = 25000

Select * from Students WHERE (StudName = ‘Satish’ And CourseFees = 25000) And CourseName = ‘MCA’

Select * from Students WHERE StudID = 1000 And StudName = ‘Satish’

Or and And Combinations

Select * from Students WHERE (StudName = ‘Satish’ And CourseFees = 25000) Or CourseName = ‘MBA’

Select * from Students WHERE (StudName = ‘Satish’ Or StudDOB = ‘1995/05/23’) And CourseFees < 20000

T-SQL OPERATORS

Between Operator

It is used to specify a range to test.

Syntax:

Text_expr [NOT] Between StartExpr And EndExpr

àText_expr:

It is the expression to test for in the range defined by StartExpr And EndExpr

àText_Expr must be the same datatype as both StartExpr and ExndExpr

à NOT specifies that the result of the predicate be negated.

à AND it acts as a placeholder.

THINGS TO NOTE

It returns TRUE if the value of the test_expr is greater than or equal to the value of start_expr and less than or equal to the value of endexpr.

NOT BETWEEN returns TRUE if the value of test_expr is less than the value of startexpr or greater than the value of endexpr.

To specify an exclusive range, use the greater than and less than operators.

If any input to the BETWEEN or NOT BETWEEN is Null, the result is Unknown.

The startexpr and endexpr are inclusive in the query.

The startexpr should be less than endexpr.

Select * from Students WHERE CourseFees BETWEEN 15000 And 18000.

Select * from Students WHERE StudDOB BETWEEN ‘1980/11/06’ And ‘1995/05/23’

Select * from Students WHERE CourseFees NOT BETWEEN 15000 And 18000

Select * from Students WHERE StudName NOT BETWEEN ‘Satish’ And ‘Kumar’

Select * from Students WHERE StudDOB NOT BETWEEN ‘1980/11/06’ And ‘1995/05/23’

IN

It determines if a given value matches any value in a subquery or a list.

Syntax:

Test_expr [NOT] IN (subquery | expr [,…n])

Test_expr: In any valid MS-SQL Server expr.

Subquery: It is a subquery that has a result set of one column.

The column should have the same data type as Test_expr.

Expr: It is a list of expression to test for a match.

Select * from Students WHERE CourseFees IN (25000, 15000)

Select from Students WHERE StudName IN (‘Satish’, ‘Kumar’)

Select * from Students WHERE StudDOB IN (‘1980/11/06’, ‘1995/05/23’)

Select * from Students WHERE CourseFees NOT IN (15000, 25000)

Select * from Students WHERE StudName NOT IN (‘Satish’, ‘Kumar’)

Select * from Students WHERE StudDOB NOT IN (‘1980/11/06’, ‘1995/05/23’)

IS [NOT] NULL

It determines whether or not a given expression is NULL.

Syntax:

Expression IS [NOT] NULL

Things to Note

If the value of the expression is NULL, Then return TRUE else returns FALSE.

To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators.

The comparison operator return UNKNOWN if either or both arguments are NULL.

Select * from Students WHERE CourseFees IS NULL.

Select * from Students WHERE CourseFees IS NOT NULL.

Select * from Students WHERE CourseFees <18000 Or CourseFees IS NULL.

LIKE

It determines whether or not a given characters string matches a specified pattern.

The pattern can include regular characters and wildcard characters.

Syntax:

Match_Expr [NOT] LIKE pattern [ESCAPE expr_char]

Pattern

% - Any string of zero or more characters.

_ - Any single character.

[] – Any single character within the specified range.

[^] – Any single character not within the range [^a_f] [^abcdef]

THINGS TO NOTE

The LIKE keyword searches for character string, date, or time values that match a specified pattern.

The LIKE keyword should use a regular expression to condition the pattern that values are matched against.

The pattern contains a character string for search, while can contain any combination of wildcards.

Enclose the wildcards and the character string in single quotation marks.

Some Illustrations

LIKE ‘MC%’ --à All strings beginning with MC.

LIKE ‘%inger’ à All strings ending with inger.

LIKE ‘%en%’ -à All strings containing en.

LIKE ‘_heryl’ -à strings with six letter words ending with heryl.

LIKE ‘[ck] ars [eo] n’ --à search for pattern which match within brackets.

EX: carsen, karsen

LIKE ‘[M-N] integer’ --à searches for all names ending with letter inger.

LIKE ‘M [^C]%’ --à searches for all names beginning with letter M that do not have the letter C as second letter.

Select * from Students WHERE StudName LIKE ‘%a%’

Select * from Students WHERE StudName LIKE ‘Kum_r’

Select * from Students WHERE StudName LIKE ‘%ar’

Select * from Students WHERE StudName LIKE ‘%Kumar’

Select * from Students WHERE StudName NOT LIKE ‘Raj %’

Select * from Students WHERE CourseName NOT LIKE ‘_c_’

Select * from Students WHERE email NOT LIKE ‘%@g_ _ _ _ .com’

USING DISTRICT IN SELECT

The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement.

The NULL values are considered to be duplicates of each other, returning only one NULL.

When group functions are implemented, DISTINCT keyword is optional.

When DISTNCT operator is declared, the expression must consist of a column name only, it cannot include arithmetic expression.

The DISTINCT operator should be applied upon the first column in the SELECT list.

A single SELECT statement can have only one DISTINCT operator.

Select DISTINCT StudName from students.

Select DISTINCT StudName, CourseName from Students.

Select DISTINCT StudName, CourseName from Students WHERE CourseName <> ‘M.Sc’

ORDER BY CLAUSE

It sorts query results by one or more columns upto 8060 bytes.

The sorting of data can be ascending (Asc) or descending (Desc), by default Asc is considered.

It is invalid in

Views, Inline function, Derived tables, Subquries.

If TOP is specified it is accepted in the above cases.

The ntext, text or image columns cannot be used in an ORDER BY clause.

NULL values are treated as the lowest possible values.

The clause can include items not appearing in the SELECT list.

The sort columns must appear when DISTINCT and UNION is specified.

Select * from CompaniesMaster ORDER BY companyID DESC.

Select CompanyName, CompanyAddress from CompaniesMaster ORDER BY CompanyPhoneNo.

The ORDER as implemented

Data type Asc Desc

Character Based A-Z Z-A

Numeric 0-9 9-0

Dates old-latest latest-old

Select StudID, StudName from Students ORDER BY StudName Desc

Select StudID, StudName from Students ORDER BY StudName Desc, StudID Asc

Select StudID, StudName, StudDOB from Students ORDER BY StudName Desc, StudID Asc, StudDOB Desc

Select StudID, StudName, StudDOB from Students WHERE StudName NOT IN (‘Satish’, ‘Kumar’) ORDER BY StudName Desc, StudDOB

T-SQL BUILT-IN FUNCTIONS

At the top level all functions in MS-SQL Server are defined as deterministic or nondeterministic

Deterministic Functions

They return the same result any time they are called with a specific set of input values.

Nondeterministic Functions

They return different results each time they are called with a specific set of input values.

Built-in Function Determinism

The determinism of any Built-in Function cannot be influenced.

The Built-in Functions are deterministic or non-deterministic based on how the functions are implemented by SQL-Server.

All the Applegate and string built-in functions are deterministic expect the string functions CHARINDEX and PATINDEX.

At the top level the categories of functions are

Row set Functions

Aggregate Functions

Scalar Functions

ROWSET FUNCTIONS

They return an object that can be used in place of a table reference in T-SQL statement.

All rowset functions are nondeterministic, as they do not return the same results every time they are called with a specific set of input values.

AGGREGATE FUNCTIONS

They perform a calculation on a set of values and return a single value, with exception of COUNT.

The Aggregate functions ignore NULLs.

These functions are used with the GROUP BY clause.

All aggregate functions are deterministic, as they return the same value any time they are called with a given set of input values.

These functions are allowed as expressions only in

The select list of a SELECT statement.

A COMPUTE Or COMPUTE BY clause.

A Having clause.

SCALAR FUNCTIONS

They operate on a single value and then a single value.

They can be used wherever an expression is valid.

Categories of Scalar Functions

Configuration Functions: Returns information about the current configuration

Cursor Functions: Returns information about cursors.

Date and Time Functions: performs an operation on a date and time input values and can returns either a string, Numeric, date or time.

Mathematical Functions: performs a calculation based on input values provided as parameters to the functions, and returns a numeric value.

Metadata Functions: returns information about the database and database objects.

Security Functions: performs an operation on a string char or varchar input value and returns a string or numeric value.

System Functions: performs operations on return info about values, objects, and settings in MS-SQL Server.

System Statistical Functions: returns statistical info about the system.

Text and Image Functions

Performs an operation on Text or Image input values or column and return information about the value.

STRING FUNCTIONS

ASCII Function

Return the ASCII code value of the leftmost character of a character expression.

Syntax: ASCII (char_expr)

Char_expr is an expression of the type Char or VarChar.

Return Type: int

Example: Select ASCII (‘A’), ASCII (‘a’)

Select ASCII (‘APPLE’), ASCII (‘apple’)

Char Function

It is a string function that converts an int ASCII code to a character.

Syntax: Char (int_expr)

Int_expr is an integer from 0 to 255.

NULL is returned if the integer expression is not this range.

Return Type: char (1)

Examples: Select char (65), char (97)

Select char (65), char (‘A’)

Select char (65), char (‘A’), char (ASCII (‘A’)), ASCII ((char (65))

Select studname, ASCII (studname) from students

Select studname, ASCII (studname) from students where ASCII (studname) =83 or studname = ‘KUMAR’

Select studname, ASCII (studname), coursename from students where ASCII (coursename) = ASCII (‘MCA’) Order By studname

CharIndex Function

It returns the startup position of the specified expression in a character string.

Syntax: CharIndex (exp1, exp2, [start_loc])

Exp1 is an expression contains the sequence of character to be found.

Exp2 is an expression usually a column searched for the specified sequence.

Start_loc is the character position to start searching for exp1 and exp2.

If the start_loc is not given | is a –ve number| is 0 the search starts at the beginning of exp2.

Return Type: int

If exp1 is not found within exp2, then charindex return 0.

Examples:

Select companyinfo, cahrindex (‘Company’, companyinfo) from CompanyMaster.

Select companyinfo, charindex (‘IS’, companyinfo) from CompanyMaster

Select companyinfo, charindex (‘IS’, companyinfo) from CompanyMaster where charindex (‘IS’, companyinfo) = 3

Select companyinfo, charindex (‘IS’, companyimfo, 40 from companyMaster where charindex (‘IS’, companyinfo) = 3

Left Function

It returns the part of a character string startup at a specified number of character from the left.

Syntax:

Left (char_expr, int_expr)

Char_expr is an expression of character or binary data.

It can be a constant, variable, or column.

Char_expr must be a datatype that can be complicity convertible to VarChar.

Explicitly the conversion is done by applying CAST function

Int_expr is a positive whole number.

If int_expr is negative then null string is returned.

Return Type: VarChar

Example:

Select Left (‘manager’, 6).

Select studname, Left (studname, 3) from students.

Select studname, Left (studname, 3) from students where Left (studname, 3) Like ‘SAT’.

Right Function

It returns part a character string starting at a specified number of int_expr characters from the right.

Syntax:

Char_expr is an expression of character data.

Char_expr can be constant, variable or column of either character or binary data.

Int_char is the starting position expressed in +ve whole number.

Int_char is –ve an error is returned.

Return Type: VarChar

Example:

Select Right (‘manager’, 4)

Select Right (‘manager’, 3) from students

Select Right (‘manager’, 3) from students where Right (studname, 3) = ‘MAR’

LTrim Function

It returns a character expression after removing leading blanks.

Syntax: LTrim (char_expr)

Char_expr is an expression of character of binary data.

Char_expr can be constant, variable or column.

Char_expr should be of a datatype that is implicitly convertible to VarChar else use CAST.

Return Type: VarChar

Example:

Select ‘This is a Sample String’, LTrim (‘This is a Sample String’).

RTrim Function

It returns a character string after truncating all trailing blanks.

Syntax: RTrim (char_expr)

Return Type: VarChar

Example:

Select ‘This is a Sample String’, RTrim (‘This is a Sample String’)

Len Function

It returns the number of characters of a given string expression, excluding trailing blanks.

Syntax: Len (string_expr)

String_expr is the string expression to be evaluated.

Return Type: int

Select ‘Sample Data’, Len (‘Sample Data’)

Select studname, Len (studname) from students

Select studname, Len (studname) from students where Len (studname) = 6

Select studname, Len (studname) from students where Len (studname) = 6 and Right (studname, 3) like ‘isn’

Lower Function

It returns a character expression after converting uppercase data to lower case.

Syntax: Lower (char_expr)

Char_expr is an expression of characters or binary data.

Char_expr can be a constant, variable or column.

Char_expr must be of datatype that is implicitly convertible to VarChar else CAST can be used.

Return Type: VarChar

Example:

Select ‘Sample Data’, Lower (‘Sample Data’)

Select ‘Sample Data’, Lower (‘Sample Data’) from students

Select ‘Sample Data’, Lower (‘Sample Data’) from students where Lower (studname) = ‘satish’

Upper Function

It returns a character with lowercase character data converted to uppercase.

Syntax: Upper (char_expr)

Char_expr is an expression of character data.

Char_expr can be a constant, variable, or column of either character or binary data.

Return Type: VarChar

Example:

Select ‘sample data’, Upper (‘sample data’)

Select ‘sample data’, Upper (‘sample data’) from students

Select ‘sample data’, Upper (‘sample data’) from students where Upper (studname) = ‘SATISH’

Select Upper (RTrim (studname)) + ‘,’ + coursename as [student information] from students order by studname.

Patindex Function

It returns the staring position of the first occurrence of a pattern in a specified expression.

It Returns Zero of the pattern is not found.

Can be implied on all valid text and character datatypes.

Syntax: Patindex (‘%Pattern%’, expr)

%Pattern% is a literal string.

%Pattern% is a wild card characters can be used.

Expr is an expression, usually a column that is searched for the specified pattern.

Return Type: int

Example:

Select companyinfo, Patindex (‘%company%’, companyinfo) from CompanyMaster.

Select companyinfo, Patindex (‘%com_any%’, companyinfo) from CompanyMaster where Patindex (‘%com_any%’, companyinfo) = 6

Replace Function

It returns all occurrences of the second gives string expression in the first string expression with a third expression.

Syntax: Replace (‘str_expr1’, ‘str_expr2’, xtr_expr3’)

Str_expr1 is the string to be searched.

Str_expr2 is the string to try to find.

Str_expr3 is the replacement string.

Return Type: Character or Binary data

Example:

Select ‘sample data’, Replace (‘sample data’, ’sample’, ‘possible’)

Replicate Function

Repeats a character expression for a specified number of times.

Syntax: Replicate (char_expr, int_expr)

Char_expr is an alphanumeric expression of character data.

Int_expr is a positive whole number.

If int_expr is negative, a null startup is returned.

Return Type: VarChar

Example:

Select ‘Sample Data’, Replicate (‘Sample Data’, 2)

Select ‘Sample Data’, Replicate (‘Sample Data’, 2) from students where Len (studname) = 6

Select ‘The Student Name is’ + Replicate (‘.’, 5) + Upper (studname) from students where coursename like ‘MCA’

Reverse Function

It returns the reverse of a character expression

Syntax: Reverse (char_expr)

Char_expr is an expression of character data.

Char_expr can be constant variable or column or either character or binary data.

Return Type: VarChar

Example:

Select ‘Sample Data’, Reverse (‘Sample Data’)

Select ‘Sample Data’, Upper (Reverse (‘Sample Data’)) from students where Reverse (studname) like ‘HSITAS’

Space Function

It returns a string of repeated spaces.

Syntax: Space (int_expr)

Int_expr is a positive integer that indicates the number of spaces.

If the value is negative a null string is returned.

Return Type: Char

Example:

Select ‘Sample Data’ +Space (5) + ‘for testing’

Select Upper (studname) +’,’ +Space (3) + coursename from students.

Str Function

Syntax: Str (float_expr [, length [, decimal]])

Float_expr is an expression of approximate numeric (float) datatype with a decimal point.

Float_expr cannot be a function or sub-query

Length is the total length, including decimal point, sign, digits, and spaces.

Decimal is the number of places to the right of the decimal point.

Return Type: Char

Example:

Select Str (236.75,6,1)

Select Str (236.75,2,2) ‘2 decimals’

Select Str (7.237) Number

Select Str (7.237,5,3) ‘3 decimals’

Select studname, coursefees, Str (coursefees, 10, 2)

Stuff Function

It deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax: Stuff (char_expr, start, length, char_expr)

Char_expr is an expression of character data

Char_expr can be a constant, variable, or column of either character or binary data.

Start is an integer value that specifies the location to begin deletions and insertion.

If Start or length is negative null is returned.

Length is an integer that specifies the number of characters to delete.

Return Type: Char for Character data

Binary for supported binary data.

Example:

Select ‘Sample Data’ Stuff (‘Sample Data’, 7, 1, ‘Simple’)

Select ‘RANA’, Stuff (‘RANA’, 2, 2, ‘OMANI”)

Select studname, Stuff (studname, 3, 2, ‘nto’) from students where Len (studname) = 6

SubString Function

It returns part of a character string, binary, text or image expression

Syntax: SubString (expr, start, length)

Expr is a character string, binary string, text, image, a column or an expression

Appropriate function column cannot be used.

Start is an integer that specifies the beginning position length.

Length specifies the length of the substring

Return Types: text VarChar

Image VarBinary

ntext nvarchar

Example:

Select ‘sample’, SubString (‘Sample’, 1, 3)

Select studname from students where subString (studname, 2, 3) like ‘ati’

MATHEMATICAL FUNCTIONS

They are scalar functions used to perform calculations.

They depend upon the input values provided by the user, and return a numeric value.

Round Function

It returns a numeric expression, rounded to the specified length of precision

Syntax: Round (Num_expr, length [, function])

Num_expr is an expression of the exact numeric or approximate numeric datatype category

Bit datatype should not be used.

Length is the precision to which num_expr has to be rounded

Length should be tinyint, smallint or int.

When positive rounds to the number of decimal places specified by the length.

When negative rounds to the left side of the decimal point.

Function specifies the type of operation to perform, which can be tinyint, smallint or int.

Default value is 0 and value other than 0 truncates

Return Type: same type as num_expr

Example:

Round (546.67, -4) 0

Round (546.67, -1) 550.00

Select Round (123.9994, 3), Round (123.9995, 3)

Select Round (150.75, 0), Round (150.75, 0, 1)

Floor Function

It returns the largest integer length than or equal to the given numeric expression.

Syntax: Floor (num_expr)

Num_expr is an expression of the exact numeric or appropriate numeric datatype category.

Return Type: same type as num_expr

Example:

Floor (123.45) 123

Floor (-123.45) -124

Select Floor (123.45), Floor (-123.45)

Ceiling Function

It returns the smallest integer greater than or equal to the given numeric expression.

Syntax: Ceiling (num_expr)

Num_expr is an expression of the exact numeric or approximate numeric datatype category.

Return Type: same as num_expr

Example: Ceiling (123.45) 124.00

Ceiling (-123.45) -123.00

Select Ceiling (123.45), Ceiling (-123.45)

Square Function

It returns the square of the given expression

Syntax: Square (float_expr)

Float_expr is an expression of type float

Return Type: float

Select Square (2), square (2.45)

Sqrt Function

It returns the square root of the given expression

Syntax: Sqrt (float_expr)

Float_expr is an expression of type float

Return Type: float

Select Sqrt (4)

Select Sqrt (4.26)

Power Function

It returns the value of the given expression to the specified power.

Syntax: Power (num_expr, Y)

Num_expr is an expression of the exact type

Y is the power to which it has to be raised

Return Type: same type as Num_expr

Example:

Select Power (2,3), Power (-2,3)

DATE FUNCTIONS

Date Arithmetic:

Date +n Returns the Date after ‘n’ days

Date –n Returns the Date before ‘n’ days

Date +n/24 Returns the data after converting ‘n’ hours to days.

Date –n/24 Returns the data before converting ‘n’ hours to days.

GetDate Function

Returns the current system date and time in the Ms-SQL Server standard interval format for date, time values.

Syntax: GetDate ()

Return Types: Datetime

Example:

Select GetDate ()

Select GetDate ()+3, GetDate () –3

Select GetDate () +72/24

Select GetDate () + studDOB, GetDate () – studDOB from students

DateAdd Function

It returns a new datetime value based on adding an interval to the specified date.

Syntax: DateAdd (datepart, number, date)

Datepart is the parameter that specifies on which part of the date return a new value.

Number is the value used to increment the datepart

Date is an expression that returns a datetime or small datetime value.

Return Type: Datetime

Date part Abbreviations

Year yy, yyyy

Quarter qq, q

Month mm, m

Day of year dy, y

Day dd, y

Week wk, ww

Hour hh

Minute mi, n

Second ss, s

Millisecond ms

Select DateAdd (yyyy, 1, GetDate ())

Select DateAdd (mm, 3, GetDate ())

Select DateAdd (dd, 7, GetDate ())

Select studDOB [Actual DOB], DateAdd (mm, 2, studDOB) [Adjust DOB] from students

DateDiff Function

It returns the number of date and time boundaries crossed between two specified dates.

Syntax: DateDiff (datepart, startdate, enddate)

Datepart specifies on which part of the date to calculate the difference.

Startdate is the beginning date for the calculation

Enddate is the date for calculations

Select DateDiff (yyyy, studdob, GetDate ()) from students

Select Studdob [Actual DOB] DateDiff (yyyy, studDOB, GetDate ()) age from students

Select Studdob [Actual DOB] DateDiff (yyyy, studDOB, GetDate ()) age from students where DateDiff (yyyy, studDOB, GetDate ()) <20

DateName Function

It returns the character string represents the specified datepart of the specified date.

Syntax: DateName (datepart, date)

Datepart is the parameter that specified the pert of the date to return.

Return Type: nVarChar

Select GetDate (). DateName (year, GetDate ()), DateName (yyyy, GetName ())

Select GetDate (). DateName (month, GetDate ()), DateName (dw, GetDate ())

Select GetDate (). DateName (weekday, GetDate ()), DateName (dw, GetDate ())

Select studDOB from students where DateName (month, studdob) = ‘November’

Select studDOB, DateName (dw, studDOB) from students where DateName (weekday, studDOB) = ‘Thursday’

DatePart Function

It returns an integer representing the specified datepart of the specified date.

Syntax: DatePart (datepart, date)

Datepart is the parameter that specifies the part of the date to return.

Return Type: int

Select DatePart (month, GetDate ())

Select studDOB, DatePart (month, studDOB) from students where DatePart (month, studDOB) = 11

Day Function

It represents an integer representing the day datepart of the specified data.

Syntax: Day (date)

Date is an expression of type datetime or smalldatetime.

Return Type: int

Select Day (‘03/12/1998’)

Select studDOB, day (studDOB) from students

Month Function

It represents an integer that represents the monthpart of a specified date.

Syntax: Month (date)

Date is an expression returning a datetime n smalldatetime value.

Return Type: int

Select month (‘03/12/1998’)

Select studDOB, month (studDOB) from students where month (studDOB) = 11

Year Function

It returns integer that represents the year part of a specified Date.

Syntax: Year (date)

Date is an expression of type datetime n smalldatetime

Return Type: int

Select Year (‘03/12/1998’)

Select studDOB, Year (studDOB) from students where Year (studDOB) = 1995

Group By Clause

It divides a table into groups.

Groups can consist of column names or results n computed columns.

It is used with SELECT statement to

(1). Specify the groups into which output rows are to be placed.

(2). Calculate a summary value for each group, when aggregate functions are included.

Things To Note

Either each column in any non-Applegate expression in the select list should be included in the Group By list.

The Group By expression must match exactly the select list expression.

Group By Components

One or more Applegate-free expression, which are usually references to the grouping columns.

Optionally, the all keyword to specify that all groups produced by the Group By clause are returned.

Cube or Rollup

Having clause is used with the Group By clause

Grouping Information Upon Columns

Select Coursename from students Group By coursename.

Select coursename, coursefees from students Group By Coiursefees, courcename.

Select studname, coursename from students Group By studname, coursename.

Note: When aggregate functions are not implemented then all the columns in the select list should be declared in the Group By clause.

Group By With Where Clause

The where clause can be implemented upon the Group By clause to eliminate particular Group value.

Select coursename, coursefees from students where coursename <> ‘M.Sc’ Group By coursename, coursefees.

Select datePart (yy, studDOB), studDOB from students Where DatePart (yy, studDOB) <> 1995 Group By DatePart (yy, studDOB), studDOB

Aggregate Functions

These functions perform a calculation on a set of values and return a single value.

Except Count Function all Applegate functions ignore NULL values.

These functions are generally used with the Group By clause of the select statement.

These functions are allowed as expressions only in

(1). The select list of the Select statement, either a subquery or an outer query.

(2). A compute or compute By clause.

(3). A Having clause.

Average function

It returns the average of the values in a group.

Null values are ignored.

Syntax: Avg ([All | Distinct] expr)

ALL: Applies the function to all values

ALL is the default

Distinct: Specifies that AVG be performed only on each unique instance of a value.

Expr: it is an expression of the exact numeric or approximate numeric datatype category.

Return Type:

Int int

Decimal decimal

Money & Smallmoney money

Float & real float

Example:

Select coursename, AVG (coursefees) from students Group By coursename

Select coursename, AVG (Distinct coursefees) from students where coursename in (‘MBA’, ‘MCA’) Group By coursename

Sum Function

It is used to return the sum of all the values or only the distinct values in the expression.

It can be used only with numeric values.

NULL values are ignored.

Syntax: Sum ([ALL | Distinct] expr)

Return Type:

Int int

Decimal decimal

Money & Smallmoney money

Float & real float

Example:

Select coursename, Sum (coursefees) from students Group By coursename

Select coursename, Sum (coursefees), Sum (Distinct coursefees) from students where coursename Like ‘_ _A’ Group By coursename

Select coursename, Sum (coursefees) [Actual fees], Sum (coursefees) + 2000 [with sports] from students where coursename Like ‘_ _A’ Group By coursename

Maximum Function

It returns the Maximum value in the expression

Ignores Null’s

Syntax: Max ([ALL | Distinct] expr)

Return Type: same as expr

Example:

Select Max (coursefees) from students

Select Max (studname) from students

Select Max (studDOB), Max (DatePart (yy, studDOB)) from students where DatePart (yy, studDOB) <> 1995

Select coursename, Max (coursefees) from students Group By coursename

Select coursename, Max (sum (coursefees)) from students Group By coursename.

Minimum Function

It returns the minimum value in the expression.

It ignores Null’s.

Syntax:

Select Min (coursename) from students.

Select Min (studname) from students.

Select Min (studDOB), Min (DatePart (yy, studDOB)) from students where DatePart (yy, studDOB) <> 1975

Select coursename, Min (coursefees) from students Group By coursename.

Count Function

It returns number of items in a group.

Syntax: Count ({[ALL | Distinct] expr]|*})

* - Specifies that all rows should be counted to return the total number of rows in a table.

Cannot take parameters and cannot be used Distinct

It doesn’t eliminate duplicate and include Null’s

Return Type: int

Example:

Select Count (*) from students.

Select Count (studname), Count (Distinct Studname) from students.

Select Count (coursefees), Count (Distinct coursefees) from students.

Select Count (coursefees), Avg (coursefees) from students.

Having Clause

It specifies a search conditions for a group or an Applegate

Having can be used only with the Select statement

It is used along with the ‘Group By’ clause

If Group By is not implemented, then it behaves like Where clause.

Example:

Select Sum (coursefees) from students Group By coursefees Having Avg (coursefees) < 18000

Select Sum (coursefees) from students Group By coursefees Having Count (Distinct coursename) = 2

PLANNING THE TABLE DESIGNING

Planning a table requires

Identifying valid values for a column

Deciding how to enforce the integrity of the data in the column

Data Integrity

It ensures the quality of the data in the database

Categories of Data Integrity

1). Entity Integrity

It defines a row as a unique entity for a particular table.

This enforces the integrity of the identifier column(s) or the primary key of a table.

It can be enforced through

Indexes

Unique Constraints

Primary key Constraints

Identity properties

2). Domain Integrity

It defines the validity of entries for a given column

It is enforces through

Restricting type Data Types

Formula Check constraint and rules

Range of possible values Foreign key, Constraint, Checkconstraint,

Default, Notnull, Rules.

3). Referential Integrity

It preserves the defined relationships between tables when records are entered or deleted.

It ensures that key values are constraint across tables.

It enforces through

Relationship between foreign key and primary key.

Foreign keys and unique keys.

It prevents

Adding a record to a related table of there is no associated record in primary table.

Changing values is an primary key table that result in orphaned records in a related table.

Example:

Create Table Students (

StudID int

Constraint PK_StudiD Primary Key,

Studname VarChar (50),

StudDOB smalldatetime,

. …….

)

Create Table StudentsLIB

(

LibID int,

BookID int,

Bookname VarChar (50),

……….

Constraint PKCK_Comp_LibID_BookID primary key (LibID, BookID)

)

Deleting records from a primary table if there ate matching related records.

4). User-Defined Integrity

It allows to define specific business rules which do not fall into one of the other integrity categories.

ENFORCEMENT STANDRDS IN MS-SQL SERVER 2000

1). Primary Key Constraint

It has a column or combination of columns whose values uniquely identify each row in the table.

A single can have only one Primary Key Constraint, and cannot contain Nulls.

The Constraint enforces data uniqueness by creating a Unique Index for Primary Key columns.

Rules

Should not contain Null values.

Should not contain Duplicate values.

2). Foreign Key Constraints

It is a column or combination of columns used to establish and enforce a link between the data in two tables.
The Link is created by adding the column or columns that hold one table primary key values to the other table.
The Foreign key can defined to reference the columns of a Unique Constraint also.
A Foreign key constraint to the data in the Primary Key table.
Changes to Primary Key Constraints are checked with Foreign Key Constraints in related tables.
Foreign Key columns are after used in Joins.
A single can have multiple Foreign Key Constraints.
It is not possible to change the length of a column defined with a foreign key constraint.

Points to Check

When a value other than Null is evaluated into the column of a Foreign Key constraint, the value must exist in the referenced column.
Foreign Key Constraints are applied to the preceding columns are specified.
Foreign Key Constraints can reference only tables within the same database on the same server.
Foreign Key Constraint can reference another column in the same table.
The references clause of a column-level Foreign Key Constraint can list only one reference column.
The reference column must have the same data type as the column on which the constraint is defined.
The Reference clause of a table level Foreign Key Constraint must have the same number of refernce columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.
Cascade may not be used if a column of type timestamp is part of either the foreign key on the referenced key.
Cascade can No Action can be combined on tables that have referential relationships with each other.
A table can contain a maximum of 253 Foreign Key constraints.
Foreign Key Constraints cannot enforce on temporary tables.
A table can refernce a maximum of 253 different tables in this Foreign Key Constraints.
Foreign Key Constraints can refernce only columns in Primary Key or Unique Constraints in the referenced table or in a Unique Index on a referenced table.
A Foreign Key can be single column key or multicolumn keys.
When declared on a single column only References clause is used.
When the constraint is named or declared table level than the Foreign Key clause along with References should be used.

Example:

Create Table StudentExam

(

ExamID int

Constraint PK_ExamID Primary Key,

ExamName VarChar (20),

ExamDate Smalldatetime,

……….

StudID int References Students (studID),

……….

)

Create Table StudentExam

(

ExamID int

Construct PK_Exam Primary Key

ExamName VarChar (30),

ExamDate smalldatetime,

…………

StudID int Foreign Key References Students (Students (StudID),

…………

)

Create Table StudentExam

(

ExamID int

Construct PK_ExamID Primary Key,

ExamName VarChar (30),

ExamDate smalldatetime,

………

StudID int Construct StudID_fk Foreign Key References Students (StudID),

……….

)

Create Table StudentExam

(

ExamID int

Construct PK_ExamID Primary Key,

ExamName VarChar (30),

ExamDate smalldatetime,

………

StudID int Construct StudID_fk Foreign Key (StudID) References Students

(StudID),

…………….

)

Foreign Key Construct On Multiple Columns.

Create Table StudBooksRet

(

Book_ID int,

Req_ID int,

Book_Title VarChar (30),

………

RetDate smalldatetime,

………

Construct fk_Books_ret

Foreign Key (Book_ID, Req_ID, Book_Title) References StudBookReq (BookReq (Book_ID, Req_ID, Book_Title)

)

Self Referential

Create Table Students

(

StudID int Construct PK_StudID Primary Key,

StudName VarChar (30),

……….

LeaderID int Constraint fk_Leader Foreign Key (LeaderID) References

dbo. Students (StudID),

………..

)

Unique Constraint

It ensures that no duplicate values are entered in specific columns, which do not participate in a primary key.
It can be used to enforce

o A column or combination of columns, that is not a Primary Key.

o A column that allows Null values.

Multiple Unique Constraints can be defined on a table.
A Unique Constraint can be referenced by a Foreign Key Constraint.
SQL Server automatically creates a Unique index to enforce the Uniqueness requirement of the Unique Constraint.
If Clustered Or nonClustered is not specified for a Unique constraint, nonClustered is used by default.
If can be created when the table is created, n can be added upon an existing table.

Example:

Create Table BookMaster

(

BookID int

Constraint PK_BookID Primary Key

Titlename Varchar (30) Constraint TN_Unique,

………..

……….

)

Create Table Departments

(

DeptID int

Constraint PK_DeptID Primary Key,

DeptName Varchar (30),

Location Varchar (30),

Constraint Dname_Loc_UNQ Unique (DeptName, Location)

)

Check Constraints

They enforce domain integrity by limiting the values that are accepted by a column.
It controls the values that are placed in a column.
It determines the valid values from a logical expression that is not based on data another column.
Check Constraint can be created with any logical expressions that returns TRUE or FALSE, based on the logical operator.
It is possible to apply multiple Check Constraints to a single columns, and are evaluated in the order in which created.
A single Check Constraint can be applied upon multiple column by creating at the table level.
When a Check Constraint is added to an existing table, it can be applied to new data only or to an existing data as well.
The logical conditions can include multiple logical expressions combined with AND and OR.
The search condition must evaluate to a Boolean expression and cannot reference another table.

Example:

Create Table StudentExam

(

ExamID int

Construct PK_ExamID Primary Key,

ExamName Varchar (30)

Construct CHK_UPPER

Check (ExamName = Upper (ExamName))

……….

StudID int Foreign Key References Students (StudID),

………..

)

Create Table Student

(

StudID int Constraint PK_StudID Primary Key,

StudName VarChar (30) Constraint Chk_Upper Check (StudNmae

Upper (StudName)),

StudName Smalldatetime Constraint Chk_StudDOB Check (StudDOB

< GetDate ())

)

Create Table Students

(

StudID int Constraint PK_StudID Primary Key,

StudName VarChar (30) Constraint Chk_Upper Check (StudName

= Upper (StudName)),

StudDOA Smalldatetime Constraint Chk_StudDOA Check (StudDOA

= GetDate ()),

Coursejoined VarChar (10) Constraint Chk_CourseJoined

Check (CourseJoined in (‘MCA’, ‘MBA’, ‘BCA’))

AdminFees Smallmoney Constraint Chk_AdminFees Check (AdminFees >=

10000 And AdminFees <=30000)

)

Create Table Course

(

CourseID Char (6) Constraint PK_ CourseID Primary Key

Constraint Chk_CourseID Check (CourseID Like ‘[A-Z] [A-Z] [A-Z] [1-9]

[0-9] [0-9]’),

CourseName VarChar (5) Constraint Chk_Up_CourseName Check (CourseName = Upper (CourseName) And CourseName in (‘MCA’, ‘MBA’, ‘BCA’)),

CourseFees smallmoney Constraint Chk_CourseFees Check (CourseFees >=10000 And CourseFees <=40000)

)

Default Constraints

It enables to define that will be supplied for a column whenever a uses fails to enter value.
While defining the delimiters should be specified correctly.
A single can have only one Default definition.
A Default definition can contain

o Constraint Values

o Functions

o SQL-92 niladic Functions

o Null

SQL – 92 niladic Functions

CURRENT_TIMESTAMP -----à Current Date and Time.

Current_user -----à Name of user performing Insert.

Session_User ----à Name of the user performing Insert.

System_User ----à Name of the user performing Insert.

User -----à Name of the user performing Insert.

Constraint Expr in a Default definition cannot refer to another column in the table, or to other tables, views or stored procedures.
Default definitions cannot be created on columns with a timestamp datatype or columns with an Identity Property.
Default definitions cannot be created for columns with user defined datatype is found to a default object.

Example:

Create Table CoutseMaster

(

CourseID int Constraint PK_CourseID Primary Key,

CourseName VarChar (30) Default ‘New Course – Title Not Decided’,

CourseInceptionDate smalldatetime Default (GetDate ())

)

NotNull | Null

These are Keywords which determine whether the Null Values are allowed in the column.
Null | NotNull is strictly not a constraint but can be specified in the same manner.

Example:

Create Table CourseMaster

(

CourseID int Constraint PK_CourseID Primary Key,

CourseName Varchar (30) NN_CourseName NotNull

Constraint Chk_CourseName Check (CourseName IN (‘MCA’, ‘MBA’)),

CourseInceptionDate smalldatetime Constraint NN_Cour_Incep_Date

NotNull Default (GetDate ())

)

SQL> Alter Table Table_Name Drop Constraint Const_Name

SQL> Alter Table TableName Add Constraint Constraint_Name, Constraint_type (Filed name)

SQL> Alter Table Dept Add Constraint PK_Code Foreign key (e_code) References Employees (e_code)

Missing Papers 135 to 155

Views With Check Option

The With CHECK option enforces all date modification statements executed against the view to adhere to the criteria set with in the SELECT statement defining the view.
When with CHECK option is implemented, the user can Insert, Update or Delete only those values that meet the conditions are stated.

Example:

Create View Companies_Info As Select CompanyName, CompanyAddress, CompDesc From CompaniesMaster Where Company_ID = 1234 With Check option

Create View SalesDept As select Empno, Ename, Sal From Employee Where DeptNo = 20 with Check option.

HIDING VIEW DEFINATION

The system table syscomments contains the text of the CREATE VIEW statement.
The view text can be encrypted by specifying the WITH ENCRIPTION option.
Encryption ensures that the data remains secure by keeping the information hidden from everyone.
Before encrypting a view, the view definition must be saved to a file.
To Unencrypt the text of a view, the view must be dropped and recreated or alter the view.

Example:

Create View Emp_Dept with Encryption As Select Ename, Dname,

Sal [Basic Salary], Sal*12 [Annual Salary] From Employee E, Department D

Where E.DeptNo = D. DeptNo

Altering Views

· A view can be altered using the ALTER VIEW statement.

· The view alternation does not affect the department stored procedures or triggers.

Syntax:

ALTER VIEW View_Name [(Column [,…..])] [With Encryption] As

Select statement [With Check option]

Example:

ALTER VIEW Companies_Info As Select CompanyName, CompanyAddress,

CompDesc, CompanyInfo From CompaniesMaster

Dropping Views

Syntax: Drop View ViewName

Example: Drop View Companiesinfo

INDEXES

Index in SQL-Server assists speeding up the retrieval of rows from table.
Index quickness the process of Selects, Updates and Deletes.
An Index is built through keys from one or more columns in the table.
Indexes require storage space in addition to the space already used by the table.
The Query Optimizer depends on the indexes columns for its functionality.

Data Access Style in SQL Server 2000

SQL Server accesses the data by

1. Table scan

2. Using Indexes.

1. Table scan

The method involves in scanning all the data paper of the table.
It starts from the beginning of the table.
It scans from page to page through all rows in the table.
It extracts the rows that meet the criteria of the Query.
Indexes created on a column, containing a lot of duplicate data have only few benefits associated with it.

Guidelines to Create Indexes

The usefulness of the Index is directly related to the percentage of rows that returned from a query.
Creating Indexes on the frequently searched columns is more useful and efficient.
It is always best to create Indexes on

Primary Key columns.
Foreign keys columns.
Columns frequently used in joining table.
Columns that are searched for a range of key values.
Columns that are accessed in sorted order.

The column which do not need indexes are

Columns that are infrequently used.
Columns that contain lots of duplicate values.
Columns that contain Bitmaps, Text or Images.

Using Indexes

It traverses the index structure to find rows that the query requests.
It extracts only the rows that meet the query criteria.

Need for Indexes

Indexes faster the process of queries

a) That Join tables.

b) That perform sorting.

c) That perform grouping operations.

Uniqueness of a column value is maintained by indexes if it is enforced when creating index.
Indexes always maintain the data in the ascending sort order.
Indexes are created on columns with a high degree of selection.

Factors Influencing Indexes

When data is modified on an indexed column then the associated indexes are updated, then creating an overhead.
Maintenance of indexes require and resources.

Types of Indexes

· Clustered Indexes

· Non-Clustered Indexes.

Clustered Index

Clustered Index is the one in which the order of the values in the index is the same as the order of the data sorted in the table.
The index is created in the actual data pages.
Only one Cluster Index is accepted upon a table.
The data is physically stored on a data page in ascending order.

Using Clustered Indexes

Clustered indexes are used in the column, which is most frequently used for ranges of key values or are accessed in stored order.
Pointes To Ponder

A table can have only one clustered index.
The physical row order of the table and the rows are both the same.
It should be created before Non Clustered Index.

Uniqueness of key value is maintained capability with the Unique Keyword, or implicitly with an internal unique identifier.
The size of the clustered index is about five percent of the table size.
When a row is deleted the space is reclaimed and is available for a new row.
When an index is created, SQL Server temporality uses disk space from the current database.
The space is automatically de-allocated after the process of creating index of over.
The maximum length of all the columns they constitute to the clustered index is 900 bytes.

Non Clustered Index

In a Non-Clustered Index the data is stored in one place and the index is stored in another place.
Non-Clustered index built on top of heap.
The index acts as pointers to the storage location of the indexed item in the data.
A single table can have more than one non-clustered index.
Non-Cluttered Index type is the default Index crated by the SQL Server.
A Non-Clustered Index is automatically built, when an existing clustered index is dropped, or a when a clustered index is created.
The order of Non-Clustered is not the same as the physical order of the table.
The maximum number of clustered indexes for a table is 249.
Accessing data using a non-clustered index is slower than accessing data using clustered indexes.

UNIQUE INDEX

A Unique Index ensures that the data is an indexed column is Unique.

Guidelines

It is created automatically upon the column that are declared as Primary Key or Unique constraints.
When an Index is created, the table is checked for duplicate values.
The duplication values are checked each time when the table is updated.
Unique index can be created only on columns in which entity integrity can be enforced.

Syntax:

Create [Unique] [Clustered | Non-Clustered]

Index Index_name On Table (column [,…..n]) [with] [Pad_Index]

[[,] FillFactor]

[[,] Ignore_Dup_Key]

[[,] Statistics_Norecompute]

] [ON file group]

Fill Factor

The option is used to optimize the performance of INSERT and UPDATE statements.
It specifies value in terms of percentage to indicate how full SQL Server should make the level of each index page during the creation of index.
The value of the FillFactor is stored along with the index in sysindexes.
The FillFactor can range from 1 to 100 percent, and the default value is 0.
The FillFactor of 100 is used only in a read-only table.

PAD_INDEX

The option specifies the percentage to which non-leaf level index pages are filled.
The option can be used only when FillFactor is specified as the PAD_INDEX percentage value is determined by the value specified in FillFactor.

Examples:

Non-Clustered Index

Create Non-Clustered Index DeptIDX on Employee (Deptno)

Clustered Index

Create Clustered Index EmpIDX in Employee (EmpNo)

UNIQUE INDEX

Create Unique Index EmpNameFnameIdx on Employee (Ename, Fname)

FillFactor and PAD_INDEX

Create Index EmployeeIDX on Employee with PAD_INDEX, FillFactor = 12

Indexes Maintenance

Index maintenance is necessary to ensure optional performance.

DBCC Showcontig Statement

It displays the fragmentation information for the data and indexes of the specified table.

Syntax:

DBCC Showcontig

[

(table_ID [,index_ID])

]

Information on Statistics

Extent Switches: Number of times the statement left an extent while transferring the pages of extent.
Average Pages per extent: specifies the number of pages per extent in the page charm.
Scan density: specifies fragmentation status 100% -àcontiguous

<100% -à fragments

Average Bytes free per page: the average number of free bytes on the scanned pages. The higher the number, the less the full pages.
Average Page Density (Full): it specifies the value of the fullness of a page.

Drop_Existing option

· This option changes the characteristics of an index or to rebuild indexes without having to drop the index recreate it.

· It enables to modify indexes created with Primary Key or Unique constraints.

Example:

Create Unique Index EmployeeIDX on Employee (Ename, fname) with Drop_Existing.

Update Command

It is used to change the existing data in a table.

Syntax:

UPDATE

{

Table_Name With (<table_hint_limited> [….n]) | View_Name |

Rowset_function_limited

}

SET

{

Column_Name = {expr | Deafault | Null}

{FROM <Table>} [Where <search_condition]

}

Table_Name is the table to be Updated.
With specifies one or more table hints that are allows for a target table.
View_Name it is the name of the view to update.
Rowset_Function_Limited it is either the openquery or openrowset function.
Set specifies the list of column or variable names to be updated.
Column_Name is the column that contains the data to be changed.

UPDATE Authors

Set Author_Au_Fname = ‘Annie’ where Au_Fname = ‘Anne’

UPDATE Titles

Set ytd_sales = t.ytd_sales + s.qty fro Titles t, sales S

Where t. Title_ID = s. Title_ID and s.ord_date = (select Max (sales. Old_date)

from sales.

UPDATE Publishers

Set city = ‘Atlanta’, state = ‘GA’

UPDATE Publishers

Set Pub_Name = Null

UPDATE Titles

Set Price = Price * 2

UPDATE Authors

Set State = ‘PC’, city = ‘Bay City’ where State = ‘CA’ and city = ‘Octaland’

UPDATE Titles

Set ytd_sales = (select sum (qty) from sales where sales. Title_ID =

Title. Title_ID and Sales.Ord_Date in (select Max (old_date) from sales) from Titles, Sales

UPDATE Authors

Set State = ‘xx’ from (select top * from Authors ORDER BY Au_Name) As t1

Where Author. Au_ID = t1. Au_ID

UPDATE Titles

Set Price = Price * 2 where Pub_ID in (select pub_ID from publishers where

Pub_Name = ‘New Moon Books’)

UPDATE Commands

It is used to remove rows from a table.

Syntax:

DELETE

[From]

{Table_Name With (<Table_Hint>)

| View_Name

| rowset_Finction

}

[Fro {table_source] [where {search_condition>}]

Example:

DELETE Employees

DELETE From Employees where Deptno = 10

DELETE From TitleAuthor where Title_ID in (select Title_ID From Titles where

Title Like ‘%computers%’)

DELETE Authors Fro (select Top 10 * from Authors) As t1

Where Authors.Au_ID = t1. Au_ID

ALTER TABLE

It modifies a table definition by altering, adding and dropping column and constraints.
It can be used for disabling or enabling constraint or Triggers.

Syntax:

ALTER Table TableName [Alter column column_Name] | ADD [<column_Name>] |

Drop [Constraint] Constraint_Name

ALTER A TABLE TO ADD NEW COLUMN

Create table Doc_Exa (Sample int)

Alter Table Doc_Exa ADD Sample_Name Varchar (30) Null

ALTER A TABLE TO DROP A COLUMN

Create Table Sample (SampleID int, SampleName Varchar (30) Null)

Alter Table Sample Drop SampleName

Alter A Table to Add A Column with constraint

Create Table Samplesys (SampleID int)

Alter Table Samplesys Add SampleName Varchar (30) Null Constraint

SamNameUNQ Unique

TRUNCATE TABLE COMMAND

It removes all rows from a table without logging the individual row deletes.

Syntax:

TRUNCATE Table TableName

TRUNCATE Table Employees

DROP Table Command

It removes a table definition and all date, indexes, triggers, constraints and permissions.

Syntax:

DROP Table Employee

DROP Table Pubs.dbo.Authors

GRANT COMMAND

It is used to Grant Permission upon objects.

Syntax:

Grant {All | Statement} To security_Account

Grant Create Database, Create Table To Sampath, Satish

Grant Select on Employee To Rajkumar

REVOKE COMMAND

It removes a previously granted or denied permission from a user.

Syntax:

REVOKE {All | Statement} From Security_Account.

REVOKE Create Database from Sampath.

REVOKE DELETE, UPDATE on Employee From Satish, Rajkumar.

COMMIT Command

It makes the end of successful implicit and User-defined Transaction.

Syntax: Commit Tran Transaction_Value

Commit

Commit Tran Set_span1.

Oracle Bill of Materials User's Guide

More....>>

God Blessings TO All the People

Search

Oracle Materials

DATA TYPE CONVERTION IN MS-SQL SERVER

CAST and CONVERT

RESTRICTING THE ROWS

Where Clause

Arguments Description

FILTERING INFORMATION FOR SPECIFIC VALUE IN A FIELD

APPLYING SEARCH CONDITIONS

SEARCH CONDITIONS ON ONE COLUMN OR CONDITION

AND Condition

MUTIPLE SEARCH CONDITIONS FOR MULTIPLE COLUMNS

OR Condition

AND Condition

Or and And Combinations

T-SQL OPERATORS

THINGS TO NOTE

IN

IS [NOT] NULL

Things to Note

LIKE

THINGS TO NOTE

Some Illustrations

USING DISTRICT IN SELECT

ORDER BY CLAUSE

T-SQL BUILT-IN FUNCTIONS

Deterministic Functions

ROWSET FUNCTIONS

AGGREGATE FUNCTIONS

SCALAR FUNCTIONS

Categories of Scalar Functions

Text and Image Functions

STRING FUNCTIONS

ASCII Function

Char Function

CharIndex Function

Left Function

Right Function

LTrim Function

RTrim Function

Len Function

Lower Function

Upper Function

Patindex Function

Replace Function

Replicate Function

Reverse Function

Space Function

Str Function

Stuff Function

SubString Function

MATHEMATICAL FUNCTIONS

Round Function

Floor Function

Ceiling Function

Square Function

Sqrt Function

Power Function

DATE FUNCTIONS

GetDate Function

DateAdd Function

Date part Abbreviations

DateDiff Function

DateName Function

DatePart Function

Day Function

Month Function

Year Function

Group By Clause

Things To Note

Group By Components

Grouping Information Upon Columns

Group By With Where Clause

Aggregate Functions

Average function

Sum Function

Maximum Function

Minimum Function

Count Function