DB4Breakfast

This post is for those that are brand new to SQL Server. I will guide you through how to set up a test environment for your database learning. After completing this tutorials you will have a SQL Server 2012 instance on your computer along with a sample database. Step 1: You can download a 180 day trial version of SQL Server 2012 here , or spent $40 to get the developer version with all the feature here . Step 2: Follow this instruction from Andy Leonard to install a default SQL Server Instance on your computer. Note make sure you follow this instruction exactly or else instruction on step 5 won't work. Step 3: Download a copy of the AdventureWorks database here . This database will be used for your learning. Step 4: Read the instruction below to launch SQL Server Management Studio(SSMS):   Windows 7: go to Start menu > All Programs > Go to "Microsoft SQL Server 2102" folder > launch SQL Server Management Studio   Windows 8: on your keyboard press this...

Data Type: Is a constraint Encapsulates behavior Relational Model's Physical Data Independence Let the storage engine take care of the internal storage format Exact Numeric: TINYINT (1 byte) SMALLINT (2 bytes) INT (4 bytes) BIGINT (8 bytes) NUMBERIC (size varies depend on precision) DECIMAL (size varies depend on precision) SMALLMONEY(4 bytes) MONEY(8 bytes) Approximate Numeric REAL (4 bytes) FLOAT Character non-Unicode (CHAR, VARCHAR) support only one language based on collation properties 1 byte/character Use single quotation marks, 'xyz', to indicate regular character strings (non-Unicode) Character Unicode (NCHAR, NVARCHAR) Unicode, support multiple language 2 bytes/character Use N'xyz', to indicate Unicode character string. Binary Strings: BINARY VARBINARY Image Date and Time: DATE (3 bytes) TIME (3 to 5 bytes) DATETIME2 (6 to 8 bytes) SMALLDATETIME (4 bytes) DATETIME (8 bytes) ...

String Concatenation with  plus (+) operator Example: the result of this query will return NULL because @mname is NULL.  Note: NULL mark represent missing data. Thus known data + missing data = unknown DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20); SELECT @lname + ', ' + @fname + ' ' + @mname AS fullName; Example: this query result an error because can't convert 'x, y' string to an TINYINT. DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT = 2; SELECT@lname + ', ' + @fname + ' ' + @a_number AS fullName_and_number; Result: Conversion failed when converting the varchar value 'y, x ' to data type tinyint. To avoid the error above we can convert number 2 to a string like this: DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT ...