Stairway to XML: Level 9 - Creating XML-Based Functions

Rob Sheldon from SQLServerCentral.com

In this Level, we’ll look at how to use XML methods within user-defined functions to return XML fragments and values from your target XML instance.  More »

SQL Server 2008 Build List

Steve Jones from SQLServerCentral.com

The list of builds for SQL Server 2008 updated SP3. More »

SQL Server Performance Tuning – Index Tuning

Additional Articles from SQL Server Performance

In earlier installments of this series we looked at T-SQL Performance optimizations along with different T-SQL practices, we can now turn our attention to the second part of this series which is index Tuning More »

From the SQLServerCentral Blogs - Audit Configuration Changes

Jason Brimhall from SQLServerCentral.com

Do you know the last time a Server Property was changed on your instances of SQL Server? Are you wondering when... More »

Google does some amazing things. In many ways I like the company and their emphasis on data. I'm not sure I'd want to work there or fit into the culture, but the company tackles some problems that could really change many of our daily lives. They experiment with many projects, one of which is the driverless car project. I'm particularly interested in seeing seeing how that might influence the world. 

One of their other projects seems more ambitious. The Knowledge Graph project aims to use a database of everything in the world (according to this piece) and provide even more information to people using the Google search engine. In addition to search results, the idea is that you can get other facts and information about the topic you are searching for.

Whether it works well or not, it's certainly an interesting idea. Can we build engines that can divine the context of what we are looking for? If we can do this often enough, then the idea of interactive computers, like the ones on Star Trek, might be closer than we think. Perhaps there's a way for us to actually mine the data we have about a customer, or a situation, and present results that are more relevant and useful. Perhaps we can build software that can do more than just automate tasks and can help us better analyze information with new ideas or concepts as it interacts with us.

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 - 16.9MB WMV
• Watch the iPod Video Podcast - 14.6MB MP4
• Listen to the MP3 Audio Podcast - 3.1MB 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:

In SQL Server, large-value data types, because of their size, are sometimes stored separately from the normal row data on special purpose pages. Is data compression available for the data that is stored separately?

By Chirag Patel

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

This question is worth 1 point in this category: Data Compression. 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 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

How many rows get inserted into the table by this code?

CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));

ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);

INSERT INTO Test_Table VALUES (0, 'row 1');
INSERT INTO Test_Table VALUES (1, 'row 2');
INSERT INTO Test_Table VALUES (2, 'row 3');
INSERT INTO Test_Table VALUES (1, 'row 4');
INSERT INTO Test_Table VALUES (NULL, 'row 5');

SELECT * FROM Test_Table;

DROP TABLE Test_Table;

By Thomas Kovarik

Answer: 1

Explanation: Correct answer: 1 row is inserted into the table. (It has Comment 'row 2'.)

Reason: The row with Comment 'row 1' is rejected by database engine because CHECK constraint "PK_check" requires PK != 0

Reference: http://technet.microsoft.com/en-us/library/ms190273(v=SQL.90).aspx The row with Comment 'row 3' is rejected by database engine because decimal integer 2 (which is binary 10) results in an attempt to insert 1 (0 was truncated because it is the least significant digit), which was already inserted; therefore it is a duplicate value which is disallowed in the Primary Key column. Reference: http://technet.microsoft.com/en-US/library/ms181043(v=SQL.90).aspx The row with Comment 'row 4' is rejected by database engine because PK = 1 is a duplicate value which is disallowed in the Primary Key column Reference: http://technet.microsoft.com/en-US/library/ms181043(v=SQL.90).aspx The row with Comment 'row 5' is rejected by database engine because NULL is disallowed in the Primary Key column Reference: http://technet.microsoft.com/en-US/library/ms181043(v=SQL.90).aspx Effectively, these CREATE and ALTER statements require a table to have at most 1 row!

» Discuss this question and answer on the forums

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.

Open transactions on a database

kyle.eldridge from SQLServerCentral.com

This script will show you all open transactions from any query windows. More »

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

SQL Server 2005 : Administering

Am i interpreting this correctly for PAGEIOLATCH wait type? - I am using below query to find total wait time for wait type 'PAGEIOLATCH_SH' . There is nothing else running on...

SQL 2008 R2 - Can't see services in SSCM or status of server in SSMS - I was getting a WMI error launching SSCM so I followed the advice of this article and ran the mofcomp...

DATABASE Ownership - I need to change ownership of several production databases. Can I make the change without causing problems? The account that...

