Database level lock: columns

Showing posts with label columns. Show all posts

Thursday, March 29, 2012

database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
ThanksHi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>|||Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/11549ff4637eff45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>> Hi,
>> IN SQL 2000 you could write a simple Update statement
>> Update Tablename
>> set memo1 = 'discount % ' + memo1
>> This will update all the 20K records in a single shot.
>> Did I answered your question?
>> Thanks
>> Hari
>> SQL Server MVP
>> "Mac" <mac@.hotmail.com> wrote in message
>> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>>
>

Saturday, February 25, 2012

database design question

Hi. I have a new OLTP database requirement, and was wondering what columns
do some of you use in your db design for primary keys, since identity
columns can't really be used for this? I was thinking of using the select
convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
Other suggestions would be appreciated. Thanks.
Alex IvascuWhy are you saying that identity cannot be used for primary keys? I would estimate that perhaps 95%
of the SQL Server population who uses surrogate keys uses identity. And indeed, identity will find
its way into the next ANSI standard.
Having said that, you might want to read Joe Celko's posts regarding identity, even though many
points are inaccurate. So, if you do that, you should also read a good response to that (one from
Tony Rogerson, for instance). You can search the archives using the link at the bottom of my URL.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> Hi. I have a new OLTP database requirement, and was wondering what columns
> do some of you use in your db design for primary keys, since identity
> columns can't really be used for this? I was thinking of using the select
> convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
> Other suggestions would be appreciated. Thanks.
> Alex Ivascu
>
>|||Hi, Tibor
Look, I am not lawyer of Celko and I agree with you that some his posts are
inaccurate.
Just yesterday I faced the problem where I was need to update the column
that is PK with Identity property.
At beginining our business logic did not allow to do such things but as you
know, came a new requirement of production in our company.
I think we falled in these 5 % that you estimated that should be used
identity property for promary key .
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$ADar5lDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Why are you saying that identity cannot be used for primary keys? I would
estimate that perhaps 95%
> of the SQL Server population who uses surrogate keys uses identity. And
indeed, identity will find
> its way into the next ANSI standard.
> Having said that, you might want to read Joe Celko's posts regarding
identity, even though many
> points are inaccurate. So, if you do that, you should also read a good
response to that (one from
> Tony Rogerson, for instance). You can search the archives using the link
at the bottom of my URL.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> > Hi. I have a new OLTP database requirement, and was wondering what
columns
> > do some of you use in your db design for primary keys, since identity
> > columns can't really be used for this? I was thinking of using the
select
> > convert(int, convert(varbinary, newid())) to insert. Any drawbacks to
this?
> > Other suggestions would be appreciated. Thanks.
> >
> > Alex Ivascu
> >
> >
> >
>|||>> was wondering what columns do some of you use in your db design for
primary keys, <<
There is no "magic, one-size-fits-all, universal" answer. Designing a
database is hard work! So, for each and every individual table:
1) Is there an ISO standard?
2) Is there a national standard?
3) Is there an industry standard?
Examples: GTIN for retail, VIN for vehicles, ISBN for books and
publications.
Other questions to ask, if you have to invent a code:
1) Will a human being input it? So, does it need an easy syntax
(airport codes)? Does it need a check digit(ISBN)? What kind of check
digit algorithm?
2) How do I verifiy it in the reality represented in my data model?
Internally or with a trusted external source?
External: government tax numbers. Internal: ISO tire sizes (155R15 =155 cm width, Radial construction, 15 inch diameter; verify with a ruler
and your eyes).
I have four chapers on designing codes in DATA & DATABASES which you
might want to read.
--CELKO--
=========================== Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

database design question

Database design question

