MS SQL Server

By definition, FLOAT(n) should store the mantissa of the floating number in n bits. For example, FLOAT(16) should have a precision one-byte less than FLOAT(24). However, SQL Server 2005 only supports two precisions for floating numbers:

* Single Precision: FLOAT(24) or REAL, stored in 4 bytes, giving about 7 digits of precision, covering all types from FLOAT(1) to FLOAT(24),

* Double Precision: FLOAT(53), stored in 8 bytes, giving about 15 digits of precision, covering all types from FLOAT(25) to FLOAT(53).

The tutorial exercise below shows you some different precision and rounding examples:

-- FLOAT(1) works like FLOAT(24)

DECLARE @x FLOAT(1)

SET @x = 9.234567890E+10;

SELECT @x;

GO

9.234568E+10 -- 7 digits precision

-- Single precision with rounding

DECLARE @x REAL; -- FLOAT(24)

SET @x = 9.234567890E+10;

SELECT @x;

GO

9.234568E+10 -- 7 digits precision

-- FLOAT(25) works like FLOAT(53)

DECLARE @x FLOAT(25);

SET @x = 9.2345678901234567890E+100;

SELECT @x;

GO

9.23456789012346E+100 -- 15 digits precision

-- Double precision with rounding

DECLARE @x FLOAT(53);

SET @x = 9.2345678901234567890E+100;

SELECT @x;

GO

9.23456789012346E+100 -- 15 digits precision

If you enter a floating number that is too big or too small for the FLOAT data type, SQL Server 2005 will behave as:

* FLOAT(24) Underflow: If a floating number is too small for FLOAT(24), it will be stored as 0 without any warning.

* FLOAT(24) Overflow: If a floating number is too big for FLOAT(24), you will get an arithmetic overflow error.

* FLOAT(53) Underflow: If a floating number is too small for FLOAT(53), it will be stored as 0 with a warning.

* FLOAT(53) Overflow: If a floating number is too big for FLOAT(53), you will get a value-out-of-range error.

The tutorial exercise below some good underflow and overflow examples:

-- Single precision underflow without warning

DECLARE @x REAL; -- FLOAT(24)

SET @x = 9.234568E-39;

SELECT @x;

GO

0

-- Single precision overflow error

DECLARE @x REAL; -- FLOAT(24)

SET @x = 9.234568E+39;

GO

Msg 232, Level 16, State 2, Line 2

Arithmetic overflow error for type real,

value = 9234568000000000400000000000000000000000.000000.

-- Double precision underflow with warning

DECLARE @x FLOAT(53);

SET @x = 9.23456789012346E-309

SELECT @x;

GO

Warning: the floating point value '9.23456789012346E-309'

is too small. It will be interpreted as 0.

0

-- Double precision overflow error

Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters: p (precision) and s (scale):

* Maximum number of digits of the integer part (digits before the decimal point) is defined as p-s. If this limit is passed, SQL Server will give you an arithmetic overflow error.

* Maximum number of digits of the decimal part (digits after the decimal point) is defined as s. If this limit is passed, SQL Server will perform a round operation.

The tutorial exercise below gives an example of arithmetic overflow errors and rounding operations.

-- Exact numeric value

DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)

SET @x = 1234567.12;

SELECT @x;

GO

1234567.12

-- Overflow error: p-s limit passed

DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)

SET @x = 123456789.12;

GO

Msg 8115, Level 16, State 8, Line 2

Arithmetic overflow error converting numeric to data type

numeric.

-- Rounding on extra decimal digits: s limit passed

DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)

SET @x = 1234567.12345;

SELECT @x;

GO

1234567.12

If you are entering an INT data type literal with representing an integer value too big for INT data type to store, the SQL Server will give you an arithmetic overflow error. The same error will happen on BIGINT, INT, SMALLINT, and TINYINT data types. Remember that INT data types uses 4 bytes to an integer value between -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The tutorial exercise below gives an example of arithmetic overflow errors.

-- INT value in the range

DECLARE @x INT;

SET @x = 2147483647;

SELECT @x;

GO

2147483647

-- INT value overflow

DECLARE @x INT;

SET @x = 2147483648;

Msg 8115, Level 16, State 2, Line 2

Arithmetic overflow error converting expression to data type int.

A numeric expression is a combination of identifiers, values, and operators that SQL Server 2005 can evaluate to obtain a numeric value. A simple expression could be a constant, a function, a column name, a variable, or a subquery without any operators. Complex expressions can be constructed by joining other expressions with operators. The following tutorial exercise shows you some expression examples:

