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

Returning the Top X row for each group (SQL Spackle)

Dave Ballantyne from SQLServerCentral.com

One of the most common T-SQL questions is on returning a ranked result set for each member of a group. Dave Ballentyne brings us a short tutorial on how you can do this in T-SQL. More »

SQL Server Transaction Log Fragmentation: a Primer

Additional Articles from SimpleTalk

Generally, you will have no need to worry about the number of virtual log files in your transaction log. However, if you use the default settings for 'auto-grow', you can end up with such 'fragmentation' in your transaction log as to affect performance noticably. How can this be avoided? How can you tell it's a problem? What do you do about it? Greg explains. More »

From the SQLServerCentral Blogs - PASS Summit 2012 Review

Devin Knight from SQLServerCentral.com

Life is finally returning to normal after a great week in Seattle for PASS Summit 2012.  The entire year is... More »

From the SQLServerCentral Blogs - PASS Summit 2012 - Done!!!

Thomas LeBlanc from SQLServerCentral.com

Thursday afternoon Thursday afternoon concluded with How to run SSRS in Sharepoint integration mode, Enriching Tabular with DAX and Monitoring SSAS.... More »

With Steve away on vacation, today's guest editorial comes from Phil Factor.

I always take an interest in the SQL Server Pro Editors' Best and Community Choice Awards for Database tools. Naturally, it is little more than an indicator of the value that the SQL Server community puts on a database tool but it nevertheless it was nice to see Redgate’s SQL Backup Pro, SQL Toolbelt, SQL Monitor, and SQL Developer Bundle all get their ‘Oscars’. Five gold awards in all. I wish it could be more of a showbiz event, since it would be fun to see Simon and Neil in their tuxedos, holding trophies and weepily thanking their nearest and dearest during their acceptance speech. To say ‘thank you’ to the community voters, I gather that Redgate is planning something special in December for SSC subscribers, to be announced soon in this Newsletter.

Looking down the list, I was surprised to find my own name, listed amongst some ‘proven SQL Server experts’. Well, that’s something to tell my devoted older sister from Brighton, I suppose. Ah, that was an Editor’s Gold award for SQL Scripts Manager, for which I contributed a couple of example scripts. Actually, the award ought to be for Ola Hallengren’s Maintenance Solution, which formed the DBA meat in the sandwich for the tool, but his SQL Script rightly got the Community Gold award anyway.

I’ve always liked the SQL Scripts Manager. Despite its name, it will also run PowerShell or Python scripts, and can be persuaded to perform powerful magic. It is a great thing to be able to give to production IT staff, so that anyone who is on duty can click on one of a menu of scripts in order to do routine maintenance or first-line response to a problem. It was a down-tools project at Red-Gate, where developers who are normally stuck with rather mundane work get to be able to try to do something more spectacular, in a hectic week of development, design, presentations and testing. SQL Scripts Manager took a couple such weeks to get it into its current shape. Once it was launched, William Brewer wrote a couple of articles about it: SQL Scripts Manager, an appreciation and SQL Scripts Manager with Powershell. Then, once we got an added tool to create the scripts, Fast and Free; SQL Scripts Manager's Script Generator. Finally we got SQL Scripts Manager and IronPython from Timothy Wiseman, who described how one could write Python scripts for it.

Although Down-tools weeks have resulted in commercial products such as MySQL Compare, they mostly become free tools for the community. Have you a tool that you’d like Redgate to write in a down-tools week?

Phil Factor from SQLServerCentral.com

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

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

Today's Question:

DECLARE @string1 VARCHAR(16) = 'Microsoft Server'

DECLARE @Stuffing VARCHAR(30) = '******************************'

SELECT DATALENGTH(@string1),DATALENGTH(@Stuffing)

Execution of the above returns 16 and 30 as expected. I then execute : (Second SELECT statement)

SELECT STUFF(@string1, 11,0,@Stuffing)
     , DATALENGTH(STUFF(@string1, 11,0,@Stuffing))

The question is: what is returned as the DATALENGTH by the Second SELECT statement?

By bitbucket

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

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

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

It's Thanksgiving in the US today, a day when many people enjoy a large feast for dinner. Which T-SQL function seems more appropriate for this holiday?

By Steve Jones

Answer: STUFF

Explanation: We could argue about it, but for a little fun question I think STUFF makes the most sense.

Happy Thanksgiving.

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

Script for no of Index in a table

Thava from SQLServerCentral.com

This script will tell you how many indexes there are for a single table and also it specifically shows how many non clustered index, and also which tables have a heap index. More »

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

SQL Server 2005 : Administering

