Showing posts with label SQLServer. Show all posts
Showing posts with label SQLServer. Show all posts

Wednesday, February 19, 2020

SQL Server Window Functions

Image result for window images

Okay, so if you got this far then I assume you know that the picture above is not exactly what you're seeking.
Sometimes when you find something that works for you, it's better to just link to the source, like I've done here. The following is a  straight forward and easy to follow explanation of the Window Functions available in SQLServer:


https://www.sqlservertutorial.net/sql-server-window-functions/

Monday, December 28, 2009

QUOTENAME : A little known util function for "bracketing" a value

DECLARE @TEXT VARCHAR(50)
DECLARE @NUMBER INT
DECLARE @QUOTE CHAR(1)

SET @QUOTE = '"'
SET @TEXT = 'hello'
SET @NUMBER = 13

--bracketed with default brackets
SELECT  QUOTENAME(@TEXT), len(@TEXT), len(quotename(@TEXT))
--bracketed with default brackets
--note that an implicit conversion occurs)
SELECT  QUOTENAME(@NUMBER)
--bracketed with a double quote
SELECT  QUOTENAME(@TEXT, @QUOTE)

Friday, December 11, 2009

Find the most recently modified table constraints

USE somedatabasename
GO

SELECT DISTINCT CONSTRAINT_NAME

                                 ,modify_date
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT OUTER JOIN sys.all_objects
ON CONSTRAINT_NAME = name
WHERE TABLE_CATALOG = N'somedatabasename'
ORDER BY modify_date DESC

--complements to Igor Malkiman of Qwest Communications for this code

Friday, April 10, 2009

Job Notification Random Features

If you've worked with job notifications in the past then you probably already know this, but I thought it was worth discussing. Any time you change the name of the operator you want to notify, the event that the operator is notified upon defaults back to 'On Success'.

There are three different events that will generate notification from the job properties:
  • When the Job Completes - generates whether the job fails or completes
  • When the job Fails - generates notification only upon failure
  • When the job Succeeds - generates only upon job success

The problem comes in that intermittently when you change the operator to be notified, the event will default back to 'When the job Fails'. This is a great problem in that you would not be notified in the event of job success.

Monday, April 6, 2009

Enable Database Mail Alerts In SQL2008

While setting up our new SQL 2008 Boxes, I came across the interesting task of configuring SQL Mail. I mean, it would be nice to get an email when your job fails or logfiles grow too big, right? So I went to the web and started looking for articles describing the task.

The articles very nicely describe how to set up mail profiles and operators on your SQL Server. There is lots of material there, so I won't bother to add non-original content. But they do leave out a critical piece, which is to configure SQL Agent to use the profile you have defined. This is funny because my Systems Admin and I were trying to figure out how the two were tied together. The answer is on the Properties Tab of your SQL Agent. Just navigate to SQL Agent, Properties and you should see something like the below. Once you configure your profiles, set up your operators and fill in the below box, you should be good to go.