DefaultValue

Hi all,
I have a table like
Tablename: Test
tst_id uniqueidentifier Not null PK
= RowGuid
tst_ts2_id uniqueidentifier Not null
Default '?
tst_code Varchar 50 Not null
And Another table
Tablename: Test2
ts2_id uniqueidentifier Not null PK
ts2_name Varchar 50 Not null
ts2_default Boolean False
there is defined a FK tst_ts2_id and ts2_id
I would to insert into table Test a row with the folloing insert
INSERT INTO test
(tst_code)
VALUES ('123456')
Now I would, that there is a trigger, or something else, that inserts me an
tst_ts2_id the ID from Table Test2, where ts2_default = True.
How to du. I tryed with trigger, but it seams, that the trigger fires to
late, if I delete the FK, then it will be inserted.
I wrote a function that returns the Id to insert, but is it possible, that
like default-value can be insertted the result of a skalar function ?
Thank you all
Klaus AstnerI'm not certain if this is what you are trying to do and as you did not post
DDL or the statements or triggers that you tried I made some guesses, but
the following worked fine for me:
create table Test2 (
ts2_id uniqueidentifier Not null primary key default(newid()),
ts2_name Varchar(50) Not null,
ts2_default bit default(0)
)
go
create function dbo.def_ts2id() returns uniqueidentifier
as
begin
declare @.guid uniqueidentifier
select @.guid = ts2_id from test2 where ts2_default = 1
return @.guid
end
go
create table Test (
tst_id uniqueidentifier Not null primary key default(newid()),
tst_ts2_id uniqueidentifier Not null foreign key references test2(ts2_id)
default(dbo.def_ts2id()),
tst_code Varchar(50) Not null
)
go
insert into test2(ts2_name,ts2_default) values('default',1)
go
INSERT INTO test(tst_code) VALUES('123456')
go
select * from test
select * from test2
go
Result:
tst_id tst_ts2_id
tst_code
-- -- --
---
18CBDA2A-6B29-43C9-8A42-B86B9102E6D4 70DC14A0-F744-4006-8CE0-A9C8695D8E83
123456
ts2_id ts2_name
ts2_default
-- ---
-- --
70DC14A0-F744-4006-8CE0-A9C8695D8E83 default
1
Craig
--
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.
"Astner Klaus" <k.astnerremove_this@.virgilio.it> wrote in message
news:eFHHSRPXDHA.2648@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I have a table like
> Tablename: Test
> tst_id uniqueidentifier Not null PK
> = RowGuid
> tst_ts2_id uniqueidentifier Not null
> Default '?
> tst_code Varchar 50 Not null
>
> And Another table
> Tablename: Test2
> ts2_id uniqueidentifier Not null PK
> ts2_name Varchar 50 Not null
> ts2_default Boolean False
>
> there is defined a FK tst_ts2_id and ts2_id
>
> I would to insert into table Test a row with the folloing insert
>
> INSERT INTO test
> (tst_code)
> VALUES ('123456')
> Now I would, that there is a trigger, or something else, that inserts me
an
> tst_ts2_id the ID from Table Test2, where ts2_default = True.
>
> How to du. I tryed with trigger, but it seams, that the trigger fires to
> late, if I delete the FK, then it will be inserted.
> I wrote a function that returns the Id to insert, but is it possible, that
> like default-value can be insertted the result of a skalar function ?
>
> Thank you all
>
> Klaus Astner
>
>
>

Default sort order when using SELECT with no ORDER BY clause

For SQL Server 2000 and 7.0:

What is the default sort order of a dataset when you perform a SELECT * FROM [tablename] without using the ORDER BY clause?

I've been told it is ordered by the clustered index (my testing does not bear this out) and I've been told that it is completely unpredictable (closer to what I'm seeing). I believe the latter to be true but cannot find this outlined anywhere in Microsoft's BOL, TechNet, KnowledgeBase, MSDN, etc.

Any pointers on where to find the definitive answer is appreciated.

TIA. RickIf there is clustered index - sorting by clustered index, else sorting by inserting records.

BOL says:

SQL Server 7.0 tables use one of two methods to organize their data pages:

Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.

Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

SQL Server also supports up to 249 nonclustered indexes on each table. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.|||Hi ,

If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted.

If you mention ORDER BY Column name , then the dafault sort order will be ASCending if SQL Server is installed by default collation.

Ex. Select * from authors order by authorname

FYI:
--
Use the locale identified by Setup, and then choose the desired binary, case, or other options.

For the release of SQL Server 2000, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set.

To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive.

For more information , see BOL - Collation|||"If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted"

if you're lucky

records can get physically stored not in insertion sequence (e.g. a row happens to be too big for the current physical database page, but the next one inserted after that isn't)

i believe records are returned in physical sequence, all things being equal

but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first

i too have heard that the sort order is unpredictable, but i too have been unable to find this on microsoft's site

a "definitive" answer will be found, of course, only on microsoft's site

rudy
http://rudy.ca/|||Hi,

I dont agree with "R937". Sorry to say that.

To his Question" but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first"

This is absolutely wrong. Thats not the concept of caching or Query Optimization.

For Example, if I excute the query as below,

Use pubs
go
/* This will return rows where job_id > 5 */
Select * from jobs where job_id > 5
go

Select * from jobs
go
AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.

Thats not true.

Concept:
----
As per SQL Server, both the query are entirely different.

First it will just look in cache whether the same SQL Query is available in cache not the data. If the SQL Query is not avaiable , then it will be excuted for optimization and return the rows from disk.

Hope you agree with me.

Any constructive criticism will be appreciated.

FYI: Concepts on Query Optimization will explain well.

Have Fun :)
Varad