SNAPSHOT Isolation with (NOLOCK) being used all over - Recently, my applications architect decided that we needed to change the level of the database Isolation from the the default...

DeadLocking issue - I want to confirm that I am reading the deadlock information properly. It looks like to me that there are...

SQL Server 2005 : Backups

Sql Server backup time expired - Hi, I'm with a problem with an database in Sql Server 2008 with compatibility level 80.. scary the compatibility level.. but...

SQL Server 2005 : Business Intelligence

mdx query - Hi, for below query , i need to avoid hardcoding for any particular year, i need to get any year data...

week data in ssas MDX - Hi All, my current problem is if any user selects week 'X' in report filter , it needs to display data upto...

How to use SSIS to map input excel data file fields to a existing target table columns - Greetings, I have a task of comparing a set of data, which is contained within an Excel spreadsheet with a set...

Business Intelligence on the Developer computer - I am confused. I hope someone can give me some direction. From the old sql 2000 we used to create DTS...

SQL Server 2005 : Development

Reg: Date Inerval - Hi i am working examination schedule project.now my scenario is university already given two dates.examination start date and end date.i...

Copy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql...

SQL Server 2005 : SQL Server 2005 General Discussion

Does READ_COMMITTED_SNAPSHOT ignore ROWLOCK hints? - Hi, I'm planning to change the isolation mode of a DB that is in READ_COMMITTED Isolation mode to READ_COMMITTED_SNAPSHOT. On the...

Regarding sql server 2005 - i am new comer of sql server 2005 how to study to sql server 2005 what are the concepts should study to...

Retrieve data from SQL Server using most resource,memory and long running query in SQL Server. - Retrieve data from SQL Server using most resource,memory and long running query in SQL Server. Also how to retrieve data from...

which below services are useful in sqlserver - HI all which below services are useful in sqlserver which cannot be disabled ??? Alerter Application Layer Gateway Service Application Management Client Services for...

Deadlock that I cannot figure out. - Hi, I have a deadlock occurring and cannot figure it out, it involves an update and a delete of a row. Here...

Help MS SQL Join Problems - I am by no means a SQL master. I am trying to access data from two tables that are relational...

Automated e-mail from sql server 2005 when the CPU utilization is high - Hi Friend, Is is possilbe to have an automated e-mail from sql server 2005 when the CPU utilization is high to add...

interview questions - any info on really good interview questions for a sql server dba?

nvarchar(MAX) limit - Hi, I am trying to use nvarchar(MAX) variable for one of my dynamic sql query. However, the query string is getting...

SQL Server 2005 : SQL Server Express

new comer of sql server 2005 - how to study sql server 2005, as a dotnet developer what are the concepts should know and what are the...

SQL Server 2005 : SQL Server 2005 Performance Tuning

This query taking long time for execution inthis case what should i do - HI all SELECT Rtrim(CUST_ACID),rtrim(pmt_id),rtrim(AC_NAME),rtrim(txn_amt),rtrim(pmt_rmks) from payawdb.dbo.pmts,acmt where payawdb.dbo.pmts.cust_acid=acmt.acid and bill_ref_info in ('57263259') 1.this query is taking long time for execution and...

find % CPU from dm_os_performance_counters - Hi , Iam collecting a baseline report for one of the sql server instance, where i have only instance level access,...

SQL Server 2005 : SQL Server 2005 Integration Services

Destination data checking - Hi, Destination (OLE DB) has two table with existing data. (table1 : Person with Bonus and Table2: Person without bonus) The requirement is...

SQL Server 2005 : T-SQL (SS2K5)

Create A stored proc template that checks if exists then Alter otherwise create - I am trying to create a template for our developers for stored procedures. I want the logic to be the...

SQL Server 2005 : SQL Server Newbies

Failed Simple Maintenance Plan only when Full Backups do not run - Hello all. Let me start off that I'm new to SQL administration and have inherited an older server that serves the...

SQL Server 2008 : SQL Server 2008 - General

Is there a way to Identify if a database is no longer in use - Hi Guys, Is there a way to identify old databases that are not in use? :-D Well aside from checking the...

SSIS - exporting view to csv - Hi, I am exporting a view to CSV, however 1 of the columns is a smalldatetime and i would like the...

difficult query - My data is the log of changes to 3 specific records for an AppID. The AppCatID describes the 3 different...

Script to create realistic generic financial data - Hi SSC, This morning I was doing some work from home on some work transcribing a financial algorithm into SQL, when...

Reads too high on indexed-table update - I'm trying to understand why SQL Profiler shows high values of reads in the following scenario: Run the following code to...

