SQL Server FAQs
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.

1. What is Microsoft SQL Server?
2. How to download Microsoft SQL Server 2005 Express Edition?
3. What are the requirements to install SQL Server 2005 Express Edition?
4. Why I am getting "The Microsoft .Net Framework 2.0 in not installed" message?
5. How to download and install Microsoft ।NET Framework Version 2.0?
6. What is mscorsvw.exe - Process - Microsoft .NET Framework NGEN?
7. How to install SQL Server 2005 Express Edition?
8. How do you know if SQL Server is running on your local system?
9. What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS)
10. How to download and install Microsoft SQL Server Management Studio Express?
11. How to connect SQL Server Management Studio Express to SQL Server 2005 Express?
12. How to run Queries with SQL Server Management Studio Express?
13. How to download and install SQL Server 2005 Books Online?
14. How to run SQL Server 2005 Books Online on your local system?

More...

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.

1. How to use Transact-SQL statements to access the database engine?
2. How to create new databases with "CREATE DATABASE" statements?
3. How to create new table with "CREATE TABLE" statements?
4. How to insert and update data into a table with "INSERT" and "UPDATE" statements?
5. How to read data in a table with "SELECT" statements?
6. How to create a login to access the database engine using "CREATE LOGIN" statements?
7. How to create a user to access a database using "CREATE USER" statements?
8. How to create a view and a stored procedure using "CREATE VIEW/PROCEDURE" statements?
9. How to grant a permission using "GRANT EXECUTE" statements?
10. How to delete database objects with "DROP" statements?
    More...

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.

1. What is a database?
2. What is the simplest way to create a new database?
3. How to set the current database?
4. How to delete a database?
5. Why I am getting this error
   when dropping a database?


6. How to get a list all
   databases on the SQL server?


7. Where is my database stored on the hard
   disk?


8. How to create database
   with physical files specified?


9. How to rename databases?


10. Why I am getting this error
    when renaming a database?


11. What are database states?


12. How to set a database state to
    OFFLINE?


13. How to move database
    physical files?


14. How to set database
    to be READ_ONLY?


15. How to set database
    to be SINGLE_USER?


16. What are system databases?

More...

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.

1. What is a table?


2. What are DDL (Data Definition Language)
   statements for tables?


3. How to create new tables with "CREATE
   TABLE" statements?


4. How To Get a List of All
   Tables with "sys.tables" View?


5. How To Get a List
   of Columns using the "sys.columns" View?


6. How To Get a List
   of Columns using the "sp_columns" Stored Procedure?


7. How To Get a List of
   Columns using the "sp_help" Stored Procedure?


8. How To Generate
   CREATE TABLE Script on an Existing Table?


9. How to create new tables
   with "SELECT ... INTO" statements?


10. How To Add a New
    Column to an Existing Table with "ALTER TABLE ... ADD"?


11. How To Delete an
    Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?


12. How to rename an existing
    column with the "sp_rename" stored procedure?


13. How to rename an
    existing column with SQL Server Management Studio?


14. How to change
    the data type of an existing column with "ALTER TABLE" statements?


15. How to rename an existing
    table with the "sp_rename" stored procedure?


16. How To Drop an Existing Table
    with "DROP TABLE" Statements?

More...

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.

1. What Are DML (Data Manipulation
   Language) Statements?


2. How To Create a Testing
   Table with Test Data?


3. How To Insert a New
   Row into a Table with "INSERT INTO" Statements?


4. How To Use
   Column Default Values in INSERT Statements?


5. How to provide column
   names in INSERT Statements?


6. What Happens If You Insert
   a Duplicate Key for the Primary Key Column?


7. How To Insert Multiple
   Rows with One INSERT Statement?


8. How To Update Values in a
   Table with UPDATE Statements?


9. How To Update Multiple
   Rows with One UPDATE Statement?


10. How to
    use old values to define new values in UPDATE statements?


11. Is the Order of Columns
    in the SET Clause Important?


12. How To Use Values
    from Other Tables in UPDATE Statements?


13. What Happens If the UPDATE Subquery
    Returns No Rows?


14. What Happens If the UPDATE
    Subquery Returns Multiple Rows?


15. How To Delete an Existing
    Row with DELETE Statements?


16. How To Delete Multiple
    Rows with One DELETE Statement?


17. How To Delete All Rows with
    DELETE Statements?


18. How To Delete All
    Rows with TRUNCATE TABLE Statement?

More...

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.

1. How To Join Two Tables in a
   Single Query?


2. How To Write a Query
   with an Inner Join?


3. How To Define and Use Table
   Alias Names?


4. How To Write
   a Query with a Left Outer Join?


5. How To
   Write a Query with a Right Outer Join?


6. How To Write
   a Query with a Full Outer Join?


7. How To Write an Inner Join
   with the WHERE Clause?


