creating unique index and clustered index in sql server management studio

Hello,
I have run across a very strange situation.
If i use the management studio to change an index that is clustered is does
not actually change the index. It shows up in the gui as changed, but the
data is not changed when returning the data.
Also when you use the createto> when right clicking on the table, none of
the indexes or index constraints are recreated.
In order to make the changes work you have to manually create the
constraints:
ie
ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
IX_key_messages_initiated_from UNIQUE CLUSTERED
(
key_messages_initiated_from
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_initiated_from ON dbo._messages_initiated_from
(
initiated_from
) ON [PRIMARY]
Then the changes work and the code is genereted in the scripts.
This used to work in enterprise manager in sql 2000.
Any ideas why this is not functioning right?
Thanks
gary
See in-line:
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have run across a very strange situation.
> If i use the management studio to change an index that is clustered is
> does not actually change the index. It shows up in the gui as changed,
> but the data is not changed when returning the data.
The order in which data is returned is never guarenteed unless you use an
ORDER BY clause. This has always been the case and is nothing new. Without
an ORDER BY clause SQL Server is free to return the rows in what ever order
it seems most appropriate. Often times when you have a clustered index on a
table and issue a SELECT without a WHERE clause it will return the data in
mostly clustered index order. But this has never been guarenteed and should
not rely on that behaviour.

> Also when you use the createto> when right clicking on the table, none of
> the indexes or index constraints are recreated.
> In order to make the changes work you have to manually create the
> constraints:
If you want all the Indexes, constraints etc. you should use the Generate
Scripts wizard found by right clicking on the DB under Tasks. There you can
pick and choose all objects or none.

> ie
> ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
> IX_key_messages_initiated_from UNIQUE CLUSTERED
> (
> key_messages_initiated_from
> ) ON [PRIMARY]
> CREATE NONCLUSTERED INDEX IX_initiated_from ON
> dbo._messages_initiated_from
> (
> initiated_from
> ) ON [PRIMARY]
> Then the changes work and the code is genereted in the scripts.
> This used to work in enterprise manager in sql 2000.
> Any ideas why this is not functioning right?
>
> Thanks
> gary
>
|||This response does not completely answer the question. First off, if the
index is clustered, the data is returned in the order of the records, which
is the order of the cluster. That is the definition.
Second of all, if you right click on the table, and select createto> to
create the script, absolutely none of the indexes that you created manually
other than the primary index appear in the script.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||I mean, when you right click on a table and select "script table to -->
create to--> the code to create the other indexes is not created.
if you were to create the indexes as constraints as the previous version did
in the enterprise manager then the script to works.
Also, on clustered indexes created with a constraint the unique setting is
automatically yes and can't be changed (it is disabled). In the new sql
console you can create a clustered index and then mark it as non-unique,
which just does not make sense as the clustered index is what all the other
indexes point to.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Ok, i guess i can assume the indexes are being created differently. But it
still does not make sense that when the tables are scripted that the script
to create the new indexes are not showing up.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||No, that's not the definition of a clustered index. The rows a STORED in
the order of the clustered index. They're returned in whatever order SQL
feels like if you don't specify an ORDER BY.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gary" <garyseven7@.nospam.nospam> wrote in message
news:eyTC0jYDGHA.740@.TK2MSFTNGP12.phx.gbl...
> This response does not completely answer the question. First off, if the
> index is clustered, the data is returned in the order of the records,
> which is the order of the cluster. That is the definition.
> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
|||gary (garyseven7@.nospam.nospam) writes:
> This response does not completely answer the question. First off, if
> the index is clustered, the data is returned in the order of the
> records, which is the order of the cluster. That is the definition.
NO!
The fact that index is clustered means that the data is at the leaf
pages of the index. Thus, data is physically sorted according to the
clustered index.
But SQL does not bother about physical storage. If you say:
SELECT ... FROM tbl WHERE ...
without an ORDER BY clause, the DB engine is free to return the rows
in any order. For instance, if the WHERE clause includes a column on
which there is an non-clustered index, it is likely that that the
output will follow that index. If you say "SELECT * FROM bigtable",
SQL Server may open parallel streams, and run from different parts of
the table, at the same time.
In a relational datbase, there is only one way get data sorted in a
certain order, and that is ORDER BY.

> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
Yes. Andrew addressed this in his post:
[vbcol=seagreen]

> Also, on clustered indexes created with a constraint the unique setting
> is automatically yes and can't be changed (it is disabled). In the new
> sql console you can create a clustered index and then mark it as
> non-unique, which just does not make sense as the clustered index is
> what all the other indexes point to.
What happens when you define a clustered index as non-unique, is that
SQL Server adds a four-byte "uniquifier". It has been this way since
SQL 7. (And all SQL Server versions have permitted non-uniuqe clustered
indexes.)

> Ok, i guess i can assume the indexes are being created differently. But
> it still does not make sense that when the tables are scripted that the
> script to create the new indexes are not showing up.
On http://lab.msdn.microsoft.com/produc...k/default.aspx you
can enter suggestions for improvments in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks for all your help.
It is kind of odd that the script table does not generate the indexes and
the script database that is in the wizard is capable of generating the
sciprts for the index (it doesn't generate them by default, you have to
select the script indexes to be true).
SQL 2000 GUI created the indexes by creating a constraint while the new SQL
2005 GUI creates them using the create index statement. If created as a
constraint the alter statement ins generated in the create table, if created
in a create index statement it is not.
This seems somewhat consistantly inconsistant so to speak.
Thanks for helping clarify the issue.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Yes I agree that it is misleading and not a very good design that it leaves
out a bunch of objects. Its really only useful for a quick look at the DDL
of the table itself. Anything more should be done thru the Generate Scripts
task.
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:OaEMUSaDGHA.4052@.TK2MSFTNGP10.phx.gbl...
> Thanks for all your help.
> It is kind of odd that the script table does not generate the indexes and
> the script database that is in the wizard is capable of generating the
> sciprts for the index (it doesn't generate them by default, you have to
> select the script indexes to be true).
> SQL 2000 GUI created the indexes by creating a constraint while the new
> SQL 2005 GUI creates them using the create index statement. If created
> as a constraint the alter statement ins generated in the create table, if
> created in a create index statement it is not.
> This seems somewhat consistantly inconsistant so to speak.
> Thanks for helping clarify the issue.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
sql

Creating random numbers

Guys, I wanted to create random numbers for some strange purpose using SQL
Server 2000. Even if the numbers are going to repeat its fine. [Pls note I
don't want to use newid() ].
For ex: I want to pass two integer values say, 10, 30 ... now i expect
randoms to be generated between these two numbers. How do to it in SQL
Server. Can any one help me in this?
Regards
SqlBeginnerdeclare @.id int
SET @.id=0
-- create a random sting
SELECT @.id = RAND()*30
SELECT CASE WHEN @.id <10 THEN @.id + 10
WHEN @.id >30 THEN @.id - 10
ELSE @.id END AS Val
Regards
Amish Shah|||declare @.a int,@.b int
set @.a = 30
set @.b = 40
select @.a + cast((@.b - @.a) * rand() as int)|||Check out http://www.drdev.net/article07.htm
Extract from that link for your easy reference:
-- Create the variables for the random number generation
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
-- This will create a random number between 1 and 999
SET @.Lower = 1 -- The lowest random number
SET @.Upper = 999 -- The highest random number
SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
To adjust the upper and lower limit of the random number simply change the
values of @.Lower and @.Upper
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> declare @.a int,@.b int
> set @.a = 30
> set @.b = 40
> select @.a + cast((@.b - @.a) * rand() as int)|||Doesn't that formula exclude 999 from the random number pool so that the
highest random number generated will be 998?
Ta,
M. E. Houston
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:9CFD8D24-E6A5-42CD-BDFB-363081B22FBE@.microsoft.com...
> Check out http://www.drdev.net/article07.htm
> Extract from that link for your easy reference:
> -- Create the variables for the random number generation
> DECLARE @.Random int;
> DECLARE @.Upper int;
> DECLARE @.Lower int
> -- This will create a random number between 1 and 999
> SET @.Lower = 1 -- The lowest random number
> SET @.Upper = 999 -- The highest random number
> SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
> To adjust the upper and lower limit of the random number simply change the
> values of @.Lower and @.Upper
> Hope this helps!
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Omnibuzz" wrote:
>|||The first problem is that there are two kinds of random selection from
a set:
1) With replacement = you can get multiple copies of the same value.
This is shooting dice.
This one is easy if you have a random function in your SQL product.
Most of the pseudo-random generators return a floating point fraction
value between 0.00 and 0.9999... at whatever precision your SQL engine
has. The choice of a seed to start the generator can be the system
clock or some other constantly changing value.
SELECT S1.key_col
FROM SomeTable AS S1, SomeTable AS S2
WHERE S1.key_col <= S2.key_col
GROUP BY S1.key_col
HAVING COUNT(S2.key_col)
= (SELECT COUNT(*)
FROM SomeTable AS S3) * RANDOM(seed) + 1.0;
Or you can add a column for this.
CREATE TABLE RandNbrs2
(seq_nbr INTEGER PRIMARY KEY,
randomizer FLOAT -- warning !! not standard SQL
DEFAULT (
(CASE (CAST(RAND() + 0.5 AS INTEGER) * -1)
WHEN 0.0 THEN 1.0 ELSE -1.0 END)
* (CAST(RAND() * 100000 AS INTEGER) % 10000)
* RAND())
NOT NULL);
INSERT INTO RandNbrs2 VALUES (1, DEFAULT);
INSERT INTO RandNbrs2 VALUES (2, DEFAULT);
INSERT INTO RandNbrs2 VALUES (3, DEFAULT);
INSERT INTO RandNbrs2 VALUES (4, DEFAULT);
INSERT INTO RandNbrs2 VALUES (5, DEFAULT);
INSERT INTO RandNbrs2 VALUES (6, DEFAULT);
INSERT INTO RandNbrs2 VALUES (7, DEFAULT);
INSERT INTO RandNbrs2 VALUES (8, DEFAULT);
INSERT INTO RandNbrs2 VALUES (9, DEFAULT);
INSERT INTO RandNbrs2 VALUES (10, DEFAULT);
SELECT * FROM RandNbrs2;
2) Without replacement = you can each value only once. This is dealing
playing cards.
This is trickier. I would start with a table that has the keys and a
sequentially numbered column in it:
CREATE TABLE CardDeck
(keycol <datatype> NOT NULL PRIMARY KEY,
seq INTEGER NOT NULL);
INSERT INTO CardDeck (keycol, seq)
SELECT S1.keycol, COUNT(S2.keycol)
FROM SomeTable AS S1, Sometable AS S2
WHERE S1.key_col <= S2.key_col
GROUP BY S1.key_col;
Now shuffle the deck by determing a random swap pair for all the rows:
BEGIN
DECLARE i INTEGER, j INTEGER;
SET i = (SELECT COUNT(*) FROM CardDeck);
WHILE i < 0
LOOP
SET j = (SELECT COUNT(*) FROM CardDeck) * RANDOM(seed) + 1.0;
UPDATE CardDeck
SET seq = CASE WHEN seq = i THEN j
WHEN seq = j THEN i
ELSE seq END;
WHERE seq IN (i, j);
SET i = i - 1;
LOOP END;
END;
You don't really need j, but it makes the code easier to read.
Biography:
Marsaglia, G and Zaman, A. 1990. Toward a Univesal Random Number
Generator. Statistics & Probability Letters 8 (1990) 35-39.
Marsaglia, G, B. Narasimhan, and A. Zaman. 1990. A Random Number
Generator for PC's. Computer Physics Communications 60 (1990) 345-349.
Leva, Joseph L. 1992. A Fast Normal Random Number Generator. ACM
Transactions on Mathematical Software. Dec 01 1992 v 18 n 4. p 449
Leva, Joseph L. 1992. Algorithm 712: A Normal Random Number Generator.
ACM Transactions on Mathematical Software. Dec 01 1992 v 18 n 4. p 454
Bays, Carter and W.E. Sharp. 1992. Improved Random Numbers for Your
Personal
Computer or Workstation. Geobyte. Apr 01 1992 v7 n2. p 25
Hulquist, Paul F. 1991. A Good Random Number Generator for
Microcomputers.Simulation. Oct 01 1991 v57 n 4. p 258
Komo, John J. 1991. Decimal Pseudo-random Number Generator. Simulation.
Oct 01 1991 v57 n4. p 228
Chambers, W.G. and Z.D. Dai. 1991. Simple but Effective Modification to
a Multiplicative Congruential Random-number Generator. IEEE
Proceedings.Computers and Digital Technology. May 01 1991 v 138 n3. p
121
Maier, W.L. 1991.. A Fast Pseudo Random Number Generator. Dr. Dobb's
Journal.May 01 1991 v17 n 5. p 152
Sezgin, Fatin. 1990. On a Fast and Portable Uniform Quasi-random Number
Generator. Simulation Digest. Wint 1990 v 21 n 2. p 30
Macomber, James H. and Charles S. White. 1990. An n-Dimensional Uniform
Random Number Generator Suitible for IBM-Compatible Microcomputers.
Interfaces. May 01 1990 v 20 n 3. p 49
Carta, David G. 1990. Two Fast Implementations of the "Minimal
Standard" Random Number Generator. Communications of the ACM. Jan 01
1990 v 33 n 1. p 87
Elkins, T.A. 1989. A Highly Random-number Generator. Computer
Language. Dec 01 1989 v 6 n 12 p 59
Kao, Chiang. A Random Number Generator for Microcomputers. OR: The
Journal of the Operational Research Society. Jul 01 1989 v 40 n 7. p
687
Chassing, P. 1989. An Optimal Random Number Generator Zp. Statistics &
Probability Letters. Feb 01 1989 v 7 n 4. p 307
Also, you can contact Kenneth G. Hamilton 72727,177 who has done some
work with RNG's. He has implemented one (at least one) of the best.
"A Digital Dissolve for Bit-Mapped Graphics Screens" by Mike Morton in
Dr.Dobb's Journal, November 1986, page 48.
CMOS Cookbook by Don Lancaster; Sams 1977, page 318.
Art of Computer Programming, Volume 2: Seminumeral Algorithms, 2nd
edition by Donald Knuth; Addison-Wesley 1981; page 29.
Numerical Recipes in Pascal: The Art of Scientific Computing by Press
et al.; Cambridge 1989; page 233.|||dear celko, if you had read the question, you would see that teh
gentleman doesn't mind duplicates, and he wants his results between two
parameters.
your solutions don't solve any of that.
Regarding your second "solution", wouldn't it be if you could do
this set based?
oh. Wait, You can.
make seq real instead of integer.
for the second answer, UPDATE CardDeck
SET seq = RANDOM(seq)
Select * from carddeck order by seq
It is somewhat interesting that there has been no advancement in random
numbers since the year 1992.
regards,
doug