good practice?

A couple of questions.
1)
I have a column Event_End_Date with smalldatetime data type. I also have
following update scripts that gets executed every day. Basically I do not
care about the time portion of smalldatetime (or rather it needs to be
default time format 00:00:00)
UPDATE tblEvents
SET Event_End_Date = CONVERT(VARCHAR, GETDATE(), 101)
Now is it more proper to program it following way? I know in other
programming language like Java, C++, you would always have to explicitly
cast it to the appropriate data type. How sensitive do I need to be when it
comes to casting in TSQL?
UPDATE tblEvents
SET Event_End_Date = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS SMALLDATETIME)
2) I have read somewhere that the following query needs to be rewritten
SELECT *
FROM tblEvents
WHERE Event_End_Date = '6/7/2006'
to
SELECT *
FROM tblEvents
WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
Of course, should there be an index Event_End_Date, it is useless with the
above query. What's your opinion on this matter relating to datetime data
types?
Thanks all.Justin,
If you use style 112, you do not have to worry about casting because sql
server will always interprets the value correctly as a datetime value, not
matter what language or date format is using your server.
UPDATE tblEvents
SET Event_End_Date = CONVERT(varchar(8), GETDATE(), 112)
If you do not pass the time portion from the client application, sql server
will default it to 00:00:00.000

> 2) I have read somewhere that the following query needs to be rewritten
> SELECT *
> FROM tblEvents
> WHERE Event_End_Date = '6/7/2006'
> to
> SELECT *
> FROM tblEvents
> WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
> Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
Not really. If the values in the column has time portion equal to
"00:00:00.000", then it will work without problems. If the values in the
column include tiem portion other that 12 AM, then you should use the patter
n:
...
where
Event_End_Date >= convert(varchar(8), @.d, 112)
Event_End_Date < dateadd(day, 1, convert(varchar(8), @.d, 112))
This way, sql server will make a proper use of an index if this exists. When
you manipulate the column in the "where" clause, sql server does not conside
r
the expresion as a search argument.
Example:
..
where
Event_End_Date >= '20060607'
Event_End_Date < '20060608'
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
AMB
"Justin" wrote:

> A couple of questions.
> 1)
> I have a column Event_End_Date with smalldatetime data type. I also have
> following update scripts that gets executed every day. Basically I do not
> care about the time portion of smalldatetime (or rather it needs to be
> default time format 00:00:00)
> UPDATE tblEvents
> SET Event_End_Date = CONVERT(VARCHAR, GETDATE(), 101)
> Now is it more proper to program it following way? I know in other
> programming language like Java, C++, you would always have to explicitly
> cast it to the appropriate data type. How sensitive do I need to be when
it
> comes to casting in TSQL?
> UPDATE tblEvents
> SET Event_End_Date = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS SMALLDATETIM
E)
> 2) I have read somewhere that the following query needs to be rewritten
> SELECT *
> FROM tblEvents
> WHERE Event_End_Date = '6/7/2006'
> to
> SELECT *
> FROM tblEvents
> WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
> Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
> Of course, should there be an index Event_End_Date, it is useless with the
> above query. What's your opinion on this matter relating to datetime data
> types?
> Thanks all.
>
>

Good practice when working with objects using SQLServer as a secure store.

I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.

I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.

I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?

I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)

tia

John

I think what you're asking is more on the client (VB programming) side than strictly in the database layer. I'd recommend checking out some of the "best practices" books and sites - you can check this one out to start:

http://msdn2.microsoft.com/en-us/vbasic/ms789183.aspx

Sorry if that's too basic - you may have already seen that site.

Buck Woody

http://www.buckwoody.com

Good or Bad practice?

Is it a bad practice to create small, one column tables to store list type
data?
like,
nameprefix: Mr., Mrs., Miss, Dr., Sir
name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
My thought is if you ever want to add to these lists you only have to do it
in the table instead of any application that uses the fields, on the other
hand the fields rarely change and you would always have to query the
database to get the values.
I know its trivial, but does anyone have an opinion on this?DAC,
Check out
http://www.windowsitpro.com/SQLServ.../5226/5226.html
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
> it in the table instead of any application that uses the fields, on the
> other hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
>|||I personally make them two column tables, with an IDENTITY column, because
if you ever have to globalize your app, you will need different information
for each supported language, so it's simpler to store the IDENTITY value in
the tables that use information in the list instead of the localized values.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
it
> in the table instead of any application that uses the fields, on the other
> hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
>|||Basically what you're talking about here is creating a domain - the set
of allowed values for a column.
To create a domain, my preferred way is to create a table and reference
that table.
This can be done with a check constraint as well, but if the allowed
values change you would have to alter the table.
I would never hard-code these into an application - too easy to get out
of synch with the domain.
DazedAndConfused wrote:

>Is it a bad practice to create small, one column tables to store list type
>data?
>like,
>nameprefix: Mr., Mrs., Miss, Dr., Sir
>name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
>My thought is if you ever want to add to these lists you only have to do it
>in the table instead of any application that uses the fields, on the other
>hand the fields rarely change and you would always have to query the
>database to get the values.
>I know its trivial, but does anyone have an opinion on this?
>
>
>|||"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
> it in the table instead of any application that uses the fields, on the
> other hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
To summarize the other (on point) comments: Good practice.
David|||Agreed. Good practice.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uUxrf6guFHA.3756@.tk2msftngp13.phx.gbl...
> I personally make them two column tables, with an IDENTITY column, because
> if you ever have to globalize your app, you will need different
information
> for each supported language, so it's simpler to store the IDENTITY value
in
> the tables that use information in the list instead of the localized
values.
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
type
> it
other
>|||By storing the meta data in a table, you make it easily accessable and
define what the valid options are. If you let the app developers store this
info in arrays, I swear it will be scattered in 10 different locations at
least. Also, even though you may think that the table needs only the one
column, you will probably find that other attributes can be logically stored
there as well.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
> it in the table instead of any application that uses the fields, on the
> other hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
>|||You have two choices:
1) a one-column table
2) a CHECK() constraint
Rule of thumb: If the list is long and changes a lot and the data
element is used in many places, use #1. If the list is short, fixed and
the data element is used in one place, use #2
Get a copy of SQL PROGRAMMING STYLE for the answers to these and other
questions.|||DazedAndConfused wrote:
> Is it a bad practice to create small, one column tables to store list
> type data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to
> do it in the table instead of any application that uses the fields,
> on the other hand the fields rarely change and you would always have
> to query the database to get the values.
> I know its trivial, but does anyone have an opinion on this?
Are you using these values to provide either validation or dropdown lists
for client-side data entry? If so, then you can't use a constraint.
I don't know what programming language you are using but it should be
possible to cache the data from these tables locally, eliminating the
necessity to go to the database every time. ASP.Net makes this easy. It can
also be done in ASP. If you are creating desktop apps, then it's even
easier.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.