8. How To Name Query Output Columns?


9. What Is a Subquery
   in a SELECT Query Statement?


10. How To Use Subqueries with
    the IN Operators?


11. How To Use Subqueries
    with the EXISTS Operators?


12. How To Use Subqueries in the
    FROM Clause?


13. How To Count Groups
    Returned with the GROUP BY Clause?


14. How To Return the
    Top 5 Rows from a SELECT Query?


15. How To Return the Second
    5 Rows?


16. How To Use UNION
    to Merge Outputs from Two Queries Together?


17. How To Use ORDER BY with UNION
    Operators

More...

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.

1. What Is a SELECT Query Statement?


2. How To Create a Testing
   Table with Test Data?


3. How To Select All
   Columns of All Rows from a Table with a SELECT statement?


4. How To Select Some Specific
   Columns from a Table in a Query?


5. How To Select Some Specific
   Rows from a Table?


6. How To Add More Data to
   the Testing Table?


7. How To Sort the Query
   Output with ORDER BY Clauses?


8. Can the Query Output Be
   Sorted by Multiple Columns?


9. How To Sort Query Output
   in Descending Order?


10. How To Count Rows with the
    COUNT(*) Function?


11. Can SELECT Statements Be Used on
    Views?


12. How To Filter Out
    Duplications in the Returning Rows?


13. What Are Group Functions in Query
    Statements?


14. How To Use Group Functions
    in the SELECT Clause?


15. Can Group
    Functions Be Mixed with Non-group Selection Fields?


16. How To Divide
    Query Output into Multiple Groups with the GROUP BY Clause?


17. How To Apply Filtering
    Criteria at Group Level with The HAVING Clause?


18. How To Count Duplicated
    Values in a Column?


19. Can Multiple Columns
    Be Used in GROUP BY?


20. Can Group Functions
    Be Used in the ORDER BY Clause?

More...

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.

1. What Is SQL Language?


2. What Is Transact-SQL Language?


3. What Is a Transact-SQL Statement?


4. How To Start and End
   Transact-SQL Statements?


5. How To Enter Comments
   in Transact-SQL Statements?


6. What Is a Transact-SQL Statement
   Batch?


7. What Happens to a Statement
   Batch If There Is a Compilation Error?


8. How To Use GO Command in
   "sqlcmd"?


9. How To Create User Messages with PRINT
   Statements?


10. How Many Categories of
    Data Types Used by SQL Server?


11. What Are Exact Numeric
    Data Types?


12. What Are Approximate
    Numeric Data Types?


13. What Are Date and Time Data Types?


14. What Are Character String
    Data Types?


15. What Are Unicode
    Character String Data Types?


16. What Are Binary String
    Data Types?


17. What Are the Differences between
    CHAR and NCHAR?


18. What Are the Differences between
    CHAR and VARCHAR?


19. What Are the Differences between
    DECIMAL and FLOAT?

More...

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.

1. What Is a Constant or Literal?


2. How To Write Character
   String Constants or Literals?


3. What Is a Collation?


4. How To Specify
   the Collation for a Character Data Type?


5. What Happens If Strings Are Casted
   into Wrong Code Pages?


6. How To Find Out What Is the Default
   Collation in a Database?


7. How Fixed Length Strings
   Are Truncated and Padded?


8. How To Enter Unicode Character
   String Literals?


9. How To Enter Binary String Literals?


10. How To Enter Date and Time Literals?


11. Why I Can Not Enter 0.001
    Second in Date and Time Literals?


12. What Happens If Date-Only Values
    Are Provided as Date and Time Literals?


13. What Happens If Time-Only Values
    Are Provided as Date and Time Literals?


14. What Are Out-of-Range
    Errors with Date and Time Literals?


15. What Happens If an Integer
    Is Too Big for INT Date Type?


16. How Extra Digits
    Are Handled with NUMERIC Data Type Literals?


17. How REAL and FLOAT
    Literal Values Are Rounded?


18. What Are the Underflow
    and Overflow Behaviors on FLOAT Literals?

More...

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.

1. What Is an Expression?


2. What Are Arithmetic Operators?


3. What Happens to an
   Arithmetic Operation with Two Different Data Types?


4. How To Convert a Numeric
   Expression from One Data Type to Another?


5. How To Convert
   Numeric Expression Data Types by Assignment Operations?


6. How To Convert Numeric
   Expression Data Types using the CAST() Function?


7. How To Convert Numeric
   Expression Data Types using the CONVERT() Function?


8. How To Convert
   Character Strings into Numeric Values?


9. What Happens
   When Converting Big Values to Integers?


10. What Happens
    When Converting Big Values to NUMERIC Data Types?


11. What Are the
    Mathematical Functions Supported by SQL Server 2005?


12. How To Convert
    Numeric Values to Integers?


