Can you use DATEDIFF in create table statements?
-
Hi there,
I would like to be able to use a DEFAULT constraint on a table (if possible), that would use DATEDIFF to calculate the default value to be inserted.
Not sure if this is possible... Of course, I know I can do it via the stored procedure that will be doing the insert for the data, but I've been "exploring" my SQL syntax to see what other ways things can be handled.
I.E.: Code sample might look like... if it worked
CREATE TABLE dbo.tablename
(--other columns, etc...
, OnloadFormDateTime
DATETIME
NOT NULL
--pulled from form (in session variable) as actual "start" time of the form loading for use
, FormDataInsertDateTime
DATETIME
NOT NULL
DEFAULT CURRENT_TIMESTAMP
--"Calculated field"
, ElaspedDataTime
INT
NOT NULL
DEFAULT
-- SELECT --?
DATEDIFF (
minute
, OnloadFormDateTime --data from above column
, @FormDataInsertDateTime --Can this be the column name instead? I.e.: FormDataInsertDateTime
)
--Or does the enddate have to be a variable, but there no way to "declare" it within the create table statement that I can find
)
Make sense?
TIA,
Dolphin
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
I believe the syntax you want for ElapsedDataTime is as follows:
, ElapsedDataTime AS DATEDIFF( minute, OnloadFormDateTime, FormDataInsertDateTime )
The above will define it as a calculated column meaning it has no real physical per row storage and is always the difference in number of minutes between the other two real columns.
-
I don't think you should place this into your DDL. This information becomes only interesting when you request it and then it is very easy to calculate this in a query. Like this:
SELECT
CustomerID
, OrderDate
, ShippedDate
, DATEDIFF(d,OrderDate,ShippedDate) AS How_long_it_takes
FROM
Northwind..orders
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Aaron,
Thanks for your help.. that indeed does work, but you don't have to provide a datatype, etc for that calculated column then? Or does it just "figure" that out?
Frank,
I agree with you, it should really be calculated on a request based basis only. That is why I said I know I could handle it as part of a stored proc, etc. But the DB that I am re-designing is part of a small version upgrade (1.1) to an existing small (but rapidly growing) specialized health care system. It is rather unique since the person who originally created the system is a nurse, but with no database design experience.
So I am fighting some challenges just to get the V1.1 DB to a more normalized state since he hired me to do it, but challenges every other thing that I do to make the DB better. V 2.0 will likely a total re-design and I will hopefully have more control (or he will have more trust to just let me do my job), as the system has some serious growth to take into consideration.
So to try and make a long story short
, he wants this column that has the calculated value of the elapsed time from when a form is "opened" in the users browser, to when they "hit print" and the command is executed to insert the data into the table. It's a pretty weird system, as the front-end forms map exactly to the tables in the version he created. And as much as I have tried to explain that is doesn't matter what table or where it is stored in the database, we've ended up with pretty much the same thing in the re-design I'm doing. Mostly he just doesn't get it and it isn't hurting things much, as I have managed to add about 10-15 tables to the design to deal with normalizing about 90% of the data.As I mentioned, I've been "exploring" my SQL syntax a bit more as I didn't work with MS-SQL for about the last 2 yrs. I have lots of code samples back from school that I am looking at now, but with very little explanation as to why a create table (or a stored procedure for that matter) should or shouldn't contain certain elements.
Can you offer any reason why this column shouldn't even exist, other than it can be calculated from 2 others on demand (if reporting required it)?
I've done more reading than posting on SQL Server Central over the past 2 yrs here, and your discussion comments have always offered insight into a problem or SQL language.
By the way.. any good resources/examples anyone can recommend for more advanced SQL relating to SPs, triggers, etc.. I.e. error handling, etc.
Thanks both of you for all your help!
Cheers, Dolphin
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
Can you offer any reason why this column shouldn't even exist, other than it can be calculated from 2 others on demand (if reporting required it)?
Isn't that reason enough?
By the way.. any good resources/examples anyone can recommend for more advanced SQL relating to SPs, triggers, etc.. I.e. error handling, etc.
Maybe some of these will help:
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/catid,1/
The sites of the SQL Server MVP's should be a really good start.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
About the data type, SQL Server determines it based upon what you provide as the calculation. That statement and more can be found at this link: http://www.winnetmag.com/Articles/Index.cfm?ArticleID=22831&DisplayTab=Article
It sounds like you have an acceptable case for using one, even though it may be best to avoid it in the future, depending upon performance issues. Related to that, I also got the following from: http://www.yafla.com/papers/SQL_Server_Performance/high_performance_sql_server_pt2_1.htm
-----------------------------------------
At the outset I advocated that you minimize space usage (increasing real data density). The goal wasn’t to try to fit that database on a floppy disk, but rather to minimize the amount of I/O necessary to satisfy a given query, as I/O is the weakest link of most enterprise systems. There are design choices, such as adding additional indexes, that actually increase the size of your database on disk yet reduce the I/O necessary for certain queries, and these are usually very worthwhile trade-offs.
Another powerful technique you can use to trade disk space for improved database performance is indexed calculated columns. There are countless variations, but I’ll cover one scenario that is fairly commonly used – report counts by month for a given year. In the case of the Orders table this could be achieved via the following query.
SELECT YEAR(OrderDate) AS [Year], MONTH(OrderDate) AS [Month], COUNT(*) AS [Monthly Orders]FROM Orders
WHERE YEAR(OrderDate)=1997
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
Instead of adhoc decomposing the date into month and year constituents, consider adding them as computed columns.
ALTER TABLE dbo.Orders ADDOrderDateYear AS CONVERT(smallint,YEAR(OrderDate)),
OrderDateMonth AS CONVERT(tinyint,MONTH(OrderDate))
Now we can change our query to the following.
SELECT OrderDateYear AS [Year], OrderDateMonth AS [Month], COUNT(*) AS [Monthly Orders]FROM Orders
WHERE OrderDateYear=1997
GROUP BY OrderDateYear, OrderDateMonth
By itself we’ve done nothing for the query efficiency (in fact it is actually less efficient as it’s applying the where predicate after building the set), though we’ve achieved a bit of “code re-use”. However we now have the foundations for some powerful indexed computed columns.
CREATE NONCLUSTERED INDEX IX_OrderDateDecomposed ON dbo.Orders(
OrderDateYear,
OrderDateMonth
) ON [PRIMARY]
GO
Now the query referencing these computed columns is dramatically more efficient. Even better, these indexed computed columns haven’t decreased the real data density of the table because they’re only materialized in the index. NOTE: Ensure that queries that don’t need these computed fields don’t pull them explicitly or implicitly via the wasteful * column selector, as it’ll unnecessarily calculate each of the computed fields for each row.
-----------------------------------------
-
Isn't that reason enough?
Maybe some of these will help:
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/catid,1/
The sites of the SQL Server MVP's should be a really good start.
Frank,
I know.. it should be reason enough (for a DBA or developer), but try explaining that to a "user", who thinks he knows something about development b/c he created the 1st version of app. and has taken a couple evening/weekend courses... I have to be really diplomatic when dealing with this situation. Because even when he says "no I don't have a problem with the changes/improvements we need to make", it's pretty obvious that just by saying so (often) he does have issues. So I have to be very careful of the changes/improvements I make to "his baby".
Thanks for the link. I looked at the various MVP's sites, but didn't find anything more than I did on my numerous goggle searches on error handling & stored procedures. Perhaps I will start a new thread just on that topic and post some code samples to get feedback on.
Cheers, Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
About the data type, SQL Server determines it based upon what you provide as the calculation. That statement and more can be found at this link: http://www.winnetmag.com/Articles/Index.cfm?ArticleID=22831&DisplayTab=Article
It sounds like you have an acceptable case for using one, even though it may be best to avoid it in the future, depending upon performance issues. Related to that, I also got the following from: http://www.yafla.com/papers/SQL_Server_Performance/high_performance_sql_server_pt2_1.htm
Aaron,
Thanks for explanation... I thought that might be the case, but couldn't find anything in my "initial" searches that said anything about the datatype being defined by the calculation.
I found the "5 Things You Probably Didn't Know About T-SQL" pretty interesting.. and it is certainly true that there will always be knowledge gaps to keep working on.
Haven't made it all the way through the other, as I try and give myself a time limit on reading/exploring for the day, as it is too easy to get absorbed in it and not get any actual work done! I did scan it though and am looking forward to the Indexes info, as I think it will address a few other questions I currently have stewing.
Regarding your excerpt, I won't need to worry about creating an index on the computed column, at least in this case (or any others that I can think of in our model). But it is useful information to file away for that rainy day
Lastly, I don't think in this case there will be performance issues. But I could see in much more complex calculations, where it would be very foolish to place it in the DDL like I am doing (still debating a bit now though) here, and in that case Frank's arguments are a go all the way, either to only output at the reporting level, or to perform the calculation in a SP and then insert that final value into the column.
Anyway, thanks all... I appreciate the posts, links, comments, etc...
Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply