kveratis

using System;
using System.Data;
using Company.Repositories;
using Company.Repositories.Sql;
using Microsoft.Practices.Unity;

namespace Company
{
    public partial class LeadAdd : WebPage
    {

kveratis

declare @who table(spid int,status varchar(50),login varchar(50),hostname varchar(50),blkby varchar(50),dbname varchar(50),command varchar(500), cputime int,diskio int, lastbatch varchar(50),programname varchar(255),spid1 int,requestid int)
insert into @who execute sp_who2;

WITH RawJob as
(
  SELECT spid
		,CAST(CONVERT(varbinary, SUBSTRING(programname, 30, 34), 1) as uniqueidentifier) as JobId -- Grab JobId and convert hex to uniqueidentifier
		,CAST(REPLACE(SUBSTRING(programname, 72, 2), ')', '') as INT) as JobStepId -- Grab step id
	FROM @who
	WHERE programname LIKE 'SQLAgent - TSQL%'

kveratis

SELECT mid.database_id
  ,db.name
	,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
	,migs.*
	,mid.database_id
	,mid.[object_id] 
FROM sys.dm_db_missing_index_groups mig 
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle 
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle 

kveratis

--  General Maintenance Notes on Indexes
--  Indexes should be rebuilt when index fragmentation is greater than 30%
--	Indexes should be reorganized when index fragmentation is between 5% and 30%
--	Index rebuilding is much more costly than reorganization.

-- Rebuild
DECLARE @database_id INT
SET @database_id = DB_ID();
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,

kveratis

--Rebuild Index
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

-- Reorganize Index
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REORGANIZE;

kveratis

Backups

Full Backup

DECLARE @BackupLocation NVARCHAR(100);
SET @BackupLocation = 'c:\data\AdventureWorks2008_' +
CONVERT(NVARCHAR(8), GETDATE(), 112) + '.bak';
BACKUP DATABASE AdventureWorks2008
TO DISK = @BackupLocation
WITH INIT;

kveratis

Wildcards

% - A substitute for zero or more characters (can be in the middle) _ - A substitute for exactly on character [charlist] - Any single character in the list [!charlist] - Any single character not in the list

JOIN (INNER JOIN)

Returns rows when there is at least one match in both tables.

kveratis

DROP TABLE [dbo].[DimDate]
GO
CREATE TABLE [dbo].[DimDate] (
	[DateKey] [int] NOT NULL PRIMARY KEY,
	[FullDate] [date] NOT NULL,
	[Year] [smallint] NOT NULL,
	[Quarter] [tinyint] NOT NULL,
	[MonthOfYear] [tinyint] NOT NULL,
	[MonthName] [varchar](10) NOT NULL,
	[WeekOfYear] [tinyint] NOT NULL,

kveratis

DROP INDEX IF EXISTS IDX_DimTimeZone_Conversion;
DROP TABLE IF EXISTS DimTimeZone;
CREATE TABLE DimTimeZone(
  TimeZoneKey INT PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Description VARCHAR(150) NOT NULL,
	StandardConversion INT NOT  NULL DEFAULT 0,
	DSTConversion INT NOT NULL DEFAULT 0,
	ObservesDST TINYINT NOT NULL DEFAULT 0
);

kveratis

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".generated.cs" #>
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

#region TextBlock

public partial class TextBlock : List<string>