In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Connect your existing source control system to SQL Server
SSMS plug-in connects SVN, TFS, Git, Hg and all others to SQL Server. No source control system needed to evaluate. Learn more.
 
SQL Storage Compress Compress live data by 73% 
Red Gate's SQL Storage Compress reduces the size of live SQL Server databases, saving you disk space and storage costs. Learn more.
 
SQL in the City Get free SQL Server training from industry-leading experts and MVPs,
as SQL in the City tours six US cities between September 28 and November 5. Find out more and register for your local event.

In This Issue

Key Word Searches

This article from Todd Fifield shows a Way to avoid the dreaded LIKE operator in your queries and dramatically speed up keyword searches. More »


Red Gate Software announces speaker line up for US SQL in the City tour

SQL in the City is a free, full day training and networking event for database professionals. After the success of last year’s event, Red Gate has expanded the event to cover six cities from sea to shining sea, including: New York, Austin, San Francisco, Chicago, Boston, and Seattle. More »


SQL Saturday #157 - San Diego

Southern California isn't all beach time. SQL Saturday comes to San Diego on Sept 15, 2012. Join fellow SQL Server pros for a day of learning. More »


Practical PowerShell for SQL Server Developers and DBAs – Part 2

Having shown just how useful PowerShell can be for DBAs in executing queries, Michael Sorens now takes us through navigating SQL Server space and finding meta-information - valuable information for anyone looking to be more productive in SQL Server. More »


From the SQLServerCentral Blogs - The Charles River – SQL in the City Boston

I once rowed in the Head of the Charles Regatta. I had the 3d seat, starboard, in a 4 man... More »


Editorial - The Dream Machine

Hardware is usually important to techies. I see now shortage of discussions and debates about this laptop or that. I see people asking about this machine or that ( the W530 looks niiiiiiiccccceee) and as various Surface tablets are announced, I often find people blogging about what they like or debating the merits on Twitter. In the SQL Server world, there are a few very impressive hardware setups at people's homes.

I ran across this post about life at Stripe and there was one benefit listed that caught my eye. In the post, the author says "New employees are asked to send in specifications for their dream machine, which is then built and waiting for them on their first day." I think that's a great benefit, and it shows that the company values their employees and wants to ensure they start off with a little excitement.

This Friday I wanted to ask you what you'd do if that were offered to you. Most of us working for corporations get the same type of machine as all other technical employees, but imagine that isn't the case.

What machine would you specify if your boss gave you the choice?

Personally I like my MacBook Air. It's small, light, comes on instantly, and I find myself really enjoying OSX. The one thing it lacks is some horsepower, and if I need more I'd have to look at either one of the new Macbook Pros or the Lenovo 530. The former is very tempting right now, but the latter would allow me to replace my desktop as well since it can drive four external monitors.

If you could spec out your machine when starting a new job tomorrow, what would you want?

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

If I run the following script how many rows will be in GoTest?

CREATE TABLE GoTest (Id INT IDENTITY(1,1), MyText varchar(20))
GO
INSERT INTO GoTest VALUES ('A')
GO
INSERT INTO GoTest (MyText)
SELECT MyText FROM GoTest
GO 5

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

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

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

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Yesterday's Question of the Day

