SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.
Read more »
Intended audience
This article is intended for application developers and database administrators who plan to develop, deploy, and/or assess solutions for Microsoft SQL Server on a Microsoft Windows platform.
Read more »
In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.
Read more »
A while ago I discovered that you can conveniently connect to a local instance of SQL Server by typing one of the following names:
Read more »
Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.
Read more »
As we know there are two types of authentication available in SQL Server Windows authentication and SQL authentication. In Windows authentication we use Active directory authentication to connect with SQL Server which makes the most secure authentication method as it can have complexity, group policy configured at AD level applied to all domain servers while in SQL Authentication SQL users are created inside SQL and provided required permissions. The Permissions includes server wide and database wide. The logins can have certain permissions at the database level might be read or write etc.
Read more »
More often than not (at least in places I’ve worked at), the job of installing, configuring and subsequent administration of Team Foundation Server (TFS) is performed by different individuals to those administering enterprise applications such as Windows Server, SQL Server, SharePoint etc. This is because TFS, unlike other enterprise applications, often has to be administered from both the server side (i.e. TFS Administration Console) as well as the client side (i.e. using Team Explorer in Visual Studio) – it is thus not surprising that a TFS administrators may once had been a developer. The benefit of having a TFS administrator with a software development experience is that it may be easy to get developer-buy-in into the tool. However, the disadvantage to this is that developers usually get accustomed to doing things in a certain way – like connecting to SQL Server using (local) convenience names.
Read more »
One of the more difficult challenges we face when analyzing data is to effectively identify and report on boundaries. Data sets can contain any number of significant starting and stopping points that may indicate significant events, such as missing data, important business events, or actionable changes in usage. Regardless of the use case, knowing how to quickly locate and manage data boundaries is extremely useful. Knowing how to design solutions that can effectively avoid these scenarios can also be helpful in the long run.
Read more »
Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.
In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.
Read more »
When talking about performance and scalability inside SQL Server, I don’t see anyone missing out on the fact to mention how locks get involved. I often see DBA’s complain to developers that their code is not scalable and they are experiencing heavy locks in the system because of the way the code has been written. The more I work with SQL Server, the more I start to understand some of these nuances.
Read more »
We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.
When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.
Read more »
Working as a DBA, the simplest task requested from you is installing SQL Server. As a start you will check the server’s hardware and software specs to make sure that you can start the installation, checking which services will be installed and the security authentication type required. After this pre-installation plan, you will start the normal installation process.
After completing the installation, you need to apply specific configurations on the server to make sure that the server can host production databases and the users can connect to it successfully.
In this article, I will describe the common configurations that you should apply after the SQL Server installation.
Read more »
Because databases aren’t always designed efficiently, data is sometimes stored in ways that make sorting and searching extremely difficult. Requests for meaningful, informative reports from such data, however, still must be fulfilled.
Read more »
In Part 1 of this article, we built a case for the benefits of documenting our schema via the use of a stored procedure, as well as the ways this information can be used for DML operations on a relational database. Below, we will continue where we left off by speeding up our SQL queries and then putting everything together so that we can demo a usable script that will hopefully make your life easier!
Read more »
In a typical OLTP environment, we want to maintain an acceptable level of data integrity. The easiest way to do this is through the use of foreign keys, which ensure that the values for a given column will always match that of a primary key in another table.
Over time, as the number of tables, columns, and foreign keys increase, the structure of that database can become unwieldy. A single table could easily link to thirty others, a table could have a parent-child relationship with itself, or a circular relationship could occur between a set of many tables.
Read more »
There are many new features in SQL Server 2016, but the one we will focus on in this post is:
One of the many new features in SQL server 2016 is the built-in support for temporal data tables. This is a feature that many developers have been urging to have as a “out of the box feature” – and therefore many have developed their own ways of implementing the capability to store information about how data looked in a certain period of time.
Read more »
This article is divided in three parts, if you started here, check the first and second part to find the previous steps.
Now we need to define if the instance will work in “Windows Authentication Mode” or “Mixed Mode”. I usually choose “Mixed Mode” because there’s always an application that don’t work with domain accounts. By choosing this option you need to specify the password for the famous “sa” login. Notice that you can change the authentication mode anytime! You also need to add accounts to receive the Sysadmin privilege, this is very important… Even more if you choose the “Windows Authentication Mode”.
Read more »
This article is divided in three parts, if you started here, check the first part to find the previous steps.
Read more »
Do you want to install a SQL Server 2014 clustered instance? Not sure how to proceed? What are the requisites? This series of articles will show this, step-by-step.
In this article I’m going to explain, step-by-step, how to install clustered SQL Server instance. The SQL Server version that we will be using is the 2014, or SQL Server 12, the latest SQL Server version, so far…
Read more »
Do you know that you can store system and user databases in a fileshare? Even for a clustered instance this is an option now. In this article we will check how to implement this solution.
On the latest versions of SQL Server new possibilities to assign storage to a clustered instance became available. In this article, we will talk about one of these options, which is also an option for standalone instances by the way: The network-attached storage – SMB Fileshare.
Read more »
In this new article, we will talk about the system views/tables/functions and how to solve common questions using them.
The system views are views that contain internal information about a Database.
The master database for example contains information about the SQL Server itself, while the msdb database contain information about the SQL Server agent and each database has its own system views/tables.
In this article we will show how to get the list of tables, views, stored procedures, how to get a list of tables of all the databases, how to find a table in multiple datatabases, how to get the list of users, logins, mapped logins, how to detect a fragmentation in a table and more.
Read more »
MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.
The Database Snapshot can be created multiple times and it can only be created using the T-SQL.
In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot. Read more »
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy