Does SQL uses index in the following select statement

I have the following table structure:
PK_Column1
PK_Column2
IndexedColumn
Column_ABC
Column_XYZ
Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
values in the following select statement:
SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
PK_Column1=@.MyParam
When I run this statement it works too slow and I see alot of reads in SQL
Server Profiler.
Is there any way to improve the performance in this case?
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Hi
If Indexed column is clustered, it probably would as it can do a clustered
index range scan.
Else, it may not. It all depends on how up to date the statistics are, the
data types of the columns, how selective the indexes are and the number of
rows.
Show the query plan and we can tell.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via webservertalk.com" <no@.spam.pls> wrote in message
news:58e799d08a0ce@.uwe...
>I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||Thank you for your answer.
Here are more details:
PK_Column1 smallint
PK_Column2 int
IndexedColumn DateTime (NON-CLUSTERED and not unique)
Column_ABC varchar
Column_XYZ varbinary(BLOB)
The table has about 4M rows.

>Show the query plan and we can tell.
How can I get it? I am using the Standard edition of SQL Server 2005.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||If there is a clustered index on PK_Column1, then it might do an index
scan on the index of IndexedColumn. However, the query would benefit
more from an index on (PK_Column1, IndexedColumn).
I am not running SQL2K5, but I guess that SET SHOWPLAN_TEXT ON will
probably still work...
HTH,
Gert-Jaqn
"Alex via webservertalk.com" wrote:
> I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1

Does SQL Server Support the "MINUS" Keyword?

Maybe there's another better way to do this... Anyway, here's what I'm trying to do:

I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.

Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).

I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars

So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).

I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.

My questions:

Is the MINUS capability supported by SQL Server? I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server... If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.

Here's a reference to the website where I initially found out about MINUS:
http://www.1keydata.com/sql/sql-minus.html

Thanks in advance for your time/comments.

I think you want EXCEPT

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

|||You can also use a NOT EXISTS to find records that don't exist in the other table|||

Thanks Simon for your comments and pointing me in the right direction.

This is what worked for me:

SELECT CarTypeId FROM CarType
WHERE (CarTypeId NOT IN
(SELECT DISTINCT CarTypeId FROM Cars)
)

Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...

Ian

|||

SQL Server has never supported MINUS.

in SQL 2005 it now has the EXCEPT clause which does the same thing

|||

Caution: Microsoft talks about this issue at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx?mfr=true. However, it is incomplete in that it does not address a curveball.

That is, the = operator does not address nulls.

The Microsoft example is as follow:

Oracle Microsoft SQL Server

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

This will work to identify students in one table but not in another. However, it will not work like Oracle's MINUS command to spot differences between tables. So, say you want to see this -- between the 2 tables, find where students are missing AND students whose names have changed -- either from A to B or from not null to null or from null to not null.

Try this:

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE

and (C.CNAME = G.CNAME or (C.CNAME is null and G.NAME is null) )

That will mimic the full functionality of Oracle's MINUS command in SQL Server 2000 T-SQL.

|||

Hi Ian,

I havent come across the Minus in SQL server but to do what you want I would try the following

SELECT T.cartypeid

FROM cartype as T

LEFT OUTER JOIN cars C

ON T.cartypeID = C.cartypeID

WHERE c.caretypeID is null

This joins everything in the cartype table but the where clause restricts the result set to those records with a null car.cartypeID

i.e. no matching record

Does SQL Server Support the "MINUS" Keyword?

Maybe there's another better way to do this... Anyway, here's what I'm trying to do:

I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.

Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).

I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars

So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).

I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.

My questions:

Is the MINUS capability supported by SQL Server? I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server... If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.

Here's a reference to the website where I initially found out about MINUS:
http://www.1keydata.com/sql/sql-minus.html

Thanks in advance for your time/comments.

I think you want EXCEPT

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

|||You can also use a NOT EXISTS to find records that don't exist in the other table|||

Thanks Simon for your comments and pointing me in the right direction.

This is what worked for me:

SELECT CarTypeId FROM CarType
WHERE (CarTypeId NOT IN
(SELECT DISTINCT CarTypeId FROM Cars)
)

Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...

Ian

|||

SQL Server has never supported MINUS.

in SQL 2005 it now has the EXCEPT clause which does the same thing

|||

Caution: Microsoft talks about this issue at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx?mfr=true. However, it is incomplete in that it does not address a curveball.

That is, the = operator does not address nulls.

The Microsoft example is as follow:

Oracle Microsoft SQL Server

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

This will work to identify students in one table but not in another. However, it will not work like Oracle's MINUS command to spot differences between tables. So, say you want to see this -- between the 2 tables, find where students are missing AND students whose names have changed -- either from A to B or from not null to null or from null to not null.

Try this:

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE

and (C.CNAME = G.CNAME or (C.CNAME is null and G.NAME is null) )

