I noticed that very few people understand how indexes work in SQL Server, especially Included Columns. Nevertheless, indexes are the great way to optimize queries. At first, I also did not get the idea of the included columns, but my experiments showed that they are very useful. (more…)
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.
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.
In this article, we are going to touch upon the topic of performance of table variables. In SQL Server, we can create variables that will operate as complete tables. Perhaps, other databases have the same capabilities, however, I used such variables only in MS SQL Server.
The pivot operator in SQL Server converts each row in the aggregated result set into corresponding columns in the output set. The pivot operator is particularly useful in writing cross-tabulation queries.
Let’s take a look at how it works in practice.
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.
Using one SQL Server instance for many projects is not unusual. However, defining the most active project may be quite difficult. Today, I would like to share several ways to analyze the activity of each particular database.
We continue to analyze what is happening on our MS SQL Server. In this article, we are going to explore how to retrieve information about user performance: who makes what, and how much resources are consumed.
I think the second part will be interesting for both database administrators and developers who need to understand what is wrong with the requests on the production server that used to work fine on the test server.
What could be simpler than to get data from an Excel table in SQL Server?
There are many ways to accomplish this task. You can utilize Integration Services (former DTS) or import and export wizard, which is the same under the hood. Another way is to create a simple ADO.NET app. You can use the Linked Server mechanism, which allows you to see any ODBC / OLE DB-reachable object in the form of a table (a collection of tables) or the result of an ad hoc query.
In this article, we are going to review DBAs mistakes, the consequences of which were quite perceptible and which I had to deal with.
The purpose of the article is to prevent users from repeating these mistakes. Sometimes, a bad experience is even more valuable than a positive one.