A community of more than 1,500,000 database professionals and growing

Stairway to XML: Level 3 - Working with Typed XML

Rob Sheldon from SQLServerCentral.com

You can enforce the validation of an XML data type, variable or column by associating it with an XML Schema Collection. SQL Server validates a typed XML value against the rules defined in the schema collection so that INSERT or UPDATE operations will succeed only if the value being inserted or updated is valid as per the rules defined in the Schema Collection. More »

Report Builder 3.0: Formatting the Elements in your Report

Additional Articles from SimpleTalk

There is a lot that can be done to make basic tabular reports more readable, using Microsoft's free Report Builder. Rob Sheldon continues his exploration of the power of this tool by showing how to format various elements within reports More »

Last week Andy Warren wrote a great article called revisiting what you know and it got quite a few interesting comments. The gist of the article is that many of us learn certain tenets and techniques because we have to, just in order for us to get a job done. We're focusing on being effective and not necessarily on doing something in the best way. So Andy's advice was that we should periodically revisit the things we believe in and see if the way we are doing things makes sense or if we should consider doing things differently.

In many ways I agree. I used to tell all junior DBAs working for me that no cursors or temp tables were allowed. It was an iron rule for them to work within in the SQL Server v6.5 days and they needed to abide by it. Then one of them saw me using a cursor to perform some administrative update and asked me why.

My answer was that it was a one time thing, the cursor worked and was quick and easy to develop, and it made the best solution. Of course I got queried as to why they couldn't write cursors in code that went into our products. My answer was that most of their code was run over and over and needed to be efficient. In general, cursors and temp tables weren't efficient and if there was another way around, we needed to code it that way.

They were skeptical, but they learned that I wasn't being hypocritical because over the next few months we had a few places where they were stumped with certain problems. We agreed a temp table solved some of them, and decided to implement it. I told them the rule still hadn't changed, and no temp tables would be allowed in other code. We could make exceptions, but it shouldn't be a tool on one of the lower shelves. This one should be a bit higher up, require the stepladder kept in my office, them to ask permission, etc.

I think that we should be effective in our jobs. Getting things done in a timely fashion is the most important thing, but we should also strive for some level of craftsmanship in our code as well. Throwing something together is sometimes necessary, but we can throw something together that has some stability. Something that is able to handle some stress or even just make a refactor easier at a later date. Or we can throw something together that wobbles like a child's bike assembled without wrenches.

Which would you prefer?

This editorial was originally published on Aug 5, 2007. It is being re-run as Steve is away on the SQL in the City US 2012 tour.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

