Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.
C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:

> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
>
>
|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.
|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>
|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...

> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.
|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jan

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:
> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> > We have a table that has ~900,000 rows in the table. The table previously
> > had
> > ~1.5 million rows of data when the ~600,000 rows where deleted the table
> > size
> > shrunk ~1 G. The table is currently useing ~27 GB of storage.
> >
> > I would like to write a query to determine my largest 25 rows. This is
> > being done to determine if the storage is truely being used.
>
>|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I was hoping to find a was to gather the information using system tables
> > ie
> > number of pages used by row.
> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...
> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jansql

Detecting Tables with no recent activity

Is there an efficent way to determine which tables in a SQL Server database
have had no activity reccently. This includes inserts, updates, deleted
and/or selects.
Are there any tools or techniques to find these tables?
All help appreciated!
Tim O wrote:
> Is there an efficent way to determine which tables in a SQL Server
> database have had no activity reccently. This includes inserts,
> updates, deleted and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
Not inherently, as far as I know. The system tables do not track changes
to underlying objects. You can easily implement some very simple
triggers on the tables you are trying to track using something like the
following. This implementation tracks changes to a table at most once
each day. Using the Ignore_dup_key option on the clustered index tells
SQL Server to ignore duplicates, preventing you from having to manually
check the table each time. You could, of course, improve on this concept
and add a user column and add the suser_sname() value to it and the
index.
CREATE TABLE [testing] (
[col1] [int] NULL)
go
create table TableAccess(TblNm nvarchar(128) NOT NULL, AccessDate
char(8) NOT NULL)
go
create unique clustered index TableAccess_idx on TableAccess (TblNm,
AccessDate) with ignore_dup_key
go
create trigger testing_change on testing
for insert, update, delete
as
Begin
insert into TableAccess (TblNm, AccessDate) Values ('testing',
CONVERT(char(8), getdate(), 112))
End
go
insert into testing values (5)
insert into testing values (6)
insert into testing values (7)
David Gugick
Imceda Software
www.imceda.com
|||"Tim O" wrote:

> Is there an efficent way to determine which tables in a SQL Server database
> have had no activity reccently. This includes inserts, updates, deleted
> and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
The use of TRIGGERS will not detect SELECTS that are executed on a TABLE as
far as I know!
I need an EFFICINIENT WAY to detect table that are neither read for written
to.
I am also concerned with the overheads that may be placed on the system.
There are a large number of tables and a high degree of daily activity!
Cheers Tim O
|||Tim O wrote:
> The use of TRIGGERS will not detect SELECTS that are executed on a
> TABLE as far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
> written to.
> I am also concerned with the overheads that may be placed on the
> system. There are a large number of tables and a high degree of daily
> activity!
> Cheers Tim O
If you are using stored procedures, you can log access from there, even
on SELECTs. The overhead is minimal on insert activity. You can place
the table on a secondary filegroup or in another database, if necessary.
David Gugick
Imceda Software
www.imceda.com
|||Tim,
There is a column in sysindexes for the object that keeps a count of
modifications to the table. The column is rowmodctr. SQL uses this to keep
track of when to run Update Stats when "Auto Update Stats" is on. If after
running update stats for that table, the value rises above 0, then there are
modifications to it. For more info please see:
http://support.microsoft.com/default...en-us%3B195565
As far as Selects, you can run profiler (trace) to see if it is called using
the Filter "TextData". You can also see if it is embedded in any stored
procs, triggers via sp_depends <table_name>. This won't indicate any
references from outside the current DB, tho.
Hope some of this helps.
Steve
"Tim O" <TimO@.discussions.microsoft.com> wrote in message
news:31F8C451-62F7-480F-9362-736E07698FFF@.microsoft.com...[vbcol=seagreen]
>
> "Tim O" wrote:
database
> The use of TRIGGERS will not detect SELECTS that are executed on a TABLE
as
> far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
written
> to.
> I am also concerned with the overheads that may be placed on the system.
> There are a large number of tables and a high degree of daily activity!
> Cheers Tim O

Design Question--partial Date data

