Search

Advertisements

TSQL Script to Find the Names of Stored Procedures that Use XQUERY

Below scripts can be used to find the names of Stored Procedures that use XQUERY :

SQL Server - Policy based management - System Views

Below query will provide the Created policies , Conditions imposed and Policy evaluation history details :


USE msdb
GO

SQL Server - Policy based management - Enforcing naming convention for Database and its objects

This post is about enforcing naming convention for database and its objects through Policy based management in SQL Server .

Naming convention can be enforced for below list of objects :

Note : But Policy evaluation mode differs for each object .

APPLICATION ROLE
ASYMMETRIC KEY
CERTIFICATE
DATABASE ROLE
DEFAULT
INDEX
RULE
SCHEMA
SQL ASSEMBLY
STORED PROCEDURE
SYMMETRIC KEY
SYNONYM
TABLE
TRIGGER
USER
USER DEFINED FUNCTION
USER DEFINED TYPE
VIEW
XML SCHEMA COLLECTION


In this post , let us see an example for enforcing naming convention while creating Stored procedure .

Excel - Transpose Rows and Columns

In Excel , we can transpose rows into columns and vice versa  .

Copy the rows or columns to be transposed , while pasting - > Paste Special ...

Check the option Transpose as shown below :

SQL Server - Capturing Missing Join Predicate for queries using Extended Events

missing_join_predicate , one of the event  -  " Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row " .


SELECT * FROM sys.dm_xe_objects WHERE name = 'missing_join_predicate'

--Create Session to track missing_join_predicate event with below mentioned Actions


TSQL Script to Generate Create ColumnStore Index Script

 This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .

SET CONCAT_NULL_YIELDS_NULL OFF;

SQL Server Data Quality Services

This article is about cleansing and formatting data using SQL Server Data Quality Services .

In this post , I am going to show on how to cleanse & format data in the columns of SQL Server table . To do this , first we have to create Knowledge base and then Data quality project .

Creating Knowledge base is nothing but giving hint of source data to DQS and then setting rules on how data should be formatted by creating Domains for each columns .

After creating Knowledge base , create Data quality project making use of knowledge base on our source data and then after deciding on the recommendations provided by DQS , we can export the cleansed (corrected) & formatted data to new table .


Note : click on images to get enlarged view

Advertisements