In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

In This Issue

SQLServerCentral Runs sp_Blitz - Performance Part 2

The second part of our performance examination on the SQLServerCentral database servers using the sp_Blitz script from Brent Ozar, PLF. More »


Query Tuning, Encryption, and more at SQL Intersection

Grant Fritchey is speaking about query tuning, Steve Jones talking about Encryption, Paul Randal telling you how to make SQL Server faster. All at SQL Intersection. Join us in April. More »


Using Optimizer_WhatIF and StatsStream to Simulate a Production Environment

SQL Server's Query optimiser judges the best query plan from the data in the relevant tables and the server's hardware. How, then, can you investigate the query plans being generated for slow-running queries on a customer's production server when you can neither access the server, nor recreate the database from a backup? More »


From the SQLServerCentral Blogs - Index Tuning

This week I've been carrying out some performance tuning of some of the more intensive procedures in one of our environments.  Using... More »


Editorial - Zettabytes and Beyond

The old Carl Sagan quote about billions and billions of stars in the universe doesn't seem so large anymore. In fact, a billion of anything, while a large number, seems immense only until we talk about the scale of data. How much data is there in the world? I'm not sure. As I was researching this for a new presentation, I'm not sure I can even conceive of the scale of data creation occurring in the world today, much less how much data we have.

Think back 30 years ago, as computers were just starting to become household items. The high density floppy disk (not really very floppy) was a 1.44MB disk. At the time, this held what felt like lots of data in terms of text pages. However many songs we listen to today wouldn't fit on this media. As we've progressed through CDs and DVDs to flash drives, we've grown the storage capacity of our hardware by unbelievable amounts. My phone has 64GB of storage, which is a level of growth so far removed from the Apollo guidance computer's 2kb that comparisons don't do it justice.

We used to create data storage analogies by listing the number of books that would fit on the device. Today that's meaningless. The 30,000 ebooks on Bookworm fit in 20GB. That's a number of books that's hard to conceive of. My local library branch has about 20,000 books, so I can somewhat grasp that scale, but not really. Two libraries worth of books is a level of words and knowledge that I'm not sure I appreciate. Trying to understand the amount of storage a library like the Vatican needs, is beyond comprehension.

The grasp of how much digital data we create is even more mind boggling. I saw a talk recently that said 24 hours worth of digital video is being uploaded to YouTube every second. Every second. That's an impressive statistic, but I'm not sure we can even comprehend what that means. An even more daunting statistic is that all the knowledge recorded from the Gutenberg printing press invention through the next 500 years totaled about 1 exabyte. At current rates, we create an exabyte of digital data in less than a month and that's only going to increase. If you read some of the analogies in this report from EMC, they're almost silly, and certainly not something most of us can relate to. I certainly can't picture 75 billion iPads.

These days the amount of data we are dealing with is growing faster than ever, and that means it's a good time to be in the data business. From "Big Data" to data warehousing to the common OLTP databases we manage, there is no shortage of bits and bytes we will get the chance to manage. For pay.

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

Is TRUNCATE TABLE a DDL command or a DML command and why is it different from the DELETE command (choose 3)?

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

This question is worth 2 points 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.

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



Yesterday's Question of the Day

/* Using SQL Server 2012 or 2008R2

I create the following two (2) tables 
*/

CREATE TABLE [dbo].[Order](
[OrderID] [int] NOT NULL,
CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED 
([OrderID] ASC)) 

GO

CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] NOT NULL,
[OrderID] [int] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
([OrderDetailID] ASC))
GO

/* I then create a foreign key constraint */

ALTER TABLE [dbo].[OrderDetail] WITH CHECK 
 ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
   REFERENCES [dbo].[Order] ([OrderID]) ON DELETE CASCADE

/* I then insert data into both tables */

DECLARE @val INT,@val2 INT
SELECT @val= 4 

WHILE @val < 6 
 BEGIN 
  INSERT INTO dbo.[Order] VALUES(@val) 
  SELECT @val2=1
  WHILE @val2 < 10 
   BEGIN 
    INSERT INTO dbo.[OrderDetail] VALUES ((@val*1000)+@val2,@val) 
    SELECT @val2=@val2+1
   END
  SELECT @val=@val+1 
 END

/* I then execute the following T-SQL statement */

DELETE FROM [Order] WHERE ORDERID = 4

The questions are: (Select two answers)

Is the row containing the value of ORDERID = 4 deleted from the Order table?

How many of the rows containing a value of ORDERID = 4 are deleted from the OrderDetail table?

