In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro 5GB free hosted storage with SQL Backup Pro
Download SQL Backup Pro and securely copy your backups to secure hosted storage. You’ll get your first 5GB of storage free. Try it now.
 
SQL Search How do you search your database schema?
"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.
 
SQL Monitor Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

In This Issue

Indexed Views

This article will show you the importance of Indexed Views and how they can help performance. More »


SQL Server Simple and Forced Parameterization

I have heard about parameterization for SQL Server queries, but what is Forced and Simple Parameterization and which one should I use for my SQL Server database? More »


Job overran Custom Metric

This SQL Monitor custom metric tells you if a specific SQL Server Agent job that runs at the same time every day has overrun. The metric is useful when a job can have negative effects on other processes if it overruns. More »


From the SQLServerCentral Blogs - Database Administration KPIs – 2/2

In my last post, I discussed about the quantitative KPIs which, to me, are useful for “outsiders”, i.e. non-DBAs, to... More »


Editorial - Visualizations

They say a picture is worth a thousand words. An image can convey an incredible amount of information, though the interpretation can vary widely depending on the viewer's perspective. However images can often condense information into a much smaller space. One that is often clearer to many people than a large spreadsheet or numbers or pages of words.

Visualizations can help us understand and consume a large amount of data quickly. They don't always provide all the information we need for a decision, but they make it easier for us to understand where we should look deeper. There's a great piece on how visualization tools help us understand big data, and also a tour of visualization methods (thanks to Brent Ozar, PLF). 

Many of us are working with larger and larger amounts of data, and being asked to help business people find tools or methods to work with data. We're also writing lots of reports to extract information, and I wanted to ask this question:

What visualization methods do you prefer working with?

If you have more than one, let us know, and if you have preferences for certain types of data or analysis, let us know. If you've found some techniques that don't work well or mislead people, let us know about those as well.

Images, animation, color, and more can really improve the transfer of information, but it's not always as useful as we might think. One of the points brought up in the editorial linked above is that our tools and visualizations need to account for uncertainty in the data quality. I don't know what methods and tools we'll use in the future, but I do know that our choices continue to grow in the SQL Server space as new tools like Power View and Geoflow are released.

» 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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

What will be the data type assigned for the columns in the temp table when to run this below query? Please select answer.

select * into #temp from 
(
 select NULL col1, NULL col2 
 union all 
 select null, 'Que'
) A

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

This question is worth 1 point in this category: NULL. 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.


Yesterday's Question of the Day

create table address_staging  
 (clientid int primary key,addressdetails varchar(250));

insert into address_staging
 select 100,'hyderbad,india'
 union all
 select 101,'banglore,india'
 union all
 select 102,'banglore,india'
;
create table address_oltp 
 (client_id int primary key,address_details varchar(250));

insert into address_oltp
 select 104,'newyork,usa'
 union all
 select 105,'chicago,usa'
 union all
 select 106,'washington,usa'
;
select *
 from address_oltp 
 where client_id in (select client_id from address_staging)

drop table address_oltp;
drop table address_staging;

How many rows are returned from the last SELECT?

Answer: 3 rows from address_oltp

Explanation: It will fetch all the values from outer query as inner query referring the same column from outer query .If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

Ref:

» 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

Another way to track the database data growth

Scripts will display size of the data in the database/data files and remaining allocated space. 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

Login Failed error (Linked server) - I am trying to access data through linked server by following query select * from [Servername].dbname.schema.view_name and my schema is not dbo. When I...

Maintenance Plans - Modify does not work !? - Hi all, I have created several Maintenance Plans in the past - I now want to modify one of them ..... So a...

Stuck with this update statement - I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120....

Database Mail & BROKER_RECEIVE_WAITFOR - Hi I have database mail setup and when I send an email via dbmail to myself for example, I see a...

Configuration of Agent XP brings down server - Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. Next alert Only administrators may connect...

SQL Server 2005 : Business Intelligence

How to Compare Data between to servers based in Id's - Hi All, I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass...

expression output - Hi, can any body tell the output for expression by taking any 2 dates as example. =cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1) Thanks, Niha

SSAS Calendar Week commencing with split at month - Hi all, Wonder if someone can help. Our CFO wants to see a cube calendar that shows week commencing but stops at...

SQL Server 2005 : SQL Server 2005 General Discussion

Create BAT File and execute the same in Trigger - Dear All, I am situation, where we have a table named as [b]Project[/b], columns for the table as follows: [code]------------------------------------------ ID | ClientCode | ProjectName ------------------------------------------ ...

A way to auto disconnect all connections to a database in SQLsrv2005? And self intro - Hi. I am new to the forums. Thank you for having me. I have provided some bio info in my profile for those...

SQL Server 2005 : SQL Server 2005 Integration Services

ssis2008 package containig script task failed to run as a job in sql server - I tried to ran a package containing simple script task having messagebox.show(string) in sql server agent as a job using...

ssis2008 package containig script task failed to run as a job in sql server - I tried to ran a package containing simple script task having messagebox.show(string) in sql server agent as a job using...

SSIS Package Optimization - Folks, We are taking Oracle Source data using Data Flow Task in which Data Reader as Source [We have Separate DSN...

using constraint and expression precedent constraint editor - can I get proper example on use of constraint and expressions in prcedent constraint in ssis 2008 ???

How to read data in a pdf file in SSIS - I was wondering if any body had a situation where data needs to be extracted from pdf files and exported...

SQL Server 2005 : T-SQL (SS2K5)

query help to find breakdown of operations - Hi All. Below is an example table of what i need to query out of: Patient Name|| Patient No.|| Age|| Cancer||Consultant||...

Can a CURSOR be populated by firing a stored procedure? - The subject says it all. Can I do this? DECLARE Test AS CURSOR LOCAL FOR [b]EXECUTE uspMyStoredProcedure[/b] OPEN Test FETCH Next FROM Test...

SQL Server 2005 : SQL Server Newbies

query to find average age and percentage of patients with something - Hi All. Below is an example table of what i need to query out of: Patient Name, Patient No., Age, Cancer,...

Relationship between two tables (what is correct?) - Hi, can someone help me with this basic doubt? "tnx in advance" I have two tables (groups, rol), I need to...

Alert Database creation - HI Expertz Can anyone please tell me how to create an alert (send mail) when a new database is created and...

SQL Server 7,2000 : T-SQL

SQL statement for new vs duplicate data - I'm looking for a SQL statement that will output 'new vs duplicate' data on a monthly bases, but I can't...

SQL Server 2008 : SQL Server 2008 - General

creating XML from SQL query problem - Hi All, I have a problem trying add a element with an illegal character. I have the following SQL [code="sql"]SELECT 'en-GB' AS...

Need output - Hi. create table #variableta ( assessment1 varchar(114), assessment2 varchar(114), assessment3 varchar(114), assessment4 varchar(114), asessment_pt1 varchar(114), asessment_pt2 varchar(114), asessment_pt3 varchar(114), asessment_pt4 varchar(114), code1 varchar(20), code2 varchar(20), code3 varchar(20), code4 varchar(30) ) insert into #variableta (txt_bh_assessmen

Attaching database - Hi All, I downloaded the adventureworks2012 database and followed the normal procedure to attach database. I placed the mdf and...

Query to Search the Data - Hi, My requirement is,I want to search records,in my application, Example, Lnno Name Code SNo This are the Text box available on the screen where user...

CTE Recursive - Hi I have a query below and I will explain what I wish to archive. I have two methods that I...

Simple 'front ends' for non-developer?! - Guys, I've been thinking of a few different ways to tackle a new project, using Excel, third party web-based tools, some...

Additional Fields in ssrs to be displayed at the end of the report - Hi everyone newbie here... :) okay so I have this question .... is it possible to display additional fields at the end of...

excluding CDC enabled tables during a full backup of db - I have a database with approximately 4000 tables on SQL 2008R2 x64 Of those tables, approximately 70 are tracked by CDC...

Databse auditing and management of audit logs - Need assistance on the following : a) What are the common SQL database events/activities that should be audited? b) How long should...

Cannot delete a SQL Server login - I've got the following error when I try to delete a login using MS SQL Server Management Studio: Drop failed for...

do Distinct, Group By, Order By & Union (without All) - all take the same hit on Sort? Is there any way to mitigate that or conditions that make one cheaper than...

Can someone tell me what's happening with this query? - Hello, I'm seeing a difference in a query between SQL 2005 and SQL 2008 and I'm not understanding. The below demonstrates...

CmdExec Job Step just hangs - Hello. Anyone seen a CmdExec job step hang before? When I manually invoke the job, nothing but the spinning green...

Best method of handling optional parameters in WHERE clause - Hi all, I'm trying to determine what the preferred method is for handling optional parameters in WHERE clauses from a theory...

SSIS Data flow failing - I have a data flow that has been working for months. Starting this week it will transfer some of the...

DB400 to SQL Server data extraction - Hi all, We have a DB400 (DB2 based AS400 platform) database and I'd like to pull data from it using SQL...

Cannot get to install SQL 2008 R2 on Windows 2003 x64 - Hi, Error is: Could not load file or assembly 'Microsoft.SqlServer.Configuration.WizardFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was...

find pairs of NULL records without resorting to CURSORS - [code="sql"] create table #tmp ( ref int, Date datetime, time int, seqno int ) insert into #tmp (ref,date,time,seqno) values(34227664,'20130227',0,399850) insert into #tmp (ref,date,time,seqno) values(34227664,'20130304',0,399860) insert into #tmp (ref,date,time,seqno) values(34227664,'20130313',0,399870) insert into...

Help with SQL Server backups slow ( backup performance slower than before after data purge) - Hi everyone, (searched SQL Central... I didn't find anything specific) Wanted to get some ideas on solutions. I have a DB...

Saving Circular and Rectangular path in geography data type - Hi all, I have an interface (google map) where i allow my users to define an area. Google map provides rectangle,...

Using SQL Management Query in Visual Studio - I am trying to use a query that works in SQL Management in Visual Studio but the code it not...

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

Has anyone used Data Domain storage for SQL 2008 backups? - We are creating a Microsoft Failover Cluster with Windows Server 2008 and SQL Server 2008. Our storage administrator would like...

SQL Server 2008 : T-SQL (SS2K8)

Differnet Join Statement - Hi guru's Thanks for your support this is my following situation [code="sql"] DECLARE @A as TABLE(Eid VARCHAR(5),DocNo VARCHAR(5)) DECLARE @B as TABLE(Cid...

Query Help - Hi,I required help in creating this type query Create table #temp (ID int,VoucherNo varchar(10), Status varchar(10), VoucherType int) Insert into #temp Values...

SQL Server system tables - Hi, I am familiar with SQL and have created queries. With TSQL the problem I'm having is I'm not sure which SQL...

how to concatenate o as perfix in case statment ? - Hi All, select Case when MONTH(GETDATE())=1 then 12 when LEN(MONTH(GETDATE()))=2 then MONTH(GETDATE()) [b]else right('0'+ convert(varchar(2), MONTH(GETDATE())),4) end[/b] In the above query else statment will...

Query Improvement - When I executet his query it takes a lot of time...Can anyone plz help in this? SELECT id, english_name FROM ys_movements WHERE is_private...

Major Challenge - Too Hard to Summarize - I have two tables. Table #1 CREATE TABLE [XXX].[SLHIH]( [WorkDate] [date] NOT NULL, [BusinessUnit] [varchar](5) NOT NULL, [ContactType] [varchar](5) NULL, [TotalContacts] [int] NULL, [SIH] [bigint] NULL, [SLEligible]...

Join creating two records - I have a join that is doing something that I can not explain. I have two tables.. linking on Delivery No...

Query seems to be constrained by single core CPU in multiple core server - any suggestions? - I receive data from an external data provider which comes in as a single large table. The data represents balance...

Why XML Datatype present in SQL Server. - Hi all experts, Every now and then i am learning something new in SQL Server. Latest in that series is XML...

LEFT and RIGHT of Delimiter - I have a value that is '0111~Group Name' in a column called GROUP_NUM in a table called TEMP_TABLE I need to...

SQL Server 2008 : SQL Server Newbies

Migrating from Access to SQL 2008 - Can someone explain the 3 different options in the upsizing wizard? 1) Create a new Access client/server application 2) Link SQL server...

must declare the scalar variable! - hello every body. I have a question about declaring variable in sql server 2012. when i declare a variable for example : [code="other"]...

Find Orphan records in a table - I have a table which follows a hierarchy and I am trying to find the orphan records. The table has two...

Creating a hierarchy group from one table column containing both parent and child hierarchy info - Hi I need help creating a hierarchy group 'using' the data in [b]Event_Role[/b] column in my sample table to create a...

Database Design Best Practice for Generic Objects - I have general question regarding design practices for generic child objects which may belong to many different types of parent...

Updated muliple rows into one column? Why is it hard? HELP - Trying to update a table using integer number in 3 columns and a signing texts name to results in the...

Maintenance cleanup task 0k button grayed out - Recently added a new maintenance cleanup task to an instance (SQL Server 2008). While the task worked I decided that...

SQL Server 2008 : Security (SS2K8)

sql agent not shown in ssms after running ssis package as a job - Hello, After running ssis package as a job on schedule I am not able to see sql server agent in ssms...

SQL Server 2008 Audit_Amazing_Expecting Comments - We enabled SQL Server external audit feature in our production server. We enabled only DML(Insert,Update,Delete) single audit event instead of...

SQL Server 2008 : SQL Server 2008 High Availability

How to find and kill Global Variables from tempdb - Here in my sql server Temp db is full and there is no disk space so here we are going to...

selection dataset - Hello Friends I want this type output.Here I want in first column how many members are join today(registration),and in second column...

Async Mirroring - Large unsent log, but status is still "Synchronized" - I have a database mirrored using async mirroring. Last night "something" happened to cause a large amount of unsent log on...

Restore is not happening - We have configured the log shipping. here backup job and copy jobs are working fine but restoring is not happening in...

Failover Clustering and DTC - I am installing SQL Server 2008 r2 SP1 on a 64-BIT Windows Server 2008 r2 SP1 Enterprise server in an...

SQL Server 2008 : SQL Server 2008 Administration

Plan_Guide not working for cross DB queries - Investigation on longer running queries has shown high compile times for some adhoc queries sent from a front end app. I...

Change SQL Notification subject - Hi All, SQL Server Job System: 'TESTJOB' completed on \\Server_Name I wanted to see if I can modify this to send e-mails...

model database won't restore - This is going to be a /facepalm moment I just know it as I'm sure I'm making a schoolboy error...

Restore of ReportServer and ReportServertempdb - I have to convert a named instance to default instance before that i have taken backups of all databases including...

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

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

EMC RecoverPoint and SQL Server - We are trying to implement EMC RecoverPoint with SQL clustering. What we had to do at the DR side after syncing...

SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Format date within Tooltip - Hi i have a tooltiip which is as follows: [b]="Current Age = " & DateDiff("yyyy",Fields!Dob_dte.Value,Now()) & " : DOB = " & Fields!Dob_dte.Value[/b] EXAMPLE OF Fields!Dob_dte.Value = [i]'1943-05-24 00:00:00.000'[/i] When viewing the tooltip in...

Left function in SSRS - Hi Members, I have a SSRS report which displays a field value based on a expression. The expression uses left function....

SSRS Report breaking with an error "System.UriFormatException: Invalid URI: The Uri string is too long." - Need quick help on this issue. Environment Details: Reporting server migrated from 2005 to 2012. But we haven't migrated rdls and these...

Reporting Services : Reporting Services 2005 Development

Line hight for barcode - font 3 of 9 Barcode - I am developing a GRN label print report and have installed font 3 of 9 Barcode. I want the barcode...

Data Warehousing : Integration Services

Whats the easiest way to read the first 10 characters of a flat file - Basically I am recieveing multiple flat files of two different file layouts, the file names do not give a clue...

Importing in ssis when a file is of a certain size - Hi All, I am creating packages that import xml files. however I want to import files that are more than 213kb...

Execute Process Task using WinSCP - Hi , I have to transfer file from sftp site to my local server on E:\ecol\ . I have used 3rd party...

How to reset metadata in SSIS packages - When working with SSIS and source or destination metadata changes SSIS does not reset the metadata it deletes the changed...

Data Warehousing : Strategies and Ideas

Strategies for dealing with Excel - Like a lot of organisations, we struggle with the fact that a lot of users manage important business data in...

Data Warehousing : Analysis Services

Adding a subtotal value to an existing dimension (beginner!) - I have created my first datacube and it has a dimension defined as [Cost Centre Structure], i have an attribute...

Microsoft Access : Microsoft Access

Permissions for view based on Linked Server - Hi, I have an access 2010 adp linked to SQL 2008 r2. Another system we use which also has a sql backend...