Stored Procedure to Get Database Tables Information

As SQL Server DBAs, we always take care of one of the most important things for the business, the data. In some cases, applications can get quite complex, and you end up with a ton of database tables scattered around your SQL Server instance(s). This might lead to a few inconveniences, such as:

  • Knowing how your data behave every day, in terms of growth trends (space and/or amount of rows).
  • Knowing what database tables require (or will require) a particular/different strategy to store the data because it is growing too fast.
  • Knowing which of your database tables take too much space, possibly leading to storage constraints.
CodingSight - Stored Procedure to Get Database Tables Information
Read More

Stored Procedure to Delete Duplicate Records in SQL Table

Sometimes during our run as DBAs, we come up across at least one table that is loaded with duplicate records. Even if the table has a Primary Key (an auto-incremental one in most cases), the rest of the fields might have duplicate values.

However, SQL Server allows for many ways to get rid of those duplicate records (e.g. using CTEs, SQL Rank function, subqueries with Group By, etc.).

CodingSight - Delete Duplicate Records in SQL Table
Read More

How to Create a Table with Multiple Foreign Keys and Not Get Confused

Understanding Table Relations

SQL server adds foreign keys to implement relations between tables in a relational database. A table can have one-to-one, one-to-many, or many-to-many relations with another table, depending upon the type of data you want to store.

A one-to-one relationship is simple and rarely used. Each record in a particular table relates to exactly one record in another table.

CodingSight - How to Create a Table with Multiple Foreign Keys and Not Get Confused
Read More

Moving SQL Server Table to Different Filegroup

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