I create the following 2 tables (#A and #B) and insert the data as shown into the respective tables.

CREATE TABLE #A(S VARCHAR(10),R VARCHAR(10))

INSERT INTO #A
VALUES ('Alpha','D1234')
     , ('beta','A1122')
     , ('charlie','D1234')
     , ('bravo','C1342')
     , ('Doug','B1964')
     , ('harry','A1122')

CREATE TABLE #B(R VARCHAR(10),S INT)

INSERT INTO #B 
VALUES ('D1234',1001)
     , ('A1122',4001)
     , ('D1234',2001)
     , ('C1342',5001)
     , ('A1122',3001);

Next I execute the following T-SQL

SELECT tblA.S, tblA.R, subQuery.S
 FROM #A tblA
  CROSS APPLY (SELECT R, S
               FROM #B tblB
               WHERE tblA.R = tblB.R
   ) subQuery;

The question is: how many rows are returned when I execute the above SELECT statement?

Answer: 9

Explanation: The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

Ref: Using APPLY - http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

» Discuss this question and answer on the forums


Featured Script

Check AD membership

Check AD membership (including nested) using ADSI as linked server. 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

Backup on tape drives are failing - Hi We had received the following error while backup is running on tape drives. The below is the following error...

Temp db log file is growing . - tempdblog file is growing to 5 gb. I dont have more space on the drive. want a immediate solution . without restarting...

Login failed for user 'abc\mssqlsrv'. Reason: Failed to open the explicitly specified database - Hi, We have sql server 2008 x64 instance with SP1. When ever SQL Serve instance got restarted, I'm seeing the below...

Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state - I am trying to remove mirroring on a partner server database due to a network issue. Here is the scenario. We...

How to change database owner back to 'sa' for system databases? - Hello folks Today i was moving system databases model and msdb from local hard drive to new SAN storage. After detach...

Steps for installing a SQL Server 2005 Cluster - Hi All, Please provide me the pre-requisites for installing the SQL Server 2005 Cluster. And also please provide me the steps...

Problem Creating Linked Server from SQL 2000 to SQL 2005 - I have a SQL Server 2000 with SP4 box and a SQL Server 2005 with SP2 box. I successfully created a...

SQL Server 2005 : Backups

Change Reovery Model During Backup - I have a SQL 2005 production database running with a Full recovery model. Once a week I have a SP...

BACKUP problem on Remote 2005 server using Powershell script from 2008 server - I have a very odd problem with my backups on a remote 2005 server. Some of the databases, user and...

backup file size - Can anybody help in this matter Original database size [b]100 MB[/b] Backup files created, in scheduled time but size was [b]357...

tran log backup confused 2.5gb log file but 70gb log backup size - Ok I have a question I have a 116gb DB the full backup ends up around 70.5GB in size I...

SQL Server 2005 : Business Intelligence

Cube processing - Hi All, i have 10 cubes in my solution, individual cube sizes are 21GB, 50GB, 19GB so on.. when am trying to...

Upgrade SSRS and SSAS - can any one expalin how to Upgrade SSRS and SSAS from sql 2005 to sql 2008 and what are the...

Versioning in SSIS - I know DTS packages do we have versioning, just wondering whether versioning feature is still available in SSIS??

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

ObjectType Reference from Default Trace - Hi there! I'm querying the default trace to get some audit info on some DB objects. (The variable @Trace contains the...

SQL Server 2005 : SQL Server 2005 Security

Sql agent jobs fail when using linked server? - Sql agent jobs fail via schedule when using linked server. These same Jobs run fine when kicked off manually via...

SQL Server 2005 : SQL Server 2005 Performance Tuning

how to find the utilization is high while we are running performance monitor - HI 1.Can any one say how to find [or how we can know ]the utilization is high while we are...

SQL Server 2005 : SQL Server 2005 Integration Services

How to store date value'30/04/2012' to YYYYMM in sql table by derived column of SSIS? - Hi , I am going to store the date value data'30\04\2012' coming from csv into sql table column like'201204'. Please help me...

hi friends - can anyone please help and provide me an example to loop over a set of excel files from a given...

SQL Server 2005 : T-SQL (SS2K5)

SP_Oacreate and SP_OAMethod Invocation limit - Does anyone know if there is a limit on the number of objects or methods that can be created/called with...

Problem with BULK INSERT - I'm trying to use the BULK INSERT statement to populate some table from a TXT file. In the TXT file field...

Strange error with sp_msforeachdb - So I'm trying to create a security dictionary - single table, containing one row per server login/database/database user/granted permission combination. Not exactly...

Performance issue with tally solution - Hello everybody! I have a performance issue with a set based solution. Please could you advice me what I'm doing wrong? I...

SQL Server 2005 : SQL Server Newbies

Longest running queries/processes in SQL. - Hi Guys, I need a script to identify the longest running queries/processes in SQL. Thanks in advance :)

Changing Windows Domain on SQL Server 2005 - Hi Folks The powers that be around here are about to change the domain name that a windows 2003 r2 running...

SQL Server 7,2000 : SQL Server Newbies

