SQL add a Default to an Existing Table Column

When a previously created database table requires the addition of a default value. Can the amendment be made easily? And without recourse to the SQL Configuration Manager. I had configured a database table. Subsequent testing showed that one of the fields required a default value. I was testing for a […]

Get nth Row From Database

For a parent-child database table relationship – could retrieving the nth child row be used? I had a project comprising a main table with an associated secondary table. Similar in style to a manufacturers parent table with associated products child table. In this particular example I knew that there would […]

Update and Replace Field Value with New Value

We can use SQL to replace the value in field with a new value. There’s no need to search for those rows affecting, downloading each in turn, making the amendment and finally making the update. For the update of the given field in the table we can use one SQL […]

Verify Database Backup

I was working with a new computer system. I had made a backup of the database. But could I rely on it? To confirm the first run of a database backup I wanted to test/verify it was OK. What I didn’t want to do was to restore the database over […]

Update Row or Insert if Doesn’t Exist

When adding an entry to a database it would be nice to combine the addition with an update. For example adding a new entry if it already exists then that entry can be updated. This means putting more of the sequence within the database stored procedure. But it will save […]

Composite SQL Field with null Value Returns Blank

The intent was to join the address fields as a single returned value from a database query select. But I found that if one of these fields was null, the whole returned result was empty. I had an address, in the usual way, across a number of database fields: address; […]

Insert string into Database as Datetime

Inserting values into a database table I wished to add a date and time. For this I used a convert CONVERT(datetime, ’26/05/2014 14:21:00′) However this will fail where the day value is too high. A value greater than 12 will be taken as an invalid month. Also the day and […]

Get Table Foreign Key Details by SQL

How to view the foreign key settings within a database administered using MyLittleAdmin? Looking to find information about foreign keys between tables, via the control panel for a website I was using MyLittleAdmin to manage the associated database. I was able to view the keys associated with a particular table […]

Turn off Identity Whilst Inserting SQL Table Row

Inserting table rows including the index idents will give an error similar to: Cannot insert explicit value for identity column in table TableName when IDENTITY_INSERT is set to OFF. I was copying the contents of a database table from one installation of DotNetNuke to another. Wishing to maintain the table […]

Convert SQL Field of Type text to nvarchar(max)

To enable database tables to handle the extended character set languages I was looking to convert fields from type┬átext to ntext. Aware of the pending loss of support for the field types text and ntext I also chose to convert these fields to nvarchar(max). SQL server 2016 is removing support […]