Methods to Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process.

(more…)

Resource Release in .NET Applications

In C#, there is one huge benefit, which is a drawback at the same time – an automatic garbage collection. With traditional desktop applications, this is great when a platform cleans up the whole garbage and releases memory. However, it is not always good for Web.

When we develop a Web application, Web-requests are mainly short; our code must perform small tasks and work very quickly. If we do not help a garbage collector, the server resources can exhaust in no time.

(more…)

Setting Database Access Permissions

Server security mainly depends on how correctly you can configure access permissions on objects. Providing a user with excessive permissions may cause many issues. No, a user will not use your errors. Instead, any hacker or I will do this. In this case, you can forget about your tables with data or the whole database.

For some reason, the security of the database is protection from the outside, such as a hacker. However, this happens very seldom. I am a programmer in a big company and an administrator does not even think about protecting the server ports, where everything is open. There is a bunch of databases, programs, and even an FTP server on a single server and it has never been hacked over the past 5 years. Fortunately, I persuaded the administrator to deploy the WEB server on a separate hardware. Otherwise, if someone knew the IP address of our main server, any slacker would be able to hack it. Neither the database nor Windows has been patched for several years.

(more…)

Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET

Introduction

Often, it is necessary to send a query to all databases of all specified servers. Many DML-queries can be created with built-in tools. However, what about DDL-queries?

In this article, we are going to explore an example of implementing the application that sends a query to all databases of all specified servers, using MS SQL Server and C#.NET.

(more…)

Using Expressions to Filter Data of Database

I would like to start with a description of the problem that I encountered. There are entities in the database that need to be displayed as tables on the UI. The Entity Framework is used to access the database. There are filters for these table columns.

It is necessary to write a code to filter entities by parameters.

For example, there are two entities: User and Product.

(more…)