Update Not Found Then Insert

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

Stored Prcocedure

 USE [DeviceDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_RptNewAccVelocity]

@SubscriberId int,

@ReportLength int = null,

@Order char(30) = null

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @SQL_STATEMENT nvarchar(3000);

SET @SQL_STATEMENT = 'SELECT c.CreatedDate AS ''AccCreatedDate'', b.AccountId, c.AccountCode, a.DeviceId FROM dbo.tblTransaction a WITH(NOLOCK) INNER JOIN ( ';

SET @SQL_STATEMENT += 'SELECT MIN(CreatedDate) AS CreatedDate, AccountId FROM dbo. tblTransaction WITH(NOLOCK)';

SET @SQL_STATEMENT += 'WHERE SubscriberId = '+ CAST(@SubscriberId as varchar(20)) + ' ';

   

IF (@ReportLength IS NOT NULL)

SET @SQL_STATEMENT += 'AND CreatedDate >= DATEADD(day, - '+ CAST(@ReportLength as varchar(20))   +', GETUTCDATE()) ';

SET @SQL_STATEMENT += 'GROUP BY AccountId ) b ON a.AccountId = b.AccountId AND a.CreatedDate = b.createdDate ';

SET @SQL_STATEMENT += 'INNER JOIN dbo.tblAccount c WITH(NOLOCK) ON c.AccountId=b.AccountId ';

      

SET @SQL_STATEMENT += 'WHERE a.SubscriberId = '+ CAST(@SubscriberId as varchar(20)) + ' ';

   

IF (@ReportLength IS NOT NULL)

SET @SQL_STATEMENT += 'AND c.CreatedDate >=  DATEADD(day, - '+ CAST(@ReportLength as varchar(20))   +', GETUTCDATE()) ';

IF (@Order IS NOT NULL)

SET @SQL_STATEMENT += 'ORDER BY ' + CAST(@Order as varchar(30)) + ' ';

EXEC sp_executesql @SQL_STATEMENT; // print @SQL_Statement

END

Insert 1M Records by SQL Server

declare @id int

declare @accountID bigint

declare @IP varchar(20)

select @id = 1

while @id <= 1000000

begin

//Insert 1st Table

INSERT INTO tblE2Account values('TestAccount' + CAST(@id AS VARCHAR(10)) , 'TestName',14,1,'E2TestBY',GETDATE(),'E2TestBY',GETDate(), NULL,0,0,'63,119','10.101.1.115',2)

// Take ID 

Select @accountID = Max(E2AccountId) From tblE2Account where CreatedBy='E2TestBY'

   
// Take ID and put in next Insert

INSERT INTO tblE2IP values('999.9.9.'+ CAST(@id AS VARCHAR(10)) , '23.1167,113.25','No Hostname','Kuala Lumpur','Kuala Lumpur','MY','59200','AS38199 Macro Lynx Sdn Bhd, Internet Service Provider, Malaysia',1,1,'E2TestBY',GETDATE(),NULL,NULL)

Select @IP = '999.9.9.' + CAST(@id AS VARCHAR(10))

INSERT INTO tblE2Transaction values(@IP, null, 2,14,@accountID,1,'1010017387,20.00,0','E2TestBY',GETDATE(),NULL,NULL)

select @id = @id + 1

end


//Avarage 30k records int 20 mins

Indexes

• speed up the querying process by providing swift access to rows in the data tables. (same as book index)
• Created on columns of tables or view.
• No need add primary key to index, because SQL Server automatically creates a unique index when you declare the primary key.
• good to use Index at column WHERE and JOIN.
• SQL Server first finds that value in index, then use the index to quickly locate the row of data
• Index not recommend create on large object data type (image,text,varchar(max))
• E.g. search value 123, the search engine will search root level and determine which intermediate level to go, 101-200, then will go to the node level until find 123.
• The leaf node will contain entire row of data or pointer to the row, depending on the index is clustered or nonclustered

Clustered Index

• Store actual data rows at the leaf level of index.
• clustered index is sorted in ascending or descending.
• only one clustered index on a table.
• Query will faster than NON-Clustered.

Nonclustered Index

• Leaf nodes of a non-clustered index only contain the values from the indexed columns and row locators to point the actual row.
• Non-clustered indexes cannot be sorted like clustered index.
• Create more than one non-clustered index per table or view.

Why many indexes will cause slower performance

• Each index is a system-managed table, so every add or update data will update the index, then will cause slower performance of data updates.
• Frequently update key column

EXEC Query

DECLARE @EndDate VARCHAR(10);

DECLARE @SQL VARCHAR(5000);

DECLARE @LastDate VARCHAR(10);

DECLARE @StartDate VARCHAR(10);

DECLARE @StartYear VARCHAR(4);

DECLARE @StartMonth VARCHAR(2);

SET @StartDate = CONVERT(VARCHAR(20),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0),23);

SET @EndDate = CONVERT(VARCHAR(20),DATEADD(MONTH,1,@StartDate),23);

SET @LastDate = CONVERT(VARCHAR(20),DATEADD(Day,-1,@EndDate),23);

SET @StartYear = CONVERT(VARCHAR(4),datepart(yyyy,@StartDate));

SET @StartMonth = CONVERT(VARCHAR(2),datepart(mm,@StartDate));

Set @SQL = 'Select *,date as ''Trans Date'' From DBYear'+ @StartYear  +'.dbo.Table WHERE TransDate BETWEEN '''+ @StartDate + ''' AND '''+ @EndDate + '''; 

exec(@SQL)

select(@SQL)

Queue and Service Broker

Find db Users

SELECT * FROM sys.database_principals

Find Permission 

SELECT * FROM sys.database_permissions WHERE grantee_principal_id = 5

Create Queue and Service Broker

CREATE QUEUE PricesMessage;

CREATE SERVICE PricesNotifications

ON QUEUE PricesMessage

([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

Grant Access For Queue (only run 1 time for all Queue 

GRANT RECEIVE ON QueryNotificationErrorsQueue TO dbadmeen;

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO dbadmeen;

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO dbadmeen;

Grant Access For Queue and ServiceBroker (Run for every queue and service) 

GRANT SEND ON SERVICE::[PricesNotifications] To dbadmeen;

GRANT RECEIVE ON [dbo].[PricesMessage] TO dbadmeen;

-- for user to see the services

GRANT VIEW DEFINITION ON SERVICE::[PricesNotifications] to dbadmeen;

****
Disable Queue & Enabale Queue
alter database dbName set disable_broker with rollback immediate;
alter database dbName set enable_broker with rollback immediate;

Get all the queue in that DB and numbers of message
SELECT sq.name, p.rows 

FROM sys.service_queues sq Join sys.internal_tables it ON sq.object_id = it.parent_id AND it.parent_minor_id = 0 AND it.internal_type = 201 Join sys.indexes as i on i.object_id = it.object_id and i.index_id = 1 Join sys.partitions as p on p.object_id = i.object_id and p.index_id = i.index_id WHERE sq.object_id = it.parent_id AND it.parent_minor_id = 0 AND it.internal_type = 201

See the queue
Select * From sys.dm_qn_subscriptions

Rename Column

Use DBName

go

EXEC sp_rename '.dbo.Table.oldColumnName', 'NewColumnName', 'COLUMN'


-NewColumnName no need Tables or any . []

Update & Delete Statement With Inner join

UPDATE Table1 SET Column1=@Value

FROM Table1 a

INNER JOIN Table2 b ON a.ID = b.ID

WHERE b.ID=1 

-----------------------------------

DELETE Table1

FROM Table1 a

INNER JOIN Table2 b on a.ID = b.ID

WHERE b.ID is not null

------------------------------------

Delete Top Number in Table

DELETE TOP (8) FROM Table1
WHERE ID = 1

Insert Table with Select Statement

2 Method to Insert Table with Select Statement (VALUES no need )

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;


1 as [ColumnName]

*Use this when Primary key is auto generate.

OR

INSERT INTO table2
SELECT * FROM table1;

SQL vs C# DataType

SQL Data Type	.NET Data Type
bit	bool
tinyint	byte
smallint	Int16 or short
int	int or Int32
bigint	long or Int64
decimal	decimal
numeric	decimal
datetime	Datetime
char, nchar	char
varchar, nvarchar, text, ntext	string
float	double
real	single
uniqueidentifier	Guid
Binary, varbinary	byte[]

Add a temproray table in SQL

SELECT *

INTO #TempTable

FROM Student

Way to do SQL without insert DB

DECLARE @NAME VARCHAR(50)

SET @NAME= 'abc01';

SELECT CHARINDEX('abc', @NAME)

SELECT * FROM Student

WHERE CHARINDEX(@NAME, Name) > 0

Clone another set Database

There are 2 method to clone another set Database in SQL Server :

1. Back up and Restore ( Local )

• Back up - Tasks (Backup) >  (Destination) Add , choose file or write file (.bak)
• Restore - Tasks(Restore Database ) >  Device : Add File 

     2.  Script ( Server )

• Tasks > generate script 
•  better dont those db , choose table , no user
•  Set Scripting Option - advanced (schema & data) , index - true

if database is so big , and just want few tables have data.

Import Data, choose source 1st, choose destination 2nd, choose table. It will create tables as well.
Run "Script", already generated will be ignore.

Detail Info

http://www.dispatchertimer.com/sql-server/how-to-generate-scripts-automatically-in-sql-server/#_Toc375325158

SQL Filteration

There are 2 method to form a filteration SQL Statment with valueable or nullable parameter.The [Country] is must filter object and [City] is an optional filter object:


METHOD 1 

1. All the Paramater is always added [Con]

const string SQL_STATEMENT =

                "SELECT COUNT(1) FROM [dbo].[Customer] " +

                "Where [Country]= @Country{0}";

string filter = (city != “”) ? " AND [City]=@City" : String.Empty;

          

Database db = DatabaseFactory.CreateDatabase(BULKSETTINGS_CONNECTIONNAME);

              

using (DbCommand cmd = db.GetSqlStringCommand(String.Format(SQL_STATEMENT, filter)))

{

db.AddInParameter(cmd, "@City ", DbType.String, city);

db.AddInParameter(cmd, "@Country ", DbType.String, country);

    result = Convert.ToInt32(db.ExecuteScalar(cmd));

}

---

METHOD 2 

1. Only Filter onject have to add param
2. need to form SQL Statment 1st and change it by cmd.CommandText

const string SQL_STATEMENT =

                "SELECT COUNT(1) FROM [dbo].[Customer] " +

                "{0}";

Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);

using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))

{

   string filter = string.Empty;

   db.AddInParameter(cmd, "@Country", DbType.AnsiString, country);

   filter += "[Country]=@Country ";

   if (!string.IsNullOrWhiteSpace(prefix))

   {

      db.AddInParameter(cmd, "@City ", DbType.AnsiString, city);

      filter += “AND [City]=@City ";

   }

   if (!string.IsNullOrWhiteSpace(filter))

        filter = "WHERE " + base.FormatFilterStatement(filter);

cmd.CommandText = string.Format(SQL_STATEMENT, filter);

---

SUPPORT METHOD

To Remve AND OR word in 1st word of  filteration sql statment

protected string FormatFilterStatement(string filter)

{

   return Regex.Replace(filter, "^(AND|OR)", string.Empty);

}

Advanced SQL Statement 1.0

WITH
WITH x AS (...)
SELECT * FROM x

Write subqueries for use in a larger SELECT query.
a temporary table or inline view.
________________________________________________________________

ROW_NUMBER
SELECT ROW_NUMBER() OVER(ORDER BY Name DESC) AS Row

Returns the sequential number of a row
________________________________________________________________

CASE
CASE is used to provide if-then-else type of logic to SQL.

1) Simple CASE expression:  

SELECT  ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product

compare an expression to static values

2) Searched CASE expression:

SELECT ProductNumber, Name, "Price Range" = 
   CASE 
      WHEN ListPrice =  0 THEN 'not for resale'
      WHEN ListPrice < 50 THEN 'Under $50'
      WHEN ListPrice > 50 and ListPrice < 250 THEN 'Under $250'
      ELSE 'Over $1000'
      END
FROM Production.Product

compare an expression to one or more logical conditions.

Combination

WITH SortedCode AS (SELECT ROW_NUMBER() OVER 
(ORDER BY cm1.ServiceCode asc) AS RowNumber, p.PriceCodeID, 
(CASE p.Category 
 WHEN 0 THEN 'Content Fee'
 WHEN 1 THEN 'Subscription Fee' ELSE '' END) AS Description
 FROM PriceCodes p 
 INNER JOIN Countries c On c.CountryID = p.CountryID  
 WHERE p.Category != 2 ) 
SELECT * FROM SortedCode WHERE RowNumber BETWEEN 1 AND 25