Sql Injection

Typical username, password validation is done in sql query using like this:

SELECT COUNT(*) FROM Users WHERE USERNAME = 'InputUsername' AND  PASSWORD   ='InputPassword'

If query returns count greater than zero, then user exists else no such user with the given input username & password.

Now what happens if the following input is given:
Username: ' OR 1=1 --
password: anyCharacters

Now the query is

SELECT COUNT(*) FROM Users WHERE USERNAME = '' OR 1=1 --AND PASSWORD ='InputPassword'

This query returns the count as total records found in the table. As a result of this validation gets bypassed even though there is no such username & password exists in table.

declare @tblVar TABLE
(
loginname varchar(20),
pwd varchar(20)
)

INSERT INTO @tblVar values ('asif','101')
INSERT INTO @tblVar values ('jiju','123')

declare @inputUser AS VARCHAR(20)
declare @inputPwd AS VARCHAR(20)

select COUNT(*) from @tblVar where loginname = 'asif' and pwd = '101'

--select COUNT(*) from @tblVar where loginname = 'inputUserName' and pwd = 'inputpwd'

--inputUsername:'or 1=1 --

select COUNT(*) from @tblVar where loginname = ''or 1=1 --' and pwd = 'inputpwd'

i.e bcoz the opening quote is closed by the quote in the inputUserName and the next stmt. is OR condition. In the OR condition we are giving true condition which always returns true. After this sql comment comes, hence the comment part is skipped.

To Find the column if you forget the table

SELECT TABLE_NAME, 
       COLUMN_NAME,
       DATA_TYPE, 
       IS_NULLABLE, 
     COLUMN_DEFAULT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE column_name LIKE '%lettersFoundInTheColumn%'

Copying data to a table from another table in a different database

insert into [DatabaseA].dbo.TableA
(

Country_Code,
Country_Name
)  
select  
Country_Code,
Country_Name
from [DatabaseB].dbo.TableB

Usage of Having Clause

We use group by  clause when you want to get result against a field, for example total no. of  medals won by countries against each sports event in an Olympics.

Select Country,Sum(Medal) From ScoreTable Group By Country

What if you want to list only the countries which has got total no. of medals exceeding 100.

Select Country,Sum(Medal) From ScoreTable Group By Country having Sum(Medal)>100

i.e use having clause when you want to filter against the result which is already grouped. Here you cannot use the where clause since the where clause doesn't work with aggregates – like sum, avg, max, etc.. Instead, what we will need to use is the having clause. The having clause was added to sql just so we could compare aggregates to other values – just how the ‘where’ clause can be used with non-aggregates. 

DB Naming Conventions

DATABASE CONVENTIONS
--------------------------------------
1. TABLES (RULES FOR TABLES)
---------------------------------------------
1. Rule 1a (Plural Names) - Table names should be plural, for example, "Customers" instead of "Customer"
2. Rule 1b (Prefixes) - Used correctly, table prefixes can help you organize your tables into related groups or distinguish them from other unrelated tables.
For example, for a healthcare application you might give your tables an "Hc" prefix so that all of the tables for that application would appear in alphabetized lists together. Note that even for the prefix, use Pascal Case.
3. Rule 1c (Notation) - For all parts of the table name, including prefixes, use Pascal Case. Using this notation will distinguish your table names from SQL keywords (all capital letters). For example, "SELECT CustomerId_Pk, CustomerName FROM MyAppGroupTable WHERE CustomerName = '%S'" shows the notation for the table name distinguishing it from the SQL keywords used in the query. PascalCase also reduces the need for underscores to visually separate words in names.
4. Rule 1e (Abbreviations) - Avoid using abbreviations if possible. Use "Accounts" instead of "Accts" and "Hours" instead of "Hrs".
5. Rule 1f (Junction a.k.a Intersection Tables) - Junction tables, which handle many to many relationships, should be named by concatenating the names of the tables that have a one to many relationship with the junction table. For example, you might have "Doctors" and "Patients" tables. Since doctors can have many patients and patients can have many doctors (specialists) you need a table to hold the data for those relationships in a junction table. This table should be named DoctorsPatients". Since this convention can result in lengthy table names, abbreviations sometimes may be used at your discretion.

