DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply numbers in T-SQL queries and table design?

Execute the following SQL Server T-SQL scripts in Microsoft SSMS Query Editor to demonstrate the application of numbers (numeric data types) in preparing queries and designing tables.

/* SQL number types (numeric data types) in SQL Server 2008

 

      BIGINT

      DECIMAL

      FLOAT

      INT

      MONEY

      NUMERIC

      REAL

      SMALLINT

      SMALLMONEY

      TINYINT

 */

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

-- INTEGER number types

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

-- TINYINT numeric data type - 1 byte

DECLARE @TinyintLow tinyint, @TinyintHigh tinyint

SELECT @TinyintLow = 0, @TinyintHigh = 255

SELECT [TINYINT Low Range]=@TinyintLow, [TINYINT High Range]=@TinyintHigh

/* Results

 

TINYINT Low Range             TINYINT High Range

0                             255

*/

 

-- SMALLINT numeric data type - 2 bytes

DECLARE @SmallintLow smallint, @SmallintHigh smallint

SELECT @SmallintLow = - 256 * 128, @SmallintHigh = 256 * 128 -1

SELECT [SMALLINT Low Range]=@SmallintLow,[SMALLINT High Range] = @SmallintHigh

/* Results

 

SMALLINT Low Range            SMALLINT High Range

-32768                        32767

*/

 

-- INT (INTEGER) numeric data type - 4 bytes

DECLARE @IntLow int, @IntHigh integer

SELECT @IntLow  = - POWER(2,30) -POWER(2,30),

       @IntHigh =   POWER(2,30) -1 + POWER(2,30)

SELECT [INTEGER Low Range]=@IntLow,[INTEGER High Range] = @IntHigh

/* Results

 

INTEGER Low Range       INTEGER High Range

-2147483648             2147483647

*/

 

-- BIGINT numeric data type - 8 bytes

DECLARE @BigintLow bigint, @BigintHigh bigint

SELECT @BigintLow  = - 9223372036854775808,

       @BigintHigh =   9223372036854775807

SELECT [BIGINT Low Range]=@BigintLow,[BIGINT High Range] = @BigintHigh

 

/* Results

 

BIGINT Low Range        BIGINT High Range

-9223372036854775808    9223372036854775807

*/

 

 

-- Formatting INT with thousands separators

SELECT REPLACE(CONVERT(varchar, CONVERT(money,2147483647),1), '.00','')

GO

-- Result: 2,147,483,647

-- Formatting BIGINT with thousands separators 

DECLARE @BigintValue bigint, @BigintString varchar(32), @i int = 4

SET @BigintValue = 9223372036854775807

SELECT @BigintString=REVERSE(CONVERT(varchar(32), @BigintValue))

WHILE ( @i <= LEN (@BigintString))

BEGIN

      SET @BigintString = STUFF(@BigintString, @i, 0, ',')

      SET @i + = 4

END

SELECT [BIGINT with thousands separators]= REVERSE(@BigintString)

/*  Result

 

BIGINT with thousands separators

9,223,372,036,854,775,807

*/

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

-- SQL cast string to integer

SELECT intValue = CAST ('123456' as INT)

-- Result: 123456

-- SQL convert string to integer 

SELECT intValue = CONVERT (INT, '00123456')

-- Result: 123456

 

SELECT intValue = CAST ('123,456' as INT)

/* Error

 

Msg 245, Level 16, State 1, Line 5

Conversion failed when converting the varchar value '123,456' to data type int.

*/

 

-- SQL cast integer to string

SELECT stringValue = CAST (123456 as varchar)

-- Result: 123456

-- SQL convert integer to string 

DECLARE @Number int=123456; SELECT stringValue=CONVERT(varchar(12), @Number);

-- Result: 123456

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

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

-- SQL decomposing INTEGER (INT) into 4 bytes

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

DECLARE @intNumber int, @o1 tinyint, @o2 tinyint, @o3 tinyint, @o4 tinyint

SET @intNumber = 2000000001

 

    SET @o1 = @intNumber / 16777216

    SET @intNumber = @intNumber % 16777216

    SET @o2 = @intNumber / 65536

    SET @intNumber = @intNumber % 65536

    SET @o3 = @intNumber / 256

    SET @intNumber = @intNumber % 256

    SET @o4 = @intNumber

   

SELECT @o1, @o2, @o3, @o4

-- Result: 119    53    148   1

 

-- SQL composing 4 bytes into an INTEGER (INT)

SELECT @o1*16777216 + @o2 * 65536 + @o3 * 256 + @o4

-- Result: 2000000001

 

 SELECT 119*16777216 + 53 * 65536 + 148 * 256 + 1

-- Result: 2000000001

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

-- SQL convert integer to binary

DECLARE @INT int = 2000000001, @BINARY binary(4)

SET @BINARY =  @INT

SELECT @BINARY

-- Result in hex (hexadecimal): 0x77359401

 

-- SQL convert binary to integer

SET @BINARY = 0x7FFFFFFF

SET @INT = @BINARY

SELECT @BINARY, @INT

-- Results: 0x7FFFFFFF 2147483647  
------------

 

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

-- NUMERIC, DEMICAL, DEC number types

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

/* Valid range for DECIMAL, DEC and NUMERIC

 

Low Range        High Range

      -10^38 +1         10^38 - 1

*/

-- SQL padding with zeroes - SQL numeric type - SQL money to numeric

SELECT TOP (5)

SalesOrderID,

OrderDate = convert( DATE, OrderDate),

SubTotal,

STNumeric=CAST(SubTotal AS NUMERIC(10,4)),

STPadded = RIGHT(REPLICATE('0', 11)

+ CAST(CAST(SubTotal AS NUMERIC(10,4)) AS VARCHAR), 11)

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY NEWID()

/* Results

 

SalesOrderID      OrderDate   SubTotal    STNumeric   STPadded

69239             2004-04-28  2428.05     2428.0500   002428.0500

47765             2002-10-06  2049.0982   2049.0982   002049.0982

56599             2003-10-24  2332.28     2332.2800   002332.2800

57120             2003-11-01  34238.9042  34238.9042  034238.9042

63494             2004-02-03  2419.06     2419.0600   002419.0600

*/

 

Related articles:

Precision, Scale, and Length (Transact-SQL)

decimal and numeric (Transact-SQL)

 

 
Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE