Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-01 : 11:03:45
|
Lana writes "Hello, team. I would really appreciate if you help me find an answer.
I am designing a table that consists of 3 columns: ConfigID int, UserID int (first 2 forming a PK)and Settings varchar(8000). It is supposed to keep track of settings (font,color, rowsize, column order in grids etc.) for an individual user. Those settings will be saved in a string that will be parsed by the calling application. In 95% of the cases 8000 characters will be enough. ( the average is 3-4 K). To accomodate the other 5% I decided to add a field called SeqNum int that will become a part of the primary key and help keep track of the Settings if it exceeds 8K. The stored procedure I wrote checks the number of records for the ConfigID,UserID combination and if it's more than 1 it returns a recordset, otherwise a string. My problem - the DataAccess component of the calling application needs to know what it's going to receive a recordset or an output param. I see 3 ways and I hate them all.
1. Find out the number of records by calling a select count(*) sp
and then decide - 2 trips to the db server
2. Always return a recordset - much-much slower than an output parameter according to Craig Utley and guys at sqlserverperformance.com
3. Make settings a text field specifying table option 'text in row','4000' - slower and hard to read and write with all those TXTPTR,DATALENGTH and TEXTSIZE functions to use.
Does anybody see a way to deal with it? Thank you." |
|
|
|
|
|
Output parameters vs a recordset