Technology Towards Microsoft

PRO ASP.NET 3.5 IN C# 2008, SECOND EDITION

noreply@blogger.com (Microsoft Techonologies Stuff) — Sun, 12 Oct 2008 03:13:00 +0530

Download PRO ASP.NET 3.5 IN C# 2008, SECOND EDITION - Book.

Publisher Apress
Author(s) Mario Szpuszta
ISBN 1590598938
Release Date 15 November 2007

ASP.NET 3.5 is the latest version of Microsoft’s revolutionary ASP.NET technology. It is the principal standard for creating dynamic web pages on the Windows platform. Pro ASP.NET 3.5 in C# 2008 raises the bar for high-quality, practical advice on learning and deploying Microsoft’s dynamic web solution.

Seasoned .NET professionals Matthew MacDonald and Mario Szpuszta explain how you can get the most from this groundbreaking new technology. They cover ASP.NET 3.5 as a whole, illustrating both the brand-new features and the functionality carried over from previous versions of ASP. This book will give you the knowledge you need to code real ASP.NET 3.5 applications in the best possible style.

The book will teach you ASP.NET 3.5 starting with core concepts to more advanced topics. You will learn

* Core concepts of ASP.NET 3.5. Why it’s special. What it’s fundamental principals are. The basics of Visual Studio. How ASP.NET 3.5 controls are created, and how they fit into ASP.NET 3.5 pages, ultimately creating full applications
* Data access details. The intricacies of ADO.NET and how to perform data binding to many sources from databases to file streams to XML. We include LINQ coverage so you’re on the bleeding edge
* Security. Once considered the Achilles heel of all Windows web applications, security has vastly improved and is a cornerstone of ASP.NET 3.5. This section explains the various forms of available security, and how to best apply them
* Taking things further using advanced user interface techniques. This includes user controls, customer server controls, client-side JavaScript, and GDI .
* Web services. In an increasingly connected world, working with web services grows in importance. This book will show you how to work with them.
* ASP.NET AJAX, with an emphasis on comtemporary web development techniques
* Development using Internet Information Services 7, Microsoft’s premier web hosting platform.
* And much more.

Download Your eBook Now

Click Here To Download

Download ASP.NET 2.0 Unleashed Book

noreply@blogger.com (Microsoft Techonologies Stuff) — Sat, 11 Oct 2008 15:35:00 +0530

Download SAM's ASP.NET 2.0 Unleashed Book

ASP.NET 2.0 Unleashed is a revision of the best-selling ASP.NET Unleashed, by Microsoft Software Legend Stephen Walther. It covers virtually all features of ASP.NET 2.0 including more than 50 new controls, personalization, master pages, and web parts. All code samples are presented in VB and C#. Throughout the more than 2,000 pages, you will be shown how to develop state-of-the-art Web applications using Microsoft's latest development tools. This resource is guaranteed to be used as a reference guide over and over!

Click Here To Download

Microsoft SQL Serever 2000 Stored Procedures and XML Programming

noreply@blogger.com (Microsoft Techonologies Stuff) — Sat, 11 Oct 2008 00:35:00 +0530

Download Microsoft SQL Server 2000 Stored Procedures and XML programming Book

Get thorough coverage of SQL Server 2000- Including extended XML capabilities and VS.NET updates with this expertly written resource. Learn to harness the full benefits of modular programming, and achieve faster execution and a reduction of network traffic. Included are a sample database and an end-to-end solution for a Web search engine.

Click Here To Download

Dynamic LINQ (Language Integrated Quary)

noreply@blogger.com (Microsoft Techonologies Stuff) — Fri, 10 Oct 2008 22:44:00 +0530

Dynamic LINQ (Language Integrated Query)

LINQ (language integrated query) is one of the new features provided with VS 2008 and .NET 3.5. LINQ makes the concept of querying data a first class programming concept in .NET, and enables you to efficiently express queries in your programming language of choice.

One of the benefits of LINQ is that it enables you to write type-safe queries in VB and C#. This means you get compile-time checking of your LINQ queries, and full intellisense and refactoring support over your code:

While writing type-safe queries is great for most scenarios, there are cases where you want the flexibility to dynamically construct queries on the fly. For example: you might want to provide business intelligence UI within your application that allows an end-user business analyst to use drop-downs to build and express their own custom queries/views on top of data.

Traditionally these types of dynamic query scenarios are often handled by concatenating strings together to construct dynamic SQL queries. Recently a few people have sent me mail asking how to handle these types of scenarios using LINQ. The below post describes how you can use a Dynamic Query Library provided by the LINQ team to dynamically construct LINQ queries.

Downloading the LINQ Dynamic Query Library

Included on the VS 2008 Samples download page are pointers to VB and C# sample packages that include a cool dynamic query LINQ helper library. Direct pointers to the dynamic query library (and documentation about it) can be found below:

Both the VB and C# DynamicQuery samples include a source implementation of a helper library that allows you to express LINQ queries using extension methods that take string arguments instead of type-safe language operators. You can copy/paste either the C# or VB implementations of the DynamicQuery library into your own projects and then use it where appropriate to more dynamically construct LINQ queries based on end-user input.

Simple Dynamic Query Library Example

You can use the DynamicQuery library against any LINQ data provider (including LINQ to SQL, LINQ to Objects, LINQ to XML, LINQ to Entities, LINQ to SharePoint, LINQ to TerraServer, etc). Instead of using language operators or type-safe lambda extension methods to construct your LINQ queries, the dynamic query library provides you with string based extension methods that you can pass any string expression into.

For example, below is a standard type-safe LINQ to SQL VB query that retrieves data from a Northwind database and displays it in a ASP.NET GridView control:

Using the LINQ DynamicQuery library I could re-write the above query expression instead like so:

Notice how the conditional-where clause and sort-orderby clause now take string expressions instead of code expressions. Because they are late-bound strings I can dynamically construct them. For example: I could provide UI to an end-user business analyst using my application that enables them to construct queries on their own (including arbitrary conditional clauses).

Dynamic Query Library Documentation

Included with the above VB and C# Dynamic Query samples is some HTML documentation that describes how to use the Dynamic Query Library extension methods in more detail. It is definitely worth looking at if you want to use the helper library in more depth:

Download and Run a Dynamic Query Library Sample

You can download and run basic VB and C# samples I've put together that demonstrate using the Dynamic LINQ library in an ASP.NET web-site that queries the Northwind sample database using LINQ to SQL:

You can use either Visual Web Developer 2008 Express (which is free) or VS 2008 to open and run them.

Beginning ASP.NET 3.5 in C# 2008: From Novice to Professional,Second Edition

noreply@blogger.com (Microsoft Techonologies Stuff) — Thu, 9 Oct 2008 01:05:00 +0530

Publisher Apress
Author(s) Matthew MacDonald
ISBN 1590598911
Release Date 15 November 2007

The most up-to-date and comprehensive introductory ASP.NET book you’ll find on any shelf, Beginning ASP.NET 3.5 in C# 2008 guides you through Microsoft’s latest technology for building dynamic web sites. This book will enable you to build dynamic web pages on the fly, and it assumes only the most basic knowledge of C#.

The book provides exhaustive coverage of ASP.NET, guiding you from your first steps right up to the most advanced techniques, such as querying databases from within a web page and tuning your site for optimal performance. Within these pages, you’ll find tips for â€œbest practicesâ€� and comprehensive discussions of key database and XML principles you need to know in order to be effective with ASP.NET. The book also emphasizes the invaluable coding techniques of object orientation and code behind, which will start you off on the track to building real-world web sites right from the beginning–rather than just faking it with simplified coding practices.

By the time you’ve finished the book, you will have mastered the core techniques and have all the knowledge you need to begin work as a professional ASP.NET developer.

Download Your eBook Now

Click Here To Download

Microsoft SQL Server FAQ's Part - IV

noreply@blogger.com (Microsoft Techonologies Stuff) — Fri, 3 Oct 2008 13:08:00 +0530

Microsoft SQL Server FAQ's Part - IV

Q1. Topic - CASTing
First question of day: what is the len of @c?

declare @c varchar(8000)
set @c = N'hello' + replicate('-',8000)
print len(@c)
print @c

A. 8000
B. 4000
C. 2000

Ans: B

Explanation: The CAST to NVARCHAR(4000) means that the maximum len is 4000, then the cast to varchar(8000) allows more characters, but the string is already truncated.

Ref: CAST and CONVERT - http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Q2. Topic - Database Background
What type of database is used on this site?

A. Oracle
B. MS SQL Server 2000
C. MySQL
D. MS SQL Server 2005
E. MS Access

Ans: D

Explanation: Of course the background of this site is MS SQL Server 2005! After running SQL Server 2000 for 6 years, the database was upgraded to SQL Server 2005 in September 2007.

Q3. Topic - Datetime conversion
You have a default SQL2005 SP2 install. What is the output from the following

set language us_english
set dateformat dmy
go
declare @date datetime
set @date = '11 apr 2008 17:10'
select left(@date,1)

A. 1
B. 4
C. A
D. An error 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

Ans: C

Explanation: The default conversion of a date type to character data is style 0, 'mon dd yyyy hh:miAM (or PM)', which gives us 'Apr 11 2008 5:10PM' and the left function returns the leftmost n characters, in this case 1 character, namely 'A'. Setting the DATEFORMAT to DMY has no impact on the outcome, as this setting is used only in the interpretation of character strings as they are converted to date values.

Ref:SET DATEFORMAT - http://msdn2.microsoft.com/en-us/library/ms189491(SQL.100).aspx

Q4. Topic - SQL Server 2008 FILESTREAM
What is the purpose of FILESTREAM storage in SQL Server 2008?

A. To enable user direct access to database files
B. To store BLOB data on file system
C. To allow network storage of data and log files

Ans: B

Explanation: FILESTREAM storage allows user to create a table with a varbinary(max) column (BLOB) which is actually stored on the filesystem, rather than as a field in the row.

Ref: Designing and Implementing FILESTREAM - http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx

Q5. Topic - Installed Instances
Which registry key houses the values for the name of SQL Server 2005's installed instances?

A. HKLM\SOFTWARE\Microsoft\Microsoft SQL Native Client
B. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server
C. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server 2005 Redist

Ans: B

Explanation: The "InstalledInstance" Value is located in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server.

Q6. Topic - Function execution
With the user defined function in SQL Server 2005:

CREATE FUNCTION [dbo].[fn_DoSomething](@Bin VARCHAR(10))
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @L AS INT
SET @L = LEN(LTRIM(RTRIM(@Bin)))
RETURN (@Bin)
END
GO

What value would you expect to be returned when executing this code in SQL Server 2005?
SELECT dbo.fn_DoSomething('1234567890ABC')

A. An error message
B. 1234567890AB
C. 1234567890
D. 1234567890ABC

Ans: C

Explanation: Try it to see for yourself. It will return "1234567890" and this appears to be a glitch in SQL 2005 at compatibility level 90 and in SQL 2000.

Q7. Topic - Returning Data from Stored Proc
In SQL Server 2005, a strored procedure can return the data using: (select all that apply)

A. Output parameter
B. Return Code
C. Cursor data type in an output parameter

Ans: A, B, C

Explanation: The stored procedure can return the data using output parameters, a return code and as well as a cursor data type.

Ref: Designing Stored Procedures - http://msdn2.microsoft.com/en-us/library/ms191132.aspx

Q8. Topic - Blocking
You have a default SQL2000 SP4 install.

select * from sysprocesses

reports SPID 55 as blocking SPID 55. You also notice that the waitime value is low and the waittype is a latch waittype

What is the most likely reason?

A. The code has entered into an infinite loop, blocking itself
B. As of SP4, sysprocesses reports latch waits and the behaviour is expected
C. Parallelism is enabled and the SPID is waiting for other threads of the SPID to finish
D. SPID 55 is reserved for CHECKPOINTS and is waiting for the next CHECKPOINT interval

Ans: B

Explanation: After you install Microsoft SQL Server 2000 Service Pack 4 (SP4), you may notice that the blocked column in the sysprocesses system table is populated for latch waits in addition to lock waits. Sometimes, you may notice brief periods of time when a single server process ID (SPID) is reported as blocking itself. This behavior is expected.

