SQL TIPS and Tricks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL TIPS and Tricks

Author

Topic

Viduruvan123
Starting Member

5 Posts

Posted - 2009-06-15 : 05:24:12

I'm new to this forums

Please some one can tell me how to improve eficency of the SQL DDL codes ............
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 05:27:28

I think you are referring to Data Manipulation Language (DML) rather than Data Definition Language (DDL).

KH
[spoiler]Time is always against us[/spoiler]

Viduruvan123
Starting Member

5 Posts

Posted - 2009-06-15 : 05:43:21

Both But currenty focus on creating Datatabels and datatypes and DDL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 05:53:56

There is no way to improve as create table statement.

But perhaps you have a specific problem scenario where we can assist you?

E 12°55'05.63"
N 56°04'39.26"

Viduruvan123
Starting Member

5 Posts

Posted - 2009-06-15 : 06:01:00

quote:
Originally posted by Peso

There is no way to improve as create table statement.

But perhaps you have a specific problem scenario where we can assist you?

E 12°55'05.63"
N 56°04'39.26"

quote:

CREATE Table Supplier(
[Sup_ID]			Int identity(1,1) PRIMARY Key,
[Sup_Type]			Varchar(10) not null check ([Sup_Type] in ('Individual','Company')) DEFAULT 'Individual',
[Sup_ComName]		Varchar(50) ,
[Sup_FName]			Varchar(20)  not null,
[Sup_MName]			Varchar(20) ,
[Sup_LName]			Varchar(30)  not null,
[Sup_Address]		Varchar(20)  not null,
[Sup_Street]		Varchar(30)  not null,
[Sup_City]			Varchar(30)  not null,
[Sup_PostalCode]	Varchar(10) ,
[Sup_TeleBusi]		tpno,
[Sup_Mob]			tpno,
[Sup_Fax]			tpno,
[Sup_Joined]		DateTime  not null,
[Sup_Email]			Varchar(30)CHECK
					(
						CHARINDEX(' ',LTRIM(RTRIM([Sup_Email]))) = 0 -- No embedded spaces
					AND 	LEFT(LTRIM([Sup_Email]),1) <> '@'  -- '@' can't be the first character of an email address
					AND 	RIGHT(RTRIM([Sup_Email]),1) <> '.' -- '.' can't be the last character of an email address
					AND 	CHARINDEX('.',[Sup_Email],CHARINDEX('@',[Sup_Email])) - CHARINDEX('@',[Sup_Email]) > 1 -- There must be a '.' after '@'
					AND 	LEN(LTRIM(RTRIM([Sup_Email]))) - LEN(REPLACE(LTRIM(RTRIM([Sup_Email])),'@','')) = 1 -- Only one '@' sign is allowed
					AND 	CHARINDEX('.',REVERSE(LTRIM(RTRIM([Sup_Email])))) >= 3 -- Domain name should end with at least 2 character extension
					AND 	(CHARINDEX('.@',[Sup_Email]) = 0 AND CHARINDEX('..',[Sup_Email]) = 0) -- can't have patterns like '.@' and '..'
					), --- This code is 100% not my own but I refer some parts from the interner to get this http://xxxxxxxxxxxxxxxxxxxxxxxxxxx
[Sup_NID]			Varchar(10)  not null,
[Sup_Photo]			pic,
[Sup_Resigned]		DateTime ,
[Sup_Statues]		Varchar(15) not null check (Sup_Statues in ('Active','Banned','On Progress','Resigned'))DEFAULT 'On Progress'),
[Sup_Web]			Varchar(50) ,
[Sup_Note]			Text ,
)

is there any way to put check constrain out of the table definition ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 06:04:50

Yes, you can create the table without the CHECK constraint.
You can add the contraint later if you want to.

But since the table is empty when you create it, I'll suggest you keep the check constraint as is, becuase adding it later when table is not empty can fail due to check contraint error and also take a very long time to add.

E 12°55'05.63"
N 56°04'39.26"

Viduruvan123
Starting Member

5 Posts

Posted - 2009-06-15 : 06:19:30

quote:
Originally posted by Peso

Yes, you can create the table without the CHECK constraint.
You can add the contraint later if you want to.

But since the table is empty when you create it, I'll suggest you keep the check constraint as is, becuase adding it later when table is not empty can fail due to check contraint error and also take a very long time to add.

E 12°55'05.63"
N 56°04'39.26"

Thanks

but can we put this

quote:

CHECK
					(
						CHARINDEX(' ',LTRIM(RTRIM([Sup_Email]))) = 0 -- No embedded spaces
					AND 	LEFT(LTRIM([Sup_Email]),1) <> '@'  -- '@' can't be the first character of an email address
					AND 	RIGHT(RTRIM([Sup_Email]),1) <> '.' -- '.' can't be the last character of an email address
					AND 	CHARINDEX('.',[Sup_Email],CHARINDEX('@',[Sup_Email])) - CHARINDEX('@',[Sup_Email]) > 1 -- There must be a '.' after '@'
					AND 	LEN(LTRIM(RTRIM([Sup_Email]))) - LEN(REPLACE(LTRIM(RTRIM([Sup_Email])),'@','')) = 1 -- Only one '@' sign is allowed
					AND 	CHARINDEX('.',REVERSE(LTRIM(RTRIM([Sup_Email])))) >= 3 -- Domain name should end with at least 2 character extension
					AND 	(CHARINDEX('.@',[Sup_Email]) = 0 AND CHARINDEX('..',[Sup_Email]) = 0) -- can't have patterns like '.@' and '..'
					), --- This code is 100% not my own but I refer some parts from the interner to get this http://xxxxxxxxxxxxxxxxxxxxxxxxxxx

as a separate function and call with in the table definiion .............

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 06:29:25

Yes, you can make the statement above as a scalar function and use the function as check instead.

E 12°55'05.63"
N 56°04'39.26"

Viduruvan123
Starting Member

5 Posts

Posted - 2009-06-15 : 06:32:34

quote:
Originally posted by Peso

Yes, you can make the statement above as a scalar function and use the function as check instead.

E 12°55'05.63"
N 56°04'39.26"

Do u have any tutorial or sample codes

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-15 : 12:05:20

This may not cover everything, but you can also use a LIKE for your constraint: Sup_Email LIKE '_%@_%.__%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources