SQL query

T-SQL tips: Replace function example

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”. Read more»

SQL and databases

Select the rows between 2 dates in MS SQL table

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. Read more»

SQL and databases

SQL recursive query from bottom to top

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. Read more»

SQL and databases

3 ways to find out if the column in table exists

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»

SQL and databases

Get records with empty binary items

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»

SQL and databases

Count rows in result of SQL query

To receive quantity of rows of the returned records of SQL query, you should use function @@ROWCOUNT. Function @@ROWCOUNT returns the quantity of rows, which were used in a query. Read more»

SQL and databases

Saving changes is not permitted in MS SQL Management studio

Figure 1. Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created

When you modify columns of a table in MS SQL management studio, you got the message “Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created … ” (Figure 1). The reason of this is re-creating of table. It’s really important for productive environment but not […] Read more»

SQL and databases

How to select all duplicates from Transact-SQL table on namesakes example

It’s a little tutorial about how to get all duplicate records on namesakes example from your database table. For example, we have a table with columns Id, FullName, Birthdate and Phone. Using ‘GROUP BY’ and ‘HAVING’ we can select all namesakes.  Read more»

SQL and databases

Popular Posts