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

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

Jeff Moden from SQLServerCentral.com

In this sequel to his first "Hierarchies on Steroids" article, SQL Server MVP Jeff Moden shows us how to build a pre-aggregated table that will answer most of the questions that you could ask of a typical hierarchy. Any bets on whether Santa is packin’ a Tally Table in his bag or not? More »

Day 6 of The OLAP Sprint

Simon E Doubt from SQLServerCentral.com

Day 6 brings security enhancements. More »

FileStream and FileTable in SQL Server 2012

Additional Articles from Database Journal

SQL Server 2012 enhanced the SQL Server 2008 FileStream data type by introducing FileTable, which lets an application integrate its storage and data management components to allow non-transactional access, and provide integrated SQL Server services. Arshad Ali explains how. More »

From the SQLServerCentral Blogs - Another Dive into Transaction Log File Forensics

Hugo Shebbeare from SQLServerCentral.com

 Over the past three years since I first took a look at the insides of a Transaction Log file, I have... More »

I really wish that we would see detailed analysis of failed systems, if for no other reasons than to avoid repeating the same mistakes in the future. I'd hope that technical people that were engaged in building systems that didn't work well would share their experiences, whether from development, deployment, administration, or even operations. I ran across a piece on the lessons of Orca, the web application that Mitt Romney's campaign used, or tried to use, to manage their operations.

It seems that there were a number of problems with this system, which is almost stunning. I'd think this is a well known process that includes a number of pieces of technology that are built into so many systems these days. Integration is never smooth, and the short time frame of an election campaigns doesn't leave a lot of time for testing, much of which apparently didn't get completed. The article talks about many of the same things that I've seen mentioned in the past when applications don't work as expected. A lack of training, a dearth of hardware, tooling that doesn't work, all of these have been reported for years in many software engineering journals and articles.

Perhaps more analysis won't help. I doubt that even a high profile failure would convince the manager of any internal software development project to spend more resources or lengthen their time line to prevent a problem with an application. Like most developers, managers are eternal optimists when it comes to software being completed, regardless of their past experience. They never seem to learn that pushing for faster releases, cutting features, and limiting testing will showcase the end product poorly for the customer.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 13.1MB WMV
• Watch the iPod Video Podcast - 18.0MB MP4
• Listen to the MP3 Audio Podcast - 3.7MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

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

Today's Question:

CREATE TABLE #QoTD 
(
  WithOutDefault int,  
  WithDefault varchar(25) DEFAULT 'default'
) 
--1
INSERT INTO #QoTD (WithOutDefault) VALUES (1) 
--2
INSERT INTO #QoTD (WithOutDefault, WithDefault) VALUES (2, NULL) 
--3
INSERT INTO #QoTD (WithOutDefault, WithDefault) VALUES (3,'DEFAULT') 
--4
INSERT INTO #QoTD (WithOutDefault, WithDefault) VALUES (4,DEFAULT) 

Which of the insert statements will use the default on column WithDefault? (select 2)

By Kenneth Fisher

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

This question is worth 1 point in this category: Defaults. 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.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

/*

When I execute this batch on SQL 2008 server, what will be the answer?

*/

DECLARE @i int = 64;

SET @i ^= 2;

SELECT @i AS Answer;

By Comfortably Numb

Answer: 66

Explanation: In the set command contains the compound operator: Exclusive Or.

1000000 XOR 10 = 1000010

Ref: http://msdn.microsoft.com/en-us/library/cc645922(SQL.105).aspx

» Discuss this question and answer on the forums

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!

Index-scripting UDF

ekkis from SQLServerCentral.com

this function will return the SQL required to re-create an index. you just provide it with the table name and index name you want. More »

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

SQL Server 2005 : Administering

Making databases highly available with SAN... - Hey folks, I am going to be setting up a new sql instance in the next while at work and I...

SQL job on startup - Hi All, How could I run a sql job or maintenance plan on server startup? Thanks

All Db's infromation of instance - Does any one know a simple query that would give following information about all databases on an insance? 1. Database Name 2....

ORMs and max dop option - Most of our queries are generated by an ORM (object relational mapper). Three weeks ago we put two new sql...

Windows Cluster Servers Migration - [font="Verdant"]Dear Friends, I need valuable suggestion to do the Servers Migration and SAN relocation in the below scenario: SQL Server...

new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower - Hi Guru's, Hope you can help. We have a new 64-bit Win 2003 SP2 server, with SQL Server 2005 SP3 installed. Running...

SQL Server 2005 : Business Intelligence

Average in ssrs 2008 - Hi, I want to find the average in my text box.i have my code(conversion) in my report properties(custom code) where i...

SQL Server 2005 : Development

Copy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql...

SQL Server 2005 : SQL Server 2005 General Discussion

How to solve this issue sqlserver.exe process is taking so much of memory [70%] - HI to all, How to solve this issue sqlserver.exe is taking so much of memory [70%] in task manager --->in process -->...

Using SSIS to export to MS Access - Hello people, I am using SSIS to export data from SQL Server 2005 to MS Access 07. I have an MS Access...

Is it "better" to specify the definition of a temporary table, or just define it on the fly? - The subject line says it all, but just to make it clearer... I could use a [b]CREATE TABLE #TempTable[/b] to define...

Try Catch with Bulk Insert - Hi All, I am having some trouble getting the following code to work, in 2005. I am trying to load a...

How to recover updated data in sql server - Hi, how to recover the updated datas in sql server 2005? Transaction block is not used at the time of Update...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Huge cost issue with Temp DB using 2008r2 sp2 - Hi Guys, I have an issue that is completely baffling I need a guru's guidance / help. so a little bit of...

SQL Server 2005 : SQL Server 2005 Integration Services

Is derived column better for performance of a package ? - I have a scenario where an excel have thousands of records with say 20 columns. In which their is a...

SQL Server 2005 : T-SQL (SS2K5)

Splitting Data by day - Hi All, I have an issue while splitting the data by day in the given period. The Function I am using is...

Dropping DF_ constraints - HELP!! I have a deployment script that is building tables and setting default values to a column. I have built...

WHERE Filter - Hi to all. I haven't posted for a while and it is now time to wear my sql cap again...

automate t-sql - I'm not an expert at T-SQL, so I hope someone can help me automate this. Currently, I'm doing this manually by...

SQL Server 2005 : SQL Server Newbies

ALLOW_PAGE_LOCKSand ALLOW_ROW_LOCKS - Hi, Online books don't explain these concepts and pros and cons of using this. cany anyone tell me about any technical...

SQL Server 7,2000 : Administration

Seibel Support - Hello, I've got users using an application called contract works that is written on Seibel. I have had reports of it...

Database was in Emergency mode, single user mode - Need to bring database online - Hello Everyone, We are using one SQL Server 2000 version, and now one database is Emergency mode and in single user...

What is extended proc sp_releaseschemalock - Does anyone have documentation or know what sp_releaseschemalock does? I had an issue that occurred last week when I was on...

SQL Server 7,2000 : SQL Server Agent

grant permissions to view SQL Server agent. - Hi all, I am not able to see the sql server agent. But, when I login as a sysadmin I am able...

SQL Server 2008 : SQL Server 2008 - General

Order by Date column - Hi, I have query to execute the output. It should be sorted with date also, but its sorting with only Seq_no. PFA - for...

Column Data Consolidation - Can someone help I have a peice of code that that i am trying to use to oull som information...

Failed Transaction Log Restore - Hello, Being new to SQL and trying to teach myself via the excellent tutorials on SQL Central, I created a test...

Index scan on foreign key reference - I have a table TabA with a column that is a foreign key to the primary key of table TabB. I...

SQL Server communication over subnets - Hi Folks, OK, the problem is that I am trying to configure two SQL Server 2005 machines to communiacte over two...

Connection issue after update SQL 2008 R2 to SP2 - Hallo, Some days ago we succesfully update Our SQL 2008 R2 failover cluster(2 nodes) to SQL 2008 R2 SP2. Now...

