SQL Server Articles, Tutorials, News, Tips & Tricks
Microsoft has announced the release of the Release to Manufacturing (RTM) version of SQL Server 2016 on June 1, 2016. The good news is that you can download SQL Server 2016 Developer Edition for Free. Different editions of SQL Server 2016 which are available are Enterprise, Standard, Web, Developer and Express Edition.
SQL Server 2016 introduces many new T-SQL features for Developers and Database Administrators. In this tutorial we will take a look at some of the new T-SQL enhancements in SQL Server 2016 along with relevant examples.
Starting SQL Server 2016, Microsoft has introduced a new clause TRUNCATE TABLE WITH PARTITIONS which can be used to truncate all rows available within a partition.
SQL Server 2016 introduces a new DROP IF EXISTS statement to DROP objects such as tables, columns, indexes, stored procedures, schemas, triggers and user-defined functions. DROP IF EXISTS statement can be used to check whether the object exists before it is dropped or removed from the database.
SQL Server 2016 introduces an interesting T-SQL enhancement to improve performance and reduce downtime ALTER TABLE WITH (ONLINE = ON | OFF). This statement will help you alter data types, change column/table collation values, change nullability settings, change column length, and many other options while the table remains online for user connectivity.
MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP T-SQL Enhancement in SQL Server 2016
SQL Server 2016 brings MAXDOP settings, for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP command. Database Administrators can leverage MAXDOP settings to above mentioned DBCC commands to achieve Max Degree of Parallelism.
SERVERPROPERTY is an inbuilt function which gives you information about the server instance.
There can be certain scenarios when SQL Server is not starting because of configuration problems. During such scenarios one need to start an instance of SQL Server by using minimal configuration. In this article we will take a look at steps which one needs to follow to start SQL Server with Minimal Configuration to troubleshoot SQL Server configuration issues.
There can be certain scenarios when one needs to connect to an SQL Server Instance in a Single User Mode by using the Startup Option -m. For example, the need could be to recover a damaged system database such as Master, Model, MSDB etc or you may want to change the server configuration options. In this article we will take a look at steps which one needs to follow to Start SQL Server in Single User Mode.
SQL Server Database Engine defines the size of virtual log files dynamically while creating or extending transactional log files in SQL Server. Virtual log files will not have a fixed size and one cannot predict the number of VLFs within the transactional log file. Internally, SQL Server will always try to maintain a smaller number of VLFs. However, there is no way for a database administrator to configure or set a fixed size for VLFs in SQL Server.
In this tip, we will take a look at few of the very importance customizations which a DBA must do for a Model System Database so that all the newly created databases on the SQL Server Instance have better configuration that the default database settings.
SQL Server Database Engine supports different startup options for SQL Server Database Engine Service. A database administrator can set the startup options very easily using SQL Server Configuration Manager. In this article we will explore startup options available in SQL Server Database Engine in detail.
How to Start SQL Server without TempDB Database or How to Start SQL Server with Minimal Configuration
This article demonstrates the steps that one needs to follow to start SQL Server without TempDB database or with minimal configuration.
There are often times when as a DBA you need to quickly retrieve SQL Server Configuration Details for a SQL Server Instance. In this article we will explore different ways to retrieve SQL Server configuration details using TSQL queries.
In this article we will take a look at the steps which you can follow to quickly identify the user who deleted the user database in SQL Server.
In this tip we will take a look at different ways to find the location of default trace file in SQL Server and how to enable or disable default trace file in SQL Server.
This article demonstrates how to enable an index in SQL Server. It is a very useful feature which will help you enable an index which was disabled earlier to check whether the index was really useful or not without actually dropping the index.
Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. Based on the choice of Recovery Model, SQL Server decides which data it needs to retain within SQL Server transactional logs and for the time period.
Full database backup represent the database at the time when the backup has finished. However, as the database size increases the full database backup takes more time to finish and it will also require more storage space. Hence for larger databases one must supplement a full database backup with a series of differential database backups and even transactional log backups if the database is in FULL or BULK-LOGGED recovery model. Transactional log backup is not allowed when the database is in SIMPLE recovery model this is by design.
Using FILE backup one can backup SQL Server Data File individually. While backing up the database you can specify whole of FILEGROUP instead of specifying each database file individually within the FILEGROUP. In case if there is any file within the FILEGROUP OFFLINE may be because of file being restored then the whole FILEGROUP will be OFFLINE and cannot be backup up. A FILE backup can also serve as the differential base for differential file backups. For more information, see Differential Backups in SQL Server.
|Latest SQL Server Tips|