Q: My table which PK column cotains 20 rows, row 9 was deleted, now I want a new record to be inserted in row 9 and not 21 – how can I do this?
Patrick O’Keeffe says: New rows are always inserted at the end of the table, unless the table has a clustered index in which case the position depends on the index value.
There is no concept of a “row number” in SQL Server, sorry!
Q: 1) Could you provide comparison in solving SQL problems by using set-based vs. procedural SQL? Please provide comparison with sample codes in two different methods. 2) How do you prevent DB programmers from writing inefficient SQL procedures? Would you provide the optimal codes in services and allow them to call your data layer service by using web methods? Please give some coding examples for this question as well.
Kevin Kline says: Your questions indicate that you’re quite new to database programming. You might not realize it, but you’re asking questions whose answers, if done properly, fill entire books.
Your first question is easier to answer. SQL, by its very nature is set-based. Every time you execute a SELECT statement (as well as INSERT, UPDATE, and DELETE statemetns), you’re acting upon a set of records. Compare a standard SELECT statement to the procedural, a.k.a. row-by-row, approach of cursors. Cursors enable you to work on records in a SQL Server table on a row-by-row basis. Cursors are a big topic, so read about them at http://www.databasejournal.com/features/mssql/article.php/1439731 for a nice little summary. But you sacrifice performance, memory space, and lock resources by doing so. Read about problems caused by cursors at http://www.sql-server-performance.com/dp_no_cursors.asp
Your second question is much more expansive and difficult to answer because there are a variety of ways to improve bad stored procedures. Rather than recount them all here, why don’t you check out the great resources for stored procedure performance tuning at SQL Server Performance.com – http://www.sql-server-performance.com/stored_procedures.asp
There are lots of examples in all of the URLs listed. I hope this helps.
Q: I must create a new identity column in table ‘A’ based on using the Max(rec_id) from table ‘B’ and incrementing by 1 for each subsequent row in table ‘A’. This is so I may append table ‘A’ with table ‘B’ and maintain contiguous (rec_id) numbering. This processing is done within scripts and must be done without intervention…how should I proceed?
Kevin Kline says: I’m not exactly sure what you’re shooting for here. If it were left to me, I’d choose a simpler approach and define Table A as all starting at 1,000,000 and Table B starting at 2,000,000 – or something like that. Then, it’s just a matter of setting the seed value of the IDENTITY attribute on each column. You could then retrieve the data from both columns using:
SELECT * FROM tableA
UNION ALL
SELECT * FROM tableB
GO
And all of the data will come out in one nice, flowing stream except that you’d have a large gap in record numbers from Table A to Table B.
If, on the other hand, you need something like:
Record ID Table of Origin
———– —————–
0001 Table A
0002 Table A
0003 Table B
0004 Table B
0005 Table A
0006 Table B
Then you’ll have to go down a more complex route. In this case, I’d use computed columns. Read about computed columns at http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c06ppcsq.mspx
You could also use a DEFAULT value for the column using a function to return a value every time a row identifier is needed. Note that this approach is likely to be slow. So your application should require a relatively low level of OLTP capabilities.
Hope this helps!
Q: Is there any procedure cache issue in SQL Server 2005?
Patrick O’Keeffe: No issues, however plan cache behaviour has changed in SQL Server 2005 – see http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx
Q: What is the syntax for retreiving date from dob column in a specified format, for example “2nd of july 1985″?
James Delve says: Here is an example:
I have chosen to represent the Hire_date from pubs..authors as a DOB column, but you get the picture.
use pubs
go
select cast(datepart(DD, hire_date) as varchar(10)) + ‘ ‘ + ‘of’ + ‘ ‘ + cast(datename([Month],Hire_date) as varchar(12))
+ ‘ ‘ + cast(Datepart(YYYY, Hire_date) as varchar(10))
as ‘DOB’
from pubs..employee
Q: I have a database DB1 with 250 tables and second database DB2 with 25 tables which are also in DB1 . Now i want to copy data of only 25 same tables from DB1 to DB2 . What is the best way to do this ?? This is done almost daily, so we can’t use import export wizard. I created a secondary filegroup and added tables in that group. Now will take the backup using secondary Filegroup. Is this the right way to solve the problem?
Kevin Kline says: No, I don’t believe your approach is the best solution.
The import/export wizard is the best way to go and is very easy to schedule. The import/export wizard is actually just a tiny subset of the features available in SQL Server Integration Services (SSIS) of SQL 2005 and Data Transformation Services (DTS) in SQL 2000.
Assuming you’re running SQL Server 2000, you should create a simple daily job to move the data over that can be set in a schedule using DTS. You can get started with DTS by reading the content on this URL: http://support.microsoft.com/kb/222073/en-us
Q: This question actuallyl has two parts:
1). I have table emp with fields eno(employee number),dob(date of birth). And the retirement age is 58. -In this I have to find the employee who is above the age of 51 and the number of days remaining to the retirement.
2). In a table there are four fields of number type. my question is how to find the minimum value among the four fields.
Iain Kick says: Two part question, huh? Well, here’s a two part answer…with code!
1) declare @calcdays int
declare @retiredays int
–number of days in 51 years
set @calcdays = datediff(day, dateadd(yy, -51, getdate()), getdate())
–number of days in 58 years
set @retiredays = datediff(day, dateadd(yy, -58, getdate()), getdate())
–return those between 51 and 58 and number of days left
select eno, dob, (@retiredays – datediff(day, dob, getdate())) as DaysToRetirement
from emp
where datediff(day, dob, getdate()) between @calcdays and @retiredays
2) Ugly but it works:
select
case
when num1 <= num2 and num1 <= num3 and num1 <= num4 then num1
when num2 <= num1 and num2 <= num3 and num2 <= num4 then num2
when num3 <= num1 and num3 <= num2 and num3 <= num4 then num3
when num4 <= num1 and num4 <= num2 and num4 <= num3 then num4
end as lowestnumber
from numtable
Q: Last night my SQL Server instance apparently froze – when I could login and sp_who2, I see GHOST CLEANUP as the top process using cpu. It is not killable. What is it doing, and how can I prevent it from running during critical time periods? (NOTE: this was on a virtual machine).
Bryan Oliver says: Here’s a couple of things that you might want to do and/or check to help get past this issue.
1. Have you got auto shrink turn on for any of the databases or have you got a schd task to run a shrink?
2. Are you getting errors in the SQL log? Sounds like if there is a Delete command pending which maybe causing the problem. Do a SQL Profiler at the times when the Ghost Clean up occurs (before if you can).
3. Also it may be worth running a DBCC Checkdb on the database that is causing the Ghost Clean Up process.
4. Check these items on the Microsoft knowledge base as well:
http://support.microsoft.com/kb/931975
http://support.microsoft.com/kb/815594
Kevin Kline says: The Ghost Thread is a system activity that monitors other normal & natural internal processes such as the lazywriter, checkpoints, and so forth. I find it hard to believe that it’s consuming many of the system resources unless there’s a bug at play. One such bug that comes into play is when you have memory problems associated with MemToLeave or with opening but not closing XML documents. Make sure you’re up-to-date on the latest service pack for your version of SQL Server (you didn’t say which).
Finally, you can start-up SQL Server without a Ghost Process by using the -T661 startup switch. I do not recommend this course of action, however, a Microsoft PSS rep may advise you to do so. Without the Ghost Thread, you won’t see those omnipresent 4 or 5 activities in Current Activities (or via sp_who) since the Ghost Thread is what monitors those internal processes.
Q: What is Magic Table? how it is used in Sql Serve while executing Stored Procedure or Trigger or Function etc….? It is possible to Execute the Stored Procedure Inside the View or Stored Procedure can Created in the View?
Iain Kick says: Magic tables are referenced in triggers and contain the inserted and deleted data for reuse. If you want to get more details on these, I’m going to refer to you this article on the site SQL Server Perfromance.
Q: How do I remove duplicate rows in database how to access small anil not capt ANIL from database?
Bryan Oliver says: There are some really great resources available online that I want to point out that can help you here. Take a look at this code for removing duplicate data on SQL Server Performance.
As for you case sensitive query take a look at this blog by Vyas Kondreddi that should answer your question (great resource by the way).