sp_send_dbmail error : Attachment file ***** is invalid - When I try to send and Attachment (@file_attachments ) with sp_send_dbmail, I receive this error: "Attachment file ***** is invalid" The file does exist...

Database Mirroring With Automatic Failover - Dear, I want to implement database mirroring with automatic failover option. Would it be a wise decision to implement with this...

Dependent Delete statements - i need to delete the records from a table with multiple conditions , like below first i am clearing non numeric...

Large object (LOB) data for table - I have an existing table for LOB data(it is used in production) that others in my org say cannot be...

Trace shuts sql service down - Greeting All, I've come across a server that, when I start a trace, within moments the SQL service shuts itself...

How to Creat SQL Server Data Base? - Hi This is joelmasan. I am living Florida.I don't know about the SQL . am i thinking SQL is a data...

SQL IO Performance - I'm running out of brain cells trying to figure out what I've overlooked. Hoping someone has a quick checklist of...

When was server last rebooted? - Is there a DMV or function that tells you when the physical server was last rebooted, as opposed to when...

Stored Procedure - I have a stored procedure and want to know after I run it is there away to get it to...

How to Skip Invalid XML - Dear All, I have a Prod table with a million on rows having an ntext column to store XML. I need...

How to flatten results from parent/children tables in view - I have a parent table and 2 children tables that store date ranges. I'm trying to create a view to...

Connection Manager Error - Hi all, I've created a very simple SSIS package where I'm trying to convert an excel spreadsheet into a flat file....

Configure sql profiler to show queries that are timing out - Hello, How can I configure sql profiler to show me the actual query that has timed out. Im getting a few...

Generate schedule table - I'm trying to generate a backup schedule table where I do FULL backups starting tonight and then do another FULL...

Auto Backup and Restore in SQL Server 2008 R2 - Dear, I have two servers such as A and B. A is my main server and B is my backup server. My purpose...

Error: 18456, Severity: 14, State: 11 - I have one user that is receiving this error on 5 instances out of the 8 they have access to....

Continuing career with SQL - Hello, I hav just joint this forum. I have to ask few things. I have worked with SQL 2000 and basic 2005.database design. I've...

Activity monitor loads slow - Hi, I have a very general question. Does anyone know why the activity monitor in SSMS 2008 and/or 2008R2 in a...

Query consumes high CPU and 285 Sec avg duration - Hi all, This service broker query consumes high CPU above 80% and ave duration is 285 sec. WAITFOR(RECEIVE conversation_handle, service_contract_name,...

change initial size of tempdb - I am seeing one of our drives are out of space. And the tempdb is on that drive, it used about...

can I map an LSN to a username or hostname? - I am exploring CDC and I found a way to map he LSN to a datetime using sys.fn_cdc_map_lsn_to_time ( lsn_value ), but...

SQL Server 2008 : T-SQL (SS2K8)

grouping rows - dear friends, I need to group records. the sample data below is result of collected data from different tables. If...

'Select Into' logs - Hi all, I need to write a stored procedure that moves large amounts of old data on a nightly basis to...

How to export XML query results to a file using Sql Server 2008 R2 From Management Studio - Hi All, I need to dump the XML query results into a file using the stored procedure below without using...

How to find second maximum record - Pls give me a query for to select second maximum of the result set. a,b is column names. insert (a,b)values('2','2012-12-21') insert (a,b)values('2','2012-12-23') insert (a,b)values('3','2012-11-23') insert...

Leading Zeros in alpha-numeric string - previous solutions didn't work - hi, need help removing leading zeros from alpha-numeric column: Before After 12345 = 12345 00123 = 123 10000 = 10000 A1234 = A1234 12A34 = 12A34 0A123 = 0A123 000A1 = 000A1 00D21 = 00D21 (ISNUMERIC will...

How to see if one column in a table matches a column in another table, when both tables are filtered with a WHERE clause - Hi - I have two tables in the same database. Table1 has a structure like this: AID IsStaffTask 1 0 2 1 3 1 4 0 Table2...

merge a result set with a table but update according to existing values in target - I have a table sectioninstructor Sectionid, Instructorid, Instructorrole ----------------------------------------------------------------------- Instructorrole (1 for primary instructor, 2 for secondary instructor) Note I want to make sure that each...

Loop through multiple Linked servers in Query - I have a challenge. One of my jobs is to produce reports on data found in 7 different databases (and database...

Exercises in t-SQL - I'm in the process of setting up a training environment here, it's a mechanism for me to bring high flyers...

Doing something like an Excel FillDown function in SQL looking for suggestions??? - I have a recordset that returns the following data: [code="plain"] ItemNumber DisplaySeqNum 10 1 2 3 4 20 5 6 7 8 30 9 10 11 [/code] It's like an invoice sheet where there's multiple parts to...

No record count in PIVOT - Hi, I have the following query to count the number of employees per costcenter using the pivot command. The @Columns parameter declares...

SQL Server 2008 : SQL Server Newbies

Memory Architecture - Hi All After scurraging through the internet trying to understand the SQL Server buffer pool, I understand the following but I...

shifting sql server from one system to another - Hi all, In our organisation we are shifting sql server to another system. we are done with that but we...

Improving intentionally denormalized table - I had a job interview about two weeks ago for a position for prviding support to teams of public health...

Unable to Rebuild or Reorganize an Index - I was hoping to rebuild all indexes on one of our SQL Server 2008 databases over the weekend. The database...

Can't restore transaction logs - Hi Everyone, I cannot restore the transaction logs after restoring the full backup. It shows the transaction log is too early to...

SQL Server 2008 : Security (SS2K8)

windows user is created and unable to connect to sqlserver - HI to all , windows user is created and unable to connect to sqlserver management studio

Error while logging Performance Monitor counter to sql server - I am trying to do following task and I donot know it is possible in this setup or not? I am...

SQL Server 2008 : SQL Server 2008 High Availability

Mount Point quick question - Hi, I am working on the design of a MS-SQL 2012 failover cluster, running on top of Windows 2008. I am...

SQL Server 2008 : SQL Server 2008 Administration

Seaparating Data files and Log Files - Hi Folks, I am facing an issue which I thought I understood but looks like reading and implementing a strategy are...

Update stats job failed. - For the first time our update stats job failed, the job runs at 5am in the morning when no one...

Unable to reinstall Sql Server 2008 on Windows 7 - I am trying to reinstall Sql 2008 R2 on my machine as I was hit with a virus. I have...

What would be the best RAID configuration for this hardware? - We have a brand new DELL MD1200 with 12-spindles - These are 15Krpm, 600GB SAS drives The server this is going to...

Wait Types concern - Hi Experts, In one of our DW servers the performance is very slow that a simple select statment is taking hours....

Erroneous results on last_execution_time, execution_count obtained from DMV sys.dm_exec_procedure_stats - When I run the following query to get the last execution time and execution count of a stored procedure in...

SQL express 2008 R2 - DB size - I am using express edition of SQL 2008 R2. As per the microsoft sites we can have max DB size...

Programming : General

Count primary key violations on a table - Hi, I am relatively new to SQL Server. I have an app that frequently inserts values with the same key to...

Execution Plan Reuse - We have SPs that take several parameters; when I check the plan cache using ... FROM sys.dm_exec_cached_plans AS ECP CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS ESQL ... I...

SQLServerCentral.com : Anything that is NOT about SQL!

Is there any good database magazine out there? - Hi there, can you recommend a database magazine that covers all database environments (MySQL, MSSQL, NoSQL, everything...? We had a good bi-monthly...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services 2005 Development

Difference in Layout and display - Problem statement: In my Report, the text boxes are not lining up in Internet Explorer as I have them in...

Data Warehousing : Integration Services

ErrorHandler problem - I'm hoping that someone can point me in the right direction with my error handling problem which is now seriojusly...

Warning: Could not open global shared memory to communicate with performance DLL - I'm a Local Admin on this Database Server but I get the following warning? [SSIS.Pipeline] Warning: Warning: Could not open global...