13. How To Round a Numeric
    Value To a Specific Precision?


14. How To Generate Random Numbers with
    the RAND() Function?

More...

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.

1. How To Concatenate Two Character
   Strings Together?


2. What Happens When Unicode
   Strings Concatenate with Non-Unicode Strings?


3. How To Convert
   a Unicode Strings to Non-Unicode Strings?


4. What Are
   the Character String Functions Supported by SQL Server 2005?


5. How To Insert New Line
   Characters into Strings?


6. How To Locate and
   Take Substrings with CHARINDEX() and SUBSTRING() Functions?


7. How To Concatenate Two Binary Strings
   Together?


8. Can Binary Strings
   Be Used in Arithmetical Operations?


9. How To Convert Binary Strings
   into Integers?


10. Can Binary Strings
    Be Converted into NUMERIC or FLOAT Data Types?


11. Can Binary Strings
    Be Converted into Character Strings?


12. Can Binary Strings
    Be Converted into Unicode Character Strings?


13. How To Convert
    Binary Strings into Hexadecimal Character Strings


14. What Are Bitwise Operations?

More...

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.

1. How To Add or Remove
   Days on Date and Time Values?


2. Can Date and Time Values
   Be Converted into Integers?


3. Can Integers Be Converted
   into Date and Time Values?


4. Are DATETIME and NUMERIC Values
   Convertible?


5. Can a DATETIME Value Be Subtracted from
   Another DATETIME Value?


6. What Are the Date
   and Time Functions Supported by SQL Server 2005?


7. How To Increment
   or Decrement Parts of DATETIME Values?


8. How To Use DATEADD() Function?


9. How To Calculate
   DATETIME Value Differences Using the DATEDIFF() Function?


10. How To Calculate Age in
    Days, Hours and Minutes?


11. How To Get Month
    and Weekday Names from DATETIME Values?


12. How To Get Parts of
    DATETIME Values as Integers?


13. How To Get Year, Month
    and Day Out of DATETIME Values?


14. What Is the Difference
    Between GETDATE() and GETUTCDATE()?


15. How To Format Time Zone in
    +/-hh:mm Format?


16. How To Format DATETIME
    Values to Strings with the CONVERT() Function?


17. How To Truncate
    DATETIME Values to Dates without Time?


18. How To Set Different
    Parts of a DATETIME Value?

More...

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.

1. What Is a Boolean Value?


2. What Are Conditional Expressions?


3. What Are Comparison Operations?


4. How To Perform Comparison
   on Exact Numbers?


5. How To Perform Comparison
   on Floating Point Numbers?


6. How To Perform Comparison
   on Date and Time Values?


7. How To Perform Comparison
   on Character Strings?


8. What To Test Value Ranges with the
   BETWEEN Operator?


9. What To Test Value Lists with the
   IN Operator?


10. What To Perform Pattern
    Match with the LIKE Operator?


11. How To Use Wildcard
    Characters in LIKE Operations?


12. How To Test Subquery Results with
    the EXISTS Operator?


13. How To Test Values Returned
    by a Subquery with the IN Operator?


14. What Are Logical/Boolean Operations?

More...

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.

1. How To Use "IF ... ELSE IF ...
   ELSE ..." Statement Structures?


2. How To Use "BEGIN ... END" Statement
   Structures?


3. How To Use WHILE Loops?


4. How To Stop a Loop Early with BREAK
   Statements?


5. How To Skip
   Remaining Statements in a Loop Block Using CONTINUE Statements?

More...

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.

1. What Samples and
   Sample Databases Are Provided by Microsoft?


2. How to download
   and install SQL Server 2005 Sample Scripts?


3. How to download
   and install the scaled-down database AdventureWorksLT?


4. How to attach
   AdventureWorksLT physical files to the server?


5. How AdventureWorksLT tables
   are related?


6. How to add an address
   record into AdventureWorksLT?

More...

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.

1. What Are Indexes?


2. How To Create an Index
   on an Existing Table?


3. How To View Existing
   Indexes on an Given Table using SP_HELP?


4. How To View
   Existing Indexes on an Given Table using sys.indexes?


5. How To Drop Existing Indexes?


6. Is the PRIMARY KEY Column of
   a Table an Index?


7. Does the UNIQUE Constraint
   Create an Index?


8. What Is the
   Difference Between Clustered and Non-Clustered Indexes?


9. How To Create a Clustered
   Index?


10. How To Create an Index for
    Multiple Columns?


11. How To Create
    a Large Table with Random Data for Index Testing?


12. How To Measure Performance
    of INSERT Statements?


13. Does Index Slows Down INSERT
    Statements?


14. Does Index Speed Up SELECT Statements?


15. What Happens If You Add a New Index
    to Large Table?


16. What Is the Impact on
    Other User Sessions When Creating Indexes?


17. What Is Index Fragmentation?


18. What Causes Index Fragmentation?


19. How To Defragment Table Indexes?


20. How To Defragment Indexes
    with ALTER INDEX ... REORGANIZE?


21. How To Rebuild Indexes with
    ALTER INDEX ... REBUILD?


22. How To Rebuild All Indexes on
    a Single Table?


23. How To Recreate an Existing
    Index?

More...

SQL Server FAQs - Understanding and
Managing Views

A collection of 21 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.

1. What Are Views?


2. How To Create a View
   on an Existing Table?


3. How To See Existing Views?


4. How To Drop Existing Views from
   a Database?


5. How To Get a List
   of Columns in a View using "sys.columns"?


6. How To Get a List
   of Columns in a View using the "sp_columns" Stored Procedure?


7. How To Get a List of
   Columns in a View using the "sp_help" Stored Procedure?


8. How To Generate
   CREATE VIEW Script on an Existing View?


9. How To Get the Definition
   of a View Out of the SQL Server?


10. Can You Create a View
    with Data from Multiple Tables?


11. Can You Create a View
    using Data from Another View?


12. What Happens If You Delete
    a Table That Is Used by a View?


13. Can You Use ORDER BY When Defining
    a View?


14. How To Modify the Underlying
    Query of an Existing View?


15. Can You Insert Data into a View?


16. Can You Update Data in a View?


17. Can You Delete Data from a View?


18. How To Assign New Column Names
    in a View?


19. How Column Data Types Are
    Determined in a View?


20. How To Bind a View
    to the Schema of the Underlying Tables?


21. How To Create an Index on a View?

More...

Volume 2

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.

1. What Are Stored Procedures?


2. How To
   Create a Simple Stored Procedure?


3. How To Execute
   a Stored Procedure?


4. How
   To List All Stored Procedures in the Current Database?


5. How
   To Drop an Existing Stored Procedure?


6. 
   How To Create a Stored Procedure with a Statement Block?


7. How To End a Stored
   Procedure Properly?


8. 
   How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?


9. 
   How To Get the Definition of a Stored Procedure Back?


10. 
    How To Modify an Existing Stored Procedure?


11. How To
    Create Stored Procedures with Parameters?


12. How
    To Provide Values to Stored Procedure Parameters?


13. What Are the
    Advantages of Passing Name-Value Pairs as Parameters?


14. 
    Can You Pass Expressions to Stored Procedure Parameters?


15. 
    How To Provide Default Values to Stored Procedure Parameters?


16. 
    How To Define Output Parameters in Stored Procedures?


17. 
    How To Receive Output Values from Stored Procedures?


18. How To
    Create a Local Temporary Stored Procedure?


19. Can Another
    User Execute Your Local Temporary Stored Procedures?

More...

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.

1. What Are User Defined
   Functions?


2. 
   What Are the Differences between User Defined Functions and Stored Procedures?


3. How
   To Create a Simple User Defined Function?


4. How To
   Use User Defined Functions in Expressions?


5. How
   To List All User Defined Functions in the Current Database?


6. How To
   Drop an Existing User Defined Function?


7. 
   How To Generate CREATE FUNCTION Script on an Existing Function?


8. 
   How To Get the Definition of a User Defined Function Back?


9. How To
   Modify an Existing User Defined Function?


10. How
    To Create User Defined Functions with Parameters?


11. 
    How To Provide Values to User Defined Function Parameters?


12. Can You
    Pass Expressions to Function Parameters?


13. 
    How To Provide Default Values to Function Parameters?


14. How
    Many Categories of Functions based Their Return Modes?


15. How Many
    Ways to Create Table-Valued Functions?


16. 
    How To Create an Inline Table-Valued Function?


17. How
    To Create an Multi-Statement Table-Valued Function?

More...

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.

1. What Is a Schema
   in SQL Server 2005?


2. How To Create
   a New Schema in a Database?


3. How
   To List All Schemas in a Database?


4. How To Create
   a New Table in a Given Schema?


5. How
   To Transfer an Existing Table from One Schema to Another Schema?


6. 
   How To List All Objects in a Given Schema?


7. What Is the
   Default Schema of Your Login Session?


8. Who Is the Owner of
   a Schema?


9. 
   How To Change the Ownership of a Schema?


10. What Happens
    If You Are Trying to Access a Schema Not Owned by You?


11. How To Drop
    an Existing Schema?

More...

SQL Server FAQs - Introduction
to Triggers

A collection of 20 FAQs on creating and managing triggers. Clear explanations and
tutorial exercises are provided on creating DML and DDL triggers; altering, dropping,
enabling and disabling triggers; accessing inserted and deleted records; overriding
DML statements.

1. What Are Triggers?


2. What Are the Basic Features
   of a Trigger?


3. How To Create a
   Simple Table to Test Triggers?


