
SQL Server DBA FAQs and Tips
SQL Server FAQs - Downloading and Installing SQL Server 2005 Express Edition
A collection of 14 FAQs on SQL Server 2005 Express Edition download and installation. Clear answers are provided with tutorial exercises on installing SQL Server 2005 Express Edition; installing .NET Framework Version 2.0; installing Management Studio Express Edition and Books Online.
- What is Microsoft SQL Server?
- How to download Microsoft SQL Server 2005 Express Edition?
- What are the requirements to install SQL Server 2005 Express Edition?
- Why I am getting "The Microsoft .Net Framework 2.0 in not installed" message?
- How to download and install Microsoft .NET Framework Version 2.0?
- What is mscorsvw.exe - Process - Microsoft .NET Framework NGEN?
- How to install SQL Server 2005 Express Edition?
- How do you know if SQL Server is running on your local system?
- What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS)
- How to download and install Microsoft SQL Server Management Studio Express?
- How to connect SQL Server Management Studio Express to SQL Server 2005 Express?
- How to run Queries with SQL Server Management Studio Express?
- How to download and install SQL Server 2005 Books Online?
- How to run SQL Server 2005 Books Online on your local system?
SQL Server Tutorials - Getting Started with Transact-SQL Statements
A collection of 10 tutorials for you to get started quickly with running Transact-SQL statements on SQL Server 2005. Clear examples are provided for running CREATE TABLE, INSERT, UPDATE, DELTE, SELECT, CREATE USER, GRANT EXECUTE statements.
- How to use Transact-SQL statements to access the database engine?
- How to create new databases with "CREATE DATABASE" statements?
- How to create new table with "CREATE TABLE" statements?
- How to insert and update data into a table with "INSERT" and "UPDATE" statements?
- How to read data in a table with "SELECT" statements?
- How to create a login to access the database engine using "CREATE LOGIN" statements?
- How to create a user to access a database using "CREATE USER" statements?
- How to create a view and a stored procedure using "CREATE VIEW/PROCEDURE" statements?
- How to grant a permission using "GRANT EXECUTE" statements?
- How to delete database objects with "DROP" statements?
SQL Server FAQs - Creating and Managing Databases and Physical Files
A collection of 16 FAQs on creating and managing databases on SQL Server 2005. Clear answers are provided with tutorial exercises on creating, renaming, and deleting databases; setting current database; bringing databases offline and online; reviewing, moving and copy database physical files.
- What is a database?
- What is the simplest way to create a new database?
- How to set the current database?
- How to delete a database?
- Why I am getting this error when dropping a database?
- How to get a list all databases on the SQL server?
- Where is my database stored on the hard disk?
- How to create database with physical files specified?
- How to rename databases?
- Why I am getting this error when renaming a database?
- What are database states?
- How to set a database state to OFFLINE?
- How to move database physical files?
- How to set database to be READ_ONLY?
- How to set database to be SINGLE_USER?
- What are system databases?
SQL Server FAQs - Creating/Managing Tables and Adding/Altering/Deleting Columns
A collection of 16 FAQs on creating and managing tables on SQL Server 2005. Clear answers are provided with tutorial exercises on creating, renaming, and deleting tables; reviewing, renaming, deleting and adding table columns; changing column data types.
- What is a table?
- What are DDL (Data Definition Language) statements for tables?
- How to create new tables with "CREATE TABLE" statements?
- How To Get a List of All Tables with "sys.tables" View?
- How To Get a List of Columns using the "sys.columns" View?
- How To Get a List of Columns using the "sp_columns" Stored Procedure?
- How To Get a List of Columns using the "sp_help" Stored Procedure?
- How To Generate CREATE TABLE Script on an Existing Table?
- How to create new tables with "SELECT ... INTO" statements?
- How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?
- How To Delete an Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?
- How to rename an existing column with the "sp_rename" stored procedure?
- How to rename an existing column with SQL Server Management Studio?
- How to change the data type of an existing column with "ALTER TABLE" statements?
- How to rename an existing table with the "sp_rename" stored procedure?
- How To Drop an Existing Table with "DROP TABLE" Statements?
SQL Server FAQs - Understanding INSERT, UPDATE and DELETE Statements
A collection of 18 FAQs on SQL INSERT, UPDATE and DELETE statements. Clear answers are provided with tutorial exercises on inserting, updating and deleting single and multiple rows; using column default values; using subqueries to INSERT and UPDATE statements.
- What Are DML (Data Manipulation Language) Statements?
- How To Create a Testing Table with Test Data?
- How To Insert a New Row into a Table with "INSERT INTO" Statements?
- How To Use Column Default Values in INSERT Statements?
- How to provide column names in INSERT Statements?
- What Happens If You Insert a Duplicate Key for the Primary Key Column?
- How To Insert Multiple Rows with One INSERT Statement?
- How To Update Values in a Table with UPDATE Statements?
- How To Update Multiple Rows with One UPDATE Statement?
- How to use old values to define new values in UPDATE statements?
- Is the Order of Columns in the SET Clause Important?
- How To Use Values from Other Tables in UPDATE Statements?
- What Happens If the UPDATE Subquery Returns No Rows?
- What Happens If the UPDATE Subquery Returns Multiple Rows?
- How To Delete an Existing Row with DELETE Statements?
- How To Delete Multiple Rows with One DELETE Statement?
- How To Delete All Rows with DELETE Statements?
- How To Delete All Rows with TRUNCATE TABLE Statement?
SQL Server FAQs - Understanding SELECT Statements with Joins and Subqueries
A collection of 17 FAQs on SELECT statements with joins and subqueries. Clear answers are provided with tutorial exercises on joining multiple tables with inner and outer joins; using subqueries with IN, EXISTS, and FROM clauses; using UNION operations.
- How To Join Two Tables in a Single Query?
- How To Write a Query with an Inner Join?
- How To Define and Use Table Alias Names?
- How To Write a Query with a Left Outer Join?
- How To Write a Query with a Right Outer Join?
- How To Write a Query with a Full Outer Join?
- How To Write an Inner Join with the WHERE Clause?
- How To Name Query Output Columns?
- What Is a Subquery in a SELECT Query Statement?
- How To Use Subqueries with the IN Operators?
- How To Use Subqueries with the EXISTS Operators?
- How To Use Subqueries in the FROM Clause?
- How To Count Groups Returned with the GROUP BY Clause?
- How To Return the Top 5 Rows from a SELECT Query?
- How To Return the Second 5 Rows?
- How To Use UNION to Merge Outputs from Two Queries Together?
- How To Use ORDER BY with UNION Operators
SQL Server FAQs - Understanding SELECT Statements and GROUP BY Clauses
A collection of 20 FAQs on SQL SELECT query statements. Clear answers are provided with tutorial exercises on simple SELECT query to select specific rows and returning specific columns; applying group aggregation functions on selected rows; dividing selected rows into multiple groups using GROUP BY and HAVING conditions; sorting selected rows and groups.
- What Is a SELECT Query Statement?
- How To Create a Testing Table with Test Data?
- How To Select All Columns of All Rows from a Table with a SELECT statement?
- How To Select Some Specific Columns from a Table in a Query?
- How To Select Some Specific Rows from a Table?
- How To Add More Data to the Testing Table?
- How To Sort the Query Output with ORDER BY Clauses?
- Can the Query Output Be Sorted by Multiple Columns?
- How To Sort Query Output in Descending Order?
- How To Count Rows with the COUNT(*) Function?
- Can SELECT Statements Be Used on Views?
- How To Filter Out Duplications in the Returning Rows?
- What Are Group Functions in Query Statements?
- How To Use Group Functions in the SELECT Clause?
- Can Group Functions Be Mixed with Non-group Selection Fields?
- How To Divide Query Output into Multiple Groups with the GROUP BY Clause?
- How To Apply Filtering Criteria at Group Level with The HAVING Clause?
- How To Count Duplicated Values in a Column?
- Can Multiple Columns Be Used in GROUP BY?
- Can Group Functions Be Used in the ORDER BY Clause?
SQL Server FAQs - Introduction To Transact-SQL Language Basics and Data Types
A collection of 19 FAQs on Transact-SQL language basics and data types. Clear answers are provided with tutorial exercises on writing Transact-SQL statements; entering comments; ending and running statement batches; GO and PRINT statements; exact and approximate numeric data types; data and time, Unicode character strings and binary strings.
- What Is SQL Language?
- What Is Transact-SQL Language?
- What Is a Transact-SQL Statement?
- How To Start and End Transact-SQL Statements?
- How To Enter Comments in Transact-SQL Statements?
- What Is a Transact-SQL Statement Batch?
- What Happens to a Statement Batch If There Is a Compilation Error?
- How To Use GO Command in "sqlcmd"?
- How To Create User Messages with PRINT Statements?
- How Many Categories of Data Types Used by SQL Server?
- What Are Exact Numeric Data Types?
- What Are Approximate Numeric Data Types?
- What Are Date and Time Data Types?
- What Are Character String Data Types?
- What Are Unicode Character String Data Types?
- What Are Binary String Data Types?
- What Are the Differences between CHAR and NCHAR?
- What Are the Differences between CHAR and VARCHAR?
- What Are the Differences between DECIMAL and FLOAT?
SQL Server FAQs - Transact-SQL Constants/Literals and Value Ranges
A collection of 18 FAQs on Transact-SQL language data constants or literals and value ranges. Clear answers are provided with tutorial exercises on entering data constants or literals; quoted character strings; date and time formats and ranges; integer and exact number literals; floating number precisions and ranges.
- What Is a Constant or Literal?
- How To Write Character String Constants or Literals?
- What Is a Collation?
- How To Specify the Collation for a Character Data Type?
- What Happens If Strings Are Casted into Wrong Code Pages?
- How To Find Out What Is the Default Collation in a Database?
- How Fixed Length Strings Are Truncated and Padded?
- How To Enter Unicode Character String Literals?
- How To Enter Binary String Literals?
- How To Enter Date and Time Literals?
- Why I Can Not Enter 0.001 Second in Date and Time Literals?
- What Happens If Date-Only Values Are Provided as Date and Time Literals?
- What Happens If Time-Only Values Are Provided as Date and Time Literals?
- What Are Out-of-Range Errors with Date and Time Literals?
- What Happens If an Integer Is Too Big for INT Date Type?
- How Extra Digits Are Handled with NUMERIC Data Type Literals?
- How REAL and FLOAT Literal Values Are Rounded?
- What Are the Underflow and Overflow Behaviors on FLOAT Literals?
SQL Server FAQs - Transact-SQL Numeric Expressions and Functions
A collection of 14 FAQs on Transact-SQL language numeric expressions and functions. Clear answers are provided with tutorial exercises on writing numeric expressions; arithmetic operations; implicit data type conversions; explicit data type conversions; mathematical functions, random number generations.
- What Is an Expression?
- What Are Arithmetic Operators?
- What Happens to an Arithmetic Operation with Two Different Data Types?
- How To Convert a Numeric Expression from One Data Type to Another?
- How To Convert Numeric Expression Data Types by Assignment Operations?
- How To Convert Numeric Expression Data Types using the CAST() Function?
- How To Convert Numeric Expression Data Types using the CONVERT() Function?
- How To Convert Character Strings into Numeric Values?
- What Happens When Converting Big Values to Integers?
- What Happens When Converting Big Values to NUMERIC Data Types?
- What Are the Mathematical Functions Supported by SQL Server 2005?
- How To Convert Numeric Values to Integers?
- How To Round a Numeric Value To a Specific Precision?
- How To Generate Random Numbers with the RAND() Function?
SQL Server FAQs - Transact-SQL Managing Character Strings and Binary Strings
A collection of 14 FAQs on Transact-SQL language character string and binary string operations. Clear answers are provided with tutorial exercises on concatenating character/binary strings; converting Unicode character strings; converting binary strings; inserting new line characters; locating and taking substrings; bitwise operations.
- How To Concatenate Two Character Strings Together?
- What Happens When Unicode Strings Concatenate with Non-Unicode Strings?
- How To Convert a Unicode Strings to Non-Unicode Strings?
- What Are the Character String Functions Supported by SQL Server 2005?
- How To Insert New Line Characters into Strings?
- How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?
- How To Concatenate Two Binary Strings Together?
- Can Binary Strings Be Used in Arithmetical Operations?
- How To Convert Binary Strings into Integers?
- Can Binary Strings Be Converted into NUMERIC or FLOAT Data Types?
- Can Binary Strings Be Converted into Character Strings?
- Can Binary Strings Be Converted into Unicode Character Strings?
- How To Convert Binary Strings into Hexadecimal Character Strings
- What Are Bitwise Operations?
SQL Server FAQs - Transact-SQL Date and Time Operations and Functions
A collection of 18 FAQs on Transact-SQL language date and time operations and functions. Clear answers are provided with tutorial exercises on converting date and time values to integers or decimal values; incrementing and decrementing date parts; taking date differences; formatting dates to strings; getting GMT times.
- How To Add or Remove Days on Date and Time Values?
- Can Date and Time Values Be Converted into Integers?
- Can Integers Be Converted into Date and Time Values?
- Are DATETIME and NUMERIC Values Convertible?
- Can a DATETIME Value Be Subtracted from Another DATETIME Value?
- What Are the Date and Time Functions Supported by SQL Server 2005?
- How To Increment or Decrement Parts of DATETIME Values?
- How To Use DATEADD() Function?
- How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
- How To Calculate Age in Days, Hours and Minutes?
- How To Get Month and Weekday Names from DATETIME Values?
- How To Get Parts of DATETIME Values as Integers?
- How To Get Year, Month and Day Out of DATETIME Values?
- What Is the Difference Between GETDATE() and GETUTCDATE()?
- How To Format Time Zone in +/-hh:mm Format?
- How To Format DATETIME Values to Strings with the CONVERT() Function?
- How To Truncate DATETIME Values to Dates without Time?
- How To Set Different Parts of a DATETIME Value?
SQL Server FAQs - Transact-SQL Boolean Values and Logical Operations
A collection of 14 FAQs on Transact-SQL language Boolean values and logical operations. Clear answers are provided with tutorial exercises on getting Boolean values with comparison operations; CASE expressions; BETWEEN and IN comparisons; pattern match with LIKE operator; EXISTS and IN subquery expressions; AND and OR logical operations.
- What Is a Boolean Value?
- What Are Conditional Expressions?
- What Are Comparison Operations?
- How To Perform Comparison on Exact Numbers?
- How To Perform Comparison on Floating Point Numbers?
- How To Perform Comparison on Date and Time Values?
- How To Perform Comparison on Character Strings?
- What To Test Value Ranges with the BETWEEN Operator?
- What To Test Value Lists with the IN Operator?
- What To Perform Pattern Match with the LIKE Operator?
- How To Use Wildcard Characters in LIKE Operations?
- How To Test Subquery Results with the EXISTS Operator?
- How To Test Values Returned by a Subquery with the IN Operator?
- What Are Logical/Boolean Operations?
SQL Server FAQs - Transact-SQL Conditional Statements and Loops
A collection of 5 FAQs on Transact-SQL language conditional statements and loops. Clear answers are provided with tutorial exercises on IF ... ELSE statements; WHILE loops; statement blocks; BREAK and CONTINUE statements.
- How To Use "IF ... ELSE IF ... ELSE ..." Statement Structures?
- How To Use "BEGIN ... END" Statement Structures?
- How To Use WHILE Loops?
- How To Stop a Loop Early with BREAK Statements?
- How To Skip Remaining Statements in a Loop Block Using CONTINUE Statements?
SQL Server FAQs - Downloading and Installing Sample Scripts and Databases
A collection of 6 FAQs on download and installing SQL Server sample scripts and sample databases. Clear answers are provided with tutorial exercises on downloading and installing AdventureWorksLT; attaching AdventureWorksLT to SQL Server.
- What Samples and Sample Databases Are Provided by Microsoft?
- How to download and install SQL Server 2005 Sample Scripts?
- How to download and install the scaled-down database AdventureWorksLT?
- How to attach AdventureWorksLT physical files to the server?
- How AdventureWorksLT tables are related?
- How to add an address record into AdventureWorksLT?
SQL Server FAQs - Understanding and Managing Indexes
A collection of 23 FAQs on SQL Server database indexes. Clear answers are provided with tutorial exercises on creating and dropping indexes; clustered and non-clustered indexes; indexes created by primary key and unique constraints; index fragmentation and rebuilding.
- What Are Indexes?
- How To Create an Index on an Existing Table?
- How To View Existing Indexes on an Given Table using SP_HELP?
- How To View Existing Indexes on an Given Table using sys.indexes?
- How To Drop Existing Indexes?
- Is the PRIMARY KEY Column of a Table an Index?
- Does the UNIQUE Constraint Create an Index?
- What Is the Difference Between Clustered and Non-Clustered Indexes?
- How To Create a Clustered Index?
- How To Create an Index for Multiple Columns?
- How To Create a Large Table with Random Data for Index Testing?
- How To Measure Performance of INSERT Statements?
- Does Index Slows Down INSERT Statements?
- Does Index Speed Up SELECT Statements?
- What Happens If You Add a New Index to Large Table?
- What Is the Impact on Other User Sessions When Creating Indexes?
- What Is Index Fragmentation?
- What Causes Index Fragmentation?
- How To Defragment Table Indexes?
- How To Defragment Indexes with ALTER INDEX ... REORGANIZE?
- How To Rebuild Indexes with ALTER INDEX ... REBUILD?
- How To Rebuild All Indexes on a Single Table?
- How To Recreate an Existing Index?
SQL Server FAQs - Understanding and Managing Views
A collection of 20 FAQs on SQL Server database views. Clear answers are provided with tutorial exercises on creating and dropping views; defining and viewing view columns; creating views from multiple tables; inserting, updating and deleting data in underlying tables; creating schema binding view; creating indexes on views.
- What Are Views?
- How To Create a View on an Existing Table?
- How To See Existing Views?
- How To Drop Existing Views from a Database?
- How To Get a List of Columns in a View using "sys.columns"?
- How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
- How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
- How To Generate CREATE VIEW Script on an Existing View?
- Can You Create a View with Data from Multiple Tables?
- Can You Create a View using Data from Another View?
- What Happens If You Delete a Table That Is Used by a View?
- Can You Use ORDER BY When Defining a View?
- How To Modify the Underlying Query of an Existing View?
- Can You Insert Data into a View?
- Can You Update Data in a View?
- Can You Delete Data from a View?
- How To Assign New Column Names in a View?
- How Column Data Types Are Determined in a View?
- How To Bind a View to the Schema of the Underlying Tables?
- How To Create an Index on a View?
SQL Server FAQs - Transact-SQL Understanding Stored Procedures
A collection of 19 FAQs on SQL Server Transact-SQL stored procedures. Clear answers are provided with tutorial exercises on creating and dropping stored procedures; retrieving and modifying stored procedures; ending stored procedures properly; defining and passing values through input and output parameters; temporary and permanent stored procedures.
- What Are Stored Procedures?
- How To Create a Simple Stored Procedure?
- How To Execute a Stored Procedure?
- How To List All Stored Procedures in the Current Database?
- How To Drop an Existing Stored Procedure?
- How To Create a Stored Procedure with a Statement Block?
- How To End a Stored Procedure Properly?
- How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
- How To Get the Definition of a Stored Procedure Back?
- How To Modify an Existing Stored Procedure?
- How To Create Stored Procedures with Parameters?
- How To Provide Values to Stored Procedure Parameters?
- What Are the Advantages of Passing Name-Value Pairs as Parameters?
- Can You Pass Expressions to Stored Procedure Parameters?
- How To Provide Default Values to Stored Procedure Parameters?
- How To Define Output Parameters in Stored Procedures?
- How To Receive Output Values from Stored Procedures?
- How To Create a Local Temporary Stored Procedure?
- Can Another User Execute Your Local Temporary Stored Procedures?
SQL Server FAQs - Transact-SQL Understanding User_Defined_Functions
A collection of 17 FAQs on SQL Server Transact-SQL user defined functions. Clear answers are provided with tutorial exercises on creating and dropping user defined functions; defining and passing parameters into functions; providing and using parameter default values; defining and using table-valued functions. Topics included in this FAQ are:
- What Are User Defined Functions?
- What Are the Differences between User Defined Functions and Stored Procedures?
- How To Create a Simple User Defined Function?
- How To Use User Defined Functions in Expressions?
- How To List All User Defined Functions in the Current Database?
- How To Drop an Existing User Defined Function?
- How To Generate CREATE FUNCTION Script on an Existing Function?
- How To Get the Definition of a User Defined Function Back?
- How To Modify an Existing User Defined Function?
- How To Create User Defined Functions with Parameters?
- How To Provide Values to User Defined Function Parameters?
- Can You Pass Expressions to Function Parameters?
- How To Provide Default Values to Function Parameters?
- How Many Categories of Functions based Their Return Modes?
- How Many Ways to Create Table-Valued Functions?
- How To Create an Inline Table-Valued Function?
- How To Create an Multi-Statement Table-Valued Function?
SQL Server FAQs - Creating and Managing Schemas
A collection of 11 FAQs on SQL Server on creating and managing schemas. Clear answers are provided with tutorial exercises on listing all schemas and all objects in a schema; transferring tables from one schema to another schema; changing ownership of a schema.
- What Is a Schema in SQL Server 2005?
- How To Create a New Schema in a Database?
- How To List All Schemas in a Database?
- How To Create a New Table in a Given Schema?
- How To Transfer an Existing Table from One Schema to Another Schema?
- How To List All Objects in a Given Schema?
- What Is the Default Schema of Your Login Session?
- Who Is the Owner of a Schema?
- How To Change the Ownership of a Schema?
- What Happens If You Are Trying to Access a Schema Not Owned by You?
- How To Drop an Existing Schema?
SQL Server FAQs - Security - Server Login and Database User
A collection of 17 FAQs on SQL Server logins and users. Clear answers are provided with tutorial exercises on security model; security principals, securables and permissions; creating, changing and dropping login names; linking users with logins.