It is really easy. You just need to add the object name as a parameter to “SYS.SP_MS_MARKSYSTEMOBJECT”. In the following code we are creating a system table named“TestSysTable”:
IF OBJECT_ID(N’TestSysTable’) IS NULL
begin
CREATE TABLE dbo.TestSysTable
(
C1 int NOT NULL,
C2 int NOT NULL,
C3 int PRIMARY KEY IDENTITY
)
EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘TestSysTable’
End
All done!
Enjoy!
If you are working in a company that your managers are getting database documentation seriously, thumbs up! One of the ways to write useful documentation that is really effective is using SQL Server extended properties.
We can add extended properties to almost all SQL Server objects by right clicking on the object-> select properties-> select “Extended Properties” and add new properties to the object.
So, after adding the new properties we might need to query those properties in the future.
Executing the following T-SQL script retrieves what we need:
select O.name ObjectName, e.name PropertyName, value
from sys.extended_properties e inner join sys.objects o on e.major_id=o.object_id
All done!
In this short post I’ll show you how to create database schema using dynamic SQL.
It’s easy, just take a look at the following code:
declare @SchemaName varchar(max)
set @SchemaName = ‘YOUR_SCHEMA_NAME’
if not exists (select 0 from sys.schemas where name=@SchemaName)
exec(‘create schema [‘+@SchemaName+‘]’)
go
Enjoy!
The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. Columnstore indexing is officially announced in SQL Server 2012. It is working based on xVelocity memory optimised technology and it improves data warehouse query performance significantly. Due to the fact that data warehousing, decision support systems and business intelligence applications are growing very quickly, we need to be able to read and process very large data sets quickly and accurately into useful information and knowledge. Columnstore index technology is especially appropriate for data warehousing data sets. It improves the common data warehousing queries’ performance significantly.
Continue reading “Digging into SQL Server 2012 columnstore index”
