In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor logo Monitor the data you care about the most
SQL Monitor's customisable monitoring and alerting keeps you up to date with SQL Server performance, wherever you are. Free trial.
 
SQL Developer Bundle logo 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
Lesson two has now been released! Read ‘Beating Backup Corruption’ and learn from the very best. Read now.

In This Issue

Exploring the Varbinary Type

A brief look at the Varbinary data type and its uses in SQL Server for beginners. More »


Free eBook: Inside the SQL Server Query Optimizer

This free ebook from Red Gate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. More »


Evaluating Policies on Multiple SQL Server Instances using Central Management Server

you have created a SQL Server Policy to check database recovery models. Now you need to check the databases on all of your SQL Server instances. In this tip we will show how you can evaluate a SQL Server policy against multiple instances.  More »


From the SQLServerCentral Blogs - SSRS 101 – Creating a SQL Server Reporting Services Project

This is the second installment in this series of SQL Server Reporting Services 2012 Beginner posts. You can read the... More »


Editorial - A Computer Goes to College

A college education is something many parents desire for their children. It's almost expected that all school children in the US will grow up aiming to attend some university. Not all kids will follow this path, and whether it's the best choice for most of them is debatable. Apparently IBM thinks it's little one is ready to go as well.

Watson supercomputer is being sent to Rensselaer Polytechnic Institute in New York to go to school. In it's quest to defeat the Jeopardy champions, this type of computer was fed a tremendous amount of information from general sources. A clone of Watson received specialized medical data to help doctors diagnose patients better. This last incarnation of the computer system will be fed information from various researchers in different fields at RPI. What will come out of this, experiment nobody knows.

The Watson system going to RPI seems modest in size, with 15TB of storage reported. That seems small. The specs posted for the Jeopardy system include 2880 cores and 16TB of RAM. That's not the size of system many of us every get to work with. The software, called DeepQA, is the core of the system and allows it to process vast amounts of data and make connections between the data to answer questions.

2880 cores is a lot, as is 16TB of RAM, but there are SQL Server installations that are getting close, with 2TB of RAM. Some Parallel Data Warehouse installations with hundreds of cores. I can foresee a day when Watson-sized systems are more common. Perhaps we'll even get a Watson version that helps us better analyze the data we work with today, a version that works closely with the DBA-turned-data-analyst to extract information from the petabytes of storage we'll have accumulated.

» 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.

Everyday Jones

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


Question of the Day

Today's Question:

I run this code:

CREATE VIEW MyView 
AS
 SELECT *
  FROM dbo.MyTable
GO

SELECT * FROM dbo.MyView
I get back the one column in MyTable in my result set. I then execute:
ALTER TABLE MyTable
  ADD x2 INT
GO

UPDATE MyTable
 SET x2 = 1
GO

SELECT * FROM dbo.MyView

However I only receive the one column with the same result set as in the first code block above. What do I need to run to ensure I get all the data back from the view?

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

This question is worth 1 point in this category: T-SQL. 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 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.


Yesterday's Question of the Day

Which of the following query will provide different answer? 

Statement A. SELECT (45 * (9 - 5 + 8)- 36 / 4)

Statement B. SELECT (45 * (9 - (5 + 8))- 36 / 4)

Statement C. SELECT (45 * (9 - 5 + 8)- (36 / 4))

Statement D . SELECT (45 * ((9 - 5) + 8)- 36 / 4)

Answer: Statement B

Explanation: The answer for all statements except Statement B is 531. The result of statement B is -189. This shows how brackets changes the precedence of computing.

Ref: http://msdn.microsoft.com/en-us/library/ms190276%28v=SQL.90%29.aspx

» Discuss this question and answer on the forums

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.


Featured Script

T-SQL Bulk Insert

T-SQL that allows you to import tab delimited text file into correct table based on file name. More »


Database Pros Who Need Your Help!

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

SQL Server 2005 : Administering