That will mimic the full functionality of Oracle's MINUS command in SQL Server 2000 T-SQL.

|||

Hi Ian,

I havent come across the Minus in SQL server but to do what you want I would try the following

SELECT T.cartypeid

FROM cartype as T

LEFT OUTER JOIN cars C

ON T.cartypeID = C.cartypeID

WHERE c.caretypeID is null

This joins everything in the cartype table but the where clause restricts the result set to those records with a null car.cartypeID

i.e. no matching record

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hans
No, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans
|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
>
>

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hansNo, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> > Hello,
> >
> > I'm trying to do this:
> > alter table maquinas add marca varchar(50) after modelo
> >
> > and I get the following error:
> > Incorrect syntax near 'after'.
> >
> > What is going on?
> >
> > hans
>
>

Does SQL Server move records between partitions when updating the partition key column?

If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?

ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?

It will move to the other partition.

Thanks,|||Thank you. That's the kind of behavior I was looking for.

Does sql server have something like dual

In oracle you can run queries against some built in virtual table called dual. Like below:

SELECT SEQ.NEXTVAL FROM DUAL

does sql server have anything similar?Do you tried 'Select SEQ.NEXTVAL' That should work!|||SQL Server does not have such virtual table for that exact purpose like oracle has, for example in Oracle you can run:

select sysdate from dual

Same would be in SQL Server:

select getdate()

e.g any virtual table is not needed, statement just consists of SELECT plus then a function or T-SQL specific stuff. With identities (same as sequences in Oracle) there are SCOPE_IDENTITY(), @.@.IDENTITY AND IDENT_CURRENT, for example to query current identity value:

SELECT IDENT_CURRENT('TABLE_NAME')

Exact difference with all three:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

So to reply completely to your question, there are functions etc which do not need any virtual table when they are general (table-independant, querying the date is good example) and then there are stuff which need the table to be specified in the query like previous identity query.sql

Does SQL Server creates some temporary table during query execution?

Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
PushkarYes, it does. If you show the query plan, you'll see icons such as Table
Spool/Eager Spool. That implies that a temporary table is being created by
the optimizer behind the scenes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
Pushkar|||Pushkar
I think SQL Server decides internally to perform some operations in tempdb
database. For sure I know that if your query has ORDER BY ,GROUP BY clauses
and it has to operate in large amount of data , so SQL Server will create a
work tables to perfom that.
I'd suggest to visit at Aaron's web site to get more explanation
www.aspfaq.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to know when does SQL Server use temporary tables for query
> processing?
> Does it use for all queries or for some complex queries? Or doesn't use at
> all?
> Thanks
> Pushkar
>|||Thanks !!!
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urnhSSiNGHA.2916@.tk2msftngp13.phx.gbl...
> Pushkar
> I think SQL Server decides internally to perform some operations in
> tempdb database. For sure I know that if your query has ORDER BY ,GROUP BY
> clauses and it has to operate in large amount of data , so SQL Server will
> create a work tables to perfom that.
> I'd suggest to visit at Aaron's web site to get more explanation
> www.aspfaq.com
>
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
>

does sql server collect stats on full table scans?

Hi all. Anyone know if sql server collects stats on how many full
table scans a table gets and if so how do I get at those stats? Trying
to track down poorly indexed tables / processes and I am guessing that
sql server does have this data secreted away somewhere much like my
db2 and informix databases do.On 20 Oct 2004 06:41:51 -0700, sumGirl wrote:

> Hi all. Anyone know if sql server collects stats on how many full
> table scans a table gets and if so how do I get at those stats? Trying
> to track down poorly indexed tables / processes and I am guessing that
> sql server does have this data secreted away somewhere much like my
> db2 and informix databases do.

There is a performance counter under the "SQL Server:Access Methods"
category called "Full Scans/sec". Unfortunately nothing cumulative as far
as I can see. You could do a trace against your database, set an alert for
Full Scans/sec > 0 and see what was happening at that time.

Does SQL Server check for constraint violation when COMMIT is called?

If there are two different transactions, both of which update the username column to 'xyz' for userid = 234 in 'Users' table. This is a unique value for username. Ater this update each transaction adds a row to 'AppLog' table. The transaction is only committed after second operation.

The 'username' column has a unique constraint on it.

If transaction isolation level is 'read committed', and both transaction execute the first operation when neither of the transactions have been committed, then the transaction that calls COMMIT later will error out or not? If COMMIT does not check constraints then it will NOT error out. As a result we will have a violation of unique constraint happening without any error being thrown by SQL Server.

sun21170:

If COMMIT does not check constraints then it will NOT error out.

The statements that COMMIT causes to execute will be checked for contraint violations. If SQL Server allowed constraint violations, it wouldn't be around very long.

sql

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time colum

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time c

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does SQL have a function that return "null" for records which dont exist in a FK realation

Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.

