I didn't drop the table

Hi, Andrea,
Thank you for your reply.
I didn't drop the table,and the records in the table seldom change.
Does it has anything to do with the weekly backup schedule? I remember I
once did something wrong and I had to restore the database.
Thanks for any relpy.
Long
hi,
Long wrote:
> Hi, Andrea,
> Thank you for your reply.
> I didn't drop the table,and the records in the table seldom change.
> Does it has anything to do with the weekly backup schedule? I
> remember I once did something wrong and I had to restore the database.
> Thanks for any relpy.
> Long
if you modified your database and data, restoring an older (unmodified)
version can produce such problems
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%
The server is running SQL Server 2000 sp4
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%
|||Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%
|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%

I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%The server is running SQL Server 2000 sp4
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a fe
w
> foreign keys against this primary key, so it's really hard to drop the ind
ex
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a fe
w
> foreign keys against this primary key, so it's really hard to drop the ind
ex
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%

I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:
> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
--
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||The server is running SQL Server 2000 sp4
"John" wrote:
> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%

I am scripting drop PRIMARY KEY script, but got DROP TABLE script

I am scripting the drop PRIMARY KEY script for all tables. But the script I got was DROP TABLE. Script options are as following:

so.DriUniqueKeys = false;

so.DriDefaults = false;

so.DriChecks = false;

so.Indexes = false;

so.DriForeignKeys = false;

so.PrimaryObject = false;

so.DriPrimaryKey = true;

so.IncludeIfNotExists = true;

so.ScriptDrops = true;

The create PRIMARY KEY script is right if I change so.ScriptDrops to false.

Am i doing something wrong or is it a bug? How to script out drop PK script?

hard to tell without the rest of your script, but what about that one here:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table t in s.Databases["Adventureworks"].Tables)

{

foreach (Index i in t.Indexes)

{

if (i.IndexKeyType == IndexKeyType.DriPrimaryKey)

{

i.Drop();

break;

}

}

}

s.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

Console.ReadLine();

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens, It works great.

Based on my test, the DriPrimaryKey=true only works for create script for tables. DriPrimaryObject=false only works for create script for tables too. For drop script,smo always generates drop table script. That's why I got drop table script when i tried to generate drop PK script (DriPrimaryKey=true and

DriPrimaryObject=false ).

The BOL lacks details about the scripting options.

I am having QUERY/ DATASET problem with SQL Reports.

Hi every1,

I really need your help.

I have a web form which is having Drop Down box in which I have 2 selection 'P' and 'C'. Depending upon the selection I have to call the Dataset( If I can make it in code behind) or I can do it in one Complex query if you guys will help me.

Query should be like this.

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a

where

acolumn2 = "USERINPUT"

but now the problem is in FROM and Where Clause.

when selection is 'P' then I have to include ptable in FROM Clause and in where clause I have to include two condition like (p.column1 = a.column1 and p.column2="USERINPUT1" or

if selection is 'C' then I have to include ctable in FROM clause and in where clause I have to include two different condition like (c.column1 = a.column1 and c.column2="USERINPUT")

So at once the Query will be any of one like below:

(1) some times this:

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a, ptable p

where

a.column2 = "USERINPUT" and

p.column1 = a.column1 and p.column2="USERINPUT1"

-

(2) some times this:

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a, ctable c

where

a.column2 = "USERINPUT" and

c.column1 = a.column1 and c.column2="USERINPUT"

Please let me know if you have any kind of confusion so I can clear this but if you got everything from my description please do help me to solve this. I am really struggling for this.

One way I was thinking to generate 3 dataset one(query or dataset) for 'P' , one for 'C' and one for else so I can get the input (selection) from the form and then decide in codebehind which dataset I have to make call so no need to write complex query. But this is my thought and I dont know how to make a call and everything either so please help me in this as well, if you like this solution .

Or try to solve the above query please.

Thanks

Roy

The best way to do this is to construct the query dynamically as a string.

And the best way to do THAT -- IMHO -- is to have your query say only:

Code Snippet

=Code.GetSQL()

... now you can write a simple little VB function that evaluates your parameters and builds up the string, returning that string. You will find it a lot more comfortable to do it this way than doing it in the query window. If you have never written a custom function before, don't worry, you don't need to build a DLL for something like this -- just use the Code tab in the Report Properties dialog. This is the kind of code that IMHO really *deserves* to be embedded in a report, since it is specific to that report and that report's parameters.

If you have the ability to write stored procedures or table-valued functions in the database, that is another option. You can pass your parameters to the procedure or function, and have that code evaluate the parameters and provide the result. It really depends on two things: what your permissions are (for writing to the database) and what your relative comfort level is (for writing VB or writing T-SQL).

HTH

>L<