SQL基础问题整理(1)——你答对了多少？

在程序中，数据库操作是必不可少的部分，所以我们要备足数据库相关知识才能去应付程序中出现的种种问题。基于此，我特地在国外网站、博客上整理了一些问题，并附带了答案和解释、参考。为了保证“原汁原味”，我就保留了英文。大家也来看看你答对了多少？
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.
参考：备份压缩 (SQL Server)
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.
参考：Introducing Dynamic SQL
4.T-SQL Output Clause
题目：Executing the