The SQL Zone
The database server is the big iron in a server environment, and keeping them running smoothly is a full-time job. If you’re looking to improve your skills as a DBA or a database developer, this is the place to start.
The articles gathered in our SQL Server Zone cover everything from basic SQL syntax to advanced tuning techniques for stored procedures.
Alongside white papers and sample code, you can read articles for all levels of proficiency, and get answers for specific questions from the authors themselves. Your ongoing SQL Server learning begins here.
Red Gate Whitepapers
Featured Article
 |
by psined
Introduction to Boomerang Framework
|
Articles
 |
by cesar_boucas
IDataReader implementation illustrated with SqlBulkCopy class usage
|
|
by Tadeusz Kaliszewski
A small .NET class library for maintaining SQL CE database schema written in C#.
|
|
by dale.newman
Google your SQL.
|
|
by Randall Smith II
A simple solution trying out the Azure platform for development and deployment.
|
|
by Shemeer NS
CAST(), CONVERT(), PARSE(), TRY_PARSE(), TRY_CONVERT(), FORMAT(), SQL Server Functions, SQL...
|
 |
by azamsharp
How to implement row deletion in GridView, with confirmation.
|
|
by UsualDosage
Demonstrates how to apply JavaScript functionality and CSS effects to an ASP.NET DataGrid.
|
|
by Adrian Pasik
This article is about transferring backups without raising the privileges of SQL Server, or...
|
|
by Weidong Shen
Part 2 of a series describing the creation of a Silverlight business application using MEF, MVVM...
|
Discussions
|
|
 |
