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

Parsing Parameters in a Stored Procedure

Diego Buendía from SQLServerCentral.com

This article shows a clean non-looping method to parse comma separated values from a parameter passed to a stored procedure. More »

Last chance for a day of free SQL Server training at SQL in the City 2012

Press Release from Red-Gate

SQL Server developers and database administrators have one last chance for a full day of free training and networking at SQL in the City 2012. More »

SQL Server Function to return a range of dates

Additional Articles from MSSQLTips.com

A quick solution is a table function that will create a date range based on the parameters given. Then, you can just join to the results. More »

From the SQLServerCentral Blogs - Don’t believe everything you read: Truncate table is not logged

matt.bowler from SQLServerCentral.com

Actually I’m not sure if anyone still believes this anymore. If you read the BOL description carefully it states that... More »

This editorial was originally released on Nov 30, 2007. It is being republished as Steve is traveling to the PASS Summit.

Microsoft got in on a deal to Nigeria that Mandriva Linux negotiated with the government there. The story almost sounds like a bad email: the provider of a "free" product complaining that another company gave their product away for free? So does that mean that in a free market where the cost is the same ($0), that Windows is superior?

There's some other editorial on that topic, but in this case, what was interesting is that the CEO of Mandriva fired off an open letter to Microsoft, which you can read here. The tone of the letter was a little whiny, and it didn't seem as though Mr. Bancilhon really thought through what he was writing.

It's Friday, and with the holidays approaching, I thought this would make a good poll.

Do you pause before sending emails?

In the old days, back when I was younger, if you wanted to fire off a letter, you had to write it and then mail it, usually ensuring some type of delay. Often you had at least one night to think about what you'd written before you dropped it in a mailbox.

There are plenty of people that still recommend that you delay sending an emotional email for at least a day. I know quite a few people that rethink their position before sending, often saving the email to a drafts folder for ten minutes, an hour, some delay to give themselves a break between writing and proofing.

I'm actually pretty bad. As I'm sure many of you have noticed, typos slip through the editorials from time to time, often because I'm in a rush. My self-proofing skills aren't the greatest, and I tend to make mistakes more often than I'd like.

However I've also learned that delays can be good things. I've often incorporated a delay in restoring a secondary log-shipped database to give me the chance to recover from an accidental deletion of data. Delaying the send of a potentially inflammatory email allows me the time to cool down and can prevent me from making a big mistake.

Email is quick and a great tool, but it shouldn't be abused. Take the time to be sure that the message you're sending is the one that you want received.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

The RSS Feed: or now on iTunes!

• Video Podcast - 26.6MB Quicktime
• Video Podcast - 12.8MB WMV
• Audio Podcast - 5.6MB MP3

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.

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

Today's Question:

create table dbo.Data
(id int not null identity(1,1),
 DateCreated datetime not null,
 DateModified datetime not null,
 Chardata char(100) not null 
    constraint DF_data_value default 'nothing'
)

create partition function pfData(datetime)
as range left for values ('2012-01-01',
                          '2012-03-01',
                          '2012-05-01',
                          '2012-07-01',
                          '2012-09-01',
                          '2012-11-01'
                          )
go

create partition scheme psData 
 as partition pfData
 all to ([primary])

By Igor Micev

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

This question is worth 3 points in this category: Partitioning. 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.

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.

How many 8-bit characters are there between '0' and 'Z' (inclusive) in the collation Latin1_General_CI_AS?

By Tom Thomson

Answer: 139

Explanation: Apart from 52 alphabetical characters in the English alphabet in upper and lower cases and the 10 numbers 0 to 9, there are characters from other Western European alphabets (eg 'þ', from Icelandic, 'ß' from German), many accented letters (both vowels like 'à' and consonants like 'Ç'), auxiliary symbols like '™', digraphs like 'œ','ª',''²', and some fractions like '¾'. All these characters are, of course, "alphanumeric".

You can easily make the complete list (in the collatiopn order) with some simple SQL, like this, provided you have (or make yourself) a Tally table:-

select CHAR(I),I from Tally 
    where char(I)  between '0' and 'Z' collate latin1_general_ci_as 
    and I < 256 order by CHAR(I) 

A Tally table is a table with only one column, I (which is an integer, and is the primary key) containing all integer values from 0 (but some people start from 1) to some fairly big number (mine goes to 65535) - Tally tables are an extremely useful thing to have around, see Jeff Moden's introduction to them.

» Discuss this question and answer on the forums

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.

Shrinking all log files on SQL Server 2008R2

Igor Micev from SQLServerCentral.com

It shrinks all log files for the databases created by users. More »

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

SQL Server 2005 : Administering