4. How To Create
   a DML Trigger using CREATE TRIGGER Statements?


5. How To Test a DML Trigger?


6. How To List All
   Triggers in the Database with sys.triggers?


7. How To
   Modify Existing Triggers using "ALTER TRIGGER"?


8. How To Delete
   Existing Triggers using "DROP TRIGGER"?


9. 
   How To Get the Definition of a Trigger Back?


10. How To Disable
    Triggers using "DISABLE TRIGGER"?


11. How
    To Create a Trigger for INSERT Only?


12. How To
    See the Event List of an Existing Trigger using sys.trigger_events?


13. How
    To Access the Inserted Record of an Event?


14. How To
    Access the Deleted Record of an Event?


15. How
    To Improve the Trigger to Handle NULL Values?
16. What Happens to a Trigger with Multiple Affected Rows?
17. How To Override DML Statements with Triggers?
18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
19. Can You Roll Back the DDL Statement in a Trigger?
20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?

More...
SQL Server FAQs - Introduction to Cursors

A collection of 9 FAQs on working with cursors. Clear explanations and tutorial
exercises are provided on declaring cursor names and cursor variables; open and
close cursors; looping through cursor result set; backward scrolling; dynamic cursors.

1. What Are Cursors?
2. How To Declare a Cursor with "DECLARE ... CURSOR"
3. How To Execute the Cursor Queries with "OPEN" Statements
4. How To Fetch the Next Row from a Cursor with a "FETCH" Statement
5. How To Transfer Data from a Cursor to Variables with a "FETCH" Statement?
6. How To Loop through the Result Set with @@FETCH_STATUS?
7. How To Declare and Use Cursor Variables?
8. How To Create a Scrollable Cursor with the SCROLL Option?
9. How To Create a Dynamic Cursor with the DYNAMIC Option?

More...

SQL Server FAQs - Introduction to NULL Values

A collection of 11 FAQs on working with NULL values. Clear explanations and tutorial
exercises are provided on assigning NULL values to columns, variables and parameters;
evaluating expressions with NULL values; testing NULL values; ISNULL() and NULLIF()
functions.

1. What Are NULL Values?
2. How To Assign NULL Values to Variables or Columns?
3. What Happens If NULL Values Are Involved in Arithmetic Operations?
4. What Happens If NULL Values Are Involved in String Operations?
5. What Happens If NULL Values Are Involved in Datetime Operations?
6. What Happens If NULL Values Are Involved in Bitwise Operations?
7. What Happens If NULL Values Are Involved in Comparison Operations?
8. What Happens If NULL Values Are Involved in Boolean Operations?
9. How To Test NULL Values Properly?
10. How To Replace NULL Values in Expressions using ISNULL()?
11. How To Replace Given Values with NULL using NULLIF()?

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.

1. What Is the Security Model Used in SQL Server 2005?
2. What Are Security Principals Used in SQL Server 2005?
3. What Is the Security Principal at the Server Level That Represents Your Session?
4. What Is the Security Principal at the Database Level That Represents Your Session?
5. How To Create a New Login Name in SQL Server?
6. How To Verifya Login name with SQLCMD Tool?
7. How To List All Login Names on the Server?
8. How To Change the Password of a Login Name?
9. How To Change a Login Name?
10. How To Disable a Login Name?
11. How To Delete a Login Name?
12. How To Create a User Name in a Database?
13. How To List All User Names in a Database?
14. How To Find the Login Name Linked to a Given User Name?
15. How To Verify a User name with SQLCMD Tool?
16. How To Change the Name of a Database User?
17. How To Delete an Existing Database User?

Reporting Services Analyst InfoCenter

Reporting Services Analyst InfoCenter
Analysts create reports and data models for personal use and for other people in
an organization. Analysts can be data processing professionals, but might also be
persons responsible for analyzing enterprise data in the course of other job functions.
Often, an analyst creates reports to identify trends, opportunities, problems, or
project characteristics. Analysts might be interested in data mining and the features
of SQL Server Analysis Services.
In Reporting Services, analysts are responsible for creating reports that include
aggregated data, expressions, and parameters. Analysts also design report models
for others in an organization to organize data into meaningful entities that facilitate
report creation.
Job titles that map to the Reporting Services analyst role include
Data Modeler, Business Analyst, or Financial Analyst, to name a few.
To help you quickly get started with the Reporting Services tools and documentation,
the following topics provide key information about creating and publishing reports
and report models. To view additional information in the documentation, from these topics
you can sync to the Table of Contents in SQL Server Books Online, where you will
find related topics on advanced features in SQL Server 2005 Reporting Services.

Concepts

Introducing Reporting Services
Reports and Report Definitions
Report Models
Ways to Create a Report
Working with Data in Reporting Services
Understanding Data Regions
Working with Report Items
Understanding Report Layout and Rendering