Ref: KB 906344 http://support.microsoft.com/default.aspx/kb/906344

Q9. Topic - Impersonation in an Execute As statement
The database user or SQL Server login is impersonated when the EXECUTE AS statement is executed or specified in a module. Which of the following statements are true about the impersonation? (Select all that apply)

A. Another EXECUTE AS statement or the REVERT statement must be used before the impersonation will end.
B. The database user or SQL Server login impersonation ends when the session is dropped or when the module finishes its execution.
C. If the statement is called by a member of sysadmin, server-level impersonation is used. If the statement is called by an account that is dbo, database-level impersonation is used.
D. The scope of the impersonation is explicitly defined

Ans: B, D

Explanation: Although using another Execute As statement or the revert statement will modify the impersonation it is not necessary to execute either statement to end the impersonation. The impersonation will end automatically when the session is dropped or the module completes its execution.

Ref: Understanding Context Switching - http://msdn2.microsoft.com/en-us/library/ms191296.aspx The scope of the impersonation is explicitly defined in the Execute AS statement.
The SETUSER statement is implicit. http://msdn2.microsoft.com/en-us/library/ms188315.aspx

Q10. Topic - Query
What will happen if following query get executed:
select CategoryID, Quantity, Pricefrom Customer c, SalesOrderDetail sodwhere sod.clientid in (select customerid where pricingplan='X')

A. Query will return error because the From clause is missing in query used in where clause
B. Query will succeed if the customerid column is available in any table used in the first From Clause
C. Query is wrong

Ans: B

Explanation: If any attribute is available in the outer From Clause then it will validate column in that before returning any error. You can view this with the following code:

create table customer (ClientID int, customername varchar(10), pricingplan char(1))
--create table customer (ClientID int, customername varchar(10), pricingplan char(1), CustomerID int) gocreate table SalesOrderDetail (ClientID int, CustomerName varchar(10), categoryid int, quantity int, price money, customerid int)
go
insert customer select 1, 'Steve', 'X'
insert customer select 2, 'Andy', 'Y'
-- insert customer select 1, 'Steve', 'X', 1
-- insert customer select 2, 'Andy', 'Y', 2
insert SalesOrderDetail select 1, 'Steve', 1, 2, 10, 1
insert SalesOrderDetail select 2, 'Andy', 1, 5, 10, 2
select CategoryID, Quantity, Price
from Customer c, SalesOrderDetail sod
where sod.clientid in (select customerid where pricingplan='X')
drop table Customer
drop table SalesOrderDetail

You can also remove CustomerID from both tables and see that it causes an error.

Q11. Topic - Delete Duplicates
Which of the three lines will correctly remove duplicated items in the following table:

Create table #new(ID int null, Keyvalue varchar(2))
insert into #new(id,keyvalue) values (1,'aa')
insert into #new(id,keyvalue) values (2,'bb')
insert into #new(id,keyvalue) values (1,'aa')
insert into #new(id,keyvalue) values (1,'aa')

A. with numbered;(SELECT rowno=row_number() over (partition by ID order by ID),ID,keyvalue from #new)delete from numbered where rowno>1
B. ;with numbered as(SELECT rowno=row_number() over (partition by ID order by ID),ID,keyvalue from #new)delete from numbered where rowno>1
C. ;with numbered as(SELECT rowno=row_number() over (partition by ID order by ID),ID,keyvalue from #new)delete from numbered where rowno=1

Ans: B

Explanation: Of these statements, the first one produces a syntax error. The last one produces a table with 2 duplicates. The second one is correct. A semi-colon at the beginning of the statement is valid and we want to delete all the matching row numbers greater than 1.

Q12. Topic - SQL Server 2008 FILESTREAM
How do you know whether FILESTREAM storage is available and enabled in SQL Server 2008?

A. Query Sys,Configurations view for 'FileStreamAccessLevel'
B. Query the Sys.assembly_files for 'FileStream'
C. Query the server property 'FilestreamEffectiveLevel'

Ans: C

Explanation: For knowing the current effective level of FileStream access you need to run the following query –

SELECT SERVERPROPERTY ('FilestreamShareName') ,SERVERPROPERTY ('FilestreamEffectiveLevel');

which will give you the instance name and the filestream access level. If the level is set to 3 the filestream storage is enabled and available.

Ref: sp_filestream_configure - http://msdn2.microsoft.com/en-us/library/bb934198(SQL.100).aspx

Q13. Topic - Statistics
What would you use to update a single table's statistics in the shortest time?

A. sp_updatestats
B. UPDATE STATISTICS
C. All of the above

Ans: B

Explanation: Sp_updatestats does not have an option to update a single table's statistic. It will update all tables in the database. This answer is incorrect. UPDATE STATISTICS can be used with single table and therefore it will run in most cases faster.

Ref: UPDATE STATISTICS - http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx

Q14. Topic - SQL Server 2005
Which of the following are system databases in SQL Server 2005? (select all that apply)

A. Master
B. Model
C. Msdb
D. Tempdb
E. Resource

Ans: A, B, C, D, E

Explanation: All of these are system databases in SQL Server 2005.

Ref: System Databases - http://msdn2.microsoft.com/en-us/library/ms178028.aspx

Q15. Topic - Bit data
The AdventureWorks database has the HumanResources.Employee table with a column SalariedFlag defined as follows:

[SalariedFlag] [dbo].[Flag] NOT NULL

The Flag user-defined data type is defined by:

CREATE TYPE [dbo].[Flag] FROM [bit] NOT NULL

What will be the result when the following query is executed on SQL Server 2005?

USE AdventureWorks
SELECT COUNT(*) as [Number of Salaried Employees]
FROM HumanResources.Employee
WHERE SalariedFlag = 'true'

A. A numeric result will be returned
B. An error message will appear: Syntax error converting the varchar value 'true' to a column of data type bit.
C. A result set with no value (a blank result) will be returned.

Ans: A

Explanation: In SQL Server 2005, the string values TRUE and FALSE can be converted to bit values.
See the SQL Server Books Online topic "bit (Transact-SQL)" at http://msdn2.microsoft.com/en-us/library/ms177603.aspx.
In SQL Server 2000, a query such as: USE Northwind; SELECT * FROM Products WHERE discontinued = 'true'; would give the error message.

Q16. Topic - ANSI_NULLS
Assume the following table
CREATE TABLE testnull(ggg INT NULL)
INSERT testnull(ggg)SELECT NULL UNION ALLSELECT 1 UNION ALLSELECT 0
What counts do you get from the followingSELECT Count(*)
FROM testnull GOSET ANSI_NULLS OFFGOSELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1;

SELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1
OR ggg=NULL;GOSET ANSI_NULLS ONGOSELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1;

SELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1
OR ggg=NULLGODROP TABLE testnull

A. 3,3,3,2,2
B. 3,3,2,2,2
C. 3,2,3,2,2
D. 3,2,3,2,3

Ans: C

Explanation: Although Setting the ANSI_NULLS setting to OFF allows for using the =NULL notation, it does NOT affect how NULL operates with regards to comparisons with non-null values. the SET ANSI_NULLS OFF has been marked as deprecated in SQL 2005 and will be removed in the future. For more info on ANSI_NULLS, read the following Books Online document:

Ref: SET ANSI_NULLS- http://msdn2.microsoft.com/en-us/library/ms188048.aspx

Q17. Topic - Implicit Conversions
What happens with this code?select 'B'union select 4

A. The int is converted to varchar
B. The varchar is converted in int (error results)
C. The query does not compile.

Ans: B

Explanation: This is an example of an implicit conversion. In this case, an error is returned as the varchar is converted to an int, or an attempt is made, which returns an error. The int is of higher precedence than a varchar, so that is the order of conversions.

Ref: Connect and Implicit Casts - http://blogs.msdn.com/isaac/archive/2008/04/10/connect-and-implicit-casts.aspxData Type Precedence - http://msdn2.microsoft.com/en-us/library/ms190309(SQL.100).aspx

Q18. Topic - T-SQL query

Is there a condition for the WHERE clause to display the details about the students born in the month numbers of 1[Jan], 3,4,5,7,8? The condition should not use both IN and OR operators

A. No
B. Yes
C. Not Supported in SQL Server

Ans: B

Explanation: We can give the condition as follows:where datename(m,dob) not like '%e%'
because these month(s) specified do not have the letter 'e', but all the other months have at least one 'e' in month name.

Q19. Topic - Rebuild Master
If you rebuild the master database in SQL Server 2005, what happens to your version level?

A. It reverts to the RTM version, losing service packs and additonal patches
B. It reverts to the latest Service Pack version, losing any additional patches.
C. It remains the same.

Ans: A

Explanation: When you rebuild the master database, all system databases, including the systemresource database, are rebuilt from original media, so all Service Pack, Hotfix, QFE/GDR information is lost and you revert to the original RTM version.

Ref: How to: Install SQL Server 2005 from the Command Prompt (scroll down to rebuild database section) - http://msdn2.microsoft.com/en-us/library/ms144259.aspx

Q20. Topic - DAC Port
On which port can you make a Dedicated Administrator Connection (DAC)?

A. 1435
B. 3389
C. 1433
D. Dynamically assigned

Ans: D

Explanation: The DAC port is dynamically assigned when the instance starts and listed in the SQL Server error log. By default it tries for 1434, but it is possible that this port is already in use. The message will be something like:Dedicated admin connection support was established for listening locally on port 1977.

Ref: Using a Dedicated Administrator Connection - http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Q21. Topic - Set trigger firing order
I have 3 UPDATE triggers on a individual table, TRA, TRB and TRC. Can I fire them in the sequence TRA, TRB and TRC?

A. No
B. Yes

Ans: B

Explanation: http://msdn2.microsoft.com:80/en-us/library/ms186762.aspx Using sp_settriggerorder Set TRA as first, TRC as LAST and TRB will fire between TRA and TRC firing which is what is desired.

Q22. Topic - Identity Columns
In SQL Server 2005, how can you easily determine which columns have the identity property set? (select all that apply).

A. Query sys.identity_columns for the rows.
B. Query sys.tables.identity_column for the name of the column in each table
C. Query sys.columns.is_identity for a value of 1
D. There is no way to do this in T-SQL.

Ans: A, C

Explanation: There is a table, sys.identity_columns that contains a row for each column in your database that has the identity property set. There is also a column called is_identity in the sys.columns view that contains a 1 if the column has the identity property set. The other answer was made up.

Ref: sys.identity_columns - http://msdn2.microsoft.com/en-us/library/ms187334.aspx

Q23. Topic - Floor Function
What is the output of this query?select floor(13.890)

A. 14
B. 13.9
C. 13

Ans: C

Explanation: Floor returns the largest integer less than or equal to the specified numeric expression. In this case, that is 13.

Q24.Topic - SQL Server Express DAC
Does SQL Server 2005 Express allow Dedicated Administrator Connections (DAC)?

A. Yes, always
B. Yes, if remote connections are allowed.
C. Yes, if started with trace flag 7806
D. No

Ans: C

Explanation: SQL Server 2005 Express Edition does not listen on the DAC port by default. If it is started with trace flag 7806, then DAC connections can be made.

Ref: Using a Dedicated Administrator Connection - http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Q25. Topic - Named Pipes
In SQL Server 2005, how do you perform a backup using Named Pipes?

A. Normal backups to local disks all use named pipes
B. In the TO part of the backup command, specify the pipe as "TO PIPE = '\sql\pipe'"
C. SQL Server 2005 does not support backup to named pipes.

Ans: C

Explanation: In SQL Server 2005, the ability to backup to a named pipe (disk or tape) was removed.

Ref: Discontinued Database Engine Functionality in SQL Server 2005 - http://msdn2.microsoft.com/en-us/library/ms144262.aspx

Windows Key Board Shortcuts

noreply@blogger.com (Microsoft Techonologies Stuff) — Wed, 1 Oct 2008 19:33:00 +0530

Windows Key Board Shortcuts

Getting used to using your keyboard exclusively and leaving your mouse behind will make you much more efficient at performing any task on any Windows system. I use the following keyboard shortcuts every day.You also try it, believe me its fun operating without mouse:

Type the following commands in your Run Box (Press Windows Key + R) or Click-->Start--> Run.

This is usually followed by:

devmgmt.msc = Device Manager

msinfo32 = System Information

cleanmgr = Disk Cleanup

ntbackup = Backup or Restore Wizard (Windows Backup Utility)

mmc = Microsoft Management Console

excel = Microsoft Excel (If Installed)

msaccess = Microsoft Access (If Installed)

powerpnt = Microsoft PowerPoint (If Installed)

winword = Microsoft Word (If Installed)

frontpg = Microsoft FrontPage (If Installed)

notepad = Notepad

wordpad = WordPad

calc = Calculator

msmsgs = Windows Messenger

mspaint = Microsoft Paint

wmplayer = Windows Media Player

rstrui = System Restore

netscp6 = Netscape 6.x

netscp = Netscape 7.x

netscape = Netscape 4.x

waol = America Online

control = Opens the Control Panel

control printers = Opens the Printers Dialog

cmd = Command Prompt

iexplore + "web address" = Internet Explorer

compmgmt.msc = Computer Management

dhcpmgmt.msc = DHCP Management

dnsmgmt.msc = DNS Management

services.msc = Services

eventvwr = Event Viewer

dsa.msc = Active Directory Users and Computers

dssite.msc = Active Directory Sites and Services

Shortcuts by Pressing some key combinations

Windows key + E = Explorer

ALT + Tab = Switch between windows

ALT, Space, X = Maximize window

CTRL + Shift + Esc = Task Manager

Windows key + Break = System properties

Windows key + F = Search

Windows key + D = Hide/Display all windows

CTRL + C = copy

CTRL + X = cut

CTRL + V = paste

Alt + Esc = Switch between running applications

Alt + letter Select menu item by underlined letter

Ctrl + Esc = Open Program Menu

Ctrl + F4 = Close active document or group windows (does not work with some applications)

Alt + F4 = Quit active application or close current window

[Alt] + [-] = Open Control menu for active document

Ctrl] Lft., Rt. = arrow Move cursor forward or back one word

Ctrl] Up, Down arrow Move cursor forward or back one paragraph

[F1] Open Help for active application

Windows + M = Minimize all open windows

Shift + Windows+M = Undo minimize all open windows

Windows + F1 = Open Windows Help

Windows + = Tab Cycle through the Taskbar buttons

Windows + Break = Open the System Properties dialog box

Acessability shortcuts:

Right SHIFT for eight seconds - Switch FilterKeys on and off.

Left ALT + left SHIFT + PRINT SCREEN - Switch High Contrast on and off.

Left ALT + left SHIFT + NUM LOCK - Switch MouseKeys on and off.

Press SHIFT Key for five times - Switch StickyKeys on and off.

Press NUM LOCK for five seconds - Switch ToggleKeys on and off.

Explorer Shortcuts:

END - Display the bottom of the active window.

HOME - Display the top of the active window.

Num Lock + Aterisk on numeric keypad(*) - Display all subfolders under the selected folder.

NUM LOCK + PLUS SIGN on numeric keypad (+) - Display the contents of the selected folder.

NUM LOCK+MINUS SIGN on numeric keypad (-) - Collapse the selected folder.

LEFT ARROW - Collapse current selection if it's expanded, or select parent folder.

RIGHT ARROW - Display current selection if it's collapsed, or select first subfolder.

BACKSPACE[<-] - To page up and traverse through different directory.

Microsoft SQL Server 2008 FAQ's

noreply@blogger.com (Microsoft Techonologies Stuff) — Wed, 1 Oct 2008 00:28:00 +0530

Click Here To Download

Download-Beginning Datsbase Design by Wiley

noreply@blogger.com (Microsoft Techonologies Stuff) — Tue, 30 Sep 2008 20:56:00 +0530

Click Here To Download

Download-Beginning SQL Server 2005 for Developers

noreply@blogger.com (Microsoft Techonologies Stuff) — Tue, 30 Sep 2008 20:22:00 +0530

Click Here To Download

Download .Net Interviews Questions Book

noreply@blogger.com (Microsoft Techonologies Stuff) — Fri, 26 Sep 2008 14:47:00 +0530

Those who want prepair for the Interviews on .Net platform. I think this book will helpful to you for preparation.

Click Here to Download

Custom Alert box using JavaScript

noreply@blogger.com (Microsoft Techonologies Stuff) — Wed, 24 Sep 2008 21:07:00 +0530


    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"

>
<html>
<head>
       <title>Custom Alert Box</title>
           <meta equiv="Content-Type" content="text/html;

charset=utf-8">

 <script type="text/javascript" language="javascript">
document.onclick=disableEvents
document.oncontextmenu=disableEvents
var ef=0
function custAlert()
{
ef=1
var ids
ids=document.getElementById('alert')
ids.style.left=300;
ids.style.top=150;
ids.style.visibility="visible"
}

function disableEvents()

{if(ef)
return false;

} </script>

</head>
<body bgcolor="#ffffff" onload="custAlert();">       <form id="Form1" method="post" runat="server">
         <div align="center" id="alert" style="border: 2px solid gray; background: #cdeb8b
url('images/img3.gif') repeat-x; left: 240; visibility: hidden; width: 349; color: red; position: absolute;
       top: 104; height: 108">
        <br>      <h3 style="left: 0; width: 152; position: absolute; top: 0; height: 40; ali

gn: Left">
 <span style="color:#356AA0;">Custom Alert Box</span></h3>
        <img style="left: 336px; width: 6px; position: absolute; top: 8px; height: 6px" height="6"
          alt="Close" src="images/Cross.png" onclick="ef=0;document.getElementById('alert').style.visibili

ty='hidden';"          >
 <br>        <hr width="99.03%" size="1" style="left: 0px; width: 99.03%; position: absolute;
         top: 32px; height: 1px">
            <div id="msg" style="width: 349; color: #356aa0; height: 85; text-decoration: none">
         NJoy Programming<p>
      Success be Yours</div>
         <p>
          </div>
            </form>
             </body>
    </html>

VirtualBox 2.0 Released - Awesome Features

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 04:30:00 +0530

Sun has released a major update to VirtualBox, a free virtualization application that lets you run one operating system inside another. VirtualBox 2.0 runs on Mac, Linux, and Windows machines and lets you run guest operating systems inside your computer's native environment.

For example, if you're a Linux user but you occasionally want to run a Windows application that doesn't perform well under WINE, you can load up VirtualBox and run Windows (assuming you have a valid license key).

The latest update brings improved performance for machines with AMD processors and support for 64 bit guest operating systems as long as your host OS is also 64 bit, and support for VHD disk images created using Microsoft Virtual PC. VirtualDisk 2.0 also adds support for the Ubuntu 8.10 and Fedora 9 Linux distributions. You can find a complete list of bug fixes and updates in the release notes.

How to Access Linux partitions From Windows

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 04:21:00 +0530

The solution is pretty simple and straightforward, and NO you don't need to reboot Linux again! You can use any of these free softwares to do the trick for you:

Linux Partition on Windows

Both the softwares install file system drivers so that you can access the Ext2 file system just like and other native Windows file system. You get both read write access to the Linux partition and it shows up as a normal drive with a drive letter assigned to it within Windows Explorer.

Copy Files in a Fastest Way in Windows XP

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 04:08:00 +0530

In Windows XP how to Copy the Files in a Fastest Way?

Fast Copy is a file copier for Windows that has been around for some time. It was recently updated making it an ideal time to review it at Ghacks. Fast Copy comes as a portable application that can be used to copy files in an efficient speedy way. It falls in the same category as programs like TeraCopy which offer the same functionality.

A major difference between the two applications is the fact that Fast Copy does not hijack the original Windows copy, delete and move operations but comes only into play when the user selects the application to handle the transfers. This can be done by loading Fast Copy or adding Fast Copy entries to the right-click context menu. The option for the latter is provided in the program's options.

Since the usage is not automatic using Fast Copy makes only sense in situations where the user has to copy many files and folders at once, for example when moving the contents of one hard drive to another. It simply takes about five to ten seconds to start the file transfer because the source and destination locations have to be added to the program manually.

The context menu entry is only adding the source location to the program but the destination would still have to be selected manually. Fast Copy uses parallel processing if the source and destination location are on different hard drives and a huge buffer if they are on the same. It comes with a lot of options that can be changed like the buffer size or actions to take if a file exists in the destination.

The command line options might make it usable for batch files that use file operations. It lacks a few of the features that TeraCopy comes with like a queue that offers options to skip files or pause the process.

Fast Copy in its current version can be used in batch files because of its command line parameters and to copy large amount of files. It does not work as a real transfer replacement for Windows and have to leave that field to Teracopy.

Manual Steps To Back Up & Restore The Registry in Windows Vista or Windows XP

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 03:44:00 +0530

How to Manually BackUp & Restore The Registry in Windows Vista or Windows XP?

BackUp the Registry

Windows Vista

1.	Click Start, type systempropertiesprotection in the Start Search box, and then press ENTER. If you are prompted for an administrator password or for a confirmation, type the password, or click Allow.
2.	Wait for Windows to search for available disks and most recent restore points. In the System Properties dialog box, on the System Protection tab, click Create,
3.	Type a name for the restore point and then click Create.
4.	After the restore point has been created successfully, click OK two times.

Note: If System Restore is turned off, click to select the local disk, click Apply and then click Create.

Windows XP

1.	Click Start, click Run, type %SystemRoot%\system32\restore\rstrui.exe, and then click OK.
2.	On the Welcome to System Restore page, click Create a restore point, and then click Next .
3.	On the Create a Restore Point page, type a name for the restore point and then click Create
4.	After the restore point has been created, click Close.

Note: If System Restore is turned off, you receive a message that asks whether you want to turn on System Restore now. Click Yes. Then, in the System Properties dialog box, click to clear the Turn off System Restore check box, click OK, and then repeat this step.

Restore the registry

Use System Restore to undo registry changes in Windows Vista or in Windows XP

Windows Vista

1.	Click Start, type systempropertiesprotection in the Start Search box, and then press ENTER. If you are prompted for an administrator password or for a confirmation, type the password, or click Allow.
2.	In the System Properties dialog box, on the System Protection tab, click System Restore,
3.	In the System Restore dialog box select Choose a different restore point, and then click Next
4.	Select the restore point that you want to use, and then click Next.
5.	Confirm your restore point, and then click Finish System restore restores the selected Windows Vista configuration and then restarts the computer.
6.	Log on to the computer. When the System Restore confirmation page appears, click OK..

Windows XP

1.	Click Start, click Run, type %SystemRoot%\System32\Restore\Rstrui.exe, and then click OK.
2.	On the Welcome to System Restore page, click Restore my computer to an earlier time (if it is not already selected), and then click Next .
3.	On the Select a Restore Point page, click the system checkpoint. In the On this list select the restore point area, click an entry that is named "Guided Help (Registry Backup)," and then click Next. If a System Restore message appears that lists configuration changes that System Restore will make, click OK.
4.	On the Confirm Restore Point Selection page, click Next. System Restore restores the previous Windows XP configuration and then restarts the computer.
5.	Log on to the computer. When the System Restore confirmation page appears, click OK..

Lock your Desktop By Mouse Click!

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 03:22:00 +0530

How to Lock your Desktop by a single Mouse Click?

As we all are getting lazier day by day, this post promotes laziness to the core! Generally we have problems in pressing Alt+Ctrl+Del or Windows+L to Lock our PCs, hence it would be better if we could do that by the click of a mouse. Here's how to do it :

1. Right click an empty spot on the desktop, point to New and click Shortcut.

2. In the Create Shortcut dialog box, copy the following into the 'Type the location' of the item text box:
"rundll32 user32.dll,LockWorkStation" remove quotes while typing.

3. Click Next.

4. In "Type a name for this shortcut", type LOCK and Click Finish

5. Now just double click on the icon, your desktop will be locked.

I tested in XP and working fine.. Just try out

Disable Windows XP Boot Screen

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 03:16:00 +0530

How to Disable Windows XP Boot Screen?

Disable your Windows XP Boot Screen by following steps

Start->Run or (Win key + R)
Enter msconfig and select BOOT.INI tab
In Boot options select /NOGUIBOOT
Restart the System you cannot see the Windows XP Boot Screen.

msconfig Utility

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 02:55:00 +0530

How to remove the unwanted programs running at the system startup and how to improve the performance of your system .Here are the procedures to remove the unwanted programmes that loads during startup and loads in System tray.

Most useful for systems that are running with low memory.

Here are the steps to follow.

Step1: Go to the Run command and type msconfig. To open Run Command Utility Press win's key + R.

Make sure that you have administrator priviledge to do .

Step2: A window utility opens and go to Startup Tab

Uncheck the unwanted programs , so that the programs do not load at system startup.

Caution : uncheck only the programs that you are sure of that event and select ok.

Windows Operating System Run Commands

noreply@blogger.com (Microsoft Techonologies Stuff) — Mon, 22 Sep 2008 02:34:00 +0530

These are very useful & important Windows Run Utility Commands. By these Run Commands
we can see lot of hidden utilities in Windows Operating System. Improve your skills in the usage of windows operating system by these commands.

Before using these commands Press the keys windows symbol key + R on your key board to activate the Run command utility and type the commands these and hit the enter key.

To Access….	Run Command
Accessibility Controls	access.cpl
Accessibility Wizard	accwiz
Add Hardware Wizard	hdwwiz.cpl
Add/Remove Programs	appwiz.cpl
Administrative Tools	control admintools
Adobe Acrobat (if installed)	acrobat
Adobe Designer (if installed)	acrodist
Adobe Distiller (if installed)	acrodist
Adobe ImageReady (if installed)	imageready
Adobe Photoshop (if installed)	photoshop
Automatic Updates	wuaucpl.cpl
Bluetooth Transfer Wizard	fsquirt
Calculator	calc
Certificate Manager	certmgr.msc
Character Map	charmap
Check Disk Utility	chkdsk
Clipboard Viewer	clipbrd
Command Prompt	cmd
Component Services	dcomcnfg
Computer Management	compmgmt.msc
Control Panel	control
Date and Time Properties	timedate.cpl
DDE Shares	ddeshare
Device Manager	devmgmt.msc
Direct X Control Panel (If Installed)*	directx.cpl
Direct X Troubleshooter	dxdiag
Disk Cleanup Utility	cleanmgr
Disk Defragment	dfrg.msc
Disk Management	diskmgmt.msc
Disk Partition Manager	diskpart
Display Properties	control desktop
Display Properties	desk.cpl
Display Properties (w/Appearance Tab Preselected)	control color
Dr. Watson System Troubleshooting Utility	drwtsn32
Driver Verifier Utility	verifier
Event Viewer	eventvwr.msc
Files and Settings Transfer Tool	migwiz
File Signature Verification Tool	sigverif
Findfast	findfast.cpl
Firefox (if installed)	firefox
Folders Properties	control folders
Fonts	control fonts
Fonts Folder	fonts
Free Cell Card Game	freecell
Game Controllers	joy.cpl
Group Policy Editor (XP Prof)	gpedit.msc
Hearts Card Game	mshearts
Help and Support	helpctr
HyperTerminal	hypertrm
Iexpress Wizard	iexpress
Indexing Service	ciadv.msc
Internet Connection Wizard	icwconn1
Internet Explorer	iexplore
Internet Properties	inetcpl.cpl
Internet Setup Wizard	inetwiz
IP Configuration(Display Connection Configuration)	ipconfig /all
IP Configuration (Display DNS Cache Contents)	ipconfig /displaydns
IP Configuration(Delete DNS Cache Contents)	ipconfig /flushdns
IP Configuration(Release All Connections)	ipconfig /release
IP Configuration(Renew All Connections)	ipconfig /renew
IP Configuration (Refreshes DHCP & Re-Registers DNS)	ipconfig /registerdns
IP Configuration (Display DHCP Class ID)	ipconfig /showclassid
IP Configuration (Modifies DHCP Class ID)	ipconfig /setclassid
Java Control Panel (If Installed)	jpicpl32.cpl
Java Control Panel (If Installed)	javaws
Keyboard Properties	control keyboard
Local Security Settings	secpol.msc
Local Users and Groups	lusrmgr.msc
Logs You Out Of Windows	logoff
Malicious Software Removal Tool	mrt
Microsoft Access (if installed)	access.cpl
Microsoft Chat	winchat
Microsoft Excel (if installed)	excel
Microsoft Frontpage (if installed)	frontpg
Microsoft Movie Maker	moviemk
Microsoft Paint	mspaint
Microsoft Powerpoint (if installed)	powerpnt
Microsoft Word (if installed)	winword
Microsoft Syncronization Tool	mobsync
Minesweeper Game	winmine
Mouse Properties	control mouse
Mouse Properties	main.cpl
Nero (if installed)	nero
Netmeeting	conf
Network Connections	control netconnections
Network Connections	ncpa.cpl
Network Setup Wizard	netsetup.cpl
Notepad	notepad
Nview Desktop Manager(If Installed)	nvtuicpl.cpl
Object Packager	packager
ODBC Data Source Administrator	odbccp32.cpl
On Screen Keyboard	osk
Opens AC3 Filter(If Installed)	ac3filter.cpl
Outlook Express	msimn
Paint	pbrush
Password Properties	password.cpl
Performance Monitor	perfmon.msc
Performance Monitor	perfmon
Phone and Modem Options	telephon.cpl
Phone Dialer	dialer
Pinball Game	pinball
Power Configuration	powercfg.cpl
Printers and Faxes	control printers
Printers Folder	printers
Private Character Editor	eudcedit
Quicktime (If Installed)	QuickTime.cpl
Quicktime Player (if installed)	quicktimeplayer
Real Player (if installed)	realplay
Regional Settings	intl.cpl
Registry Editor	regedit
Registry Editor	regedit32
Remote Access Phonebook	rasphone
Remote Desktop	mstsc
Removable Storage	ntmsmgr.msc
Removable Storage Operator Requests	ntmsoprq.msc
Resultant Set of Policy (XP Prof)	rsop.msc
Scanners and Cameras	sticpl.cpl
Scheduled Tasks	control schedtasks
SecurityCenter	wscui.cpl
Services	services.msc
Shared Folders	fsmgmt.msc
Shuts Down Windows	shutdown
Sounds and Audio	mmsys.cpl
Spider Solitare Card Game	spider
SQL Client Configuration	cliconfg
System Configuration Editor	sysedit
System Configuration Utility	msconfig
System File Checker Utility (Scan Immediately)	sfc /scannow
System File Checker Utility (Scan Once At Next Boot)	sfc /scanonce
System File Checker Utility (Scan On Every Boot)	sfc /scanboot
System File Checker Utility (Return to Default Setting)	sfc /revert
System File Checker Utility (Purge File Cache)	sfc /purgecache
System File Checker Utility (Set Cache Size to size x)	sfc /cachesize=x
System Information	msinfo32
System Properties	sysdm.cpl
Task Manager	taskmgr
TCP Tester	tcptest
Telnet Client	telnet
Tweak UI (if installed)	tweakui
User Account Management	nusrmgr.cpl
Utility Manager	utilman
Windows Address Book	wab
Windows Address Book Import Utility	wabmig
Windows Backup Utility (if installed)	ntbackup
Windows Explorer	explorer
Windows Firewall	firewall.cpl
Windows Magnifier	magnify
Windows Management Infrastructure	wmimgmt.msc
Windows Media Player	wmplayer
Windows Messenger	msmsgs
Windows Picture Import Wizard (need camera connected)	wiaacmgr
Windows System Security Tool	syskey
Windows Update Launches	wupdmgr
Windows Version (to show which version of windows)	winver
Windows XP Tour Wizard	tourstart
Wordpad	write

Garbage Collector

noreply@blogger.com (Microsoft Techonologies Stuff) — Sun, 21 Sep 2008 16:42:00 +0530

It is Automatic Memory Manager for .Net Framework. It manages the memory allocated to .Net Framework. When a variable is defined it gets a space in memory (stack) and when an object is created memory for the object is allocated in heap. When an object is assigned to a variable it increments the reference counts for the object and when program control comes out of the function the scope of variable gets ended Or NULL is assigned to variable it decrements the reference count of object by 1. When reference count of one object becomes zero GC acts call destructor of object and then releases the memory acquired by the object.

Microsoft SQL Server FAQ's Part-III

noreply@blogger.com (Microsoft Techonologies Stuff) — Sat, 20 Sep 2008 20:47:00 +0530

Q1. Topic- Log File Page Size
In SQL Server 2005, what is the size of log file page?

A. 8KB
B. 16KB
C. Log files do not contain pages.

Ans: C

Explanation: Log files do not contain pages; they contain a series of log records.

Ref: Pages and Extents - http://msdn.microsoft.com/en-us/library/ms190969(SQL.90).aspx

Q2. Topic- Data Collector
In SQL Server 2008, can data collection items within the same collection set have different upload schedules?

A. Yes
B. No

Ans: B

Explanation: The correct answer is no. All collection items within a collector set, regardless of target, must be on the same upload schedule.

Ref: Data Collection Terminology - http://msdn.microsoft.com/en-us/library/bb677279.aspx

Q3. Topic- Cursor optimization
Of course, we all know that cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can't be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.

So, suppose you find yourself in a situation where you do need a cursor - which cursor option must be specified in order to assure maximum performance, assuming the cursor fits in cache?

A. none, the default options work best
B. FAST_FORWARD
C. FORWARD_ONLY
D. READ_ONLY
E. STATIC

Ans: E

Explanation: The default options are actually the slowest, since they create a dynamic cursor. In spite of its name, FAST_FORWARD does not result in the fastest performance, but can be faster with large tables that do not fit in memory. STATIC has been shown to be faster in situations where the cursor fits in cache. Specifying FORWARD_ONLY or READ_ONLY on a static cursor will not affect performance.

Ref: DECLARE CURSOR - http://msdn.microsoft.com/en-us/library/ms180169.aspx Curious Cursor Optimization - http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx (actual performance comparisons)

Q4. Topic- Identity Columns
Consider the following table.

CREATE TABLE Test
(
Col1 int IDENTITY(1,1)
)

Write the INSERT statement to populate the above table with sample values. Note: You are not allowed to use 'set identity_insert'

A. INSERT dbo.Test VALUES (1)
B. INSERT dbo.Test DEFAULT VALUES
C. INSERT dbo.Test DEFAULT VALUE

Ans: B

Explanation: The table contains only one column and it is an IDENTITY column. You cannot explicitly insert values to an IDENTITY column. Either you have to SET IDENTITY_INSERT ON before inserting the value or use the default values clause. DEFAULT VALUE is not allowed.

Ref: INSERT - http://msdn.microsoft.com/en-us/library/ms174335.aspx

Q5. Topic- Independence Day
Since SQL Server is mostly developed in the US, it's a holiday, and the editor lives in Denver, who were the main characters in the movie Independence Day?

A. Will Smith
B. Denzel Washington
C. Dennis Quaid
D. Jeff Goldblum

Ans: A, D

Explanation: Independence Day is a great science fiction, adventure movie about aliens invading the Earth and us fighting back on July 4th, Independence Day in the US. Will Smith and Jeff Goldblum play a fighter pilot and TV engineer respectively. Recommended for this weekend if you haven't seen it (or even if you have).

Ref: Independence Day - http://www.imdb.com/title/tt0116629/

Q6. Topic-Maximum Capacity
The maximum capacity of RAM that is supported by SQL Server 2005 Workgroup edition is how much?

A. 1GB
B. 2GB
C. 3GB
D. Operating system maximum

Ans: C

Explanation: The Workgroup edition of SQL Server 2005 can support up to 3GB of RAM.

Ref: Compare Editions - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Q7. Topic- Moving Files
How can you move data files in SQL Server 2005 from one drive to another? (Check all that apply)

A. Use sp_detach_db and sp_attach_db
B. ALTER DATABASE
C. Shut down the instance, move the files to a new drive, restart the instance with the -D parameter that specifices the database name and new path deparated by a colon.
D. Backup the database, drop it, and restore it using the WITH MOVE option.

Ans: A, B, D

Explanation: There are two basic ways of moving data files in SQL Server 2005. You can detach the files, copy them to the new location, and then attach them back. You can also use the ALTER DATABASE command with the MODIFY FILE options. You can also backup the database, drop it, and restore using the new location.

Ref: Moving User Databases - http://msdn.microsoft.com/en-us/library/ms345483.aspx

Q8. Topic- TABLESAMPLE
You have a default standard SQL 2005 SP2 server.
There is a table BigTable (col1 varchar(50)) with 10,000 rows.

How many rows does the following statement return?select col1 from bigtable tablesample system (10 percent)

A. 10
B. 1000
C. 10000
D. impossible to determine

Ans: D

Explanation: TABLESAMPLE returns an approximate percentage of rows, even if a number of rows is specified. This is used to get a sample of data from large rows and does not guarentee a number of rows or a random sample.

Ref: Limiting Result Sets Using TABLESAMPLE - http://technet.microsoft.com/en-us/library/ms189108.aspx

Q9. Topic- ALL
Assuming that I have the following values in the TimeGroup table, not case sensitive, (value1, value2, value3, value4), what does this query return?

if 'value1' < all ( select column1 from TimeGroup )
select 1
else
select 0

A. 1
B. 0

Ans: A

Explanation: This query checks the value given against the subquery by applying the "ALL" keyword to the operation. If all of the values returned meet these criteria, then TRUE is returned to the IF statement and it is false in this case since "VALUE1" was given in the question and that is not less than "value1". If A >= would return true.

Q10. Topic- Windowed Aggregate functions
Given the following table:

drop table #windows
create table #windows(id int identity(1,1), grp char(5), subgrp int, val int)
insert #windows (grp, subgrp, val)
select 'aaaaa',1,1 union all
select 'aaaaa',2,2 union all
select 'aaaaa',2,2 union all
select 'aaaaa',3,3 union all
select 'aaaaa',3,3 union all
select 'aaaaa',3,3 union all
select 'bbbbb',1,1

What does the following query return:

select grp,subgrp,count(*)
OVER (partition by grp,subgrp) grpcount,
count(*) OVER (partition by grp) subcount,
count(*) OVER (partition by NULL) grandcount
from #windows
group by grp, subgrp

A. aaaa 1 1 6 7, aaaa 2 2 6 7, aaaa 3 3 6 7, bbbb 1117
B. aaaaa 1 1 3 4, aaaaa 2 1 3 4, aaaaa 3 1 3 4, bbbbb 1 1 1 4
C. aaaaa 1 1 14 15, aaaaa 2 4 14 15, aaaaa 3 9 14 15, bbbbb 1 1 1 15
D.Msg 5309, Level 16, State 1, Line 1Windowed functions do not support constants as PARTITION BY clause expressions.

Ans: B

Explanation: Per ISO SQL - Windowed Aggregate function are calculated based on the relevant window in the RETURN SET. According to the expanded definition - that means that the Window is applied to the rows remaining, based on the following SQL Processing Steps (in ORDER):
1. JOIN/FROM clause
2. WHERE clause
3. GROUPING
4. AGGREGATION
5. AGGREGATION SELECTION (HAVING clause)
6. Window functions
7. SELECT clause
8. ORDER BY clause

In this example - the Windowed functions are firing against the result set AFTER the group by has been applied (so it's windowing over distinct values).

Ref:- For ISO SQL syntax information on Windowed Aggregate functions, see - http://savage.net.au/SQL/sql-2003-2.bnf.html

Q11. Topic- Database SnapShot
Database SnapShots are not allowed on which databases? (Check all that apply)

A. SalesDB
B. Master
C. Tempdb
D. AdventureWorksDW
E. Model
F. Msdb

Ans: B, C, E

Explanation: Database snapshots cannot be created on master, tempdb and model databases.

Ref: Limitations on Database Snapshots - http://technet.microsoft.com/en-us/library/ms189940.aspx

Q12. Topic- Cloning
Ever want or need a very much smaller version of your massive SQL 2005 production database where you could examine the execution plans for slow running queries, modify them and check for improvements, create new SPs and check execution plans all without tying up or bogging down your production environment? You do? Then create a clone. Which of these statements about cloning is true in SQL Server?

A. Creating a clone is not legal
B. Create a clone for testing
C. Clone is not efficient

Ans: B
Explanation: From a public blog - so owners permission to use is assumed - http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/21/cloning-in-sql-server-2005.aspx

Q13. Topic- CHECKDB
Does DBCC CHECKDB require space in tempdb?

A. Yes
B. No, all activity is in the current database being checked.

Ans: A

Explanation: A number of structures are created in memory while running tempdb and these can spill into tempdb if space is needed. It is not guarenteed that tempdb will be used, but it is used by the CHECKDB process if required.

Ref: CHECKDB From Every Angle: Why would CHECKDB run out of space? - http://www.sqlskills.com/blogs/paul/2007/11/06/CHECKDBFromEveryAngleWhyWouldCHECKDBRunOutOfSpace.aspx (near the bottom)

Q14. Topic- Sort Order - Include Null al last
You have a table with EmpName and DateOfLeaving columns. The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company. The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group. Consider the Example:

Table: Employees
EmpName DateOfLeaving
Abc 10 Oct 1999
Bcd 11 Nov 1998
Ccd null
Dcd 10 Aug 2000
Eed null

The solution should be:
EmpName DateOfLeaving
Bcd 11 Nov 1998
Abc 10 Oct 1999
Dcd 10 Aug 2000
Ccd null
Eed null

Which query will produce this output? (select all that apply)

A. Select EmpName, DateOfLeaving from Employees order by DateOfLeaving, EmpName asc
B. Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,'10/10/9999'),EmpName asc
C. Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc

Ans: B

Explanation: In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Ref: ORDER BY - http://msdn.microsoft.com/en-us/library/ms188385.aspx

Q15. Topic- ANY
Assuming that I have the following values in the TimeGroup table (value1, value2, value3, value4), what does this query return?

if 'Value1' < any ( select column1 from TimeGROUP )
select 1
else
select 0

A. 1
B. 0

Ans: A

Explanation: This query checks the value given against the subquery by applying the "ANY" keyword to the operation. If any value meets these criteria, then TRUE is returned to the IF statement and it is true in this case since "VALUE1" was given in the question.

Ref: ANY - http://msdn.microsoft.com/en-us/library/ms175064.aspx

Q16. Topic- CLR Assemblies
If you ALTER ASSEMBLY for one of your CLR objects and set the visibility to OFF, what does this imply?

A. No new objects can be created against the assembly
B. This assembly is only intended to be called by other assemblies
C. The assembly is disabled and all objects calling it return an error.
D. Reflection will not work with this assembly.

Ans: B

Explanation: The visibility property determines if the assembly can be called by other objects (stored procedures, functions, etc.) or only from other assemblies. OFF implies only other assemblies will call it. If you have objects referencing this assembly, the visibility cannot be changed.

Ref: ALTER ASSEMBLY - http://msdn.microsoft.com/en-us/library/ms186711.aspx

Q17. Topic- Cursors
If you open a cursor, which of these will allow you to get a count of the number of rows in the cursor? (select all that apply)

A. sp_cursor_list
B. sp_describe_cursor
C. sp_describe_cursor_columns
D. @@cursor_rows

Ans: A, B, D

Explanation: All of these items except sp_describe cursor columns will allow you to determine the number of rows in some cursors, subject to restrictions. The @@Cursor_rows requires a non-dynamic cursor.

Ref: sp_cursor_list - http://msdn.microsoft.com/en-us/library/ms186256.aspx sp_describe_cursor - http://msdn.microsoft.com/en-us/library/ms173806.aspx sp_describe_cursor_columns - http://msdn.microsoft.com/en-us/library/ms182755.aspx @@cursor_rows - http://msdn.microsoft.com/en-us/library/ms176044.aspx

Q18. Topic- A Hex on Your Database
Assuming you've taken these steps to prevent SQL Injection:
1. Replace single-quote ' with double-single-quote ''
2. Check for Select, Update, Delete
What happens if your web page runs into this at the end of the

URL:/YourPage.asp?account=1;declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)

And runs that in a dynamic SQL command in SQL Server 2005?

A. Error Message "Syntax error near '0x736...'"
B. Nothing
C. A list of all the databases on your server

Ans: C

Explanation: This is a currently common SQL injection attack. If the web page does not use stored procedures, but instead uses dynamic SQL, this is a valid SQL 2005 command (there are versions for SQL 2000), and might execute.

cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000)) resolves to "select name from sys.databases;".

With Varchar(max) and Varbinary(max), much more complex commands can be issued.

Q19. Topic- BETWEEN
Given the following script -

create table #fun(id int identity(1,1) primary key clustered, crit int)
insert #fun(crit)
select top 250000 rand(checksum(newid()))* 10
from syscolumns sc1, syscolumns sc2

--select statement #1
select count(*) from #fun
where crit between 3 and 5

--select statement #2
select count(*) from #fun
where crit between 5 and 3

Would statements #1 and #2 consistently return the same result set?

A. Yes
B. No
C. Can't be determined

Ans: B

Explanation: In plain language, BETWEEN is a commutative operation, so the order in which you specify the criteria is irrelevant.
In TransactSQL, however, BETWEEN is not communtative. Books Online defines BETWEEN as:
"BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression."
By that definition, there is no attempt to reorder the parameters, so Statement #2 will always return 0.
Ref:- For more info on BETWEEN - http://msdn.microsoft.com/en-us/library/ms187922.aspx

