|
I'm playing with the idea to build a sample, on how to integrate the Time Period Library for .NET into the SQL Server.
So far I have build a demo library/assembly/procedure and does some time period calculations using the AdventureWorks catalog ... and it works  .
Because I have little experience using CLR SP, here my questions:
- Makes this integration sense to reduce the complexity of T-SQL programming?
- What's the performance difference between CLR SP and T-SQL?
|
|
|
|
|
From what I have read, the CLR SP seems to actually return data quicker than T-SQL but that it becomes much harder to maintain your application. Before going this route you need to determine which is more important, speedy data return, or application maintenance.
|
|
|
|
|
It certainly can, but have you considered user-defined functions?
|
|
|
|
|
Implicitly, yes.
The question is if it makes sense to provide a library which supports time operations for database tables.
Something like:
Overlaps( startDateColumnName, endDateColumnName, testPeriodStartDate, testPeriodEndDate );
usage:
SELECT * FROM Bookings WHERE Overlaps( 'Start', 'End', '20110101', '20110331' )
Another sample may be:
GetGaps( tableOrViewName, startDateColumnName, endDateColumnName, searchStartDate, searchEndDate );
usage:
EXEC GetCaps( 'Bookings', 'Start', 'End', '20110101', '20110331' ) -- return all gaps
|
|
|
|
|
If you use them a lot then sure.
On my last job I made a database that contained only user-defined functions that I then used in many queries from many databases.
User-defined table-valued functions combine the best of views and stored procedures -- they're like views that can take parameters.
|
|
|
|
|
The idea is to provide a generic library with predefined SPs and UDFs that can be used for complex time period calculations (inside, overlap, gap, intersection...).
For example:
EXEC TimePeriodInfo
@table = 'HumanResources.vEmployeeDepartmentHistory',
@startDateField = 'StartDate',
@endDateField = 'EndDate'
returns (only fake sample):
periods:
05.01.1998 - 30.06.2000 | 907.00:00
20.01.1998 - 15.08.1999 | 572.00:00
08.02.1999 - 01.10.2001 | 966.00:00
04.03.1999 - 15.12.2003 | 1747.00:00
28.03.2001 - 30.08.2001 | 155.00:00
31.08.2001 - 15.08.2002 | 349.00:00
intersected periods:
20.01.1998 - 15.08.2002 | 1668.00:00
combined periods:
05.01.1998 - 15.12.2003 | 2170.00:00
|
|
|
|
|
Hi,
CLR procedures are handy and the programmability is much wider than using plain T-SQL. However, depending on the specs it may/may not be wise to use them.
Few thoughts:
- if you're working with sets (possibly with large amount of rows), building a procedure or function that works row-based may cause intolerable response times. SQL is very good in set based operations (it's designed for that ).
- if you need calculations, complex logic etc then the programmability of .Net will offer much more possibilities over SQL and it may be a better option.
Based on the examples you've written, they may be fairly easy to do with SQL using proper conditions etc and in that case the performance would be far better. But as they were only examples I'm not able to say if that's really the situation.
A good rule of thumb could be that there's usually no need to replace such functionality with CLR operations that already exists in SQL itself unless there's some other benefits and not too much downsides.
Best regards,
mika
|
|
|
|
|
Hi Everybody ,
How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL?
The execution of the 2nd procedure is conditional and based on whether the 1st statement returns any values or not?
Essentially as an example:
select * from cupboard where cupColour = 'white'
if cupboard.CupsFound = 0 then
select * from cupboard
end if
return dataset
Any help would be greatly appreciated...i will continue to search and read
|
|
|
|
|
butchzn wrote: How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL?
One at a time
Try an
IF EXISTS(<your sql statement here>)
BEGIN
<conditional statement here>
END
I are Troll 
|
|
|
|
|
Hi Thanks for bumping me in the right direction i have managed to solve it with your input!
ALTER PROC [dbo].[FindCupByCupIdAndDescription]
@CupID int,
@CupDescription nvarchar(50)
AS
IF EXISTS(SELECT CupID FROM Cupboard WHERE CupId = @CupId)
SELECT * FROM Cupboard
WHERE CupId = @CupId
ELSE
SELECT * FROM Attic
WHERE CupDescription LIKE '%' + @CupDescription + '%'
this is a really simplified example but is essentially the logical illustration of what i was hoping to achieve using a result as a condition. 
Thanks you.
|
|
|
|
|
You're welcome
|
|
|
|
|
Assuming that you want to use either ID or description and not both (does not make sense) then the following will do the job with a conditional where caluse.
SELECT *
FROM TableName
WHERE (ISNULL(@CupID,0)=0 OR CupID = @CupID)
AND (ISNULL(@CupDesc,'') = '' OR CupDesc = @Cupdesc)
There are a number of variations on this, search for "conditional where clause"
Caveat, string too many of these together (5+) and SQL Server gets very cranky
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT T2.* FROM (SELECT TOP(1) Color FROM Cabinet WHERE Color='Yellow') T1 INNER JOIN Cabinet T2 ON 0=0
|
|
|
|
|
Hi All,
I am want to show records in hindi which is retrieve the data from ms sql database through repeater controls as user controls.Kindly request you to please give me a response as soon as possible.
Thanks.
Raghav
|
|
|
|
|
This is not related to DB. If your problem is storing Hindi text following is the answer:
Use a nVarchar or nText column and store data using N"Hindi text here". Your DB server should have unicode Hindi font to view it.
If your problem is displaying it in Repeater, try ASP.Net forum.
|
|
|
|
|
Good answer, my 5
|
|
|
|
|
Thanks.
|
|
|
|
|
Hi to codeproject world!
i have a project with sql server 2008 which has a server and 5 client.
[i mean that the project i have written is running on 6 pc's(one server and 5 client)]
now my problem is when the client1 is editting a record ( for exm:- Id:12 , from table1 )
other 4 client and sever should not have permission to edit that record.
hint :
i send Id to sql and get record and then i start to edit. at that time i am not connected to sql..
|
|
|
|
|
Do you really mean that other clients won't be able to modify the record while it's under editing at some client or that if 2 clients modify the same record, the program can notice this. Typically it's the later choice.
The latter choice can be achieved using optimistic locking. Add a timestamp column (if SQL Server) to your table which will change every time the record is modified. Now always when updating or deleting, include the value of the timestamp column in the where condition. This way you'll notice if something has changed.
The first one can be done using locking. First you lock the record (for example using update), you keep the transaction open, make modifications in UI and then update again and if everything is fine, commit otherwise rollback. However there should never be UI conversations in the middle of a transaction. This will typically cause many problems. If you're not connected to the database, another process can hold the lock while editing is done.
There are of course several other possibilies and especially if you're talking about offline appplication then factors like how long time between connections how much modifications etc will affect the choice. If the amount of time between edit start and end is long then locking isn't a good choice.
|
|
|
|
|
Be very sure this is a real world issue, not just an excercise in theoretical design question on how to build a bulletproof data entry system. Over many years and some fairly high volume data entry apps I have never, not once, had to implement a locking structure to secure against simultaneous editing.
I once wrote a system that kept track of the occurrence of data conflicts to assess their impact. The tracking system ran for 3 weeks and recorded 2 incidents, on a very high volume travel booking system, some twat of a manger had predicted multiple conflicts every hour. When the cost of the tracking was factored in management discarded it.
So make sure you (probably some manager somewhere) are not just wasting your time on unrealistic problems.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks in advance,
I Want to restore a SQL 2005 databse .bak file during setup while i am installing SQL Server in silent mode.
Any Suggestions.
SOFTDEV
Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.
|
|
|
|
|
Why not create a command script (or similar) which first calls the silent setup and after that the script to restore the database. To run the restore script, you could use the newly installed SQLCMD[^].
|
|
|
|
|
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'd:\SQLServerBackups\sampleDB.bak'
WITH FILE=1,
NORECOVERY;
|
|
|
|
|
hi
i want to write a good app for schools to use in regeister student.
please help me to create a good database for that !
thanks for your helping ! ! ! 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