I create the following 2 tables (#A and #B) and insert the data as shown into the

      -- respective tables

CREATE TABLE #A(S VARCHAR(10),R VARCHAR(10))

INSERT INTO #A
 VALUES('Alpha','D1234'),('beta','A1122'),('charlie','D1234'),('bravo','C1342'), ('Doug','B1964'),('harry','A1122')

CREATE TABLE #B(R VARCHAR(10),S INT)

INSERT INTO #B 
 VALUES('D1234',1001),('A1122',4001),('D1234',2001),('C1342',5001) ,('A1122',3001);

Next I execute the following T-SQL

SELECT tblA.S, tblA.R, subQuery.S
 FROM #A tblA
   OUTER APPLY (SELECT R, S
                FROM #B tblB
                WHERE tblA.R = tblB.R) subQuery;

The question is how many rows are returned when I execute the above SELECT statement?

By bitbucket

Think you know the answer? Click here, and find out if you are right.

This question is worth 2 points in this category: APPLY. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

Within SQL Server 2008 R2, there are multiple channels in the extended events architecture. What are those channels? (Choose 2. Each correct answer is a part of the solution.)

By Jason Brimhall

Answer:

• Admin and Debug
• Analytic and Operational

Explanation: SQL Server 2008 R2 Extended Event architecture has four channels. Each correct answer contains two of the channels. All other options were incorrect if any of the two options represented in the answer choice was invalid.

The correct channels are:

• Admin
• Debug
• Analytic
• Operational

Ref: http://msdn.microsoft.com/en-us/library/dd822788(v=sql.100).aspx

You can query for these channels through the DMVs sys.dm_xe_object_columns and sys.dm_xe_map_values. The following query demonstrates the possible channels in SQL 2008 R2

SELECT DISTINCT v.map_value AS Channel
	FROM sys.dm_xe_object_columns c
		INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name
			AND c.column_value = cast(v.map_key AS nvarchar)
	WHERE c.name = 'channel';

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

All database files growth

Rahul from SQLServerCentral.com

Get growth details of database files More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB? - I'm not a DBA but I've been tasked with this change for the simple fact we don't have a DBA...

SQL Server 2005 : Backups

t-logs - i have 2 db whose t-log backups occurs everyhour. but everyweekend the log file fills...needs to to be shrunk to...

Odd Filegroup backup behavior - We have a particularly large database that is partitioned across multiple filegroups. We have nightly file group backups on the...

SQL Server 2005 : Development

sqlmail timout connection pool - I am using an SQL Job to call a stored procedure which queries the database to get some email addresses...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Is it safe to Shrink Database file in SQL server 2005 - Hi All, I had a table (Size = 10GB) with Millions of records that was storing ShowXML plan through SQL Server Profiler...

SQL Server 2005 : SQL Server 2005 Integration Services

Accessing Object variable in an Execute SQL Task - Hello, I have created a variable in SSIS 2008 of type Object that returns a set of ids that exist in...

SQL Server 7,2000 : Backups

Auto Backup in remote server in sql server 2000 - Hello Experts, I want to do auto backup of my database into remote servers directly. But I am not getting the...

SQL Server 7,2000 : T-SQL

SUBSTRING And CHARINDEX to find start/end of string - Hi all, I have a big long text string that can vary in lengh and contrain audit information about data that...

Csv list in column to separate columns - Hi all, Can I ask please if I have a row and one column has a csv list in it. Is...

SQL Server 2008 : SQL Server 2008 - General

SQL Serevr Services - Hi All, Can we start and stop the SQL Server services without being the member of Adminstative group on local server...

Why does this take so long! - Hi Everybody This is my first posting in this forum, so please bear with me if I should get some thing...

SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete - I have a few SQL Server 2008R2 SP1 and SP2 as well as a couple SQL Server 2008 SP3 getting...

open transaction on none Editable table - Dear Experts, We have some tables that we call them LKs.( look up tables) It is obvious that we have no insert,...

how to write a query using stored procedure - Like to know more about stored procedure

Read nodes from XML to table - Hi everyone, I have xml structure as below: <row id="PDPD1134200001" xml:space="preserve"> <c2>100113</c2> <c3>KHR</c3> <c4>1</c4> <c5>TR</c5> <c6>00</c6> <c7>1</c7> <c8>21050</c8> <c9>B</c9> <c10>10049</c10> <c11>72.00</c11> <c13>

Reg: Windows Authentication - Hi In My Laptop i am using sqlserver 2008.i forget my 'sa' password. I tried windows authentication.But I am facing...

Heart Rate Monitor Watch, £8.99 (was £19.99) | Deal Trove. - Monitor the progress of your workout resolutions with this data collecting sports watch. This watch not only monitors your heart...

Robocopy false error code of 8 when executed from SQL - I am trying to call robocopy from a bat fie to move backups to a network server. This is working...

Practice exam for 70-451 - Can anyone recommend a practice exam for the 70-451? I usually use measureup but they don't appear to have one.

Trying to use Case Statement to get value into a GL Account with periods in the format - I am new to SQL and trying to migrate data into a GL account field which has the following format...

How can I find the first column having NULL values in a table - Hi Friends, I have a file reader function that will read the file and load the data into a temp table....

SQL Server 2008 : T-SQL (SS2K8)

Date Comparison Error - Dear All, Greetings!!!! Today I face a problem in Date Comparison please help me in out thanks in advance... problem mention below:- In...

Help with Count over days (but not per day) ... the total up until that day through the date range. - I am having an issue trying to wrap my head around this. I am trying to get the count of something...

search levels - Hello all, I'm wondering if it is possible to do the following. I have a table with locations in the world. All...

query to get dates for a number of weeks - I need a query that will produce rows of dates given a starting date and incrementing that date for a...

Rollup issues - I cannot figure out how to add columns or ordering to my query which has a Rollup without it changing...

Consolidate Overlapping Date Periods - Getting late in the day and I could use some support from those who are feeling fresh! :-) Suppose I have...

SQL Server 2008 : SQL Server Newbies

Query Help - Hi All I have 2 tables. Table 1 MasterDefects Field1: Department Field2: Process Field3: Type Field4: Defect Table 2 Results Field1: Department Field2: Process Field3: Type Field4: Defect Field5: ShiftID Field6: SizeID Field7: Amount Field8:...

SQL Server 2008 : Security (SS2K8)

View Records That Are Associated With a User Table - I would like to find a way to setup a user table with logins (UserID) in one column and a...

SQL Server 2008 : SQL Server 2008 High Availability

Replication - After setting up Replication (Transactional Publication with updatable Subscription) between two instances one instance being both Distributor and Publisher, with...

SQL Server 2008 : SQL Server 2008 Administration

Installing SP2 on SQL Server 2008R2 RTM (Standard) - Need to install the latest service pack on SQL Server 2008 R2 RTM (Standard Edition). Please let me know if...

Capacity Plan for Server ? - I have a scenario where I need to import data from a server to another one. The data transfer is...

DBCC CHECK DB ERROR - When we run DBCC CHECKDB on one of our production Server we get the below error.. Error:--The database could not be...

Is there a time limit for Maintenance Plan execution - Hi, We have a maintenance plan that performs a DBCC against all databases on the server before executing a backup of...

SQL server performance disgnostic tool - I am Sr. DBA in one of the leading bank and we would like to buy MS SQL server performance...

Reporting Services : Reporting Services

Can we add Z-axis (third vertical axis) to a chart in SSRS 2008 R2 - I have searched the web and SSRS documentation but can't seem to find any information whether SSRS allows adding Z-axis...