70-461 Work with Data

Question

I'm filling gaps in our 70-461 Exam Guide Querying Microsoft SQL Server 2012 and work with data has some gaps, where I cannot find appropriate links to Microsoft documentation.

I would appreciate if you could clarify the points for me and ideally points me to Microsoft articles.

1) implement logic which uses dynamic SQL and system metadata.

-- I'm ok with first part but I'm not entirely sure about system metadata? What exactly should I understand by that?

2) write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables;

I'm not able to find Microsoft documentation page that would specifically talk about derived tables... has anyone come across it?

3) determine what code may or may not execute based on the tables provided;

ok in this case I have no idea what they means.

4) use and understand different data access technologies;

Any links to Microsoft discuss it??

Many thanks for your time and input

Emil

Answer 1

FOR 2ND QUESTION

http://technet.microsoft.com/en-us/library/aa337504.aspx

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

---

Mudassar

Answer 2

Hi

Thanks for the links, although they mention derived tables I was more hoping fro a page that goes into more details and examples of T-SQL with derived tables (from Microsoft site), I think in this case I might just include a link to our page without Microsoft page to support it.

Take care

Emil

Answer 3

>1) implement logic which uses dynamic SQL and system metadata.

Reference blog: http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

Take a look at the logic:

A. It uses metadata from INFORMATION_SCHEMA.TABLES view (to get the list of tables)

B. It uses a cursor (loop) to process all tables

C. It constucts a dynamic SQL string and executes it

-- including all types of joins versus the use of derived tables;

If you take a query and put it into a wrapper ( SELECT query) as X  - you created a derived table X. For more sophistication, you can add column alias list:

( SELECT query) as X ( OrderDate, OrderQty, ProductNumber, CustomerID)

CTE is the new competition to derived table.

Reference:

http://www.sqlusa.com/bestpractices2005/derivedtable/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

Hi Kalman,

Thanks for your answer.

The way I read your reply is that information_schema.tables is part of system metadata? which sounds fine. I suspect 'system metadata' can be also called database metadata? not necessarily server metadata? 

Would system metadata be both database and server (instance) metadata?

I think I found the link I was after (I changed my google query from system metadata to system views) system views and I presume the most relevant category for 70-461 is information schema views

With q2. I think I won't find any links

q3. Still not sure...security? syntax?

Many thanks

Emil

Answer 5

The way I read your reply is that information_schema.tables is part of system metadata?

It's rather a view of it. The actual system tables are not directly visible.

I suspect 'system metadata' can be also called database metadata? not necessarily server metadata? 

Well, I guess that depends on the context, and I don't know yours, so I cannot comment further.

With q2. I think I won't find any links

This query features a derived table:

  SELECT col1, col2, colcnt
  FROM   (SELECT col1, col2, COUNT(*) AS colcnt
          FROM   tbl
          GROUP  BY col1, col2) AS x
  WHERE  colcnt > 3

q3. Still not sure...security? syntax?

Again, I have no idea of what the context may be. Can you be more specific?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Hi Erland,

I don't have anything specific in mind with my comments. The initial question was to find out if there are Microsoft pages (links) that can support specific exam requirements that I listed (taken from 'skills measured').

Regards

Emil

Answer 7

> I suspect 'system metadata' can be also called database metadata? not necessarily server metadata?

Database metadata is preferred use for system information related to a specific database, although in the right context system metadata understood as the same. In the old days, they were located in sys... (system tables). Nowadays, they are located in  system views (400!) which includes the sys. views and the INFORMATION_SCHEMA. views (ANSI). Strictly speaking system metadata covers server metadata as well such as sys.databases  or sys.servers system views. 

The "INFORMATION_SCHEMA" schema has 21 views and the "sys" schema has 379 views, altogether 400.

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 8

>Microsoft documentation page that would specifically talk about derived tables... has anyone come across it?

It is discussed under the FROM clause.

References:

BOL: "derived_table

Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query."

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

Add Derived Tables to Queries (Visual Database Tools)

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 9

>3) determine what code may or may not execute based on the tables provided;

For an INNER JOIN between two tables a FOREIGN KEY constraint is required. Similar consideration for LEFT OUTER JOIN & RIGHT OUTER JOIN. Example: Sales.SalesOrderDetail has an FK constraint to Sales.SalesOrderHeader, I can JOIN them.

If I want to INNER JOIN Sales.SalesOrderDetail with Purchasing.PurchaseOrderHeader, I cannot do it due to lack of FK constraint between them. The attempted INNER JOIN would not give a syntax error, but the result would be meaningless.

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 10

Hi Everyone,

Many thanks for all comments and your time.

We have updated our guide. Our goal is to provide Microsoft links that are useful but in these cases we haven't managed to find links to would specifically cover the requirement therefore we decided it is better for most of our visitor if we do not include Microsoft links and later on we will create our own article and include it.

Take care

Emil