2. COLUMNS - (incl. PRIMARY, FOREIGN, AND COMPOSITE KEYS)
------------------------------------------------------------
1. When naming your columns, keep in mind that they are members of the table, so they do not need the any mention of the table name in the name. The primary key field is typically the only exception to this rule where including the table name is justified so that you can have a more descriptive field name than just "Id". "CustomerId" is acceptable but not required. Just like with naming tables, avoid using abbreviations, acronyms or special characters. All column names should use Pascal Case to distinguish them from SQL keywords (all upper case).
2. Rule 2a (Identity Primary Key Fields) - For fields that are the primary key for a table and uniquely identify each record in the table, the name should simply be “Id“
3. Rule 2b (Foreign Key Fields) - Foreign key fields should have the exact same name as they do in the parent table where the field is the primary key
4. Rule 2e (Data Type Specific Naming) - Boolean fields should be given names like "IsDeleted", "HasPermission", or "IsValid" so that the meaning of the data in the field is not ambiguous. If the field holds date and/or time information, the word "Date" or "Time" should appear somewhere in the field name.

3. STORED PROCEDURES
---------------------
1. Rule 6a (Prefixes or Suffixes) - The way you name your stored procedures depends on how you want to group them within a listing. If you'd like to group them by the type of CRUD operation they perform, then prefix the name with "Create", "Get", "Update" or "Delete". Using this kind of prefix will, for example, group all of your "Create" procedures together since they will all start with the Create prefix, like "CreateProductInfo" or "CreateOrder". If instead, you would like to have your procedures ordered by the table they perform a CRUD operation on, adding "Create, Get, Update, or Delete" as a suffix will do that for you. For example, "ProductInfoCreate" or "OrdersCreate". If your procedure returns a scalar value, or performs an operation like validation, you should not use a CRUD prefix or suffix. Instead use the verb and noun combination. For example, "ValidateLogin"
2. Rule 6c (Bad Prefixes) - Do not prefix your stored procedures with something that will cause the system to think it is a system procedure. For example, in SQL Server, if you start a procedure with "sp_", "xp_" or "dt_" it will cause SQL Server to check the master database for this procedure first, causing a performance hit.

Records Which Are Having Duplicate Column Values

SELECT A.Field1 FROM TableA as A GROUP BY A.Field1 HAVING COUNT(A.Field1)>1

Date Filteration In SQL

Usually we need to pass the date which is typically in the format "YYYY-MM-dd" , but while converting this into datetime by using CONVERT(DATETIME,@ToDate,121) where to @ToDate ='2012-01-18' we get the result as '2012-01-18 00:00:00.000'. Because of this when you use this in BETWEEN operator like below to filter the records

select * from @tblVar where CONVERT(VARCHAR(10),LogDate,111) between  
CONVERT(VARCHAR(10),'2011/05/25',111) and  
CONVERT(VARCHAR(10),'2011/05/27',111) 

You will get only the reocrds which falls in between 2011-05-25 00:00:00.000 and 2011-05-27 00:00:00.000, because of this you will not get the records in the 2011-05-27 which hour-minute part is > 0. So to find all the records which falls in between 2 dates you have to use the below one.

CREATE PROCEDURE [dbo].[sampleReport]
(
@FromDate    VARCHAR(30) ,
@ToDate        VARCHAR(30)
)
AS

Declare @CurrentToDate datetime
Declare @DayAfterCurrentToDate datetime
Set @CurrentToDate = CONVERT(DATETIME,@ToDate,121)
set @DayAfterCurrentToDate = DateAdd(day, 1,@CurrentToDate)

select * from Table p
WHERE
P.DateField >= CONVERT(DATETIME,@FromDate,121) and 
P.DateField < @DayAfterCurrentToDate

To Get the Count Of Items By Groupwise

Some times we may need to get the count of items by grouping the items. For example we may need to get the Fruits, No Of Fruits In Quantity

SELECT FruitName,
COUNT(*) AS FruitQty
FROM FruitsTable
GROUP BY FruitName
HAVING COUNT(FruitName) > 10
ORDER BY 2 DESC

This will list the fruits which are more than 10 in quantity

To find the count of Empty OR Null Columns in a table

DECLARE @tblName as VARCHAR(50)
SET @tblName = 'dbo.YourTableName'
DECLARE @tblWPCustProfColumns TABLE (ID int IDENTITY(1,1),FieldName VARCHAR(50))
INSERT INTO @tblWPCustProfColumns
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@tblName)

DECLARE @MinID INT
DECLARE @MaxID  INT
SELECT @MinID = MIN(ID) FROM @tblWPCustProfColumns
SELECT @MaxID = MAX(ID) FROM @tblWPCustProfColumns
While @MinID <= @MaxID
begin
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @CountEmpty varchar(30);
DECLARE @col Varchar(30);

SELECT @col =      FieldName FROM @tblWPCustProfColumns WHERE ID = @MinID

