Determine the size of an Image datatype

Is there any method to find out the size of an image datatype currently in the database. For example I have 2 tables that contain the same image, but the archive table is not displaying the same as the main. I am thinking the image file was corrupted somehow but don't know how to check this out.
Any ideas?DataLength() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_4ep4.asp)

-PatP

Design the Database in SQL First time

Hi all,
I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type).
how can i manage the Relationship....
plz discuss in detail....
thanx in advance
Sajjad Rizvi

1. Do you need syntax on how to create table?

2. Or do you need just information/suggestions on giving field types?
3. Or do you want suggestion on naming conventions on fields etc

|||Thanx for reply
I need information/suggestions on giving field types
and want suggestion on naming conventions on fields etc......
and also wanna know about various data type, like booleon,varchar,numeric....
thanx in advance
Sajjad|||sorry forget....
I m using Sql Server 2000
Sajjad|||

Try these two links the first is Microsoft new sample database AdventureWorks2000 download and the second is the book used to create it right click and download the PPT slides. When you have installed AdventureWorks2000 look at the 113 tables and see if you could use some for your application. Data Modeling is very complex some one cannot teach you online, if you need a book buy the book in the link it comes with six complete databases. BTW if you have problem installing AdventureWorks use my email in my profile and I will email you the only file you need to install it, you need SP3 to install it. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=487C9C23-2356-436E-94A8-2BFB66F0ABDC&displaylang=en

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html

|||No one can teach you database design in a forum. That's what books andonline articles are for. Lacking that Microsoft provides a pile ofdocumentation as well. Not as accessible but it's included with whatyou already have.
There are no shortcuts. Learning SQL will take some time. It'sworthwhile though so get to it and come back when you have *specific*questions.
|||

What your asking is like "can you tell me how to build a house using a Catipillar Crane and Earth Mover"?

Let's also not forget that SQL Server 2000 is a tool. You need to learn the fundamentals of relational theory and data modeling. Date's INTRODUCTION TO DATABASE SYTEMS is standard issue for fundamentals.

|||I agree that it will take time to learn SQL in and out. Database design is not easy if you have never done it before. However, there are some great links above that should point you in the right direction. If you have ever used Microsoft Access, you should be alright. In SQL 2000, you can use the "designer" (Enterprise Manager) to assist with developing the tables or you can use transact-SQL directly within Query Analyzer. The best suggestion I would have for you is to draw out everything first. Then determine what limitations need to be specified.|||

Thanx to All for great reply,

I just wanna know Normilizaton, Relation ship and some introduction about common Datatype and their usage.....

Sajjad

Design Question

I am creating a table and would like to know the most efficent way to do
this.
One of the colums will be text datatype and can have several thousand
characters in it (less than10k). The 'ntext' datatype in SQL and hold over
a million characters so that isn't a problem.
The question is: Is it more efficent to have the data directly in the
database or should I write it to a file and store a filename?
I would prefer storing the data directly in the table but if this is a
terribly bad idea I want to know. The problem I have with the file aspect
is maintenace of all the files and over years of use there could be quite a
few files generated.

Thank you in advance,
EricEric, in my opinion the choice of keeping the data in the database and
keeping only a reference to external data really depends on the use of
the data.

Will the files in question be updated after initial creation?
How often?
Are the updates complete file replacement or changes to data within the
file?
What are the chances multiple uses will attempt to update a file at the
same time?
How many different applications need access to the data?

The more changes, changes to only peices of the files, the more times
the data is accessed, and the more concurrent update activity the more
I would favor storing the data in the database.
IMHO -- Mark D Powell --|||Thank you for your response. Answers to your questions.

> Will the files in question be updated after initial creation?
Yes
> How often?
A small subset would rarely be updated.
> Are the updates complete file replacement or changes to data within the
> file?
The updates would be edits only.
> What are the chances multiple uses will attempt to update a file at the
> same time?
Infrequent.
> How many different applications need access to the data?
1

I favor the database method too, despite the fact that the updates would not
be a frequent.
Partly for security, easy to back up the database compared to many files.
My concern was eventual database size, but there must be many large database
that store records at least as large as I mentioned.

Thanks again,
Eric|||Beringer (borden_eric@.invalid.com) writes:
> I am creating a table and would like to know the most efficent way to do
> this.
> One of the colums will be text datatype and can have several thousand
> characters in it (less than10k). The 'ntext' datatype in SQL and hold
> over a million characters so that isn't a problem.
> The question is: Is it more efficent to have the data directly in the
> database or should I write it to a file and store a filename?
> I would prefer storing the data directly in the table but if this is a
> terribly bad idea I want to know. The problem I have with the file
> aspect is maintenace of all the files and over years of use there could
> be quite a few files generated.

There are people who quite strongly recommend storing only the the filename
in the database, and the text as separate files.

I am not one of those. The point with storing on file is mainly simplicity
in programming. The text datatype is messy to work with, not the least in
ADO.

On the other hand, you perfectly right that backing one database is
easier than backing up one database + lots of files. Add to that you
cannot get transactional consistency with a file system, and that a
file may disappear without the knowledge of SQL Server.

Thus, in my opinion, storing only the filename is a low-budget solution
when your requirements are lax. Storing the data in the database may
require more development, and, yes, it can also have some negative
effect on performance. But it is a more robust solution.

And in your case, since your texts are no more than 10000 chars, it's
not really an issue. Go with ntext.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL server is capable of handling that sort of data in a column.. So I
would suggest holding it in the database rather than putting them in a
filesystem. What will happen if you need to migrate things to a
different server.. or one of the path to that folder changes...? Plus
if you need to remove the data, you need to delete the file plus the
path/file name reference in the database.. Things can go wrong if you
dont stay consistant with all the updates... Plus, if more than one
person need to update one record in a particular time(dont ask me how
often thats gonna happen... but as an architect you need count that
into account when making a decision) that will complicate things...!
Plus its easy to do searches of a text in sql than thru windows
search....

Design For Loading Data Without Knowlged of Datatype or Column Count

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this...

My thanks!

If you're doing what I think you're doing, you might want to use an EAV (Entity/Attribute/Value) table to store your data. The attribute table stores the column definitions. The entity table store the "row id"s. The value table stores the actual values and is of the form

Entity_id int

Attribute_id int

Value (string/whatever)

You can have multiple value tables for different data types, but I find that it's more trouble than it's worth. I've used this on several projects where the data being captured is set by the users at run time.