I have a database design questions. How can we specify one of two
columns in a table should be not null? In other words, if one column is
null then another column should not be null. Here is an example. Let's
say we have document and we need to specify permissions to the
document. The document has either individual or group level
permissions.
Document table
doc_id int primary,
doc_name varchar(50) not null
User table
user_id varchar(50) primary,
user_name varchar(100) not null
Group table
group_id int primary,
group_name varchar(50) not null
UserGroup table
user_group_id int primary,
group_id int not null,
user_id varchar(50) not null
DocumentPermission table
doc_id int,
group_id int,
user_id varchar(50)
So, either group_id or user_id should not be null in the
DocumentPermission table. Is there anyway we can have constraint like
this on the table? Is creating another group for that user the only
solution (Involves more administration)? Any help would be appreciated.
Thanks,
SreedharNot sure if this could be handled using a constraint or not but you
could create a trigger to ROLLBACK the transaction (and raise an
error), if both the column values are null.
sreedhardasi@.gmail.com wrote:
> I have a database design questions. How can we specify one of two
> columns in a table should be not null? In other words, if one column is
> null then another column should not be null. Here is an example. Let's
> say we have document and we need to specify permissions to the
> document. The document has either individual or group level
> permissions.
> Document table
> doc_id int primary,
> doc_name varchar(50) not null
> User table
> user_id varchar(50) primary,
> user_name varchar(100) not null
> Group table
> group_id int primary,
> group_name varchar(50) not null
> UserGroup table
> user_group_id int primary,
> group_id int not null,
> user_id varchar(50) not null
> DocumentPermission table
> doc_id int,
> group_id int,
> user_id varchar(50)
> So, either group_id or user_id should not be null in the
> DocumentPermission table. Is there anyway we can have constraint like
> this on the table? Is creating another group for that user the only
> solution (Involves more administration)? Any help would be appreciated.
> Thanks,
> Sreedhar|||Hi
If you had a superset of Users and Groups you would not need two columns and
a foreign key constraint could be added. Why are is user_id varchar(50)?
John
"sreedhardasi@.gmail.com" wrote:
> I have a database design questions. How can we specify one of two
> columns in a table should be not null? In other words, if one column is
> null then another column should not be null. Here is an example. Let's
> say we have document and we need to specify permissions to the
> document. The document has either individual or group level
> permissions.
> Document table
> doc_id int primary,
> doc_name varchar(50) not null
> User table
> user_id varchar(50) primary,
> user_name varchar(100) not null
> Group table
> group_id int primary,
> group_name varchar(50) not null
> UserGroup table
> user_group_id int primary,
> group_id int not null,
> user_id varchar(50) not null
> DocumentPermission table
> doc_id int,
> group_id int,
> user_id varchar(50)
> So, either group_id or user_id should not be null in the
> DocumentPermission table. Is there anyway we can have constraint like
> this on the table? Is creating another group for that user the only
> solution (Involves more administration)? Any help would be appreciated.
> Thanks,
> Sreedhar
>|||I guess your suggestion is that I have a superset (assuming both
group_id and user_id are same datatype) and a type column (specifies if
group or individual) in a table and reference this to UserPermission
table. Am I correct? I have user_id varchar(50) because I am storing
windows user names. I guess I just need to add another integer column
to User table and use it as primary. Thanks for your response.
John Bell wrote:
> Hi
> If you had a superset of Users and Groups you would not need two columns and
> a foreign key constraint could be added. Why are is user_id varchar(50)?
> John
> "sreedhardasi@.gmail.com" wrote:
> > I have a database design questions. How can we specify one of two
> > columns in a table should be not null? In other words, if one column is
> > null then another column should not be null. Here is an example. Let's
> > say we have document and we need to specify permissions to the
> > document. The document has either individual or group level
> > permissions.
> >
> > Document table
> > doc_id int primary,
> > doc_name varchar(50) not null
> >
> > User table
> > user_id varchar(50) primary,
> > user_name varchar(100) not null
> >
> > Group table
> > group_id int primary,
> > group_name varchar(50) not null
> >
> > UserGroup table
> > user_group_id int primary,
> > group_id int not null,
> > user_id varchar(50) not null
> >
> > DocumentPermission table
> > doc_id int,
> > group_id int,
> > user_id varchar(50)
> >
> > So, either group_id or user_id should not be null in the
> > DocumentPermission table. Is there anyway we can have constraint like
> > this on the table? Is creating another group for that user the only
> > solution (Involves more administration)? Any help would be appreciated.
> >
> > Thanks,
> > Sreedhar
> >
> >|||Hi
Yes you are correct! It should be alot simpler to use than your current
design and you can add a FK constraint, which you would only be able to do
with a CHECK constraint and function in the current design (which would be
slow!). If necessary you could create views that correspond to the current
users and groups tables.
John
"sreedhardasi@.gmail.com" wrote:
> I guess your suggestion is that I have a superset (assuming both
> group_id and user_id are same datatype) and a type column (specifies if
> group or individual) in a table and reference this to UserPermission
> table. Am I correct? I have user_id varchar(50) because I am storing
> windows user names. I guess I just need to add another integer column
> to User table and use it as primary. Thanks for your response.
> John Bell wrote:
> > Hi
> >
> > If you had a superset of Users and Groups you would not need two columns and
> > a foreign key constraint could be added. Why are is user_id varchar(50)?
> >
> > John
> >
> > "sreedhardasi@.gmail.com" wrote:
> >
> > > I have a database design questions. How can we specify one of two
> > > columns in a table should be not null? In other words, if one column is
> > > null then another column should not be null. Here is an example. Let's
> > > say we have document and we need to specify permissions to the
> > > document. The document has either individual or group level
> > > permissions.
> > >
> > > Document table
> > > doc_id int primary,
> > > doc_name varchar(50) not null
> > >
> > > User table
> > > user_id varchar(50) primary,
> > > user_name varchar(100) not null
> > >
> > > Group table
> > > group_id int primary,
> > > group_name varchar(50) not null
> > >
> > > UserGroup table
> > > user_group_id int primary,
> > > group_id int not null,
> > > user_id varchar(50) not null
> > >
> > > DocumentPermission table
> > > doc_id int,
> > > group_id int,
> > > user_id varchar(50)
> > >
> > > So, either group_id or user_id should not be null in the
> > > DocumentPermission table. Is there anyway we can have constraint like
> > > this on the table? Is creating another group for that user the only
> > > solution (Involves more administration)? Any help would be appreciated.
> > >
> > > Thanks,
> > > Sreedhar
> > >
> > >
>

Sunday, February 19, 2012

Database design

I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided.

My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments.

What is best practice for putting this db structure together?

Thanks

Andrew

You have 4 principal objects: Company, Person, Service and Testimonial. That leads to 4 tables:

Company
CompanyIDPK
Address1
Address2
etc...

Person
PersonIDPK
Title
Initials
FirstName
Surname
Position
CompanyIDFK
etc...

Services
ServiceID
Service

Testimonials
TestimonialIDPK
Testimonial
PersonIDFK
ServiceIDFK

So now each testimonial is linked to a person and a service, and each person is linked to a company, so a testimonial is linked to a company through the person. You can add another table:

CompanyServices
CompanyIDFK
ServiceIDFK

to manage the relationship between companies and all the services they avail themselves of, regardless of whether they comment on it.

Thursday, March 29, 2012

database in SQL2K need help in upading/adding 2 memo columns

database in SQL2K need help in upading/adding 2 memo columns

database in SQL2K need help in upading/adding 2 memo columns

Saturday, February 25, 2012

database design question

database design question

Database design question

Sunday, February 19, 2012

Database design

Database level lock

Blog Archive

About Me