More useful SQL Server commands

When you modify a table in SQL Server (I still use mostly SQL Server 2005) and try to uncheck an Allow Nulls option for a certain field (thus prohibiting having NULL values in this column), the following error may occur:

Cannot insert the value NULL into column ‘<columnname>’, table ‘<dbname>.dbo.Tmp_<tablename>’; column does not allow nulls. INSERT fails. The statement has been terminated.

This error message appears because there are already null values in the column. So, what you should do is update the data in the table, so that this column does not contain any null values. If the column with null values is an integer (int) type you might update it with this SQL statement:

UPDATE <databasename>.dbo.<tablename>

SET <integercolumnname> = -1

Then you can go to the design mode of the table again and uncheck Allow Nulls option. To prohibit having NULL values by using an SQL command you could type:

ALTER TABLE <tablename>

ALTER COLUMN <columnname> <datatype> NOT NULL

Example:

ALTER TABLE Employees

ALTER COLUMN EmpName nvarchar(40) NOT NULL

This will force users not to leave this field in the table empty.

Dealing with null values in geodatabases feature classes

It is impossible to change Allow NULL values option for a field in a feature class, which already contains data. The only possible solution, to my knowledge, is to create a new empty feature class, define all required fields and data types and then set Allow NULL values appropriately. Thereafter, the load data from the original feature class to a newly created one has to be done. More info in the ESRI KB #34782.

How to add UNIQUE constraint for a field in SQL Server table

Sometimes it is necessary to allow users to add only unique values to a certain column. This can be done by running this command:

alter table <tablename>

add unique (<columname>)

It is worth noting that, in ArcMap, when you edit attribute fields of an SDE feature class for which you have set the unique constraint in SQL Server, and try to enter an existing value in a field with defined unique constraint, you will get an error message and the value will not be entered.

Adding a non-unique values

Thus, you can enforce the integrity of your data on ArcMap and DBMS level at the same time.

Update: use the Attribute Inspector (available from the Editing toolbar > Editor > Editing Options > Attributes tab. Check the Display the attributes dialog before storing new features option. This will allow you to enter attributes prior to writing rows to your database at the DBMS level.

If you are unsure what constraints have been defined for a field, you might like using the sp_help command, which will give you all the information about the table.

Just start a new query and run

sp_help ‘<tablename>’

Example:

sp_help ‘Employees’

How to select only certain rows from a table

If you need to select rows that match a specific query you might like using this syntax:

select * from <table>

where <columnname> in (value1, value2, value3)

Example:

select * from Employees

where EmplRegion in (South, Central, North)

If you need to perform a more complex query, then it might be useful to give this syntax a try:

select * from <table>

where <columnname> in (select <columnname> from <tablename> where <columnname> is not null)

Example:

select * from Employees

where EmplRegion in (select Districts from NorthDistricts where ReportedBy is not null)

This query will allow you to select all rows from the Employees’ table based on the name of the district where a report has been submitted.

To get more tips on using SQL Server I would recommend visiting http://www.sql-server-helper.com – which is a great web site containing a lot of useful information.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s