Answer:

  • Row containing ORDERID = 4 is deleted from the Order Table
  • All rows containing ORDERID = 4 are deleted from the OrderDetail Table

Explanation: The answers are the row with ORDERID = 4 is deleted and all rows containing ORDERID 4 are deleted from the child table.

ON DELETE CASCADE - Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.

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

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Featured Script

DB File Overview

This script will give you a overview of the current database files. 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

Track changes to database objects - I need to supply my manager with a monthly report showing all the database objects that was changed. E.g. stored...

Changed SQL Services Acct - "Cannot Generate SSPI Context" - We want to rollout a new account to use for SQL Services. On a Dev server (SQL 2005 SP3, Windows...

How do I interpret sp_who2 results? - I have been looking around at various articles and threads and in SQL Server 2005 Tutorial and have not come...

SQL Server 2005 : Backups

Log Backups Failed to Truncate Log - I have a full recovery database used for holding logging information, typically Name, Type, Time and some other values. Each...

Restoring database from production to Dev - We have Sql Server 2005 database server in both production and development environment. I have the requirement to restore a database...

Server is rebooting after taking backup and it is not giving the Bak file also - HI to all, Server is getting restarted whenever we are running the SQL Backup [scheduled backup maintenance plan job and...

Table Level Rescovery/Restoration - HI All, Can we restore/recover individual Table in Microsoft SQL Server. As far as i know it is not possible Out...

Log backup and running time - Hi, Bit of an idea to ponder: I have a server with 15 user databases ranging in size, the largest being 120GB....

Transaction Log - I have a database in SQL server 2005 (64 bit) server. The recovery mode is set to simple recovery, and...

SQL Server 2005 : Development

what is alternative for *= in sqlserver 2012 - Recently we have migrated our projects to SQLSERVER 2012 , some of the queries has *= [syntax in where condition]now we need...

SQL Server 2005 : SQL Server 2005 General Discussion

Microsoft SQL setup files configuartion failed. - Hi All , We did encountered some issue while doing an inplace upgrade from SQL 2000 TO 2005 as the setup...

Update one column of a table with data from another table - I have two tables which are different with the exception of a single field (column name = "LocationCode"). Both tables can also...

SQL Server 2005 : SQL Server 2005 Security

How do i grant permission to msdb.dbo.sp_send_dbmail ? - I have my custom sp in my testDB database which is using dbo.sp_send_dbmail system sp from msdb. I have many...

SQL Server 2005 : SQL Server 2005 Integration Services

ftp task - PGP public key - Hi, I am in the process of using a FTP task in my ssis package. This is to retrieve files from a...

Access Schemas - I was recently assigned a task of dynamically extracting Access schemas...How do i approach using SSIS..Any thoughts or suggestions would...

SQL Server 2005 : T-SQL (SS2K5)

sp_send_dbmail fails within a trigger - I'm trying to write a trigger that will inform me via an email using sp_send_dbmail when a certain value has...

SQL Server 7,2000 : Administration

CAST CONVERT doesn't work - Hello everybody I have th following CAST Convert statement: SELECT CAST(CONVERT(CHAR(8), EZACDT) AS DATE) AS Accounting_Date FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00 WHERE EZCONO = 2 AND EZDIVI = '001' And get...

SQL Server 2008 : SQL Server 2008 - General

Comparision of tables in two instances - Am having two instances A and B, in that having a table called students in two databasess, but the records...

Check Data While Inserting - Dear All, I have a UI like, LineNo ProdID Discount(%) and a "Insert" Button -------- ------ -------- I can insert a single or multiple lines in this UI. While...

best optimized query for the requirement - I have the below query and need the best query to optimize the requirement SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=1...

Not able to find historical data using SQL Server Profiler in SQL Server 2008 - Hello All, I would like to capture the script that was ran by user on yesterday between particular time. But somehow i...

SQL Script Help - Need to capture a single transaction from many possibilities - Here is the DLL [code="sql"] IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XX_TEMP_AR_INSERT]') AND type IN ( N'U' ) ) DROP TABLE [dbo].[XX_TEMP_AR_INSERT] GO SET ANSI_NULLS ON GO SET...

System.Data.SqlClient.SqlException (0x80131904): Timeout Expired - I apologize if this question has been asked before, but we're really stumped and the answers I've seen posted don't...

Transactional replication problem - Hi everyone, We encountered some strange error when we were trying to add a subscriber. Following steps were performed: 1. Publsher and distributor...

BULK INSERT Different Data Types in the SAME Columns - Issue: I have a small csv file uploaded through a web page by an end-user. It has two INT columns....

Update YTD number with the sum of MTD numbers - I have a table with budget data. The month to date number has been loaded. I need to update the...

Problem having restoring database backup file on secondary server - Hi for last two days at one of our client side we have been trying figure out a why its...

Bulk Insert via VBA - Operating system error 5 - Hi, I hope this is the correct location for my question. I have written a simple piece of VBA to convert some...

Need help to read text file - Hi, My text file as attachment ~ AG-Persekutuan-Bln-09-2012.txt How to read this file as follows, 1. Taxpayer data starting in the second row 2. The...

BCP to import tab delimited file with header - Hi, i am trying to load a file which is a tab delimited file , which has a header row in...

CPU usage - Hi, I ran some DMV queries to get a server's health and came across some questions.. * The signal waits query gave...

Deadlock on update command - I have the following dead lock happening quite frequently at 3-4 times a day. The deadlock seems to happen on...

Strange issue with one of the four Sharepoint databases that we Log ship to our DR site. - On the server that hosts our Sharepoint databases we are log shipping 4 of them to a sql server instance...

SSMS User Interface Tip to Freeze Panes. - Accidentally discovered this, and thought I should share it, as I don't think I've seen it mentioned before. In Management Studio,...

reorganize index - Hi Guys, I'm going to reorganize indexes with > 10%. Is there a need to update statistics after the re-org? Understand that if...

ROWLOCK - CREATE TABLE [dbo].[TESTROWLOCK]( [ID] [int] NULL, [NAME] [varchar](100) NULL, [SURNAME] [varchar](200) NULL ) ON [PRIMARY] GO [b]TAB -1 [/b] I m trying BEGIN TRAN SELECT ID FROM TESTROWLOCK...

BombProof Data Import from Excel ? - I have a very large spreadsheet of 125,000 rows, 200 columns, some of which contain a lot of text. I...

Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table? - Would be most grateful for confirmation/infirmation of theory below: Changing an existing table column from NUMERIC (12,6) to NUMERIC (19,4) should...

Insertion in table - HI, This is the table structure and script below: CREATE TABLE [dbo].[GV_Booklet]( [BookletId] [int] IDENTITY(1,1) NOT NULL, [LeafCount] [nchar](10) NULL, [Denomination] [int] NULL, [VoucherTypeId] [int] NOT...

XLSX Import Error: result code db_sec_E_auth_failed - I'm trying to import a 190 meg .XLSX file to a SQL 2008 (not R2) server. First, I installed "Office...

Data not inserting - Hi i have written a query to check the insertion but its not working neither getting any error : Declare @valuebased...

Read Committed_Snapshot - "Read Committed_Snapshot" isolation level in sql has any negatives or disadvantages?

SSRS 2008R2 showing counties (not just states) - I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way...

Validating trace file events - Hello, If I have a trace running and populating some *trc files.. My question is how can I validate that i am...

Primary key or not to primary key, that is the question - Yeah, bad taste on the subject but nonetheless, you're reading my post :). I'd like to pose a scenario and see...

Few tricky Questions about SQL Server 2008 that I came across... - Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of...

SQL Server 2008 : T-SQL (SS2K8)

How to read XML file with multiple children and load it in SQL Server - I have an XML file in the below given format. How can I import it into SQL Server using OPENXML...

Incrementing code to add code - Hi, I am trying to join the table to itself many times on a condition. Is there a way to automate...

asp with sql server 2000? - hai friends, I m currently using sql server 2000 DB for my asp application my table is create table ttr ( startdate datetime, from_loctn varchar(100), to_location...

T-SQL substring replace function - Hello, I have some data and need to extract the first 9 numbers from this pattern across multiple rows of...

SP Help - Hello

How to get month number like Jan -01, Feb-02 ... Instead of Jan-1, Feb-2 - Hi, Need help on get month number from Date field, I am getting month number as 1 for Jan, 2...

Help with the query! - I came across a situation as follow [code="sql"] CREATE TABLE DAYOFWEEK(WeekID INT IDENTITY,DAY NVARCHAR(50)) CREATE TABLE SubjectToStudy(WeekID INT,SUBJECT NVARCHAR(50)) INSERT INTO DAYOFWEEK VALUES('MONDAY') INSERT INTO...

Ranking functions, views, and predicates - So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue...

problem in select command - hi please look at this select command : [Code][b]select top 2 * from dbo.NFFeeds order by id desc [/b][/code] for my database the...

Custom sp_who - Hello All, Couldn't find anything that addresses this specifically, so here goes. I have a group that I want to allow to...

SQL Server 2008 : SQL Server Newbies

a query like factorial - Hi. I ask you for help because its more than 2 days im thinking about this question. Imagine we have a table...

Obtaining Before & After Record & Missing Record. - I have a database that has two tables. On table is a date table that has all of the possible...

SSIS - Data Flow Task With Delete Very Slow - I have a Data Flow Task that reads from a Flat File Source (orders.csv) and then does an OLE DB...

Do Users based on logins in master have access to other databases? - When I create a user and select master as the default database does that mean or imply that user can...

New Database Request Form - Hi guys, Just wondering if anyone has created a new SQL Server Database Request Form? What I'm looking for is a...

SQL Server 2008 : Security (SS2K8)

permission to windowsgroup - I have created 3 users in windows which are part of windows group named wingrp1. I created a login in...

how to give create or drop permission through ssms - in sql 2008 it is possible to assign permission to an object such as table or view insert or select permission...

working with sa account - I am working with sql 2008 and using all features of security but not able to understand the need and...

users not able to access database - Hi Friends, We are facing an issue on one of our servers. We have some domain users in our database. These...

Deny member of sysadmin user access to databases - Hi, I am not sure if this is possible, but here goes. Can a user who is a member of the...

Disable & Rename 'SA' - Hi All, Our SQL risk assessment determined that 'sa' should be renamed and disabled. As a fallback readiness, i gathered all...

sql server login failure - I am working with asp.net and try to access through my login form sql server database mydb when logging in as windows...

Need advice on designing SQL Server security using Active Direcory groups - Hello, For some reason I must not be Googling this correctly. I am looking for advice on how to best design...

Move logins from one to anothe - I would like to move a database from one server. In this move, I need a script to copy all...

SQL Server 2008 : SQL Server 2008 Administration

Doubts on mirroring - Dear All I need some clarifications on mirroring. please help me out 1) After configure the mirroring if the principal is failed...

CLR Question - We have a production CLR process that queries Active Directory for User Information and loads it into a table. We...

Log Shipping Compression - Hello, does anybody know if any versions of SQL have the ability to compress log files before shipping. The plan...

Sql server back ground processes. - I want to know , when i am execute sql select query , what are processes will be running in background Please

SP3 upgrade on SQL 2008 issue - Hi, Recently we tried upgrading SQL 2008 cluster instance from SP2 to SP3. After patching done on passive node, we tried...

Log File Shrink - Now that we got the log file backups under control, now it's time to shrink the log file. I'm going...

SQL 08R2 SP2 - did not update!! - Hello SQL peeps, I installed SQL 08R2 SP2 last week on about 20 servers however one did not update. (All...

how to know the delta data volume processed every day in mb - Hi I wanted to know if there is a way to figure out how many bytes of data have been...

SSIS ForEach Loop Container - loop through all DB's - I am trying to get the FELC to loop through all the databases for my sql server. I want to...

SQL Agent - SQLSTATE 21000 error - I have found weird error in my SQL Agent which is produced about every 30 seconds. The message reads as...

Career : Certification

Details on the certification - I am just wondering how much each exam would cost...and where to register.. Would also like to know how many...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

Issue with IFCode shortcuts in IE 10 on Windows 8 - I just got my shiny new computer from Dell with Windows 8 pre-loaded on it. It is quite zippy compared...

SQLServerCentral.com : Suggestions

Save output of DBCC SHOW_STATISTICS into a table - Hi, At our company, we are using a single package on SQL server for Accounting and Warehousing. We experience some performance problems...

Reporting Services : Reporting Services

date parameter issue - Hi I have created a parameter with a called MonthEnding that has a datatype Date/Time. In my report parameter properties i...

barcode generation problem in RDLC local report - I am trying to encode [url=http://www.aspper.com/barcode-rdlc/tutorial.html]barcodes in RDLC reports[/url], I am using vs 2008 and C# class library. It works well...

Database Design : Design Ideas and Questions

24/7 Mulitple Table Design - Hi All I will apologise this will be a little vague but is all the info I can give at the...

Data Warehousing : Integration Services

Configuration Table and deployment to Production Server - Hi all, I have to deploy a set of packages from the development server to the test server and later on...

Data Warehousing : Strategies and Ideas

sql12 tabular mode design - Hello, Im finding it a little "extra" work is needed to get a complicated tabular model designed vs traditional OLAP...

Data Warehousing : Analysis Services

Communication link failure - Shared Memory Provider: No process is on the other end of the pipe - Dear All, I having a SSIS package to process the cube (SSAS 2008) in daily basic. This morming I check the...