在程序中，数据库操作是必不可少的部分，所以我们要备足数据库相关知识才能去应付程序中出现的种种问题。基于此，我特地在国外网站、博客上整理了一些问题，并附带了答案和解释、参考。为了保证“原汁原味”，我就保留了英文。大家也来看看你答对了多少？
1.SQL Server 2008 Backup
题目：Is it possible to restore a SQL Server 2008 Enterprise Edition compressed backup to a SQL Server 2008 Standard Edition?
答案：yes
解释：RESTORE from compressed backups on SQL Server 2008 Standard Edition is possible, although the backup compression feature is not supported in SQL Server 2008 Standard Edition.
2.Identity
题目：We want to insert record into table a
create table a (a int identity(1,1))
Which statement will work?
- insert into a default values
- insert into a values (default)
- insert into a values (1)
- could not insert explicit for identity column
答案：insert into a default values
解释：An insert statement with "default values" works.
参考：INSERT
3.Dynamic SQL
题目：Sam has to run a query dynamically & get the count in a variable and do some processing based on the count. Which of the following queries will return expected output?
declare @tablevariable varchar(100)
set @tablevariable = ‘Employees‘
A)
declare @sql varchar(100)
Declare @cnt int
Set @sql = ‘Select ‘ + Convert(varchar(100),@cnt) + ‘ =count(*) from ‘ + @tablevariable
Exec (@sql) select @cnt
B)
declare @sql varchar(100)
Declare @cnt int
Set @sql = ‘Select count(*) from ‘ + @tablevariable
@cnt = Exec (@sql) select @cnt
C)
DECLARE @sql nvarchar(4000), @params nvarchar(4000), @count int
SELECT @sql = N‘ SELECT @cnt = COUNT(*) FROM dbo.‘ + quotename(@tablevariable)
SELECT @params = N‘@cnt int OUTPUT‘
EXEC sp_executesql @sql, @params, @cnt = @count OUTPUT select @count
答案：C
解释：For getting a variable as output in a dynamic statement, we need to use sp_executeSQL.
4.T-SQL Output Clause
题目：Executing the following code. How many rows are returned by the first and second SELECT * FROM #CategoryChanges statements?
USE Northwind;
CREATE TABLE #CategoryChanges
(ChangeID int Primary Key Identity
, CategoryID int
, OldCategoryName nvarchar(15)
, NewCategoryName nvarchar(15)
, ModifiedDate datetime2
, LoginID nvarchar(30));
BEGIN TRANSACTION
UPDATE Categories
SET CategoryName = ‘Dried Produce‘
OUTPUT inserted.CategoryID, deleted.CategoryName
, inserted.CategoryName, getdate(), SUSER_SNAME() INTO #CategoryChanges
WHERE CategoryID = 7;
SELECT * FROM #CategoryChanges --first select statement
Rollback tran
SELECT * FROM #CategoryChanges --second select statement
Choose your answer:
- 1st 0 rows 2nd 0 rows
- 1st 1 row, 2nd 0 rows
- 1st 1 row. 2nd 2 rows
答案：1st 1 row, 2nd 0 rows
解释：The ROLLBACK TRANSACTION rolls back both the update to the table categories and the temp table #CategoryChanges.
5.T-SQL
题目：In T-SQL, what would this be considered: " colmnnX IN (x, y, z, ...)"
答案：Predicate
解释：In T-SQL, a PREDICATE allows you to check whether a value or scalar expression evaluates to TRUE, FALSE, or UNKNOWN. The IN clause, with column and values becomes a predicate and checks to see if at least one of the elements in a set is equal to a given value or expression.
6.Server Administration
题目：Select the best option to allocate maximum available RAM (Already installed on Windows) to SQL Server where system has following configuration: OS: Windows 2008 64 bit, Enterprise Edition. SQL: SQL Server 2008 64 bit, Enterprise Edition. RAM: 6 GB.
Choose your answer
- Enable AWE option from SQL Server configuration
- Add /3GB switch in boot.ini
- Do Nothing
答案：Do Nothing
解释：AWE is valid option for 32 bit architecture. Note that the sp_configure awe enabled option is present on 64-bit SQL Server, but it is ignored. It is subject to removal in future releases or service packs of 64-bit SQL Server.
3 GB Switch is supported for 32 bit editions. It tell operating system to allocate 2 GB RAM to OS and 2 GB RAM to other program such as SQL Or Exchange, if 4 GB RAM is installed.
参考：内存体系结构
7.SQL Server 2008
题目：The DEFAULT value for a column can be specified in the definition of a user-defined table type?
答案：True
解释：A DEFAULT value can be specified in the definition of a user-defined table type.
8.Session Settings
题目：In SQL 2008, the QOD_Customers table contains the column [Region] [nvarchar](15) NULL and 90 rows of data. The following stored procedure is created and then run.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QOD_Test_1]
AS
SET ANSI_DEFAULTS ON
-- Before rollback Select Statement
SELECT COUNT(CompanyName) AS ‘Before rollback‘ FROM [dbo].[QOD_Customers]
WHERE [dbo].[QOD_Customers].[Region] IS NULL
UPDATE Dbo.QOD_Customers SET Region = ‘XXX‘ WHERE dbo.QOD_Customers.region IS NULL
-- The after update Select Statement
SELECT COUNT(CompanyName) AS ‘After update‘ FROM [dbo].[QOD_Customers]
WHERE [dbo].[QOD_Customers].[Region] IS NULL
ROLLBACK TRANSACTION
SET ANSI_DEFAULTS OFF
-- The after rollback Select Statement
SELECT COUNT(CompanyName) AS ‘After Rollback‘ FROM [dbo].[QOD_Customers]
WHERE [dbo].[QOD_Customers].[Region] IS NULL
GO
The before rollback Select statement returns a count of 60. The after update Select statement returns a count of 0 What count of rows does the after rollback Select statement return?
答案：60
解释：When ANSI_DEFAULTS is enabled (ON), this option enables the following ISO settings:...SET IMPLICIT_TRANSACTIONS. Msg 3903 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
参考：SET ANSI_DEFAULTS (Transact-SQL)
9.Severity Levels
题目：Error messages with severity levels below 10 indicate what?
Choose your answer:
- Errors can be corrected by the user
- Insufficient Resources
- Informational messages
- Nonfatal Internal Error Detected
- SQL Server Error in Resource
答案：Informational messages
解释：This is an informational message that indicates a problem caused by mistakes in the information the user has entered and not actual errors.
参考：Error Message Severity Levels
10.Exec on Linked Server
题目：What parameter marker is used, when EXEC() is executed on a Linked Server?
答案：?
解释：There is one thing that you can do with EXEC() at a linked server, that you cannot do with EXEC() on a local server: you can use parameters, both for input and output. The confuse matters, you don‘t use parameters with names starting with @, instead you use question marks (?) as parameter holders. You can run this:
DECLARE @cnt int
EXEC(‘SELECT ? = COUNT(*) FROM Northwind.dbo.Orders WHERE CustomerID = ?‘,
@cnt OUTPUT, N‘VINET‘) AT SQL2K
SELECT @cnt
参考：How to use EXEC() at Linked Server
11.SQL 2005 - Table-Valued Parameters
题目：In SQL Server 2005 variables and parameters of a table type can be set to NULL?
答案：SQL 2005 does not support Table-Valued Parameters
解释：Only SQL Server 2008 supports the Table-Valued Parameters. This was not a feature available in SQL Server 2005.
12.SQL Server 2008 Policy-Based Management
题目：SQL Server 2008 Policies can be defined against the following SQL Server Editions. Choose all if apply.
Choose your answer:
- SQL Server 2008
- SQL Server 2005
- SQL Server 2000
答案：SQL Server 2008, SQL Server 2005, SQL Server 2000
解释：The Enterprise Policy Management (EPM) Framework leverages and extends the new Microsoft SQL Server 2008 Policy-Based Management feature across an entire SQL Server enterprise, including down-level instances of SQL Server such as SQL Server 2000 and SQL Server 2005.
参考：Enterprise Policy Management Framework with SQL Server 2008
13.Indexes in SQL Server 2005
题目：What is the maximum number of indexes (clustered and nonclustered) allowed for a table in SQL Server 2005?
答案：250
解释：Number of Clustered indexes in SQL 2005 is one and 249 non clustered indexes, altogether 250 indexes for a table in SQL Server 2005. In SQL Server 2008, the maximum is 1000.
参考：CREATE INDEX (Transact-SQL)
14.Predict output
题目：Try to predict the output of this code...
declare @i int, @j int
set @i = 1
create table #temp (id int)
while (@i<=5)
begin
begin try
begin transaction
if (@i = 3)
set @j = @i/0
insert into #temp values (@i)
commit transaction
end try
begin catch
rollback transaction
print ‘this is an exception‘;
end catch
set @i = @i + 1
end
select * from #temp
Choose your answer:
- Results: 1 2 3 4 5 No messages
- Results: 1 2 3 No Messages
- Results: 1 2 4 5 Message: this is an exception
- Results: 1 2 Message: this is an exception
答案：Results: 1 2 4 5 Message: this is an exception
解释：This is a beautiful usage of TRY-CATCH block with looping. This will do the action, create the error message for the erroneous action, don‘t disturb the other actions and iterate until the last one. The results will include 4 rows, skipping the "3" and the Messages tab will list the exception.
15.Database Size
题目：What is the Initial size of newly created database (w/o specifiing the size for mdf/ldf)?
答案：3MB
解释：When you create the database without specifying the size for mdf / ldf the initial size for the database if 3 MB. Click on the database node create new database enter database name just click on OK. Check the size in properties or can also see the before creating it in Initial Size col of New database dialog box.
The default size for an mdf is 2MB and 1MB for an ldf, based on the model database.
16.Removing permissions
题目：You have a standard SQL Server 2005 instance. You allow the user ‘Mary‘ to call a stored procedure ‘UpdateCustomer‘ by using the following sql :
grant execute on UpdateCustomer to Mary
Prior to issuing this statement, Mary had no explicit permissions on the SP. You then realise that you‘ve made a mistake and want to reverse the action you have just taken. Which statement is the best option, without impacting on any other effective permissions?
Choose your answer:
- REMOVE EXECUTE permission statement
- REVOKE EXECUTE permission statement
- DENY EXECUTE permission statement
- GRANT NONEXECUTE permission statement
答案：REVOKE EXECUTE permission statement
解释：You are simply looking to revoke the permission you have just granted. DENY would take precedence over any other effective permissions, and may not be what you want to achieve. REMOVE and GRANT NONEXECUTE are not valid SQL.
17.Declarative Data Integrity
题目：After executing the following code, how many rows remain in each table (Countries, Cities and Buyers)?
CREATE TABLE Test.Countries(CountryId INT PRIMARY KEY)
INSERT INTO Test.Countries VALUES(1),(2),(3)
GO
CREATE TABLE Test.Cities( CityId INT PRIMARY KEY
,CountryId INT REFERENCES Test.Countries ON DELETE CASCADE);
INSERT INTO Test.Cities VALUES(1,1),(2,1),(3,2)
GO
CREATE TABLE Test.Buyers(CustomerId INT PRIMARY KEY
,CityId INT REFERENCES Test.Cities ON DELETE CASCADE);
INSERT INTO Test.Buyers VALUES(1,1),(2,1),(3,2)
GO
DELETE FROM Test.Countries WHERE CountryId = 1
答案：Countries 2, Cities 1, Buyers 0
解释：The constraints prevent some inserts and deletes from occurring.
参考：级联引用完整性约束
18.Wildcard
题目：From the data below, I need to get records with the FirstName of Kim or Tim only. Frame the query, applying a wildcard search on the FirstName column.
答案：WHERE FirstName LIKE ‘[KT]im‘
解释：The wildcards that can be used with LIKE include the brackets, [], which match any single character that‘s included inside them with the data in the field.
19.Query cost
题目：Which of the two WHERE clauses is cost effective:
--1.
SELECT [name] FROM teacher WHERE teacher_id IN (SELECT teacher_id FROM student)
--2.
SELECT [name] FROM teacher
WHERE EXISTS (SELECT 1 FROM student WHERE teacher.teacher_id = student.teacher_id)
答案：2 is more cost effective
解释：This is not a great question, and there is some debate about it. Please read the discussion to understand. The original explanation is below:
EXISTS will return a boolean value, while IN retruns actual result set (making results from IN heavier than EXISTS).
参考：EXISTS (Transact-SQL)、IN (Transact-SQL)
20.Bit by bit
题目：What will be result of following query:
DECLARE @bit BIT
SET @bit = 500
IF @bit = 1
PRINT ‘yes‘
ELSE
PRINT ‘no‘
答案：yes
解释：Bit constants are represented by the numbers 0 or 1, if a number larger than one is used, it is converted to one.
21.Rowcount
题目：In SQL Server 2005/2008, what would be the output of this code when you open a new query window and execute it?
select @@ROWCOUNT
select @@ROWCOUNT
答案：1,1
解释：When we first open a query window, the client must execute something to connect with no results. However the result of @@rowcount is set to one. If you were to execute some command like a SET NOCOUNT ON will @@rowcount return 0.