Please Give Me the Query For Below Mentioned Resultant Tables. - Hi All, I have two tables for example, tblCategories(CategoryID[INT],CategoryName[VARCHAR]) tblEmployees(EmployeeID[INT],EmployeeName[VARCHAR],CategoryIDs[VARCHAR]) Example OUTPUT Like, tblCategories: CategoryID CategoryName 1 AAA 2 BBB 3 CCC tblEmployees: EmployeeID EmployeeName CategoryIDs 1

SQL Server 2008 : SQL Server 2008 - General

Install SQL Server 2012 in multiple machines using Configuration.ini - Dear Friends, Recently I went through an article about installing SQL Sever 2012 using a configuration.ini file. This procedure basically standardize...

migrating ssrs 2005 to ssrs 2008 - Hi , i want to migrate the ssrs 2005 report to ssrs 2008 . Please let me know any solution

The conversion of char data type to smalldatetime - Hi I have this code below below and when I try to run run it I get this error: "The conversion of...

Order of TRY/CATCH and TRAN Blocks - I've recently inherited a database that I've been working on for the last 6 months or so. I'm also studying...

Sum(Sum(Field,Scope)) Syntax for SSRS Expression? - Hey everyone, I'm building a report where I'm trying to write an expression for a variable (not parameter). I'm planning...

How to upload images in a post in SQLSERVERCENTRAL forum ? - How to upload images in a post in SQLSERVERCENTRAL forum ?

SQL Server Auditing in 2008R2 - Hi DBA'S I am enabling the SQL Server auditing to trace the changes in databases, Actually i enabled one sample Audit...

ssis daily package for zip file - How i can create a package to download file from website which is in zipped format then i have to...

Notification when a Job gets disabled. - Hi everyone, Thanks for looking into my question. Is there a way to get notified when a SQL Server Agent job...

Database analisys - Hi, I work at a software house that has an ERP software (manages human resources, accounting, sales, purchases, ...) and I've recently...

Please Help on Trailing spaces - Hello, Can someone please help me removing the trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc. for...

Learning SQL from Scratch - Hi, I have been programming in VBA for years and VB now for just over a year and I would like...

Rollback Script - Hi I have a simple stored procedure having a select statement. Recently I added a NOLOCK to the table name in the...

create YTD in a Table - I want to obtain an YTD value for every year and on the same row I have to put the...

Replication Error due to Trigger - Situation: I have setup transactional replication between 2 computers. In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion...

SSIS runs well independently but fails in a Job, probably because of a Script Task - Hello, I'm helping here with a SSIS package. The package loads data, exports it into Excel file, protects the Excel files...

SQl 2008 change collation of all columns - HI, How can I change collation of all columns in sql 2008. I am getting error for keys. Is there any script to...

Backing Up/Restore to non-local drive - I've told my IT Admin many times that we don't/can't do SQL DB backups to network shares or anything but...

Feedback On Proposed Drive/RAID Configuration For New SQL 2008R2 System - I am no hardware expert and so when it comes to things like RAID configurations and Channels I'm not versed...

SQL/BIDS 2008 R2 Error Processing Mining Structure - I receive the following errors when attempting to process a data mining model against Adventure Works DW ... when following the...

SQL Server 2008 : T-SQL (SS2K8)

Order by with case - Hi All I want to sort some table data using column index The following query works: [code="sql"] declare @index varchar(10) = 4 select CityInternalID, CityDefaultName, CityCountryID...

CASE STATEMENTS - I have two identical queries with only one filter difference between the two queries. Can anybody suggest me a solution...

Order by a column keeping the families together - I have two tables Table A -------- ItemID Date Table B --------- ItemID ParentID Records in Table A may be items with parents (or) items without parents. Some of...

Help Required - Hello Everyone. I need your suggestions on the following. I have a 3 Tables 1) Iss (IssID, subject, description created_on) 2) jou (jouID, IssID,...

How to find a Query in all stored procedure - Hi, I have database with over 100 SP. Now i have a query which i am suppose to search in all...

SQL help - I need some assistance in finding records from a table where a certain ID exists within close proximity to another...

Time as a high precision difference of dates - Hi all, I am trying to get a high precision difference of dates in SQL Server 2008 R2, and can't seem...

Loading the distinct rows in the destination table fromt the source table with some logic - Please take a look at the below details and let me know if anyone can help. I'm trying to write...

Cannot get Left JOIN to work correctly - I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner...

SQL Server 2008 : SQL Server Newbies

Problems with identity and reseed while appending records - Hello, I know this has an easy answer, but I can't find it anywhere or figure it out. I have TableA...

SQL Server 2008 : SQL Server 2008 High Availability

same role name for mirroring setup? - Hi, 1. Principal & mirror server result the same role, Is it correct for FULL safty configuration without wittness? [code="sql"]SELECT role FROM sys.database_mirroring_endpoints;[/code] Role...

Failover Clustering - I just added a third node to the existing 2 nodes SQL 2008 enterprise cluster. I attempted to failover to...

Standby-file location in log-shipping - Is there a query I can use to find the location (path) of the standby file in a log-shipping configuration?

Mirroring Configuration - Hi, Finally sucssfully configured database mirroring with no errors. For High safty with manual failover method... Principal has been synchronized with Mirror...

Mirroring and CDC(change data capture) - I'm using SQLSERVER 2008R2 with HA config using async mirroring. on the principal server, i also activate change data capture...

database auditing performance issues? - Can anyone explain me that do we get any performance issues when we implement database auditing on whole database. I...

SQL Server 2008 : SQL Server 2008 Administration

Some linked server connections stop working - I have a 2008R2 Standard instance (2008R2 SP1 10.50.2500) which has developed strange problems with linked server connections. All of...

how do we find out cluster environment? - Could you pls helpme out to find out the environment? Thank you in Advance :-)

error msg: Could not continue scan with NOLOCK due to data movement. - Hi, i keep getting the error msg; msg 601:Could not continue scan with NOLOCK due to data movement when backing up...

Investigating the Plan Cache - Hi All I've created the below script (by taking pieces from other similar scripts) to check my plan cache and query_stats...

SQL Agent memory issue - Hi I have a bunch of(6x) 64 bit sql 2008 (not R2) standard edition servers that seem to be leaking memory the...

index usage stats - I have a Non-Unique Non-clustered index on col1,col2.Using sys.dm_db_index_usage_stats & sys.dm_db_index_operational_stats DMVs for this index shows this result: user_seek=0 user_scan=67443 system_scan=5 system_seek=0 singleton_lookup_count=12628 range_scan_count=1828037 here's the question: How is...

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

Career : Certification

Need Dumps for 70-433 and 70-448. Please help - Hi All, Could any one can help me to find out or can provide me dumps of 70-433 and 70-448. I...

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Programming : General

TSQL snippet: table to .Net class generator - Heres a snippet for generating a VB.net class from a table, just replace the target table name just a few tweaks...

Programming : Powershell

Get-WMIObject Win32_Volume fails on proxy account from Agent Job - It may just be a case of me missing something, but I get unexpected behaviour when running a job as...

SQLServerCentral.com : Anything that is NOT about SQL!

Is it OK to ask salary range before applying? - Hi all, I came across a Jr. DBA position with no salary attached. A google search of the company didn't turn...

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

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

ssrs null values - hi friends i have small data in sssrs .plz tell mehowto solve this issue how to handle nulls data in ssrs...

SSRS vs. T-SQL - Query Response Time - I have a relatively complex stored procedure that runs great. I can execute the sp via Management Studio New Query...

Data Warehousing : Integration Services

Tab Delimited file handling - Hi, I am new to Integration Services technology. In SQL 2008 R2 Integration Services, We will be given Tab delimited Text files...

daily package for zipped file - How i can create a package to download file from website which is in zipped format then i have to...

SSIS and replication - foxpro database - ideas? - I am working in an environment where there exists a foxpro database (business application), which has to be synchronized to...

query AD in SSIS - I would like to use SSIS get data from AD to a SQL table. I setup the ado.net conneciton manager and...

Integration Services contain Analysis Services Processing Task. Fails but works in Analysis Services - The dimensions have processed Successfully prior to the updating of the SEN Cubes The attribute key cannot be found when processing:...

Data Warehousing : Strategies and Ideas

Development Source Data - Is there a best practice for what source data to use during development. I could use data from the source development...