/* Author: Michael Thomas email: michael@michael-thomas.com Used for the examples at: www.michael-thomas.com for MSSQL Commands. */ use master IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTempDB') begin DROP DATABASE [MyTempDB] end go create database MyTempDB go use MyTempDB -- ************************** -- MyTableTop -- ************************** if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTableTop') ) begin drop table MyTableTop end go create table MyTableTop ( myInt int, myChar char(1), myNVarchar nvarchar(255) ) go insert into MyTableTop ( myInt, myChar, myNVarchar ) values (7, 'G', 'Hello World 7') insert into MyTableTop ( myInt, myChar, myNVarchar ) values (6, 'F', 'Hello World 6') insert into MyTableTop ( myInt, myChar, myNVarchar ) values (5, 'E', 'Hello World 5') insert into MyTableTop ( myInt, myChar, myNVarchar ) values (4, 'D', 'Hello World 4') insert into MyTableTop ( myInt, myChar, myNVarchar ) values (3, 'C', 'Hello World 3') insert into MyTableTop ( myInt, myChar, myNVarchar ) values (2, 'B', 'Hello World 2') insert into MyTableTop ( myInt, myChar, myNVarchar ) values (1, 'A', 'Hello World 1') select * from MyTableTop -- Returns all records. select count(*) from MyTableTop -- 7 (Results: 7) select top 2 * from MyTableTop order by MyChar /* Results: 1 A Hello World 1 2 B Hello World 2 */ select top 10 percent * from MyTableTop order by MyChar /* Results: 1 A Hello World 1 */ -- ************************** -- MyTableOrderBy -- ************************** if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTableOrderBy') ) begin drop table MyTableOrderBy end go create table MyTableOrderBy ( myInt int, myChar char(1), myNVarchar nvarchar(255) ) go insert into MyTableOrderBy ( myInt, myChar, myNVarchar ) values (1, 'A', 'Hello World 1') insert into MyTableOrderBy ( myInt, myChar, myNVarchar ) values (2, 'C', 'Hello World 3') insert into MyTableOrderBy ( myInt, myChar, myNVarchar ) values (3, 'B', 'Hello World 2') go select * from MyTableOrderBy select * from MyTableOrderBy order by myChar /* Results: (Physical table order.) 1 A Hello World 1 3 B Hello World 2 2 C Hello World 3 */ select * from MyTableOrderBy order by myChar ASC /* Results: (Ascending order) 1 A Hello World 1 3 B Hello World 2 2 C Hello World 3 */ select * from MyTableOrderBy order by myChar DESC /* Results: (Descending order) 2 C Hello World 3 3 B Hello World 2 1 A Hello World 1 */ -- ************************** -- MyTableOrderBy_B -- ************************** if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTableOrderBy_B') ) begin drop table MyTableOrderBy_B end go create table MyTableOrderBy_B ( myInt int, myChar char(1), myNVarchar nvarchar(255) ) go insert into MyTableOrderBy_B ( myInt, lastname, myNVarchar ) values (1, 'Thomas', 'Michael') insert into MyTableOrderBy_B ( myInt, lastname, myNVarchar ) values (2, 'Thomas', 'Charmaine') insert into MyTableOrderBy_B ( myInt, lastname, myNVarchar ) values (3, 'Thomas', 'Adam') insert into MyTableOrderBy_B ( myInt, lastname, myNVarchar ) values (4, 'Doe', 'John') go select * from MyTableOrderBy_B -- ************************** -- MyTableTop -- ************************** if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTableMath') ) begin drop table MyTableMath end go create table MyTableMath ( myInt int, Rate float, Amount float ) go insert into MyTableMath ( myInt, Rate, Amount ) values (1, 10.0, 100.00) insert into MyTableMath ( myInt, Rate, Amount ) values (2, 10.0, 200.00) insert into MyTableMath ( myInt, Rate, Amount ) values (3, 5.0, 30.00) insert into MyTableMath ( myInt, Rate, Amount ) values (4, 5.0, 40.00) select myInt, Amount, Multipler = (1+(Rate/100)), Formula = ( (1+(Rate/100)) * Amount ) from MyTableMath /* Results: 1 100.0 1.1000000000000001 110.00000000000001 2 200.0 1.1000000000000001 220.00000000000003 3 30.0 1.05 31.5 4 40.0 1.05 42.0 */ select myInt, Amount, Multipler = (1+(Rate/100)), Formula = ( (1+(Rate/100)) * Amount ) from MyTableMath order by Formula /* Results: 3 30.0 1.05 31.5 4 40.0 1.05 42.0 1 100.0 1.1000000000000001 110.00000000000001 2 200.0 1.1000000000000001 220.00000000000003 */ -- *********************************** -- Local Variables -- *********************************** if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTableLocalVar') ) begin drop table MyTableLocalVar end go create table MyTableLocalVar ( myInt int, myChar char(1), myNVarchar nvarchar(255) ) go insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (7, 'G', 'Hello World 7') insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (6, 'F', 'Hello World 6') insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (5, 'E', 'Hello World 5') insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (4, 'D', 'Hello World 4') insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (3, 'C', 'Hello World 3') insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (2, 'B', 'Hello World 2') insert into MyTableLocalVar ( myInt, myChar, myNVarchar ) values (1, 'A', 'Hello World 1') --Ex: Assign 1 or more variables a value from a select. declare @MyVar1A nvarchar(255) declare @MyVar1B nvarchar(255) select @MyVar1A = myChar, @MyVar1B = myNVarchar from MyTableLocalVar where MyChar = 'A' select @MyVar1A as 'MyVar1A', @MyVar1B as 'MyVar1B' -- Results: A Hello World 1 --Ex: Assign 1 variable a value from a select. declare @MyVar2 nvarchar(255) set @MyVar2 = ( select myChar from MyTableLocalVar where MyChar = 'A' ) select @MyVar2 as 'MyVar2' -- Results: A declare @MyVar3 nvarchar(255) select @MyVar3 = ( select myChar from MyTableLocalVar where MyChar = 'A' ) -- Displays nothing. select @MyVar3 -- Results: A -- ******* -- Add order by a col alias or expression. -- *******