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.
 
Deployment Manager Deploying a SQL Server or .NET app tomorrow? Sleep easy tonight
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.

In This Issue

The Cascading (CROSS) APPLY

An old trick nowadays but one which is still underused here on SSC is the cascading CROSS APPLY – where output from one CROSS APPLY is used as input for another. More »


SQL Server Central Webinar Series #23: Safeguard your data offsite with SQL Backup Pro

You may already have a great backup and recovery plan. However, if something were to happen to your databases and you needed to restore from your backups, you’d want to be in control, with the ability to access a copy of those backups quickly, restoring them with minimal downtime and minimal fuss. In this session Grant Fritchey, SQL Server MVP, will discuss the key reasons why you need to have offsite backups, and the advantages of hosted storage. He’ll address some of the fears surrounding cloud backups, and show how offsite backup is made quick and easy with the new ‘backup to hosted storage’ features in SQL Backup Pro 7.3.  More »


T-SQL Coding Style

When you write T-SQL code what kind of coding style do you use? Do you have a consistent way to comment and format your code? Does your organization provide standards for how you must code your T-SQL? Join Greg Larsen as he discusses some of the coding styles you should consider when writing your T-SQL scripts and stored procedures. More »


From the SQLServerCentral Blogs - SQL Server – Generate Calendar using TSQL

Introduction Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011.... More »


Editorial - Don't Be a Ghost

I talk about branding at many different events, with the idea that you should control your personal career brand, and make an effort to improve it. As more and more employers look to validate your experience and verify your skills, having a strong brand helps you to stand out amongst the competition. The cost of a bad hire is high, and companies are trying different things all the time to reduce the chance they'll make a bad hire.

I'm not the only one that thinks you should have some brand out there. Troy Hunt wrote a piece about being a ghost programmer and how it hurts your career. It basically talks about how a programmer that doesn't leave any kind of trail on the Internet is not likely one you want to hire. There are all sorts of reasons someone might not have a blog, or participate on a site like Ask SQLServerCentral, but many of the reasons are signs that a person isn't really working efficiently. I'm not sure I completely agree with this interpretation, but I do think that the less information a potential employer can find about you, the more reason they might have to ignore your resume and move on to someone who they can perform more due diligence on.

I completely understand the desire for many people to keep their lives private. There's nothing wrong with that, though it can be detrimental to your job search. However you can overcome some of this, but maintaining some documentation about your projects, skills, thoughts, and providing that along with your resume, or as additional proof to your resume when requested. You can even use anonymous handles on the Internet, providing them in the same manner you have "references upon request" on your resume. They can be sent in an instant, but they aren't public. I'd even make a note on my resume to that effect, if I were inclined to maintain more privacy about my career.

You don't have to be a Jon Skeet or an Iris Classon, but you should have some information you can share about your career when you are looking for a job. If you don't, you might never know how much it impacts your job search. After all, is your phone not ringing because you aren't qualified or employers just don't know if you are?

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


create table #temp (SomeText varchar(20), OtherText varchar(10))

insert into #temp 
select 'SomeText1', NULL
union all
select NULL, 'OText1'
union all
select 'SomeText2', 'OText2'
union all
select 'SomeText3', 'OText3'
union all
select 'SomeText3', 'OText3'

select COUNT(*) from #temp
select COUNT(1) from #temp
select COUNT(SomeText) from #temp  
select COUNT(All SomeText) from #temp 
select COUNT(distinct 1) from #temp 
select COUNT(distinct SomeText) from #temp 
select COUNT('T') from #temp
select COUNT(convert(int,NULL)) from #temp

drop table #temp 
In above query COUNT() function is being used in different ways. What will the count of rows each statement returns from #temp? Please give output of each 8 count statements in sequence as your best answer from below. 

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

This question is worth 2 points in this category: Count. 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 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Yesterday's Question of the Day

Below are two tables. What will be the count of rows returned by all 5 queries?

CREATE TABLE TableA
(
  ID INT
, Country VARCHAR(20)
)
;
GO
CREATE TABLE TableB
(
  ID INT
, Country VARCHAR(20)
)
;
GO
INSERT TableA SELECT 1, 'India';
INSERT TableA SELECT 1, 'US';
INSERT TableB SELECT 1, 'India';
INSERT TableB SELECT 1, 'US';
GO
select COUNT(*)
 from TableA 
  join TableB
   on TableA.ID = TableB.ID 
;
select COUNT(*)
 from TableA 
  left join TableB
   on TableA.ID = TableB.ID 
;
select COUNT(*)
 from TableA 
  right join TableB 
   on TableA.ID = TableB.ID 
;
select COUNT(*) 
 from TableA 
  full join TableB 
   on TableA.ID = TableB.ID 