very high CPU, 0 I/O, no blocking, NULL in wait type? What could be possibly going wrong? - I have a select query which is in a "hung" state. I checked there is no blocking. From the activity...

Tempgb log - not able to shrink....none of the queries are helping - I have gone through many many different forums in trying to identify my tempdb issues. None of the queries are...

SQL Server 2008 R2 Service Pack 3 using SCCM package - Is it a good idea to get sql server service pack through SCCM package. Description: We will calling SCCM package and Sccm...

SQL Server 2005 with SQL Server Express - Hi All, Is it possible to install SQL Server Express when a full version of SQL Server 2005 client components...

SQL Server 2005 : Business Intelligence

Cognos impromptu - Hello all, My first post - so be gentle! We use SQL Server 2008 R2 and I'm just beginning to explore BI for...

Want to add Recordcount in Flat File output - Hi, I have a requirement where i need to export my data to a flat file (.csv file) and at the...

Any agile syntax tool for analytics and statistical computing? - hi, as an business man, have u ever find a agile sytax tool for statistics and analytics? I find R...

SSIS Oracle Driver - Currently we have a couple packages that connect to source systesm via Linked Servers and OPENQUERY. We are wanting to...

Using Checkpoint Files - Afternoon all. Has anyone used checkpoint files where a data flow splits data and updates two destinations? On question relating to...

How do I know - SSRS - I believe if SSRS has been installed/enabled on my server, I can find out be looking in the sys.databses table...

SQL Server 2005 : CLR Integration and Programming.

CLR Eval function - I want to be able to pass a string and have it perform the equation within the string. For example,...

SQL Server 2005 : Development

simple update statement - DECLARE @lClub_group_joined_test varchar(20) SELECT @lClub_group_joined_test = club_group_id FROM dba.t_club_group WHERE acct_id = @iAcctid UPDATE #temp_ticket t SET t.club_group_joined = STRING(t.club_group_id_col, ',', CONVERT(date, t.club_group_joined_datetime)) WHERE t.club_group_id_col = CASE WHEN t.club_group_id_col = @lClub_group_joined_test THEN...

SQL Server 2005 : Working with Oracle

How to corelate oracle dba concepts and sqlserver dba concepts - Hello Team, How to corelate between oracle dba concepts and sqlserver dba concepts Please provide books or any links Thanks in advance

Disappearing Oracle Provider - Hello all, I am experiencing an odd problem with my systems and hope someone here can shed some light on my...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server 2005 cpu at 100% - Hello - I would like some help please on an issue I am having on a site. I have found a...

SQL Server 2005 : SQL Server Express

What installs SQL Express? - We're in the middle of a security audit at my place of work and we have unearthed a lot of...

How do I configure a named instance in SQL EXPRESS 2008 R2? - I've installed a SQL Server 2008 R2 Express onto a user's machine (he's stuck on the east coast, due to...

SQL Server 2005 : SQL Server 2005 Integration Services

Replication user account - Hello Could any one explain which login acount we should use while configuring Replication or while providing security credentials during replication...

Latest file picking from the folder - Dear all, I am using the XML data source while is daily downloaded from FTP and placed in one common folder. I...

SQL Server 2005 : T-SQL (SS2K5)

union and order by - I had used 5 queries and union their result then appllied order by clause .but order by doesn't work result...

How to filter if data was not there - I have a query (shown below) that I have been asked to filter so that only stock activity that has...

SELECT DISTINCT AND ORDER BY CASE - Hi i am trying to use select distinct with order by case but getting an error like [color=#990000]"ORDER BY items...

xp_cmdshell - Procedure expects parameter 'command_string' of type 'varchar' - Here are the contents of @Cmd (varchar(max)): bcp "SELECT [Data] FROM myserver..dbo.PEC_Citrus_RptData where [Data] is not null order by [Data]" queryout...

trying to export to .CSV with comma delimited AND double-quotes - What I have is a simple table (in SQL 2005) that provides input to a mapping program. It has four columns....

SQL Server 7,2000 : Data Corruption

MDMP files - Hi, In one of my production server, am seeing many MDMP files created on Oct 24. I just refreshed my mind...

SQL Server 2008 : SQL Server 2008 - General

is there any change in query execution time , when we replace NTEXT with nVARCHAR ? - i have table with lots of NTEXT fields. some of them are rarely used. i just replaced ntext with nvarchar. is...

union with order by is not working - I had used 5 queries and union their result then appllied order by clause .but order by doesn't work result...

Updating 566787 Records takes forever - Hi, I am writing a script to update bad data in a column of a table. The Table has 566787 rows...

Error in max(substring) - can we add startid, endid SplitLayout startid, endid 1,1 2,1 3,1 4,2 6,3 9,1 10,5 15,4 19,3 22,4 Startid = will start from 1 and will add endpoint for second row 1+1...

Integration Services Packages and email error - i have SQL Server Integration Services Packages, which contains foreach loop that get data from stored procedure write it to...

Strange transactional replication problem - I have two SQL databases P and S. I created backup of P and restore the backup at S. Then I created...

sql server performance - tempdb related to the sql server performance?

SQL Server 2008 Replication Issue - Hi I am running Replication between SQL Server 2008 and Oracle. Publisher : Oracle Distributor : SQL Server 2008 Subscriber : SQL Server 2005 Replication type : Transactional Was...

SQL to split a value across multiple lines - Hi Guys, I am currently generating a results set as follows; [code="plain"][font="Courier New"] Policy Claim Job Excess A 1 E 50.00 A 1 F 0.00 A...

quickly parse the query for the problem - Hi, is there any quick way to find the problem in this query? the error is:string or binary data would be truncated the...

tempdb recreations - How do I track all sql server restarts in a domain ? Ex:Lets says there are 100 servers.\ I would like to...

ntext indexing problem? - why we cannot apply indexing to ntext datatypes?

Linked server - Running the below sql on server1 where I have defined linked servers to other servers. SELECT name as DBName,[crdate] AS [LastStartupDate] FROM [Server2].[master].[dbo].[sysdatabases] where...

How to get index fragmentation of a database with query ? - Hi I have a database having 260+ tables and many indexes and states. Is there any way to get index fragmentation...

can i get result of EXEC sp_executeSQL as record set? - DECLARE @query nvarchar(MAX) ; set @query = 'SELECT * FROM Products' ; EXEC sp_executeSQL @query is this query return the record set as normal 'SELECT...

determing when a database was last modified - Hi, Can you determine when a database was last modified by looking at its mdf files last modified date (i.e. on...

Best practices to create databases for different applications - Hi, We are in a process of developing 4 different applications and they are all not related. All 4 application developers created...

Customery Survey Query with no Duplicates - Hi, I'm trying to build a customer survey query of 50 random jobs where no customer (UserID) appears more than once....

SQL Merge Question - Experts. A sql merge question, from example below. Is there a way to make update @BookChangeTracking in the UPDATE condition like...

Storing Files greater than 8000 bytes - Hi, Is there task which will be capable of uploading/storing a file in a database which can be greater than 8000...

SQL Multiple Pivot - Hi Guys, I am a bit stuck trying to pivot some data in SQL and was wondering if you could offer...

Best index structure for table that holds a large amount of log data - Folks I have a ASP.NET solution that needs to log every time a method is invoked.(that part I have done) I am...

Check Constraint Works on Inserts, but Not on Updates - I created a UDF to check for more than one instances of a value 1 in a bit column corresponding...

Suspended Queries - Hi Team, I am getting blocking in one of my sql server. I can query which is suspened causing the block. It is...

100 Most famous interview Questions and Answers - Please go to the following link:- http://sqlcheatsheet.wordpress.com/2012/10/26/sql-server-2008-2008-r2-cheatsheet/ Download the PDF (sqlserver2008r2_cheatsheet_v1-01.pdf). Password: harinam This is very good document for the freshup your memories...

Question on Lock wait types - Hello Everyone, Our production server experiences lot of blocking and locking from application. I have attached the top waits from...

Backup script - Hi, I'm using ola backup script and want to have all backups in the given backup directory NOT to create Instancename\databaseName\BackupType...

Failure to calculate super-latch promotion threshold - I saw this error message in the error log of one of my SQL Server 2008 R2 servers today (with...

how to find ASCII characters in a table? - Can any one tell me the sql query to find the ASCII characters (0 to 127) from multiple columns in...

Running a sql server 2008 job to back up to network folder - I'm trying to schedule a back up database task to backup some specific databases to a network folder but kept...

SQL Server 2008 : T-SQL (SS2K8)

Add Minute - Hi, I've got quite an unusual question. I'm running this simple query: SELECT SourceHub, SnapshotDate, NetworkName, QOSGroup, CountOfRemotes, SumOfTX, SumOfRX FROM ConfigurationSnapshot this is...

Change Data Capture 2008 - I have enable the change data capture on db and enabled one table and i made updates on couple of...

char(??) vs varchar(??) - Hi all. I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that...

Some way to write an INNER JOIN, but joining on either of two columns - Hi, Each doctor in my doctors table has a main zip code and an alternate zip code. SELECT D.* FROM Doctors D INNER...

SQL Server 2008 : SQL Server Newbies

Using SUM(1) to count Rows (vs Count(1)) - I have run into an instance where a developer was using SUM(1) to count rows in a table rather than...

Confused by table size - I have a table in a database I inherited that has an mdf file of 25GB, with ~870MB space available....

quotation and punctuation in sql server - Does anyone have some rules by which quotation in SQL Server works? An example: Select Year, Max(Case When Name='Tour de France'Then Coureur...

Multiple Rows in One Resultset - Hi All, Let's say I have a table like the following: row1 row2 row3 row4 0 6 0 0 0 0 30 0 4 0 0 0 0 0 0 18 but I want to return a resultset like: row1 row2 row3 row4 4 6 30 18 Does anyone have any...

SQL Server 2008 : Security (SS2K8)

Permission question - Suppose there is a tableA and trigger defined for Insert, Update and delete to populate audit table say tableB. Now...

Cannot Generate SSPI Context with AD account - I have several SQL 2K8R2 servers in "the cloud"; most work correctly. One is giving me problems. On that one (which...

sql server permission - I have a question about setting up permissions on a sql server 2008 r2 datbase. I basically wrote and enhanced...

Sending remote attachments using sp_send_dbmail - Hi, I am using Windows Authentication to send email using sp_send_dbmail. Sending email works if I do not try to add a...

SQL Server 2008 : SQL Server 2008 High Availability

Logs - Hello all, In SQL SERVER 2008, if any DML activity or any import export or any Backup restore etc is been...

Transactional Replication - Inner workings - Hi all Just wondering how the inner working of replication happens. eg. You have server A replication to Server B. (transactional...

Mirror Monitoring issue - I have setup mirroring on a pair of SQL 2008 R2 servers. This appears to be working ok when I...

SQL Server 2008 : SQL Server 2008 Administration

CPU High utilization -urgent help please - Hi All, One of our Prod box running SQL2008R2 having CPU high utilization issue I already checked Min/Max memory changed...

Access question - The default access of all new employees of our company is db_datareader but how do we deny access to particular...

Kill process or user - Hello, My database is in singlemode and I can't change to multi user mode,get error message that someone in this database,how...

Backup size estimation - We are going to do an upgrade from 2005 to 2008. Is there a way to estimate the size of...

DBCC FREEPROCCACHE - Can we use in DBCC FREEPROCCACHE in production database? Thanks in advance

Do people rebuild indexes if non-leaf levels exceed fragmentation threshold? - I recently realized that our nightly index-maintenance process rebuilds indexes if the avg. fragmentation is higher than 30% for *any*...

lightspeed silent installation - Getting below licence expired error While installing lightspeed with silently but Licence key is valid and not expired. Error saving license. SLSInstall...

Career : Certification

Which exams to take? (2008 vs 2012) - I'm more interested in the BI side of things but think that having a good grounding in database fundamentals will...

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

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Programming : XML

Returning multiple rows from XML - Greetings, I am new to XML and am struggling with shredding an XML column into a tabular format. Here is what I...

SQLServerCentral.com : Anything that is NOT about SQL!

Not enough work? - Just curious, has anyone ever had to quit a job because they just can't keep you busy?:doze:

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

Reporting Services : Reporting Services

using subscriptions for Date Parameter Reports - I have a report created that has two datetime parameters. I want to set up a monthly subscription for the report...

[SSRS 2008 R2] How to search a built-in variabel Global RenderFormat.Name in another dataset - Hi guys, I currently on a project that will implement restriction in rendering export format in SSRS 2008 R2. By default...

Subscription of multiple valued parameterized report in SSRS - Hi, I have a report in SSRS report server URL, it takes location as a parameter from the query dynamically has...

Database Design : Design Ideas and Questions

Hi, about Indexes, can someone help me?, thank you in adv - 1.- After how many records is appropiate to begin to use indexes? 2.- Is it correct to create a nonclustered index...

Data Warehousing : Integration Services

SSIS Configurations - Hi all, I have a master package to execute 200 child packages. The master package goes thru a loop and executes...

Windows batch file executions within SSIS fail when run from SQL Job Agent - Windows batch file executions do not fail within SSIS when SSIS run using DTExec The account we are using to execute...

Data Warehousing : Strategies and Ideas

Does it make sense store measures in a dimension table? - Hi everybody: Right now I´m working in a BI solution and I received a set of tables with a multidimensional structure....

Anyone use SCD Transformation? - Does anyone use the SCD transformation in their production ETLs? In my two previous DW jobs, we never used the...

Data Warehousing : Data Transformation Services (DTS)

DTS Package fails with error - Invalid OLEVERB structure - Dear All,   I have a DTS Package that transfers SQL database objects (Tables & Constraints) from one server to another server using...