SQL Server Database Tutorials

Q

Where to find SQL Server database server tutorials?

✍: FYIcenter.com

A

Here is a collection of tutorials, tips and FAQs for SQL Server DBA and SQL Server database application developers. Clear explanations and sample scripts provided can be used as learning tutorials or interview preparation guides.

It doesn't matter whether you are a beginner or an experienced SQL Server DBA or developer, browse through our SQL Server DBA FAQ and tips. They will always help you to improve your skills and find some good ideas to solve problems in your daily tasks.

So far, Our SQL Server team have written over 200 questions and answers to share with you. Each one of them answers one commonly asked SQL Server question with a short, but precise and simple example.

Downloading and Installing SQL Server 2005 Express Edition

What Is Microsoft SQL Server in SQL Server

Downloading SQL Server 2005 Express Edition in SQL Server

System Requirements for SQL Server 2005 Express Edition in SQL Server

Missing .NET Framework 2.0 for SQL Server 2005 Express Edition in SQL Server

Downloading and Installing Microsoft .NET Framework Version 2.0 in SQL Server

mscorsvw.exe - Process - Microsoft .NET Framework NGEN in SQL Server

Installing SQL Server 2005 Express Edition in SQL Server

Verifying SQL Server Running Status in SQL Server

sqlservr.exe - Process - SQL Server (SQLEXPRESS) in SQL Server

Downloading and Installing SQL Server Management Studio Express in SQL Server

Connecting SQL Server Management Studio Express To a SQL Server in SQL Server

Running Queries with SQL Server Management Studio Express in SQL Server

Running Queries with 'sqlcmd' Tool in SQL Server

GO - Sending a Statement Batch from "sqlcmd" in SQL Server

Downloading and Installing SQL Server 2005 Books Online in SQL Server

Running SQL Server 2005 Books Online on Your Local System in SQL Server

Installing Sample Scripts and Databases in SQL Server

Sample Databases Are Provided by Microsoft in SQL Server

Downloading and Installing SQL Server Sample Scripts in SQL Server

AdventureWorksLT - Downloading and Installing the Sample Database in SQL Server

Attaching AdventureWorksLT Physical Files to the Server in SQL Server

AdventureWorksLT Entity Relation Diagram in SQL Server

Adding Address Records into AdventureWorksLT in SQL Server

Getting Started with Transact-SQL Statements in SQL Server

"CREATE DATABASE" Statement - Creating New Databases in SQL Server

"CREATE TABLE" Statement - Creating New Tables in SQL Server

"INSERT" and "UPDATE" Statements - Inserting and Updating Data In Tables in SQL Server

"SELECT" Statements - Reading the Data In a Table in SQL Server

"CREATE LOGIN" Statements - Creating a Login in SQL Server

"CREATE USER" Statements - Creating a User in SQL Server

"CREATE VIEW/PROCEDURE" Statements - Creating a View and a Stored Procedure in SQL Server

"GRANT EXECUTE" Statements - Granting EXECUTE permission in SQL Server

"DROP" Statements - Deleting database objects in SQL Server

Database Engine Tutorials from SQL Server 2005 Books Online

Managing Databases and Physical Files in SQL Server

What Is a Database in SQL Server

Simplest Way To Create New Databases in SQL Server

"USE" - Setting the Current Database in SQL Server

"DROP DATABASE" - Deleting Databases in SQL Server

Database in Use When Dropping a Database in SQL Server

Getting a List of All Databases on the Server in SQL Server

Location of Database Files in SQL Server

Creating Databases with Specified Physical Files in SQL Server

Renaming Database Names in SQL Server

Database in Use When Renaming a Database in SQL Server

ONLINE/OFFLINE - Database States in SQL Server

OFFLINE - Taking a database offline in SQL Server

Moving Database Physical Files to New Locations in SQL Server

READ_ONLY/READ_WRITE - Database Update Options in SQL Server

SINGLE_USER/MULTI_USER - Database User Access Options in SQL Server

System Databases Used by SQL Servers in SQL Server

Managing Tables and Columns in SQL Server

What Is a Table in SQL Server

DDL (Data Definition Language) Statements for Tables? in SQL Server

CREATE TABLE - Creating New Tables in SQL Server

"sys.tables" - Getting a List of All Tables in SQL Server

"sys.columns" - Getting a List of Columns in a Table in SQL Server

"sp_columns" - Getting a List of Columns in a Table in SQL Server

"sp_help" - Getting a List of Columns in a Table in SQL Server

Generating CREATE TABLE Script on Existing Tables in SQL Server

"SELECT ... INTO" - Creating New Tables With Queries in SQL Server

"ALTER TABLE ... ADD" - Adding New Columns to Existing Tables in SQL Server

"ALTER TABLE ... DROP COLUMN" - Deleting Existing Columns in SQL Server

"sp_rename ... 'COLUMN'" - Renaming an Existing Column in SQL Server

Renaming an Existing Column with Management Studio in SQL Server

"ALTER TABLE ... ALTER COLUMN" - Changing Column Data Type in SQL Server

"sp_rename ... 'OBJECT'" - Renaming Existing Tables in SQL Server

"DROP TABLE" - Deleting Existing Tables in SQL Server

Understanding and Managing Indexes in SQL Server

What Are Indexes in SQL Server

CREATE INDEX - Adding an Index on an Existing Table in SQL Server

SP_HELP - Viewing Existing Indexes on an Given Table in SQL Server

sys.indexes - Viewing Existing Indexes on an Given Table in SQL Server

DROP INDEX - Removing Existing Indexes in SQL Server

Primary Key - Default Indexes of Tables in SQL Server

UNIQUE Constraint Creating Default Index in SQL Server

Difference Between Clustered and Non-Clustered Indexes in SQL Server

CREATE CLUSTERED INDEX - Adding Clustered Indexes in SQL Server

Creating an Index for Multiple Columns in SQL Server

Creating a Large Table with Random Data for Indexes in SQL Server

Measuring Performance of INSERT Statements in SQL Server

Index Slowing Down INSERT Statements in SQL Server

Index Speeding Up SELECT Statements in SQL Server

Adding a New Index to a Large Table in SQL Server

CREATE INDEX - Impact on Other User Sessions in SQL Server

What Is Index Fragmentation in SQL Server

What Causes Index Fragmentation in SQL Server

Defragmenting Table Indexes in SQL Server

"ALTER INDEX ... REORGANIZE" - Defragmenting Indexes in SQL Server

"ALTER INDEX ... REBUILD" - Defragmenting Indexes in SQL Server

Rebuilding All Indexes on One Table in SQL Server

DROP_EXISTING - Recreating an Existing Index in SQL Server

Understanding and Managing Views in SQL Server

What Are Views in SQL Server

CREATE VIEW - Creating a View on an Existing Table in SQL Server

sys.views - List of Existing Views in SQL Server

DROP VIEW - Deleting Existing Views in SQL Server

"sys.columns" - Getting a List of Columns in a View in SQL Server

"sp_columns" - Getting a List of Columns in a View in SQL Server

"sp_help" - Getting a List of Columns in a View in SQL Server

Generating CREATE VIEW Scripts on Existing Views in SQL Server

sys.sql_modules - Getting View Definitions Back in SQL Server

Creating a View with Data from Multiple Tables in SQL Server

Creating a View with Data from Another View in SQL Server

Deleting a Table That Is Used by a View in SQL Server

Using ORDER BY to Define a View in SQL Server

ALTER VIEW - Modifying Existing Views in SQL Server

Inserting Data into a View in SQL Server

Updating Data in a View in SQL Server

Deleting Data from a View in SQL Server

Assigning New Column Names in a View in SQL Server

Determining Data Types of View Columns in SQL Server

SCHEMABINDING - Binding Views to Underlying Tables in SQL Server

Creating an Index on a View in SQL Server

Using INSERT, UPDATE and DELETE Statements in SQL Server

