Query for Table structure

Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null

Query Date Stored as Text

I am using Windows "Performance Logs and Alerts" to log performance statistics to a SQL database. In this case I am using a Counter Log, which creates the necessary tables upon startup of the log. I can see my data pouring into the database and now I am writing some T-SQL statements to query the data and display it on an aspx page. I have one particular problem that is presenting me with a bit of a challenge. I want to be able to query my captured performance counters based on date ranges entered on the aspx page. Unfortunately I am having trouble doing that because the "CounterDateTime" field where the date for each counter sample is stored is a char(24) data type instead of a datetime data type. I'm not sure why MS chose to store dates in a char field but I am not sure how to query that field for date ranges. So my question is, how would one query for a date range when the dates that I'm querying are in char format? Thanks in advance.

Could you post some sample of how the format of the datetime value looks like in the table? If it is some format that retain order in character format for datetime values then you can simply use string comparisons. Best is to convert the column to datetime and then do your manipulations. Of course, performance will suffer if your primary search condition is the datetime column only. For the conversion part, I think you may have to strip some time part. If I remember correctly the value stored by perfmon are higher resolution than what SQL Server currently supports. This is probably one reason why they can't store it in datetime column for example.|||

Here is an example of what the data looks like in the datetime field: 2006-08-15 11:37:17.121

Unfortunately, I doubt that changing the datatype in the table is an option since the table and datatypes are created automatically by the SysMon counter log. My guess is that if I change the structure of the field, the counter log will error out when it tries to log data since the format in the table will not jive with the type of data that the counter log is trying to insert. I don't know if there is a good solution to this problem. Is the CounterDateTime field basically just useless data that one can't query? On my ASP.Net web page, I allow the user to select a start date and an end date. Therefore, in my SQL query I want to select everything that falls in between the two dates. Since the date fields are text, perhaps that is not going to be possible.

Thanks.

|||

It is the ODBC canonical format so you can just use it directly without converting the datetime. This format will preserve order of the values. See examples below:

select * from Northwind.dbo.Orders
where convert(varchar, OrderDate, 121) >= '1996-07-10 00:00:00.000' and
convert(varchar, OrderDate, 121) < '1996-08-01 06:40:00.000'

select * from Northwind.dbo.Orders
where OrderDate >= '1996-07-10 00:00:00.000' and
OrderDate < '1996-08-01 06:40:00.000'

I didn't imply that you should convert the column in the table. Instead you can do a convert at run-time in your query. In any case, it is not really required. You can do your query like:

select *

from perfmon_table

where CounterDateTime >= convert(varchar, @.datetime_start, 121)

and CounterDateTime < convert(varchar, @.datetime_end, 121)

-- or if there is no index on the CounterDateTime column then just do:

select *

from perfmon_table

where cast(CounterDateTime as datetime) >= @.datetime_start

and cast(CounterDateTime as datetime) < @.datetime_end

|||Thanks for your help! That works like a champ!

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?
For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
...
and many other questions need to be asked and answered to come up with the
best choices.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>
|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>
|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> For this particular query, an index on (col2, col1) would most likely be
>> faster. But index tuning isn't done one query at a time.
>> What other queries reference table1 and access col1 and col2?
>> You need to design indexes so that all the queries that access the table
>> will perform reasonably well. There may be other choices of indexes that
>> work well for this query, and also work well for many other queries.
>> What percentage of the table have col2 values less than 100?
>> How many rows fit per page?
>> Are you also performing updates to the table?
>> ..
>> and many other questions need to be asked and answered to come up with
>> the best choices.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster
>> ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>>
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (> ), less than (< ), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>