SET @SQLString = N' SELECT @CountEmptyOUT = COUNT(*) FROM '+ @tblName +' WHERE ' + @col + '='''' OR ' + @col + ' IS NULL';
SET @ParmDefinition = N'@CountEmptyOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,@CountEmptyOUT=@CountEmpty OUTPUT;
IF @CountEmpty>0
BEGIN
SELECT @col,@CountEmpty
END
Set @MinID = @MinID + 1
end

How to store the result of a storedprocedure to a table variable

declare @tblVar TABLE
(
spid INT,
ecit int,
status varchar(20),
loginname varchar(20),
hostname varchar(20),
blk int,
dbname varchar(30),
cmd varchar(max),
reques_id int
)

INSERT INTO @tblVar
exec sp_who 'sa'

select * from @tblVar where hostname = 'BIDC22'

Finding the total query execution time

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @TimeDifference DATETIME
SELECT @StartTime = GETDATE()
------------------------------------
Your Qurey Here
----------------------------------
SELECT @EndTime = GETDATE()
SET @TimeDifference = @EndTime - @StartTime
SELECT DATEDIFF(millisecond,@StartTime,@EndTime) AS TotalMilliSecondsTaken
SELECT DATEDIFF(second,@StartTime,@EndTime) AS TotalSecondsTaken
SELECT DATEDIFF(minute,@StartTime,@EndTime) AS TotalMinutesTaken
select @TimeDifference as Difference,@EndTime as EndTime, @StartTime as StartTime

SQL to query different databases which are in different server instances

EXEC sp_addlinkedserver
@server='S3',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.10.130',
@catalog ='WC+PRDMAINDBNEW'
EXEC sp_addlinkedsrvlogin 'S3', 'false', NULL, 'sa', 'atotestdb'

SELECT TBLWPAirlineOperatingSector.
FromSector,TBLWPAirlineOperatingSector.ToSector,TBL2.TaxFromSector,TBL2.TaxToSector FROM

(
SELECT FromSector,ToSector FROM S3.[WC+PRDMAINDBNEW].DBO.WPAirlineOperatingSector WHERE AirlineCode like '6E' AND LEN(FromSector)=3 AND LEN(ToSector)=3
) AS TBLWPAirlineOperatingSector
LEFT OUTER JOIN [ANS+CACHEDB].DBO.B2CAirlineTaxNRMCacheDOMIndigo AS TBL2
ON TBLWPAirlineOperatingSector.FromSector= TBL2.TaxFromSector AND
TBLWPAirlineOperatingSector.ToSector= TBL2.TaxToSector

SQL DateTime Conversion & Other Related

Most of the time you may need to select records from table based on date filteration. Date given in UI is mostly in any of the 9 total possible combinations and mostly your UI date is not in the format of the MSSQL datetime format stored in the database i.e in this format

yyyy-mm-dd hh:mm:ss.mmm

You need to convert the date from UI to these format for filtering the date fields.

1. Convert your UI date to format yyyy/mm/dd

2. Then in the sql filtering condition give like this CONVERT(DATETIME,'uidateYear/uiDateMonth/uiDay',121)

Example:

DECLARE @tblVar TABLE (
ID INT IDENTITY(1,1),
LogDate DATETIME,
ErrorDescription VARCHAR(MAX)
)

INSERT INTO @tblVar VALUES (DATEADD(d,0,getdate()),'
Description1')
INSERT INTO @tblVar VALUES (DATEADD(d,1,getdate()),'Description1')
INSERT INTO @tblVar VALUES (DATEADD(d,2,getdate()),'Description1')
INSERT INTO @tblVar VALUES (DATEADD(d,3,getdate()),'Description1')



select * from @tblVar where CONVERT(VARCHAR(10),LogDate,111) between
CONVERT(VARCHAR(10),'2011/05/25',111) and
CONVERT(VARCHAR(10),'2011/05/27',111)

To Filter records based on yyyy/mm/dd

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

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

SELECT * FROM Table WHERE CONVERT(VARCHAR(10),DateField,111) = CONVERT(VARCHAR(10),GETDATE(),111)

For More Reference look here

Paging In MSSQL

WITH ResultSet AS

(

SELECT ROW_NUMBER() OVER

(

ORDER BY

ErroLog.Field1,

ErroLog.Field2,



)AS RowNumber,

TableAliasName.Field1 AS LogID,

TableAliasName.Field2 AS UPL,



FROM TableName AS TableAliasName

WHERE TableAliasName.Field2 LIKE '111%'

)

SELECT * FROM ResultSet WHERE RowNumber BETWEEN BeginCounter AND EndCounter

NOTE> This will not work for a billion reocords or more than that but will work upto million quite easily. I don't know how to fetch when the result set contains more than million in MSSQL.

Bulk Insert Update Delete sql stored procs

CREATE PROCEDURE qspBulkAddUpdateDeleteXML
(
@XMLDoc XML
)AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @hdoc INT

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLDoc


INSERT INTO Table
(

Field1,
Field2,
Field3
)
SELECT
XMLTableInsert.F1,
XMLTableInsert.F2,
XMLTableInsert.F3
FROM OPENXML(@hdoc, 'TBDBOQAttachmentDetailsList/TBDBOQAttachmentDetails/Insert')
WITH
(
F1 INT 'XMLTagForField1',
F2 INT 'XMLTagForField2',
F3 VARCHAR(250) 'XMLTagForField3'
)AS XMLTableInsert


UPDATE TBDBOQAttachmentDetails
SET
AttachmentPath = XMLTableUpdate.AttachmentPath
FROM OPENXML(@hdoc, 'TBDBOQAttachmentDetailsList/TBDBOQAttachmentDetails/Update')
WITH
(
TBDBOQAttachmentDetailsID INT 'TBDBOQAttachmentDetailsID',
AttachmentPath VARCHAR(250) 'AttachmentPath'
)AS XMLTableUpdate
WHERE
TBDBOQAttachmentDetails.TBDBOQAttachmentDetailsID = XMLTableUpdate.TBDBOQAttachmentDetailsID

DELETE FROM TBDBOQAttachmentDetails
WHERE TBDBOQAttachmentDetails.TBDBOQAttachmentDetailsID IN
(
SELECT TBDBOQAttachmentDetailsID FROM
OPENXML(@hdoc, 'TBDBOQAttachmentDetailsList/TBDBOQAttachmentDetails/Delete')
WITH
(
TBDBOQAttachmentDetailsID INT 'TBDBOQAttachmentDetailsID'
)
)


END

Common Requirements in SQL

-- Using Optinal Parameters In SQL Store Procedures:

declare @param as int=null
SELECT * FROM TableA where Field1 = ISNULL(@param,Field1) 

This will select only the records matching if  @param has got value, else all the records from the table.

Another Example:

CREATE PROCEDURE TestProc

(

@Param1 varchar(50) = NULL,

@Param2 varchar(50) = NULL,

@Param3 varchar(50) = NULL

)

AS

SELECT

*





FROM

TestTable





WHERE

(@Param1 IS NULL OR col1 = @Param1) AND

(@Param2 IS NULL OR col2 = @Param2) AND

(@Param3 IS NULL OR col3 = @Param3)





Results in:



exec TestProc

exec TestProc I

exec TestProc I, Love

exec TestProc I, Love, SPROCs





--Using Optional Parameters With Between Operator:





ALTER PROCEDURE [qspMKTRPTProjectAvailabilityStatus]



@COMCompanyID INT,



@FromUnit INT = NULL,



@ToUnit INT = NULL





SELECT



COMProject.COMCompanyID



FROM



COMProject



WHERE



(COMProjectUnit.COMProjectUnitID BETWEEN @FromUnit AND @ToUnit OR (@FromUnit IS NULL AND @ToUnit IS NULL))









--* Search Stored Procedures with SQL in SQL Server



sp_stored_procedures '%qspTBD%'



or



SELECT DISTINCT SO.NAME



FROM SYSOBJECTS SO (NOLOCK)



WHERE SO.TYPE = 'P'



AND SO.NAME LIKE '%scheduletemplate%'



ORDER BY SO.Name







--* Drop Procedures





IF EXISTS (select * from sys.procedures where name like 'qspProcToDrop')



DROP PROCEDURE qspProcToDrop



GO





--* DROPPING RELATIONSHIPS IN TBDBOQSubContractRateDetails TABLE



IF EXISTS(select * from sys.objects where name like 'FK_TBDBOQSubContractRateDetails_TBDBOQScheduleDetails')





--* DROP A COLUMN AFTER CHECKING FOR EXISTENCE





if Exists(select * from sys.columns where Name = N'columnName'

and Object_ID = Object_ID(N'tableName'))







--* ADD COLUMNS TO TABLE



ALTER TABLE table_name

ADD column_name datatype



--* ADD COLUMNS TO TABLE WITH DEFAULT VALUE



ALTER TABLE table_name ADD column_name datatype DEFAULT 0


--* TO CHANGE THE DATATYPE OF AN EXISTING COLUMN



ALTER TABLE TBDBOQSubContractRateDetails ALTER COLUMN Rate DECIMAL(12,3)



--Iiterate through a result set by using Transact-SQL:





SELECT @MinID = MIN(Column) FROM @Table



SELECT @MaxID = MAX(Column) FROM @Table





WHILE @MinID<=@MaxID



BEGIN





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



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



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



SET @MinID = @MinID + 1





END