Once again experiments and tests. I added a new column to the existing table and needed to fill it with different values.

I had to update the date values in the MSSQL database table for tests and demo. I needed to set random Dates from the specific range.

There's a data type 'uniqueidentifier' in SQL. If you need to feed this field with random value, you can use NEWID(). The 'uniqueidentifier' value in SQL looks like this: '234779E5-996F-45AC-90C3-21631AF4A08D'.

In the developer database (MS SQL Server) I had a field, in which all the values were the same. The value was "OldDoc.txt". I wanted to make values unique and for this I wanted to add "ID" before ".txt".

If you need to select the records between 2 dates in MS SQL, where the DateTime column contains both Date and Time, you can use SQL functions for WHERE clause and avoid of adding the time information like '23:59:59.000'. Though you can do it and this way.

Create a console app with .Net Framework 4.7.2 with Dapper and datasource in MSSQL Server or PostgreSQL.

If you create this ancient app, you should only add Dapper and NPGSQL packages from Nuget. The support of MS SQL Server is already built in. The scenario for .Net Framework is from real life, several applications still require this framework.

The task is to build a tree in a table with parentID from bottom to top. It's useful for generating hierarchy strings, lists or any other types. You can also create a storage procedure of this and it will make it easy to use in queries.

To create a full backup of MS SQL Server database with compression use this SQL command:

use YourDataBaseName
go

BACKUP DATABASE YourDataBaseName
TO DISK = 'Z:\BACKUP_FILE_NAME.bak'
WITH COMPRESSION

According to database size and your server capabilities, backup time can vary.

There are at least 3 ways to find out if the column in table exists in MS SQL server. It's a common task for developers or administrators when you need to modify table.

(read more...)

There's a column Photo with type "Image" in MS SQL table (database – MS SQL Server 2008 R2). The task is to get all the records where Photo is empty. Values of empty images is "0x00000000".

(read more...)