|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
INDEX ON VARIABLE tableI create a variable table using this statement in my UDF ,I'm like to put an
index on of its field (Internal_ID),How can I do it? DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID NUMERIC(9),COURSE_CODE VARCHAR(3), COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), INTERNAL_ID NUMERIC(9)) Thanks You cannot explicitly create indexes on a table variable, rather only those
created by unique or primary key constraints. If internal_id is unique, create a unique constraint on it: DECLARE @TBEnrollmentsForActiveClients TABLE ( ENROLLMENT_ID NUMERIC(9), COURSE_CODE VARCHAR(3), COURSE_YEAR NUMERIC(5), COURSE_SESSION NUMERIC(5), INTERNAL_ID NUMERIC(9) UNIQUE ) If it isn't, make it the first column of a unique constraint, and include additional columns that would make it unique. Show quote "Ray5531" <Ray***@microsft.com> wrote in message news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... > I create a variable table using this statement in my UDF ,I'm like to put an > index on of its field (Internal_ID),How can I do it? > > DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID > NUMERIC(9),COURSE_CODE VARCHAR(3), > COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), > INTERNAL_ID NUMERIC(9)) > > Thanks > > Dose it make a join on internal_ID boosted.The reason I was trying to create
index on it is that I'm joing this varaible table with another table on internal_id .. Thanks Show quote "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... > You cannot explicitly create indexes on a table variable, rather only > those > created by unique or primary key constraints. If internal_id is unique, > create a unique constraint on it: > > DECLARE @TBEnrollmentsForActiveClients TABLE > ( > ENROLLMENT_ID NUMERIC(9), > COURSE_CODE VARCHAR(3), > COURSE_YEAR NUMERIC(5), > COURSE_SESSION NUMERIC(5), > INTERNAL_ID NUMERIC(9) UNIQUE > ) > > If it isn't, make it the first column of a unique constraint, and include > additional columns that would make it unique. > > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Ray5531" <Ray***@microsft.com> wrote in message > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... >> I create a variable table using this statement in my UDF ,I'm like to put > an >> index on of its field (Internal_ID),How can I do it? >> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >> NUMERIC(9),COURSE_CODE VARCHAR(3), >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >> INTERNAL_ID NUMERIC(9)) >> >> Thanks >> >> > > Sure can.
Just keep in mind that the optimizer doesn't maintain statistics for table variables like it does for regular and temp tables. So make sure you benchmark solutions with temp tables as well, comparing the performence with table variables. Show quote "Ray5531" <Ray***@microsft.com> wrote in message news:#hu8xY0VFHA.1044@TK2MSFTNGP10.phx.gbl... > Dose it make a join on internal_ID boosted.The reason I was trying to create > index on it is that I'm joing this varaible table with another table on > internal_id .. > > Thanks > "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message > news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... > > You cannot explicitly create indexes on a table variable, rather only > > those > > created by unique or primary key constraints. If internal_id is unique, > > create a unique constraint on it: > > > > DECLARE @TBEnrollmentsForActiveClients TABLE > > ( > > ENROLLMENT_ID NUMERIC(9), > > COURSE_CODE VARCHAR(3), > > COURSE_YEAR NUMERIC(5), > > COURSE_SESSION NUMERIC(5), > > INTERNAL_ID NUMERIC(9) UNIQUE > > ) > > > > If it isn't, make it the first column of a unique constraint, and include > > additional columns that would make it unique. > > > > -- > > BG, SQL Server MVP > > www.SolidQualityLearning.com > > > > > > "Ray5531" <Ray***@microsft.com> wrote in message > > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... > >> I create a variable table using this statement in my UDF ,I'm like to put > > an > >> index on of its field (Internal_ID),How can I do it? > >> > >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID > >> NUMERIC(9),COURSE_CODE VARCHAR(3), > >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), > >> INTERNAL_ID NUMERIC(9)) > >> > >> Thanks > >> > >> > > > > > > I know that sql server hugely uses statistics to execute queries iin the
best way,but I just wondred how it uses statistcs and what are they? why we should update them with fullscan sometimes? Thanks Show quote "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message news:eeEcYV1VFHA.2572@TK2MSFTNGP14.phx.gbl... > Sure can. > > Just keep in mind that the optimizer doesn't maintain statistics for table > variables like it does for regular and temp tables. So make sure you > benchmark solutions with temp tables as well, comparing the performence > with > table variables. > > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Ray5531" <Ray***@microsft.com> wrote in message > news:#hu8xY0VFHA.1044@TK2MSFTNGP10.phx.gbl... >> Dose it make a join on internal_ID boosted.The reason I was trying to > create >> index on it is that I'm joing this varaible table with another table on >> internal_id .. >> >> Thanks >> "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in > message >> news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... >> > You cannot explicitly create indexes on a table variable, rather only >> > those >> > created by unique or primary key constraints. If internal_id is unique, >> > create a unique constraint on it: >> > >> > DECLARE @TBEnrollmentsForActiveClients TABLE >> > ( >> > ENROLLMENT_ID NUMERIC(9), >> > COURSE_CODE VARCHAR(3), >> > COURSE_YEAR NUMERIC(5), >> > COURSE_SESSION NUMERIC(5), >> > INTERNAL_ID NUMERIC(9) UNIQUE >> > ) >> > >> > If it isn't, make it the first column of a unique constraint, and > include >> > additional columns that would make it unique. >> > >> > -- >> > BG, SQL Server MVP >> > www.SolidQualityLearning.com >> > >> > >> > "Ray5531" <Ray***@microsft.com> wrote in message >> > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... >> >> I create a variable table using this statement in my UDF ,I'm like to > put >> > an >> >> index on of its field (Internal_ID),How can I do it? >> >> >> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >> >> INTERNAL_ID NUMERIC(9)) >> >> >> >> Thanks >> >> >> >> >> > >> > >> >> > > J-R, try this whitepaper:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322096 Show quote "J-R" <Ray***@microsft.com> wrote in message news:OeCMv01VFHA.2960@TK2MSFTNGP15.phx.gbl... > I know that sql server hugely uses statistics to execute queries iin the > best way,but I just wondred how it uses statistcs and what are they? why we > should update them with fullscan sometimes? > > Thanks > "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message > news:eeEcYV1VFHA.2572@TK2MSFTNGP14.phx.gbl... > > Sure can. > > > > Just keep in mind that the optimizer doesn't maintain statistics for table > > variables like it does for regular and temp tables. So make sure you > > benchmark solutions with temp tables as well, comparing the performence > > with > > table variables. > > > > -- > > BG, SQL Server MVP > > www.SolidQualityLearning.com > > > > > > "Ray5531" <Ray***@microsft.com> wrote in message > > news:#hu8xY0VFHA.1044@TK2MSFTNGP10.phx.gbl... > >> Dose it make a join on internal_ID boosted.The reason I was trying to > > create > >> index on it is that I'm joing this varaible table with another table on > >> internal_id .. > >> > >> Thanks > >> "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in > > message > >> news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... > >> > You cannot explicitly create indexes on a table variable, rather only > >> > those > >> > created by unique or primary key constraints. If internal_id is unique, > >> > create a unique constraint on it: > >> > > >> > DECLARE @TBEnrollmentsForActiveClients TABLE > >> > ( > >> > ENROLLMENT_ID NUMERIC(9), > >> > COURSE_CODE VARCHAR(3), > >> > COURSE_YEAR NUMERIC(5), > >> > COURSE_SESSION NUMERIC(5), > >> > INTERNAL_ID NUMERIC(9) UNIQUE > >> > ) > >> > > >> > If it isn't, make it the first column of a unique constraint, and > > include > >> > additional columns that would make it unique. > >> > > >> > -- > >> > BG, SQL Server MVP > >> > www.SolidQualityLearning.com > >> > > >> > > >> > "Ray5531" <Ray***@microsft.com> wrote in message > >> > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... > >> >> I create a variable table using this statement in my UDF ,I'm like to > > put > >> > an > >> >> index on of its field (Internal_ID),How can I do it? > >> >> > >> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID > >> >> NUMERIC(9),COURSE_CODE VARCHAR(3), > >> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), > >> >> INTERNAL_ID NUMERIC(9)) > >> >> > >> >> Thanks > >> >> > >> >> > >> > > >> > > >> > >> > > > > > > Actually ENROLLMENT_ID is unique so I can 100% garanty that combination of
ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code below? Thanks Show quote "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... > You cannot explicitly create indexes on a table variable, rather only > those > created by unique or primary key constraints. If internal_id is unique, > create a unique constraint on it: > > DECLARE @TBEnrollmentsForActiveClients TABLE > ( > ENROLLMENT_ID NUMERIC(9), > COURSE_CODE VARCHAR(3), > COURSE_YEAR NUMERIC(5), > COURSE_SESSION NUMERIC(5), > INTERNAL_ID NUMERIC(9) UNIQUE > ) > > If it isn't, make it the first column of a unique constraint, and include > additional columns that would make it unique. > > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Ray5531" <Ray***@microsft.com> wrote in message > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... >> I create a variable table using this statement in my UDF ,I'm like to put > an >> index on of its field (Internal_ID),How can I do it? >> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >> NUMERIC(9),COURSE_CODE VARCHAR(3), >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >> INTERNAL_ID NUMERIC(9)) >> >> Thanks >> >> > > Just make both part of the unique constraint or primary key:
DECLARE @TBEnrollmentsForActiveClients TABLE ( ENROLLMENT_ID NUMERIC(9), COURSE_CODE VARCHAR(3), COURSE_YEAR NUMERIC(5), COURSE_SESSION NUMERIC(5), INTERNAL_ID NUMERIC(9), UNIQUE(ENROLLMENT_ID, INTERNAL_ID) ) or DECLARE @TBEnrollmentsForActiveClients TABLE ( ENROLLMENT_ID NUMERIC(9), COURSE_CODE VARCHAR(3), COURSE_YEAR NUMERIC(5), COURSE_SESSION NUMERIC(5), INTERNAL_ID NUMERIC(9), PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID) ) Show quote "Ray5531" <Ray***@microsft.com> wrote in message news:#BGDjd0VFHA.2700@TK2MSFTNGP12.phx.gbl... > Actually ENROLLMENT_ID is unique so I can 100% garanty that combination of > ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code > below? > > Thanks > "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message > news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... > > You cannot explicitly create indexes on a table variable, rather only > > those > > created by unique or primary key constraints. If internal_id is unique, > > create a unique constraint on it: > > > > DECLARE @TBEnrollmentsForActiveClients TABLE > > ( > > ENROLLMENT_ID NUMERIC(9), > > COURSE_CODE VARCHAR(3), > > COURSE_YEAR NUMERIC(5), > > COURSE_SESSION NUMERIC(5), > > INTERNAL_ID NUMERIC(9) UNIQUE > > ) > > > > If it isn't, make it the first column of a unique constraint, and include > > additional columns that would make it unique. > > > > -- > > BG, SQL Server MVP > > www.SolidQualityLearning.com > > > > > > "Ray5531" <Ray***@microsft.com> wrote in message > > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... > >> I create a variable table using this statement in my UDF ,I'm like to put > > an > >> index on of its field (Internal_ID),How can I do it? > >> > >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID > >> NUMERIC(9),COURSE_CODE VARCHAR(3), > >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), > >> INTERNAL_ID NUMERIC(9)) > >> > >> Thanks > >> > >> > > > > > > Thanks
Show quote "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in message news:uwkDwU1VFHA.2128@TK2MSFTNGP14.phx.gbl... > Just make both part of the unique constraint or primary key: > > DECLARE @TBEnrollmentsForActiveClients TABLE > ( > ENROLLMENT_ID NUMERIC(9), > COURSE_CODE VARCHAR(3), > COURSE_YEAR NUMERIC(5), > COURSE_SESSION NUMERIC(5), > INTERNAL_ID NUMERIC(9), > UNIQUE(ENROLLMENT_ID, INTERNAL_ID) > ) > > or > > DECLARE @TBEnrollmentsForActiveClients TABLE > ( > ENROLLMENT_ID NUMERIC(9), > COURSE_CODE VARCHAR(3), > COURSE_YEAR NUMERIC(5), > COURSE_SESSION NUMERIC(5), > INTERNAL_ID NUMERIC(9), > PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID) > ) > > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Ray5531" <Ray***@microsft.com> wrote in message > news:#BGDjd0VFHA.2700@TK2MSFTNGP12.phx.gbl... >> Actually ENROLLMENT_ID is unique so I can 100% garanty that combination > of >> ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code >> below? >> >> Thanks >> "Itzik Ben-Gan" <it***@REMOVETHIS.solidqualitylearning.com> wrote in > message >> news:uCNjDU0VFHA.2492@TK2MSFTNGP10.phx.gbl... >> > You cannot explicitly create indexes on a table variable, rather only >> > those >> > created by unique or primary key constraints. If internal_id is unique, >> > create a unique constraint on it: >> > >> > DECLARE @TBEnrollmentsForActiveClients TABLE >> > ( >> > ENROLLMENT_ID NUMERIC(9), >> > COURSE_CODE VARCHAR(3), >> > COURSE_YEAR NUMERIC(5), >> > COURSE_SESSION NUMERIC(5), >> > INTERNAL_ID NUMERIC(9) UNIQUE >> > ) >> > >> > If it isn't, make it the first column of a unique constraint, and > include >> > additional columns that would make it unique. >> > >> > -- >> > BG, SQL Server MVP >> > www.SolidQualityLearning.com >> > >> > >> > "Ray5531" <Ray***@microsft.com> wrote in message >> > news:etd1LM0VFHA.2124@TK2MSFTNGP14.phx.gbl... >> >> I create a variable table using this statement in my UDF ,I'm like to > put >> > an >> >> index on of its field (Internal_ID),How can I do it? >> >> >> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >> >> INTERNAL_ID NUMERIC(9)) >> >> >> >> Thanks >> >> >> >> >> > >> > >> >> > >  |
|||||||||||||||||||||||
Other interesting topics