Tag: database-design

When is it better to store flags as a bitmask rather than using an associative table?

I’m working on an application where users have different permissions to use different features (e.g. Read, Create, Download, Print, Approve, etc.). The list of permissions isn’t expected to change often. I have a couple of options of how to store these permissions in the database. In what cases would Option 2 be better? Option 1 […]

INSERT INTO if not exists SQL server

I have a database structured as follows: users userid (Primary Key) username group groupid (PK) groupName user_groups userid (Foreign Key) groupid (Foreign Key) The first time a user logs in I would like their info to be added to the users table. So essentially the logic I would like to have if if (//users table […]

Why to use “not null primary key” in TSQL?

I am seeing too frequently “not null primary key” in scripts creating tables in TSQL in blogs, articles, books, forums, here in SO, etc. For example, BING gives 630,000 results ((just in English) if to search against “not null” NEAR “primary key” “sql server”: http://www.bing.com/search?q=%22not+null%22+NEAR+%22primary+key%22+%22sql+server%22&go=&form=QBRE&filt=lf I always perceived “NOT NULL” as inseparable part of definition […]

design database relating to time attribute

I want to design a database which is described as follows: Each product has only one status at one time point. However, the status of a product can change during its life time. How could I design the relationship between product and status which can easily be queried all product of a specific status at […]

Performance of different aproaches to time-based data

I’m asking this in the context of PerformanceDBA’s claim in this answer to another question that this query: SELECT ProductId, Description FROM Product p, ProductStatus ps WHERE p.ProductId = ps.ProductId — Join AND StatusCode = 2 — Request AND DateTime = ( — Current Status on the left … SELECT MAX(DateTime) — Current Status row […]

SQL Unique constraint across multiple tables

I am trying to create a unique constraint across multiple tables. I have found similar questions answered here but they don’t quite capture the spirit of what I am trying to do. As an example, I have three tables, t_Analog, t_Discrete, t_Message CREATE TABLE t_Analog( [AppName] [nvarchar](20) NOT NULL, [ItemName] [nvarchar](32) NOT NULL, [Value] [float] […]

SQL Server, can't insert null into primary key field?

I’m about ready to rip my hair out on this one. I’m fairly new to MS SQL, and haven’t seen a similar post anywhere. When I try to do a statement like this: INSERT INTO qcRawMatTestCharacteristic VALUES(NULL, 1,1,1,1,1,1,1,”,”,”, GETDATE(), 1) I get the following: Cannot insert the value NULL into column ‘iRawMatTestCharacteristicId’, table ‘Intranet.dbo.qcRawMatTestCharacteristic’; column […]

What does ON mean?

I’m creating an SQL setup script and I’m using someone else’s script as an example. Here’s an example of the script: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[be_Categories]( [CategoryID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_be_Categories_CategoryID] DEFAULT (newid()), [CategoryName] [nvarchar](50) NULL, [Description] [nvarchar](200) NULL, [ParentID] [uniqueidentifier] NULL, CONSTRAINT [PK_be_Categories] PRIMARY KEY CLUSTERED […]

Storing HTML in SQL Server

What data type should I use to store HTML content in SQL Server 2008? It’s for dynamic content for a CMS.

SQL Server: One Table with 400 Columns or 40 Tables with 10 Columns?

I am using SQL Server 2005 Express and Visual Studio 2008. I have a database which has a table with 400 Columns. Things were (just about manageable) until I had to perform bi-directional sync between several databases. I am wondering what arguments are for and against using 400 column database or 40 table database are? […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.