Solving tech problems

How to delete duplicated records in MS SQL Server?

Databases, Programming

First, create one demo table Let’s create one demo table first. It will be called Person and will hold some fictive person data. Now, fill the data Insert some fictive person data and do some duplicated records that have the same values for fields  firstname, lastname and email. Now the magic Use this subquery trick to extract duplicate records and then delete them. Notice the use of PARTITTION and WITH Transact-SQL keywords. All records that have RowNumber > 1 are deleted.

18.08.2014

Using SQL Server APPLY command in queries

Databases

APPLY clause in Transact-SQL is interesting because it can be user in the following scenario: For example, you might create a query that returns a list of payments (in this case last 2 payment amounts and dates) for each subscriber inside your SaaS product. You get something like this 958 Usain Jordan 2014-06-01 299.00 958 Usain Jordan 2014-07-18 458.00 110 Seagal KungFu 2014-01-12 15.16 110 Seagal KungFu 2014-03-06 17.45

08.07.2014

Using SQL Server PIVOT feature to transform rows into columns

Databases, Programming

PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level. Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount. What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query: And we finally get

28.05.2014

How to get list of sequential dates in MS SQL Transact SQL and cursors alternative

Databases

Let’s imagine that we have table where we store some service prices and that this price can vary regarding period of the year. You would then have something like this in your table: $50 from January 1st to May 31th $70 from June 1st to October 31th $60 from November 1st to December 31th What we want is to get all daily prices for given date region. So, if we give range from May 15th to 15th June we want to get this: May 15th $50 May 16th $50 May…read more

14.03.2014
20.11.2013

How to create Linked MS SQL Server in Management Studio?

Databases

Creating MS SQL Linked Server means that you can access your remote MS SQL database (on other server) like it is local. Syntax to access some data is as simple as that: [linked server name].[database name].dbo.[table name] Official Microsoft documentation is really sometimes hard to use. A lot of information but none practical example of usage. Google search also didn’t helped much on the first try. After few tries I finally found out how you can add MS SQL Linked server to your existing database server. So, you need first…read more

MongoDB vs MS SQL Server Comparison

Databases

We are using both databases for our projects and we can finalize some thoughts on both of them… Mongo Facts Free – official website is here and you can download it here Because it is using NoSQL concept it is really extremely fast when it comes to the inserts into database. It is almost fast as writing directly into the disk. It is at least 10 times faster when doing inserts. Because is is using NoSQL there is no scheme, you just store your object into database (JSON serialization is…read more

31.05.2013