Extract number from a string

Hi All,
I have a table in which has a Notes field.
Each of these notes field has a phone number - eg. "Please provide
number 1234 to user XYZ."
I need to extract this number from each of the Notes field.
Can anybody tell me how to extract a number from a string?...
TIA!!!
Hello, snigs
If you want to extract the first number from a string and the number
does not contain any punctuation in it, you can try something like
this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable
If you want to extract all the numbers from a string, including any
punctuation found inside the number, you can try something like this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable
Razvan
PS. With this occasion, I would like to submit my two entries for the
"most unreadable query of the month" contest ;)
|||On 6 Dec 2005 12:07:18 -0800, Razvan Socol wrote:

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
>PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
>-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable

>PS. With this occasion, I would like to submit my two entries for the
>"most unreadable query of the month" contest ;)
Hi Razvann,
Month, year, century - you win them all! ;-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

extract non-unique records from a table

Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:

> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>

extract just the date from a datetime field using T-SQL

I am using a calendar control to pass a date to a stored procedure. The field in the table is a datetime field. Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?

WHERE (datepart(mm,sampletimestamp) = month(@.selcteddate) and
datepart(dd,sampletimestamp) = day(@.selcteddate) and
datepart(yyyy,sampletimestamp) = year(@.selcteddate)
)

This works, but I thought there must be an easier way.

There are many ways. Easiest is to do below:

convert(varchar, sampletimestamp, 112) = @.selcteddate

|||

Something like this:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

-- --
2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

And best to use a form like this for your where:

WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @.selcteddate))
and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @.selcteddate))

So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

Extract data in "Insert Into..." statement format

Is there a way in SQL Server 2000 to extract data from a table, such that
the result is a text file in the format of "Insert Into..." statements, i.e.
if the table has 5 rows, the result would be 5 lines of :

insert into Table ([field1], [field2], ... VALUES a,b,c)
insert into Table ([field1], [field2], ... VALUES d, e, f)
insert into Table ([field1], [field2], ... VALUES g, h, i)
insert into Table ([field1], [field2], ... VALUES j, k, l)
insert into Table ([field1], [field2], ... VALUES m, n, o)

Thanks in advanceVyas has just what you need:
http://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--|||INSERT INTO TABLE1(FIELD1,FIELD2)
SELECT ABC, XYZ from TABLE2 where bla bla

or

INSERT INTO TABLE1(A,B,C)
SELECT X,Y, 'some static text' FROM TABLE2

the number of columns must tally

Chad Richardson wrote:
> Is there a way in SQL Server 2000 to extract data from a table, such
that
> the result is a text file in the format of "Insert Into..."
statements, i.e.
> if the table has 5 rows, the result would be 5 lines of :
> insert into Table ([field1], [field2], ... VALUES a,b,c)
> insert into Table ([field1], [field2], ... VALUES d, e, f)
> insert into Table ([field1], [field2], ... VALUES g, h, i)
> insert into Table ([field1], [field2], ... VALUES j, k, l)
> insert into Table ([field1], [field2], ... VALUES m, n, o)
> Thanks in advance|||You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com|||You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com|||Thanks all for the responses!

"Chad Richardson" <chad@.NIXSPAM_chadrichardson.com> wrote in message
news:1102ir7m0udi5cc@.corp.supernews.com...
> Is there a way in SQL Server 2000 to extract data from a table, such that
> the result is a text file in the format of "Insert Into..." statements,
> i.e. if the table has 5 rows, the result would be 5 lines of :
> insert into Table ([field1], [field2], ... VALUES a,b,c)
> insert into Table ([field1], [field2], ... VALUES d, e, f)
> insert into Table ([field1], [field2], ... VALUES g, h, i)
> insert into Table ([field1], [field2], ... VALUES j, k, l)
> insert into Table ([field1], [field2], ... VALUES m, n, o)
> Thanks in advance

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito CorleoneAre you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito Corleone
Are you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone
sql

EXtract data from 10 identical oracle database into one

Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.

