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

The Technical Nuances of Technical Writing

Jamie Scharbrough from SQLServerCentral.com

A helpful guide to writing technical papers by focusing on the non-technical elements. More »

Exploring Semantic Search Key Term Relevance

Additional Articles from SimpleTalk

SQL Server's 'Semantic Search' feature seemed an exciting feature when first shown. Was it really true that Microsoft had come up with a system to rival the industry-leaders, one that could extract the contextual meaning of terms in text, or automatically categorise the subject matter of text? On first inspection, it seems unlikely. More »

From the SQLServerCentral Blogs - SQL Server # TSQL to Convert STRING in PROPER format

vinaypugalia from SQLServerCentral.com

Problem Statement SQL Server has got in-built functions to convert the given string into LOWER() or UPPER() format but it does... More »

I recently went to a Microsoft event in Denver on Windows Server 2012 and Hyper-V improvements. A bunch of the information was presented by Harold Wong (b | t) and there’s a number of demos and notes from the talks on his blog.

I haven’t looked much at the Windows server OS’s in years and not much at Hyper-V. I have preferred VMWare for my demo/research environments, especially as I move between Windows and OSX regularly. However I’ve thought Hyper-V was rapidly improving and on the right track. I was surprised to find the new limits in Hyper-V under Windows Server 2012 to be quite high for both the host OS and the guests. You can have up to

• 64 virtual processors
• 1TB RAM
• 64TB (vhdx format)
• 4 virtual Fibre Channel adapters
• much more

With 320 logical processors and 4TB of ram on the host, it seems as though Hyper-V is on par with VmWare ESX 5. There’s a lot more to look at than software cost, but at this time, it appears all new virtualization projects using Windows ought to consider Hyper-V.

There were interesting demos on replicas, live migration, improvements in file transfers and more. They were designed to make things look good, and there’s a good marketing presentation on the capabilities. I’m sure the actual implementation isn’t as easy or smooth as in the talks, but it did make me think there’s no reason virtualization shouldn’t be considered for SQL Servers, especially as you move to newer hardware.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 21.2MB WMV
• Watch the iPod Video Podcast - 18.5MB MP4
• Listen to the MP3 Audio Podcast - 3.8MB MP3

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

Today's Question:

What is the output of the below statement?

SELECT STR(123.45, 2, 2);
GO

By Ravi DBA

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.

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.

What value is returned?

Declare @a varchar(100)

select @a = null

select @a = isnull(coalesce(@a,coalesce('NULL','NA')),'Pass')+'Fail'

select @a

By SKR V

Answer: NULLFail

Explanation: The innermost coalesce returns a typed NULL after which the second coalesce is executed which in turn returns again a typed NULL as the variable @a is holding a null value. The isnull function returns the same typed null which is then appended to the value Fail.

Ref: Coalesce - http://msdn.microsoft.com/en-us/library/ms190349.aspx

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

Read large C2 Audit Mode trace files

Michael Lascuola from SQLServerCentral.com

Here's a set of scripts that allow you to save pertinent information from SQL Server Profiler trace files. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

SQL login gets locked automatically - Yesterday, I have faced an incident that users were getting login locked out error in their applications. I wanted to check...

window for searching a new login is not opening up in SSMS - This is on a Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Standard...

Types of DBA's ??? - Can any of you gentlemen tell me totally how many types of DBA's exists for an organization or overall Administrative...

'DBCC' is not recognized as an internal or external command - Hi, Can anyone tell me what am i missing if i tried to test my script on the command prompt and...

Unable to deallocate kept page - DBCC CHECKDB - Hello all, Received a call this morning that the database maintenance plan on a server is failing on it's Check Database...

Small database with 1 million users - Hi all, We've a small database (1 GB) and SQL 2005 server is clustered one. Now Business is forecasting that database...

CPU 100%, current queries show "CREATE PROCEDURE ... " - Hi, I have a SQL Server 2005, which works fine and on some time it starts to use 100% of CPU....

"Incorrect syntax near 'AS'. Expecting ID, '(', or PRIMARY" - I am getting the error above while trying to implement a Snapshot. Before the snapshot, I created three filegroups with...

how to giver users access to viewing JOB Activity Monitor - I want to give access to some users to view the job activity monitor. Now I know there is the...

SQL Server 2005 : Backups

.bak file quadrubles in size with restore - I'm a newbie and I'm using using SQL 2008 R2 I have a 10GB .bak file that when I restore requires...

Very Large Transaction File - I have a database on a SQL 2008R2 server running (virtual) Windows 2008R2 - all 64 bit - all current with service...

Operating System error 5(Access is Denied) - I am an intern working with two SQL Server 2005 instances on two Windows Server 2003. The servers are linked...

SQL Server 2005 : Business Intelligence

Converting Stored Procedures to SSIS packages - Hi All, We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps: 1....

SQL Server 2005 : Development

UDF AND Stored Procedures - Hi Guys, I know, the question is Funny, but i want to know the details of stored procedures and UDF I have...

Query to return the rows with values - --Sample script CREATE TABLE Professor ( ProfID INT , DeptID INT , ProfName VARCHAR(100) , CourseID INT , LanguageID INT ) INSERT INTO Professor VALUES(1, 201, 'Maria Soosai',...

Reset the Number by Column Value using ROW_NUMBER() OVER (PARTITION BY Field) - Hi Guys Need your help [code="sql"] /* SELECT IDENTITY(BIGINT ,1 ,1) AS RowID ,CAST( '' AS DATETIME) AS TodaysDate ,CAST( '' AS BIGINT) AS RowNumber ,CAST( '' AS...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Proc Cache Question - Hi, Background... SQL Server 2005 sp2. Mirrored with witness Problem Every few days queries start getting slower The only way to speed them up again...

SQL Server 2005 : SQL Server 2005 Integration Services

Execute SQL Task not using parameter mapping - I'm doing a fairly straight foward insert command with a parameter in the select clause, but the query doesn't seem...

SQL Server 2005 : T-SQL (SS2K5)

Remove decimal places and force leading zeros - I have output for scores that have 2 decimal places (all zeros). I need to remove the decimal places and...

How to trim the decimal places? - Hi Guys I have a column with the figures which have 4 decimal points. I want to trim them to two decimal...

SQL Server 2005 : SQL Server Newbies

Test - Test Post

SQL Server 7,2000 : Administration

Queries(joins?) slow after applying sql2000 SP4. - We have a couple servers still running sql2000, we just upgraded one to SP4 from 8.00.2039, a simple query select...

SQL 2000 RTM -> SP4 Upgrade Failing - So, I have 4 SQL instances, 3 clustered and 1 standalone. I can't seem to get SP4 to install to...

What is the BEST way to determine the REAL version of SQL 2000?? - So I just inherited a server I must administer. It runs Windows Server 2003 and SQL Server 2000 -- version [i]I'm...

SQL Server 2008 : SQL Server 2008 - General

Disk Usage by Top Tables - Hello All, The report [font="Comic Sans MS"]Disk Usage by Top Table[/font] is very usefull. [b]What is the source of the numbers in...

How do you admin your databases? - So I would like to try and find out - ideally for those working for a single employer - not consulting multiplexers working...

End Date Issue - Hi Professional, I need your help for solving below issue, [code="sql"]CREATE TABLE tmp_end_date (ID INT, tgs_rec_eff_dt DATE, tgs_rec_end_dt DATE, SEQNUM INT ) GO insert into tmp_end_date values(12345,'15-Mar-2012',NULL,4) insert into...

Script run history - Morning all Is there any way I can retrieve details about a script that was a run outside of a scheduled...

Checking values in multiples columns - ;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) AS (SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL SELECT 102, 'B',...

I had a Question Asked of Me today, that I Never Thought About Before - A person came up to me today, and asked if SQL 2008 and SQL 2008R2 can reside on the same...

slow dm_db_index_physical_stats query - I have a query I use to report on index fragmentation. SELECT TOP 25 db.name AS 'DatabaseName' , SCHEMA_NAME(obj.schema_id) AS 'SchemaName' , OBJECT_NAME(ps.OBJECT_ID) AS 'TableName' ...

Output who invoked a SQL Job - Hi all, Is there a way I could output the username of whoever invoked a SQL job each time it runs?...

Create select statement for the query - Hi, I have a requirement to create a CASE statement for the following condition: SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is...

Database Restore Problem - Hi Gurus, We've a DR server which is using VM machine, running Windows 2008 R2 (Standard). Had configured MS SQL Server...

Removing non-alphanumeric characters from a column using tally tables - I have a table TXNS Column A is int (3) Column B is varchar 10 I wish to remove all non-alphanumeric characters from...

Restoring SQL2008 Enterprise Edition db into SQL2008 Standard edition question - A vendor of ours is going to give us a database that is in SQL2008 Enterprise Edition for us to...

using dateadd function - Hi All I'm new @ SQL server. i need to select record that are within 30 days of this given date...

How to get backup of Accidentel Executed procedure script - Hello, someone executed accidentally old script in a database , have any way to get back date procedure script . if have,...

How do I join tables, but select top 1 from 1-many tbl? - Essentially I have a Contact table and a History table. I'd like to get the join the tables, but only show...

Columns not in key look up, why? - Hey all, We came across a problem today, no solutions yet, here is the scene, we have a query which uses...

Tempdb - Hi I have a tempdb that is 45gb in size. When I go to shrink db it says there is...

A quick query puzzle: - Hi I have a table with below data. Requirement is to replace all integers with continous 6 or more occurances with...

Multiple columns search with some blank/null values - Hi, I have a Form of multiple fields and there are some fields that are optional. How I can perform that...

Running .bat file in SSIS package - Hi, From a VB script task in SSIS, I am trying to run a bat file. Here is my piece of code....

Image upload from Client to server - Hi all, Can we upload a image from client location to server using sql script. I can access the server db from...

SP_msforeachdb - I am trying to use SP_msforeachdb to query one table in every DB I have, but no system db's. here...

SQL Server 2008 : T-SQL (SS2K8)

CROSS APPLY - CROSS APPLY ( SELECT col = CASE WHEN MAX(col) = MIN(col) THEN MAX(col) ELSE NULL END )

SSRS - Hi, Anyone knows about this? I want to make SSRS reports based on cube(SSAS). Now further more I want to filter data. for...

Get all the domain and SQL users from an instance, per database - Hello, For SQL 2k5, 2K8 and 2K8R2, I need a T-SQL query to get all the users listed in: SSMS => "Instance"...

SSIS Package Inside Trigger keeps running endlessly - Hi, Following some requirements, that came in yesterday, I have been assigned a task of executing an SSIS package from inside...

Pivot a table - Hi All, following is the result of my query Year_Month.........cat1_per......cat2_per........cat3_per......cat4_per 2004_06...............0.892..........0.778............0.467..........0.871 2005_10...............0.790..........0.629............0.581..........0.978 but i want output of my query to be Category...........2004_06..............2005_10 cat1_per.............0.892........

Logic problem in "SET [Quantity_Remaining] = [Quantity_Remaining] - 1" - Any ideas?... I have a trigger (not working obviously) set up that decrements from my [tblBulkPurchases].[Quantity_Remaining] field an amount by...

which is the easy way to Eliminate the duplicates? - Hi, Please suggest me to get the distinct values from below query declare @T as table(id int identity, code varchar(10)) insert into @T(code)...

replace - I have 2 columns in the database. The value in column 1 is 10000000. The value in column 2 is...

error - incorrect syntax near '4' - CREATE PROCEDURE usp_update @pcid int ,@ppid int ,@ptid int ,@pqty int AS BEGIN SET NOCOUNT ON; DECLARE @Rate int SET @Rate = (select prate from M_PRDT where PID=@ppid) IF EXISTS...

UNION between two stored procedures? - Just curious: is it possible to perform a UNION between two stored procedure executions (that both return identical data formats)?

SQL Server 2008 : SQL Server Newbies

Stored Procedure to Add Details to a table - Hi, I have a patient table, nurse table and a doctor table but would like to store the common details,...

Just taken new Job as a SQL Server DBA, can anyone recommend some learning material? - I come from a SQL developer background but have now taken a position as a DBA. Recovery plans , security policies...

Multiple Rows of Data To Single Row With One ID - Hi, I'm trying to alter a table to contain data in a horizontal (row) format rather than a vertical (column)...

Relationship between tables - Hi, this query returns almost all that I need, but NUMEROCCF from TPRD is missing. If I just add it...

Upgraded SQL Server 2005 to SQL Server 2008, but it still reports 2005 - I upgraded my SS2k5 instance to SS2k8 using the standard upgrade process from MS, but the instance still shows as...

QUERY taking too much time in execution - I have a scenario in which I have a table citymaster(cityid,cityname,stateid) which needs to be getupdate from statemaster (stateid,statename,statecode). I...

Job - Scheduled update table in another databaes nightly - please review and comment on script / process - [b]Please review Newbie's code and plan:[/b] Comments welcome SQL 2008 - Database name FOO FOO has 2 databases DBT and GIS Only...

split column pipe delimited - Hi All, I'm having trouble separating a single column into multiple columns where the pipe is the delimiter. I have enclosed an...

SQL Server 2008 : SQL Server 2008 High Availability

CLustering with Mirroring for Disaster Recovery - hi we are planning to do the following two physical servers, each physical server two vm.one is for db and other...

Quic answer plzzzz - Any method to perform Database Mirroring? without impacting perfromance??

Running SQL 2005 & 2008 in a 2 node cluster - Is it possible to have a setup running SQL 2005 & 2008 in a 2 node cluster? Thanks Pete

SQL Server 2008 : SQL Server 2008 Administration

Migration - SQL 2000 to SQL 2008 - Hi, I need to migrate SQL 2000 to SQL 2008. The path I want to take is backup SQL 2000 and restore...

SQL Server Agent job output query results to file size too large - I have a complex query which I wish to run everyday (shown below) [code] USE GATEKEEPER IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP...

just backup and restore will fix any performance problem? - just backup and restore will fix any performance problem?. One person in my office claiming that applican performacne issue is...

Seek predicate Vs Predicate - Can someone please explain the difference between Seek predicate and predicate in execution plans?

sysobjects shows table not in database - I am new to SQL Server 2008 R2. However, I do have many years as a DBA, but for other...

Server Upgrade - Hi Experts, One of our SQL server got upgraded from 10.50.2789 to higher version. Is there anyway to find who did...

Programming : XML

XML SHREDDING - DECLARE @docHandle int DECLARE @xmlDocument nvarchar(1000) SET @xmlDocument = N' <hearingData> <author> <date>2012-09-12T12:41:22</date> <companyName>TK Group</companyName> <software>Audiogram System</software> <softwareVersion>6.0.30</softwareVersion> <fileVersion>1.02</file

SQLServerCentral.com : Anything that is NOT about SQL!

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

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

chart x-axis. Separate label format plz help - Hello everyone, I want to do a simple thing which costed me a lot of time already :-) View the following chart: [img]http://i.imgur.com/kTZ9t.png[/img] For...

Auto size columns in SSRS - Is it possible to auto size a tablix column to the data they hold? Thanks Regards

Expression Error - Hi geniuses, I'm having problems with a traffic light indicator expression: =SWITCH( ReportItems!Textbox21.Value="1.1.PC" and ReportItems!Textbox23.Value<>"",0, ReportItems!Textbox21.Value="4.2.PP" or "4.3.PE" or "4.4.S" and ReportItems!Textbox23.Value="",1, ReportItems!Textbox21.Value="1.1.PC"...

Sum with condition not working - Hopefully someone here can help me solve this problem. What I have so far is a list with two tables in...

Reportin services - AVG of avg - Hello, I have question, how to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I...

Reporting Services : Reporting Services 2005 Development

Failed to host SSRS Report on other server - [font="Courier New"][size="3"]Hi All, I am working on SSRS, I have developed a report when deployed it in local instance its working...

Database Design : Disaster Recovery

Graph to show downtime / data loss? - Not long ago I came across a graph plotting acceptable system downtime and acceptable data loss in an easy-to-understand format...

Data Warehousing : Integration Services

Create Multiple Excel output files - Hi everyone - hope someone can help... I have a SQL table that contains information on visits to our centre. The table...

Data Warehousing : Analysis Services

Need to correct below MDX query. - Hi, I need to write MDX query to calculate GLOBAL_BALANCE (For MTD & For given date). I used below query. It...

SSAS- - hello I am getting error when i am processing qube on instance . But it works fine on one instance but not...

Date Filter in Excel for pivot table with cube data source not filtering - hello all, after much googleing and setting of parameters , member values etc, i am at a dead end I am trying to...

Article Discussions by Author : Discuss Content Posted by Santveer Singh

SQL Query to get all the databases and permissions on them for a login - Hi, I have situation where i need to prepare a report to find all the users and what access have they...