Running DBCC Shrink commands is quite a controversial issue across the SQL Server community. In this article, we will review details about this command and provide a brief overview of its use and also warn you about the risks of running this command. As DBAs, a number of databases were handed over to from other teams or vendors, and it is not always we get to manage the databases which we created. As DBAs, whenever we are involved in migrations or new projects, we need to ensure that we carefully plan a smooth transition of the database to production and regular use. It is at this stage that we need to factor in the size of the database. Can you imagine, you set up a database application without considering the growth forecast for the first year or so. How about you create a SQL Server database with size so small that it needs to grow every other day raising capacity disk alerts in the middle of the night? It may sound silly, but in reality, the truth is this happens, and this sometimes may not be in your control.
Category: SQL Server
Automating SQL Database Maintenance Tasks using SQLCMD
This article is about automating SQL database maintenance tasks through SQLCMD utility which lets you run T-SQL commands directly from the command prompt without using SSMS (SQL Server Management Studio).
Typically, automating database tasks requires SSMS (SQL Server Management Studio) for scheduling jobs that run these tasks, but in this article, an alternative approach is used to automate database tasks without having to use the much-needed SSMS.
The SQLCMD utility can be a real time saver for database developers and DBAs since they can immediately run the necessary SQL scripts from the command line, and automating database maintenance tasks with the SQLCMD utility is a plus.
The Difference Between Primary Key And Unique Key
“What is the difference between a primary key constraint and a unique key constraint?”
This is probably the most frequently-asked job interview question for database developers. In this article, we will try to answer it.
Let’s start by looking at what primary and unique keys are, as well as at their similarities.
Exporting Data to Flat File with BCP Utility and Importing data with Bulk Insert
The BCP (Bulk Copy Program) utility in SQL Server allows database administrators to import data into a table and export data from a table into a flat file. The BCP utility also supports various features that facilitate the process of exporting and importing of the bulk data.
Now let’s get started with a business scenario. Read More
DBCC CheckDB Overview
Introduction
Regular database maintenance is an important part of a Database Administrator’s job which helps to ensure that critically important systems are running as per normal. One of the easiest ways to accomplish this will be to automate tasks related to DBCC CheckDB. No matter what version of SQL Server you are running, there will never be a database that requires no maintenance. You will have to plan the maintenance to occur regularly so that you can cover your back especially at the time of a real disaster scenario.
Replacing SQL Cursors with Alternatives to Avoid Performance Issues
In this article, we’ll look at some alternatives to using SQL cursors which can help to avoid performance issues caused by using cursors.
Before discussing the alternatives, let’s review the general concept of SQL cursors.
Quick Overview of SQL Cursors
SQL cursors are primarily used where set-based operations are not applicable and you are required to access data and perform operations one row at a time rather than applying a single set-based operation to an entire object (such as a table or a set of tables).
SQL Server CRUD Operations – Part 2
CRUD word is an acronym of the following essential database functions.
In the first part of CRUD article series, we mentioned the concept of the CRUD operations and then discussed the INSERT operation with examples. In this part, we will continue exploring the CRUD operations and will focus on the read, update and delete operation details of CRUD. Read More
Advanced SQL: CROSS APPLY and OUTER APPLY
In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used.
In particular, we will learn:
- the difference between CROSS APPLY and the JOIN clause
- how to join the output of SQL queries with table-evaluated functions
- how to identify performance issues by querying dynamic management views and dynamic management functions.
How to use SQL Cursors for Special Purposes
This article describes SQL cursors and how to use them for some special purposes. It highlights the importance of SQL cursors along with their downsides.
It is not always the case that you use SQL cursor in database programming, but their conceptual understanding and learning how to use them helps a lot to understand how to perform exceptional tasks in T-SQL programming.
SQL Cursors Overview
Let us go through some basics of SQL cursors if you are not familiar with them.
Concatenation in Transact-SQL
Introduction
Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.