There is a table which list all the 10 databases.

You could do it all in one data flow task or many it's up to you. Basically, you'll need to create a connection manager for oracle and one for sql server. For the source define the table or query to extract the needed data from orcale and then connect it to the destination. Then just map the columns from source to target.

|||

JIGJAN wrote:

Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.

There is a table which list all the 10 databases.

You can use a For Each loop to drive this process. Put an Execute SQL to retrieve the list of databases, and connect it to a For Each loop set to enumerate a rowset. In the For Each loop map a variable to the database name. Put an expression on the connection manager for the Oracle database, to create the connect string dynamically. Put a data flow (or multiple flows, depending on the number of tables) inside it, configured to copy the tables.

Extra white space in table

I've got a table with a header, group, and detail row. The detail row
is collaspable however when it's collasped there is several lines of
white space between the parent groups. How do I get rid of this white
space?
Thanks in advance for the helpIs it possible that the white space is really the detail row, and it is
not shrinking as expected? (I've had this same problem before...) If
you are supressing just a column or two, but not hiding the entire
detail row (such as setting visibility --> hidden property based upon
conditional expression) then you will get the spacing issue as
described.|||Thanks Matt
That was the problem. I was setting the visibility at the cell level
when i should have been setting it on the over all row. thanks for the
help
Mathiassql

Extra Space....

Hello!

I have written a report that is being displayed in a table. For some reason all the way to the right of the table has a HUGE amount of space......I checked to make sure there was no layers over it and I looked in the properties and saw no padding....ANY IDEAS? I am so frusterated because this defies all logic!!! Has anybody experienced this?

TIA!

That happened to me and I checked the width of the object that contained the table. In my case, the width of the rectangle that contained my table was way bigger than it should have been.|||

Oh Thank you for responding!! Are you talking about the width of the table itself....or the width of the page?

Thanks Again!

|||

Hi,

Check what the parent of the table is. If it says "body", then that is what you would adjust the width of.

|||Oh great! thanks!

Extra column in destination

I have worked with straight-forward simplistic SSIS up to now, so I apologize if this is a simple question. I have a SQL table destination that accepts daily data from an AS400 table. We now have a need to datestamp the incoming data so I added a new column to the destination table where I want today's date. Is there a data flow transformation that I need to use to default the destination column to today's date? Time is not a consideration and not needed, just the date.

Thanks for the information.

Use a derived column transformation and inside add a new column using "getdate()" as the expression.|||Thanks again for the help!|||

guyinkalamazoo3 wrote:

Thanks again for the help!

Yep, no problem. There are a whole host of other dates available for your use as well, if you look in the system variables list. System::ContainerStartTime, etc...

Extra column in destination

I have worked with straight-forward simplistic SSIS up to now, so I apologize if this is a simple question. I have a SQL table destination that accepts daily data from an AS400 table. We now have a need to datestamp the incoming data so I added a new column to the destination table where I want today's date. Is there a data flow transformation that I need to use to default the destination column to today's date? Time is not a consideration and not needed, just the date.

Thanks for the information.

Use a derived column transformation and inside add a new column using "getdate()" as the expression.|||Thanks again for the help!|||

guyinkalamazoo3 wrote:

Thanks again for the help!

Yep, no problem. There are a whole host of other dates available for your use as well, if you look in the system variables list. System::ContainerStartTime, etc...sql

Extra "blanks" in the cloumn field

When I enter a data in a table, SQL Server automatically completes the data with blanks up to length of column.

this happens in a web form also in Management Studi?o,

Whan am I doing wrong ?

does Collation have anything with it ?

SQL Server 2005 / Developer Edition

It sounds like you are using the char data type. SQL Server will automatically pad this with spaces as it is a fixed number characters. Use varchar instead.|||

thanks for your help,

now it is not padding.

Exterpise Manager Select Export (ASCII,Excel,Access)?

Hi All. A client needs to send me some sample data. He has insisted he can query the table in Enterprise Manager via a simple select... "Select * from Table1"...

Now I need to get the data in some simple form (ASCII, Excel, Access, etc.) sent to me.

Can someone please provide me the info so I can pass it on for him to query a table from Enterprise Manager and "export it" to a simple file so I can receive it.

ANY THOUGHTS would be helpfull and GREATLY Appreciated!

Thanks.

BillIf this is a one timer I would go for "Tools -> Data Transformation Services -> Export Data".

// Pati|||Hey Pati... It may just be a one timer... but if the data looks good, it may be more frequent. it turns out some other process is taking records from this table, and may be removing them... Part of the reason we are trying to get some snapshots of the data.

I'm trying not to write an app until I know if we need this data.

Is it really simple to use to do the menu picks? Doesn't seem like the end user is very experienced, nor am I on Sql Server.

Thanks for the thoughts.

Bill|||If you think that you will need the same procedure again then you can save the DTS package for further use and then schedule it to run as desired.
...or then for another approach you could automate everything with scripts/scheduled jobs.

But as you're saying that you don't have much experience in SQL Server and that this might just be a one off solution then I would stick to DTS.

// Patisql

External table is not in the expected format

Hi,

I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called

External Table is not in the expected format

System.Data.OleDb.OleDbException: External table is not in the expected format

Any help will be appreciated.

Regards,

Karen

Try to save the Excel file in cvs format before the importing. There can be something in the excel document that mess up the import.

|||

Thanks for ur answer.. but the other 6 files work fine.|||

There might be something in the 7th file that messes up things. Try CSV and see if it helps. If it doesn't then something else is wrong...

|||

Johram,

I tried converting it to a csv file and getting the same error

|||

OK, you gotta make sure all values in a column is in the same format. Upon import, the first row is scanned in order to determine the datatype of each column. If he find a number in the first column, then he assumes that this is a numerical column. If there is character data in the column somewhere then there will be an error. Maybe it's not character data that's your problem, but such a thing as an empty value or a decimal value when an integer value is expected.

If you open up the 7th file and scan through the rows, then you might spot the deviation?

See this article:http://blog.lab49.com/?p=196

Good luck!

|||

Johram,

Thanks a lot for your help.. the reason i was getting that was i had given

flSubAdvisor.PostedFile.SaveAs(location6)

instead of

flGrowth10K.PostedFile.SaveAs(location6)

and

flSubAdvisor.PostedFile.SaveAs(location6) was equal nothing or it was empty..

any way thanks a lot for ur help.

Regards

Karen

external link table

Does SQL server support external link database like DB2 or Oracle through
ODBC?
Any information is appreciated.
See the topic, "Configuring Linked Servers" in SQL Server Books
Online. It describes how to set up and configure a linked server to
access OLE DB data sources.
--Mary
On Sat, 8 Jan 2005 19:42:31 -0500, "souris" <inungh@.videotron.ca>
wrote:

>Does SQL server support external link database like DB2 or Oracle through
>ODBC?
>Any information is appreciated.
>
|||Thanks millions,
Souris
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:s4b3u0hai65rjbo35v78m3a1gc0gc44i8g@.4ax.com...
> See the topic, "Configuring Linked Servers" in SQL Server Books
> Online. It describes how to set up and configure a linked server to
> access OLE DB data sources.
> --Mary
> On Sat, 8 Jan 2005 19:42:31 -0500, "souris" <inungh@.videotron.ca>
> wrote:
>

external link table

Does SQL server support external link database like DB2 or Oracle through
ODBC?
Any information is appreciated.See the topic, "Configuring Linked Servers" in SQL Server Books
Online. It describes how to set up and configure a linked server to
access OLE DB data sources.
--Mary
On Sat, 8 Jan 2005 19:42:31 -0500, "souris" <inungh@.videotron.ca>
wrote:

>Does SQL server support external link database like DB2 or Oracle through
>ODBC?
>Any information is appreciated.
>|||Thanks millions,
Souris
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:s4b3u0hai65rjbo35v78m3a1gc0gc44i8g@.
4ax.com...
> See the topic, "Configuring Linked Servers" in SQL Server Books
> Online. It describes how to set up and configure a linked server to
> access OLE DB data sources.
> --Mary
> On Sat, 8 Jan 2005 19:42:31 -0500, "souris" <inungh@.videotron.ca>
> wrote:
>
>

External image problem when sorting in table

Reporting Services I am using external image png as my header for reports When i am clicking on sort row of the report the header image is dissapeared.Though it is working well in Visual Studio 2005 the problem is occuring after deploying it in reportserver.

What the reason and how can i remove this error.It was working well when i had embeded the image..But i need to have external image only..

Did you install SP1 on the report server (http://www.microsoft.com/sql/sp1.mspx)?

-- Robert

Extents

In Informix you have to really watch your extents. If
you end up with too many extents on a table, you've
essentially "fragmented" that table too much and it will
significantly slow down performance because Informix has
to go out to so many locations on the drive.
How can I tell the equivalent in Sql Server? I just ran
a dbcc showcontig on a table that is giving me troubles
and came up with the following:
DBCC SHOWCONTIG scanning 'B0001' table...
Table: 'B0001' (610101214); index ID: 1, database ID: 99
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 ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7150.0
- Avg. Page Density (full)................: 11.66%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Does this mean it has only two extents? (This table has
37 million records - I'm a little surprised it's in just
two extents.)
Also: this DBCC Showcontig ran in like two seconds. Is
that really that fast or did I do something wrong?Here are the results I should have posted. I ran it on
our development server accidently for my last post.
Also, in Informix extent is a "continuous" block of disk
space. That's what I meant by extent but it looks like
Sql Server just means 8 pages. I am trying to figure out
if my Sql Server extents/pages are all "glued" together
in a relatively contiguous block or blocks. Hope that
makes sense.
DBCC SHOWCONTIG scanning 'B00599601' table...
Table: 'B00599601' (2058451103); index ID: 1, database
ID: 92
TABLE level scan performed.
- Pages Scanned........................: 1145334
- Extents Scanned.......................: 143865
- Extent Switches.......................: 143866
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.51%
[143167:143867]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.02%
- Avg. Bytes Free per Page................: 204.0
- Avg. Page Density (full)................: 97.48%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.|||It seems that you have 2 pages which are on average ~12%
full, giving 1.92k. That's pretty impressive for 37
million records! Even with a null in a single field, the
row overhead means this is not possible. Can you do a
select count(*) from B0001 just to confirm.
Rgds,
Paul Ibison
>--Original Message--
>In Informix you have to really watch your extents. If
>you end up with too many extents on a table, you've
>essentially "fragmented" that table too much and it will
>significantly slow down performance because Informix has
>to go out to so many locations on the drive.
>How can I tell the equivalent in Sql Server? I just ran
>a dbcc showcontig on a table that is giving me troubles
>and came up with the following:
>DBCC SHOWCONTIG scanning 'B0001' table...
>Table: 'B0001' (610101214); index ID: 1, database ID: 99
>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 ..............: 50.00%
>- Extent Scan Fragmentation ...............: 50.00%
>- Avg. Bytes Free per Page................: 7150.0
>- Avg. Page Density (full)................: 11.66%
>DBCC execution completed. If DBCC printed error
messages,
>contact your system administrator.
>Does this mean it has only two extents? (This table has
>37 million records - I'm a little surprised it's in just
>two extents.)
>Also: this DBCC Showcontig ran in like two seconds. Is
>that really that fast or did I do something wrong?
>.
>|||OK - this data looks more like it :)
This looks like a healthy table - the pages are
contiguous within the extents, there's practically no
page splits and your row size allows the pages to be very
full. I would be concerned about such full pages if there
were page-splits, which would mean that the fill-factor
should be reduced. However I suspect that your PK is an
identity one as there aren't any page splits, so such
fullness seems OK.
Rgds,
Paul Ibison|||Q: How could you tell I didn't have any page splits?
And you're correct: it's a composite index with an
identity for the third column. How could you tell that?
Why would an indentity eliminate page splits? Is it
because the data will always get laid out serially if
it's on an identity which isn't the case for us since
it's composite?
>--Original Message--
>OK - this data looks more like it :)
>This looks like a healthy table - the pages are
>contiguous within the extents, there's practically no
>page splits and your row size allows the pages to be
very
>full. I would be concerned about such full pages if
there
>were page-splits, which would mean that the fill-factor
>should be reduced. However I suspect that your PK is an
>identity one as there aren't any page splits, so such
>fullness seems OK.
>Rgds,
>Paul Ibison
>.
>|||Have a look at this page which will help you interpret these results:
http://www.sql-server-performance.com/rd_index_fragmentation.asp
External fragmentation occurs when pages are contiguous within extents.
Ideally, you have one extent switch every 8 pages; the degree to which you
have more than this determines the external fragmentation, but you don't
have any.
I guess it's theoretically possible to set an identity_insert to on and
insert a new identity value into a hole on an existing identity range to
cause a page split, but apart from that case it doesn't happen as the
records are always added at the end ('left' or 'right' depending on the
increment). I'm assuming a clustered index on the identity column BTW.
Interesting point about the composite index though. If your first column in
the index isn't the identity one, then I was just lucky to guess that there
was an indentity column there :) and the lack of fragmentation is presumably
because the data has been recently reindexed, or the index just added, or
the data has ben miraculously added in a completely sorted order. If it was
the first column out of the three, then I'd expect the corresponding lack of
page-splits as mentioned previously.
Rgds,
Paul Ibison|||Thx. Great article.
>--Original Message--
>Have a look at this page which will help you interpret
these results:
>http://www.sql-server-
performance.com/rd_index_fragmentation.asp
>External fragmentation occurs when pages are contiguous
within extents.
>Ideally, you have one extent switch every 8 pages; the
degree to which you
>have more than this determines the external
fragmentation, but you don't
>have any.
>I guess it's theoretically possible to set an
identity_insert to on and
>insert a new identity value into a hole on an existing
identity range to
>cause a page split, but apart from that case it doesn't
happen as the
>records are always added at the end ('left' or 'right'
depending on the
>increment). I'm assuming a clustered index on the
identity column BTW.
>Interesting point about the composite index though. If
your first column in
>the index isn't the identity one, then I was just lucky
to guess that there
>was an indentity column there :) and the lack of
fragmentation is presumably
>because the data has been recently reindexed, or the
index just added, or
>the data has ben miraculously added in a completely
sorted order. If it was
>the first column out of the three, then I'd expect the
corresponding lack of
>page-splits as mentioned previously.
>Rgds,
>Paul Ibison
>
>.
>|||Sorry - typo - 2nd para should read "are not contiguous"
Rgds,
Paul

Extent of FullText Population

Is there a means to determine to what extent an existing
SQL table has been populated with the FullText indexing
service .... so to try to assess how long an
incremental population may take ?
Thanks
PhilipPhilip,
Yes, you can use one or more the system metadata, such as
FullTextCatalogProperty('<FT_Catalog_Name>', 'populatestatus') to monitor FT
Populations.
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Philip" <plippard@.nc.rr.com> wrote in message
news:02b301c34f27$15108970$a301280a@.phx.gbl...
> Is there a means to determine to what extent an existing
> SQL table has been populated with the FullText indexing
> service .... so to try to assess how long an
> incremental population may take ?
> Thanks
> Philip

extent locks

hi
Im getting exclusive locks on my table and ext is the resource locked......
can anyone explain me..why do we get ext locks on tables.
regardjust means SQL server has identified a group of 8 pages(extent) for update insert(most likely upadte and insert), create or drop destined for an exclusive lock. More efficient than locking an individual page of a contiguous set of 8 pages|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
--
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>