Q20. Topic- Know your UNION(s), NULL(s), COUNT(s) ?
What do you expect the result of the following query to be? No cheating, don't run until you've answered!

WITH DATA (Numbers) AS
(SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION
SELECT 3)
SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA
WHERE Numbers IS NULL

A. Null
B. 0
C. 1
D. 3
E. 6

Ans: B

Explanation: The final UNION negates the duplicates selected with the previous UNION ALL statements. The COUNT(ALL expression) evaluates the expression for each row in a group and returns the number of nonnull values. ALL Applies the aggregate function to all values. ALL is the default.
For investigation, change the final UNION to UNION ALL, and the SELECT COUNT (ALL Numbers) to COUNT (*)

Ref: COUNT - http://msdn2.microsoft.com/en-us/library/ms180026.aspx COUNT - http://msdn2.microsoft.com/en-us/library/ms175997.aspx

Q21. Topic- Database Mirroring
In SQL Server 2005 Database Mirroring, which form is used to specify the network address of the servers? (items inside brackets are replaced with your specifics)

A. tcp://:
B. http://:
C. dbm://:
D. udp://:

Ans: A

Explanation: The server address in Database Mirroring is specified with the TCP start, followed by colon and two slashes before the server address and port are specified, separated by a colon. The port must correspond to the endpoint on the other server that has been setup for database mirroring.

Ref: Specifying a Server Network Address (Database Mirroring) - http://msdn2.microsoft.com/en-us/library/ms189921.aspx

Q22. Topic- DMVs
What information is stored in the sys.dm_clr_tasks DMV?

A. A row for each assembly that has been added to the database
B. A row for each function in the database that has a CLR reference in its code.
C. A row for each task that is running a batch containin a CLR task
D. This DMV does not exist in SQL Server 2005

Ans: C

Explanation: The sys.dm_clr_tasks contains a row for each CLR task that is executing. Each batch containing a reference to CLR code creates a task and all CLR items in that batch execute under that task.

Ref: sys.dm_clr_rasks - http://msdn2.microsoft.com/en-us/library/ms177528.aspx

Q23. Topic- SQL Server 2008 FILESTREAM
How can you enable FILESTREAM in SQL Server 2008?

A. sp_filestream_configure
B. xp_cmdshell
C. xp_filestream_configure
D. sp_cmdshell

Ans: A

Explanation: For enabling FILESTREAM storage in SQL Server 2008 one needs to execute sp_filestream_configure with parameter @enable_level set to 1, 2, or 3.

Ref: sp_filestream_configure - http://msdn2.microsoft.com/en-us/library/bb934198(SQL.100).aspx

Q24. Topic- CASTing
Second question of day: what is the len of @c?

declare @c varchar(800)
set @c = N'hello' + replicate('-',800)
print len(@c)print @c

A. 800
B. 400
C. 4000

Ans: A
Explanation: The CAST to nvarchar(800) has a maximum 4000 character len. The CAST then to varchar(800) fits in that space, so the len is 800

Ref: CAST and CONVERT - http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Q25. Topic- Physical Database Architecture
How many pages are allocated to a log file in a database with a 10MB log?

A. 1280
B. 160
C. 0

Ans: C

Explanation: Zero. Log files contain log records, not pages.

Reference: Pages and Extents - http://msdn2.microsoft.com/en-us/library/ms190969.aspx

Download HTML Sample Book

noreply@blogger.com (Microsoft Techonologies Stuff) — Sat, 20 Sep 2008 02:47:00 +0530

Click Here to Download!

Microsoft SQL Server FAQ's Part-II

noreply@blogger.com (Microsoft Techonologies Stuff) — Thu, 18 Sep 2008 21:29:00 +0530

Q1. Topic- Exponent Engima
Executing the following script on SQL Server 2005 :

declare @a bigint, @b bigint
set @a = 16
select sqrt(@a^@a - 17)

Returns "A domain error occurred." Why?

A. 16^16 = 2^64 which is out of bigint's range
B. @b-17 is negative, which is invalid for an exponent
C. Sqrt() only allows non-negative inputs
D. This is a known bug in Sql Server 2005

Ans: C

Explanation: @a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.

Ref: ^ (Bitwise XOR) - http://msdn.microsoft.com/en-us/library/ms190277.aspx

Q2. Topic- Error Logs
Which of the following is written in the SQL Server error log? (select all that apply)

A. Current Build Number
B. Server Process ID
C. Startup Parameters
D. Licensing Mode

Ans: A, B, C

Explanation: All of these except the licensing mode are written to the SQL Server error log.

Q3. Topic- Partition Functions
What would this code return?

CREATE PARTITION FUNCTION PartitionFunction1 ( int )
AS
RANGE FOR VALUES (10, 100, 1000) ;
GO
SELECT $PARTITION.PartitionFunction1 (10) ;
GO

A. 0
B. 1
C. Sntax error enar $Partition

Ans: B

Explanation: The Partition$ function returns the partition number from a partition function setup and the range value passed in.

Q4. Topic- Data Compression
When compressing indexes with Page compression, how are non-leaf level pages compressed?

A. They are not compressed
B. They are row compressed
C. They are page compressed

Ans: B

Explanation: Non leaf level pages in indexes are only compressed with row compression, even when page compression is enabled for the index.

Ref: Page Compression Implementation - http://msdn.microsoft.com/en-us/library/cc280464(SQL.100).aspx

Q5. Topic- Count
What will be output of below query?

DECLARE @t table
( id int identity(1,1),
value varchar(50)
)

INSERT INTO @t values(NULL)
INSERT INTO @t values(NULL)
INSERT INTO @t values(NULL)
INSERT INTO @t values('1')
INSERT INTO @t values('2')
INSERT INTO @t values('3')
SELECT count(id), count(value) FROM @t

A. 3 3
B. 3 6
C. 6 3
D. 6 6

Ans: C

Explanation: The Count aggregate does not include NULL values, so the count will be 3 rows for the 2nd column.

Ref: COUNT - http://msdn.microsoft.com/en-us/library/ms175997.aspx

Q6.Topic- GROUP BY ALL
NOTE. This question is about what the RTM version of SQL Server 2008 will do, based on currently publicly available documentation and other information as of July 1st, 2008.

Suppose that you have just installed the RTM version of SQL Server 2008, with all options and settings at the default values. You now create and populate a table as follows:

CREATE TABLE DailySales
(Region tinyint NOT NULL,
SalesDate date NOT NULL,
SalesTotal decimal(9,2) NOT NULL,
PRIMARY KEY (Region, SalesDate));
INSERT INTO DailySales (Region, SalesDate, SalesTotal)
VALUES (1, '20080601', 153), (1, '20080602', 208),
(1, '20080603', 905), (1, '20080604', 12),
(2, '20080603', 19), (2, '20080605', 195),
(3, '20080605', 75), (3, '20080606', 230),
(3, '20080607', 900), (3, '20080608', 405);

What result should be returned when you run this query:

SELECT Region, COUNT(*) AS NumSales, AVG(SalesTotal) AS AvgValue
FROM DailySales
WHERE SalesDate < '20080605' GROUP BY ALL Region HAVING COUNT(*) <2;

A. Msg 156, Level 15, State 1, Line 17 - Incorrect syntax near the keyword 'ALL'.

B. Msg 4147, Level 15, State 1, Line 17. The query uses GROUP BY ALL. To run this query without modification, please set the compatibility level for current database

C. to 90, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query. In future versions of SQL Server, GROUP BY ALL will not be supported even in backward-compatibility modes.

D. Results of Region = 1, 2 - NumSales = 4, 1 - AvgValue = 319.5, 19

E. Results of Region = 2, 3 - NumSales = 1, 0 - AvgValue = 19, NULL

F. Results of Region = 2 - NumSales = 1 - AvgValue = 19

Ans: D

Explanation: The ALL option of the GROUP BY causes the result set to include a group for each Region in the input of the query, even those for which all rows are filtered out by the WHERE clause. The effect of the ALL keyword is applied before the HAVING clause is, so HAVING COUNT(*) > 0 would effectively undo the results of the ALL keyword.
Even though GROUP BY ALL will be deprecated in SQL Server 2008, it is still supported and does not require the database to be set to a lower compatibility level.

Ref: Group by - http://msdn.microsoft.com/en-us/library/ms177673(SQL.100).aspx

Q7.Topic- Power
What will this SQL statement return?

SELECT power(2,31)

A. 0
B. 2147483648
C. Arithmetic overflow error

Ans: C

Explanation: Return of Power will be of int type as base value is int (2 is considered an int). But power(2,31) exceeds the limit of int value, so this will throw arithmetic overflow error.

Q8.Topic- Resource Governer
You have created a new workload pool in SQL Server 2008's Resource Governer and your application's logins are assigned to this pool. You notice that they are submitting many batches which are slowing your ETL process. You adjust their maximum CPU down to 50%. When does this take effect?

A. Immediately
B. When the database is closed and reopened
C. When the instance is restarted
D. When the host is rebooted.

Ans: A

Explanation: If you change the CPU percentages in the resource governer, the effect takes place immediately, though currently executing threads will complete their execution.

Ref: Resource Governor States - http://msdn.microsoft.com/en-us/library/bb895389(SQL.100).aspx

Q9. Topic- TOP and TABLESAMPLE
You have a default standard SQL 2005 SP2 server. There is a table BigTable (col1 varchar(50)) with 10,000 rows. Which of the following statements are guaranteed to return 1000 rows? (select all that apply)

A. select top 1000 col1 from bigtable
B. select col1 from bigtable tablesample system (1000 rows)
C. select top 10 percent col1 from bigtable
D. select col1 from bigtable tablesample system (10 percent)

Ans: A, C

Explanation: TABLESAMPLE returns an approximate percentage of rows, even if a number of rows is specified. TOP will return the number of rows specified if that number of rows exist.

Ref: Limiting Result Sets by Using TABLESAMPLE - http://technet.microsoft.com/en-us/library/ms189108.aspx Limiting Result Sets by Using TOP and PERCENT - http://technet.microsoft.com/en-us/library/ms187043.aspx

Q10. Topic- SQL Trace
In SQL Server 2008, if you have a server side trace running, and you run out of disk space, what happens?

A. The server instance stops.
B. The current database in which the trace is set closes.
C. Tracing stops, but the server continues to run.
D. The server pauses, allowing read-only queries, but no updates until more disk space is added.

Ans: C

Explanation: If you have a normal SQL Trace setup, and you run out of disk space, the server continues to run, but tracing stops.

Ref: Using SQL Trace - http://msdn.microsoft.com/en-us/library/ms191443.aspx

Q11. Topic- Deterministic Functions
The RAND function is deterministic only when a seed parameter is specified.

A. Yes
B. No
C. depends on definition

Ans: A

Explanation: RAND is one of those functions that is deterministic in some situations. If a SEED parameter is used, this is a deterministic function.

Ref: Deterministic and Nondeterministic Functions - http://technet.microsoft.com/en-us/library/ms178091.aspx

Q12.Topic- Page Compression
Page compression consists of which types of compression? (Select all that apply)

A. Row compression
B. Prefix Compression
C. Postfix Compression
D. Dictionary Compression

Ans: A, B, D

Explanation: Page compression is a superset of Row compression and includes Row compression. It also does prefix compression and dictionary compression to fit more rows on a page.

Ref: Page Compression Implementation - http://msdn.microsoft.com/en-us/library/cc280464(SQL.100).aspx

Q13. Topic- Updating through cursors
Of course, we all know that server-side cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can't be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.

So, suppose you find yourself in a situation where you do need a cursor, and you also need to change the data retrieved by the cursor - how can you assure maximum performance?

A. Use FOR UPDATE OF columnname, columnname in the cursor declaration, and use WHERE CURRENT OF cursorname in the update statement
B. Use FOR UPDATE (without column list) in the cursor declaration, and use WHERE CURRENT OF cursorname in the update statement
C. Do not use FOR UPDATE in the cursor declaration, and use WHERE keycolumn = @keyvalue in the update statement

Ans: C

Explanation: Though not documented by Microosoft, extensive testing has shown that reading a STATIC cursor with no FOR UPDATE option and using the primary key to update the row just read is faster than specifying a FOR UPDATE option (either with or without a column list) and using the WHERE CURRENT OF clause in the UPDATE statement.

Ref: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

Q14. Topic- References Permission
What is the purpose of the REFERENCES permission?

A. Allows altering the size, but not the data type of the table.
B. Allows the owner of another table to use columns in the table to which they've been granted that permission as part of a foreign key.
C. Allows the user to send a data reference directly from the database to others without SELECT permissions.
D. Allows users (other than the table owner) to read encrypted values.

Ans: B

Explanation: Assigning REFERENCES permission allows the owner of another table to use columns in the table to which they've been granted that permission as the target of a REFERENCES FOREIGN KEY constraint with his or her table. However, that person won't be allowed to change the structure of the table they've been granted the permission for.

Ref: GRANT Object Permissions- http://msdn.microsoft.com/en-us/library/ms188371.aspx

Q15.Topic- SQL Server Profiler
In SQL Server 2008's Profiler, which is a faster way to capture trace data?

A. Capturing to a table
B. Capturing to a file
C. Capturing to an SSIS stream.

Ans: B

Explanation: SQL Server 2008's Profiler allows you to capture data to a SQL Server table or a file. Capturing to a file is much faster.

Ref: Saving Traces and Trace Templates - http://msdn.microsoft.com/en-us/library/ms189616.aspx

Q16.Topic- ANSI_NULLS
Assume the following table

CREATE TABLE testnull(ggg INT NULL)
INSERT testnull(ggg)SELECT NULL UNION ALLSELECT 1 UNION ALLSELECT 0
What counts do you get from the followingSELECT Count(*)
FROM testnull GOSET ANSI_NULLS OFFGOSELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1;

SELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1
OR ggg=NULL;GOSET ANSI_NULLS ONGOSELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1;

SELECT Count(*)
FROM testnull WHERE ggg=1
OR ggg!=1
OR ggg=NULLGODROP TABLE testnull

A. 3,3,3,2,2
B. 3,3,2,2,2
C. 3,2,3,2,2
D. 3,2,3,2,3
Ans: C

Explanation: Although Setting the ANSI_NULLS setting to OFF allows for using the =NULL notation, it does NOT affect how NULL operates with regards to comparisons with non-null values.

the SET ANSI_NULLS OFF has been marked as deprecated in SQL 2005 and will be removed in the future. For more info on ANSI_NULLS, read the following Books Online document:

Ref: SET ANSI_NULLS- http://msdn2.microsoft.com/en-us/library/ms188048.aspx

Q17.Topic- Resource Governer
In the SQL Server 2008 resource governer, if you alter your classification function, when does this take effect?
A. For all current connections
B. For new session connections
C. When a new database is accessed
D. When the server restarts

Ans: B

Explanation: If you alter the classification function, this affects all new session connections. Existing connections are not altered with this change.

Ref: Resource Governor States - http://msdn.microsoft.com/en-us/library/bb895389(SQL.100).aspx

Q18.Topic- Resource Governor
Which components of SQL Server 2008 can the Resource Governor operate on?

A. The database engine
B. Reporting Services
C. Integration Services
D. Analysis Services

Ans: A

Explanation: The Resource Governor can control resources in the database engine only. It cannot work on Reporting Services, Integration Services, or Analysis Services.

Ref: Introducing Resource Governor - http://msdn.microsoft.com/en-us/library/bb895232(SQL.100).aspx">

Q19. Topic- Sparse Columns
If you add sparse columns to a table in SQL Server 2008, what is the maximum row size for in-row storage?
A. 8,060
B. 8,128
C. 8,018
D. 2 GB

Ans: C

Explanation: In SQL Server 2008, there is a new type of column, a sparse column, for columns that are mostly NULL values. You can have many sparse columns, up to 30,000, in a table, but if you add sparse columns, the maximum row size is reduced to 8,018.

Ref: Using Sparse Columns - http://msdn.microsoft.com/en-us/library/cc280604(SQL.100).aspx

Q20. Topic- Transaction
In SQL Server 2005, which isolation levels do not acquire shared locks while performing read transactions?

A. READ UNCOMMITTED
B. SNAPSHOT
C. REPEATABLE READ
D. ALL

Ans: A, B

Explanation: READ UNCOMMITTED and SNAPSHOT levels do not acquire shared locks.

Ref: http://msdn.microsoft.com/en-us/library/ms173763.aspx

Q21. Topic- Name Spaces
After running the following code in the AdventureWorks sample database on SQL Server 2008 (or SQL Server 2005): (URL broken for formatting purposes.

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT Applicants.LastName.value('fn:string(.)[1]', 'nvarchar(100)')
FROM HumanResources.JobCandidate jc
CROSS APPLY jc.Resume.nodes('//ns:Name.Last') Applicants(LastName);

What do you get?

A. An error message since WITH XMLNAMESPACES can only be used with the OPENXML function and not with the xml data type .nodes() method.
B. An error message since SQL Server’s XQuery implementation does not support the “//” (descendant-or-self) axis specifier.
C. No error message and the last name of every AdventureWorks job candidate is returned in a result set.

Ans: C

Explanation: The answer is "No error message is returned and the last name of every AdventureWorks job candidate is returned in a result set.

WITH XMLNAMESPACES can be used with the xml data type methods, including .nodes(), so (a) is wrong. SQL Server’s XQuery implementation does support the “//” (descendant-or-self) axis specifier so (b) is wrong.

This question brought to you by Michael Coles, author of Pro SQL Server 2005 Programmer's Guide

Q22. Topic- Data Compression
Which of the following is true?

A. Enabling Row compression enables Page compression
B. Enabling Page compression enables Row compression
C. Enabling Backup compression enables Page compression
D. Enabling Backup compression enables Row compression

Ans: B

Explanation: If you enable Page compression on an object, it enables Row compression by default. Page compression is a superset of Row compression. Neither is affected by Backup Compression.

Ref: Page Compression Implementation - http://msdn.microsoft.com/en-us/library/cc280464(SQL.100).aspx

Q23. Topic- Reformat via query?
A user at your site shows you the contents of his SSMS window, connected to a SQL Server 2005 server with Service Pack 2. In the query pane, he has:

Drive C:\ is being reformatted, please wait...
COMPLETE
Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when sending the request to the server. (error: Cannot open file for database 'Master' at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf)

A. Accidental omission of the WHERE clause
B. A trojan sqlservr.exe acting just like Microsoft's sqlservr.exe except that executing the query above will reformat the main hard drive
C. A coworker prank where the error text was directly typed into the Result Messages tab
D. A SQL 2005 SP2 bug with disastrous consequences

Ans: C

Explanation: SSMS allows the user to type input text directly into the message tab. Running the indicated query would replace the error text shown with the number of records returned or "Command(s) completed successfully." as expected.

Q24.Topic- Resource Governor
At which level of the server does the Resource Governor control resource limits?

A. The object level
B. The schema level
C. The database level
D. The instance level
E. The Windows host level

Ans: D

Explanation: The Resource Governor in SQL Server 2008 operates at an instance level. Different connections can be separated into different pools, and they might have limits on which sections of the server they can use, but the limits are set a the instance level.

Ref: Introducing Resource Governor - http://msdn.microsoft.com/en-us/library/bb895232(SQL.100).aspx

Q25. Topic- SQL Server XML
After running the following code in SQL Server 2008 (or SQL Server 2005), what is the result?

DECLARE @x xml;SET @x = CONVERT(xml, N' ]> Steak Green Beans Cornbread ©right; ', 2);SELECT @x;

What is the result?

A. An error message that SQL Server cannot parse XML with internal subset DTDs.
B. An error message that the XML is not well formed check because of an undeclared entity.
C. No error message and some XML returned.
D. An error message that the XML does not conform to the structure defined in the DTD.

Ans: C

Explanation: The answer is c) No error message is returned, but the entity defined in the DTD (©right;) is expanded in the XML data. This data is returned:

Steak Green Beans
Cornbread
© 2008 by SQL Server Chefs, Inc.

Using CONVERT with the style option 2 (or 3) allows SQL Server to parse XML with internal subset DTDs, so (a) is wrong. The ©right; entity is defined in the DTD so SQL Server does not throw an undeclared entity error, making (b) wrong. The SQL Server xml data type does not use DTDs to constrain structure (instead it uses XML schemas), so the fact that the XML data does not conform to the DTD does not throw an error. This makes (d) wrong.

This question is provided by Michael Coles, author of Pro T-SQL 2005 Programmer's Guide (Expert's Voice)

Microsoft SQL Server FAQ's Part-I

noreply@blogger.com (Microsoft Techonologies Stuff) — Sat, 13 Sep 2008 20:02:00 +0530

Microsoft SQL Server FAQ's

Those who wants to test there knowledge towards Microsoft SQL Server here i have a some important Questions.

Q1. Topic: iFTS( integrated full- text)
In SQL Server 2008's integrated full-text search, if you wish to create a stoplist, what are your options? (Choose all that apply)

A. Base it on an empty list (create from scratch)
B. Create from a system default stoplist
C. Create it from another user defined stoplist.
D. Create it from a dictionary file

Ans: A, B, C.

Explanation: Custom stoplists in SQL Server 2008 contain noise words that are not relevant to most searches.

Ref: Stopwords and Stoplists - http://msdn.microsoft.com/en-us/library/ms142551.aspx

Q2. Topic- SQL Trace
If you are creating a server side trace with T-SQL stored procedures in SQL Server 2008, can events be lost if you have disk space?

A. Yes, if the server is busy
B. No, all events are guarenteed to be captured.

Ans: B

Explanation: The correct answer is no. All events are guaranteed to be captured if a server side trace is used. It is possible that you cannot read all events if a write error occurs, but the event would still be captured, just the logging missed.

Ref: Using SQL Trace - http://msdn.microsoft.com/en-us/library/ms191443.aspx

Q3. What is the result ? (SQLServer 2005)
In SQL Server 2005, what would be the result of this code?

print 'Hey what''s going on ?';
GO 10

A. Incorrect syntax was encountered while parsing GO.
B. 'Hey what''s going on ?'; is printed in the result
C. The batch is executed 10 times

Ans: C

Explanation: The result is:

Hey what's going on ?

The batch execution is completed 10 times. BOL states:

GO [count]

where count is a positive integer. The batch preceding GO executes the specified number of times.

Ref: GO - http://www.exceptionaldba.com/images/exceptional-logo.gif

Q4. Topic- Type lengths
How many rows will be returned in each of the three resultsets below?

CREATE TABLE #TEST(A varchar,B varchar(30));
INSERT INTO #TEST(A) VALUES('A');
INSERT INTO #TEST(B) VALUES('Some text');
INSERT INTO #TEST(A) VALUES('B');
INSERT INTO #TEST(B) VALUES('Some more text');

SELECT COUNT(*)
FROM #TEST
GROUP BY LEN(ISNULL(B,A));

SELECT COUNT(*)
FROM #TEST
GROUP BY LEN(ISNULL(A,B));

SELECT COUNT(*)
FROM #TEST
GROUP BY LEN(COALESCE(A,B));

A. 1,1,1

B. 2,2,2

C. 3,3,3

D. 3,1,3

E. 3,1,1

Ans: D

Explanation: The output type of ISNULL is determined by its first argument. Hence, ISNULL(A,B) above will result in a varchar of length 1 (datatype of column A).

Ref: IsNULL - http://msdn.microsoft.com/en-us/library/ms184325.aspx

Q5. Topic- Data Collector
What type of data store is the Management Data Warehouse in SQL Server 2008?

A. Relational Database
B. Object Database
C. Cube
D. Flat File database

Ans: A

Explanation: The Management Data Warehouse in SQL Server 2008 is a relational database, just like any other relational database in the SQL Server database engine. It stored performance data from SQL Server instances and databases.

Ref: Data Collection Terminology - http://msdn.microsoft.com/en-us/library/bb677279.aspx

Q6.Topic- Spatial Indexing
What is the maximum number of spatial indexes on a table in SQL Server 2008?

A. 1
B. 8
C. 249
D. 1024

Ans: C

Explanation:
You can create up to 249 spatial indexes on columns in a table, including more than one spatial index on the same column.

Ref: Restrictions on Spatial Indexes - http://msdn.microsoft.com/en-us/library/bb964740(SQL.100).aspx

Q7. Topic- Best Practice
You receive some data from UI and need to populate table Customer if the name received from UI does not exist in database. Which of options below should be used for this purpose:

IF NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )
INSERT INTO [Customer]
( [Name])
VALUES
( @CustomerName)

2.

INSERT INTO [Customer]
( [Name])
SELECT @CustomerName
WHERE NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )

A. Option 1 is the one to use
B. Option 2 is the one to use
C. Both options are equal, any one may be used according to personal preferences

Ans: B

Explanation: Option 1 has 2 separate statements. The first one applies locks relevant table resources in order to perform check for existence. As soon as it's completed SQL Server releases all applied locks. At the moment parallel SPID may insert a record into the table BEFORE following INSERT statement has applied its locks.

Therefore initial check may be irrelevant at the moment when following INSERT starts. IF there is a unique constraint on Customer.Name INSERT would fail despite you've done the check for existence.

The second option does everything in a single transaction. It applies the locks and holds it until INSERT transaction has finished its job. Another SPID cannot insert another row until all locks on the object applied by INSERT statement are released.

So, the first option is unacceptable, it relies on a user's luck not to have another user doing same thing at the same time. Of course, probability of the failure is quite low but it's a possible event. In active transactional systems with hundreds of transactions per second it does not look so improbable.

Script to run the test:

USE pubs

CREATE TABLE Customer

(

ID int IDENTITY(1,1) NOT NULL,

Name nvarchar(100) NOT NULL,

UNIQUE (Name)

)

DECLARE @CustomerName nvarchar(100)

SET @CustomerName = 'Customer2'

IF NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )

BEGIN

WAITFOR DELAY '00:00:05'

/* this 5 sec pause lets you insert same row from another QA window:

DECLARE @CustomerName nvarchar(100)

SET @CustomerName = 'Customer2'

INSERT INTO [Customer]

( [Name])

VALUES

( @CustomerName)

INSERT INTO [Customer]

( [Name])

VALUES

( @CustomerName)

END

SET @CustomerName = 'Customer3'

INSERT INTO [Customer]

( [Name])

SELECT @CustomerNameWHERE NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )

select Object_Id('Customer')

DROP TABLE Customer

Q8.Topic- Index covering
What is a "covered query" or "Index Covering?

A. A covered query is a query where all of the columns necessary are contained in the index.
B. There's no such thing, you made it up
C. A query that accesses more than one table
D. A query that accesses the same table more than once

Ans: A

Explanation: Since all of the columns are in the index, it's not necessary for the query to use the table - it will only use the index. As always with indexes, be sure to test, because while indexes will speed up queries, they will generally have the opposite effect on inserts and updates.
Reference:

Designing an Index - http://msdn.microsoft.com/en-us/library/aa933128.aspx

General Index Design Guidelines - http://msdn.microsoft.com/en-us/library/ms191195.aspx http://msdn.microsoft.com/en-us/library/ms979195.aspx - http://msdn.microsoft.com/en-us/library/ms979195.aspx

Q9. Topic- iFTS
In SQL Server 2008's integrated full-text search, what is the new terminology for noise words?

A. Noise Text
B. Discards
C. Stopwords
D. Ignore Words

Ans: C

Explanation:
SQL Server 2008 introduces the concept of stop words and stoplists (a collection of stop words) to replace the previously used noise words in SQL Server 2000 and SQL Server 2005.

Ref: Stopwords and Stoplists - http://msdn.microsoft.com/en-us/library/ms142551.aspx

Q10. Topic- Simulating memory pressure
You have been asked to optimize a stored procedure that runs against a terabyte-sized database. The stored procedure executes several steps consecutively. The performance problems appear to be mainly I/O related.

You install a severely trimmed down test version of the database (1 GB in size) on your desktop computer running SQL Server Developer Edition. Before you start optimizing, you want to establish a baseline by timing the stored procedure on your development machine, so that you can later compare performance after adding indexes and tweaking code.

However, your desktop has 2 GB of memory installed, and you are concerned that the performance test results may be skewed because the test version of the database fits entirely in cache. What is the best way to simulate the production circumstances as closely as possible?

A. Create a bigger test database, at least 5 GB in size.
B. Execute sp_configure and RECONFIGURE to limit server memory to 512 MB, then restart the SQL Server service.
C. Shut down your desktop, open the cover, remove some of the memory DIMMs and restart the desktop.
D. Execute DBCC DROPCLEANBUFFERS before starting the test.
E. Start some really memory hungry applications before starting the test.

Ans: B

Explanation: Executing "EXEC sys.sp_configure N'max server memory (MB)', N'512'" followed by "RECONFIGURE WITH OVERRIDE" will limit the memory used by your server to 512 MB. Since SQL Server might have already allocated more memory, which will not be released immediately, a reboot will force this memory setting to be respected.

Creating a bigger test database will also work, but involves a lot more work, will cause your tests to run longer, and might require you to clean up your collection of holiday pictures. Removing some RAM from your computer works as well, but will cause all other processes in your computer to slow down as well. Not recommended (especially if you are as clumsy in handling electronic equipment as I am)

Executing DBCC FREEPROCCACHE before starting the tests will cause the FIRST query to run with no data in cache, but the remaining steps of the stored procedure will once more be able to read data from cache. This is not a good simulation of the production circumstances. Starting memory hungry applcations might indeed cause available memory for SQL Server to be reduced. But it will also cause extra strain for the CPU, and you might get SQL Server and the other applications "fighting" for memory or Windows starting to page some applications in and out of memory. Again, not a good simulation of the production circumstances.

Q11. Topic- TSQL variable
What will be the output?

Declare @var

intSelect @var = isnull(@var,1) + Value1

From (Select 1 Value1 Union All Select 1 Union All Select 2) as a Select @var

A. 3
B. 5
C. Syntax Error

Ans: B

Explanation: The correct answer is 5. The query's behavior is the same as selecting value1 = value1 + 1.

Q12. Topic- DB File Extension
SQL Server 2005 does not enforce database filename extensions, but which of the following are the default file extensions for database files? (choose all that apply)
A. *.mdf
B. *.ldf
C. *.ndf

Ans: A, B, C

Explanation: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use. MDF is for primary database files, NDF is for secondary database files and LDF is for log files.

Ref: Files and Filegroups Architecture - http://msdn.microsoft.com/en-us/library/ms179316.aspx

Q13. Topic- Single Page Restore
Can all page types be restored with a single page restore?

A. Yes
B. No

Ans: B

Explanation: While data pages can be restored with the single page restore feature, there are page types that cannot be restored. The following types cannot be single page restored:

. File header pages
. Boot page
. GAM, SGAM, DIFF map, and ML map pages

Ref: Search Engine Q&A #22: Can all page types be single-page restored? - http://www.sqlskills.com/blogs/paul/2008/08/11/SearchEngineQA22CanAllPageTypesBeSinglepageRestored.aspx

Q14. Topic- Counts Puzzle
What will be the count against each category? Each row in the result is shown as a pair in the answers.

CREATE TABLE #CATEGORY
(

CATID INT,

VAL1 INT

)

INSERT INTO #CATEGORY VALUES(1,NULL)
INSERT INTO #CATEGORY VALUES(2,1)
INSERT INTO #CATEGORY VALUES(3,2)

CREATE TABLE #DATA
(VAL1 INT)

INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(2)
INSERT INTO #DATA VALUES(2)
INSERT INTO #DATA VALUES(3)

SELECT C.CATID, COUNT(*)
FROM #DATA D
INNER JOIN #CATEGORY C ON C.VAL1 = D.VAL1 OR C.VAL1 IS NULL
GROUP BY C.CATID

A. 1 6,2 3,3 2
B. 1 5,2 3,3 2
C. 1 6,2 3,3 3
Ans: A

Explanation: For category 1, the join condition is true for all data records, that's why its count is 6. For category 2 and 3, due to the equi join, it returns the exact count for each one respectively.

Q15. Topic- Go go go?
Running this in SSMS gives what?
CREATE PROC GO @GO int=NULL AS SELECT @GO

GO;

GO 3

EXECUTE('GO 3')

GO 3

DROP PROC GO

GO
A. Some resultset are returned along with the error: Incorrect syntax near the keyword 'GO'.
B. Error: Incorrect syntax near the keyword 'GO'.
C. Some resultsets are returned.
D. Error: Invalid object name 'GO'.
E. No results and no error.
F. Error: A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO.

Ans: C

Explanation: "GO" is simply a batch delimiter used by SSMS and is not passed the query engine. "GO;" however, is interpreted as statement (because of the ";") and since "GO" is simply a SSMS thing creating and running the procedure is all ok. "G0 x" means run the above batch x times.

Q16. Topic- Spatial Indexing
If you wish to create a spatial index in SQL Server 2008, which of the following is required? (select all that apply)
A. A primary key must be defined on the table.
B. The index cannot be on an indexed views.
C. The table must have a clustered index
D. The table must be have a rowversion column defined.

Ans: A, B

Explanation: A spatial index is for columns that are of the geometry or geography types and requires that a primary key be defined, of which there are less than 15 key columns. There cannot be a spatial index created on indexed views, but the table does not need a rowversion column or a clustered index.

Ref: Restrictions on Spatial Indexes - http://msdn.microsoft.com/en-us/library/bb964740(SQL.100).aspx

Q17. Topic- Boot Page
In which Page does the BootPage of the Database reside?
A. Header Page
B. Master Database Header Page.
C. 1 in file 1
D. 9 in file 1

Ans: D

Explanation: The Boot Page of Any database is on the page 9 in File 1.

Ref: Boot pages and boot page corruption - http://www.sqlskills.com/blogs/paul/2008/07/11/SearchEngineQA20BootPagesAndBootPageCorruption.aspx

Q18. Topic- Hashing
An undocumented hash routine in SQL Server is pwdencrypt. What is the name of the function to check the hash?
A. pwdcontrast
B. pwdcompare
C. pwdconfirm

Ans: B

Explanation: The function that should be used is PWDCOMPARE as shown in this sample code:

Select (pwdcompare('mypassword',CONVERT(varbinary(255), pwdencrypt('mypassword')))) as checker

Ref: SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt - http://blogs.msdn.com/lcris/archive/2007/10/31/sql-server-undocumented-password-hashing-builtins-pwdcompare-and-pwdencrypt.aspx

Q19. Topic- SQL Server 2005
The Database Engine features that are disabled by default on SQL Server 2005 installations are:
A. CLR Integration
B. Database Mail
C. SQL Mail
D. Web Assistant

Ans: A, B, C, D.

Explanation: All of these features are disabled by default in SQL Server 2005.

Ref: CLR - http://msdn.microsoft.com/en-us/library/ms345099.aspx

Database Mail - http://msdn.microsoft.com/en-us/library/ms175887.aspx

SQL Mail - http://msdn.microsoft.com/en-us/library/ms175887.aspx

Web Assistance - http://msdn.microsoft.com/en-us/library/ms175576.aspx

Q20. Topic- FORMATMESSAGE
If you use the FORMATMESSAGE command to return an error message, is the text localized?
A. Yes
B. No

Ans: A

Explanation: The FORMATMESSAGE determines looks up localized versions of error messages and returns those. If one does not exist, the US English version is used.

Ref: FORMATMESSAGE - http://msdn.microsoft.com/en-us/library/ms186788.aspx

Q21. Topic- Short cut key
What is the short cut key to execute sp_help procedure for any object in a Query Window in Management Studio? The object must be highlighted in the code window.

A. Ctrl+F1
B. Alt+F1
C. Shift+F1
D. Short cut is not available for sp_help

Ans: B

Explanation:
To execute sp_help procedure for any object, just select object name and press Alt+F1.

Ref: Query Window Shortcuts - http://www.kodyaz.com/articles/sql-query-window-short-cuts.aspx

Q22. Topic- Update Stats with Reindex
Does SQL Server update statistics with Reindex on a table in SQL Server 2005?

A. No
B. Yes, with partial sampling.
C. Yes, With full table scan details

Ans: C

Explanation: Whenever an index is rebuilt the stats on the table is updated with the details of a full table scan.

Ref: Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics - http://www.sqlskills.com/blogs/paul/2008/01/27/SearchEngineQA10RebuildingIndexesAndUpdatingStatistics.aspx

Q23. Topic- LIKE operator
Consider the T-SQL statements given below;

create table #Like_Test
( Col1 varchar(5) )
go

insert #Like_Test values('_Cus')

insert #Like_Test values('Cus_')

insert #Like_Test values('C_us')

insert #Like_Test values('Cus')

insert #Like_Test values('Cu_s')

select * from #Like_Test
where Col1 Like '%Cu_%'

How many records would you expect from the SELECT query?

A. 4
B. 1
C. 5

Ans: A

Explanation: The meaning of the ‘_’ (underscore) in LIKE string is, to represent any single character. This will not recognize the ‘_’ as a string of the Col1. You can use ECSAPE key word at the end of the statement to consider ‘_’ as a string.

Ref: LIKE - http://msdn.microsoft.com/en-us/library/ms179859.aspx

Q24. Topic- Rowversion
What data type does this return in SQL Server 2005, SP2 and above?

SELECT MIN_ACTIVE_ROWVERSION()

A. int
B. Bigint
C. Datetime
D. Timestamp
E. Nothing as this is not a real SQL Server function.

Ans: D

Explanation: The MIN_ACTIVE_ROWVERSION function returns a timestamp data type that is the minimum value in the database. The rowversion value actually replaces the timestamp data type.
This was introduced in SP2 for SQL Server 2005.
Ref: MIN_ACTIVE_ROWVERSION - http://msdn.microsoft.com/en-us/library/bb839514.aspx

Q25. Topic- VarChar(max)?!?
What does the following SQL 2005 code snipet return?

Declare @Str VarChar(max);

Set @Str=Replicate('*',10000)+Replicate('*',10000);

Print Len(@Str);

A. 8000
B. 10000
C. 20000

Ans: A

Explanation: Apparenly VarChar(max) isn't intrinsically supported everywhere. You can fix it by CONVERT/CASTing the Replicate() string parameters to VarChar(max)!

Declare @Str VarChar(max);

Set @Str=Replicate(Cast('*' as VarChar(max)),10000)+Replicate(Cast('*' as VarChar(max)),10000);

Print Len(@Str);