Thank you very much.

when doing select with 'join's over two tables it would return null.

For example, you have customers and customerdetails table

if you do

select *

from customers

left join customerdetails on customers.customerId = customerdetails.customerId

if there is no customer details for a customer the fields corresponding to the customer details table would be null.

Check the different types of joins 'left join', 'inner join', 'outer join', 'cross join'...i think that's all :)

Cheers,

Yani

|||

Thank you very much indeedYani Dzhurov.

Does SQL 2005 encryption on x64 work?

Are there any known issues with EncryptByKey/DecryptByKey on x64 machines?

I have a test script where I create a sample table and encrypt a column and later decrypt it. It works fine on my x86 box. When I run the *exact* same test script on an x64 server I'm getting unprintable characters back on the DecryptByKey. I cannont find anything I'm doing different between the two.

Has anyone seen anything like this before?

We have not seen this problems yet but I will take a look and attempt to repro this. Would it be possible to get a copy of the script you are using (obviously, please remove all private/sensitive info first)?

Thanks,

Sung

|||

Nevermind! I figured it out.

I was doing an INSERT INTO SELECT FROM... and the column I was selecting from, TaxID, had to be converted to Unicode first. So it ended up looking like this:

EncryptByKey(@.KeyGuid, CAST([TaxID] As NVARCHAR(9))) As 'TaxID'

On my x86 machine I had hard-coded the TaxID as N'123456789' and didn't realize at the time that the N was converting it to Unicode for me.

|||

Ah, ok, I was wondering if it had something to do with text encoding as well. Glad to hear it works!

Sung

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advance
WITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR
|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>
|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> that
> table
>
|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
(however,[vbcol=seagreen]
> the
blocked[vbcol=seagreen]

>

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advanceWITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> that
> table
>|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
(however,[vbcol=seagreen]
> the
blocked[vbcol=seagreen]
[vbcol=seagreen]
>

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advanceWITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> > I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> > another process attempting to acquire an IX lock on that table. I saw
> that
> > the showcontig had an "S" lock on the table.
> >
> > Is there any way to influence SQL Server into taking an IS lock on the
> table
> > and S locks on the pages?
> >
> > Thanks in advance
> >
> >
>|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> > Not currently. Even using WITH FAST requires a Shared Table lock
(however,
> > except on the largest tables any blocking should be fairly transient as
> the
> > results are returned so quickly)
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> > news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> > > I noticed that while running a DBCC SHOWCONTIG - this processed
blocked
> > > another process attempting to acquire an IX lock on that table. I saw
> > that
> > > the showcontig had an "S" lock on the table.
> > >
> > > Is there any way to influence SQL Server into taking an IS lock on the
> > table
> > > and S locks on the pages?
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>sql

Does 'SELECT INTO' not work in SQL Mobile?

I'm trying to programmatically create a new table that is a copy of another. The SQL statement I'm making is:

"SELECT * INTO sensor_stream_temp FROM sensor_stream"
and various combinations thereof (such as specifying the columns, etc)

The error I keep getting is:
There was an error parsing the query. [Token line number 1, Token line offset 10, , Token in error = INTO ]

Is it not possible to copy a table using this statement? *sigh*
Thanks in advance for any help.
-Dana
OK, so it looks like "INTO" is not allowed in SQL Mobile; how do I make a copy of a table?

|||

Try this:

INSERT INTO sensor_stream_temp (SELECT * FROM sensor_stream);

Note: Both sensor_stream_temp, sensor_stream MUST be of similar.

Does RS allow querying between tables?

I have a very complex query that needs to return monthly and year-to-date
data. I'm using one table (table A) to correctly return the monthly data. I
would now like to create another table and use a result from table A to
correctly filter and display the year-to-date data along side the monthly
data.
Does RS allow querying between tables?
Example of desired results
Account July 2004 Year-to-Date
Ace Hardware $3000.00 $8452.21
--
DCountYou can't really use table A to display results into a different table. But
what you may want to try is to use the same dataset used in table A to create
a new table B and then on table B goto the properties of the table and then
goto the filters entry there and apply the filters you want to the table.
That should get you the results you're looking for.
> I have a very complex query that needs to return monthly and year-to-date
> data. I'm using one table (table A) to correctly return the monthly data. I
> would now like to create another table and use a result from table A to
> correctly filter and display the year-to-date data along side the monthly
> data.
> Does RS allow querying between tables?
> Example of desired results
> Account July 2004 Year-to-Date
> Ace Hardware $3000.00 $8452.21
>
> --
> DCount

does not violate 2nf

Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?

I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?

First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:

Create Table T(p int primary key nonclustered, x int, y int);

Create Clustered Index T_i on T(x);

Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.

Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.

|||

2nf just states that all data in a table must rely on the entire key.

that doesnt mean the key cant be composite.

if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)