Read the most frequently asked 100 top MS SQL Server multiple choice questions and answers PDF for freshers and experienced. MS SQL Server objective questions and answers pdf download free..
MS SQL Server Multiple Choice Questions and Answers PDF Experienced Freshers
1 :: What is Microsoft SQL Server?
Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It runs on Windows systems and uses Transact-SQL as the query language.
Microsoft SQL Server release history:
* 1993 - SQL Server 4.21 for Windows NT
* 1995 - SQL Server 6.0, codenamed SQL95
* 1996 - SQL Server 6.5, codenamed Hydra
* 1999 - SQL Server 7.0, codenamed Sphinx
* 1999 - SQL Server 7.0 OLAP, codenamed Plato
* 2000 - SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
* 2003 - SQL Server 2000 64-bit, codenamed Liberty
* 2005 - SQL Server 2005, codenamed Yukon (version 9.0)
* 2005 - SQL Server 2005 Express Edition, restricted free version
2 :: How to download Microsoft SQL Server 2005 Express Edition?
Microsoft SQL Server 2005 Express Edition is the free version of the Microsoft SQL Server 2005. If you are interested to try SQL Server 2005, you should follow this tutorial to download Microsoft SQL Server 2005 Express Edition:
1. Go to the Microsoft SQL Server 2005 Express Edition download page.
2. Go to the Files in This Download section, and click Download button next to the "SQLEXPR.EXE - 53.5 MB" file. The File Download box shows up.
3. Save the download file to C:temp directory. When the download is done, you should get the following file:
Name: SQLEXPR.EXE
Description: Microsoft SQL 2005 Server Express Edition
Location: C:temp
Size: 56,105,688 bytes
Version: 9.0.1399.6
3 :: System Requirements for SQL Server 2005 Express Edition?
The following system requirements cover the SQL Server 2005 Express Edition:
Processor
32-bit Processor of 600-megahertz (MHz) or faster
Operating System
Windows XP with Service Pack 2 or later
Windows 2000 Professional with SP4
Windows 2000 Server with Service Pack 4 or later
Windows Server 2003 Standard, or Enterprise SP1
Windows Server 2003 Web Edition SP1
Windows Small Business Server 2003 with SP1
Vista Home Basic and above
Framework
.NET Framework 2.0
Memory
512 megabytes (MB) or more recommended
Hard Disk
Approximately 425 MB of available hard-disk space
4 :: Why I am getting "The Microsoft .Net Framework 2.0 in not installed" message?
When you try to install SQL Server 2005 Express Edition, you may get a Microsoft SQL Server 2005 Setup error box with this message: "The Microsoft .Net Framework 2.0 in not installed. Please install before running setup."
You are getting this error, because .NET Framework 2.0 is not installed on your system yet. Read the next tutorial to download and install .NET Framework 2.0.
5 :: How to download and install Microsoft .NET Framework Version 2.0?
.NET Framework Version 2.0 is required by many Microsoft applications like SQL Server 2005. If you want download and install .NET Framework Version 2.0, you should follow this tutorial:
1. Go to the Microsoft .NET Framework Version 2.0 Redistributable Package (x86) page.
2. Click the Download button. Save the download file dotnetfx.exe to c:temp.
3. Look at and compare the downloaded file properties with:
Name: dotnetfx.exe
Location: C:temp
Size: 23,510,720 bytes
Version: 2.0.50727.42
4. Close all IE (Internet Explorer) windows.
5. Double click the downloaded file: to c:tempdotnetfx.exe. The Microsoft .NET Framework 2.0 Setup windows shows up. Follow the instructions to finish the installation.
6 :: What is mscorsvw.exe - Process - Microsoft .NET Framework NGEN?
Process mscorsvw.exe is installed as a system service as part of the .NET Framework 2.0. You can disable it, if you are not using any applications that require .NET Framework 2.0.
mscorsvw.exe process and program file info:
CPU usage: 00%
Memory usage: 2,704K
Launching method: System Service
Directory: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727
File name: mscorsvw.exe
Description: .NET Runtime Optimization Service
Size: 66,240 bytes
Date: Friday, September 23, 2005, 7:28:56 AM
Version: 2.0.50727.42
Company name: Microsoft
System essential: No
Virus/Spyware/Adware: No
7 :: How to install SQL Server 2005 Express Edition?
Once you have downloaded SQL Server 2005 Express Edition, you should follow this tutorial to install it on your system:
1. Double click SQLEXPR.EXE. The setup window shows up.
2. Click Next to let the setup program to unpack all files from the downloaded file.
3. When unpack is down, the setup program will check all required programs on your system.
4. Then the setup program will start the installation process.
5. On the Authentication Mode window, click the radio button for Mixed Mode (Windows Authentication and SQL Server Authentication). And enter "GlbalGuideLine" in the "Specify the sa logon password below:" fields.
6. Continue to finish the installation process.
7. When installation is done, you will see a new program menu entry as: Start > Programs > Microsoft SQL Server 2005 > Configuration Tools.
8 :: What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS)?
Process sqlservr.exe is the Microsoft SQL Server system service installed as part of the Microsoft SQL Server 2005 Express Edition.
mscorsvw.exe process and program file info:
CPU usage: 00%
Memory usage: 1,316K
Launching method: System Service
Directory: C:Program FilesMicrosoft SQL ServerMSSQL.1
MSSQLBinn
File name: sqlservr.exe
Description: SQL Server Windows NT
Size: 28,768,528 bytes
Date: Friday, October 14, 2005, 3:51:46 AM
Version: 2005.90.1399.0
Company name: Microsoft
System essential: No
Virus/Spyware/Adware: No
9 :: How do you know if SQL Server is running on your local system?
After installing SQL Server 2006 Express Edition, it will be running on your local system quietly as a background process.
If you want to see this process is running, run Windows Task Manager. You should see a process called sqlservr.exe running in the process list:
sqlservr.exe 00 1,316 K
If you select sqlservr.exe and click the "End Process" button, SQL Server will be stopped.
If you can not find sqlservr.exe in the process list, you know that your SQL Server is running.
10 :: How to connect SQL Server Management Studio Express to SQL Server 2005 Express?
Once you have SQL Server 2005 Express installed and running on your local machine, you are ready to connect SQL Server Management Studio Express to the server:
Click Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express to launch SQL Server Management Studio Express.
The "Connect to Server" box shows up. The Server Name field has a default value of "LOCALHOSTSQLEXPRESS". So don't change it. Select "SQL Server Authentication" as the Authentication. Enter enter "sa" as the Login, and "GlbalGuideLine" as the Password.
SQL Server 2005 Connect Window.
Click the Connect button, you should see the SQL Server Management Studio Express window comes up.
11 :: How to download and install Microsoft SQL Server Management Studio Express?
Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services. If you want to download and install it to your system, follow this tutorial:
1. Go to SQL Server Management Studio Express home page.
2. Go to the "Files in This Download" section.
3. Click the Download button next to "SQLServer2005_SSMSEE.msi - 38.5 MB" And save it to c:temp directory.
4. Look at and compare the downloaded file properties with:
Name: SQLServer2005_SSMSEE.msi
Location: C:temp
Size: 40,364,032 bytes
5. Double click to install. The setup window shows up. Follow the instructions to finish the installation process.
6. When installation is done, you will see a new program menu entry as: Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express
12 :: How to download and install SQL Server 2005 Books Online?
1. Go to the SQL Server 2005 Books Online download page.
2. Click the download button, the File Download box shows up. Save the download file to c:temp.
3. Double click on the downloaded file: c:tempSqlServer2K5_BOL_Feb2007.msi. The installation setup window shows up. Follow the instructions to finish the installation.
4. When the installation is done. You will see a new entry in the Start menu: Start > Programs > Microsoft SQL Server 2005 > Documentation and Tutorials
13 :: How to run Queries with SQL Server Management Studio Express?
1. Launch and connect SQL Server Management Studio Express to the local SQL Server 2005 Express.
2. Click on the "New Query" button below the menu line. Enter the following SQL statement in the query window:
SELECT 'Welcome to GlobalGuideLine.com Tips on SQL Server!'
3. Click the Execute button in the toolbar area. You should get the following in the result window:
Welcome to GlobalGuideLine.com Tips on SQL Server!
14 :: How to run SQL Server 2005 Books Online on your local system?
SQL Server 2005 Books Online can be accessed by a Web browser over the Internet. But you can also download it and read it on your local system. If you have downloaded and installed SQL Server 2005 Books Online package, you follow this tutorial to run it:
1. Click Start > Programs > Microsoft SQL Server 2005 > Documentation and Tutorials > Tutorials > SQL Server Tutorials. The SQL Server 2005 Books Online window shows up.
2. Click the plus sign (+) next to "SQL Server 2005 Tutorials in the Contents window".
3. Click the plus sign (+) next to "SQL Server Tools Tutorials".
4. Click "Lesson 1: Basic Navigation in SQL Server Management Studio". The book content shows up for you to read.
15 :: How to use Transact-SQL statements to access the database engine?
Transact-SQL statements can be used to access the database engine directly. Here are some good tutorials provided by the SQL Server 2005 Books Online. See the SQL Server 2005 Tutorials > Database Engine Tutorials > Writing Transact-SQL Statements Tutorial section in the SQL Server 2005 Books Online document.
This SQL Questions Guide is intended for users who are new to writing SQL statements. It will help new users get started by reviewing some basic statements for creating tables and inserting data. This tutorial uses Transact-SQL, the Microsoft implementation of the SQL standard. This tutorial is intended as a brief introduction to the Transact-SQL language and not as a replacement for a Transact-SQL class. The statements in this tutorial are intentionally simple, and are not meant to represent the complexity found in a typical production database.
16 :: How to create new databases with "CREATE DATABASE" statements?
This is the first SQL Questions Guide of a quick lesson on creating database objects with Transact-SQL statements. This section shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Questions Guide assumes that you are running SQL Server Management Studio Express.
Like many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: the name of the database. CREATE DATABASE also has many optional parameters, such as the disk location where you want to put the database files. When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters. This tutorial uses very few of the optional syntax parameters.
To create a database - In a Query Editor window, type but do not execute the following code:
CREATE DATABASE YourDataBaseName
GO
17 :: How to create new table with "CREATE TABLE" statements?
This is the second tutorial of a quick lesson on creating database objects with Transact-SQL statements. This section shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this section is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Guide assumes that you are running SQL Server Management Studio Express.
To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column.
Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value cannot be repeated in the table.
18 :: How to insert and update data into a table with "INSERT" and "UPDATE" statements?
Now you how to create a database, create a table in the database, and then access and change the data in the table. Because here is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. Now we assumes that you are running SQL Server Management Studio Express.
Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.
ProductID ProductName Price ProductDescription
1 Clamp 12.48 Workbench clamp
50 Screwdriver 3.17 Flat head
75 Tire Bar Tool for changing tires
3000 3mm Bracket .52
The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax.
19 :: How to read data in a table with "SELECT" statements?
Now this part is for creating database objects with Transact-SQL statements. This Question shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this Answer is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This Guide assumes that you are running SQL Server Management Studio Express.
Use the SELECT statement to read the data in a table. The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. For this Answer, you will work with five simple versions.
To read the data in a table - Type and execute the following statements to read the data in the Products table.
-- The basic syntax for reading data from a single table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
GO
You can use an asterisk to select all the columns in the table. This is often used in ad hoc queries. You should provide the column list in you permanent code so that the statement will return the predicted columns, even if a new column is added to the table later.
-- Returns all columns in the table
-- Does not use the optional schema, dbo
SELECT * FROM Products
GO
20 :: How to create a login account in MS SQL Server to access the database engine using "CREATE LOGIN" statements?
Now this answer will teach you that how to create login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
To access the Database Engine, users require a login. The login can represent the user's identity as a Windows account or as a member of a Windows group, or the login can be a SQL Server login that exists only in SQL Server. Whenever possible you should use Windows Authentication.
By default, administrators on your computer have full access to SQL Server. For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer. To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server. The following instructions are for Windows XP Professional.
21 :: How to create a user to access a database in MS SQL Server using "CREATE USER" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
Mary now has access to this instance of SQL Server 2005, but does not have permission to access the databases. She does not even have access to her default database YourDataBaseName until you authorize her as a database user.
To grant Mary access, switch to the YourDataBaseName database, and then use the CREATE USER statement to map her login to a user named Mary.
To create a user in a database - Type and execute the following statements (replacing computer_name with the name of your computer) to grant Mary access to the YourDataBaseName database.
USE [YourDataBaseName];
GO
CREATE USER [Mary] FOR LOGIN [computer_nameMary];
GO
Now, Mary has access to both SQL Server 2005 and the YourDataBaseName database.
22 :: How to create a view and a stored procedure in MS SQL Server using "CREATE VIEW/PROCEDURE" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
Now that Mary can access the YourDataBaseName database, you may want to create some database objects, such as a view and a stored procedure, and then grant Mary access to them. A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch.
Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.
23 :: How to grant a permission in MS SQL Server using "GRANT EXECUTE" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This answer shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.
Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.
GRANT EXECUTE ON pr_Names TO Mary;
GO
24 :: How to delete database objects with "DROP" statements in MS SQL Server?
To remove all database objects created by previous tutorials, you could just delete the database. However, in this tutorial, you will go through the steps to reverse every action you took doing the tutorial.
Removing permissions and objects - Before you delete objects, make sure you are in the correct database:
USE YourDataBaseName;
GO
Use the REVOKE statement to remove execute permission for Mary on the stored procedure:
REVOKE EXECUTE ON pr_Names FROM Mary;
GO
Use the DROP statement to remove permission for Mary to access the YourDataBaseName database:
DROP USER Mary;
GO
Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:
DROP LOGIN [Mary];
GO
Use the DROP statement to remove the store procedure pr_Names:
DROP PROC pr_Names;
GO
Use the DROP statement to remove the view vw_Names:
DROP View vw_Names;
GO
Use the DELETE statement to remove all rows from the Products table:
DELETE FROM Products;
GO
Use the DROP statement to remove the Products table:
DROP Table Products;
GO
You cannot remove the YourDataBaseName database while you are in the database; therefore, first switch context to another database, and then use the DROP sta
25 :: What is a database in MS SQL Server?
A database is a logical container that contains a set of related database objects:
* Tables - Storages of structured data.
* Views - Queries to present data from tables.
* Indexes - Sorting indexes to speed up searches.
* Stored Procedures - Predefined SQL program units.
* Users - Identifications used for data access control.
* Other objects.
26 :: What is the simplest way to create a new database in MS SQL Server?
The simplest way to create a new database is to use the "CREATE DATABASE" statement with this syntax:
CREATE DATABASE database_name
For example, run this statement:
CREATE DATABASE YourDataBaseName
GO
A new database called "YourDataBaseName" should be created in the SQL server. Of course, YourDataBaseName database should be empty at this moment - no tables. But it should have some other data objects automatically created by the server.
27 :: How to set the current database in MS SQL Server?
Once you are connected to the SQL Server, you should select a database to work with and set it as the current database using the "USE" statement with this syntax:
USE database_name
The following tutorial example shows you how to set "YourDataBaseName" as the current database, and create a table in "YourDataBaseName":
USE YourDataBaseName
GO
Changed database context to 'YourDataBaseName'.
CREATE TABLE Links (Name NVARCHAR(32))
GO
SELECT name, type_desc, create_date FROM sys.tables
GO
name type_desc create_date
Links USER_TABLE 2007-05-19 23:05:43.700
28 :: How to delete a database in MS SQL Server?
If you created a database incorrectly, or you have a database that is not needed any more, you can delete it with the "DROP DATABASE" statement with this syntax:
DROP DATABASE database_name
For example, execute this statement:
DROP DATABASE YourDataBaseName
GO
The database "YourDataBaseName" created in the previous tutorial should be deleted from the SQL server.
Warning, if you delete a database, all tables and their data in that database will be deleted.
29 :: Why I am getting this error when dropping a database in MS SQL Server?
If you are trying to drop a database that is in use, you will get an error message like this: 'Cannot drop database "GlobalGuidelineData" because it is currently in use.'
Before dropping a database, you must stop all client sessions using this database. If your own client session is using this database, you should set a different database as the current database as shown in this tutorial example:
CREATE DATABASE GlobalGuidelineData
GO
USE GlobalGuidelineData
GO
DROP DATABASE GlobalGuideLineDatabase
GO
Msg 3702, Level 16, State 4, Server LOCALHOSTSQLEXPRESS
Cannot drop database "GlobalGuideLineDatabase" because it is
currently in use.
USE master
GO
DROP DATABASE GlobalGuideLineDatabase
GO
30 :: How to get a list all databases on the SQL server?
If you don't remember database names you have created, you can get a list of all databases on the server by query the "sys.databases" view as shown in this tutorial example:
CREATE DATABASE GlobalGuideLineDatabase
GO
SELECT name, database_id, create_date FROM sys.databases
GO
[name] [database_id] [create_date]
master 1 2003-04-08 09:13:36.390
tempdb 2 2007-05-19 13:42:42.200
model 3 2003-04-08 09:13:36.390
msdb 4 2005-10-14 01:54:05.240
GlobalGuideLineDatabase 5 2007-05-19 20:04:39.310
As you can see, the newly created database is listed at the end of query result.
31 :: Where is my database stored on the hard disk in MS SQL Server?
If a database is created with simple CREATE DATABASE statement, the server will create two database files on the hard disk to store data and configuration information about that data bases:
* database_name.mdf - SQL Server Database Primary Data File
* database_name_log.ldf - SQL Server Database Transaction Log File
To find out the location of database files, you can query the "sys.database_files" view as shown in this tutorial example:
USE GlobalGuideLineDatabase
GO
SELECT type_desc, physical_name, size
FROM sys.database_files
GO
type_desc physical_name size
ROWS c:Program FilesMicrosoft SQL Server
MSSQL.1MSSQLDATAGlobalGuideLineDatabase.mdf 152
LOG c:Program FilesMicrosoft SQL Server
MSSQL.1MSSQLDATAGlobalGuideLineDatabase_log.LDF 63
Go verify these two files with Windows Explorer.
32 :: How to create database with physical files specified in MS SQL Server?
If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement:
CREATE DATABASE database_name
ON (NAME = logical_data_name,
FILENAME = physical_data_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
LOG ON (NAME = logical_log_name,
FILENAME = physical_log_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
For example, the following statement will create a database with database files located in the C:temp directory:
USE master
GO
DROP DATABASE GlobalGuideLineDatabase
GO
CREATE DATABASE GlobalGuideLineDatabase
ON (NAME = GlobalGuideLineDatabase,
FILENAME = 'C:tempGlobalGuideLineDatabase.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C:tempGlobalGuideLineDatabase.ldf',
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)
GO
SELECT type_desc, name, physical_name, size
FROM sys.database_files
GO
type_desc name physical_name size
ROWS GlobalGuideLineDatabase C:tempGlobalGuideLineDatabase.mdf 1280
LOG GlobalGuidelineLog C:tempGlobalGuideLineDatabase.ldf 128
33 :: How to rename databases in MS SQL Server?
If don't like the name of a database, you can change it by using the "ALTER DATABASE" statement with the following syntax:
ALTER DATABASE database_name
MODIFY NAME = new_database_name
The tutorial example below shows you how change the database name from "GlobalGuideLineDatabase" to "GlobalGuideLine":
ALTER DATABASE GlobalGuideLineDatabase
MODIFY NAME = GlobalGuideLine
GO
The database name 'GlobalGuideLine' has been set.
34 :: Why I am getting this error when renaming a database in MS SQL Server?
If you are trying to rename a database that is in use, you will get an error message like this: "The database could not be exclusively locked to perform the operation."
Before renaming a database, you must stop all client sessions using this database. Otherwise, you will get an error as shown in this tutorial example:
1. Launch one instance of SQL Server Management Studio and run:
USE GlobalGuideLine
GO
2. Keep the first instance running and launch another instance of SQL Server Management Studio:
ALTER DATABASE GlobalGuideLine
MODIFY NAME = GlobalGuideLineDatabase
GO
Msg 5030, Level 16, State 2, Server LOCALHOSTSQLEXPRESS
The database could not be exclusively locked to perform
the operation.
Obviously, the first instance is blocking the "ALTER DATABASE" statement.
35 :: What are database states in MS SQL Server?
A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys.databases catalog view. The following table defines the database states.
* ONLINE - Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
* OFFLINE - Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
* RESTORING - One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
* RECOVERING - Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
36 :: How to set a database state to OFFLINE in MS SQL Server?
If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax:
ALTER DATABASE database_name SET OFFLINE
The following tutorial example will bring "GlobalGuideLine" offline:
ALTER DATABASE GlobalGuideLine SET OFFLINE
GO
SELECT name, state_desc from sys.databases
GO
name state_desc
master ONLINE
tempdb ONLINE
model ONLINE
msdb ONLINE
GlobalGuideLine OFFLINE
USE GlobalGuideLine
GO
Msg 942, Level 14, State 4, Line 1
Database 'GlobalGuideLine' cannot be opened because it is offline.
37 :: How to move database physical files in MS SQL Server?
If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, and link it to the files at the new location. The following tutorial gives you a good example:
ALTER DATABASE GlobalGuideLine SET ONLINE
GO
USE GlobalGuideLine
GO
CREATE TABLE Links (Name NVARCHAR(32))
GO
ALTER DATABASE GlobalGuideLine SET OFFLINE
GO
Now it is safe to move the database physical files to a new location:
1. Run Windows Explorer
2. Create a new directory: c:tempdata
3. Drag and drop c:tempGlobalGuideLine.mdf to c:tempdata
3. Drag and drop c:tempGlobalGuideLine.mdf to c:tempdata
Go back to the SQL client program and run:
ALTER DATABASE GlobalGuideLine
MODIFY FILE (NAME = GlobalGuideLineDB,
FILENAME = 'C:tempdataGlobalGuideLineDB.mdf')
GO
38 :: How to set database to be READ_ONLY in MS SQL Server?
Databases in SQL Server have two update options:
* READ_WRITE - Data objects are allowed to be queried and modified. This is the default.
* READ_ONLY - Data objects are allowed to be queried, but not allowed to be modified.
You can use the "ALTER DATABASE" to change database update options as shown in the tutorial below:
USE GlobalGuideLineDB
GO
INSERT Links (Name) VALUES ('www.skills9.com')
GO
(1 rows affected)
ALTER DATABASE GlobalGuideLineDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.skills9.com')
GO
Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1
Failed to update database "GlobalGuideLineDB" because
the database is read-only.
SELECT * FROM Links
GO
Name
www.skills9.com
ALTER DATABASE GlobalGuideLineDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.skills9.com')
GO
(1 rows affected)
As you can see from the output, inserting data into a table is not allowed if the database is in READ_ONLY mode.
39 :: How to set database to be SINGLE_USER in MS SQL Server?
Databases in SQL Server have three user access options:
* MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. This is the default.
* SINGLE_USER - One user at a time is allowed to connect to the database. All other user connections are broken.
* RESTRICTED_USER - Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.
You can use the "ALTER DATABASE" to change database user access options as shown in the tutorial below:
USE GlobalGuideLineDatabase
GO
ALTER DATABASE GlobalGuideLineDatabase SET SINGLE_USER
GO
Now connect to server with another client session and try:
USE GlobalGuideLineDatabase
GO
Msg 924, Level 14, State 1, Line 1
Database 'GlobalGuideLineDatabase' is already open and can only
have one user at a time.
Go back to the first session and re-set the database to MULTI_USER:
ALTER DATABASE GlobalGuideLineDatabase SET MULTI_USER
GO
40 :: What are system databases in MS SQL Server?
System databases are created by the SQL Server itself during the installation process. System databases are used by the SQL server to help manage other user databases and client execution sessions. SQL Server 2005 Express Edition uses 4 system databases:
* master - The brain of a SQL server - Stores server configuration, runtime information, and database metadata.
* model - An empty database model - Used to clone new databases.
* msdb - The background job scheduler - Used for background jobs and related tasks.
* tempdb - The temporary database - Used by the server as a scratch pad.
41 :: What is a database table?
A table in database is a data object used to store data. Tables have the following features:
* Data is stored in a table with a structure of rows and columns.
* Columns must be pre-defined with names, types and constrains.
* A table object may have other associated data objects like, constrains, triggers, indexes, and statistics.
For example, a table called Address may have columns defined to store different elements of an address like, street number, city, country, postal code, etc.
42 :: What are DDL (Data Definition Language) statements for tables in MS SQL Server?
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are three primary DDL statements to create and manage tables:
* CREATE TABLE - Creating a new table.
* ALTER TABLE - Altering the definition of an existing table.
* DROP TABLE - Dropping an existing table.
43 :: How to create new tables with "CREATE TABLE" statements in MS SQL Server?
If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":
CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATETIME NULL)
GO
This scripts creates a testing table called "tip" with 4 columns in the current database.
44 :: How To Get a List of All Tables with "sys.tables" View in MS SQL Server?
If you want to see the table you have just created, you can use the "sys.tables" system view to get a list of all tables in the current database. The tutorial script gives you a good example:
SELECT name, type_desc, create_date FROM sys.tables
GO
name type_desc create_date
tip USER_TABLE 2007-05-19 23:05:43.700
The output shows that there is only one table in the current database.
45 :: How To Get a List of Columns using the "sys.columns" View in MS SQL Server?
If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database.
In order to a list of columns of a single table, you need to join sys.columns and sys.tables as shown in the tutorial example below:
SELECT * FROM sys.columns c, sys.tables t
WHERE c.object_id = t.object_id
AND t.name = 'tip'
GO
object_id name column_id user_type_id max_length
2073058421 id 1 56 4
2073058421 subject 2 167 80
2073058421 description 3 167 256
2073058421 create_date 4 61 8
You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.
46 :: How To Get a List of Table Columns using the "sp_columns" Stored Procedure in MS SQL Server?
If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sp_columns" stored procedure to get a list of all columns of the specified table. The following tutorial script shows you a good example:
sp_columns tip
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar ...
dbo tip description varchar ...
dbo tip create_date datetime ...
The "sp_columns" stored procedure returns a long list of properties for each column of the specified table. Take a look at each of them.
47 :: How To Get a List of Columns using the "sp_help" Stored Procedure in MS SQL Server?
Another way to get a list of columns from a table is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the table information, the column information, the identity column, the row GUID column, the primary key, indexes, and constraints. It you run "sp_help tip" in SQL Server Management Studio, you will see the result as shown in this picture:
sp_help Stored Procedure
48 :: How To Generate CREATE TABLE Script on an Existing Table in MS SQL Server?
If you want to know how an existing table was created, you can use SQL Server Management Studio to automatically generate a "CREATE TABLE" script The following tutorial shows you how to do this:
1. Run SQL Server Management Studio and connect to SQL server.
2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Tables > dbo.tip.
3. Click right mouse button on dbo.tip. The context menu shows up.
4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:
USE [GlobalGuideLineDatabase]
GO
/****** Object: Table [dbo].[tip]
Script Date: 05/05/2008 11:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tip](
[id] [int] NOT NULL,
[subject] [varchar](80)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [varchar](256)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[create_date] [datetime] NULL,
PRIMARY KEY CLUSTERED (
[id] ASC
)WITH (PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
49 :: How to create new tables with "SELECT ... INTO" statements in MS SQL Server?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "SELECT ... INTO" statement. The tutorial script below gives you a good example:
INSERT INTO tip VALUES (1, 'Learn SQL',
'Visit www.skills9.com','2006-05-01')
GO
SELECT * INTO tipBackup FROM tip
GO
(1 rows affected)
SELECT * FROM tipBackup
GO
id subject description create_date
1 Learn SQL Visit www.skills9.com 2008-05-01
sp_columns tipBackup
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tipBackup id int ...
dbo tipBackup subject varchar ...
dbo tipBackup description varchar ...
dbo tipBackup create_date datetime ...
As you can see, the "SELECT ... INTO" statement created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".
50 :: How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD" in MS SQL Server?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD" statement. The tutorial script below shows you a good example:
ALTER TABLE tip ADD author VARCHAR(40)
GO
sp_columns tip
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar ...
dbo tip description varchar ...
dbo tip create_date datetime ...
dbo tip author datetime ...
SELECT * FROM tip
GO
id subject description create_date author
1 Learn SQL Visit www.skills9.com 2008-05-01 NULL
This SQL script added a new column called "author" to the "tip" table. NULL values were added to this column on all existing data rows.
51 :: How to rename an existing column with the "sp_rename" stored procedure in MS SQL Server?
If you have an existing column in a table and you want to change the column name, you can use the "sp_rename ... 'COLUMN'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT. The tutorial example below shows you how to rename a column:
USE master
GO
sp_rename 'tip.subject', 'title', 'COLUMN'
GO
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213
Either the parameter @objname is ambiguous or the claimed
@objtype (COLUMN) is wrong.
USE GlobalGuideLineDatabase
GO
sp_rename 'tip.subject', 'title', 'COLUMN'
GO
Caution: Changing any part of an object name could break
scripts and stored procedures.
SELECT id, title, description, author FROM tip
GO
id title description author
1 Learn SQL Visit www.skills9.com NULL
You are getting the first error because 'GlobalGuideLineDatabase' is not the current database.
52 :: How to rename an existing column with SQL Server Management Studio?
If you are using SQL Server Management Studio, you can rename almost any data objects through the Object Explorer window. The tutorial example below shows you how to rename a column:
1. Run SQL Server Management Studio and connect to SQL server.
2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Tables > dbo.tip > Columns > title.
3. Click right mouse button on "title". The context menu shows up.
4. Select "Rename", type "subject" over "title", and press Enter key. The column name will be changed.
53 :: How to change the data type of an existing column with "ALTER TABLE" statements in MS SQL Server?
Sometimes, you may need to change the data type of an existing column. For example, you want increase the string length of a column. You can use the "ALTER TABLE ... ALTER COLUMN" statements in the following syntax:
ALTER TABLE table_name ALTER COLUMN column_name new_type
Here is a good example of change column data types:
-- Can not make a string column shorter
ALTER TABLE tip ALTER COLUMN subject VARCHAR(10)
GO
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
-- Can make a string column longer
ALTER TABLE tip ALTER COLUMN subject VARCHAR(100)
GO
Command(s) completed successfully.
-- Can not change string to numeric
ALTER TABLE tip ALTER COLUMN subject NUMBER
GO
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The statement has been terminated.
As you can see, the new date type must be compatible with the old data type in order for the "ALTER TABLE ... ALTER COLUMN" statement to work.
54 :: How to rename an existing table with the "sp_rename" stored procedure in MS SQL Server?
If you have an existing table and you want to change the table name, you can use the "sp_rename ... 'OBJECT'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT (including tables). The tutorial example below shows you how to rename a table:
sp_rename 'tip', 'faq', 'OBJECT'
GO
Caution: Changing any part of an object name could break
scripts and stored procedures.
SELECT name, type_desc, create_date FROM sys.tables
GO
name type_desc create_date
faq USER_TABLE 2007-05-19 23:05:43.700
tipBackup USER_TABLE 2007-05-19 23:25:23.357
You can also rename a table with on the Object Explorer window of SQL Server Management Studio. See tutorials on rename table columns.
55 :: How To Drop an Existing Table with "DROP TABLE" Statements in MS SQL Server?
If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:
SELECT * FROM tipBackup
GO
id subject description create_date
1 Learn SQL Visit www.skills9.com 2006-07-01
DROP TABLE tipBackup
GO
SELECT * FROM tipBackup
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tipBackup'.
Be careful, when you use the "DROP TABLE" statement. All data rows are gone too.
56 :: What Are DML (Data Manipulation Language) Statements in MS SQL Server?
DML (Data Manipulation Language) statements are statements to change data values in database tables. The are 3 primary DML statements:
* INSERT - Inserting new rows into database tables. For example "INSERT INTO ggl_links VALUES (101, 'www.skills9.com', NULL, 0, '2006-04-30')" inserts a single new row in the ggl_links table.
* UPDATE - Updating existing rows in database tables .
* DELETE - Deleting existing rows from database tables.
57 :: How To Create a Testing Table with Test Data in MS SQL Server?
If you want to practice DML statements, like INSERT, UPDATE and DELETE statements, you should create a testing table. The tutorial exercise shows you a good example:
CREATE TABLE ggl_links (id INTEGER PRIMARY KEY,
url VARCHAR(80) NOT NULL,
notes VARCHAR(1024),
counts INT,
created DATETIME NOT NULL DEFAULT(getdate()))
GO
SELECT c.column_id as seq, c.name, x.name as type,
c.max_length, c.is_nullable
FROM sys.columns c, sys.tables t, sys.systypes x
WHERE c.object_id = t.object_id
AND c.system_type_id = x.xtype
AND t.name = 'ggl_links'
ORDER BY c.column_id
GO
seq name type max_length is_nullable
1 id int 4 0
2 url varchar 80 0
3 notes varchar 1024 1
4 counts int 4 1
5 created datetime 8 0
You should keep this table to practice other tutorial exercises presented in this collection.
58 :: How To Insert a New Row into a Table with "INSERT INTO" Statements in MS SQL Server?
To insert a new row into a table, you can use the INSERT INTO statement with values specified for all columns as in the following syntax:
INSERT INTO table_name VALUES (list_of_values_of_all columns)
Note that the list of values of all columns must be specified in the same order as how columns are defined in the CREATE TABLE statement. The following tutorial example inserts a row into "ggl_links":
INSERT INTO ggl_links VALUES (101,
'www.skills9.com',
NULL,
0,
'2006-04-30')
GO
(1 row(s) affected)
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
The values are stored in the new record nicely.
59 :: How To Use Column Default Values in INSERT Statements in MS SQL Server?
If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column. The following tutorial exercise gives a good example:
INSERT INTO ggl_links VALUES (102,
'www.skills9.com',
NULL,
0,
DEFAULT)
GO
(1 row(s) affected)
SELECT * FROM fyi_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
102 www.skills9.com NULL 0 2007-05-19
The default value, getdate(), is used for "created" column, which gives the current date.
60 :: How to provide column names in INSERT Statements in MS SQL Server?
If you don't want to specify values for columns that have default values, or you want to specify values to columns in an order different than how they are defined, you can provide a column list in the INSERT statement. If a column is omitted in the column, SQL Server applies 3 rules:
* If default value is defined for the column, that default value will be used.
* If no default value is defined for the column and NULL is allowed, NULL will be used.
* If no default value is defined for the column and NULL is not allowed, SQL Server will reject the insert statement with an error.
The following tutorial exercise gives you some good examples:
INSERT INTO ggl_links (url, id)
VALUES ('www.skills9.com',103)
GO
(1 row(s) affected)
INSERT INTO ggl_links (id) VALUES (110)
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'url',
table 'skills9Database.dbo.ggl_links'; column does
not allow nulls. INSERT fails.
The statement has been terminated.
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
102 www.skills9.com NULL 0 2007-05-19
103 www.skills9.com NULL NULL 2007-05-19
61 :: What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server?
If your table has a primary key column, and you are trying to insert a new row with duplicate key value on the primary key column, you will get an error. The reason is simple - Primary key column does not allow duplicate values. The following tutorial exercise gives you a good example:
SELECT * FROM ggl_links
INSERT INTO ggl_links VALUES (101,
'www.skills9.com',
NULL,
0,
'2006-04-30')
GO
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint
'PK__ggl_links__03317E3D'. Cannot insert duplicate
key in object 'dbo.ggl_links'.
The statement has been terminated.
You are getting this error, because value "101" has already been used by an existing row.
62 :: How To Insert Multiple Rows with One INSERT Statement in MS SQL Server?
If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives you a good example:
INSERT INTO ggl_links SELECT id+500, REVERSE(url),
notes, counts, created FROM ggl_links
GO
(3 row(s) affected)
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
102 www.skills9.com/html NULL 0 2007-05-19
103 www.skills9.com/sql NULL NULL 2007-05-19
601 www.skills9.com/seo NULL 0 2006-04-30
602 www.skills9.com/xml NULL 0 2007-05-19
603 www.skills9.com/JavaScript_Guide NULL NULL 2007-05-19
As you can see, "INSERT INTO ... SELECT ..." is powerful statement. you can use it build up data in tables quickly.
63 :: How To Update Values in a Table with UPDATE Statements in MS SQL Server?
If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example:
SELECT * FROM ggl_links WHERE id = 101
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
UPDATE ggl_links SET counts = 999, notes = 'Good.'
WHERE id = 101;
GO
(1 row(s) affected)
SELECT * FROM ggl_links WHERE id = 101
GO
id url notes counts created
101 www.skills9.com Good. 999 2006-04-30
As you can see, the SET clause takes column and value pairs to provide new values, while the WHERE clause defines which row to apply the update.
64 :: How To Update Multiple Rows with One UPDATE Statement in MS SQL Server?
If the WHERE clause in an UPDATE statement matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example:
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 www.skills9.com NULL 0 2006-04-30
602 www.skills9.com/html NULL 0 2007-05-19
603 www.skills9.com/sql NULL NULL 2007-05-19
SELECT * FROM ggl_links
UPDATE ggl_links SET counts = 9, notes = 'Wrong'
WHERE id >= 500
(3 row(s) affected)
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 www.skills9.com Wrong 9 2006-04-30
602 www.skills9.com/html Wrong 9 2007-05-19
603 www.skills9.com/sql Wrong 9 2007-05-19
The UPDATE statement updated 3 rows with the same new values.
65 :: How to use old values to define new values in UPDATE statements in MS SQL Server?
If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exercise below shows you a good example:
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 www.skills9.com Wrong 9 2006-04-30
602 www.skills9.com/html Wrong 9 2007-05-21
603 www.skills9.com/sql Wrong 9 2007-05-23
UPDATE ggl_links SET id = id+200, counts = id*2
WHERE id >= 500
GO
(3 row(s) affected)
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
801 www.skills9.com Wrong 1202 2006-04-30
802 www.skills9.com/html Wrong 1204 2007-05-19
803 www.skills9.com/sql Wrong 1206 2007-05-19
This statement increased values in the id column by 200. It also updated the counts column with the newly increased id value.
66 :: Is the Order of Columns in the SET Clause Important in MS SQL Server?
The answer is NO. The order of columns in the SET clause of the UPDATE statement is NOT important. You probably already noticed from the previous tutorial. There is a BIG DIFFERENCE among SQL Server, MySQL and Oracle on update multiple columns with previous values:
* SQL Server provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
* MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.
* Oracle provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
Here is a good tutorial exercise:
SELECT * FROM ggl_links
-- Check the old values
SELECT * FROM ggl_links WHERE url = 'www.skills9.com'
GO
id url notes counts created
101 www.skills9.com Good. 999 2006-04-30
-- Update "id" before "counts"
UPDATE ggl_links SET id = id+200, counts = id*2
WHERE url = 'www.skills9.com'
GO
(1 row(s) affected)
67 :: How To Use Values from Other Tables in UPDATE Statements in MS SQL Server?
If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clause. The tutorial exercise below shows you a good example:
-- Create another table
CREATE TABLE ggl_rates (id INTEGER,
comment VARCHAR(16))
Go
-- Insert some rows in the new table
INSERT INTO ggl_rates VALUES (101, 'The best')
Go
INSERT INTO ggl_rates VALUES (102, 'Well done')
GO
INSERT INTO ggl_rates VALUES (103, 'Thumbs up')
Go
-- Update ggl_links with values from ggl_rates
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id > 0 AND id < 110
GO
(3 row(s) affected)
-- View the updated values
SELECT * FROM ggl_links
WHERE id > 0 AND id < 110
GO
id url notes counts created
101 www.skills9.com The best 999 2006-04-30
102 www.skills9.com/html Well done 0 2007-05-19
103 www.skills9.com/sql Thumbs up NULL 2007-05-19
Note that if column names are confusing between the inner table and the outer table, you need to pref
68 :: What Happens If the UPDATE Subquery Returns No Rows in MS SQL Server?
If you use a subquery to assign new values in the SET clause in an UPDATE statement, and the subquery returns no rows for an outer row, SQL Server will provide a NULL value to the SET clause. The tutorial exercise below shows you a good example:
-- insert a new row
INSERT INTO ggl_links (id, url, notes)
VALUES (0, 'www.skills9.com', 'Number one')
GO
(1 row(s) affected)
-- view old values
SELECT * FROM ggl_links WHERE id = 0
GO
id url notes counts created
0 www.skills9.com Number one NULL 2007-05-23
-- make sure there is no matching row in fyi_rates
SELECT * FROM ggl_rates WHERE id = 0
GO
0 rows
-- update a subquery returning no rows
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id = 0
(1 row(s) affected)
-- view new values
SELECT * FROM ggl_links WHERE id = 0
GO
id url notes counts created
0 www.skills9.com NULL NULL 2007-05-23
Column "notes" gets updated with NULL if there is no return rows in the subquery.
69 :: What Happens If the UPDATE Subquery Returns Multiple Rows in MS SQL Server?
If a subquery is used in a UPDATE statement, it must return exactly one row for each row in the update table that matches the WHERE clause. If it returns multiple rows, SQL Server will give you an error message. To test this out, you can try the following tutorial exercise:
-- insert two rows to ggl_rates
INSERT INTO ggl_rates VALUES (0, 'Number 1')
GO
INSERT INTO ggl_rates VALUES (0, 'Number 2')
GO
-- make sure there are 2 match rows
SELECT * FROM ggl_rates WHERE id = 0
GO
id comment
0 Number 1
0 Number 2
-- update with subquery that returns 2 rows
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id = 0
GO
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted
when the subquery follows =, !=, <, <= , >, >= or when
the subquery is used as an expression.
The statement has been terminated.
It is clear that we are using subquery as an expression, and it must return 0 or 1 row. Otherwise, we will get an error.
70 :: How To Delete an Existing Row with DELETE Statements in MS SQL Server?
If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements:
-- insert a row for this test
INSERT INTO ggl_links (url, id)
VALUES ('www.google.com', 301)
GO
(1 row(s) affected)
-- view the inserted row
SELECT id, url, notes, counts FROM ggl_links
WHERE id = 301
GO
id url notes counts
301 www.google.com NULL NULL
-- delete one row
DELETE FROM ggl_links WHERE id = 301
GO
(1 row(s) affected)
-- try to view the deleted row
SELECT id, url, notes, counts FROM ggl_links
WHERE id = 301
no rows
Row with id of 301 is truly deleted.
71 :: How To Delete Multiple Rows with One DELETE Statement in MS SQL Server?
You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the ggl_links table:
-- view rows to be deleted
SELECT id, url, notes, counts FROM ggl_links
WHERE id > 300
GO
id url notes counts
801 www.skills9.com Wrong 1202
802 www.skills9.com/html Wrong 1204
803 www.skills9.com/sql Wrong 1206
-- delete multiple rows
DELETE FROM ggl_links WHERE id > 300
GO
(3 row(s) affected)
-- try to view the deleted row
SELECT id, url, notes, counts FROM ggl_links
WHERE id > 300
GO
no rows
72 :: How To Delete All Rows with TRUNCATE TABLE Statement in MS SQL Server?
If you want to delete all rows from a table, you have two options:
* Use the DELETE statement with no WHERE clause.
* Use the TRUNCATE TABLE statement.
The TRUNCATE statement is more efficient the DELETE statement. The tutorial exercise shows you a good example of TRUNCATE statement:
SELECT COUNT(*) FROM ggl_rates
GO
5
TRUNCATE TABLE ggl_rates
GO
SELECT COUNT(*) FROM ggl_rates
GO
0
73 :: How To Join Two Tables in a Single Query in MS SQL Server?
Two tables can be joined together in a query in 4 ways:
* Inner Join: Returns only rows from both tables that satisfy the join condition.
* Left Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the first (left) table.
* Right Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the second (right) table.
* Full Outer Join: Returns rows from both tables that satisfy the join condition, the rest of rows from the first (left) table, and the rest of rows from the second (right) table.
74 :: How To Write a Query with an Inner Join in MS SQL Server?
If you want to query from two tables with an inner join, you can use the INNER JOIN ... ON clause in the FROM clause. The tutorial exercise below creates another testing table and returns output with an inner join from two tables: ggl_links and ggl.rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
CREATE TABLE ggl_rates (id INTEGER,
comment VARCHAR(16))
GO
INSERT INTO ggl_rates VALUES (101, 'The best')
GO
INSERT INTO ggl_rates VALUES (102, 'Well done')
GO
INSERT INTO ggl_rates VALUES (103, 'Thumbs up')
GO
INSERT INTO ggl_rates VALUES (204, 'Number 1')
GO
INSERT INTO ggl_rates VALUES (205, 'Not bad')
GO
INSERT INTO ggl_rates VALUES (206, 'Good job')
GO
INSERT INTO ggl_rates VALUES (207, 'Nice tool')
GO
SELECT ggl_links.id, ggl_links.url,
ggl_rates.comment FROM ggl_links
INNER JOIN ggl_rates ON ggl_links.id = ggl_rates.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
Note that when multiple tables are used in a query, column names need to be prefixed with table names in case the same colu
75 :: How To Define and Use Table Alias Names in MS SQL Server?
When column names need to be prefixed with table names, you can define table alias name and use them to prefix column names. To define an alias for a table name, just enter the alias name right after the original table name in the FROM clause as shown in the following select statement:
SELECT l.id, l.url, r.comment FROM ggl_links l
INNER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
76 :: How To Write a Query with a Left Outer Join in MS SQL Server?
If you want to query from two tables with a left outer join, you can use the LEFT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a left outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
SELECT l.id, l.url, r.comment FROM ggl_links l
LEFT OUTER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
104 www.google.com NULL
105 www.yahoo.com NULL
106 www.php.net NULL
107 www.mysql.com NULL
Note that a left outer join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.
The extra rows returned from the left outer join in this example represents links that have no rates in the above example.
77 :: How To Write a Query with a Right Outer Join in MS SQL Server?
If you want to query from two tables with a right outer join, you can use the RIGHT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a right outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
SELECT l.id, l.url, r.comment FROM ggl_links l
RIGHT OUTER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
NULL NULL Number 1
NULL NULL Not bad
NULL NULL Good job
NULL NULL Nice tool
Note that a right outer join may return extra rows from the second (right) table that do not satisfy the join condition. In those extra rows, columns from the first (left) table will be given null values.
The extra rows returned from the right outer join in this example represents rates that have no links in the above example.
78 :: How To Write a Query with a Full Outer Join in MS SQL Server?
If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
SELECT l.id, l.url, r.comment FROM ggl_links l
FULL OUTER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/seo Thumbs up
104 www.google.com NULL
105 www.yahoo.com NULL
106 www.php.net NULL
107 www.mysql.com NULL
NULL NULL Number 1
NULL NULL Not bad
NULL NULL Good job
NULL NULL Nice tool
As you can see, an full outer join returns 3 groups of rows:
* The rows from both tables that satisfy the join condition.
* The rows from the first (left) table that do not satisfy the join condition.
* The rows from the second (right) table that do not satisfy the join condition.
79 :: How To Write an Inner Join with the WHERE Clause in MS SQL Server?
If you don't want to use the INNER JOIN ... ON clause to write an inner join, you can put the join condition in the WHERE clause as shown in the following query example:
SELECT l.id, l.url, r.comment
FROM ggl_links l, ggl_rates r WHERE l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
80 :: How To Name Query Output Columns in MS SQL Server?
Each column in the query output has a default name. If you don't like the default name, you can specify a new name for any column in the query output by using the AS clause. The following statement shows you a good example:
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM ggl_links
GROUP BY tag, YEAR(created) ORDER BY COUNT(*) DESC
GO
Category Year Counts
HTML 2003 1
SQL 2004 1
SEO 2005 1
Jobs 2006 1
XML 2006 1
XSL 2007 1
JavaScript 2007 1
81 :: What Is a Subquery in a SELECT Query Statement in MS SQL Server?
A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The output of the subquery will be used in the final evaluation of the criteria. Usually, subqueries are used in the following Boolean operations:
* "expression IN (subquery)" - True if the expression matches one of the returned values from the subquery.
* "expression NOT IN (subquery)" - True if the expression does not match any of the returned values from the subquery.
* "EXISTS (subquery)" - True if the subquery returns one or more rows.
* "NOT EXISTS (subquery)" - True if the subquery returns no rows.
82 :: How To Use Subqueries with the IN Operators in MS SQL Server?
A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator. It returns all links with ids in the ggl_rates table.
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links WHERE id IN (SELECT id FROM ggl_rates)
GO
id url tag Year
101 www.skills9.com main 2006
102 www.skills9.com/html HTMLA 2007
103 www.skills9.com/sql SQL 2007
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links
WHERE id IN (101, 102, 103, 204, 205, 206, 207)
GO
id url tag Year
101 www.skills9.com main 2006
102 www.skills9.com/html HTMLA 2007
103 www.skills9.com/sql SQL 2007
As you can see, the subquery is equivalent to a list of values.
83 :: How To Use Subqueries with the EXISTS Operators in MS SQL Server?
A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from ggl_links table that there are rows existing in the ggl_rates table with the same id.
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links WHERE EXISTS (
SELECT * FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id)
GO
id url tag Year
101 www.skills9.com main 2006
102 www.skills9.com/html DBA 2007
103 www.skills9.com/sql SQL 2007
Note that the subquery uses columns from the source table of the outer query.
84 :: How To Use Subqueries in the FROM Clause in MS SQL Server?
If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:
SELECT * FROM (SELECT l.id, l.url, r.comment
FROM ggl_links l LEFT OUTER JOIN ggl_rates r
ON l.id = r.id) WHERE url LIKE '%er%'
GO
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
SELECT * FROM (SELECT l.id, l.url, r.comment
FROM ggl_links l LEFT OUTER JOIN ggl_rates r
ON l.id = r.id) s WHERE s.url LIKE '%er%'
GO
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/xml Thumbs up
107 www.skills9.com/sql NULL
The error on the first query is caused by the missing alias name to name output of the subquery as a temporary table.
85 :: How To Count Groups Returned with the GROUP BY Clause in MS SQL Server?
If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. If you want to count the number of groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on the main query as shown in the following tutorial exercise:
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM ggl_links GROUP BY tag,
YEAR(created)
GO
Category Year Counts
HTML 2003 1
XML 2004 1
CSS 2005 1
SQL 2006 1
SEO 2006 1
JavaScript 2007 1
JOBS 2007 1
SELECT COUNT(*) FROM (
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM ggl_links GROUP BY tag,
YEAR(created) ) groups
GO
7
86 :: How To Return the Top 5 Rows from a SELECT Query in MS SQL Server?
If you want the query to return only the first 5 rows, you can use the "TOP 5" clause. The TOP clause takes one parameter to indicate how many top rows to return. The following statements returns the first 5 rows and 3 rows from the ggl_links:
SELECT TOP 5 id, url, counts, tag FROM ggl_links
ORDER BY counts DESC
GO
id url counts tag
102 www.skills9.com 972 DBA
105 www.google.com 960 DBA
107 www.yahoo.com 828 SQA
103 www.mysql.com 728 SQA
106 www.php.net 439 DEV
SELECT TOP 3 id, url, counts, tag FROM ggl_links
ORDER BY counts DESC
GO
id url counts tag
102 www.skills9.com 972 DBA
105 www.google.com 960 DBA
107 www.yahoo.com 828 SQA
87 :: How To Return the Second 5 Rows in MS SQL Server?
If you want to display query output in multiple pages with 5 rows per page, and the visitor wants to see the output for the second page, you need to display query output from row 6 to row 10. If you are using MySQL server, you can use the "LIMIT startRow maxRows".
But the LIMIT clause is not supported by the SQL server. And there seems to be no easy workaround. You may consider to return the top 10 rows, skip the first 5 rows, then keep the second 5 rows.
88 :: How To Use UNION to Merge Outputs from Two Queries Together in MS SQL Server?
If you have two queries that returns the same row fields, you can merge their outputs together with the UNION operator. The following tutorial exercise shows you how to use the UNION operator:
SELECT * FROM ggl_links WHERE tag = 'DBA'
GO
id url notes counts created tag
102 skills9.com NULL 972 2007-05-19 DBA
104 www.mysql.com 390 2006-01-01 DBA
105 www.oracle.com 960 2005-01-01 DBA
SELECT * FROM ggl_links WHERE tag = 'DEV'
GO
id url notes counts created tag
101 skills9.com/html NULL 120 2006-04-30 DEV
106 www.php.net 439 2004-01-01 DEV
SELECT * FROM ggl_links WHERE tag = 'DBA'
UNION
SELECT * FROM ggl_links WHERE tag = 'DEV'
GO
id url notes counts created tag
102 skills9.com NULL 972 2007-05-19 DBA
104 www.mysql.com 390 2006-01-01 DBA
105 www.oracle.com 960 2005-01-01 DBA
101 skills9.com/html NULL 120 2006-04-30 DEV
106 www.php.net 439 2004-01-01 DEV
89 :: How To Use ORDER BY with UNION Operators in MS SQL Server?
If you need to sort the output from two queries grouped together with a UNION operator, you need to apply the ORDER BY clause at the group level, not at the subquery level.
Note that SQL Server and MySQL react differently to the ORDER BY clause used in a subquery of a UNION operator:
* SQL Server will give error if ORDER BY is used inside a subquery of a UNION operator.
* MySQL will ignore the ORDER BY clause inside a subquery of a UNION operator.
The following tutorial exercise shows you how to use ORDER BY clause with UNION operator:
(SELECT * FROM ggl_links WHERE tag = 'DBA'
ORDER BY created)
UNION
(SELECT * FROM ggl_links WHERE tag = 'DEV'
ORDER BY created)
GO
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.
(SELECT * FROM ggl_links WHERE tag = 'DBA')
UNION
(SELECT * FROM ggl_links WHERE tag = 'DEV')
ORDER BY created
GO
90 :: What Is a SELECT Query Statement in MS SQL Server?
The SELECT statement is also called the query statement. It is the most frequently used SQL statement in any database application. SELECT statements allows you to retrieve data from one or more tables or views, with different selection criteria, grouping criteria and sorting orders.
A SELECTE statement has the following basic syntax:
SELECT select_list
FROM table_source
WHERE search_condition
GROUP BY group_by_expression
HAVING search_condition
ORDER BY order_by_expression
Here is an example of a SELECT statement with all clauses mentioned above:
SELECT SalesOrderID, SUM(LineTotal) AS TotalPrice
FROM SalesLT.SalesOrderDetail
WHERE ModifiedDate > '2004-05-01'
GROUP BY SalesOrderID
HAVING COUNT(*) > 30
ORDER BY TotalPrice DESC
91 :: How To Select All Columns of All Rows from a Table with a SELECT statement in MS SQL Server?
The simplest query statement is the one that selects all columns of all rows from a single table: "SELECT * FROM tableName". The (*) in the SELECT clause tells the query to return all columns. The missing WHERE clause tells the query to return all rows in specified table. The tutorial exercise below returns all columns and all rows from table "ggl_links":
SELECT * FROM ggl_links
id url notes counts created
101 skills9.com NULL 0 2006-04-30
102 skills9.com/html NULL 0 2007-05-19
103 skills9.com/sql NULL NULL 2007-05-19
92 :: How To Select Some Specific Columns from a Table in a Query in MS SQL Server?
If you want explicitly tell the query to return some specific columns, you can specify the column names in the SELECT clause. The following select statement returns only three columns, "id", "created" and "url" from the table "ggl_links":
SELECT id, created, url FROM ggl_links
id created url
101 2006-04-30 www.skills9.com
102 2007-05-19 www.skills9.com/html
103 2007-05-19 www.skills9.com/sql
93 :: How To Select Some Specific Rows from a Table in MS SQL Server?
If you don't want select all rows from a table, you can specify a WHERE clause to tell the query to return only the rows that meets the condition defined in the WHERE clause. The WHERE clause condition is a normal Boolean expression. If any data from the table needs to be used in the Boolean expression, column names should be used to represent the table data.
The first select statement below only returns rows that have url names containing the letter "a". The second select statement returns no rows, because the WHERE clause results FALSE for all rows in the table.
SELECT * FROM ggl_links WHERE url LIKE '%s%'
GO
id url notes counts created
102 skills9.com.com/sql NULL 0 2007-05-19
103 skills9.com.com/xslt NULL NULL 2007-05-19
SELECT * FROM ggl_links WHERE id < 100
GO
0 row
94 :: How To Add More Data to the Testing Table in MS SQL Server?
If you want to continue with other tutorial exercises in this FAQ collection, you need to add more data to the testing table. Follow the script below to add a new column and more rows:
ALTER TABLE ggl_links ADD tag VARCHAR(8)
GO
UPDATE ggl_links SET tag = 'DEV' WHERE id = 101
GO
UPDATE ggl_links SET tag = 'DBA' WHERE id = 102
GO
UPDATE ggl_links SET tag = 'SQA' WHERE id = 103
GO
INSERT INTO ggl_links VALUES (104,
'www.mysql.com', '', '0', '2006-01-01', 'DBA')
GO
INSERT INTO ggl_links VALUES (105,
'www.oracle.com', '', '0', '2005-01-01', 'DBA')
GO
INSERT INTO ggl_links VALUES (106,
'www.php.net', '', '0', '2004-01-01', 'DEV')
GO
INSERT INTO ggl_links VALUES (107,
'www.winrunner.com', '', '0', '2003-01-01', 'SQA')
GO
UPDATE ggl_links
SET counts = CAST(LOG(id)*1000000 AS INT) % 1000
GO
95 :: How To Sort the Query Output with ORDER BY Clauses in MS SQL Server?
If you want the returning rows to be sorted, you can specify a sorting expression in the ORDER BY clause. The simplest sort expression is column name who's values will be sorted by. The following select statement returns rows sorted by the values in the "counts" column:
SELECT * FROM ggl_links ORDER BY counts
GO
id url notes counts created tag
101 skills9.com NULL 120 2006-04-30 DEV
104 www.mysql.com 390 2006-01-01 DBA
106 www.php.net 439 2004-01-01 DEV
103 skills9.com/html NULL 728 2007-05-19 SQA
107 www.winrunner.com 828 2003-01-01 SQA
105 www.oracle.com 960 2005-01-01 DBA
102 globalguideline.com/sql NULL 972 2007-05-19 DBA
The output is sorted by the values of the "counts" column.
96 :: Can the Query Output Be Sorted by Multiple Columns in MS SQL Server?
You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns rows sorted by "tag" and "counts" values:
SELECT tag, counts, url, created
FROM ggl_links ORDER BY tag, counts
GO
tab counts url created
DBA 390 www.mysql.com 2006-01-01
DBA 960 www.oracle.com 2005-01-01
DBA 972 skills9.com.com 2007-05-19
DEV 120 skills9.com.com/xml 2006-04-30
DEV 439 www.php.net 2004-01-01
SQA 728 skills9.com.com/sql 2007-05-19
SQA 828 www.winrunner.com 2003-01-01
97 :: How To Sort Query Output in Descending Order in MS SQL Server?
If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the "tag" in descending order, then sorts the "counts" in ascending order:
SELECT tag, counts, url, created
FROM ggl_links ORDER BY tag DESC, counts
GO
tab counts url created
SQA 728 skills9.com/sql 2007-05-19
SQA 828 www.winrunner.com 2003-01-01
DEV 120 skills9.com.com/xml 2006-04-30
DEV 439 www.php.net 2004-01-01
DBA 390 www.mysql.com 2006-01-01
DBA 960 www.oracle.com 2005-01-01
DBA 972 skills9.com.com 2007-05-19
98 :: How To Count Rows with the COUNT(*) Function in MS SQL Server?
If you want to count the number of rows, you can use the COUNT(*) function in the SELECT clause. The following tutorial exercise shows you some good examples:
SELECT COUNT(*) FROM ggl_links
GO
7
SELECT COUNT(*) FROM ggl_links
WHERE url LIKE '%glo%'
GO
3
So there are 7 rows in total in table "ggl_links", and 3 rows that have 'glo' as part of their url names.
99 :: Can SELECT Statements Be Used on Views in MS SQL Server?
Select (query) statements can be used on views in the same way as tables. The following tutorial exercise helps you creating a view and running a query statement on the view:
CREATE VIEW myLinks AS SELECT * FROM ggl_links
WHERE url LIKE '%glo%'
GO
SELECT tag, counts, url, created
FROM myLinks ORDER BY counts DESC
GO
tag counts url created
DBA 972 skills9.com 2007-05-19
SQA 728 skills9.com/html 2007-05-19
DEV 120 skills9.com/sql 2006-04-30
100 :: How To Filter Out Duplications in the Returning Rows in MS SQL Server?
If there are duplications in the returning rows, and you want to remove the duplications, you can use the keyword DISTINCT in the SELECT clause. The DISTINCT applies to the combination of all data fields specified in the SELECT clause. The tutorial exercise below shows you how DISTINCT works:
CREATE TABLE ggl_team (first_name VARCHAR(8),
last_name VARCHAR(8))
GO
INSERT INTO ggl_team VALUES ('John', 'Gate')
GO
INSERT INTO ggl_team VALUES ('John', 'Russell')
GO
INSERT INTO ggl_team VALUES ('John', 'Seo')
GO
INSERT INTO ggl_team VALUES ('John', 'Gate')
GO
INSERT INTO ggl_team VALUES ('James', 'Gate')
GO
INSERT INTO ggl_team VALUES ('Peter', 'Gate')
GO
INSERT INTO ggl_team VALUES ('John', 'Gate')
GO
MS SQL Server Multiple Choice Questions and Answers PDF Experienced Freshers
Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It runs on Windows systems and uses Transact-SQL as the query language.
Microsoft SQL Server release history:
* 1993 - SQL Server 4.21 for Windows NT
* 1995 - SQL Server 6.0, codenamed SQL95
* 1996 - SQL Server 6.5, codenamed Hydra
* 1999 - SQL Server 7.0, codenamed Sphinx
* 1999 - SQL Server 7.0 OLAP, codenamed Plato
* 2000 - SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
* 2003 - SQL Server 2000 64-bit, codenamed Liberty
* 2005 - SQL Server 2005, codenamed Yukon (version 9.0)
* 2005 - SQL Server 2005 Express Edition, restricted free version
2 :: How to download Microsoft SQL Server 2005 Express Edition?
Microsoft SQL Server 2005 Express Edition is the free version of the Microsoft SQL Server 2005. If you are interested to try SQL Server 2005, you should follow this tutorial to download Microsoft SQL Server 2005 Express Edition:
1. Go to the Microsoft SQL Server 2005 Express Edition download page.
2. Go to the Files in This Download section, and click Download button next to the "SQLEXPR.EXE - 53.5 MB" file. The File Download box shows up.
3. Save the download file to C:temp directory. When the download is done, you should get the following file:
Name: SQLEXPR.EXE
Description: Microsoft SQL 2005 Server Express Edition
Location: C:temp
Size: 56,105,688 bytes
Version: 9.0.1399.6
3 :: System Requirements for SQL Server 2005 Express Edition?
The following system requirements cover the SQL Server 2005 Express Edition:
Processor
32-bit Processor of 600-megahertz (MHz) or faster
Operating System
Windows XP with Service Pack 2 or later
Windows 2000 Professional with SP4
Windows 2000 Server with Service Pack 4 or later
Windows Server 2003 Standard, or Enterprise SP1
Windows Server 2003 Web Edition SP1
Windows Small Business Server 2003 with SP1
Vista Home Basic and above
Framework
.NET Framework 2.0
Memory
512 megabytes (MB) or more recommended
Hard Disk
Approximately 425 MB of available hard-disk space
4 :: Why I am getting "The Microsoft .Net Framework 2.0 in not installed" message?
When you try to install SQL Server 2005 Express Edition, you may get a Microsoft SQL Server 2005 Setup error box with this message: "The Microsoft .Net Framework 2.0 in not installed. Please install before running setup."
You are getting this error, because .NET Framework 2.0 is not installed on your system yet. Read the next tutorial to download and install .NET Framework 2.0.
5 :: How to download and install Microsoft .NET Framework Version 2.0?
.NET Framework Version 2.0 is required by many Microsoft applications like SQL Server 2005. If you want download and install .NET Framework Version 2.0, you should follow this tutorial:
1. Go to the Microsoft .NET Framework Version 2.0 Redistributable Package (x86) page.
2. Click the Download button. Save the download file dotnetfx.exe to c:temp.
3. Look at and compare the downloaded file properties with:
Name: dotnetfx.exe
Location: C:temp
Size: 23,510,720 bytes
Version: 2.0.50727.42
4. Close all IE (Internet Explorer) windows.
5. Double click the downloaded file: to c:tempdotnetfx.exe. The Microsoft .NET Framework 2.0 Setup windows shows up. Follow the instructions to finish the installation.
6 :: What is mscorsvw.exe - Process - Microsoft .NET Framework NGEN?
Process mscorsvw.exe is installed as a system service as part of the .NET Framework 2.0. You can disable it, if you are not using any applications that require .NET Framework 2.0.
mscorsvw.exe process and program file info:
CPU usage: 00%
Memory usage: 2,704K
Launching method: System Service
Directory: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727
File name: mscorsvw.exe
Description: .NET Runtime Optimization Service
Size: 66,240 bytes
Date: Friday, September 23, 2005, 7:28:56 AM
Version: 2.0.50727.42
Company name: Microsoft
System essential: No
Virus/Spyware/Adware: No
7 :: How to install SQL Server 2005 Express Edition?
Once you have downloaded SQL Server 2005 Express Edition, you should follow this tutorial to install it on your system:
1. Double click SQLEXPR.EXE. The setup window shows up.
2. Click Next to let the setup program to unpack all files from the downloaded file.
3. When unpack is down, the setup program will check all required programs on your system.
4. Then the setup program will start the installation process.
5. On the Authentication Mode window, click the radio button for Mixed Mode (Windows Authentication and SQL Server Authentication). And enter "GlbalGuideLine" in the "Specify the sa logon password below:" fields.
6. Continue to finish the installation process.
7. When installation is done, you will see a new program menu entry as: Start > Programs > Microsoft SQL Server 2005 > Configuration Tools.
8 :: What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS)?
Process sqlservr.exe is the Microsoft SQL Server system service installed as part of the Microsoft SQL Server 2005 Express Edition.
mscorsvw.exe process and program file info:
CPU usage: 00%
Memory usage: 1,316K
Launching method: System Service
Directory: C:Program FilesMicrosoft SQL ServerMSSQL.1
MSSQLBinn
File name: sqlservr.exe
Description: SQL Server Windows NT
Size: 28,768,528 bytes
Date: Friday, October 14, 2005, 3:51:46 AM
Version: 2005.90.1399.0
Company name: Microsoft
System essential: No
Virus/Spyware/Adware: No
9 :: How do you know if SQL Server is running on your local system?
After installing SQL Server 2006 Express Edition, it will be running on your local system quietly as a background process.
If you want to see this process is running, run Windows Task Manager. You should see a process called sqlservr.exe running in the process list:
sqlservr.exe 00 1,316 K
If you select sqlservr.exe and click the "End Process" button, SQL Server will be stopped.
If you can not find sqlservr.exe in the process list, you know that your SQL Server is running.
10 :: How to connect SQL Server Management Studio Express to SQL Server 2005 Express?
Once you have SQL Server 2005 Express installed and running on your local machine, you are ready to connect SQL Server Management Studio Express to the server:
Click Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express to launch SQL Server Management Studio Express.
The "Connect to Server" box shows up. The Server Name field has a default value of "LOCALHOSTSQLEXPRESS". So don't change it. Select "SQL Server Authentication" as the Authentication. Enter enter "sa" as the Login, and "GlbalGuideLine" as the Password.
SQL Server 2005 Connect Window.
Click the Connect button, you should see the SQL Server Management Studio Express window comes up.
11 :: How to download and install Microsoft SQL Server Management Studio Express?
Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services. If you want to download and install it to your system, follow this tutorial:
1. Go to SQL Server Management Studio Express home page.
2. Go to the "Files in This Download" section.
3. Click the Download button next to "SQLServer2005_SSMSEE.msi - 38.5 MB" And save it to c:temp directory.
4. Look at and compare the downloaded file properties with:
Name: SQLServer2005_SSMSEE.msi
Location: C:temp
Size: 40,364,032 bytes
5. Double click to install. The setup window shows up. Follow the instructions to finish the installation process.
6. When installation is done, you will see a new program menu entry as: Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express
12 :: How to download and install SQL Server 2005 Books Online?
1. Go to the SQL Server 2005 Books Online download page.
2. Click the download button, the File Download box shows up. Save the download file to c:temp.
3. Double click on the downloaded file: c:tempSqlServer2K5_BOL_Feb2007.msi. The installation setup window shows up. Follow the instructions to finish the installation.
4. When the installation is done. You will see a new entry in the Start menu: Start > Programs > Microsoft SQL Server 2005 > Documentation and Tutorials
13 :: How to run Queries with SQL Server Management Studio Express?
1. Launch and connect SQL Server Management Studio Express to the local SQL Server 2005 Express.
2. Click on the "New Query" button below the menu line. Enter the following SQL statement in the query window:
SELECT 'Welcome to GlobalGuideLine.com Tips on SQL Server!'
3. Click the Execute button in the toolbar area. You should get the following in the result window:
Welcome to GlobalGuideLine.com Tips on SQL Server!
14 :: How to run SQL Server 2005 Books Online on your local system?
SQL Server 2005 Books Online can be accessed by a Web browser over the Internet. But you can also download it and read it on your local system. If you have downloaded and installed SQL Server 2005 Books Online package, you follow this tutorial to run it:
1. Click Start > Programs > Microsoft SQL Server 2005 > Documentation and Tutorials > Tutorials > SQL Server Tutorials. The SQL Server 2005 Books Online window shows up.
2. Click the plus sign (+) next to "SQL Server 2005 Tutorials in the Contents window".
3. Click the plus sign (+) next to "SQL Server Tools Tutorials".
4. Click "Lesson 1: Basic Navigation in SQL Server Management Studio". The book content shows up for you to read.
15 :: How to use Transact-SQL statements to access the database engine?
Transact-SQL statements can be used to access the database engine directly. Here are some good tutorials provided by the SQL Server 2005 Books Online. See the SQL Server 2005 Tutorials > Database Engine Tutorials > Writing Transact-SQL Statements Tutorial section in the SQL Server 2005 Books Online document.
This SQL Questions Guide is intended for users who are new to writing SQL statements. It will help new users get started by reviewing some basic statements for creating tables and inserting data. This tutorial uses Transact-SQL, the Microsoft implementation of the SQL standard. This tutorial is intended as a brief introduction to the Transact-SQL language and not as a replacement for a Transact-SQL class. The statements in this tutorial are intentionally simple, and are not meant to represent the complexity found in a typical production database.
16 :: How to create new databases with "CREATE DATABASE" statements?
This is the first SQL Questions Guide of a quick lesson on creating database objects with Transact-SQL statements. This section shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Questions Guide assumes that you are running SQL Server Management Studio Express.
Like many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: the name of the database. CREATE DATABASE also has many optional parameters, such as the disk location where you want to put the database files. When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters. This tutorial uses very few of the optional syntax parameters.
To create a database - In a Query Editor window, type but do not execute the following code:
CREATE DATABASE YourDataBaseName
GO
17 :: How to create new table with "CREATE TABLE" statements?
This is the second tutorial of a quick lesson on creating database objects with Transact-SQL statements. This section shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this section is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Guide assumes that you are running SQL Server Management Studio Express.
To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column.
Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value cannot be repeated in the table.
18 :: How to insert and update data into a table with "INSERT" and "UPDATE" statements?
Now you how to create a database, create a table in the database, and then access and change the data in the table. Because here is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. Now we assumes that you are running SQL Server Management Studio Express.
Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.
ProductID ProductName Price ProductDescription
1 Clamp 12.48 Workbench clamp
50 Screwdriver 3.17 Flat head
75 Tire Bar Tool for changing tires
3000 3mm Bracket .52
The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax.
19 :: How to read data in a table with "SELECT" statements?
Now this part is for creating database objects with Transact-SQL statements. This Question shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this Answer is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This Guide assumes that you are running SQL Server Management Studio Express.
Use the SELECT statement to read the data in a table. The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. For this Answer, you will work with five simple versions.
To read the data in a table - Type and execute the following statements to read the data in the Products table.
-- The basic syntax for reading data from a single table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
GO
You can use an asterisk to select all the columns in the table. This is often used in ad hoc queries. You should provide the column list in you permanent code so that the statement will return the predicted columns, even if a new column is added to the table later.
-- Returns all columns in the table
-- Does not use the optional schema, dbo
SELECT * FROM Products
GO
20 :: How to create a login account in MS SQL Server to access the database engine using "CREATE LOGIN" statements?
Now this answer will teach you that how to create login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
To access the Database Engine, users require a login. The login can represent the user's identity as a Windows account or as a member of a Windows group, or the login can be a SQL Server login that exists only in SQL Server. Whenever possible you should use Windows Authentication.
By default, administrators on your computer have full access to SQL Server. For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer. To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server. The following instructions are for Windows XP Professional.
21 :: How to create a user to access a database in MS SQL Server using "CREATE USER" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
Mary now has access to this instance of SQL Server 2005, but does not have permission to access the databases. She does not even have access to her default database YourDataBaseName until you authorize her as a database user.
To grant Mary access, switch to the YourDataBaseName database, and then use the CREATE USER statement to map her login to a user named Mary.
To create a user in a database - Type and execute the following statements (replacing computer_name with the name of your computer) to grant Mary access to the YourDataBaseName database.
USE [YourDataBaseName];
GO
CREATE USER [Mary] FOR LOGIN [computer_nameMary];
GO
Now, Mary has access to both SQL Server 2005 and the YourDataBaseName database.
22 :: How to create a view and a stored procedure in MS SQL Server using "CREATE VIEW/PROCEDURE" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
Now that Mary can access the YourDataBaseName database, you may want to create some database objects, such as a view and a stored procedure, and then grant Mary access to them. A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch.
Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.
23 :: How to grant a permission in MS SQL Server using "GRANT EXECUTE" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This answer shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.
Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.
GRANT EXECUTE ON pr_Names TO Mary;
GO
24 :: How to delete database objects with "DROP" statements in MS SQL Server?
To remove all database objects created by previous tutorials, you could just delete the database. However, in this tutorial, you will go through the steps to reverse every action you took doing the tutorial.
Removing permissions and objects - Before you delete objects, make sure you are in the correct database:
USE YourDataBaseName;
GO
Use the REVOKE statement to remove execute permission for Mary on the stored procedure:
REVOKE EXECUTE ON pr_Names FROM Mary;
GO
Use the DROP statement to remove permission for Mary to access the YourDataBaseName database:
DROP USER Mary;
GO
Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:
DROP LOGIN [Mary];
GO
Use the DROP statement to remove the store procedure pr_Names:
DROP PROC pr_Names;
GO
Use the DROP statement to remove the view vw_Names:
DROP View vw_Names;
GO
Use the DELETE statement to remove all rows from the Products table:
DELETE FROM Products;
GO
Use the DROP statement to remove the Products table:
DROP Table Products;
GO
You cannot remove the YourDataBaseName database while you are in the database; therefore, first switch context to another database, and then use the DROP sta
25 :: What is a database in MS SQL Server?
A database is a logical container that contains a set of related database objects:
* Tables - Storages of structured data.
* Views - Queries to present data from tables.
* Indexes - Sorting indexes to speed up searches.
* Stored Procedures - Predefined SQL program units.
* Users - Identifications used for data access control.
* Other objects.
26 :: What is the simplest way to create a new database in MS SQL Server?
The simplest way to create a new database is to use the "CREATE DATABASE" statement with this syntax:
CREATE DATABASE database_name
For example, run this statement:
CREATE DATABASE YourDataBaseName
GO
A new database called "YourDataBaseName" should be created in the SQL server. Of course, YourDataBaseName database should be empty at this moment - no tables. But it should have some other data objects automatically created by the server.
27 :: How to set the current database in MS SQL Server?
Once you are connected to the SQL Server, you should select a database to work with and set it as the current database using the "USE" statement with this syntax:
USE database_name
The following tutorial example shows you how to set "YourDataBaseName" as the current database, and create a table in "YourDataBaseName":
USE YourDataBaseName
GO
Changed database context to 'YourDataBaseName'.
CREATE TABLE Links (Name NVARCHAR(32))
GO
SELECT name, type_desc, create_date FROM sys.tables
GO
name type_desc create_date
Links USER_TABLE 2007-05-19 23:05:43.700
28 :: How to delete a database in MS SQL Server?
If you created a database incorrectly, or you have a database that is not needed any more, you can delete it with the "DROP DATABASE" statement with this syntax:
DROP DATABASE database_name
For example, execute this statement:
DROP DATABASE YourDataBaseName
GO
The database "YourDataBaseName" created in the previous tutorial should be deleted from the SQL server.
Warning, if you delete a database, all tables and their data in that database will be deleted.
29 :: Why I am getting this error when dropping a database in MS SQL Server?
If you are trying to drop a database that is in use, you will get an error message like this: 'Cannot drop database "GlobalGuidelineData" because it is currently in use.'
Before dropping a database, you must stop all client sessions using this database. If your own client session is using this database, you should set a different database as the current database as shown in this tutorial example:
CREATE DATABASE GlobalGuidelineData
GO
USE GlobalGuidelineData
GO
DROP DATABASE GlobalGuideLineDatabase
GO
Msg 3702, Level 16, State 4, Server LOCALHOSTSQLEXPRESS
Cannot drop database "GlobalGuideLineDatabase" because it is
currently in use.
USE master
GO
DROP DATABASE GlobalGuideLineDatabase
GO
30 :: How to get a list all databases on the SQL server?
If you don't remember database names you have created, you can get a list of all databases on the server by query the "sys.databases" view as shown in this tutorial example:
CREATE DATABASE GlobalGuideLineDatabase
GO
SELECT name, database_id, create_date FROM sys.databases
GO
[name] [database_id] [create_date]
master 1 2003-04-08 09:13:36.390
tempdb 2 2007-05-19 13:42:42.200
model 3 2003-04-08 09:13:36.390
msdb 4 2005-10-14 01:54:05.240
GlobalGuideLineDatabase 5 2007-05-19 20:04:39.310
As you can see, the newly created database is listed at the end of query result.
31 :: Where is my database stored on the hard disk in MS SQL Server?
If a database is created with simple CREATE DATABASE statement, the server will create two database files on the hard disk to store data and configuration information about that data bases:
* database_name.mdf - SQL Server Database Primary Data File
* database_name_log.ldf - SQL Server Database Transaction Log File
To find out the location of database files, you can query the "sys.database_files" view as shown in this tutorial example:
USE GlobalGuideLineDatabase
GO
SELECT type_desc, physical_name, size
FROM sys.database_files
GO
type_desc physical_name size
ROWS c:Program FilesMicrosoft SQL Server
MSSQL.1MSSQLDATAGlobalGuideLineDatabase.mdf 152
LOG c:Program FilesMicrosoft SQL Server
MSSQL.1MSSQLDATAGlobalGuideLineDatabase_log.LDF 63
Go verify these two files with Windows Explorer.
32 :: How to create database with physical files specified in MS SQL Server?
If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement:
CREATE DATABASE database_name
ON (NAME = logical_data_name,
FILENAME = physical_data_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
LOG ON (NAME = logical_log_name,
FILENAME = physical_log_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
For example, the following statement will create a database with database files located in the C:temp directory:
USE master
GO
DROP DATABASE GlobalGuideLineDatabase
GO
CREATE DATABASE GlobalGuideLineDatabase
ON (NAME = GlobalGuideLineDatabase,
FILENAME = 'C:tempGlobalGuideLineDatabase.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C:tempGlobalGuideLineDatabase.ldf',
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)
GO
SELECT type_desc, name, physical_name, size
FROM sys.database_files
GO
type_desc name physical_name size
ROWS GlobalGuideLineDatabase C:tempGlobalGuideLineDatabase.mdf 1280
LOG GlobalGuidelineLog C:tempGlobalGuideLineDatabase.ldf 128
33 :: How to rename databases in MS SQL Server?
If don't like the name of a database, you can change it by using the "ALTER DATABASE" statement with the following syntax:
ALTER DATABASE database_name
MODIFY NAME = new_database_name
The tutorial example below shows you how change the database name from "GlobalGuideLineDatabase" to "GlobalGuideLine":
ALTER DATABASE GlobalGuideLineDatabase
MODIFY NAME = GlobalGuideLine
GO
The database name 'GlobalGuideLine' has been set.
34 :: Why I am getting this error when renaming a database in MS SQL Server?
If you are trying to rename a database that is in use, you will get an error message like this: "The database could not be exclusively locked to perform the operation."
Before renaming a database, you must stop all client sessions using this database. Otherwise, you will get an error as shown in this tutorial example:
1. Launch one instance of SQL Server Management Studio and run:
USE GlobalGuideLine
GO
2. Keep the first instance running and launch another instance of SQL Server Management Studio:
ALTER DATABASE GlobalGuideLine
MODIFY NAME = GlobalGuideLineDatabase
GO
Msg 5030, Level 16, State 2, Server LOCALHOSTSQLEXPRESS
The database could not be exclusively locked to perform
the operation.
Obviously, the first instance is blocking the "ALTER DATABASE" statement.
35 :: What are database states in MS SQL Server?
A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys.databases catalog view. The following table defines the database states.
* ONLINE - Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
* OFFLINE - Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
* RESTORING - One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
* RECOVERING - Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
36 :: How to set a database state to OFFLINE in MS SQL Server?
If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax:
ALTER DATABASE database_name SET OFFLINE
The following tutorial example will bring "GlobalGuideLine" offline:
ALTER DATABASE GlobalGuideLine SET OFFLINE
GO
SELECT name, state_desc from sys.databases
GO
name state_desc
master ONLINE
tempdb ONLINE
model ONLINE
msdb ONLINE
GlobalGuideLine OFFLINE
USE GlobalGuideLine
GO
Msg 942, Level 14, State 4, Line 1
Database 'GlobalGuideLine' cannot be opened because it is offline.
37 :: How to move database physical files in MS SQL Server?
If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, and link it to the files at the new location. The following tutorial gives you a good example:
ALTER DATABASE GlobalGuideLine SET ONLINE
GO
USE GlobalGuideLine
GO
CREATE TABLE Links (Name NVARCHAR(32))
GO
ALTER DATABASE GlobalGuideLine SET OFFLINE
GO
Now it is safe to move the database physical files to a new location:
1. Run Windows Explorer
2. Create a new directory: c:tempdata
3. Drag and drop c:tempGlobalGuideLine.mdf to c:tempdata
3. Drag and drop c:tempGlobalGuideLine.mdf to c:tempdata
Go back to the SQL client program and run:
ALTER DATABASE GlobalGuideLine
MODIFY FILE (NAME = GlobalGuideLineDB,
FILENAME = 'C:tempdataGlobalGuideLineDB.mdf')
GO
38 :: How to set database to be READ_ONLY in MS SQL Server?
Databases in SQL Server have two update options:
* READ_WRITE - Data objects are allowed to be queried and modified. This is the default.
* READ_ONLY - Data objects are allowed to be queried, but not allowed to be modified.
You can use the "ALTER DATABASE" to change database update options as shown in the tutorial below:
USE GlobalGuideLineDB
GO
INSERT Links (Name) VALUES ('www.skills9.com')
GO
(1 rows affected)
ALTER DATABASE GlobalGuideLineDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.skills9.com')
GO
Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1
Failed to update database "GlobalGuideLineDB" because
the database is read-only.
SELECT * FROM Links
GO
Name
www.skills9.com
ALTER DATABASE GlobalGuideLineDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.skills9.com')
GO
(1 rows affected)
As you can see from the output, inserting data into a table is not allowed if the database is in READ_ONLY mode.
39 :: How to set database to be SINGLE_USER in MS SQL Server?
Databases in SQL Server have three user access options:
* MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. This is the default.
* SINGLE_USER - One user at a time is allowed to connect to the database. All other user connections are broken.
* RESTRICTED_USER - Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.
You can use the "ALTER DATABASE" to change database user access options as shown in the tutorial below:
USE GlobalGuideLineDatabase
GO
ALTER DATABASE GlobalGuideLineDatabase SET SINGLE_USER
GO
Now connect to server with another client session and try:
USE GlobalGuideLineDatabase
GO
Msg 924, Level 14, State 1, Line 1
Database 'GlobalGuideLineDatabase' is already open and can only
have one user at a time.
Go back to the first session and re-set the database to MULTI_USER:
ALTER DATABASE GlobalGuideLineDatabase SET MULTI_USER
GO
40 :: What are system databases in MS SQL Server?
System databases are created by the SQL Server itself during the installation process. System databases are used by the SQL server to help manage other user databases and client execution sessions. SQL Server 2005 Express Edition uses 4 system databases:
* master - The brain of a SQL server - Stores server configuration, runtime information, and database metadata.
* model - An empty database model - Used to clone new databases.
* msdb - The background job scheduler - Used for background jobs and related tasks.
* tempdb - The temporary database - Used by the server as a scratch pad.
41 :: What is a database table?
A table in database is a data object used to store data. Tables have the following features:
* Data is stored in a table with a structure of rows and columns.
* Columns must be pre-defined with names, types and constrains.
* A table object may have other associated data objects like, constrains, triggers, indexes, and statistics.
For example, a table called Address may have columns defined to store different elements of an address like, street number, city, country, postal code, etc.
42 :: What are DDL (Data Definition Language) statements for tables in MS SQL Server?
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are three primary DDL statements to create and manage tables:
* CREATE TABLE - Creating a new table.
* ALTER TABLE - Altering the definition of an existing table.
* DROP TABLE - Dropping an existing table.
43 :: How to create new tables with "CREATE TABLE" statements in MS SQL Server?
If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":
CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATETIME NULL)
GO
This scripts creates a testing table called "tip" with 4 columns in the current database.
44 :: How To Get a List of All Tables with "sys.tables" View in MS SQL Server?
If you want to see the table you have just created, you can use the "sys.tables" system view to get a list of all tables in the current database. The tutorial script gives you a good example:
SELECT name, type_desc, create_date FROM sys.tables
GO
name type_desc create_date
tip USER_TABLE 2007-05-19 23:05:43.700
The output shows that there is only one table in the current database.
45 :: How To Get a List of Columns using the "sys.columns" View in MS SQL Server?
If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database.
In order to a list of columns of a single table, you need to join sys.columns and sys.tables as shown in the tutorial example below:
SELECT * FROM sys.columns c, sys.tables t
WHERE c.object_id = t.object_id
AND t.name = 'tip'
GO
object_id name column_id user_type_id max_length
2073058421 id 1 56 4
2073058421 subject 2 167 80
2073058421 description 3 167 256
2073058421 create_date 4 61 8
You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.
46 :: How To Get a List of Table Columns using the "sp_columns" Stored Procedure in MS SQL Server?
If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sp_columns" stored procedure to get a list of all columns of the specified table. The following tutorial script shows you a good example:
sp_columns tip
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar ...
dbo tip description varchar ...
dbo tip create_date datetime ...
The "sp_columns" stored procedure returns a long list of properties for each column of the specified table. Take a look at each of them.
47 :: How To Get a List of Columns using the "sp_help" Stored Procedure in MS SQL Server?
Another way to get a list of columns from a table is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the table information, the column information, the identity column, the row GUID column, the primary key, indexes, and constraints. It you run "sp_help tip" in SQL Server Management Studio, you will see the result as shown in this picture:
sp_help Stored Procedure
48 :: How To Generate CREATE TABLE Script on an Existing Table in MS SQL Server?
If you want to know how an existing table was created, you can use SQL Server Management Studio to automatically generate a "CREATE TABLE" script The following tutorial shows you how to do this:
1. Run SQL Server Management Studio and connect to SQL server.
2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Tables > dbo.tip.
3. Click right mouse button on dbo.tip. The context menu shows up.
4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:
USE [GlobalGuideLineDatabase]
GO
/****** Object: Table [dbo].[tip]
Script Date: 05/05/2008 11:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tip](
[id] [int] NOT NULL,
[subject] [varchar](80)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [varchar](256)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[create_date] [datetime] NULL,
PRIMARY KEY CLUSTERED (
[id] ASC
)WITH (PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
49 :: How to create new tables with "SELECT ... INTO" statements in MS SQL Server?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "SELECT ... INTO" statement. The tutorial script below gives you a good example:
INSERT INTO tip VALUES (1, 'Learn SQL',
'Visit www.skills9.com','2006-05-01')
GO
SELECT * INTO tipBackup FROM tip
GO
(1 rows affected)
SELECT * FROM tipBackup
GO
id subject description create_date
1 Learn SQL Visit www.skills9.com 2008-05-01
sp_columns tipBackup
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tipBackup id int ...
dbo tipBackup subject varchar ...
dbo tipBackup description varchar ...
dbo tipBackup create_date datetime ...
As you can see, the "SELECT ... INTO" statement created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".
50 :: How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD" in MS SQL Server?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD" statement. The tutorial script below shows you a good example:
ALTER TABLE tip ADD author VARCHAR(40)
GO
sp_columns tip
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar ...
dbo tip description varchar ...
dbo tip create_date datetime ...
dbo tip author datetime ...
SELECT * FROM tip
GO
id subject description create_date author
1 Learn SQL Visit www.skills9.com 2008-05-01 NULL
This SQL script added a new column called "author" to the "tip" table. NULL values were added to this column on all existing data rows.
51 :: How to rename an existing column with the "sp_rename" stored procedure in MS SQL Server?
If you have an existing column in a table and you want to change the column name, you can use the "sp_rename ... 'COLUMN'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT. The tutorial example below shows you how to rename a column:
USE master
GO
sp_rename 'tip.subject', 'title', 'COLUMN'
GO
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213
Either the parameter @objname is ambiguous or the claimed
@objtype (COLUMN) is wrong.
USE GlobalGuideLineDatabase
GO
sp_rename 'tip.subject', 'title', 'COLUMN'
GO
Caution: Changing any part of an object name could break
scripts and stored procedures.
SELECT id, title, description, author FROM tip
GO
id title description author
1 Learn SQL Visit www.skills9.com NULL
You are getting the first error because 'GlobalGuideLineDatabase' is not the current database.
52 :: How to rename an existing column with SQL Server Management Studio?
If you are using SQL Server Management Studio, you can rename almost any data objects through the Object Explorer window. The tutorial example below shows you how to rename a column:
1. Run SQL Server Management Studio and connect to SQL server.
2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Tables > dbo.tip > Columns > title.
3. Click right mouse button on "title". The context menu shows up.
4. Select "Rename", type "subject" over "title", and press Enter key. The column name will be changed.
53 :: How to change the data type of an existing column with "ALTER TABLE" statements in MS SQL Server?
Sometimes, you may need to change the data type of an existing column. For example, you want increase the string length of a column. You can use the "ALTER TABLE ... ALTER COLUMN" statements in the following syntax:
ALTER TABLE table_name ALTER COLUMN column_name new_type
Here is a good example of change column data types:
-- Can not make a string column shorter
ALTER TABLE tip ALTER COLUMN subject VARCHAR(10)
GO
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
-- Can make a string column longer
ALTER TABLE tip ALTER COLUMN subject VARCHAR(100)
GO
Command(s) completed successfully.
-- Can not change string to numeric
ALTER TABLE tip ALTER COLUMN subject NUMBER
GO
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The statement has been terminated.
As you can see, the new date type must be compatible with the old data type in order for the "ALTER TABLE ... ALTER COLUMN" statement to work.
54 :: How to rename an existing table with the "sp_rename" stored procedure in MS SQL Server?
If you have an existing table and you want to change the table name, you can use the "sp_rename ... 'OBJECT'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT (including tables). The tutorial example below shows you how to rename a table:
sp_rename 'tip', 'faq', 'OBJECT'
GO
Caution: Changing any part of an object name could break
scripts and stored procedures.
SELECT name, type_desc, create_date FROM sys.tables
GO
name type_desc create_date
faq USER_TABLE 2007-05-19 23:05:43.700
tipBackup USER_TABLE 2007-05-19 23:25:23.357
You can also rename a table with on the Object Explorer window of SQL Server Management Studio. See tutorials on rename table columns.
55 :: How To Drop an Existing Table with "DROP TABLE" Statements in MS SQL Server?
If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:
SELECT * FROM tipBackup
GO
id subject description create_date
1 Learn SQL Visit www.skills9.com 2006-07-01
DROP TABLE tipBackup
GO
SELECT * FROM tipBackup
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tipBackup'.
Be careful, when you use the "DROP TABLE" statement. All data rows are gone too.
56 :: What Are DML (Data Manipulation Language) Statements in MS SQL Server?
DML (Data Manipulation Language) statements are statements to change data values in database tables. The are 3 primary DML statements:
* INSERT - Inserting new rows into database tables. For example "INSERT INTO ggl_links VALUES (101, 'www.skills9.com', NULL, 0, '2006-04-30')" inserts a single new row in the ggl_links table.
* UPDATE - Updating existing rows in database tables .
* DELETE - Deleting existing rows from database tables.
57 :: How To Create a Testing Table with Test Data in MS SQL Server?
If you want to practice DML statements, like INSERT, UPDATE and DELETE statements, you should create a testing table. The tutorial exercise shows you a good example:
CREATE TABLE ggl_links (id INTEGER PRIMARY KEY,
url VARCHAR(80) NOT NULL,
notes VARCHAR(1024),
counts INT,
created DATETIME NOT NULL DEFAULT(getdate()))
GO
SELECT c.column_id as seq, c.name, x.name as type,
c.max_length, c.is_nullable
FROM sys.columns c, sys.tables t, sys.systypes x
WHERE c.object_id = t.object_id
AND c.system_type_id = x.xtype
AND t.name = 'ggl_links'
ORDER BY c.column_id
GO
seq name type max_length is_nullable
1 id int 4 0
2 url varchar 80 0
3 notes varchar 1024 1
4 counts int 4 1
5 created datetime 8 0
You should keep this table to practice other tutorial exercises presented in this collection.
58 :: How To Insert a New Row into a Table with "INSERT INTO" Statements in MS SQL Server?
To insert a new row into a table, you can use the INSERT INTO statement with values specified for all columns as in the following syntax:
INSERT INTO table_name VALUES (list_of_values_of_all columns)
Note that the list of values of all columns must be specified in the same order as how columns are defined in the CREATE TABLE statement. The following tutorial example inserts a row into "ggl_links":
INSERT INTO ggl_links VALUES (101,
'www.skills9.com',
NULL,
0,
'2006-04-30')
GO
(1 row(s) affected)
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
The values are stored in the new record nicely.
59 :: How To Use Column Default Values in INSERT Statements in MS SQL Server?
If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column. The following tutorial exercise gives a good example:
INSERT INTO ggl_links VALUES (102,
'www.skills9.com',
NULL,
0,
DEFAULT)
GO
(1 row(s) affected)
SELECT * FROM fyi_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
102 www.skills9.com NULL 0 2007-05-19
The default value, getdate(), is used for "created" column, which gives the current date.
60 :: How to provide column names in INSERT Statements in MS SQL Server?
If you don't want to specify values for columns that have default values, or you want to specify values to columns in an order different than how they are defined, you can provide a column list in the INSERT statement. If a column is omitted in the column, SQL Server applies 3 rules:
* If default value is defined for the column, that default value will be used.
* If no default value is defined for the column and NULL is allowed, NULL will be used.
* If no default value is defined for the column and NULL is not allowed, SQL Server will reject the insert statement with an error.
The following tutorial exercise gives you some good examples:
INSERT INTO ggl_links (url, id)
VALUES ('www.skills9.com',103)
GO
(1 row(s) affected)
INSERT INTO ggl_links (id) VALUES (110)
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'url',
table 'skills9Database.dbo.ggl_links'; column does
not allow nulls. INSERT fails.
The statement has been terminated.
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
102 www.skills9.com NULL 0 2007-05-19
103 www.skills9.com NULL NULL 2007-05-19
61 :: What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server?
If your table has a primary key column, and you are trying to insert a new row with duplicate key value on the primary key column, you will get an error. The reason is simple - Primary key column does not allow duplicate values. The following tutorial exercise gives you a good example:
SELECT * FROM ggl_links
INSERT INTO ggl_links VALUES (101,
'www.skills9.com',
NULL,
0,
'2006-04-30')
GO
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint
'PK__ggl_links__03317E3D'. Cannot insert duplicate
key in object 'dbo.ggl_links'.
The statement has been terminated.
You are getting this error, because value "101" has already been used by an existing row.
62 :: How To Insert Multiple Rows with One INSERT Statement in MS SQL Server?
If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives you a good example:
INSERT INTO ggl_links SELECT id+500, REVERSE(url),
notes, counts, created FROM ggl_links
GO
(3 row(s) affected)
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
102 www.skills9.com/html NULL 0 2007-05-19
103 www.skills9.com/sql NULL NULL 2007-05-19
601 www.skills9.com/seo NULL 0 2006-04-30
602 www.skills9.com/xml NULL 0 2007-05-19
603 www.skills9.com/JavaScript_Guide NULL NULL 2007-05-19
As you can see, "INSERT INTO ... SELECT ..." is powerful statement. you can use it build up data in tables quickly.
63 :: How To Update Values in a Table with UPDATE Statements in MS SQL Server?
If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example:
SELECT * FROM ggl_links WHERE id = 101
GO
id url notes counts created
101 www.skills9.com NULL 0 2006-04-30
UPDATE ggl_links SET counts = 999, notes = 'Good.'
WHERE id = 101;
GO
(1 row(s) affected)
SELECT * FROM ggl_links WHERE id = 101
GO
id url notes counts created
101 www.skills9.com Good. 999 2006-04-30
As you can see, the SET clause takes column and value pairs to provide new values, while the WHERE clause defines which row to apply the update.
64 :: How To Update Multiple Rows with One UPDATE Statement in MS SQL Server?
If the WHERE clause in an UPDATE statement matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example:
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 www.skills9.com NULL 0 2006-04-30
602 www.skills9.com/html NULL 0 2007-05-19
603 www.skills9.com/sql NULL NULL 2007-05-19
SELECT * FROM ggl_links
UPDATE ggl_links SET counts = 9, notes = 'Wrong'
WHERE id >= 500
(3 row(s) affected)
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 www.skills9.com Wrong 9 2006-04-30
602 www.skills9.com/html Wrong 9 2007-05-19
603 www.skills9.com/sql Wrong 9 2007-05-19
The UPDATE statement updated 3 rows with the same new values.
65 :: How to use old values to define new values in UPDATE statements in MS SQL Server?
If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exercise below shows you a good example:
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
601 www.skills9.com Wrong 9 2006-04-30
602 www.skills9.com/html Wrong 9 2007-05-21
603 www.skills9.com/sql Wrong 9 2007-05-23
UPDATE ggl_links SET id = id+200, counts = id*2
WHERE id >= 500
GO
(3 row(s) affected)
SELECT * FROM ggl_links WHERE id >= 500
GO
id url notes counts created
801 www.skills9.com Wrong 1202 2006-04-30
802 www.skills9.com/html Wrong 1204 2007-05-19
803 www.skills9.com/sql Wrong 1206 2007-05-19
This statement increased values in the id column by 200. It also updated the counts column with the newly increased id value.
66 :: Is the Order of Columns in the SET Clause Important in MS SQL Server?
The answer is NO. The order of columns in the SET clause of the UPDATE statement is NOT important. You probably already noticed from the previous tutorial. There is a BIG DIFFERENCE among SQL Server, MySQL and Oracle on update multiple columns with previous values:
* SQL Server provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
* MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.
* Oracle provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
Here is a good tutorial exercise:
SELECT * FROM ggl_links
-- Check the old values
SELECT * FROM ggl_links WHERE url = 'www.skills9.com'
GO
id url notes counts created
101 www.skills9.com Good. 999 2006-04-30
-- Update "id" before "counts"
UPDATE ggl_links SET id = id+200, counts = id*2
WHERE url = 'www.skills9.com'
GO
(1 row(s) affected)
67 :: How To Use Values from Other Tables in UPDATE Statements in MS SQL Server?
If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clause. The tutorial exercise below shows you a good example:
-- Create another table
CREATE TABLE ggl_rates (id INTEGER,
comment VARCHAR(16))
Go
-- Insert some rows in the new table
INSERT INTO ggl_rates VALUES (101, 'The best')
Go
INSERT INTO ggl_rates VALUES (102, 'Well done')
GO
INSERT INTO ggl_rates VALUES (103, 'Thumbs up')
Go
-- Update ggl_links with values from ggl_rates
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id > 0 AND id < 110
GO
(3 row(s) affected)
-- View the updated values
SELECT * FROM ggl_links
WHERE id > 0 AND id < 110
GO
id url notes counts created
101 www.skills9.com The best 999 2006-04-30
102 www.skills9.com/html Well done 0 2007-05-19
103 www.skills9.com/sql Thumbs up NULL 2007-05-19
Note that if column names are confusing between the inner table and the outer table, you need to pref
68 :: What Happens If the UPDATE Subquery Returns No Rows in MS SQL Server?
If you use a subquery to assign new values in the SET clause in an UPDATE statement, and the subquery returns no rows for an outer row, SQL Server will provide a NULL value to the SET clause. The tutorial exercise below shows you a good example:
-- insert a new row
INSERT INTO ggl_links (id, url, notes)
VALUES (0, 'www.skills9.com', 'Number one')
GO
(1 row(s) affected)
-- view old values
SELECT * FROM ggl_links WHERE id = 0
GO
id url notes counts created
0 www.skills9.com Number one NULL 2007-05-23
-- make sure there is no matching row in fyi_rates
SELECT * FROM ggl_rates WHERE id = 0
GO
0 rows
-- update a subquery returning no rows
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id = 0
(1 row(s) affected)
-- view new values
SELECT * FROM ggl_links WHERE id = 0
GO
id url notes counts created
0 www.skills9.com NULL NULL 2007-05-23
Column "notes" gets updated with NULL if there is no return rows in the subquery.
69 :: What Happens If the UPDATE Subquery Returns Multiple Rows in MS SQL Server?
If a subquery is used in a UPDATE statement, it must return exactly one row for each row in the update table that matches the WHERE clause. If it returns multiple rows, SQL Server will give you an error message. To test this out, you can try the following tutorial exercise:
-- insert two rows to ggl_rates
INSERT INTO ggl_rates VALUES (0, 'Number 1')
GO
INSERT INTO ggl_rates VALUES (0, 'Number 2')
GO
-- make sure there are 2 match rows
SELECT * FROM ggl_rates WHERE id = 0
GO
id comment
0 Number 1
0 Number 2
-- update with subquery that returns 2 rows
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id = 0
GO
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted
when the subquery follows =, !=, <, <= , >, >= or when
the subquery is used as an expression.
The statement has been terminated.
It is clear that we are using subquery as an expression, and it must return 0 or 1 row. Otherwise, we will get an error.
70 :: How To Delete an Existing Row with DELETE Statements in MS SQL Server?
If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements:
-- insert a row for this test
INSERT INTO ggl_links (url, id)
VALUES ('www.google.com', 301)
GO
(1 row(s) affected)
-- view the inserted row
SELECT id, url, notes, counts FROM ggl_links
WHERE id = 301
GO
id url notes counts
301 www.google.com NULL NULL
-- delete one row
DELETE FROM ggl_links WHERE id = 301
GO
(1 row(s) affected)
-- try to view the deleted row
SELECT id, url, notes, counts FROM ggl_links
WHERE id = 301
no rows
Row with id of 301 is truly deleted.
71 :: How To Delete Multiple Rows with One DELETE Statement in MS SQL Server?
You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the ggl_links table:
-- view rows to be deleted
SELECT id, url, notes, counts FROM ggl_links
WHERE id > 300
GO
id url notes counts
801 www.skills9.com Wrong 1202
802 www.skills9.com/html Wrong 1204
803 www.skills9.com/sql Wrong 1206
-- delete multiple rows
DELETE FROM ggl_links WHERE id > 300
GO
(3 row(s) affected)
-- try to view the deleted row
SELECT id, url, notes, counts FROM ggl_links
WHERE id > 300
GO
no rows
72 :: How To Delete All Rows with TRUNCATE TABLE Statement in MS SQL Server?
If you want to delete all rows from a table, you have two options:
* Use the DELETE statement with no WHERE clause.
* Use the TRUNCATE TABLE statement.
The TRUNCATE statement is more efficient the DELETE statement. The tutorial exercise shows you a good example of TRUNCATE statement:
SELECT COUNT(*) FROM ggl_rates
GO
5
TRUNCATE TABLE ggl_rates
GO
SELECT COUNT(*) FROM ggl_rates
GO
0
73 :: How To Join Two Tables in a Single Query in MS SQL Server?
Two tables can be joined together in a query in 4 ways:
* Inner Join: Returns only rows from both tables that satisfy the join condition.
* Left Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the first (left) table.
* Right Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the second (right) table.
* Full Outer Join: Returns rows from both tables that satisfy the join condition, the rest of rows from the first (left) table, and the rest of rows from the second (right) table.
74 :: How To Write a Query with an Inner Join in MS SQL Server?
If you want to query from two tables with an inner join, you can use the INNER JOIN ... ON clause in the FROM clause. The tutorial exercise below creates another testing table and returns output with an inner join from two tables: ggl_links and ggl.rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
CREATE TABLE ggl_rates (id INTEGER,
comment VARCHAR(16))
GO
INSERT INTO ggl_rates VALUES (101, 'The best')
GO
INSERT INTO ggl_rates VALUES (102, 'Well done')
GO
INSERT INTO ggl_rates VALUES (103, 'Thumbs up')
GO
INSERT INTO ggl_rates VALUES (204, 'Number 1')
GO
INSERT INTO ggl_rates VALUES (205, 'Not bad')
GO
INSERT INTO ggl_rates VALUES (206, 'Good job')
GO
INSERT INTO ggl_rates VALUES (207, 'Nice tool')
GO
SELECT ggl_links.id, ggl_links.url,
ggl_rates.comment FROM ggl_links
INNER JOIN ggl_rates ON ggl_links.id = ggl_rates.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
Note that when multiple tables are used in a query, column names need to be prefixed with table names in case the same colu
75 :: How To Define and Use Table Alias Names in MS SQL Server?
When column names need to be prefixed with table names, you can define table alias name and use them to prefix column names. To define an alias for a table name, just enter the alias name right after the original table name in the FROM clause as shown in the following select statement:
SELECT l.id, l.url, r.comment FROM ggl_links l
INNER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
76 :: How To Write a Query with a Left Outer Join in MS SQL Server?
If you want to query from two tables with a left outer join, you can use the LEFT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a left outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
SELECT l.id, l.url, r.comment FROM ggl_links l
LEFT OUTER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
104 www.google.com NULL
105 www.yahoo.com NULL
106 www.php.net NULL
107 www.mysql.com NULL
Note that a left outer join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.
The extra rows returned from the left outer join in this example represents links that have no rates in the above example.
77 :: How To Write a Query with a Right Outer Join in MS SQL Server?
If you want to query from two tables with a right outer join, you can use the RIGHT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a right outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
SELECT l.id, l.url, r.comment FROM ggl_links l
RIGHT OUTER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
NULL NULL Number 1
NULL NULL Not bad
NULL NULL Good job
NULL NULL Nice tool
Note that a right outer join may return extra rows from the second (right) table that do not satisfy the join condition. In those extra rows, columns from the first (left) table will be given null values.
The extra rows returned from the right outer join in this example represents rates that have no links in the above example.
78 :: How To Write a Query with a Full Outer Join in MS SQL Server?
If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:
SELECT l.id, l.url, r.comment FROM ggl_links l
FULL OUTER JOIN ggl_rates r ON l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/seo Thumbs up
104 www.google.com NULL
105 www.yahoo.com NULL
106 www.php.net NULL
107 www.mysql.com NULL
NULL NULL Number 1
NULL NULL Not bad
NULL NULL Good job
NULL NULL Nice tool
As you can see, an full outer join returns 3 groups of rows:
* The rows from both tables that satisfy the join condition.
* The rows from the first (left) table that do not satisfy the join condition.
* The rows from the second (right) table that do not satisfy the join condition.
79 :: How To Write an Inner Join with the WHERE Clause in MS SQL Server?
If you don't want to use the INNER JOIN ... ON clause to write an inner join, you can put the join condition in the WHERE clause as shown in the following query example:
SELECT l.id, l.url, r.comment
FROM ggl_links l, ggl_rates r WHERE l.id = r.id
GO
id url comment
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/sql Thumbs up
80 :: How To Name Query Output Columns in MS SQL Server?
Each column in the query output has a default name. If you don't like the default name, you can specify a new name for any column in the query output by using the AS clause. The following statement shows you a good example:
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM ggl_links
GROUP BY tag, YEAR(created) ORDER BY COUNT(*) DESC
GO
Category Year Counts
HTML 2003 1
SQL 2004 1
SEO 2005 1
Jobs 2006 1
XML 2006 1
XSL 2007 1
JavaScript 2007 1
81 :: What Is a Subquery in a SELECT Query Statement in MS SQL Server?
A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The output of the subquery will be used in the final evaluation of the criteria. Usually, subqueries are used in the following Boolean operations:
* "expression IN (subquery)" - True if the expression matches one of the returned values from the subquery.
* "expression NOT IN (subquery)" - True if the expression does not match any of the returned values from the subquery.
* "EXISTS (subquery)" - True if the subquery returns one or more rows.
* "NOT EXISTS (subquery)" - True if the subquery returns no rows.
82 :: How To Use Subqueries with the IN Operators in MS SQL Server?
A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator. It returns all links with ids in the ggl_rates table.
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links WHERE id IN (SELECT id FROM ggl_rates)
GO
id url tag Year
101 www.skills9.com main 2006
102 www.skills9.com/html HTMLA 2007
103 www.skills9.com/sql SQL 2007
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links
WHERE id IN (101, 102, 103, 204, 205, 206, 207)
GO
id url tag Year
101 www.skills9.com main 2006
102 www.skills9.com/html HTMLA 2007
103 www.skills9.com/sql SQL 2007
As you can see, the subquery is equivalent to a list of values.
83 :: How To Use Subqueries with the EXISTS Operators in MS SQL Server?
A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from ggl_links table that there are rows existing in the ggl_rates table with the same id.
SELECT id, url, tag, YEAR(created) As year
FROM ggl_links WHERE EXISTS (
SELECT * FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id)
GO
id url tag Year
101 www.skills9.com main 2006
102 www.skills9.com/html DBA 2007
103 www.skills9.com/sql SQL 2007
Note that the subquery uses columns from the source table of the outer query.
84 :: How To Use Subqueries in the FROM Clause in MS SQL Server?
If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:
SELECT * FROM (SELECT l.id, l.url, r.comment
FROM ggl_links l LEFT OUTER JOIN ggl_rates r
ON l.id = r.id) WHERE url LIKE '%er%'
GO
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
SELECT * FROM (SELECT l.id, l.url, r.comment
FROM ggl_links l LEFT OUTER JOIN ggl_rates r
ON l.id = r.id) s WHERE s.url LIKE '%er%'
GO
101 www.skills9.com The best
102 www.skills9.com/html Well done
103 www.skills9.com/xml Thumbs up
107 www.skills9.com/sql NULL
The error on the first query is caused by the missing alias name to name output of the subquery as a temporary table.
85 :: How To Count Groups Returned with the GROUP BY Clause in MS SQL Server?
If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. If you want to count the number of groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on the main query as shown in the following tutorial exercise:
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM ggl_links GROUP BY tag,
YEAR(created)
GO
Category Year Counts
HTML 2003 1
XML 2004 1
CSS 2005 1
SQL 2006 1
SEO 2006 1
JavaScript 2007 1
JOBS 2007 1
SELECT COUNT(*) FROM (
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM ggl_links GROUP BY tag,
YEAR(created) ) groups
GO
7
86 :: How To Return the Top 5 Rows from a SELECT Query in MS SQL Server?
If you want the query to return only the first 5 rows, you can use the "TOP 5" clause. The TOP clause takes one parameter to indicate how many top rows to return. The following statements returns the first 5 rows and 3 rows from the ggl_links:
SELECT TOP 5 id, url, counts, tag FROM ggl_links
ORDER BY counts DESC
GO
id url counts tag
102 www.skills9.com 972 DBA
105 www.google.com 960 DBA
107 www.yahoo.com 828 SQA
103 www.mysql.com 728 SQA
106 www.php.net 439 DEV
SELECT TOP 3 id, url, counts, tag FROM ggl_links
ORDER BY counts DESC
GO
id url counts tag
102 www.skills9.com 972 DBA
105 www.google.com 960 DBA
107 www.yahoo.com 828 SQA
87 :: How To Return the Second 5 Rows in MS SQL Server?
If you want to display query output in multiple pages with 5 rows per page, and the visitor wants to see the output for the second page, you need to display query output from row 6 to row 10. If you are using MySQL server, you can use the "LIMIT startRow maxRows".
But the LIMIT clause is not supported by the SQL server. And there seems to be no easy workaround. You may consider to return the top 10 rows, skip the first 5 rows, then keep the second 5 rows.
88 :: How To Use UNION to Merge Outputs from Two Queries Together in MS SQL Server?
If you have two queries that returns the same row fields, you can merge their outputs together with the UNION operator. The following tutorial exercise shows you how to use the UNION operator:
SELECT * FROM ggl_links WHERE tag = 'DBA'
GO
id url notes counts created tag
102 skills9.com NULL 972 2007-05-19 DBA
104 www.mysql.com 390 2006-01-01 DBA
105 www.oracle.com 960 2005-01-01 DBA
SELECT * FROM ggl_links WHERE tag = 'DEV'
GO
id url notes counts created tag
101 skills9.com/html NULL 120 2006-04-30 DEV
106 www.php.net 439 2004-01-01 DEV
SELECT * FROM ggl_links WHERE tag = 'DBA'
UNION
SELECT * FROM ggl_links WHERE tag = 'DEV'
GO
id url notes counts created tag
102 skills9.com NULL 972 2007-05-19 DBA
104 www.mysql.com 390 2006-01-01 DBA
105 www.oracle.com 960 2005-01-01 DBA
101 skills9.com/html NULL 120 2006-04-30 DEV
106 www.php.net 439 2004-01-01 DEV
89 :: How To Use ORDER BY with UNION Operators in MS SQL Server?
If you need to sort the output from two queries grouped together with a UNION operator, you need to apply the ORDER BY clause at the group level, not at the subquery level.
Note that SQL Server and MySQL react differently to the ORDER BY clause used in a subquery of a UNION operator:
* SQL Server will give error if ORDER BY is used inside a subquery of a UNION operator.
* MySQL will ignore the ORDER BY clause inside a subquery of a UNION operator.
The following tutorial exercise shows you how to use ORDER BY clause with UNION operator:
(SELECT * FROM ggl_links WHERE tag = 'DBA'
ORDER BY created)
UNION
(SELECT * FROM ggl_links WHERE tag = 'DEV'
ORDER BY created)
GO
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.
(SELECT * FROM ggl_links WHERE tag = 'DBA')
UNION
(SELECT * FROM ggl_links WHERE tag = 'DEV')
ORDER BY created
GO
90 :: What Is a SELECT Query Statement in MS SQL Server?
The SELECT statement is also called the query statement. It is the most frequently used SQL statement in any database application. SELECT statements allows you to retrieve data from one or more tables or views, with different selection criteria, grouping criteria and sorting orders.
A SELECTE statement has the following basic syntax:
SELECT select_list
FROM table_source
WHERE search_condition
GROUP BY group_by_expression
HAVING search_condition
ORDER BY order_by_expression
Here is an example of a SELECT statement with all clauses mentioned above:
SELECT SalesOrderID, SUM(LineTotal) AS TotalPrice
FROM SalesLT.SalesOrderDetail
WHERE ModifiedDate > '2004-05-01'
GROUP BY SalesOrderID
HAVING COUNT(*) > 30
ORDER BY TotalPrice DESC
91 :: How To Select All Columns of All Rows from a Table with a SELECT statement in MS SQL Server?
The simplest query statement is the one that selects all columns of all rows from a single table: "SELECT * FROM tableName". The (*) in the SELECT clause tells the query to return all columns. The missing WHERE clause tells the query to return all rows in specified table. The tutorial exercise below returns all columns and all rows from table "ggl_links":
SELECT * FROM ggl_links
id url notes counts created
101 skills9.com NULL 0 2006-04-30
102 skills9.com/html NULL 0 2007-05-19
103 skills9.com/sql NULL NULL 2007-05-19
92 :: How To Select Some Specific Columns from a Table in a Query in MS SQL Server?
If you want explicitly tell the query to return some specific columns, you can specify the column names in the SELECT clause. The following select statement returns only three columns, "id", "created" and "url" from the table "ggl_links":
SELECT id, created, url FROM ggl_links
id created url
101 2006-04-30 www.skills9.com
102 2007-05-19 www.skills9.com/html
103 2007-05-19 www.skills9.com/sql
93 :: How To Select Some Specific Rows from a Table in MS SQL Server?
If you don't want select all rows from a table, you can specify a WHERE clause to tell the query to return only the rows that meets the condition defined in the WHERE clause. The WHERE clause condition is a normal Boolean expression. If any data from the table needs to be used in the Boolean expression, column names should be used to represent the table data.
The first select statement below only returns rows that have url names containing the letter "a". The second select statement returns no rows, because the WHERE clause results FALSE for all rows in the table.
SELECT * FROM ggl_links WHERE url LIKE '%s%'
GO
id url notes counts created
102 skills9.com.com/sql NULL 0 2007-05-19
103 skills9.com.com/xslt NULL NULL 2007-05-19
SELECT * FROM ggl_links WHERE id < 100
GO
0 row
94 :: How To Add More Data to the Testing Table in MS SQL Server?
If you want to continue with other tutorial exercises in this FAQ collection, you need to add more data to the testing table. Follow the script below to add a new column and more rows:
ALTER TABLE ggl_links ADD tag VARCHAR(8)
GO
UPDATE ggl_links SET tag = 'DEV' WHERE id = 101
GO
UPDATE ggl_links SET tag = 'DBA' WHERE id = 102
GO
UPDATE ggl_links SET tag = 'SQA' WHERE id = 103
GO
INSERT INTO ggl_links VALUES (104,
'www.mysql.com', '', '0', '2006-01-01', 'DBA')
GO
INSERT INTO ggl_links VALUES (105,
'www.oracle.com', '', '0', '2005-01-01', 'DBA')
GO
INSERT INTO ggl_links VALUES (106,
'www.php.net', '', '0', '2004-01-01', 'DEV')
GO
INSERT INTO ggl_links VALUES (107,
'www.winrunner.com', '', '0', '2003-01-01', 'SQA')
GO
UPDATE ggl_links
SET counts = CAST(LOG(id)*1000000 AS INT) % 1000
GO
95 :: How To Sort the Query Output with ORDER BY Clauses in MS SQL Server?
If you want the returning rows to be sorted, you can specify a sorting expression in the ORDER BY clause. The simplest sort expression is column name who's values will be sorted by. The following select statement returns rows sorted by the values in the "counts" column:
SELECT * FROM ggl_links ORDER BY counts
GO
id url notes counts created tag
101 skills9.com NULL 120 2006-04-30 DEV
104 www.mysql.com 390 2006-01-01 DBA
106 www.php.net 439 2004-01-01 DEV
103 skills9.com/html NULL 728 2007-05-19 SQA
107 www.winrunner.com 828 2003-01-01 SQA
105 www.oracle.com 960 2005-01-01 DBA
102 globalguideline.com/sql NULL 972 2007-05-19 DBA
The output is sorted by the values of the "counts" column.
96 :: Can the Query Output Be Sorted by Multiple Columns in MS SQL Server?
You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns rows sorted by "tag" and "counts" values:
SELECT tag, counts, url, created
FROM ggl_links ORDER BY tag, counts
GO
tab counts url created
DBA 390 www.mysql.com 2006-01-01
DBA 960 www.oracle.com 2005-01-01
DBA 972 skills9.com.com 2007-05-19
DEV 120 skills9.com.com/xml 2006-04-30
DEV 439 www.php.net 2004-01-01
SQA 728 skills9.com.com/sql 2007-05-19
SQA 828 www.winrunner.com 2003-01-01
97 :: How To Sort Query Output in Descending Order in MS SQL Server?
If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the "tag" in descending order, then sorts the "counts" in ascending order:
SELECT tag, counts, url, created
FROM ggl_links ORDER BY tag DESC, counts
GO
tab counts url created
SQA 728 skills9.com/sql 2007-05-19
SQA 828 www.winrunner.com 2003-01-01
DEV 120 skills9.com.com/xml 2006-04-30
DEV 439 www.php.net 2004-01-01
DBA 390 www.mysql.com 2006-01-01
DBA 960 www.oracle.com 2005-01-01
DBA 972 skills9.com.com 2007-05-19
98 :: How To Count Rows with the COUNT(*) Function in MS SQL Server?
If you want to count the number of rows, you can use the COUNT(*) function in the SELECT clause. The following tutorial exercise shows you some good examples:
SELECT COUNT(*) FROM ggl_links
GO
7
SELECT COUNT(*) FROM ggl_links
WHERE url LIKE '%glo%'
GO
3
So there are 7 rows in total in table "ggl_links", and 3 rows that have 'glo' as part of their url names.
99 :: Can SELECT Statements Be Used on Views in MS SQL Server?
Select (query) statements can be used on views in the same way as tables. The following tutorial exercise helps you creating a view and running a query statement on the view:
CREATE VIEW myLinks AS SELECT * FROM ggl_links
WHERE url LIKE '%glo%'
GO
SELECT tag, counts, url, created
FROM myLinks ORDER BY counts DESC
GO
tag counts url created
DBA 972 skills9.com 2007-05-19
SQA 728 skills9.com/html 2007-05-19
DEV 120 skills9.com/sql 2006-04-30
100 :: How To Filter Out Duplications in the Returning Rows in MS SQL Server?
If there are duplications in the returning rows, and you want to remove the duplications, you can use the keyword DISTINCT in the SELECT clause. The DISTINCT applies to the combination of all data fields specified in the SELECT clause. The tutorial exercise below shows you how DISTINCT works:
CREATE TABLE ggl_team (first_name VARCHAR(8),
last_name VARCHAR(8))
GO
INSERT INTO ggl_team VALUES ('John', 'Gate')
GO
INSERT INTO ggl_team VALUES ('John', 'Russell')
GO
INSERT INTO ggl_team VALUES ('John', 'Seo')
GO
INSERT INTO ggl_team VALUES ('John', 'Gate')
GO
INSERT INTO ggl_team VALUES ('James', 'Gate')
GO
INSERT INTO ggl_team VALUES ('Peter', 'Gate')
GO
INSERT INTO ggl_team VALUES ('John', 'Gate')
GO
0 comments:
Post a Comment