;
select COUNT(*)
 from TableA,TableB
;
GO
DROP TABLE TableA;
DROP TABLE TableB;

Answer: 4,4,4,4,4

Explanation: All the queries will return same count. The first 4 queries will return all matching rows based on condition which is ID. So TableA 1st row ID 1 matches with 2 rows in TableB and same applies for 2nd row ID 1, which returns (2+2 = 4) rows

In last query Cross Join is used which will return Cartesian products.Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table.

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

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Restore Latest Backup with Powershell

Restores the latest backup of a database to a destination server of your choice, and creates a new backup if needed. 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

PBM on Sql Server 2005 ? - Hi All, I have a few critical sql 2005 production servers and I have been asked to try implement policies on...

Index Rebuild Taking too Long? - I have an instance running the following version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c)...

"..table "ItemNum" cannot be reorganized because page level locking is disabled" - I get this error message when I attempt to backup a sql 2005 database. The databse is set to Simple...

SQL Server 2005 : Business Intelligence

simple web based reporting - I’m looking for a simple web based tool that can be used to generate tabular reports. Our application has a...

SQL Server 2005 : CLR Integration and Programming.

RegEx CC - Hi Guys, What I need to do is find rows that may have credit card in the text. Here is the...

SQL Server 2005 : Data Corruption

Table data Corrupted - While Fetching record from a table we are getting below error for few records. Error:-Msg 823, Level 24, State 2,...

SQL Server 2005 : Development

can I use while loop for only one column? - Hi All, Please help me on below scenario. I am having application where user selects startdate and enddate from his application(front end)....

SQL Server 2005 : SQL Server 2005 General Discussion

Dropping Indexes after comparing Index Definition - Hello, I need to update all the indexes in my database through C# installer. I am using below queries to drop...

SQL Server 2005 : SS2K5 Replication

CPU Intensive Enumerating Changes - We've recently moved our replication system to a new server, and spent some time creating new indexes to help performance....

SQL Server 2005 : SQL Server 2005 Performance Tuning

Memory usage not matching up with that shown in DMV's - I have a sql 2005 64bit instance on a 64bit windows server. The Server has 32Gb RAM Total with 26GB set...

Max server memory setting - In one of the server we have defined 'max server memory' below is the server configuration OS = windows server 2003 enterprise...

SQL Server 2005 : SQL Server 2005 Integration Services

dynamic excel columns into dynamic table - I have excel with dynamic column and i want to load into table creating dynamic columns. I need to add new...

Sequence container not to fail if a task fails inside it - hi All, Just need a steer...in that i have a sequence container with 3 tasks i need to go down the failure...

SQL Server 2005 : T-SQL (SS2K5)

Assign Validation Codes - Here's the scenario. Table CustomersA: RowID CustomerName CustomerID CreationDate CreatedBy Validation 1 Vam 000245 04/01/2013 Cris 2 Vam 000245 04/01/2013 Cris 3 Vam 000245 04/02/2013 Cris 4 Vam 000245 04/01/2013 ted Table ParametersB: Name Lock_Count Customer

userdefined function for average - Hi guys, I tried to create a new userdefined function for calculating the average of top 10 values on different columns...

SQL Server 2005 : SQL Server Newbies

How to display 2D barcodes in SSRS reports - The [url=http://www.avapose.com/dotnet_barcode_reporting_service/2d_barcodes.shtml]SQL Reporting Services 2D Barcode Generator Control[/url] by Avapose.com is able to add 2D barcode generation function into Reporting...

SQL Server 7,2000 : Administration

require db monitor script for sql 2000 - Hi, Need help in writing a script for sql server 2000 to check the status of all db's in the instance...

SQL Server 7,2000 : Performance Tuning

Need help optimizing a query - Just fair warning, I am definitely a newbie, I fell into this work so to speak. I have a query that...

SQL Server 7,2000 : T-SQL

Find Maximum Consecutive Years In a Series - I am trying to solve a problem of how to find the maximum count of consecutive years in a series...

SQL Server 2008 : SQL Server 2008 - General

Exceeding 25 replication agents on an instance - Using SQL Standard Edition (2005, 2008) the server is not able to startup more than 25 replication agents. We've run into...

Reinitialize log shipping in sql server 2008 R2 after activate Secondary server DB - Hi All, I have set up logshipping between server A (Primary) and server B (Secondary), and tested log shipping by...

Using linked server or create another connection string? - In my one asp.net app, I created a connection string to ServerA and then created a linked server from ServerA...

Sqls2k8 R2 maximum database size - I have a data about 250Gb to 400gb.. And now i have doubt about which database i have use to...

Update query help - Hi, I need an update query which will get the 'scenario' and 'actType' values from all the rows in column strComment...

Procedure Execution Plans - Hi all, I have a question regarding the best way to approach a situation where an application user can query a...

Cross Server Dictionary - I guess this post is not a 'question' per se, but more just me rambling on about my approach so...

Is there a better way to insert data than Access? - Is there a better tool to insert data into SQLserver than Access ? For managers. Small number of people. Very limited...

Is there a better way to insert data than Access? - Is there a better tool to insert data than Access ?

Need Link - Can anyone provide link for :Visual Studio 2005 as i am unable to find it

Views + OUTER APPLY - Hey, SQL Views don't seem to like OUTER APPLY, so is there an alternative (I know they work, just not in...

pre requisities to install cluster - What are the pre requisites to install cluster in sql 2008

Help using like operator - I have a table create table accounts ( accname varchar(100), recstatus bit ) and records are as follows: Insert into accounts values ('Pepsi Co',0) Insert into accounts values...

Combine data from 2 tables and insert in another table - I have following 3 table definition with data [code="sql"] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [voucherDr]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix]...

LOB and OLTP - Hi Guys, I have see people talking about relational databases as OLTP but now I have started to ear people talk...

DBCC CHECKDB Failing - Hi, We are running DBCC CHECKDB WITH ALL_ERRORMSGS. After couple of minutes, the execution fails, sql service crashes and we get the...

sql:space at the end of string - I have a sql code like shown below declare @str nvarchar(max),@i int set @i=0 set @str='abc ' declare @tbl Table(a nvarchar(max)) insert @tbl select...

Building a SQL Server test lab - Hi, I would like to set up a test lab where I can mimic SQL Server environments in a basic way....

Dynamic SQL in stored procedure doesn't return result set; when run in SSMS I get results - I have a stored procedure that builds a dynamic sql and executes it. It gives no error messages, but it...

error string to datetime - where to set localisation for speific database ? - Hi, we have the following problem in our app (Sage OfficeLine): when changing a record, the app tries to write a record...

Database Query - Hi, I have couple of questions related to sql server, though I googled it, did not get any definite answer. I...

Copy a table from one db to another including all configurations - How to copy a table from one db to another including all configurations, such as index, primary key... I use select...

SSIS list file names in directory - Hello All, I am trying to create an SSIS package that will look at a directory, get a list of all...

Turn off Predictive Typing - Hi, There does anyone know how to turn of the Predictive Typing feature in SQL Server 2008 and 2008 R2 when...

SQL Server 2008 : T-SQL (SS2K8)

How the ssrs expressions works for DateDiff? - I'm working on SSRS reports there is on column i.e Holding period where we actually calculate for how many days the...

Group of Groups - Hello All, Thanks if you would like to help. I am hoping there is a 'best practice' way to solve this problem...

Using pivot in sql - Hello, I have a sql table as below AutoId PID ItemId Item ItemValue Date 7560 432 1 Wbc 4 05/05/12 00:00 7561 432 2 HCT 4 05/05/12 00:00 7562 ...

CROSS APPLY - Logical Query Processing - I have a question about when a CROSS APPLY is processed in a query Using the following code as an example: [code="other"] DECLARE...

Arithmetic overflow error - double join? - Guys, I've created a temp table which has a bunch of IDs and dates, something dead simple like this: [quote]SELECT Pg.ID, pg.Name, COUNT(pa.Log_ID) as A, FROM...

Alternative UPDATE Syntax - Hi all, I have a question about the UPDATE syntax. There are the following - fictive - tables: Table ParamValues: [code="other"] PV_ChangeDate PV_ChangeUser PV_ID PV_Item PV_PK_ID PV_IntValue -----------------------...

insert recursive days data into table. - Hi, Please guide me on following scenario. In one of my application i am giviving provision to user select startdate and enddate...

Dynamic Date Columns - Is it possible to create dynamic dates with SQL. I want to display all the dates for a particular date...

