:::: MENU ::::

Posts Categorized / Data Modeling and Database Design

  • Jun 23 / 2009
  • 0
Data Modeling and Database Design, Data Types, dbDigger

Attempting to add multiple identity columns to table ‘%.*ls’ using the SELECT INTO statement.

The following error message
Msg 8109, Level 16, State 1, Line 1
Attempting to add multiple identity columns to table ‘%.*ls’ using the SELECT INTO statement.

is a run time error message and appears when you try to execute a SELECT INTO statement in which you try to add more than one column with the IDENTITY property to the new table by using more than one IDENTITY function call.
Every table can contain at most only one IDENTITY column. You must remove multiple IDENTITY function calls from the SELECT INTO statement. Following is an example of such ststement.

 
SELECT IDENTITY(INT, 1, 1) AS i1, IDENTITY(INT, 10, 1) AS i2  
 INTO #t  
 FROM Northwind.dbo.Orders  
 WHERE CustomerID LIKE 'B%'  
  • Jun 22 / 2009
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, SSMS tips and tricks, T-SQL Scripts

Create unique key constraint through T-SQL and SSMS

There may be required to make sure that to fulfill a certain business logic, each record should be unique in terms of some columns. To implement this there is no need to create any primary key having combination of columns. Simply a unique key constraint may be created.
To consider an example of creating unique key constraint let us create a sample table

 
USE AdventureWorks  
 GO  
 CREATE TABLE TestUKey(serNumb SMALLINT IDENTITY(1,1),   
 name VARCHAR(50),  
 city VARCHAR(50),  
 phone VARCHAR(13),  
 cellNumb VARCHAR(13))  
 GO  

Suppose now it is required to make sure that each record have unique combined values in name and cellnumb. To implement this constraint we may use following T-SQL statement
[/sql] USE AdventureWorks
GO
ALTER TABLE TestUKey
ADD CONSTRAINT IX_TestUKey_NameCell
UNIQUE(Name, CellNumb)
GO
[/sql]
Our required logic has been implemented and to confirm it following is a part of result from <

sp_help TestUKey

Unique key constraint confirmation

To create unique key constraint through SQL Server Management Studio (SSMS), following are required steps.
Drop existing unique key constraint so that we may create unique key constraint through SSMS

 
USE AdventureWorks  
 GO  
 ALTER TABLE TestUKey  
 DROP CONSTRAINT IX_TestUKey_NameCell  
 GO  

Now we are ready to create unique key constraint through SSMS

  • Right click on table on which constraint will be implemented and go to Design
  • Right click on left bar of design to access indexes and keys or access from tool bar

Create unique key constraint through SSMS 1

  • A frame will appear. Choose both columns and select unique key in type.
  • Close the frame and exit from designer.

Unique key has been created and this may be verified through SSMS

Confirm unique key constraint through SSMS

I have noticed that along with each unique key created a non clustered index is automatically created comprising the columns of unique key.
Click here for another example of unique key constraint implementation.

  • Jun 11 / 2009
  • 0
Data Modeling and Database Design, dbDigger

Invalid usage of the option %.*ls in the %ls statement.

The following error message
Msg 153, Level 15, State 1, Line 7
Invalid usage of the option %.*ls in the %ls statement.

appears when you try to use an option in an invalid context. Example of such invalid context is when you try to use a decimal number in the FILEGROWTH option of a CREATE DATABASE statement as in below example.

 
CREATE DATABASE abc  
 ON PRIMARY  
 ( NAME = abc_dat,  
 FILENAME ='E:abc_dat.mdf',  
 SIZE = 0,  
 MAXSIZE = 0,  
 FILEGROWTH = '15.4%'  
 )  
  • May 19 / 2009
  • 0
Data Modeling and Database Design, Data Types, dbDigger

Only base table columns are allowed in the TEXTPTR function.

The error message
Msg 280, Level 16, State 1, Line 15
Only base table columns are allowed in the TEXTPTR function.
is a run time error message and is raised when you try to use constructs such as computed columns in the TEXTPTR function. To resolve the error provide base columns as input to TEXTPTR function. The TEXTPTR function only accepts “base” columns as valid input.

  • Apr 21 / 2009
  • 0
Data Modeling and Database Design, dbDigger, Performance Tunning and Optimization

Is normalization always required in database design

Normalization is an important aspect of database design. It removes redundancy and ensures relational integrity of database. Most of OLTP databases are designed up to 3rd normal form. It is important to understand that all database designs do not require normalization as it is commonly implemented till third normal form.
OLTP databases or some times called production databases make more use of normalization than OLAP or data ware house databases. Benefits of normalized database cost processing power to resolve joins and relations.
For this reason in data ware house modeling, normalization is avoided. Due to large amount of data data ware housing architects are conscious of processing time of their scripts and reports. So they prefer to consume disk resources through redundancy in data but do not afford to use joins in their scripts because joins in such large amount of data would slow down the query processing.
So we can say that use of normalization depends upon amount of data, type of database and being used hardware resources.
More levels of normalization used more tables will be created and as a result more joins will be used.

Consult us to explore the Databases. Contact us