SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Problems with Big Data

Big Data is constantly in the news. We've been asked at SQLserverCentral to try and develop some articles, perhaps even a stairway to explain what Big Data is and how we might use it. I'm still trying to grasp the concepts myself, and unlike the amorphous cloud, I'm still looking for some good examples of what Big Data really is.

When I ran across this piece warning that Big Data isn't the final solution to all our questions in the world, I wasn't surprised. The piece notes that Google Flu hasn't been very accurate in its predictions of outbreaks. At first glance, this gives lots of credence to the idea that the good, solid data analysis and mining techniques we've used for years are just as good as any new Big Data fad.

However as I read more about the piece, it's not that big data and the analysis of large quantities of information is flawed, it's that a solid hypothesis matters. Researchers need to be willing to evolve their algorithms as they learn more about a problem. Probably they should also assume their algorithms are not correct until they've proven their ability to predict actual trends for some period of time.

We'll constantly be searching for ways to better interpret information and make better decisions. No new technology or product is going to magically solve our problems. Good solid understanding of the problem domain will continue to matter as much as the data itself.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.8MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA 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.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
SQL Toolbelt

16 essential SQL Server tools

In one installer, the award-winning SQL Toolbelt contains everything you need to work with SQL Server. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download a free trial.

SQL Search

Have you tried SQL Search yet?

SQL Search has one job, and it does it well. Search database schemas for fragments of SQL text in sprocs, functions, views and more. Download Red Gate SQL Search - it's free!

Fundamentals of SQL Server Replication eBook

Get to grips with SQL Server replication

In this FREE eBook, Sebastian Meine guides you through the fundamentals of SQL Server Replication, to the point where you should feel comfortable using it in production. Download the free eBook.

Featured Contents

 

Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function

Gregory Larsen from SQLServerCentral.com

There are times where you need to write a single T-SQL statement that is able to return different T-SQL expressions based on the evaluation of another expression. When you need this kind of functionality you can use the CASE expression or IIF function to meet this requirement. In this Stairway level Gregory Larsen reviews the CASE and IIF syntax and showing you examples of how the CASE expression and IIF function. More »


 

Free eBook: Understanding SQL Server Concurrency

Press Release from Red-Gate

When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do. More »


 

How much overhead does encryption add to a SQL Server query

Additional Articles from MSSQLTips.com

If you have been in the information technology industry long enough, you have probably heard the expression "this adds overhead" when discussing any extra processing added on to what is considered normal processing. Dallas Snider answers the question of additional overhead caused by encryption. More »


 

From the SQLServerCentral Blogs - Analyzing CXPACKET waits

Enrico van de Laar from SQLServerCentral Blogs

One of the most common wait types you will see on a SQL Server is the CXPACKET wait type. This... More »

Question of the Day

Today's Question (by Joe Barth):

Okay, you need Adventureworks, on a instance you don't care about or you will want to wrap the deletes in a transaction and rollback.

Run statement one:

SELECT TOP 10 'DELETE FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] ' + CHAR(13) + 'WHERE [BusinessEntityID]='+CAST([BusinessEntityID] AS VARCHAR(50))+ CHAR(13) + 'GO'
 FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] 

Run statement two:

SELECT TOP 10 'DELETE FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] ' + CHAR(13) + 'WHERE [BusinessEntityID]='+CAST([BusinessEntityID] AS VARCHAR(50))+ CHAR(10) + 'GO'
 FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] 

When you copy the results and paste in a new SSMS query window,  which Deletes will successfully execute?

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 2 points in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

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.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

You are using a symmetric key to encrypt some data in a stored procedure. However you want to follow good coding practices and stop using the key when you are done with the encryption before you execute other code. What T-SQL would you use to ensure that the symmetric key, SalesSymKey, cannot be used later in your session?

Answer: CLOSE SYMMETRIC KEY SalesSymKey

Explanation:

The correct answer is the CLOSE SYMMETRIC KEY T-SQL command. This will prevent the key from being used later in your session for another purpose unless it is opened.

Ref: CLOSE SYMMETRIC KEY - http://technet.microsoft.com/en-us/library/ms177938.aspx


» Discuss this question and answer on the forums

Featured Script

Find dependencies between field and sp/function

Raymond Xie from SQLServerCentral.com

I would like to share my experience on how to find out dependencies between a table's field name and stored procedures or functions.

I often encounter situations that require database schema changes to meet business requirement changes. For example, changing the name for some fields. It might be an easy thing if the database is small and not used for a complex front-end. However, in my cases, changing a field's name often means:

  1. table schema change
  2. stored procedures change
  3. functions change
  4. data access layer code change
  5. data presentation layer code change

At least the first three are in the SQL scope, and it would be time consuming if we have to open each stored procedure or function and search from there.

This script is not complicated but rather a handy tool. It will list all the stored procedures and functions that contains the given keyword passed as a parameter, and will further indicate if the keyword is actually used as a parameter. This is important because it helps me to find out what functions are related to it in my data access layer code.

There are some limitations:

  1. All my stored procedures start with sp or dd, so you should modify the script to meet your own situation.
  2. All my stored procedures are no longer than 3000 characters, so if you have a longer one, please increase the parameter in the script.
  3. Keyword length is set to varchar(20). You can increase it by modifying the script if you want to search for a longer keyword.

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

License SQL 2014 - Cores - Hi - Hypothetical situation: I have a 32 core server. SQL 2014 enterprise installed. The server licensed for 32 cores. Question raised - can...

Hardware Requirements for 1 TB Database with Backup - We are migrating Databases, which we expect to reach 1 TB at the final lap. We need the following info...

SQL Server 2014 Developer Edition - availability for download ? - Does anyone know when SQL Server 2014 Developer Edition will be availability for download ? I thought SQL Server 2014 was...


SQL Server 2012 : SQL 2012 - General

Execution Plan Icons - Hi, I want to find out more information about the execution plan. I saw Parallelism (Gather Streams) in the excution plan....

SSMS 2012 run in guest context without run as administrator option - Hello, folks I run SQL Management Studio 2012 locally on a server with Windows server 2012 R2 and then connect to...

Duplicate records on a join. - Hi all, I'm having problems with the query below, this is a link to my previous post on the forum...

Replaying workloads with minimal external factors - Hi all, I'm currently working on a project at work to test the effects of database compression, trying to obtain measurable...

Backup Compression Restrictions - I came across the below Backup Compression restrictions. can someone please add on any more restrictions to backup compression in...

Problem with DatabaseMail - Hi all We've just moved our database to a new server, and we're having problems getting databaseMail to work. Essentially, when we...

Which path will be the perfect in MCSD for me to deal with SQL Server Stuff ? - I intended to be a SQL Server developer and Administrator , but I planning to take MCSD before MCSA SQL Server...

Export SSIS Package/Project - Using SQL Server 2012 and Visual Studio 2010 as the development platform. I Deploy the Project and the processes run...

simple store procedure - Hey Guys, I am using SQL Ser 2012 and using adventureworks 2012 db. I am creating a simple SP wherein I search...

COMPARE TWO TABLES COPY TABLEA VALUES TO TABLEB - I HAVE TWO TABLES LIKE.. TABLE_A SNO NAME MOBILE EMPID 1 RAJU 9014494876 A12345 2 VAMSHI 9765432123 B12367 3 KARTHIK 986623456 C34567 TABLE_B SNO NAME MOBILE EMPID 1...

Execution Plan - Hi, I saw Index scan in execution plan. The table has primary key. We are selecting that PK data based on...

SSIS Package - FTP Task fails when run from a job - We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. I have a SSIS Package...


SQL Server 2012 : SQL Server 2012 - T-SQL

