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
*/ |
