Querying Microsoft SQL Server 2012

Itzik Ben-Gan, Dejan Sarka, Ron Talmage

Mentioned 3

Designed to prepare students for the certification exam by using practical exercises to teach them to create database objects, work with data, and modify data.

More on Amazon.com

Mentioned in questions and answers.

I know Scope_Identity(), Identity(), @@Identity, and Ident_Current all get the value of the identity column, but i would love to know the difference.

Part of the controversy i'm having is what do they mean by scope as applied to these functions above?

I would also love a simple example of different scenarios of using them?

Here is another good explanation from the book:

As for the difference between SCOPE_IDENTITY and @@IDENTITY, suppose that you have a stored procedure P1 with three statements:
- An INSERT that generates a new identity value
- A call to a stored procedure P2 that also has an INSERT statement that generates a new identity value
- A statement that queries the functions SCOPE_IDENTITY and @@IDENTITY The SCOPE_IDENTITY function will return the value generated by P1 (same session and scope). The @@IDENTITY function will return the value generated by P2 (same session irrespective of scope).

I saw this query somewhere -

SELECT *
FROM HR.Employees
WHERE country <> N'JAP';

What does that "N" mean ? I am not sure if this is valid only for SQL server.

Additional information taken from the book - http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059

If you write an expression that involves operands of different types, SQL Server will have to apply implicit conversion to align the types. Depending on the circumstances, implicit conversions can sometimes hurt performance. It is important to know the proper form of literals of different types and make sure you use the right ones. A classic example for using incorrect literal types is with Unicode character strings (NVARCHAR and NCHAR types).

The right form for a Unicode character string literal is to prefix the literal with a capital N and delimit the literal with single quotation marks; for example, N'literal'. For a regular character string literal, you just delimit the literal with single quotation marks; for example, 'literal'. It’s a very typical bad habit to specify a regular character string literal when the filtered column is of a Unicode type, as in the following example.

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = 'Davis';

Because the column and the literal have different types, SQL Server implicitly converts one operand’s type to the other. In this example, fortunately, SQL Server converts the literal’s type to the column’s type, so it can still efficiently rely on indexing. However, there may be cases where implicit conversion hurts performance. It is a best practice to use the proper form, like in the following.

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = N'Davis';

This question contains complete learning curve for Cpp from Beginner,*Intermediate* and advance which is very helpful.

Please suggest me same for Databases,to become a good Database Expert or a Good DBA. As there are may books and tutorials which to choose.

I found following books written by Itzik Ben-Gan very helpful:

Beginning:

  1. Microsoft SQL Server 2012 T-SQL Fundamentals http://www.amazon.com/Microsoft-Server-2012-T-SQL-Fundamentals/dp/0735658145

Advanced:

  1. Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 (MSPress, December 2012) http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059

  2. Inside Microsoft SQL Server 2008: T-SQL Programming (MSPress, 2009) http://www.amazon.com/Inside-Microsoft%C2%AE-Server%C2%AE-2008-Pro-Developer/dp/0735626022/ref=pd_sim_b_1?ie=UTF8&refRID=1ZFP1YGEB2QPZBZCBFZS

I'd say: learn the internals

http://www.amazon.com/Microsoft-SQL-Server-2012-Internals/dp/0735658560

From there you have a solid knowledge of querytuning, how the optmizer works and solid knowledge of how SQl Server works. Something every dba should know.

And the books of Iztik Ben Gan: http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030/ref=sr_1_sc_2?s=books&ie=UTF8&qid=1393572731&sr=1-2-spell&keywords=iztik+ben+gan