Urgent Query help needed- calculate one field based on other field in SELECT statment - Hi Friends, I have reporting TSQL query- in this query within the select statement I have 2 fields 1) Age (working...

Query help needed - Like a Pivot with generic headings? - [code="sql"]CREATE TABLE [dbo].[mike_inventory]( [itemid] [int] NULL, [location] [varchar](50) NULL[/code] [code="sql"]insert into mike_inventory values (10131,'Row A') insert into mike_inventory values (10131,'Row B') insert into mike_inventory values...

something like PIVOT? - I have a table that stores a tablename, the last time the table was accessed and when that data was...

Question about query hint nolock - Hi people, I have a question about nolock. I work in a company that there is a rule: all select query...

Multiple DMLS in one when clause of MERGE Statement - Hi All, I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But...

CTE - UNION ALL - GROUP BY - Today I got a mail from one of our developers. It is a little complicated to explain and I cannot...

Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt - Can someone help me understand why this is happening: If I put the following in a where clause: CASE WHEN PATINDEX('%[^0-9 ]%',h.oe_po_no) = 0...

Begginer Sub Query problem - //EDIT: I've posted in the wrong board, I should have posted in the General Board, ut I don't know how...

t-sql trimming before and after stored proc name - Hi, I have requirement to get only stored proc name and trim everything before and after stored procedure name. Here...

please help with cursor syntax - I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor...

How to call a batch file to execute from an SP - Hi All, Need your assistance please, I am not very good with scripting. I have created a draft of SP, and I...

SSMS Query Logging - We have a user who has come from an environment (SAS) where all of the queries she ran were logged,...

SQL Server 2008 : SQL Server Newbies

Date Intervals like YTD - My requirement is,we have a drop down in our application with YTD,QTD,MTD YTD--Year To Till Date. QTD--Quarterly To Till Date. MTD--Monthly To Till...

Find client accounts missing a specific service - I am looking for the right way to determine a missing service from all similar client accounts. We have six...

MSSQL Sql Not working or partially working - Hi All and thanks in advanced. I Have in my MSSQL Database a Table named TIME_OFF_CODES , in where I have the...

how to query MySQL from SqlServer 2K8R2 - Hi all, Could someone please share a link or article to help me setup a way to query MySQL database from...

How to switch ID number to new values (but maintaining duplicates for new ones) - Hello!!! I need a database from an Insurance company, but they don't want to give it because it has a column...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL SSRS 2008 - Where are the Report Properties for setting the default Start and End Date and Calendar in the Report Thank You from...

SQL Server 2008 : Security (SS2K8)

Stored Procedures Execute Permissions - My global office programmed all our stored procedures and it contains a lot of create/delete temp tables, truncate tables, update,...

REVOKE ability to GRANT - I want to revoke the ability for a particular user (who owns a schema) to grant permissions on objects in...

SQL Server 2008 : SQL Server 2008 High Availability

Locking problem while initializing the replication - Hello, I have a question for the replication experts here: This morning replication has stopped because of a reboot of the Subscription...

SQL Server 2008 : SQL Server 2008 Administration

dbWarden questions - Hello -- I am trying out the dbWarden application by running it within the 2008 Standard R2 SQL Studio application. It...

Restore Transactional Log Backup - Hi, I am new to this forum.Could anyone help me with this questions please. I am not able to resore my...

CPU on SQL server - Our .net application runs slow from a couple of weeks ago, it is using the SQL database, I see the...

No performance gain on queries on different servers with considerable hardware change - I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied....

Simple Replication Question - I am doing some testing with Replication, I have not used much replication before. I have set up a Subscription that...

Create DML trigger for single table in SQL 2008 and send email alert to group - Hi, i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send...

SQL Server Jobs, status, duration running, step - Hello, I need a help to pull information about SQL Server Agent jobs running on multiple instaces. (I am using Powershell) I...

Encrypted File system(EFS) in sql Server 2008 - Hi, how can i impleted EFS in sql level user in sql server 2008r2..?is it possiable please help me..

Programming : Connecting

Connecting to Excel Macro Connection via Windows 7 Task Scheduler - Hello, I am currently running an SSIS package that connects to an Excel Macro enabled file and loads these records to...

Reporting Services : Reporting Services

update parameter changes as soon as another parameter - I have a parameter in vai get today's date and have another parameter that will get the date one year...

Data Warehousing : Integration Services

Rowcount incorrect in Execute SQL Task - I think I'm running into some sort of table lock issue within my SSIS package. I have a Data Flow...

Create or modify a mapping in a DFT depending on the column value - Hi friends, I already asked this question some weeks ago, but how would you add a column to a DFT depending...

Load dim Table using Lookup - Hi, I am a newbie currently trying handson on desinging SSIS and Star schema. I have 2 tables Location (LocID, LocName) and...

How to Prevent Parallel execution ? - Hi Guys, I need some help. I have a package which has two sequencontainers. One has data flow task & other an...

Merge Statement using Execute SQL Task - A deployment issue - Hi friends, [b]Background:[/b]I'm facing the same problem across different projects and I’m not able to find an appropriate solution: When I want...

Data Warehousing : Strategies and Ideas

Data Warehouse Models - Someone from my company's strategy has said that the Data Warehouse model needs to be: - Atomic - 3rd NF - Relational But I...

Data Warehousing : Analysis Services

How to Monitor SSAS - We migrated from 2005 to 2008 and have several cubes. Despite having a better server, our performace has gone way...

Computing on a cube? - Hallo, I'm totally new on computing on SSAS, and quite new to SSAS in general. I'd like to get some index...