Determine Index Size

Can anyone help me figure out the size used by my indexes?
TIA
RobEXEC sp_spaceused
optional:
EXEC sp_spaceused '<table name>'
--
Jacco Schalkwijk
SQL Server MVP
"Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> Can anyone help me figure out the size used by my indexes?
> TIA
> Rob
>|||Use can query the "used" column in sysindexes table for Indid = 1 , indid
between 2 and 249
select object_name(id) as Table_name,sum(used) from sysindexes
where indid between 1 and 249
group by object_name(id)
Thanks
Hari
MCDBA
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:#b4gKg0uDHA.1756@.TK2MSFTNGP09.phx.gbl...
> EXEC sp_spaceused
> optional:
> EXEC sp_spaceused '<table name>'
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
> news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> > Can anyone help me figure out the size used by my indexes?
> >
> > TIA
> >
> > Rob
> >
> >
>sql

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
WadeNevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Determine Date Range Falls between other Date Range

I need to figure out how to see if a date in a date range falls between
another date range. For instance - I need to know if any date between
3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
Does anyone know of a way to code that without using a cursor to go
through every day to see if that day of the month is between the other
date range?
Melissambonsted@.yahoo.com wrote:
> I need to figure out how to see if a date in a date range falls between
> another date range. For instance - I need to know if any date between
> 3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
> Does anyone know of a way to code that without using a cursor to go
> through every day to see if that day of the month is between the other
> date range?
> Melissa
Here's some sample data and a query:
CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
INSERT INTO tbl VALUES ('20060301', '20060331');
DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
SET @.dt_from = '20030216';
SET @.dt_to = '20060401';
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from >= @.dt_from
AND dt_to <= @.dt_to ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 Apr 2006 14:21:55 -0800, David Portas wrote:

>mbonsted@.yahoo.com wrote:
>Here's some sample data and a query:
>CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
>CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
>INSERT INTO tbl VALUES ('20060301', '20060331');
>DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
>SET @.dt_from = '20030216';
>SET @.dt_to = '20060401';
>SELECT dt_from, dt_to
> FROM tbl
> WHERE dt_from >= @.dt_from
> AND dt_to <= @.dt_to ;
Hi David,
This will only find date ranges completely embedded in another date
range. To find any overlap, change it to
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from <= @.dt_to
AND dt_to >= @.dt_from ;
(Change <= and >= to < and > if one range starting the same day that
another range ends is not considered an overlap)
Hugo Kornelis, SQL Server MVPsql

Design question regarding comma delimited data

Hi,

I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below:

movie_title | movie_rating | category_name
------------------------------
Pirates of the Carribean | PG-13 | Action,Adventure,Fantasy
Evil Dead | Unrated | Horror

and so on ...

I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift?

It all seems counter-intuitive. Thoughts?

Thanks :o)

You should go with the second approach (separate table where you store MovieId, CategoryId) unless you have a good reason to not do it so.

Think about the fact that search by category is not the only possible query you will need. How would you do implement the following using your first approach:Select the number of movies in each category? Using a separate table you can just group by category and do a count().

Keep in mind that normalization is almost always a good thing to do. If you don't know what that means you really have to take a book on database design and read about it.

|||

Create a child table that joins the two tables together

Your Movies Table
MovieID int identity PK
Title varchar
Rating varchar

Your Categories Table
CategoryID int identity PK
Name varchar

Your MovieCategories table
MovieID int
CategoryID int

So if you enter a movie in the Movies table
Title: Pirates of the Carribean
Rating: PG-13

It would get assigned an auto-incrementing ID (in this case 1)

You then populate your Category Table with things like
Action (gets assigned 1)
Adventure (gets assigned 2)
Drama (gets assigned 3)
Comedy (gets assigned 4)
Fantasy (gets assigned 5)

Then in your child table you assign the MovieID for Pirates with the CategoryID's that it belongs to
MovieID CategoryID
1 1
1 2
1 5

Then when you query you would join the necessary tables together to get what you are after.

|||

>>Now how would I associate one movie with several categories?

The method you described isvery typical ( a third table to link the other two).

Table: MovieCategoriesMovieID CatID1 11 32 23 13 23 3

This is the utilimate in flexibility but requires more work from you.

A quick and dirty way (not always bad) would be a boolean column for each category.

This assumes that categories are fixed and you will not be adding new categories

|||

The best will be to create table

Movie with MovieID and Movietittle

Ratings with RatingID and RatingName

Category CategoryID and categoryname

and two tables to link your information

MovieRatings with mrID, mrMovieID, mrRatingID

MOvieCategory with mcID, mcMovieID ,mcCategoryID

With this structure your queries will be much faster when with like statement and also it will save you same space in database

You can have multiple categories connected to movie in movie category table and also different ratings if you need.

Thanks

|||

Thanks for all the replies.

See, I used the, for lack of a better word, "third table" approach before. It's the only way I could think of doing what I wanted it to do. I just never liked that idea of having to create another table. But apparently this is pretty common, so I don't feel so bad about it anymore :o)

It's basically going to look like this:

Movies
movie_id movie_title movie_rating
-------------
1 Bad Movie PG
2 Cool Movie R
3 So So Movie PG-13
Categories
category_id category_name
----------
1 Action
2 Adventure
3 Comedy
4 Drama
Movies/Categories
movie_id category_id
--------
1 1
1 2
2 3
2 4
3 3

I guess that makes good sense, but it always seemed redundant.

Thanks :o)