In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Disaster Recovery is made quick and easy with SQL Backup Pro
Use SQL Backup Pro's restore wizards and scripts to get your database back online as quickly as possible following a disaster scenario. Download a free trial today.
 
Red Gate Deployment Manager NEW! Take the stress out of .NET deployment
Eliminate the risk in deploying manually to live systems using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL DBA Bundle ‘The Case of the Missing Index’
Discover the Top 5 hard-earned lessons of a DBA, presented by The DBA Team. Learn from lesson one now.

In This Issue

Has tempdb grown since the last restart?

Check tempdb to see if it has been autogrown since the last restart. If it has, there may be an opportunity to improve server performance. More »


Free ebook: Troubleshooting SQL Server: A Guide for the Accidental DBA

We spend much of our working life helping solve SQL Server-related performance problems, hands-on, during consulting, or on online forums. We've seen a few weird-and-wonderful issues but, mainly, it’s the same problems and misconceptions time-and-again. This is our attempt to describe, diagnose, and solve the most common problems with SQL Server 2005, 2008, and 2008 R2. And it's free. More »


Performance Tuning Re-indexing and Update Statistics – A Case Study

Recently we started experiencing a very strange issue in our production reporting environment where the Re-indexing and Update Statistics operation suddenly began taking more than 2 days to complete and was thus causing blockage in the database which in turn caused impairment in application performance. More »


From the SQLServerCentral Blogs - SQL PASS Summit 2012 Came and Went – and Changed My Life

I cannot believe how surreal this past SQL PASS Summit was for me. This was the second Summit I have... More »


Editorial - A Tool for the Job

There is just a short distance between 'I can’t see how to do this with a relational database' and 'this shouldn't be done with a relational database'.

The great strength of relational databases is that they can be adapted for a wide range of tasks. They do some things slowly or ungracefully, but a properly-designed SQL Server database can generally get the whole job done. Without blushing, I can say that I've never faced a database problem so obscure that I couldn't do it in SQL Server. Would the application have been quicker had I grabbed one or more specialised NoSQL databases, and would the performance gain would have been worth the on-cost in training and support? There is value in having a consistent architecture

So what shouldn't be done in SQL Server? Andrew C. Oliver, ('I am a NoSQLer and a big data guy') lists ten things never to do with a Relational Database. It is an interesting list:

Search ('Outside of Oracle, many other RDBMS products don't have real search extensions').

Recommendations e.g. People who bought torches often bought batteries too ('that gets ugly in the RDBMS').

High-frequency trading ('High-frequency traders were among the first people to adopt and, in some cases, create NoSQL approaches. Low latency is king for HFT').

Product cataloguing ('Had we kept the product information in a graph database, it would have been simple to map').

Users/groups and ACLs: ('To some degree, LDAP was the original NoSQL database').

Log analysis: ('they really don't need transactions, and low latency is job No. 1').

Media repository: ('You're better off using some kind of distributed storage or clustered file system for your images and other binaries').

Email: ('Email really is moderately unstructured data with metadata that is best stored another way').

Classified ads: ('There's search, there's metadata, there's short-sweet content. Eventual consistency would be good enough here').

Time-series/forecasting: ('The issues surrounding time in relational databases are the stuff of legend').

It is an interesting list and it is useful to get some clarity from a NoSQL/Big-Data fan about those commercial problems for which he considers an RDBMS unsuited. I have my own views about this list, but I'd be more interested in yours, since I suspect that those people who come up with SQL Server algorithms for these problems don't necessarily share their techniques with others unprompted. Consider yourself prompted.

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

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


Question of the Day

Today's Question:

SQL2012 introduced a new functionality called the FileTable. What is it, where and how is it used and what data is stored therein? (Select 3 options)

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

This question is worth 2 points in this category: FileTable. 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 Security Cookbook 

Each recipe comprises step-by-step instructions followed by an analysis of what was done in each task and other useful information. The book is designed so that you can read it chapter by chapter, or look at the list of recipes and refer to them in no particular order. Each example comes with its expected output to make your learning even easier thus enabling you to successfully secure your SQL Server 2012 database. This book is for SQL Server administrators, developers, and consultants who want to secure their SQL Server database with cutting edge techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial-of-service attacks, and SQL Injection, securing business intelligence, and more. Working knowledge of SQL Server is expected.

Get your copy from Amazon today.


Yesterday's Question of the Day

declare @x float, @y float

set @x = 3.65
set @y = 3.75

--What values are returned by the following statements?
select 'x = ' + str(@x,10,1) 
select 'y = ' + str(@y,10,1)

Answer: C: x = 3.6 y = 3.8

Explanation: Correct answer C: x = 3.6 y = 3.8

Rounding a number y to the nearest integer requires some tie-breaking rule for those cases when y is exactly half-way between two integers — that is, when the fraction part of y is exactly 0.5.

Ref: STR - http://msdn.microsoft.com/en-us/library/ms189527.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 Security Cookbook 

Each recipe comprises step-by-step instructions followed by an analysis of what was done in each task and other useful information. The book is designed so that you can read it chapter by chapter, or look at the list of recipes and refer to them in no particular order. Each example comes with its expected output to make your learning even easier thus enabling you to successfully secure your SQL Server 2012 database. This book is for SQL Server administrators, developers, and consultants who want to secure their SQL Server database with cutting edge techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial-of-service attacks, and SQL Injection, securing business intelligence, and more. Working knowledge of SQL Server is expected.

Get your copy from Amazon today.


Featured Script

Deleting Duplicate Records

In Datawarehousing, a normal scenario is eleminating duplicate records/rows or deleting duplicate records, here's the solution.  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

SP - output confusion? - Hello, we have a SP which in turn calls 2 sp's. because of one sp output data got updated wrongly . but when...

collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - Hi, We have sql server 2005 reporting services on server A & its databases on Server B. I'm seeing the below error...

High Log flush time??? - Hi, We are using Spot light for Monitoring SQL Server 2005 and we are getting the below error frequently. What this error...

Mirroring In sql server 2005- Witness Full Quorum - Hi All, I have established a Synchronous mirroring with witness ( to achieve automatic failover), I am getting all the configuration perfectly...

SQL Server 2005 : Backups

Backup file HUGE compared to DB size - Hi Guys, Have a strange one... Found a job running on one of my servers. Job did a full backup of...

SQL Server 2005 : Business Intelligence

Export top(n) rows from Excel - Hi, I am trying to export only top (100) rows from an excel source. I added the excel file to the...

SQL Server 2005 : Development

Date Issue - I have a problem with dates Problem: There is a master table which defines the date based on range as follows [sup] [b][From][/b] [b][To] [/b] [b][ResultDate][/b] '1st...

Producing dates from SYSJOBS and SYSJOBHISTORY - Hi all I recently decided to start to report on durations of jobs that I have scheduled on my SQL 2005...

SQL Server 2005 : SQL Server 2005 General Discussion

Delete Using join in min 3 tables - Can any one plz tell me how to delete records from minimum 3 tables using inner join on the basis...

SQL Server 2005 : SQL Server 2005 Security

Please excuse and delete the dups - feeling like a n00b. :-(

SQL Authentication login weirdness... - Forgive me upfront for not supplying a snapshot of this issue, but here is what happened. I tried to log...

SQL Authentication login weirdness... - Forgive me upfront for not supplying a snapshot of this issue, but here is what happened. I tried to log...

SQL Authentication login weirdness... - Forgive me upfront for not supplying a snapshot of this issue, but here is what happened. I tried to log...

Want to remove an existing user mapping for a login. - In SQL Server 2012, I want to remove (programmatically in C# or via SQL command line) a login mapping to...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Best Maintenance Plan - Hi, I am currently tasked with an incident to optimize the current MP that we are running on one of our...

Question about included columns in nonclustered indexes: Where exactly are they stored? - I figured an indexing question would best be answered by folks who focus on performance tuning, if there was a...

SQL Server 2005 : SQL Server 2005 Integration Services

rolling back data in SSIS package - Hi, I have an SSIS 2008 package which will call a stored procedure and store the data into a variable...

how to use substring to get the values in bold - Hello All, Could you please tell me how to get the value after 'between', before 'and' i.e., BOKSAM KAND LAMOG also...

SQL Server 2005 : T-SQL (SS2K5)

Alternate way for SELF JOIN - Hi All, I have a table like below. create table fund( fund_id integer primary key not null, name varchar(100) not null, short_name...

Query to tell when Isolation level was set... - Hi - I need a way to find out when an isolation level was last set on a database (or originally...

Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs - Hey all, Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables: [code] CREATE TABLE...

SSIS Script Task - Can anyone help me with adding log in information to a script task which goes to a webpage? The current...

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 7,2000 : Administration

Remove Duplicated row on a iseries table - I have a table similar to below Customer Referencedate card_number created_by John 112233451 2008-02-01 48001796 Sen John 112233451 2008-02-01 48001796 Sen Eddy 123565654 2008-03-26 48001799 Mark Mary 124578128 2009-10-27 48001850 Mark From the above table I need to remove all duplicated rows leaving...

SQL Server 7,2000 : General

Database is stored on multiple volumes - Hi, In our production box, I got an alarm saying " Alarm Details: SQLWRITER: [24579] Sqllib error: Database XXXXXX of SQL server...

SQL Server 7,2000 : T-SQL

export all table to excel with header using bcp - Hi, I'm tring to export all table to xls file with header , I'm using the following code its working fine...

SQL Server 2008 : SQL Server 2008 - General

To OTAP or not to OTAP? - Currently I am working in an organization, where development and production databases reside in the same SQL Server instance. The...

Transaction log file increases abnormally after replication - Dear, 1. I have implemented snapshot replication on my database. My database size is 2 GB. I scheduled snapshot agent to...

Merging the rows - The Output result set is coming as Column ------------------------- a-1 (row 1) b-91e25b15-(row 2) c-66364531(row 3) expected output required(all rows needs to merged into single row) a-1...

www.jumpmanbiz.com SDEE5566 - It is so lucky for women since there is no lack of styles when it comes to Trendy Christian Audigier....

www.jumpmanbiz.com SDEWE3633 - It is so lucky for women since there is no lack of styles when it comes to Trendy Christian Audigier....

What is DCEXEC? - As the question. It is used in an SQLAgent job. Googling links it to SSIS execution, but no explanation AFAI...

!!! Database server slowing down - I have a vb.net application using SQL database, I have installed SQL Server 2008 on Server but server does not perform...

Update on an ADO Dataset that is linked to a view - Hi, I have a table in my DB that has my Product information. this table has a tree structure (each record...

setup maximum and minimum memory - I have a dedicated SQl server that has 12 gb physical memory. I manually change the default max memory to 8.5...

SQL Server 2005 Cluster - Removing / Rebuilding / Adding Node - We have a two node SQL Server 2005 SP2 cluster, Running Windows Server 2003 SP1. My company implemented a new...

size of LOB data 41GB smaller after BACKUP/RESTORE - Hi everyone, Maybe someone more experienced than I can explain this to me. Made a backup of our largest production database,...

Problem when using a User Defined Function in a SET loop - I am using SQL Server 2008 R2. I created a User Defined Function like this: [quote] CREATE FUNCTION [dbo].[Custom_StringToTableWithID] ( @string VARCHAR(MAX), @delimiter...

Using Full-Text Search - Hi, I am quering a big table (about 150,000 rows) with a big varchar field (size 2000) which can't be indexed...

Time out in insert statement - Hi every body I have a large table (270 G) and i only have insert and select statement over this table....

SQL Search Query - hello, right now on my search page the sql query i am using is similar to: select * from table where name like...

query taking long time for execution inthis case what should i do - hi all 1. application team complaining that queries are executing slow and want reason for that in this case what should...

SQL Server instance is taking 100% CPU and is very slow - Hi, SQL Server instance is taking 100% cpu. I tried to find the active sessions using sp_who2 'active' and I got...

Restore 1.9T on SQL 2008 - Hi guys, Need urgent help.. I am restoring 1.9T from multiple backup files, the restore was doing 10% an hour until it...

Keeping track of the number of times a record appears in query results - Goal: I have a web site for people to search for doctors. For marketing purposes for doctors, in my stored...

Loading data from Exel 2007 file - I can not access an excel file that has no permission issues. I have SQL Server 2008 R2 installed on a...

Is there a smarter way than a cursor for this? - Hi all, I'm migrating some data due to software improvements. We have about 25k surveys, each with around 3 questions on...

Query Optimization - Hi guys, I have one query which is listed below which takes 5hrs mininum to execute it.. please help me on...

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

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

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

how to avoid duplictae records of column in a table in a select query in this proc? - here iam having two table @companytable and @boardtable in the @boardtable table will have foreign key of companyid from @companytable...

sql server 2008 - HI, WHEN I SELECT SQL SERVER CONFIGURATION MANAGER AND SELECT SQL SERVER SERVICES IN THE LEFT PORTION AND IN THE...

Which information can i log when i use Change Data Capture - Hi When i use change data capture can i log hostname,username information?

SQL Server 2008 : T-SQL (SS2K8)

Updation - I have a table proceduremaster in which I need to update ParentProcedureID on basis of procedurecode and procedureID. create table proceduremaster (...

Full Database Backup with no truncate - Hello! I need write tsql statement for full database backup without truncate log Please Help

TSQL Split XML attributes - I have an xml string and need to manipulate it within sql. [code="xml"] <RuleData ReturnVariable=""> <MateProperties> <Property Key="value" DisplayName="name value" /> <Property...

Get Records according to dataset. - Hello Expert ! I have a CTE Query but I have nothing concepts about it. And I am new in programming....

xml data type vs several records - Hello I am trying to save student answers to questions in a quiz I am looking for best performance and...

Convert values of mulitiple rows into single column with no duplicate entries - hi all, i went through the forum and found out a way of concatenating row values into column using stuff, but...

how to remove characters char(0) to char(31) - Hi there, Sometimes customers add data with unwanted characters in the ascii range 0 to 31. How do I remove them?...

Avoiding IF/ELSE - --I believe the DDL and my commented out notes and questions should explain what I am hoping to achieve CREATE TABLE...

Avoiding cursor: Help with getting only first match after previous match - Hi I'm sure there is a better name for this problem, and its probably quite a common one with a...

Need an Efficient Way to Rank Rows with Some Rows Having the Same Rank - Does anyone have a suggeston on the most efficient way to do this ranking? I need to add an incremental...

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

String Function Help - Hello, Total Newbie to SQL Server, so pardon any non synchronous sql lingo. In a table, i have column "IP Address" which has...

New DBA job, done some digging on Database and have some concerns! - I've recently started a Job at company working as the sole DBA. I come from a .net developer background and...

List Features installed - hi all, Does anyone have a way to list the features installed on an instance? At present all i can...

Alternative to views - Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2...

html from sql server 2K8R2 - Hi all, I am trying to generate an HTML-formated email from sql server. I need to be able to control the...

Getting variable name from cursor as part of query results - Before you chastise me on not doing cursors, since I scanned articles before submitting this request, I know cursors are...

SQL Server 2008 : SQL Server 2008 High Availability

Mirroring configure HA and DR - Hi, Database have been configured High availability in same datacenter by using database mirroring.. For the anohter requirement the same principal database...

SQL SP/CU taking HOURS on Multi-Node Multi-Instance Cluster - We need to install SQL SP2 and CU1 at multiple sites on SQL/WIN 2008r2 EE clusters with 5 nodes, 22...

SQL Server 2008 : SQL Server 2008 Administration

SSMS Job Creation/Editing Error - My SQL Server Version is given below (Production Environment). Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43...

backup/restore process - Hi, Hi, Our business can afford to loose data up-to one hour. So in-case of say if a table is dropped or a...

Policy Based management script? - Is there a tsql script that can give me details (name, condition, created by etc) of all the policies created...

have a job step execute a job on another server - i need the last step of a job to execute a job on another server. Can someone post that command? Thanks

SQL Server Backups - This is more of a poll but I'd like to get others opinions. What is the recommend and preferred methods...

SQL Server Agent Powershell Subsystem with only 2 worker threads - My Sql Server Agent can only run two powershell jobsteps at one time which apparently is caused by the max_worker_threads...

Programming : XML

Returning Multiple Unrelated Tables in Single XML Output From Stored Proc - Hi There, I'm quite inexperienced in returning XML from SQL and have only been using single tables to return normal XML...

Programming : Powershell

Get SQL Query Results as E-Mail - Gurus, I am trying to write a code to send out list of failed jobs in the last 24 hours....

SQLServerCentral.com : Anything that is NOT about SQL!

DOT.NET Data Reader - Is there any documentation about the inner workings of the DOT.NET data reader. I mean Sstep by Painful Step. I am...

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

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

Scheduling the SSRS Report from SP - How can we subscribe the SSRS report from a Stored Procedure

Preserve SSRS formatting when exporting to Excel - I'm pretty sure SSRS doesn't do this on its own, but I was wondering if someone could help me find...

Database Design : Design Ideas and Questions

Advanced group design - Hi All I ask you guidance on this design question because this seems to be one of those things where if...

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Data Warehousing : Integration Services

error when creating SSIS Packages - Hi i have a task to develop SSIS packages, i've installed SQL Server 2008 R2 Developer and installed Intergration Services but...

What is the best isolation level - Could you tell me , Which is best isolation level for SSIS and T- SQL ? anyone advice me.. Thanks solomon

Data Warehousing : Analysis Services

MDX to fill listbox - Hi, I'm using an MDX statement to fill a listbox with available dates: Current statement is SELECT NON EMPTY [Period].[Period by Financial...

Data Warehousing : Data Transformation Services (DTS)

trying to insert several csv files into a db table using a ssis package - I have several .csv files and i'm trying to insert them into a database table. all my files are saved from...