SQL UNION Cheat Sheet with 10 Easy and Useful Tips

Total: 2 Average: 5

Having a hard time with SQL UNION? It happens if the results you combined put your SQL Server into a standstill. Or a report that’s been working before pops up a box with a red X icon. An “Operand type clash” error occurs pointing to a line with UNION. The “fire” starts. Sounds familiar?

Whether you’ve been using SQL UNION for a while or just start it out, a cheat sheet or a concise set of notes won’t hurt. This is what you are going to get today in this post. This list offers 10 useful tips for both newbies and veterans. Also, there will be examples and some advanced discussions.

Read More

Moving SQL Server Table to Different Filegroup

Total: 2 Average: 5

Introduction

A table is a logical structure. When you create a table, you typically would not care which drives it sits on at the storage layer. However, if you are a database administrator, this knowledge may become essential if you need to move certain database portions to alternative storage or volume. Then, you might want definite tables to be on a particular volume or set of disks.

Filegroups in SQL Server offer that abstraction layer allowing us to control the physical location of our logical structures – tables, indexes, etc.

Read More

Do You Make These Mistakes When Using SQL CURSOR?

Total: 1 Average: 5

For some people, it’s the wrong question. SQL CURSOR IS the mistake. The devil is in the details! You can read all sorts of blasphemy in the entire SQL blogosphere in the name of SQL CURSOR.

If you feel the same way, what made you come to this conclusion?

If it’s from a trusted friend and colleague, I can’t blame you. It happens. Sometimes a lot. But if someone convinced you with proof, that’s a different story.

We haven’t met before. You don’t know me as a friend. But I hope that I can explain it with examples and convince you that SQL CURSOR has its place. It’s not much, but that small place in our code has rules.

Read More

Virtual Columns and Functional Indexes

Total: 1 Average: 5

Much too often, we see poorly written complex SQL queries running against the database tables. Such queries may take a very short or a very long time to execute, but they consume a huge amount of CPU and other resources. Nevertheless, in many cases, complex queries provide valuable information to the application/person. Therefore, it brings useful assets in all varieties of applications.

Read More

Understanding SQL Server ALTER TABLE ADD COLUMN Statement

Total: 1 Average: 5

This article describes the SQL Server ALTER TABLE ADD COLUMN statement. The purpose of this statement is to add a new column to an existing table. Also, the article will clarify the following use cases:

  1. Add multiple columns.
  2. Add a new primary key column.
  3. Add a new foreign key column.
  4. Add new columns that have computed columns.
  5. Add a new column that has CHECK and UNIQUE constraints.
Read More

Using INNER JOIN to Combine SQL Server Tables and Display Them in ASP.NET Webforms

Total: 1 Average: 5

This article describes the ways of using an INNER JOIN to combine results from two tables in the SQL Server database and display them in an HTML5 table inside an ASP.NET webform. Additionally, it touches on the question of using CSS3 (cascading style sheets) to apply a style to the HTML5 table in an ASP.NET webform.

Let’s begin.

Read More

SQL DROP TABLE Statement and Various Use Cases

Total: 1 Average: 5

The SQL DROP TABLE statement serves to drop the table from the database. It removes the table and its data and indexes associated with it. The statement is irreversible. Thus, you can restore the table only by restoring the backup.

This article covers the following cases:

  1. Delete one or multiple tables.
  2. Delete the table used in a View.
  3. Delete the table having a foreign key.
  4. Delete the table having clustered and non-clustered indexes.
  5. Delete the table from the replication setup.
Read More

Fundamentals of Managing Datafiles in SQL Server

Total: 1 Average: 5

Introduction

Datafiles are physical objects that constitute the most important part of the database system since they contain actual data. You can think of a database as a collection of data files. An instance gives you the means of mounting and accessing such files.

Here, managing datafiles is understanding how to monitor and resize existing datafiles and how to add or remove the data files from a database.

Read More