DTS Error - Hi Experts, Need your help in fixing this issue. I installed SQL server 2008 [b]Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)...

Performance issue while fetching 20000000 records - I have some 20000000 rows in my database. While fetching records on some criteria it take too much time. I...

what is new method in create a script in (scptxfr.exe') for 2008 - what is new method in create a script in (scptxfr.exe') for 2008

how to store perfmon data(.csv) into sql table.create a schedule job - how to store perfmon data(.csv) into sql table.create a schedule job

SQL Trace causes application to hang - Hi, We've recently upgraded to Windows 2008 R2, SQL Server 2008 R2 SP + CU4, multi-instance cluster. 32 cores (64 with h/t),...

Need stored procedure in sql 2008 which scripts all the database - We used the below procedure to script all the Databases in sql server 2000 CREATE Procedure dbObjectBackup as DECLARE @name VARCHAR(50)...

How to find which are all databases are in use in sql server 2008 - Hi, I have a SQL Server 2000 box that houses several databases, some of which are probably no longer in use....

MAX behavior difference with nvarchar and varchar column types - Hi All, We are getting different results for MAX function with nvarchar and varchar columns. create table my_table(id1 nvarchar(10), id2 varchar(10); insert...

database relationships - i have master tables in one database and child table in one database, but no relationship between them, need to...

Changing Column Data type - Hi We need to change int column to bigint data type on a primary key column. So we generate script from managment...

directional based search - sql 2008 r2 setup, full-text is enabled if required here. Would anybody be able to share some implementation steps for seaching...

Script to get list of databases for which a user has db_owner database role ? - What is the command to get list of databases for which a user has db_owner database role ?

Database disk usage - IS there a sp to show disk usage of a Database?

Ignore fields with Commas - Hi all, I have a dataflow task that makes an initial connection to a flatfile csv. There are 4 columns ie:...

Execute scripts from directory by desired order - Hi All, I need to execute 300 .sql scripts from a directory. They are named like: 10001.name1 10002.name2 ... 10010.name10 10011.name11 ... and they are listed in this...

how to handle "timestamp" datatype value in sql server - Hi, I am getting trouble to pass the value of column2 to of my table in SQL stored procedure from...

how can i write 2 select statements in single procedure - Hi all, how can i write 2 select statements in single procedure.

How to check mirrored database - Dear, I have implemented database mirroring with automatic failover option. My principal database is a busy one. Now I am not...

Do not get the notification when the job fails - I have everything configured, have the sendemail stored procedure under msdb, havethe operators, same configuration on other 2 servers work with...

Capacity Planning - Hi All, I was asked to do capacity planning of SQL 2005 & 2008 servers. What are some of the things you...

SQL Service Broker - Administratively Disabled - Have implemented SQL Service Broker at one customer's site and it is up and running fine. Took the same scripts...

Script failed for StoredProcedure "dbo.XXX" - Hi, I am getting below error while trying to read stored procedure script from database using SMO. Script failed for StoredProcedure at...

SQL Server 2008 : T-SQL (SS2K8)

Weekly data converted to months - Hi all. Hope you can help. I have data that is structured by week format. Year Week Total 2012 49 10 2012 50...

Adventure Works database - I treid to install the Adventure Works sample database in my system but am not able to install that.. databse is...

Which is best Inner join or Left outer join - hope i get a solution here This is my DB structure [code] CREATE TABLE [dbo].[TableA]( [id] [int] NOT NULL, [AName] [varchar](50) NULL, [address] [nchar](10) NULL, ...

multiple columns to single column (normalization) - Hi, I'm working on normalizing a small part of our DB, but having trouble building a particular query that would...

Default fillfactor kept by SQL Server when it is rebuilding the indexes. - Hi, When we want to do performance tuning we usually rebuild the indexes. When SQL Server is rebuilding this indexes it...

need help with a query - I want to compare column values of 2 consecutive records in a table of 425 records and capture the column...

List of SP - Hi guys, Need help, how i can get the list of SP in my Database that were not accessed in...

Update statement question - I'm doing a DB review and have always been taught to write updates with a Join like using this syntax. [code="sql"] Update...

Optimization of stored procedures - Hi, I have a number of stored procedures used for reporting and whenever we run these reports it slows down the...

switching address with Subtring & len funtion - I have something out of order what is it I'm not sure, I want to reverse the field(s) Doe.John@CompanyABC.com to ...

No record count in PIVOT - Hi, I have the following query to count the number of employees per costcenter using the pivot command. The @Columns parameter declares...

searching for address matches using sql server 2008 full text search - Hi, I am not sure how to search for address matches using sql server 2008 full text search. TableA ------ Address1 Address2 City State Zip All the above...

SQL Server 2008 : SQL Server Newbies

How to delete only one of a set of duplicates - I have made some mistakes in my programming, ending op in a 1:many, where one of the 'Many' is dulicated. I...

Unique constraint column using nvarchar(max)?? - Can anyone help please? Can I use nvarchar(max) when creating a unique constraint on a column and if not (which I...

SSRS Newbie - Advice - Hi, I have installed SQL Server 2008 R2 Express with Advanced Tools to gain access to SSRS. I have also installed...

DATEADD minus 1 month - Questions? - Hi, hopefully a bit of an obvious question, but I need to run the following on a daily basis to...

Two Rows Returned as One for Reporting - Hi, I've got a problem that may or may not have a possible solution. What I'm essentially trying to achieve is...

New DBA job, done some digging on Database and have some concerns! - I've recently started a Job at company working as the sole DBA. I come from a .net developer background and...

Name query resultls by different column names - Hi, I will try to explain myself as clear as I can: I am having difficulty trying to list query results...

SQL Server 2008 : Security (SS2K8)

SQL Server Login and Users - Advise needed on best security model - Hi everyone, I am new here and have looked for an appropriate answer to the following two questions but cannot...

Trace login created or dropped event - Hi All, I have been trying to findout the logins that were created or dropped in past few days. I queried...

SQL Server 2008 : SQL Server 2008 High Availability

How to change the subject of alert email in SQL SERVER 2008 R2 - Hi Everyone, I have set up some alerts for mirroring monitoring, and in case of failure we recieve notification emails.Is there...

SQL Server 2008 : SQL Server 2008 Administration

DMV to replace SP_WHO - I have been looking at the DMV's intended to replace SP_WHO and SP_WHO2. I have knocked up a query to...

DBCC TABLE CHECK - Morning/Afternoon/Evening. There is a job that is doing DBCC CHECKDB - cmd in sysprocesses is DBCC TABLE CHECK. I can its waitype and...

How to rectify cpu utilization in sql server 2008 r2 - Hi Friends, In my sql server 2008 server having 80gb ram.some time its utilizing more than 65 gb. What might be the...

Why does my log show two backup events for the same database? - So looking over logs today, I saw the following: [code="plain"]11/20/2012 6:02:52pm - Backup - Database backed up. Database... Pages dumped: 9218 11/20/2012 7:00:13pm - Backup...

Delete Orphaned Constraints - Hello Everyone I am working with a database and have found that there are a couple constraints that are no longer...

Writing speed much slower in SQL Server than a simple file copying - Hi, I have a doubt as writing to disk by the Sql Server. We use the server with the settings below,...

Setting Notification email (When the job fails) - Hi Everyone, I have setup a job(SQLS ERVER 2008 R2) in two servers Server1.Domain1 & Server1.Domain2 and I have enabled notification email...

SQLServerCentral.com : Anything that is NOT about SQL!

Need to start off DB2 - I am an SQL DBA with 7 years of experience. I have a business requirement where I will need to learn...

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

Generate one PDF per record - Hi all, I've built a report which displays data from a db. This works fine, rendering the report correctly. My query returns...

Database Design : Design Ideas and Questions

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Data Warehousing : Integration Services

SSIS Package not importing particular rows to sql server - I have a package that imports CSV files into SQL server, however certain rows are not importing. As soon as...

IS Package to split a SQL File into multiple csv files using a parameter. Getting cannot be applied error - How to explain. I have a SQL table SELECT School_Name, Dfe_No,PIN_For_Schools FROM dim.SWF_Schools I then Have a SQL Task which Creates...

CSV file import using ssis - I have a CSV file as follows: "W3713","20121114171956","0.00","1","Standard","33646X","571270/6/1/69","0.00", "W6372","20121114171956","0.00","2","Standard","33646X","571811/09/10","0.00", As you can see the last column has a comma expecting a field. This...

Bulk Insert Task, set default date format - Guys, I am writing a Package that will do a number of bulk inserts. The files are flat file Pipe delimited...

Execute Process Task - Not returning exit process code from SQLCMD SQL Script - Hi I have a SSIS Package developed in 2008 R2 BIDS environment that includes a process task to execute a SQL...

Data Warehousing : Analysis Services

MDX Statement and Calculated Measure showing different outcomes - Hi, I wrote a series of MDX statements to find a total figure. I was happy it work regardless of what...

Microsoft Access : Microsoft Access

refreshing subform after right click and filter field in main form - i am right clicking a non key field in my main form and filtering by a quote number. The key...