Interview Questions and Answers
Freshers / Beginner level questions & answers
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 "WithoutBook" as the Password.
SQL Server 2005 Connect Window.
Click the Connect button, you should see the SQL Server Management Studio Express window comes up.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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!
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 statement.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 "withoutbook" 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 withoutbook
GO
USE withoutbook
GO
DROP DATABASE withoutbook
GO
Msg 3702, Level 16, State 4, Server LOCALHOSTSQLEXPRESS
Cannot drop database "withoutbook" because it is
currently in use.
USE master
GO
DROP DATABASE withoutbook
GO
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbookdb
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
withoutbookdb 5 2007-05-19 20:04:39.310
As you can see, the newly created database is listed at the end of query result.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbookdb
GO
SELECT type_desc, physical_name, size
FROM sys.database_files
GO
type_desc physical_name size
ROWS c:Program FilesMicrosoft SQL Server
MSSQL.1MSSQLDATAwithoutbookdb.mdf 152
LOG c:Program FilesMicrosoft SQL Server
MSSQL.1MSSQLDATAwithoutbookdb_log.LDF 63
Go verify these two files with Windows Explorer.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbookdb
GO
CREATE DATABASE withoutbookdb
ON (NAME = withoutbookdb,
FILENAME = 'C:tempwithoutbookdb.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C:tempwithoutbookdb.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 withoutbookdb C:tempwithoutbookdb.mdf 1280
LOG GlobalGuidelineLog C:tempwithoutbookdb.ldf 128
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 "withoutbookdb" to "withoutbook":
ALTER DATABASE withoutbookdb
MODIFY NAME = withoutbook
GO
The database name 'withoutbook' has been set.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbook
GO
2. Keep the first instance running and launch another instance of SQL Server Management Studio:
ALTER DATABASE withoutbook
MODIFY NAME = withoutbookdb
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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 "withoutbook" offline:
ALTER DATABASE withoutbook SET OFFLINE
GO
SELECT name, state_desc from sys.databases
GO
name state_desc
master ONLINE
tempdb ONLINE
model ONLINE
msdb ONLINE
withoutbook OFFLINE
USE withoutbook
GO
Msg 942, Level 14, State 4, Line 1
Database 'withoutbook' cannot be opened because
it is offline.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbook SET ONLINE
GO
USE withoutbook
GO
CREATE TABLE Links (Name NVARCHAR(32))
GO
ALTER DATABASE withoutbook 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:tempwithoutbook.mdf to c:tempdata
3. Drag and drop c:tempwithoutbook.mdf to c:tempdata
Go back to the SQL client program and run:
ALTER DATABASE withoutbook
MODIFY FILE (NAME = withoutbookDB,
FILENAME = 'C:tempdatawithoutbookDB.mdf')
GO
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbookDB
GO
INSERT Links (Name) VALUES ('www.withoutbook.com')
GO
(1 rows affected)
ALTER DATABASE withoutbookDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.withoutbook.com')
GO
Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1
Failed to update database "withoutbookDB" because
the database is read-only.
SELECT * FROM Links
GO
Name
www.withoutbook.com
ALTER DATABASE withoutbookDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.withoutbook.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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 withoutbookDatabase
GO
ALTER DATABASE withoutbookDatabase SET SINGLE_USER
GO
Now connect to server with another client session and try:
USE withoutbookDatabase
GO
Msg 924, Level 14, State 1, Line 1
Database 'withoutbookDatabase' 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 withoutbookDatabase SET MULTI_USER
GO
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 wb (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 "wb" with 4 columns in the current database.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
wb USER_TABLE 2007-05-19 23:05:43.700
The output shows that there is only one table in the current database.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 = 'wb'
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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 wb
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.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 > WithoutbookDatabase > Tables > dbo.wb.
3. Click right mouse button on dbo.wb. The context menu shows up.
4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:
USE [WithoutbookDatabase]
GO
/****** Object: Table [dbo].[wb]
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].[wb](
[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
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 wb VALUES (1, 'Learn SQL',
'Visit www.Withoutbook.com','2006-05-01')
GO
SELECT * INTO wbBackup FROM wb
GO
(1 rows affected)
SELECT * FROM wbBackup
GO
id subject description create_date
1 Learn SQL Visit www.Withoutbook.com 2008-05-01
sp_columns wbBackup
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo wbBackup id int ...
dbo wbBackup subject varchar ...
dbo wbBackup description varchar ...
dbo wbBackup create_date datetime ...
As you can see, the "SELECT ... INTO" statement created a table called "wbBackup" using the same column definitions as the "wb" table and copied all data rows into "wbBackup".
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Ques 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 test ADD author VARCHAR(40)
GO
sp_columns test
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo test id int ...
dbo test subject varchar ...
dbo test description varchar ...
dbo test create_date datetime ...
dbo test author datetime ...
SELECT * FROM test
GO
id subject description create_date author
This SQL script added a new column called "author" to the "test" table. NULL values were added to this column on all existing data rows.
Save For Revision
Save For Revision
Bookmark this item, mark it difficult, or place it in a revision set.
Log in to save bookmarks, difficult questions, and revision sets.
Most helpful rated by users:
- What is Microsoft SQL Server?
- What is the simplest way to create a new database in MS SQL Server?
- How to delete a database in MS SQL Server?
- How to download and install Microsoft .NET Framework Version 2.0?
- How To Get a List of Columns using the "sp_help" Stored Procedure in MS SQL Server?