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?
解释：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.
题目：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.
题目：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'
declare @sql varchar(100) Declare @cnt int Set @sql = 'Select ' + Convert(varchar(100),@cnt) + ' =count(*) from ' + @tablevariable Exec (@sql) select @cnt
declare @sql varchar(100) Declare @cnt int Set @sql = 'Select count(*) from ' + @tablevariable @cnt = Exec (@sql) select @cnt
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
解释：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.
题目：In T-SQL, what would this be considered: " colmnnX IN (x, y, z, ...)"
解释：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.
题目：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
解释：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?
解释：A DEFAULT value can be specified in the definition of a user-defined table type.
题目：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?
解释：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.
题目：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
解释：This is an informational message that indicates a problem caused by mistakes in the information the user has entered and not actual errors.
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
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.
13.Indexes in SQL Server 2005
题目：What is the maximum number of indexes (clustered and nonclustered) allowed for a table in SQL Server 2005?
解释：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.
题目：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.
题目：What is the Initial size of newly created database (w/o specifiing the size for mdf/ldf)?
解释：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.
题目：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.
题目：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.
题目：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).
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'
解释：Bit constants are represented by the numbers 0 or 1, if a number larger than one is used, it is converted to one.
题目：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
解释：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.