|
Apologies for the shouting but this is important.
When answering a question please:
- Read the question carefully
- Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
- If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
- If the question is inappropriate then click the 'vote to remove message' button
Insults, slap-downs and sarcasm aren't welcome. Let's work to help developers, not make them feel stupid.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
For those new to message boards please try to follow a few simple rules when posting your question.- Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
- Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
- Keep the subject line brief, but descriptive. eg "File Serialization problem"
- Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
- Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
- Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
- If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
- Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
- Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
- Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
- If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
- No advertising or soliciting.
- We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
I have this SQL Server 2012 trivial stored procedure:
CREATE PROCEDURE usp_SelectCompany
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM Company
END
and in a SSMS window, I execute this commands below to discover my SP is much slower than directly executing SELECT * from Company, even after the SP is run once to compile it.
SELECT * INTO #Company FROM Company WHERE 1=0
INSERT #Company EXEC usp_SelectCompany
PRINT 'Start Time: ' + cast(sysdatetime() as varchar)
INSERT #Company EXEC usp_SelectCompany
PRINT 'SP Time: ' + cast(sysdatetime() as varchar)
INSERT #Company SELECT * FROM Company
PRINT 'Select Time: ' + cast(sysdatetime() as varchar)
Here is the output:
(0 row(s) affected)
(127733 row(s) affected)
Start Time: 2013-05-22 11:56:57.7208741
(127733 row(s) affected)
SP Time: 2013-05-22 11:57:03.2067327
(127733 row(s) affected)
Select Time: 2013-05-22 11:57:04.0015524
I note that the SP executes in 5.4858586 seconds, and the direct "select" statement only takes 0.7948197 seconds, or about 7 times slower.
Can anyone explain this behavior?
|
|
|
|
|
Saxet wrote: Can anyone explain this behavior?
I can't, but a wild guess would be the optimizer (select and insert vs. insert and exec some proc).
What's the timing when you move the insert-statement inside the sproc?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
It's better (1.0295934 seconds), but still slower than the select in the SSMS window.
Start Time: 2013-05-22 12:51:03.6580584
End Time: 2013-05-22 12:51:04.6876518
|
|
|
|
|
Saxet wrote: It's better (1.0295934 seconds), but still slower than the select in the SSMS window.
The server has to do some things that can be skipped when being fed the statement directly, like looking up the access-rights to the sproc. So yes, compared to a simple query, it'd be a (small) bit slower.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm not entirely surprised, but the difference is probably not accurate. What you might try is simply using ExecuteReader from code on each and see how long it takes to do -- without reading the data.
I also wonder whether it's a local database or remote.
|
|
|
|
|
Hi,
I am using VS 2012 Free Edition (et al)
I am contemplating using this for the next version of 25 year old successfull software. We need to update various tables in a single transaction. This transaction needs to proceed in a step in which either All succeed, or None succeed. (resulting in a roll-back if any step failed) The database must deal with a high likelyhood that several terminals will at the same time try to write to the same entry in the same table. I handled this in MFC, with my own DB by giving each contentious item a serial-number, The first check for a modify-save would be if the serial number was still the same. if not, the item would be refused, and the transaction rolled back.
How does SQL handle this.
N.B.
The word 'Serial Number' seems to link to some add somewhere. Nothing to do with me! Just, Don't click it on my behalf.
Thankfully that link has disappeared since. TAKE NOTE IF IT HAPPENS TO YOU. The term 'Serial Number' turned into a link to some mobile phone sellers. Its gone now.
Bram van Kampen
|
|
|
|
|
Maybe I don't fully understand the problem, but SQL Server will handle "transactions" the same way. You declare the start of a transaction "Begin Transaction", do your stuff, then either "Commit" or "Rollback" the activity.
SQL server can automatically generate "serial" numbers for you. Look into the column data-type known as "Identity Column".
Did that answer your question?
|
|
|
|
|
I have created a stored procedure which lists all customers that have 7 days left on their membership.
----------
create proc spGetMemReminder
as
select users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where convert(varchar(10), expiryDate,105) = convert(varchar(10), (getdate() +7), 105)
-------
I would like to insert this list into another table automatically. How do I achieve this? any suggestions appreciated. Thanks
|
|
|
|
|
|
Just use an insert:
CREATE PROCEDURE spGetMemReminder
AS
INSERT INTO YourTableNameHere (FullName, ExpiryDate)
SELECT users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where expiryDate = CAST(DATEADD(day, 7, getdate()) AS DATE
if expiryDate is a date column it is better to do date compare than to convert both sides.
|
|
|
|
|
|
Use an insert statement, that is not why I am posting a reply.
There are many ways to compare dates, comparing string (varchar) is probably the worst possible method. Do some research into the datetime object, you could have used datediff or dateadd neither of which require a convert.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi All,
I have an SSIS package, which is trying to import data from a flat file in to a destination table in SQL. But the problem is not all columns in the destination table should come from flat file, there are few columns that should be filled from some other SQL Server source table.
Can we import data from a flat file and a table simultaneously into one table, is so how can we do that?
Please help me, its urgent. I am also searching but any help like code snippet, link or even a free advice is great.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
Change your strategy to ELT, import your data into a staging table that is an exact match for the source, all varchar columns!
The use a stored proc to transform the data into your final table. This method NEVER fails in the load process because of missing columns or date formats or a number has a $ sign.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Have a five.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Dammed if I know why someone would down vote this it is a perfectly valid question - balanced.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is Entity Framework is best choice for database creation and manipulation in Web application which is made on MVC4.
If yes then how is it ?
|
|
|
|
|
NO the best way is to roll your own DAL layer. You will then have the knowledge and control to manage your data. EF is too much of a black box for my taste.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well said: I used it on a couple of projects but have now gone back to ADO and my own DAL as it gives me more control and I don't feel that ORMs are the be-all and end-all.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Those who seek perfection will only find imperfection
nils illegitimus carborundum
me, me, me
me, in pictures
|
|
|
|
|
hi to all
how to union two below query
thanks in advance
SELECT
dailyAbsences.*,'غیبت' as [RowType], '0' as [RowTypeCode]
INTO
#fullPersonnelDetails2
FROM
#personnelWorkingPeriodRange personnelWorkingPeriodRange
INNER JOIN dbo.tkp_vwDailyAbsences As dailyAbsences
ON dailyAbsences.PersonnelBaseID = personnelWorkingPeriodRange.PersonnelBaseID
And dailyAbsences.StartDate >= personnelWorkingPeriodRange.StartDate
And dailyAbsences.EndDate <= DateAdd(day, 1, personnelWorkingPeriodRange.EndDate)
LEFT JOIN tkp_CalculationQueue As calculationQueue
ON dailyAbsences.PersonnelBaseID = calculationQueue.PersonnelBaseID
And calculationQueue.StartDate <= personnelWorkingPeriodRange.EndDate
ORDER BY
dailyAbsences.PersonnelBaseID,
dailyAbsences.StartDate
UNION
SELECT
'اضافه کاری' as [RowType], '1' as [RowTypeCode]
FROM
dbo.tkp_DailyStatistics dailystatistics
WHERE
dailystatistics.ActualExtraWork > dailystatistics.ExtraWork AND
dailystatistics.PersonnelBaseID = @PersonnelBaseID AND
dailystatistics.WorkingPeriodID = @WorkingPeriodID AND
dailystatistics.YearWorkingPeriodID = @WorkingPeriodYear
IF EXISTS ( SELECT * FROM tempdb..SYSOBJECTS WHERE Name = '#PersonnelWorkingPeriodRange' and xType = 'u')
DROP TABLE #PersonnelWorkingPeriodRange
|
|
|
|
|
Your 2 select statements do not have the same number of columns - union requires the 2 queries to match exactly. dailyabscence.* is not in your 2nd query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Also using * is not advised. Technically okay but it is better to spell out the columns.
|
|
|
|
|
|
|
 General  News  Suggestion  Question  Bug  Answer  Joke  Rant  Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
Red Gate Resources
Free eBooks
Product Announcements
|
|
Submit an article or tip
Post your Blog