One SPID - Split into 127? - My query is paralleled , when i query sys.sysprocesses i see 127 records for that same spid with CXPAcket wait types....

Delete with TABLOCK - [b]To delete rows in a heap and deallocate pages, use one of the following methods. Specify the TABLOCK hint in the...

very high CX packet waits - i have noticed very high CXPacket waits on one of our dataware house server. All these queries are just select...

about storing sql script in a table? - Hi Can i store the database creation script in a table call the script in stored procedure to create new...

SQLCMD mode in a job step? - Is there anyway to get SQLCMD mode to run in a job step? I find it a quick method to execute...

SQL Server 2005 : Backups

Maintenance plan delete sometimes fails - I have a strange issue... in addition to our normal backups, there are a bunch of databases on our production...

SQL Server 2005 : Business Intelligence

Custom DLL deployment - Hi, I have two SSIS packages which uses the Custom DLL in a script task. This Custom DLL has code which...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Subtraction - Seperate rows - Hi I currently use the following statement: SELECT * FROM Month1_Month2_Diff_Detail ORDER BY Name, Order, Month and receive the following back: Name Order Month Detail ...

SQL Server 2005 : SQL Server 2005 Security

Best way to monitor or audit SQL - Hello, I'm looking for any thoughts on a good monitoring tool for SQL server 2005. Although the users are safely locked...

Simple Auditing Setup - I want to setup an auduting system witout buying any 3rd party tools where in i am looking to get...

SQL Server 2005 : SQL Server 2005 Integration Services

Creating Header AND Footer to a flat file destination file - Hi Guys, I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement....

SQL Server 2005 : T-SQL (SS2K5)

plz i need help!!!! - i want the entry for adding 2 value but i want show in the third cloumn all result with null...

SQL Server 2005 : SQL Server Newbies

Trigger After Update - Values from inserted table - Hi, I am trying to create a table with references to the updates of a specific table. So simply - TableA.KeyField TableA.Description When TableA.KeyField is...

SQL Server 7,2000 : Administration

DTS Package - TestServer: SQL Server 2000 ProdServer: SQL Server 2000 Client on my machine: Sql Server Management Studio 2008. [b]Goal[/b]: Move DTS Package from Test...

SQL Server 7,2000 : General

address management software - Does anyone know of any address management software that is compliant with sql server 2000, i.e. for record duplication and...

SQL Server 2008 : SQL Server 2008 - General

MDS Import Data - Hi all, I created model ,entity,attributes successfully in MDS.I want to know how to load master data into MDS(sql server...

Unique Constraint on existing table with duplicate data - Hi, I have a table called GN_CarParkingAreas Fields: 1. CarParkId int PK 2. CarParkNo varchar(10) not null 3. DevelopmentID int not null 4. Notes varchar(100)...

Corrupted Database - Issue: The database in corrupt status So far Tried: ALTER DATABASE @database SET EMERGENCY; GO ALTER DATABASE @database SET SINGLE_USER; GO DBCC CHECKDB (@database, REPAIR_ALLOW_DATA_LOSS) WITH...

Shrinking log is bad? - Hi All, I am aware that shrinking leads to fragmentation and doing it often is a very bad practice. Shrinking mdf...

if Updated my table i want change status - Hi, I was updated my table ,then i want change status_flg in updated table is it possiable ? help me ,, Regards Arjun

Update All columns in table - HI , I have table EMP(source), in this i have 5 columns we don't know which column was updated in the source but...

Linked Server Problem - Dear All, I have configured a linked server between sql server 2008 r2 and oracle 11g. Everything is successfully established. But...

Can't shrink physical logfile size. - Hi, Under Sql 2008 I really need to shrink the physical size of the logfile. The database uses full logging...

Query processor ran out of internal resources and could not produce a query plan. - I'm getting this error message on a query that I've put together. It queries Infor's Smartstream product for general ledger...

sql server datapage storage content - hi all, Is the Data Page contains the data from a single table or multiple tables data.

Can concurrent INSERTs mess up a UNION? - Can SQL Server guarantee that if I issue a command like: [code="sql"] INSERT INTO T(...) SELECT t.a, t.b, 1 FROM sometable t UNION SELECT -t.a, t.b,...

TIME/DATE Datatypes Accuracy. - Hi all, What is the meaning of "Accuracy of 100 nonseconds" in case TIME data type in sql server 2008. And...

SQL SERVER CPU Usage 100 % even at no traffic time - HI, SQL SERVER CPU Usage shoots up to 100 % even at a time when user is not accessing application or any...

An easy way to synchronize data - We are about to begin User Acceptance Testing. The business users want to use a copy of "live" production data...

Import Excel using OPENROWSET only works for SA - In our development environment web application we're uploading excel files to the SQL server and importing them using openrowset. This...

Foreign Keys,Deadlocks, Partioning,OLTP ......what is the best setup? - Hi All I am a DBA and I have just started a new job working on a large scale OLTP system....

select and condition column+column - Hello, is any possible idea for faster performance ? I have two columns date and sequence, so i need to join...

getting total disk space - i want to find the total drive space in server instaed of free space , can any one send the script...

problems to copy out SSID packages to file - Hello from germany! I have a problem with copying a SSIS in MSDB out to as a file stored as a...

Unable to open Alerts in SQL Server Agent / Cannot view properties - I am unable to open Alerts in SQL Server Agent and Cannot view the properties using GUI. Although I am...

Failure to calculate super-latch promotion threshold - I saw this error message in the error log of one of my SQL Server 2008 R2 servers today (with...

Server drop connection error - Error: 17886, Severity: 20, State: 1. The server will drop the connection, because the client driver has sent multiple requests...

SQL 2008 R2 Installation - Missing Instance Features - Hey everyone. I'm trying to install a Database Server using SQL Server 2008 R2 Enterprise edition on Windows Server 2008...

adding login with renamed AD account - I am trying to add a login to a SQL server 2008 R2 SP1 active/passive server on windows 2008 R2...

The log for database is not available; Database was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. - Testing and Production db's have lost connection to the translog 2 days in a row now. Today, my prod db...

SQL Server 2008 : T-SQL (SS2K8)

Need to optimize query - HI I need to optimize below query ... currently I do not have DDL and index info with me..find below SP...

group wise sum query - I have a table in the below format. S.no Travel Expense Stay Expense Mis Expens Date Employee 1 10 15 0 19/Jan/2012 James 2 5 5 5...

Build Drive Path of File - I need to build the drive path in the select query??? Tables: Drive DriveID (int) DriveLetter char(1) Directory DirectoryID (int) DriveID (int, fk) ...

Is there a way to force recompilation of already running query based on plan handle - Hi, Is there any way to force the recompilation of an already running query based on plan handle? Thanks.

Query tanking longer when created as SP - Hello Everyone, Currently I am having an issue, I did my SQL Script and it takes a decent amount of time...

T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE - PLEASE HELP ME WHO EVER CAN WRITE MORE EFFICIENT T-SQL THAT ANSWERS THE QUESTION. THIS IS WHEN WE DONT KNOW THE...

T-SQL Help with a loop (I believe) - Here is my very basic code below. The results are below that. What I need is a query that reads...

Conversion - How do i convert the below date from [b]Mon Jan 28 11:03:06 EST 2013[/b] To [b]2013-01-28 11:03:06[/b] Thanks for your help in advance

Order of JOIN in multiple table joins - I want to know how order of joins executed in sql server. Suppose i have used 3 tables in join. table1...

what to place in Stored Proc sysname parameter - I have a table that my company uses for logging. One of the columns is declared as: event (sysname(nvarchar(128)),not null) We...

Inactive Extended Event Sessions - To check if an extended event session with a name exists I use the following statement: [code="sql"]SELECT 1 FROM sys.dm_xe_sessions WHERE...

Bulk Insert from file to table - Hi, I have .dat file with following format data. 1,2,3, 4,5,6, 7,8,9, i want load this format file into sql table using bulk insert...

unique combination of concatenated columns - Hi, All. Could you, please, help me with sql query ? For example, if I have a table1 like the following (fields/values...

Help on how to count guests in hotel every day - I have a table keeps guest records in a hotel. For each guest I have check in date and check...

Need a backup script to get backup history status for the latest backup from all linked sql servers - Hi Guys, I am actually in need of a t-sql or a powershell script that will help me get the latest...

CASE STATEMENT in WHERE CLAUSE - [font="Arial"][color="#333333"][size="2"]I am trying to add a CASE to my WHERE clause that is a little more complex than I normally...

SQL Server 2008 : Working with Oracle

how to view a Oracle stored procedure result set in sql developer? - Hi, Sorry coming from the SQL world something as easy as highlighting the sp name and viewing the results in...

Cannot connect oracle 8 client on Windows 7 to and oracle database. - Hey Guys, I am having a problem connecting to an oracle database using oracle8 client. It is installed on windows7. I have the...

SQL Server 2008 : SQL Server Newbies

changing a sql 2005 developer license to production - I have a windows 2003 server running sql 2005 with a developer license installed. It's now in production. I have...

when to defragment indexes - Hi All, I understand that indexes need to be defragmented when they get fragmented, however must one defragment an index even if...

Pivot results not as expected - I have a select statement as below: SELECT Institution_Number, Attached_Account_Number, [1], [2], [3] FROM ( SELECT CARD_Attached_Acct.Institution_Number, CARD_Attached_Acct.Card_Number, CARD_Attached_Acct.Portfolio, CARD_Attached_Acct.Attached_Account_Type, CARD_Attached_Acct.Attached_Account_Number, Inquiry_Name_To_DDA.Name_ID, Inquiry_Name_To_DDA.Name_Line FRO

Connect to a remote sql server using stunnel - Hello everyone, I am trying to connect to a remote sql server using stunnel. I have already configure the stunnel.conf file...

Stored procedure very slow execution - Hello, Please help me, I'm desperate. I have an old SQL2000 (SP4) - and no, I can't upgrade it, is in my...

Secondary indexes in SQL Server. - Hi all, What are secondary indexes in SQL Server and why are they needed.

Database in Recovery - Hi All I had a situation where my SQL Server was stopped in the middle of a long running transaction, I...

SQL Server login Server Roles needed for Backup Exec User - SQL 2008r2 ---- Windows Server 2008 Standard I want to create a new SQL server login to allow Backup Exec to pull...

SQL Server 2008 : Security (SS2K8)

Data reencryption - Hi everyone. At what point does the actual data get reencrypted by SQL Server? Does it happen when I regenerate a...

Script Logins - Hi All Is there any way to script out all the Logins on a SQL Server instance, including server level privileges,...

Encryption basics. - Hi all. I am researching the feasability of encrypting a few columns in one of our databases. I have done...

SQL Server 2008 : SQL Server 2008 High Availability

History of Transactional Replication (TR) latency - Just wondering- are there some tables or DMVs that I can use to get TR latency history (let say, 2...

Mirroring Log Issue - Hi All, I have a db with mirroing in place and log is growing about 49GB.....Log is growing since log backup...

replica of prod server - Hi, I am trying to create a replica of production server, so that we can divert some traffic. I am thinking...

SQL Server Clustering Questions - Hi, Can someone provide few important interview questions on clustering with answers?

SQL Server 2008 : SQL Server 2008 Administration

Changing database to Read_Write - I've a read-only database and I'd like to change it to read_write but the following script never seems to complete....

Red Gate SQL Monitoring fails - I just started a new DBA job and was introduced to SQL Monitoring from Red Gate. The program is not...

checkdb error - Hi experts, CHECKDB threw error Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is...

Questions on VM & Disk configuration - hi, I am a developer and given dba responiblities by default. I have 3 sql servers that are in virtualized...

Programming : General

Add SQL Objects to TFS/ Version Control - I work for a small company and we are trying to get all database objects in to TFS. Our dot...

SQLServerCentral.com : Anything that is NOT about SQL!

Web application design query - Hello, I am new to web application designing and using .net (MS Visual Studio 2010) to design an application. The issue I...

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

Another SSRS Conditional Formatting Question - Afternoon all... now ive been using SSRS for about 6 months and am becoming used to all the functions within it...

report builder expression - hello everyone - The user just happened to find a bug in my report. Here’s what happened : The below expression was...

how to get the source code from .rdl file - Hi , i have a rdl file that is displaying report, i want the source code of that rdl file.(since i...

Reporting Services : Reporting Services 2005 Development

Select all records in current month - I have to change a prior report which included all records in which DateFinished is in the last 2 weeks...

Database Design : Design Ideas and Questions

Table with one row: Bad idea or not? - I was thinking of using a table with one row to keep configuration data and information about the server the...

Data Warehousing : Integration Services

what to do when package suspends when run as a job - Hello, I'm working on a package that does these things: 1. moves a file on a network to a server on that...

SSRS Report Server destination - Is it possible to use the Reporting Services Report server as a SSIS destination? In the SSRS Report manager it's possible...

SSIS Mappings Files - can SSIS XML mapping files be Coustomized? Has any one out there tried it.

Data Warehousing : Data Transformation Services (DTS)

How do I preserve legacy DTS packages in sql 2005 after uninstall/install - Hi I have to uninstall sql server 2005 enterprise edition and then install sql server 2005 standard edition. I have 74...