What is wrong with this syntax? Query will work, but CTE will not "compile" - [code="sql"]with UnloadDates as select DISTINCT ShipmentID, (select Min(starttime) from tblInvoice I where I.ShipmentID = O.shipmentID and DataSent is null ) StartTime, (select Max(Endtime) from tblInvoice I where I.ShipmentID = O.shipmentID and DataSent...

Import a data from mysql to mssql - Hi, I have a table in mysql which has 215 millions of rows and I want to migrate those 215 millions...

Find the Numbers from a string - Hi experts, i hope my problem will be solved. I have a string and i want to get only the numbers...


SQL Server 2008 : SQL Server 2008 - General

CHAR column concenation - Hey gang, I'm a little stumped here. I want to concatenate character data from a table. [code="sql"]declare @ttest table (nItem integer not null, ctext varchar(32) not null) insert...

Recreate linked servers from 2000 to 2K8 - Hi all, Is there any way to script this and restore to 2k8? Also, if I script the 2K logins, will their...

trouble shoot - I got error as belwo Description: x:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tData.mdf Operating system error 665(The requested operation could not be completed due to a...

Ideas on how to flag existing user tables for future deprecation - We are in the process of moving about 20 databases to a new data center. As part of that move,...

Use of create statistics - What is the use of create statistics ? I am having a DB With AUTO_CREATE and AUTO_UPDATE for statistics ON, then...

The character string that starts with ... is too long. Maximum length is 4000 - Can anyone tell me if there is a way to get around the 4000 character limit for executing dynamic SQL...

Need Query for summarizing data - Hi there, I need query for the attached output [img]http://www.sqlservercentral.com/Forums/Attachment15316.aspx[/img] [code="sql"] create table #Wholeseller ( wsid varchar(100),[Productid From] int, [Productid To] int, units int) create table #Retailer ( retid...

rebuild index for 1 table at 1 time? - Hi, I have a huge DB with 30 tables out of which 10 are extremely huge tables. From the index physical...

Out Of Memory Exception - Hi All, Application users are getting following error very often and developers think that its SQL server error. But no errors...

Can't understand why some data is repeated - Hi Everyone, I am trying to eliminate repeated data in my results, towards which end I am using the ROW_NUMBER() function,...

Is there a way? - I have many tables that have 1000+ columns, of which about 85% of them are NULL. Is there a way...

Doubt on Dynamic SQL - I have a set of 5 queries as 5 rows in a table. I am looping 1 by 1 and...

Link SQL job history to SSIS log table - The SSIS package is saved at the file system of the SQL server. The job calls the SSIS package. The...

SQL Server Read ismore - Insert into Table Select * from anothertable where somecondition in(select some_condition from thirdtable) While doing this operation i see there are more reads...

Number of columns in table - I've been researching issues on the number of columns in a table vs performance. I have a Customer table, 87 columns,...

how can i set up windows event log notification through mail using SQL server - how can i set up windows event log notification through mail using SQL server. Scenario: i have to audit an specific eventid...

SQL Server on domain or workgroup - Hi Friends, I have very limited knowledge on this topic. I have worked and working on SQL servers on the machines...

Trying to restore a .BAK from another domain - We are trying to restore a .BAK from another domain. There are no trusts created and we are told it...

Get the hierarchy orders(top to bottom) of the table - Can we get the hierarchy orders of table Eg: 10 tables Table1 is master to table2 and so on till table10 Just...

Table Partitioning - I am trying to partition a large table. The table is wide as well as long. There is a clustered...

Partition key should be part of Primary key - Hi, I would like to know, when I'm creating a partition on a table, partition field should be part of...


SQL Server 2008 : T-SQL (SS2K8)

Need assistance with XML - Hello all, I have a table which contains an XML column. This column is set up as "<properties><propertyName1>Value1</propertyName1><propertyName2>Value2</propertyName2>" etc... I also have...

Elegant query needed to combine multi rows into one, thanks - Table: [code="sql"] CREATE TABLE [dbo].[KPI]( [SVP] [varchar](20) NULL, [Wk1] [int] NULL, [Wk2] [int] NULL, [Wk3] [int] NULL, [Wk4] [int] NULL, [Wk5] [int] NULL, [Y] [int] NULL, [Q] [int] NULL, [Wk] [int]...

Simple query I'm struggling with! - I'm using Sybase for this Sample data: [code="sql"]CREATE TABLE #SampleData (ID INT, Tran_Date DATETIME, Ref VARCHAR(10) NULL) INSERT INTO #SampleData (ID, Tran_Date, Ref) SELECT...

What does >+ do? - I Have a question for all you t-sql experts. I made a type in my code that I just noticed...

Embedding a variable into a parameter when calling a stored procedure - HI all, I want to add the result of a Select statement inside a parameter, not too sure how to...

Complex SQL Pivot Query Urgent Help Needed - Hi Guys, I want to convert the data from Original Table to Reporting View like below, I have tried but not...

Query regarding Update the data using application - SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact Contact table contains 4 columns as explained. in the application there is one tab called Contact where...

Table Size Query - Hi Experts, I'm using Ms SQL Server 2008 R2 (Enterprise edition), I need some help in compiling the correct SQL query...


SQL Server 2008 : SQL Server Newbies

Rounding up to fifth decimal - Hi, I have a field that stores 8 decimals, but I want to select this field in a query with the...

Parameters - I am actually working on a report where I need to pass parameters. I need to pass the parameters HoursStated which...

Combining multiple rows into columns - I would like to create a query that will join two tables and have the differences into columns instead of...

How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups??? - 'morning all I've had a report of missing data (restore from full was done, diff wasn't applied) in a db that...

decrypt to date datatype - I accidentally used date data type (rather than datetime) to encrypt a column. As I understood from online research, that...


SQL Server 2008 : SQL Server 2008 High Availability

Windows Server 2012 R2 Clustering Issues - Hi, I am setting up a test Windows Server 2012 R2 VM with SQL Server 2008 R2 cluster. Setting up Windows...


SQL Server 2008 : SQL Server 2008 Administration

Restore History - Time taken for the restores - Hello, I got a requirement from the client asking for database restore duration's for the last couple of months. I can...

Procedure Execution Error - Hi Experts, We are getting below error when tried to execute a procedure.. Msg 18483, Level 14, State 1, Line 1 Could not...

Possible to write a trigger to monitor for specific privileges being granted? - We're working with a customer right now who's looking to move their existing SQL server to our control. Currently, one...

I can't able to restore the Tail Log back up.. - Dear All, Am Using 2008, i have restored the full and Differential backup with norecovery model, But when am trying to restore the...

Recommend me Best tools for monitoring SSIS, SSRS and SSAS - I would like to know best tools for monitoring SSIS, SSRS and SSAS. Thanks.

SQL Server Agent jobs not running as scheduled - Hi all, has anyone encountered sql server agent not actually running the jobs it is schdeduled to run?! It was recently...


Cloud Computing : SQL Azure - Development

Azure Newbie Question - I want to set up a demo SQL Server 2012, Sharepoint 2013, Powerview instance. I want to do BI Development,...


SQL Server 2005 : Administering

Public Sql ser role is missing - Gurus I have no clue but public server role within my sql server is missing,I have all other showing like...

calling sqlwb from batch file. - Darlings ... Potentially dim question but seriously annoying issue. I'm calling sqlwb from a batch file. (Ultimately I will have a bunch...


SQL Server 2005 : Backups

Error 2 opening reg key in sqlboot!GetSkuInfo - I am getting the following error message. Error 2 opening reg key in sqlboot!GetSkuInfo. Reg Key: Software\Microsoft\Microsoft SQL Server\100\DTS\Setup. [32008] Error 2...


SQL Server 2005 : Business Intelligence

OlapEvent = ObjIdUpdate(2) - Hi All When I execute sp_WhoIsActive to see who is active, I constantly see this running select SELECT @OlapEvent = ObjIdUpdate(2) with wait...

Error while exporting data from SQL to Access - Hi, I am developing a SSIS package where I am trying to pull records from SQL server tables and pushing the...


SQL Server 2005 : CLR Integration and Programming.

How to send security headers when calling the web service from clr stored procedure - Hi, I am able to call the web service from clr stored procedure before. Now service provider enabled authentication. Now CLR stored procedure...


SQL Server 2005 : SS2K5 Replication

The row was not found at the Subscriber when applying the replicated command. - Hello, I have the following problem: I have a bidirectional transactional replication on SQL Server 2005 Std Ed. SP 1 . On...


SQL Server 2005 : SQL Server 2005 Performance Tuning

SQLOS: Runnable, Running, Wait List - I was reading a Microsoft White Paper (haven't completed the reading yet) Using the illustration below, why would SPID 56 go...


SQL Server 2005 : SQL Server 2005 Integration Services

Flat File Source Problem in SSIS - Hi, Is any one have/had problem. I have very simple SSIS Package. Flat file source and SQL Destination. No other...

Variable As OLE DB Connection. - Hi Guys, Need Help,I want to know what is the best way to deal this kinda Problem or approach. I...

SSIS Package not failing? - Hello - My SQL Server Agent shows successful completion of SSIS job but messages in Log File view shows that there...


SQL Server 2005 : T-SQL (SS2K5)

Convert Row Data in 1 Field to Many Columns - Hello this is my first post and my TSQL is average at best so any help will be much appreciated I...


SQL Server 2005 : SQL Server Newbies

datediff include 1 hour before midnight - Hi, I need to find all records for all 3 shifts in 1 day and the datediff below does not include...


Reporting Services : Reporting Services

Access SSRS From Multiple Untrusted Domains? - Hi. I'm working with a client who has an SSRS portal that is accessed via web browser. They would like...

How can I access OUTPUT variable of a store procedure in RDL dataset - Is there a way to access OUTPUT variable of a store procedure in RDL dataset. If the store procedure has not...

Report Builder 3.0 Expression count - I have got a dataset which is pulling monitoring counter values for logical drives, ram and cpu for multiple machines....

SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.) - I built a SSRS 2005 report, which calls a stored proc on SQL Server 2005. The proc contains following code: ...

SSRS Interview Questions and Answers - Hi all, Please help me with SSRS interview Questions and Answers. I want to go for the Interview for SSRS and...


Reporting Services : Reporting Services 2005 Development

How to increase the legend size of the charts - Hi, I am new to ssrs. Could anyone help me in finding out option to increase the legend size of the...


Reporting Services : Reporting Services 2008 Development

Calling an SSRS Report from a CRM form's ribbon button with a guid - [b]First, a long-winded description of the situation.[/b] [i] Pretty much basic stuff, really. A report developed in Visual Studio 2K8 w/BIDS extension is...

Link in Report that automatically exports raw data to excel? - Hey everyone, Does anyone know if there is a way to create a link that automatically exports the raw data of...

How to write ssrs expression to sum the amounts from different tables - Hello, I am preparing an ssrs report where I have two tables and each table is using same dataset and...

SSRS Reports Timeout decrease for Standard Subscriptions - Hi, I am trying to reduce the SSRS Time out limit as there are number of reports which runs for very...


Programming : XML

Noob xquery problem - Hi all, I'm still having a hard time writing out xquery statements. I have a better grasp of querying element...


Programming : Powershell

Filtering Get-ChildItem results with -LIKE? - i cannot for the life of me pinpoint my error here. this should be fairly simple: find matching files, and print...


Data Warehousing : Integration Services

Database Mail - I configured Database Mail and enabled Mail Profile in SQL Server Agent but still I dont get any emails to...

Query from Source not sure how to get into Destination - I am using SSIS 2012 and am pulling from a Server in cloud that I have read only rights to....


Database Design : Design Ideas and Questions

[DB Redesign][MSSQL]Need Help About Spliting a single table data flow into multiple tables - Hello, I am a software developer using MSSQL databases in certain of my projects. I Still have basic skills in SQL...


Database Design : Virtualization

Sql 2008, Task Manager, and Hyper-V Guest - I was told that one can not expect reliable results using Task Manager inside a VM (Guest). If that is...

vmware snapshot and database corruption? - Anyone heard that vmware snapshots can cause database corruption on SQL Server databases? The VMware is ESX 4 running Windows...


SQLServerCentral.com : Anything that is NOT about SQL!

Cloud based learning environments - Hi all, I wondered if anyone could help suggest a cloud hosting provider. I am mentoring a young SQL Server newbie and...

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


SQLServerCentral.com : Articles Requested

Changing Cardinality Estimation - A short piece that just discusses how to change the cardinality estimation in SQL Server 2014, using the compatibility level...

SQL Server 2014 Cardinality Estimator - An article that looks at the changes in the cardinality estimator and covers what this can mean for people. Not...

Avoiding Cursors - After seeing this: [url]http://www.sqlservercentral.com/Forums/Topic1546354-391-1.aspx[/url] I'd like to see some articles that look at real situations where you've replaced a cursor (or...

T-SQL Comparing Rates - A writeup based on this thread. Show how this works and why one method may be better than the other [url]http://www.sqlservercentral.com/Forums/Topic1537283-3077-1.aspx[/url]

Export with Headers - How can you export data from a query, using bcp, SQLCMD, etc. and include the column headers at the top?...


SQL Server 7,2000 : T-SQL

redirecting output from SP - Hi, is it possible to redirect all the messaging output from a stored procedure to a variable (or anywhere else) which...


Microsoft Access : Microsoft Access

reference recordsource on subform - I have a form with 3 subform. On Currrent event on subform1, I am getting the ID and then using...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com