Extended Properties for adding descriptions

Question

Extended Properties for adding descriptions

Raghunath Garlapati

Ten Centuries

Points: 1122
More actions
February 28, 2013 at 12:15 am

#290129

Hi,
I want to use extended properties for adding description to our database. I tried it and I am able to do that, my problem here is; The database which we have is very huge in size. Can we use extended properties on these kind of DB's. If no, what will the problems of doing so? If Yes, are there any other advantages doing so?
Please give as much as information on extended properties related to performance affect on DB.
Thanks in advance!!!
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

By huge in size do you mean a large number of tables/columns or that each table is very large?

The text you specify obviosuly has to be stored, but compared to the user data I would not have thought you would have any impact ion performance unless you started storing huge volumes of text on thousands of columns.

Mike John

Lowell SSC Guru Points: 323518 More actions · Answer 2

extended properties don't have any significant impact on the database size; it's not describing per-row data, but rather schema data.

the other thing to remember is that objects that are not accessed do not have an impact on other queries. if i add a new table, even if it has a lot of data, it will not affect other queries that don't touch that table.

Sizewise impact, for example, my pretty good sized database has under 2000 tables, and under 20,000 columns.

if i added a typical 50 character description for every table and every column, i'd only add a bit over a Meg in size i think(select (22000.0 * 50.0) / (1024 * 1024) As MegaBytes)

the max size looks like a sqlvariant, which would be a varchar(8000) i guess, so that could add up to 167 meg in size, if i had a full 8000 chars worth of descriptiosn for every column.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Raghunath Garlapati Ten Centuries Points: 1122 More actions · Answer 3

Thanks for your reply!!!

We have around 1500 tables and 15000 columns. My concern is not about the increase in DB size(memory).

I am more interested to know that if we add description to all these tables and columns (take 1000 chars per column on avg) will this affect DB performance. DB performance in the sense normal DDL or DML operations.

Usually it shouldn't, still I would like to check with experts whether any hidden challenges or problems exist using the concept of Extended properties for Huge Transactional DB's.

/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/

Raghunath Garlapati Ten Centuries Points: 1122 More actions · Answer 4