• SQL
• Optimization
  • LEFT JOIN, INDEX on Foreign Key on right table
• Sort
  • inline view
  • tablo

CREATE TABLE TableName

CREATE TABLE

ALTER TABLE [TableName]
ADD [FieldName] INT IDENTITY(1, 1);

ALTER TABLE `TableName`
ADD `FieldName` INT AUTO_INCREMENT

ALTER TABLE

ALTER TABLE TableName
DROP CONSTRAINT UQ_ConstraintName

ALTER TABLE TableName
DROP INDEX UQ_ConstraintName

master.dbo.fn_varbintohexstr(CAST(FieldName AS VARBINARY(MAX)))

AFTER [UPDATE]
INSTEAD OF [INSERT]
FOR [INSERT]

declare @@dt datetime
set @@dt = '09-22-2007 15:07:38.850'
select dateadd(day, datediff(day, 0, @@dt), 0)

 SELECT top 10 *
 FROM [ServerIP].DatabaseName.dbo.TableName

DATEDIFF(day, bg_reported_date, GETDATE()) > 365

ALTER PROCEDURE [dbo].[usp_sel_Table]
    (@@Limit              INT
    ,@@Type               VARCHAR(250) = NULL -- Optional
    ,@@CurrentPageNumber  INT = NULL)         -- Optional
 
AS

DECLARE @@StartRowIndex int;
DECLARE @@EndRowIndex int;
SET @@StartRowIndex = (@@CurrentPageNumber - 1) * @@Limit + 1;
SET @@EndRowIndex = @@CurrentPageNumber * @@Limit;

WITH TempTable AS (SELECT id, Type, StartDate, EndDate, RecordCount, Notes, LogFilePath, 
	 ROW_NUMBER() OVER(ORDER BY id DESC) as RowIndex, 
	 COUNT(id) OVER() AS TotalRecordCount 
	 FROM tbl_Table)

SELECT id, Type, StartDate, EndDate, RecordCount, Notes, LogFilePath, TotalRecordCount
FROM TempTable
WHERE RowIndex BETWEEN @@StartRowIndex AND @@EndRowIndex;

PRINT @@SQL
EXEC('select')
cast(@@Val1 AS VARCHAR(10))
char(13)

create table #Table (Aid INT, BId bigint)
drop table #Table
dynamic query into #TempTable

from table with (updlock)

update table
set field = var
output inserted.Field1, inserted.AccountID INTO #Table

convert(char(8), rc.CollectedDate, 112)
cast(dateadd(dd, -@@create, getdate()) as date)

begin tran
commit tran

len()
ltrim()
rtrim()
isnumeric()
stuff()
min()
max()
patindex('%[^,]%', Field1)
substring()
reverse()
rank() over (partition by ...)
dense_rank() over (order by lo.Edit desc, rn.RecoveryID) as Rnk
select @@ID = scope_identity()

insert into
output
select

unpivot()
pivot()		
		
		
		
		
		
		
		















    delimiter $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@@`localhost` SQL SECURITY DEFINER VIEW `core`.`view_equitypositiontotal` AS select sum(`core`.`equitypositions`.`Price`) AS `Price` from `core`.`equitypositions`$$
-----------------------------
    delimiter $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@@`localhost` SQL SECURITY DEFINER VIEW `core`.`onetroyouncegoldcoins` AS select sum(`core`.`assetgold`.`Price`) AS `Total`,count(1) AS `Amount` from `core`.`assetgold` where (`core`.`assetgold`.`Size` = 1.0000)$$
----------------------------
    delimiter $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@@`localhost` SQL SECURITY DEFINER VIEW `core`.`viewassetcommodity` AS select sum(`core`.`viewassetcommodity2`.`Total`) AS `Total` from `core`.`viewassetcommodity2`$$
----------------------------
    delimiter $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@@`localhost` SQL SECURITY DEFINER VIEW `core`.`viewassetcommodity2` AS select 'Gold' AS `Metal`,27 AS `Quantity`,1825 AS `SpotPrice`,(27 * 1825) AS `Total`,sum(`core`.`assetgold`.`Price`) AS `Paid` from `core`.`assetgold` union select 'Silver' AS `Metal`,1 AS `Quantity`,41 AS `SpotPrice`,sum(`core`.`assetsilver`.`Price`) AS `total`,0 AS `Paid` from `core`.`assetsilver` union select 'Sold Gold' AS `Metal`,1 AS `Quantity`,1 AS `SpotPrice`,(sum(`core`.`sellgold`.`Price`) * -(1)) AS `Total`,0 AS `Paid` from `core`.`sellgold`$$
---------------------------
    delimiter $$
CREATE DEFINER=`root`@@`localhost` PROCEDURE `usp_ins_File`(IN content MEDIUMBLOB, IN fileExtension VARCHAR(50))
BEGIN
INSERT INTO File (Content, FileExtension)
VALUES (content, fileExtension);
END$$
--------------------------
delimiter $$
CREATE DEFINER=`root`@@`localhost` PROCEDURE `usp_sel_File`(IN fileID INT)
BEGIN
    SELECT *
    FROM File
    WHERE FileID = fileID;
END$$

use core
show tables;
help;
\h
\?
\c
exit
\q
To check MySQL version:
select version();
drop view viewname;
c:\"Program Files"\MySQL\"MySQL Server 5.1"\bin\mysqldump -u username -p databasename > backup_db.sql
    http://www.joellipman.com/articles/sql/628-cheat-sheet-for-mysql-vs-t-sql.html

    dbcc checkident ('TableName')

SELECT *
FROM [view_StateAndPracType]
	PIVOT ( MAX(LicenseEntry) FOR [State] IN ([AK],[WV],[WY])) AS Test
------------------------------------------------------------------------
waitfor delay ' '
extended events
------------------------------------------------------------------------
EXPLAIN SELECT

SHOW INDEXES FROM tbl
ALTER TABLE ADD INDEX Field1;
ALTER TABLE DROP INDEX Field1;
------------------------------------------------------------------------
    SELECT X.LicenseEntryID,
    STUFF((SELECT ',' + I.PracType
           FROM view_PracTypes I
           WHERE X.LicenseEntryID = I.LicenseEntryID
           FOR XML PATH('')), 1, 1, '') AS PracTypes
FROM view_PracTypes X
GROUP BY X.LicenseEntryID
------------------------------------------------------------------------
set @@rank = 0;
@@rank := @@rack + 1 AS RANK