Xpoint
   [напомнить пароль]

Как создать индекс в таблице-переменной

Метки: [без меток]
2007-02-02 08:21:38 [обр] Top manager(0/2)[досье]

К примеру есть таблица-переменная:

DECLARE @plants TABLE (
   id INT
   ,name VARCHAR(50)
   ,currency VARCHAR(3)
   ,invoicesCount SMALLINT
   ,totalAmout MONEY
   PRIMARY KEY (id ASC)
)

Код рабочий. Кк добавить в эту переменную (таблицу) индекс, например на поле name.
Слудующий код выдаёт ошибку

CREATE INDEX [@plants_name]
   ON @plants (name)
GO
спустя 2 часа 42 минуты [обр] Алексей Рюмин aka Dwarf(120/864)[досье]

"You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does with them is another story. <G> One thing to note is that you cannot explicitly name your constraints, e.g.:"

DECLARE @myTable TABLE 
( 
    CPK1 int, 
    CPK2 int, 
    CONSTRAINT myPK PRIMARY KEY (CPK1, CPK2) 
) 
 
-- yields: 
Server: Msg 156, Level 15, State 1, Line 6 
Incorrect syntax near the keyword 'CONSTRAINT'. 
 
-- yet the following works: 
DECLARE @myTable TABLE 
( 
    CPK1 int, 
    CPK2 int, 
    PRIMARY KEY (CPK1, CPK2) 
)

(c) http://databases.aspfaq.com/da......table-or-a-table-variable.html

спустя 4 минуты [обр] Top manager(0/2)[досье]

Алексей Рюмин aka Dwarf[досье] сайт http://databases.aspfaq.com не работает

"You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does with them is another story. <G> One thing to note is that you cannot explicitly name your constraints, e.g.:" - ок

спустя 3 минуты [обр] Алексей Рюмин aka Dwarf(120/864)[досье]
Top manager[досье] У меня работает. Если что, его IP 67.15.124.165
Powered by POEM™ Engine Copyright © 2002-2005