DECLARE @site VARCHAR(40);

SET @site = 'GlobalGuideLine.com';

SELECT 'Welcome'; -- Expression: constant

SELECT @site; -- Expression: variable

SELECT GETDATE(); -- Expression: function

SELECT 'Welcome to '+@site; -- Expression with an operator

GO

Welcome

GlobalGuideLine.com

2007-05-19 18:42:09.077

Welcome to GlobalGuideLine.com

DECLARE @rate NUMERIC(5,2);

DECLARE @deposit MONEY;

DECLARE @value MONEY;

SET @rate = 5.25;

SET @deposit = 4000.00;

-- Expression with multiple operators.

SET @value = @deposit*(1.0+@rate/100)*(1.0+@rate/100);

PRINT @value;

GO

4431.03

If you want to convert the data type of a numeric expression to a new data type, you can use the CAST(expression AS data_type) function. The tutorial exercise below shows you how to use the CAST() function:

-- FLOAT converted to NUMERIC by CAST()

DECLARE @pi FLOAT(24);

SET @pi = 3.141592E+00;

SELECT CAST(@pi AS NUMERIC(5,2));

GO

3.14

-- FLOAT converted to NUMERIC by CAST()

DECLARE @x FLOAT(24);

SET @x = 12345.12E+00;

SELECT CAST(@x AS NUMERIC(10,5));

GO

12345.12305

-- FLOAT converted to INT by CAST()

DECLARE @x FLOAT(24);

SET @x = 12345.12E+00;

SELECT CAST(@x AS INT);

GO

12345

There are 4 ways to convert a numeric expression from one data type to another data type:

* Implicit conversion by arithmetic operations - When arithmetic operations are performed on expressions of different data types, implicit data type conversion will be performed before the arithmetic operation on the expression with a lower data type rank.

* Implicit conversion by assignment operations - When a numeric expression is assigned to variable, column, or parameter of different data type, the expression will be converted to match the data type of the variable, column, or parameter.

* Explicit conversion using the CAST() function - A numeric expression can be explicitly converted to different data type using the CAST(expression AS data_type) function.

* Explicit conversion using the CONVERT() function - A numeric expression can be explicitly converted to different data type using the CONVERT(data_type, expression) function.

Some numeric data type conversion examples are provided in the tutorial exercise below:

-- Implicit conversion by an arithmetic operation

-- INT converted to NUMERIC

DECLARE @d NUMERIC(9,6);

SET @d = 1.0;

SELECT @d/3;

GO

0.33333333

-- Implicit conversion by an assignment operation

-- NUMERIC converted to INT

DECLARE @i INT;

DECLARE @d NUMERIC(9,3);

SET @d = 123.456;

SET @i = @d;

SE

If you want to convert the data type of a numeric expression to a new data type, you can use the CONVERT(data_type, expression) function. The tutorial exercise below shows you how to use the CONVERT() function:

-- FLOAT converted to NUMERIC by CONVERT()

DECLARE @pi FLOAT(24);

SET @pi = 3.141592E+00;

SELECT CONVERT(NUMERIC(5,2), @pi);

GO

3.14

-- FLOAT converted to NUMERIC by CONVERT()

DECLARE @x FLOAT(24);

SET @x = 12345.12E+00;

SELECT CONVERT(NUMERIC(10,5), @x);

GO

12345.12012

-- FLOAT converted to INT by CONVERT()

DECLARE @x FLOAT(24);

SET @x = 12345.12E+00;

SELECT CONVERT(INT, @x);

GO

12345

An assignment operation is used to assign an expression to a variable, a column, or a parameter. If the data type of the expression does not match the data type of the receiving variable, column, or parameter, SQL Server will perform an implicit data type conversion on the expression. Note that implicit data type conversion during assignment operation can convert a higher rank data type to a lower rank data type, which may resulting in losing data during the conversion. The tutorial exercise shows you some good examples: -- INT converted to NUMERIC DECLARE @i INT; DECLARE @d NUMERIC(9,3); SET @i = 123; SET @d = @i; SELECT @d; GO 123.000 -- INT converted to NUMERIC DECLARE @i INT; DECLARE @d NUMERIC(9,3); SET @i = 123; SET @d = @i; SELECT @d; GO 123.000 DECLARE @pi FLOAT(24); DECLARE @dp NUMERIC(5,2); SET @pi = 3.1415927E+00; SET @dp = @pi; SELECT @dp; GO 3.14

"IF ... ELSE IF ... ELSE ..." statement structure is used to select one of the specified statements to be executed based on pacified Boolean conditions. Here is the syntax of "IF ... ELSE IF ... ELSE ..." statement structure:

IF condition_1 statement_1;

ELSE IF condition_2 statement_2;

...

ELSE IF condition_n statement_n;

ELSE statement_o;

-- Executes statement_x is

if condition_x results in Boolean TRUE

The tutorial exercise below shows you how to use an IF ... ELSE statement structure to selectively execute one of the CREATE TABLE statements:

USE GlobalGuideLineDatabase

GO

DECLARE @site_name VARCHAR(40);

SET @site_name = 'SQA';

IF @site_name = 'DBA'

CREATE TABLE dba_links (url VARCHAR(256));

ELSE IF @site_name = 'SQA'

CREATE TABLE sqa_links (url VARCHAR(256));

ELSE

PRINT 'Unknown site name: '+@site_name;

GO

Command(s) completed successfully.

SELECT name FROM sys.tables WHERE name LIKE '%links';

GO

sqa_links

Normally, the comparison operator IN is used against a list of specific values as in the format of: "test_value IN (value_1, value_2, ..., value_n)". But you can also replace the list of values by a subquery as the following formats:

test_value IN (SELECT column FROM ...)

-- Returns TRUE if the test_value equals to one of

the values returned from the subquery

test_value NOT IN (SELECT column FROM ...)

-- Returns TRUE if the test_value does not equal to any of

the values returned from the subquery

The following tutorial exercise shows you two examples of IN operators. The sample database AdventureWorksLT provided by Microsoft is used.

USE adventureWorksLT

GO

SELECT COUNT(*) FROM SalesLT.Customer c

WHERE c.CustomerID IN (

SELECT s.CustomerID FROM SalesLT.SalesOrderHeader s

)

GO

32

SELECT COUNT(*) FROM SalesLT.Customer c

WHERE c.CustomerID NOT IN (

SELECT s.CustomerID FROM SalesLT.SalesOrderHeader s

)

GO

408

Logical (Boolean) operations are performed on Boolean values with logical operators like 'AND', 'OR', or 'NOT'. Logical operations return Boolean values. SQL Server 2005 supports the following logical operations:

* AND - Returns TRUE if both operands are TRUE.

* OR - Returns TRUE if one of the operands is TRUE.

* NOT - Returns TRUE if the only operand is FALSE.

Logical operations are commonly used to combine Boolean values resulted from comparison operations. The following tutorial exercise shows you a good example:

DECLARE @income MONEY;

DECLARE @marriage_status VARCHAR(10);

SET @income = 55000.00;

SET @marriage_status = 'Single';

SELECT CASE WHEN

(@marriage_status = 'Married' AND @income < 65000)

OR (@marriage_status = 'Single' AND @income < 35000)

THEN 'Qualified for the benefit.'

ELSE 'Not qualified for the benefit.'

END;

GO

Not qualified for the benefit.

EXISTS is a special operator used to test subquery results. EXISTS can be used in two ways:

EXISTS (SELECT ...)

-- Returns TRUE if the specified subquery has one or more rows returned.

NOT EXISTS (SELECT ...)

-- Returns TRUE if the specified subquery no rows returned.

The following tutorial exercise shows you two examples of EXISTS operators. The sample database AdventureWorksLT provided by Microsoft is used.

USE AdventureWorksLT

GO

-- Number of customers with orders

SELECT COUNT(*) FROM SalesLT.Customer c

WHERE EXISTS (

SELECT * FROM SalesLT.SalesOrderHeader s

WHERE s.CustomerID = c.CustomerID

)

GO

32

-- Number of customers without orders

SELECT COUNT(*) FROM SalesLT.Customer c

WHERE NOT EXISTS (

SELECT * FROM SalesLT.SalesOrderHeader s

WHERE s.CustomerID = c.CustomerID

)

408

"BEGIN ... END" statement structure is used to group multiple statements into a single statement block, which can be used in other statement structures as a single statement. For example, a statement block can be used in an "IF ... ELSE ..." statement structure as a single statement.

The tutorial exercise below shows you how to use "BEGIN ... END" statement structures to place multiple statements into an "IF ... ELSE" statement structure:

DECLARE @site_name VARCHAR(40);

SET @site_name = 'SQA';

IF @site_name = 'DBA'

BEGIN

PRINT 'Dropping table: dba_links';

DROP TABLE dba_links;

END

ELSE IF @site_name = 'SQA'

BEGIN

PRINT 'Dropping table: sqa_links';

DROP TABLE sqa_links;

END

ELSE

PRINT 'Unknown site name: '+@site_name;

GO

Dropping table: sqa_links

Wildcard character '%' can be used in the pattern string for the LIKE operator to match any string of zero or more characters. The following example uses '%Sport% Store' to search all company names that has a partial word 'Sport' and ended with the word 'Store'. The sample database AdventureWorksLT provided by Microsoft is used.

USE adventureWorksLT

GO

SELECT c.CompanyName FROM SalesLT.Customer c

WHERE c.CompanyName LIKE '%Sport% Store'

GO

CompanyName

-------------------------

Specialty Sports Store

Camping and Sports Store

Vigorous Sports Store

Our Sporting Goods Store

Sports Store

Sports Products Store

Pattern match is a very important operation for search records base on character string columns. SQL Server 2005 offers the LIKE operator to perform pattern match operations in two formats:

target_string LIKE pattern

-- Returns TRUE

if the target string matches the pattern

target_string NOT LIKE pattern

-- Returns TRUE

if the target string does not match the pattern

Pattern match is a powerful operation. But you need to remember several rules:

* Pattern may contain predefined wildcard characters, similar to Unix Regular Expressions. But they are not the same.

* '%' is a wildcard character that matches any string of zero or more characters.

* '_' is a wildcard character that matches any single character.

* '_' is a wildcard character that matches any single character.

* '[abc]' is a wildcard character that matches any character listed inside the brackets.

* '[a-c]' is a wildcard character that matches any character in the range defined in the brackets.

* '[^abc]' is a wildcard character that matches any character not listed inside the brackets.

* '[^a-c]' is a wildcard character that matches any character not in the range defined in the brackets.

In order to help you to learn SQL Server, Microsoft provides several free sample scripts and sample databases.

* SqlServerSamples.msi - 25,469 KB: Sample scripts.

* AdventureWorksDB.msi - 28,053 KB: Sample OLTP database: AdventureWorks, case-sensitive collation version.

* AdventureWorksDBCI.msi - 29,177 KB: Sample OLTP database: AdventureWorks, case-insensitive collation version.

* AdventureWorksBI.msi - 7,393 KB: Sample data warehouse database: AdventureWorks, case-sensitive collation version.

* AdventureWorksBICI.msi - 16,764 KB: Sample data warehouse database: AdventureWorks, case-insensitive collation version.

* AdventureWorksLT.msi - 2,251 KB: The scaled-down sample database AdventureWorks for beginners.

If you want to skip the remaining statements in a loop block, you can use the CONTINUE statement.

The tutorial exercise below shows you how to use a CONTINUE statement to skip the remaining statements and continue the next iteration:

-- Printing first 7 Sundays in 2000

DECLARE @date DATETIME;

DECLARE @count INT;

SET @date = '1999-12-31';

SET @count = 0;

WHILE DATEPART(YEAR, @date) < 2001 BEGIN

SET @date = DATEADD(DAY, 1, @date);

IF @count = 7 BREAK;

IF DATENAME(WEEKDAY, @date) <> 'Sunday' CONTINUE;

PRINT CONVERT(VARCHAR(40),@date,107);

SET @count = @count + 1;

END

GO

Jan 02, 2000

Jan 09, 2000

Jan 16, 2000

Jan 23, 2000

Jan 30, 2000

Feb 06, 2000

Feb 13, 2000

If you want to practice you DBA skills with the scaled-down version of the sample database AdventureWorksLT provided by Microsoft, you should follow this tutorial to download and install it first:

1. Go to the SQL Server 2005 Samples and Sample Databases download page.

2. Go to the x86 section in the Instructions section, Click "AdventureWorksLT.msi -- 2,251 KB" to download the sample file. Save the download file to c: emp.

3. Double click on the downloaded file: c: empAdventureWorksLT.msi. The installation setup window shows up. Follow the instructions to finish the installation.

When the installation is done, two physical database files are installed in the data directory: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData:

AdventureWorksLT_Data.mdf 5,120 KB

AdventureWorksLT_Log.ldf 2,048 KB