Hi everyone,
I am working on an SQL Server 2K application that includes a requirement for
~20 "partial" Date fields (spread through several of the database tables).
By partial Date, I am referring to a Date where the Day portion is not
required, the Month portion or may or may not be required (depending on the
particular field), but the Year is always required. When either Month or Day
is not required, they _may_ be entered if known.
Clear so far? OK. I have considered creating a user-defined datatype
'pDate (int)' wherein the date data would be encoded e.g. 20070904 (today) or
20060000 (Year is 2006, Month and Day are unknown) or 19840500 (May 1984).
My thinking behind this approach is that 1.) ints are relatively compact, and
2.) Sort/compare should be easy, particularly since my client wants to use
the 'filing' rule ("Nothing always comes before something") so 19840430 <
19840500 < 19840501 ("May 1984" occurs just after "April 30, 1984" and just
before "May 1, 1984").
Then, I thought of applying a Rule to allow me to validate the data entry
(yyyy0230 is always invalid, but yyyy0229 is OK only in a leap year, etc.).
The beauty of this idea, at least on intial consideration, is that a
user-defined Rule can be associated with the user-defined pDate custom
datatype, and so I wouldn't have to associate (and, perhaps more importantly,
maintain) the same exact check constraint with each of the 20 instances of my
partial-Date fields.
By now you're probably already thinking "Rules are too simple--that won't
work." or "Rules are being deprecated by Microsoft--good luck when your
client upgrades to SQL Server 2008." If so, I agree...I think...but then I'm
a relative newbie, and so I'm not very certain of that.
Question 1: Do you know of a better way to create such a partial-Date data
type?
Question 2: If my 'pDate(int)' idea is reasonable, is there a way to define
a reusable (i.e. single-place definition) check constraint that can be
applied to each of these fields?
Furthermore, recall that some of these "pDate" fields will require a Month,
some will not. I am considering creating and then setting an extended
property on each such pDate(int) field as a way to identify this requirement
because, it seems to me, this might be a good way for my data entry
validation procedure to retrieve "on the fly" and act on this requirement as
desired.
Question 3: Is there a reason why such an extended property won't work?
Question 4: Can you think of a better way to implement such a
field-instance-dependent data validation requirement?
Based on past experience, I am probably missing something blatantly obvious
to an even slightly expert SQL Server developer, so any advice or suggestions
would be most welcome. Thanks in advance.
Best regards,
EdWhy not store Y (smallint), M (tinyint) and D (tinyint) in separate columns.
When M or D is NULL you can use COALESCE to still generate a valid date when
converting. And a separate column (or existing criteria) could be used to
dictate whether M/D can/should be NULL.
A|||Aaron,
Thanks for the suggestion; I think that's a great idea, not to mention much
simpler than where I was headed, and at no additional storage space cost!
Thank you.
Ed
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why not store Y (smallint), M (tinyint) and D (tinyint) in separate columns.
> When M or D is NULL you can use COALESCE to still generate a valid date when
> converting. And a separate column (or existing criteria) could be used to
> dictate whether M/D can/should be NULL.
> A

Design question

We currently have an ASP.NET app that includes some reports. I'm
considering
making the data available in RS and I'm looking for some opinions
about
the feasibility and difficulty in doing this.
The app uses ODBC to connect to DB2. The users use their DB2 userid
and password, and this is what is used in the connection string.
The current solution includes a lot of manipulation of datatables
which are then bound to ASP.NET datagrids.
My thought was to create a web service that would serve as an RS Data
Extension, but I'm just taking a first look at this and don't know how
practical it would be. The idea is that the data retrieval and
manipulation logic could be in the web service, which would return a
dataset (or whatever).
The solution would have to include drilling down / linking to other
reports, so the userid and password would have to be maintained behind
the scenes so they could be passed, along with other parameters, to
the web service for each report/subreport.
Any thoughts are welcome.
TIA,
JimSounds like a winner to me :-). It doesn't have to be a web service of
course, you can write a data extension which would itself query your data
source and massage the data.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jim corey" <jhcorey@.yahoo.com> wrote in message
news:1c4f8dcf.0409210702.587ac17d@.posting.google.com...
> We currently have an ASP.NET app that includes some reports. I'm
> considering
> making the data available in RS and I'm looking for some opinions
> about
> the feasibility and difficulty in doing this.
> The app uses ODBC to connect to DB2. The users use their DB2 userid
> and password, and this is what is used in the connection string.
> The current solution includes a lot of manipulation of datatables
> which are then bound to ASP.NET datagrids.
> My thought was to create a web service that would serve as an RS Data
> Extension, but I'm just taking a first look at this and don't know how
> practical it would be. The idea is that the data retrieval and
> manipulation logic could be in the web service, which would return a
> dataset (or whatever).
> The solution would have to include drilling down / linking to other
> reports, so the userid and password would have to be maintained behind
> the scenes so they could be passed, along with other parameters, to
> the web service for each report/subreport.
> Any thoughts are welcome.
> TIA,
> Jim