script executing in distribution database - Hi, We havent set the replication still how distribution database get created. I have found below script is executing in distrubution database. (@param...

A severe error occurred on current operation. How bad is corruption fix? - Rookie mistake, posted in SQL 2005 instead of 2008. Don't see how to recall/delete post. Sorry

Idera SQL DM - Hi, I'm currently using Idera SQL DM to pull back the following metric: - Disk Reads / Sec Is there any reason why this...

Comparing two rows - Dear, I want to compare string type values between two rows within a table. Pls help me to do this.

Ruuning Update Stats - Greeting All: Using SQL Server 2005 (9.00.5000.00), but moving to SQL Server 2008 in 4 months. I am in the process of...

database mail sending error - hi, i am getting following error Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81 Mail not queued. Database Mail is...

How can I loop sp_help through for all tables or objects? - I want to create a text file summary of the schematics for a database. Essentially, I want to have what...

Failover Alerts - Hello everyone ... i have been searching and searching for a good way to email alert me when my sql cluster...

MODEL transaction log growing - Hello All, Our support team has recently received 3 calls from our customer stating the transaction log for the MODEL database...

SQL Server 2005 : Business Intelligence

SSRS Reports deploy in Reporting Server Manager - Hi Friends, I have a set of reports developed in BIDS env and now my SQL server has been reinstalled...

Unable to Connect Reporting Services (2008R2) in SSMS - Dear All, I am facing some problem in connecting the Reporting Services (2008R2). it is throwing the following error. [size="2"][color=#FF0000] TITLE: Connect to Server ------------------------------ Cannot...

Replace function in ssrs - Hi All, am using a Stored Procedure in my report, for that I need to pass 3 parameter @string, @storeKey, @language. when...

SQL Server 2005 : SQL Server 2005 General Discussion

MS SQL Server 2005 Standard x64 OEM - media cd pack kit - Hello, my media OEM CD with MS SQL Server 2005 Standard x64 OEM was damaged. I contacted with MS and DELL...

Can DatabaseMail attach a formatted Excel file - my query USE [AP_ECC] GO /****** Object: StoredProcedure [dbo].[ap_ecc_Mail_for_statuslog] Script Date: 02/06/2013 11:03:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[ap_ecc_Mail_for_statuslog] as EXEC msdb.dbo.sp_send_dbmail @recipients=N'internal@zeroone.com' ,@body='Hi Team,...

SP4 error - I am trying to install SQL 2005 SP4 and it keep failing. The SQL tries to restart itself after that....

SQL Server 2005 : SQL Server 2005 Security

Kerberos Not Set Up On Server - Hello Another party set up the Server and Databases. First the Server was set up Windows Server 2008 SP1 Then the Databases were...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Loading All Trace files - I was wondering if someone had already put the SQL together to load all trace files in a folder, into...

SQL Server 2005 : SQL Server 2005 Integration Services

Dialogue box from SSIS's script task throw error when called through SSMS Job - I have got dialogue box added to SSIS package,through script at the end,that populates when process is completed. But it throws...

SQL Server 2005 : T-SQL (SS2K5)

Alternate to Joins - folks, i have 10 to 20 tables to be joined to get required results but i heard joins on so many...

Problem with Full Text Searching - I have one table in sql server say example Table Name candidates Test table contains some columns cid , cname,c_resume cid in Int...

SQL Server 7,2000 : Backups

How to take backup & restore for one table? - How to take backup & restore for one table?

SQL Server 7,2000 : Data Corruption

Export DB - Hi, Can someone please clarify my below doubt I have a database on which 2 tables are corrupted, I dont have good...

SQL Server 7,2000 : T-SQL

Count Decimal Places - i'm trying to count the number of decimal places in a field. e.g. mynumber decimal 9 (18,9) Len(mynumber) result = 11 I've tried...

SQL Server 2008 : SQL Server 2008 - General

database realted - is there any way i can find out , from which machine changes been done on particular database ( suppose databasename abc)

Could not login after changing domain account. - Hi all, I installed a SQL instance on my computer. Here is info: Instance name: DTDUNG\MSSQLSERVER Login Mode: Window Authentication Domain user: T\dtdung After that,...

backup history details - backup history details through command ? easy way

Reg: spotlight - Hi every one, Is spot light offering the licenced per instance like others or its licensed per server so that...

How can i read from XML string in SQL 2008 - Dears, i want read XML string in SQL 2008.Here the XML: <InputColl> <WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ConfirmServiceFeesPayment"> <Texts> <T Name="Notes" Value="" /> <T Name="WorkItemID"...

Generate Sequence Day and number Using Type Expression - Hi i am trying to generate record like this Day Week 1 1 1 2 1 3 1 4 1 5 1 6 1 7 2 1 2 2 2 3 2 4 2 5 2 6 2 7 3 1 3 2 3 3 3 4 3 5 3 6 3 7 4 1 4 2 4 3 4 4 4 5 4 6 4 7 For this i write this expression [quote];with WeekTable(i,Week,Day,NoWeeK,NoDay,Total) as ( select 1,...

Automation of sql server management studio settings - Hi, We are using sql server management studio 2008. After installing management studio to each computer if we want to...

Is it possible to use a case statement inside a cursor? - I have to update a group of records but I need to make some comparison before actually making the update....

query tuning (PFA, execution plan and indexed view)- need help to enhance performance - well, guys using the following query also created indexed view and statistics as advised by DTA -------------------------------------------------------------------------------------------- declare @FromDate datetime = '1-march-2012' declare @ToDate...

Staging table population - advice needed - I have a process like this: STEP 1: Data files get loaded into a staging database. STEP 2: Phonetic Keys table get...

Clustered Index affecting DELETE - Hi, I have a table with 31 Million rows in, keyed off an INT IDENTITY column. This column is also used...

Store procedure variable - Below is statement for a table select * from myserver.mydatabase.dbo.order I want to declare a path to modify above statement but got an...

SQL is the first Language i am learning..how do i practice it ?? Pls Guide me.. - I am from Non IT background. Want to learn SQL. I am reffering sql for dummies book. i want to practice...

conditionnal auto-increment - Hi everybody, I am an intern in charge of creating a "quality dept" database for an automotive parts manufacturer. I Have...

Performance Issue - understanding query plans - Hi, We have a query with a bad performance issue. (28 hours to run). I have been looking at the estimated...

SQL Function takes more than 2 hours to return a table.. - I have a function with cursors which returns a table in SQL Server 2008. On executing the query which calls...

how to find result for this table - PaymentHistoryID paymentID Amount receivedType Reason 1 11 5000.00 Dr ASD 2 12 4000.00 Cr BG 3 11 8000.00 Cr BG 4 11 3000.00 Dr SD 5 11 3000.00 Cr ASD 6 13 50000.00 Cr ASD 7 13 50000.00 Cr ASD 8 13 1000.00 Cr ASD 9 13 500.00 Cr ASD This is my table……….. pls help me at vanapandi@gmail.com Payment ID Amount(dr-cr) Reason 11 5000-3000=2000 ASD 11 0-8000=-8000 BG 12 0

my queries are too slow when SET FORCEPLAN OFF - Hi All, I am using an reporting tool for my dwh system. My tool are generating queries and those queries are too...

Backup Time for 2 TB DB - I am using SQL 2008 R2 before I start backup want to know how long it will take to do...

I need to read the xml which is passed as an variable in stored procedure ?how to achieve that ? - I need to read the xml which is passed as an variable ,and insert its values to temporary table @msgchunk...

Record locking - Question1: How can a user, say User1, be able to lock a row, say Row1, and any dependent rows, say...

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

Unable to Upload Files into FILESTREAM col - Hi, I have successfully created a table with a column of VARBINARY(MAX) data type and its FILESTREAM is set. I then linked...

How to imbed an image into an email sent by dbmail - Hello all and thank you in advance for your time and expertise. I am sending out customer statements in email using...

Database performance issue - The version of sql server is 2008. I just restored a backup of a database from the development server to the...

Activity Monitor - % Processor Time Grayed Out - Does anyone know why the % Processor Time area within Activity Monitor would be grayed out?

Invoke or BeginInvoke cannot be called on a control until the window handle has been created - Hi I am trying to install SQL Server 2008 Developer Edition x64 on a Windows Server 2008 VM. I run the...

SQL Server 2008 : T-SQL (SS2K8)

Fill Result Table with Sale Brackets - Hi there I have a table of sales that has a ItemID, Quantity and Value of the sale. Say for week 1,...

How to crea5e procedure??? - I'm creating one table ps(phonenumber search) create table ps ( name varchar(100), email varchar(100), mobile varchar(100), address varchar(100) ) insert into (name,email,mobile,address)...

Help on sys.dm_db_index_physical_stats - Hi all, I am trying sys.dm_db_index_physical_stats. I observed something about sys.dm_db_index_physical_stats and i am not able to make it out why...

Do you really need the log file - We have a 70 GB mdf w/ a 29GB ldf. After doing full backup, can I truncate the log via...

Can DatabaseMail attach a formatted Excel file - my query USE [AP_ECC] GO /****** Object: StoredProcedure [dbo].[ap_ecc_Mail_for_statuslog] Script Date: 02/06/2013 11:03:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[ap_ecc_Mail_for_statuslog] as EXEC msdb.dbo.sp_send_dbmail @recipients=N'internal@zeroone.com' ,@body='Hi Team,...

How to generate UniqueIdentifiers and insert in a column - Hi All, I have a table in which one of the column is UniqueIdentifier. I have NULLS from records 1...

change connection string info - In the connection string information to a sql server 2008 r2 database, I am currently setting the integrated security to...

Dynamic Case Statement - Hello all, I'm having some issues with a query I have which includes a dynamic case statement. I realize having...

Error converting data type varchar to numeric - I get this error when using this function: [code="sql"]CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue DECIMAL(15,4), @DataType NCHAR(1)) RETURNS VARCHAR(20) AS BEGIN DECLARE @FormattedData VARCHAR(20) SELECT @FormattedData = CASE WHEN @DataType...

CXPacket and PAGEIOLATCH_EX or SH - My company processes data for our clients. We have one database for each of the clients. Largest DB is around...

consecutive days count irrespective of weekends, holidays - Hello, I am working with SQL 2008r2. I have a situation where I need to count >15 consecutive school days absent...

t-sql alter problem - I am getting the following error on a sql server 2008 r2 database: Msg 102, Level 15, State 1, Line 2 Incorrect...

Calculate time difference between multiple rows - Hi everyone, I'm fairly new to T-SQL and I got stuck on a query. We have a large DB with a lot...

UNION vs OR --> NP-Complete Problem - I have recently read about this in the below link. http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/ I just heard about "NP-Complete Problem" first time :) [quote] From Joe Celko... This...

Finding patterns in rows (date ordered) - Dear All, Here's the background - I have a table of staffids and the shifts/durations worked - with usually 1 shift worked per...

SQL Server 2008 : SQL Server Newbies

Primary - Hi gurus I would like to know how can I tell wich of the two sql in my cluster is the...

Display two fields of two UNRELATED tables WITHOUT crossjoin - Im trying to write this query for my personal fun and training. Imagine we have two tables: [i]Table1:[/i] PK, Field ----------------- 1, XXXXXX 2, XXXXXX [i]Table2:[/i] PK, Field ----------------- 1,...

execution plan window - I am trying to observe the graphic execution plan, but my query is big. Is there any way that I...

Help to diagnose LCK_M_IX Query - Hi all, I have a query that is stuck (running for few hours but normally takes 2-5 mins).. Id like...

SQL Server 2008 : Security (SS2K8)

Login Auditing - I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008 R2 shutdown / startup procedure - I have a SQL Server 2008 R2 2-node cluster running Windows Enterprise 2008 R2 and SQL Server standard Edition 2008...

Failover Cluster Upgrade 64Bit - Hi everyone, we had to Upgrade RAM to our Servers running on Windows Server 2008 32 Bit. In Order to use...

Need suggestions on replication - We are implementing a new architecture for one our modules. There are 100 tables in the database and out of...

SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers - Hello all, I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional...

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 2008 : SQL Server 2008 Administration

Server configuration - Hi, I've a customer that has a weird infrastructure implemented to store his customers databases. He has 4 SQL Servers, with 1...

Granting sysadmin rights to local admin to a db - I need to give sysadmin rights to a local admin to only one database on the server. There are 100...

Optimize ad hoc workloads - We have a database server that has databases for research department. It is used mainly for them to read data using...

Restarting the SQL Agent - Hello -- I made several changes to the Database Mail configuration, and in order for them to go into effect, I...

Existing Table division - I have a table "Claim" which contains 85+ columns. records in thousand(Not High-transactional table). The data get populated with the...

Adding Integration Services to clustered nodes - I have installed Integration Services 2008 R2 on two clustered nodes, 'NODE1' and 'NODE2', part of an active/passive cluster named...

conenction is refused using SQL Authentication on SSIS - Hi all I have a problem when I use SSIS Package Configuration and Conenctions using SQL Authentication. I develop simple to complex...

SQL 2000 - 2005 - 2008 - DB from 2000 to 2005 it was recommended to change compatibility to 90 and then do full rebuild of the...

SQLServerCentral.com : Anything that is NOT about SQL!

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

how to show description and deccription_id - Hi, I have two columns Id,id_desc i am creating a report using procedure,in SP i am calling id only while i creating a...

Data Warehousing : Integration Services

Deployed package is not picking up dtsconfig file - Still learning SSIS. I have built a really simple package in BIDS2005 SMTP server connection SendMailTask Sends me an email (bob@myaddress.com) - Works a treat...

Help!! SSis package corruption error - Hi All, I have a big problem. I built the deployment utility of my ssis package to deploy it in production. After...

SSIS - Failed to acquire connection - Hi, I creating an SSIS package which has a script task in it. In the script task, i have provided the details...

Choose one static source inside for loop - HI i have a qs: if i have a data flow task inside for loop container. and DFT contains five...

SSIS Script Task update on a table - Hi, I created a SSIS package to prepare data for analysis. The data comming from a phone system and for...

check contents of a text file - Guys, I am using the Konesans file watcher to watch for a file (status.txt) to change. The file contains only one line...