Sometimes when you run a query in SQL Server, you might want to know what the underlying data type of each column is, its precision, length, whether or not its nullable, etc.
If you’re querying just one table, you can get this sort of data with procedures such as sp_columns. But if your query runs across many tables, this could get unwieldy very quickly.
Fortunately there are several ways you can go about getting such metadata for a result set in SQL Server.
Continue readingWhen you compress a partitioned table in SQL Server, you can compress all partitions, some, or just one partition.
To do this, use the REBUILD PARTITION syntax within the ALTER TABLE statement.
When doing this, you can rebuild just the specified partition or all partitions. Alternatively, you can rebuild all partitions, while compressing only a specific partition or list of partitions.
Continue readingWhen you create a table in SQL Server, you have the option of using data compression.
Data compression helps reduce the size of the database. It can also help improve performance of I/O intensive workloads due to the data being stored in fewer pages, therefore reducing the number of pages that queries need to read from disk.
To do this, use the DATA_COMPRESSION option when creating the table.
You can use the ALTER TABLE statement to enable compression on an existing table in SQL Server.
To do this, you need to use the REBUILD WITH option, while specifying your desired compression type.
SQL Server has a system stored procedure called sp_estimate_data_compression_savings, which allows you to check an object’s size and its estimated size with various levels of compression.
If the object is already compressed, you can use this procedure to estimate its size when recompressed.
Objects can be compressed by using row, page, columnstore or columnstore archive compression.
Compression can be evaluated for whole tables or parts of tables. This includes heaps, clustered indexes, nonclustered indexes, columnstore indexes, indexed views, and table and index partitions.
Continue readingSQL Server’s Database Mail has a stored procedure called sp_send_dbmail that you can use to send emails from SQL Server.
You can use this stored procedure to send query results formatted into an HTML table.
Continue readingWhen you use the sp_send_dbmail stored procedure to email the results of a query, the column headers are included by default.
You can include or exclude the column headers with the @query_result_header argument. To remove the column headers, use @query_result_header = 0.
In SQL Server, you can create DML triggers that execute code only when a specific column is updated.
The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated.
You can do this by using the UPDATE() function inside your trigger. This function accepts the column name as its argument. It returns a boolean.
SQL Server has a database mail option that you can use to send mail from the database server.
For example, you can get notifications when SQL Server Agent jobs finish running or fail, or when there’s a high-severity error, etc.
Continue readingIf you find yourself needing to send an email automatically upon certain events occurring in SQL Server, you can do this via a trigger.
For example, you could automatically send an email when somebody deletes or updates a record from a table, etc.
To do this, you need to create a trigger that includes code for sending the email upon the required event.
Continue reading