Defining Report Data

Connecting to a Data Source
Defining Report Datasets for a SQL Server Relational Database
Working with Parameters in Reporting Services
Filtering Data in a Report
Using Expressions in Reporting Services

Designing and Publishing Reports
Designing the Report Layout
Adding Interactive Features
Previewing Reports
Publishing Reports to a Production Environment

Designing Report Models
Working with Model Designer
Creating a Report Model Project

Reporting Services Administrator InfoCenter

Reporting Services Administrator InfoCenter
A report server administrator installs, configures, secures, and maintains a report
server deployment. A report server administrator determines report access and availability
by specifying role assignments, report execution and data source property settings,
and determining the availability of optional features. The administrator may also
be called upon to create a disaster recovery plan, troubleshoot problems, and monitor
and optimize report server performance. A report server administrator must also
understand the network topology and configuration of the computers used to host
and access SQL Server 2005 Reporting Services components.

Job titles that map to the report server administrator role include Database Administrator,
Report Server Administrator (Corporate IT), Security Expert, or Lead Database Administrator.

To help you be as effective as possible, the following links jump to those topics
considered most useful for the maintenance and optimization of a Reporting Services
installation. To view additional information in the documentation, from these topics
you can sync to the Table of Contents in SQL Server Books Online, where you will
find related topics on advanced features in Reporting Services.

Concepts

Report Server
Reports and Report Definitions
Linked Reports
Report Snapshots
Report Server Folder Hierarchy
Connections and Accounts in a Reporting Services Deployment

Configuration
Server Deployment Checklist
Report Deployment Checklist

Configuring Reporting Services Components
Configuring a Report Server for Internet Access

Security
Securing Reporting Services
Managing Permissions and Security for Reporting Services

Server Administration
Configuring Service Accounts and Passwords in Reporting
Services
Administering a Report Server Database
Managing and Working With Published Reports

Reporting Services Developer InfoCenter

SQL Server 2005 Reporting Services (SSRS) provides the developer several programming interfaces that can be used to extend the functionality of Reporting Services। These interfaces include Web services for managing and executing reports, a WMI provider interface for configuration management, a set of Microsoft ।NET Framework managed APIs for creating Reporting Services extensions, and URL access for easy integration with Web portals and applications.
The following lists provide links to a number of object models and APIs for use in programmatically extending the functionality of Reporting Services.

Concepts

Introducing Reporting Services Programming
Report Server Web Service
URL Access
Reporting Services Extensions
Report Definition Language

Web Service Programming
Introducing the Report Server Web Service
Accessing the SOAP API
Building Applications Using the Web Service and the .NET
Framework

URL Access Programming
Choosing Between URL Access and SOAP
Integrating Reporting Services Using URL Access

Programming Extensions
Security Considerations for Extensions
Introducing Data Processing Extensions
Introducing Delivery Extensions
Introducing Rendering Extensions
Introducing Reporting Services Security Extensions

Administrative Programming
Introducing the Reporting Services WMI Provider
Reporting Services WMI Provider Library

Report Programming
Generating Report Definition Language Programmatically
Using Custom Assemblies with Reports

SQL Server Reporting Services FAQ

Question 1: How to create a custom report template in Reporting Services?
Answer: A report template also exists as a normal report (RDL file). If you want to customize your own report templates, you can put the previously prepared reports (which will be used as new templates) in a specific folder. By default, the path is:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Then, to use these templates, you can right click on the Reports folder in your project and select New Item. You will then see the name of the report templates you added in the above folder

Question 2: Does SQL Server 2005 Reporting Services support horizontal tables which have fixed rows and dynamic columns?

Answer: Note: This Q&A is only for SQL Server 2005 Reporting Services.
By default, there is no native "horizontal table" report item. We can use Matrix to simulate simple horizontal tables:
1. Add a matrix to your report.
2. Add static rows to the matrix: Right-click in the Data cell and select "Add Row". Repeat for the number of fixed rows you want.
3. Drag fields into the Data cells:

You'll notice that the design tool automatically wraps your field reference in the "First" aggregate (e.g. =First(Fields!City.Value)). Since you're doing this in the context of a matrix, the design tool is ensuring that the expression is meaningful even in the context of a subtotal or if the matrix is showing aggregated data rather than detail data.
Note: Please do NOT remove the aggregate expression.
4. Add a column grouping:

1) Right-click on the column header and select "Edit Group".
2) Enter this for the group expression: =RowNumber(Nothing). This will cause the matrix to give you one column per row of data. Since horizontal tables can end up rather wide, you may want your table wrap around to the next "line" after a specific number of columns.

5. Add a list to your report and drag the table into it.
6. Group by a number of rows:
1) Right-click on the list and select Properties. Then click on Edit Details Group.
2) Enter this for the group expression: =Ceiling(RowNumber(Nothing)/3)
This will cause the list to group on every three rows. So you'll get a separate table for every three rows.

7. Adjust the group expression in the matrix:
Edit the column group expression in your matrix and change the RowNumber argument to be the list group name. For example: =RowNumber("list1_Details_Group")

More Information: SQL Server 2008 Reporting Services has a new Date Region control named Tablix, which can be used for the same requirement easily. For more information about Tablix, Please refer to the documents below:

Question 3: How to reset the page number back to 1 every time the report gets a group break?

Answer: Resetting the page number on group breaks is not natively supported. There is a workaround: you can track group breaks in a shared variable, and reset this variable to 1 when reaches a new group break.

Here are the steps:

1. Make sure there's a textbox in the report which contains the group expression.
2. Add shared variables to track the current group and page offset:

Shared offset as Integer
Shared currentgroup as Object

3. Add a custom function to set the shared variables and retrieve the group page number:

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function

4. Use the function in the page header or footer:

=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)



Question 4: How to open the drill- through report in a new browser window?

Answer: Generally, if you use the "Jump to URL" option for a textbox, it will redirect to the specific URL in the same browser when the user clicks on the textbox.

To open the link in a new window, you can use syntax similar to this:

="javascript:void window.open ('http://servername/reportserver?/freportfolder/ReportName&Parameter1=" & Fields!Field1.Value & "&Field2=" & Fields!Field2.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

Question 5: How to use multiple datasets?

Answer:You can combine two or more datasets in one report. There are multiple options to achieve this:

• If the data comes from different tables in the same database, join the datasets based on a common key present in both tables.

• Use the Linked Server functionality.

For more information, see Accessing External Data

• Use the OpenRowSet functionality to join data from another database server into the current query.

For more information, see OPENROWSET (Transact-SQL)


Question 6: How to upgrade report from SQL Server 2000 to SQL Server 2005?

Answer:You can use the following steps:

1. Back up ReportServer and ReportServerTemDB.
2. Back up encryption keys from Reporting services 2000 by using RsKeyMgmt.
3. Restore the databases backed up from step 1.
4. Connect to newly restored databases from step 3 from Reporting Services 2005 Configuration manager and database setup.
5. By using the provided credentials, Reporting Services will connect to new databases and perform the upgrade operations.
6. Confirm Reporting services upgrading the database and granting adequate rights to the credential account.
7. Restore backed up keys from step 2. Confirm that everything is displayed as green now.

Question 7: Is Report Builder available in SharePoint Integrated Mode?
Reporting Services and SharePoint Technology Integration
Answer:Yes. After integrating with SharePoint, what you need to do is to add Report Builder Model through Content Types:

1. Open SharePoint website as administrator.
2. Click Report Center website.
3. Click Site Actions list.
4. Select Site Settings and then select Modify Pages Library Settings.
5. On Content Types part, click Add from existing site content types.
6. Select Report Server Content Types from site content types drop-down list.
7. Select Report Builder.
8. Click OK to finish.

After that, you will be able to use Report Builder.

Please Note: Currently, Report Builder 2.0 has been released as a stand-alone installation for SQL Server 2008 Reporting Service. However, Report Builder 1.0 can still be used for SharePoint as a "click-once" application. This function will be included in the future service pack of SQL Server 2008.

Question8: How to manage user to view reports in SharePoint integrated mode?
Answer:In SharePoint website, if the default security settings do not meet your requirements, you can create new permission levels for the specific report server items or operations. Custom security settings are helpful if restricting access to a particular report is required.

To customize permissions of viewing reports, please refer to the following documents:

Question 9: How to refer the reports on the Report Server in SharePoint Services?
Answer:After integrating SQL Server Reporting Services with SharePoint Services, you can refer a SSRS report in SharePoint Services. Here is the steps:

1. Install the SQL Server Reporting Services Add-in for SharePoint

For more information, see How to: Install or Uninstall the Reporting Services Add-in

2. Refer to the documents below for using SQL Server Reporting Services Add-in:
Using the Report Viewer Web Part on a SharePoint Site

Question 10: How to integrate SQL Server Reporting Services and SharePoint Services?
Answer:1. Check the requirements for running reporting service in SharePoint integrated mode:
Requirements for Running Reporting Services in SharePoint Integrated Mode
2. Follow the documents below to configure the server:
Configuring Reporting Services for SharePoint 3.0 Integration
For more information for Reporting Services and SharePoint Technology Integration, please refer to:
Reporting Services and SharePoint Technology Integration

Question 11: How to deploy reports to SQL Server Reporting Services in SharePoint Services integrated mode?
Answer:You can use the following steps:
1. Create a web application in SharePoint with Report Center template.
2. Open Business Intelligence Development Studio and modify report solution properties as below:

TargetDataSourceFolder



Question 12: How to enable the Select All option for a multi-value parameter?

Problem:

When a user defines an available values list for a multi-value parameter, a Select ALL option on the report parameter drop-down list automatically appears. However, if you don’t want to make the parameter multi-value, how to enable the Select All option for the query parameter?

Solution:

Since the single-value query parameter does not provide the Select All option, you cannot query the data without any limitations. Sometimes, the end user may want to ignore the condition for the parameterized query. In this case, you can add the "Select All" option to the parameter dataset by using SQL statement.

Here are the detailed steps:

1. Create a dataset for the parameter using the following query string:

SELECT ‘ALL’
UNION ALL
SELECT Field from table

2. Edit your base dataset query:

The original query:
… AND Field=@Parameter…
Change it to this:
…AND (( Field=@Parameter ))OR(@Parameter=’ALL’))…

3. Set the report parameter.

1) Set the available values as the field created in Step 1.
2) Set the default value of parameter to All.

Question 13: How to improve PDF quality of the report exported in Reporting Services 2005?

Answer: The PDF renderer in Reporting Services 2005 resizes all images that it is given at 96 DPI no matter what DPI the image is when you pass it to the renderer. That means that a 300 DPI image or even a 600 DPI image will be sized in the PDF as if it is only 96 DPI. In other words, your high DPI image may render larger than expected.

Despite sizing the images as if they were 96 DPI, the PDF renderer appears to render higher DPI images at a higher quality than 96 DPI. Even if the sizing being wrong, the image actually is rendering at a higher quality.

A necessary workaround is to size the image to the proper number of inches based on 96 DPI calculations. Then use Bitmap.SetResolution to set the images to at least 300 DPI. That may provide a higher quality image that is the proper number of pixels to fit properly in the report.

Question 14: How to enable anonymous access to Report Server, and is there any security consideration?
Answer: Please note that when using anonymous access, every person accesses the reports under the anonymous user context, and no one will have rights to administer Reporting Services through Report Manager. This is the reason why it is not recommended to use anonymous access in Reporting Services.

In order to differentiate between users, we need to either disable anonymous access or use a custom security extension. We have a sample using Forms Authentication:
Using Forms Authentication in Reporting Services

In some special situations, it may be necessary to use anonymous access. For security considerations, we can use the steps as below:

1. Add the anonymous user to the System User role
2. Create an Item-level role with all of the tasks you want this user to be able to use, and then assign the anonymous user to that role at the root level.
3. Check permissions for the anonymous user on folders that SRS uses:
For example:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services (and subfolders)
C:\\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files
The temp folder for the anonymous user

Note: Step1 & Step2 can be accomplished through Report Manager only if anonymous access is disabled and the logged-on user is a member of the System Administrator role.

Question 15: Error of EXECUTE permission denied on object 'xp_sqlagent_notify'.

Problem:
In some special cases, you may receive the following error when trying to select the option execution from a report:

EXECUTE permission denied on object 'xp_sqlagent_notify', database '######', schema '###'.

Cause:

Usually, this error is caused by the user missing the "RSExecRole" role in the Master and MSDB system databases.

Solution:

Reporting Services uses a predefined database role called "RSExecRole" to grant report server permissions to the report server database. If you get the error mentioned above, try to create and provision the "RSExecRole" role in Master and MSDB system database.

For more information, see:

How to: Create the RSExecRole
http://technet.microsoft.com/en-us/library/cc281308.aspx

Question 16: How to combine connecting string via parameter?
Answer:You can include parameter reference in the data source connection string and allow the user to choose a data source for the report. For example, with an expression-based connection string, a user who is running a sales report can select a data source for a particular county before running the report.
The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:
="Data Source=" & Parameters! ServerName.Value & "; initial catalog=AdventureWorks"

Question 17: How to migrate SQL Server 2008 Reporting Services to another computer?

Answer: The migration process for Reporting Services includes manual and automated steps. The following tasks are part of a report server migration:

1. Back up database, application, and configuration files.
2. Back up the encryption key.
3. Install a new instance of SQL Server 2008. If you are using the same hardware, you can install SQL Server 2008 side-by-side your existing SQL Server 2000 or 2005 installation. Be aware that if you do this, you might need to install SQL Server 2008 as a named instance.
4. Move the report server database and other application files from your SQL Server 2000 or 2005 installation to your new SQL Server 2008 installation.
5. Move any custom application files to the new installation.
6. Configure the report server.
7. Edit RSReportServer.config to include any custom settings from your previous installation.
8. Optionally, configure custom Access Control Lists (ACLs) for the new Reporting Services Windows service group.
9. Test your installation.
10. Remove unused applications and tools after you have confirmed that the new instance is fully operational.

If you are reusing an existing report server database that you created in a previous installation, be aware that there are restrictions on the editions of SQL Server you use to host the report server database.