Are Bit Flags Bad?

Is the process of using integer data types to represent multiple values via the use of bit flags bad practice? It seems to go against the rules of normalization in a single field can represent multiple values. On the other hand that since these values can be tested for via bitwise operations, that it's not entirely bad.

Any insight would be appreciated.

ThanksI've used integers to store multiple bit states on occasion and found it very usefull.

For instance, you can have multiple boolean security states, and if you think of them as separate boolean values then separate bit fields would seem appropriate. But is you think of them as a single value describing the overall state of the security setting, then you aren't really violating any principles by storing them as a single field.

At a practical level, SQL Server will combine up to eight bit fields and store them internally as a single byte anyway.

Normalization is good practice because it leads to efficient and functional design. But if you avoid an instance where violating it would actually improve an application, then you are letting the cart lead the horse. Normalization is principle, not dogma.|||there is a multitude of examples where M$ itself used...smallint...as a datatype for this type of scenarios. i think int would be an overkill, while following the trend would save you storage space while doing the job.

Archiving

Hi All,
I'm building an archiving applicaition for my small organization, but I have many file types such as videos, photos, texts, pdfs... and some huge file sizes of about 500 MB.
My questions are:
1. Somefriend told me that there is a hardware compression device which can compress allready compressed videos and JPEGs. Is it correct? If so, where can I surf to find such devices?
2. Which datatype have I to use with SQL Server 2000 to handle all the different file types and sizes?

Thanks very much
HaythamAny answer there?|||1. I'd be surprised if such a thing exists. Ask your friend to be more specific.

2. I'd guess binary or varbinary. I have no experience storing binaries in sql-server though.|||Store raw datafiles as text or image datatypes.

Applying Service Packs

Are there any guidelines or best practices for deploying or testing Service Packs? What types of tests should be performed prior to upgrading or do I just trust Microsoft?I always put them on test machines first for a month or so. Enough time that some applications have had a testing cycle, anyway. If there are no major complaints, the first production guinea pig is selected, and watched for a week. If no major problems come up, then the rest of the machines get it. never trust Microsoft. They are human, too...well, half human.|||That's the way to do it. We have dev, test, and production environments. Dev gets the release 1st, and if no one screams after a couple of weeks, we apply it to test. Again, the absence of any wailing and gnashing of teeth means we move to production.

Plus, an added benefit is that you get to practice the install in case something goes horribly wrong (oh my ... would that ever happen?)|||Definitely agree with the two previous posters. In addition, read the release notes, particularly if you have a clustered environment or one where you use replication (upgrades MUST be done in a certain order when replication is involved)

Lempster|||Thank you all!! Pretty much thought so, but just wanted to see how others were handling it. :)

Applying CASE in Stored Procedures

I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:

ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)

AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.

Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys

But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.

I'm using SQL Server 2000, connecting with Visual Studio.NET 2003

Moving to the Transact-SQL forum, which is best suited to this type of questions.|||

What is the value of @.keys?

If you hard-code the values do you get results?

|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Well, the CASE in the WHERE does works

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.

|||

This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:

http://www.sommarskog.se/dyn-search.html

It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.

All of this is nicely covered in that article.

|||

Hi,

In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..

Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys