One of the common tasks performed when retrieving data from a SQL Server database is returning the result as a comma-delimited output instead of a result set. This task can be performed by using a CURSOR selecting the column to be concatenated together. Each row returned by the CURSOR is then concatenated together into a variable separating each one by a comma. Here’s how the script will look like using the table in the Northwind database. A sample…
Dynamic management views Dynamic management views, first introduced with SQL Server 2005, provide information about server and database state. These views are useful for monitoring overall SQL Server health, identifying the root cause of SQL Server performance bottlenecks, and tuning SQL Server instance or database performance. The following is a list of dynamic management views available in SQL Server 20008R2, SQL Server 2012 and SQL Server 2014. You can use these views to obtain SQL Server memory usage information;…
“ I have been asked several times for advice on how to effectively maintain a production database. Sometimes the questions come from none-DBAs who are implementing new solutions and want help fine-tuning maintenance practices to fit their new databases’ characteristics. As with the majority of tasks and procedures in the IT world, there isn’t an easy one-size-fits-all solution for effective database maintenance, but there are some key areas that nearly always need to be addressed. My top five areas of…
This article explains the base functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL. Samples script: Sample Results: Variable Data Results ——————– HELLO WORLD RIGHT Results ————- RLD LEFT Results ———— HEL CHARINDEX Results —————– 6 SUBSTRING Results —————– LO WO Combined Results ——————– HELLO
Find how many tables in database AdventureWorks have column name like ‘EmployeeID’.
Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
SQL joins are used to combine rows from two or more tables. SQL JOIN An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. Let’s look at a selection from the “Orders” table: OrderID CustomerID OrderDate 10308 2 1996-09-18 10309 37…
Using the WHERE clause to return on the records in the Employee table where the employees are a manager of some type. This example uses the % character for any number of characters. Using the LIKE keyword to return records from the Person table where users’ first names end in “ary”. This example uses the _ characters to represent a single character. Using the LIKE keyword to return records from the Person table where users’ first names end in…
Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.
A very common question we developer often encounters is what is the current Timezone of the server where SQL Server is installed. It is very easy to get current Timezone of the server where SQL Server is installed. Here is the simple script for the same.