DML (Data Manipulation Language_Statements in SQL Server

Testing Table for DML Statements in SQL Server

"INSERT INTO" - Inserting a New Row into a Table in SQL Server

DEFAULT - Using Column Default Values in INSERT Statements in SQL Server

Providing Column Names in INSERT Statements in SQL Server

Duplicate Key Error on Primary Key Columns in SQL Server

Inserting Multiple Rows with One INSERT Statement in SQL Server

Updating Values with UPDATE Statements in SQL Server

Updating Multiple Rows with One UPDATE Statement in SQL Server

Using Old Values to Define New Values in UPDATE Statements in SQL Server

Importance of Column Order in the SET Clause in Update Statements in SQL Server

Using Values from Other Tables in UPDATE Statements in SQL Server

UPDATE Subquery Returning No Rows in SQL Server

UPDATE Subquery Returning Multiple Rows in SQL Server

Deleting an Existing Row with DELETE Statements in SQL Server

Deleting Multiple Rows with One DELETE Statement in SQL Server

Deleting All Rows with DELETE Statements in SQL Server

Deleting All Rows with TRUNCATE TABLE Statement in SQL Server

Using SELECT Statements with Joins and Subqueries in SQL Server

Joining Two Tables in a Single Query in SQL Server

"INNER JOIN ... ON" - Writing Queries with Inner Joins in SQL Server

Defining and Using Table Alias Names in SQL Server

"LEFT OUTER JOIN ... ON" - Writing Queries with Left Outer Joins in SQL Server

"RIGHT OUTER JOIN ... ON" - Writing Queries with Right Outer Joins in SQL Server

"FULL OUTER JOIN ... ON" - Writing Queries with Full Outer Joins in SQL Server

Writing Inner Joins with the WHERE Clause in SQL Server

"AS" - Naming Query Output Columns in SQL Server

What Is a Subquery in a SELECT Query Statement in SQL Server

Using Subqueries with the IN Operators in SQL Server

Using Subqueries with the EXISTS Operators in SQL Server

Using Subqueries in the FROM Clause in SQL Server

Counting Groups Returned with the GROUP BY Clause in SQL Server

"TOP" - Return the Top 5 Rows from a SELECT Query in SQL Server

Returning the Second 5 Rows from a Query in SQL Server

UNION - Merging Outputs from Two Queries Together in SQL Server

Using ORDER BY with UNION Operators in SQL Server

Using SELECT Statements and GROUP BY Clauses in SQL Server

What Is a SELECT Query Statement in SQL Server

Testing Table for SELECT Statements in SQL Server

Selecting All Columns of All Rows from a Table in SQL Server

Selecting Some Specific Columns from a Table in SQL Server

Selecting Some Specific Rows from a Table in SQL Server

Adding More Test Data for Query Statements in SQL Server

Sorting Query Output with ORDER BY Clauses in SQL Server

Sorting Query Output by Multiple Columns in SQL Server

Sorting Query Output in Descending Order in SQL Server

Counting Rows with the COUNT(*) Function in SQL Server

Using SELECT Statements on Views in SQL Server

Filtering Out Duplications in the Returning Rows in SQL Server

Group Functions in Query Statements in SQL Server

Using Group Functions in the SELECT Clause in SQL Server

Mixing Group Functions with Non-group Selection Fields in SQL Server

"GROUP BY" - Dividing Query Output into Multiple Groups in SQL Server

HAVING - Apply Filtering Criteria at Group Level in SQL Server

How To Count Duplicated Values in a Column? in SQL Server

Using Multiple Columns in the GROUP BY Clause in SQL Server

Using Group Functions in the ORDER BY Clause in SQL Server

Creating and Managing Triggers in SQL Server

What Are Triggers in SQL Server

Basic Features of a Trigger in SQL Server

Creating a Simple Table to Test Triggers in SQL Server

"CREATE TRIGGER" - Creating a DML Trigger in SQL Server

Testing DML Triggers in SQL Server

sys.triggers - Listing All Triggers in the Database in SQL Server

"ALTER TRIGGER" - Modifying Existing Triggers in SQL Server

"DROP TRIGGER" - Deleting Existing Triggers in SQL Server

sys.sql_modules - Getting Trigger Definitions Back in SQL Server

"DISABLE TRIGGER" - Disabling Triggers in SQL Server

Creating Triggers for INSERT Statements Only in SQL Server

sys.trigger_events - Event List of an Existing Trigger in SQL Server

"INSERTED" - New Record of an DML Event Instance in SQL Server

"DELETED" - Old Record of an DML Event Instance in SQL Server

Improving the Trigger to Handle NULL Values in SQL Server

Triggers with Multiple Affected Rows in SQL Server

"INSTEAD OF" - Overriding DML Statements with Triggers in SQL Server

"CREATE TRIGGER" - Creating a DDL Trigger in SQL Server

Rolling Back the DDL Statement in a Trigger in SQL Server

Creating a Logon Trigger in Express Edition in SQL Server

Creating and Managing Schemas in SQL Server

What Is a Schema in SQL Server 2005 in SQL Server

"CREATE SCHEMA" - Creating a New Schema in a Database in SQL Server

sys.schemas - Listing All Schemas in a Database in SQL Server

Creating a New Table in a Given Schema in SQL Server

Transferring Tables from One Schema to Another in SQL Server

"sys.objects" - Listing All Objects in a Given Schema in SQL Server

Default Schema of Your Login Session in SQL Server

Who Is the Owner of a Schema in SQL Server

"ALTER AUTHORIZATION" - Changing the Ownership of a Schema in SQL Server

Accessing a Schema Not Owned by You in SQL Server

"DROP SCHEMA" - Dropping an Existing Schema in SQL Server

Managing Security, Login and User in SQL Server

Security Model Used in SQL Server 2005 in SQL Server

Security Principals Used in SQL Server 2005 in SQL Server

Suser_Sname() - Sever Level Security Principal of Your Session in SQL Server

User_Name() - Database Level Security Principal of Your Session in SQL Server

"CREATE LOGIN" - Creating a New Login Name in SQL Server

Verifying a Login Name with SQLCMD Tool in SQL Server

sys.server_principals - Listing All Login Names in SQL Server

"ALTER LOGIN" - Changing the Password of a Login Name in SQL Server

"ALTER LOGIN" - Changing a Login Name in SQL Server

"ALTER LOGIN" - Disabling a Login Name in SQL Server

"DROP LOGIN" - Deleting a Login Name in SQL Server

"CREATE USER" - Creating a User Name in a Database in SQL Server

sys.database_principals - Listing All User Names in SQL Server

Finding the Login Name Linked to a Given User Name in SQL Server

Verifying a User Name with SQLCMD Tool in SQL Server

"ALTER USER" - Changing the Name of a Database User in SQL Server

"DROP USER" - Deleting a Database User in SQL Server

2016-10-18, 589👍, 0💬