Tag: how to

2 Ways to Create a Database on a Linked Server using T-SQL

Posted on by Ian

One way of creating a database on a linked server, is to simply jump over to that server and create it locally.

But you’d probably feel a bit cheated if I included that as one of the “2 ways” to create a database on a linked server.

Also, while that option is fine if you’re able and willing to do it, this article shows you how to do it remotely using T-SQL, without having to jump over to the local server. Plus you might find this technique quicker than jumping over to the other server.

Both of the “2 ways” involve the EXECUTE statement (which can also be shortened to EXEC). We can use this statement to execute code on the linked server, and that includes creating a database on it.

Continue reading →

2 Ways to Create a Table on a Linked Server using T-SQL

Posted on by Ian

If you need to create a table on a linked server, you can do this either directly on the remote server, or you can do it by running a script from your local server.

Here are two ways of using T-SQL to create a table on a linked server.

Both methods use the EXECUTE/EXEC statement. But each uses a different approach.

Continue reading →

3 Ways to Get the Schema of a Result Set in SQL Server

Posted on by Ian

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 reading →

Compress a Specific Partition within a Table in SQL Server (T-SQL)

Posted on by Ian

When 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 reading →

Create a Table with Compression in SQL Server (T-SQL)

Posted on by Ian

When 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.

Continue reading →

Estimate Data Compression Savings in SQL Server

Posted on by Ian

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 reading →

How to Execute a Trigger Only When a Specific Column is Updated (SQL Server)

Posted on by Ian

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.

Continue reading →