Convert Exponential varchar to numeric:

select convert(numeric(18, 3),cast('1.50721e+006' as float))
select convert(numeric(18, 3),cast('1.50721e+006' as float))
select cast('1/05/07' as datetime) , but in this case casting is usually not required, autocast. But to convert datetime into varchar, casting must be done.
select convert(varchar, getdate(), 103)  01/01/2008


select function_name(params_comma_seperated)

select top 1400 * from gkam_midas_data
select top 1400 * from gkam_midas_data

rtrim()
ltrim()
left(str, 4)
right(str, 4)


declare @var_name int
declare @var_name table( @id int, @name varchar(20) )

Nested case when:
update #Pre_Final set [PI]=
Case when RegionID is null and CountryID is null then
Case When EvaluationIndex >= 100 then 1
When EvaluationIndex < 100 then -1 else -2 End
when RegionID is not null and CountryID is null then
Case When RegionEvaluationIndex >= 100 then 1
When RegionEvaluationIndex < 100 then -1 else -2 End
when RegionID is not null and CountryID is not null then
Case When CountryEvaluationIndex >= 100 then 1
When CountryEvaluationIndex < 100 then -1 else -2 End
End


if @period_type IN ('QTR','YTD','MAT')
select @period = dbo.GET_PERIOD_FROM_DATE('Quarter', @max_data_date)
Else if @period_type = 'YEAR' --or @period_type = 'YTD'
select @period = dbo.GET_PERIOD_FROM_DATE('Year',@max_data_date)
Else --if @period_type != 'YEAR' and @period_type = 'QTR'
select @period = dbo.GET_PERIOD_FROM_DATE('Quarter',@max_data_date)


Bulk Update Query:

UPDATE [TEMP] SET [temp].COUNTRYID = B.COUNTRY FROM
(SELECT REGION, COUNTRY FROM RAW_DATA)B
WHERE [temp].REGIONID = [B].REGION

Here RegionID must uniquely identify a row. If not then some unique id must be matched in the where clause.

Take one value from a sql in a variable:
select @a=accountID from gkam_performance_data where accountID=1



DECLARE @order int
SET @order = 3
SELECT * FROM dbo.ICD10_DIAGNOSIS_temp
ORDER BY
CASE WHEN @order = 1 THEN ICD10_DIAG_CD END DESC,
CASE WHEN @order = 2 THEN PAR_ICD10_DIAG_CD END ASC,
CASE WHEN @order = 3 THEN ICD10_DIAG_CD_LVL_NBR END DESC,
CASE WHEN @order = 4 THEN ICD10_DIAG_DESC END ASC

DECLARE @order int
SET @order = 3
SELECT * FROM dbo.ICD10_DIAGNOSIS_temp
ORDER BY
CASE WHEN @order = 1 THEN ICD10_DIAG_CD
WHEN @order = 2 THEN PAR_ICD10_DIAG_CD
WHEN @order = 3 THEN ICD10_DIAG_CD_LVL_NBR
WHEN @order = 4 THEN ICD10_DIAG_DESC
END
ASC

Remember dynamic SQL (creating SQL in a string and then executing) cannot be effectively cached so this is a much better option.

Find duplicates
SELECT column,COUNT(*) c FROM table
GROUP BY column HAVING COUNT(*) > 1

Looping with GO

CREATE TABLE #TEST (ID INT IDENTITY (3,1), ROWID uniqueidentifier)
CREATE TABLE #TEST2 (ID INT IDENTITY (1,3), ROWID uniqueidentifier)
GO
INSERT INTO #TEST (ROWID) VALUES (NEWID())
INSERT INTO #TEST2 (ROWID) VALUES (NEWID())
GO 100



SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city



Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS) SQL (all I've tested it in).

select * from information_schema.columns
where table_name = 'mhplt_level'

IF NOT EXISTS
(
SELECT * FROM [information_schema].[columns]
WHERE table_name = 'Customer'
AND table_schema = 'dbo'
AND column_name = 'FavoriteColorId'
)
BEGIN
ALTER TABLE [dbo].[Customer]
ADD FavoriteColorId int



Receiving a table output from a sql:
insert @atc4_list
exec sp_executesql @v_dynsql, @v_dynsql_params, @dyn_tg_id = @p_tg_id, @dyn_a_market = @v_a_market



SELECT OBJECT_ID,
OBJECT_NAME(OBJECT_ID) AS TABLENAME,
NAME AS COLUMNNAME,
SEED_VALUE,
INCREMENT_VALUE,
LAST_VALUE,
IS_NOT_FOR_REPLICATION
FROM SYS.IDENTITY_COLUMNS ORDER BY 2

DBCC CHECKIDENT('mhplt_objective',RESEED,1)