Avoid "print" statement from Sql Store procedure, It impacts the performance

DECLARE @date DATETIME2

DECLARE @count INT

SET @count = 1

SET @date = SYSUTCDATETIME()

WHILE @count < 1000000





BEGIN


--RAISERROR ('%d',0,1, @count) WITH NOWAIT


--PRINT @count

SET @count = @count + 1




END


SELECT DATEDIFF(MICROSECOND, @date, SYSUTCDATETIME()) / 1000000.

HMACSHA256 based API call using WebRequest

private void button1_Click(object sender, EventArgs e)
       {
           try
           {
               WebRequest request = WebRequest.Create("DemoAPI");
               request.ContentType = "application/json; charset=utf-8";
               string authInfo = Signature("test", "Keys1", "keys2");
               request.Headers["Authentication"] = authInfo;
               request.Method = "POST";
               request.ContentLength = 0;
               var response = request.GetResponse();
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }
 
       public string Signature(string secret, string value, string key2)
       {
           var secretBytes = Encoding.UTF8.GetBytes(secret);
           var valueBytes = Encoding.UTF8.GetBytes(value);
           string signature;
 
           using (var hmac = new HMACSHA256(secretBytes))
           {
               var hash = hmac.ComputeHash(valueBytes);
               signature = Convert.ToBase64String(hash);
           }
           //return signature;
           return key2 + ":" + signature;
       }

SQL Server : List of unused sql tables from database With RowCount 0 or not used more that 2 months

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )

AS (

SELECT DBTable.name AS TableName

,PS.row_count AS TotalRowCount

,DBTable.create_date AS CreatedDate

,DBTable.modify_date AS LastModifiedDate

FROM sys.all_objects DBTable

JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name

WHERE DBTable.type ='U'

AND NOT EXISTS (SELECT OBJECT_ID

FROM sys.dm_db_index_usage_stats

WHERE OBJECT_ID = DBTable.object_id )




)


-- Select data from the CTE


SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate

FROM UnUsedTables

ORDER BY TotalRowCount ASC

SQL Server database level Store procedure to check dependent records

sp to check dependent records
CREATE PROCEDURE [dbo].[sp_IsDependentRecordExist]
 @tableName varchar(250),
 @idValue varchar(50)

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    declare @tableCount as int
 declare @RowID as int
 declare @recordExist as bit
 Declare @tbl varchar(200)
 Declare @col as varchar(200)
 declare @refCount as int
 set @recordExist = 0
 --create temp table for dependent table
 IF OBJECT_ID('tempdb..#dependentTables') IS NOT NULL
 BEGIN
    DROP TABLE #dependentTables
 END
 CREATE TABLE #dependentTables
  (tableid int,Parent_Table varchar(250),Parent_Column varchar(100),Reference_Table varchar(250),Reference_Column varchar(100)) 
 -- INSERT data INTO #dependentTables
 INSERT INTO #dependentTables
  (tableid,Parent_Table,Parent_Column,Reference_Table,Reference_Column)
 SELECT
  row_number() over (order by (select NULL)) as tableid,
  SO_P.name as Parent_Table
  ,SC_P.name as Parent_Column
  ,SO_R.name as Reference_Table
  ,SC_R.name as Reference_Column
 from sys.foreign_key_columns FKC
  inner join sys.objects SO_P on SO_P.object_id = FKC.parent_object_id
  inner join sys.columns SC_P on (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
  inner join sys.objects SO_R on SO_R.object_id = FKC.referenced_object_id
  inner join sys.columns SC_R on (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
  where
   ((SO_R.name = @tableName) AND (SO_R.type = 'U'))

 SELECT @tableCount = COUNT(1) from #dependentTables
 SET @RowID = (SELECT TOP 1 tableid from #dependentTables order by tableid)
 --loop through dependent Tables
 WHILE (@tableCount > 0)
 BEGIN
 
  SELECT @tbl =Parent_Table from #dependentTables where tableid=@RowID
  SELECT @col=  Parent_Column from #dependentTables where tableid=@RowID
  IF OBJECT_ID('tempdb..#Data') IS NOT NULL
  BEGIN
     DROP TABLE #Data
  END
  CREATE TABLE #Data (var int)
 
  if ((select count(1) from sys.columns where Name = N'IsDeleted' and Object_ID = Object_ID(@tbl)) > 0)
  begin
   INSERT #Data exec ('Select count(1) from ' + @tbl + ' where '+  @col +'='+ @idValue + ' and IsDeleted=0')
  end
  else
  begin
   INSERT #Data exec ('Select count(1) from ' + @tbl + ' where '+  @col +'='+ @idValue)
  end
   
  SELECT @refCount = var from #Data
 
  IF OBJECT_ID('tempdb..#Data') IS NOT NULL
  BEGIN
     DROP TABLE #Data
  END
  if @refCount>0
  begin
   set @recordExist = 1
   break
  end
  SET @RowID = @RowID + 1
  set @tableCount = @tableCount - 1
 END
 --drop temp tables
 IF OBJECT_ID('tempdb..#dependentTables') IS NOT NULL
 BEGIN
    DROP TABLE #dependentTables
 END

 select @recordExist as IsDependentRecordExist

END

Customized paging options using SQL server for Paging, Sorting and Searching ... Very fast and robust solution

****************************** THE SP

ALTER PROCEDURE [dbo].[caw_XYZs_GridView]
@startrec int
, @endrec int
, @statusfilterid int
, @filter_XYZname varchar(50)
, @filter_skucount varchar(50)
, @sortcolumn varchar(2)
, @sortdir varchar(4)
AS
BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------
Modified Date: 12-14-2015
------------------------------------------------------------------------------------------------------------------------------------------------------*/
BEGIN TRY
DECLARE @TotalRows numeric(16, 2)

IF @filter_XYZname IS NULL
AND @filter_skucount IS NULL
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List

SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
ELSE
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount +  '%'))

SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount +  '%'))
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END

SELECT TotalRows
, TotalPages
FROM dbo.fun_PagingAndRows(@TotalRows)
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END

******************************* THE FUNCTION 

ALTER FUNCTION [dbo].[fun_PagingAndRows]

(

@totalrows int

)

RETURNS @rows TABLE

(

TotalRows int

, TotalPages int

)

AS

BEGIN

/*------------------------------------------------------------------------------------------------------------------------------------------------------

Modified Date: 10-01-2015

------------------------------------------------------------------------------------------------------------------------------------------------------*/

INSERT INTO @rows

(

TotalRows

, TotalPages

)

SELECT ISNULL(CONVERT(int, @TotalRows), 0) AS TotalRows

, CASE WHEN (CEILING(ISNULL(@TotalRows, 0) / 10.0)) < 1 THEN 1

ELSE (CEILING(ISNULL(@TotalRows, 0) / 10.0))

END AS TotalPages

RETURN

END

Understand the Universal time-zone

Type DateTime

All date and time data is expressed as calendar dates (year, month, day) and a 24-hour day. Day and time data is always expressed in a combined format. No truncated formats are allowed. Dates are based on the Gregorian calendar. All date and time data is expressed numerically, leading zeros are preserved. Time can be expressed down to millisecond resolution, if necessary.

The basic type DateTime string pattern is yyyy-MM-ddTHH:mm:ss.sssZhhmm or date-element 'T' time-element 'Z' [zone-offset]. The character 'T' is arequired delimiter between the date and time sections. Times are expressed in the extended format: the ':' is used as a delimiter between hours, minutes and seconds. Fractional seconds are expressed with a decimal mark ('.' or ',') when needed.

 

 yyyy-MM-ddTHH:mm:ss.sssZ±hhmm

• date-element = yyyy [year-element]
• month-element = '-' MM [months-element]
• day-element = '-' dd [days-element]
• date-time delimiter = 'T'
• time-element = HH [hours-element]
• minute-element = ':' mm [minutes-element]
• second-element = ':' ss [seconds-element]
• fraction = ('.' | ',') digit+ [fraction]
• zone-offset = 'Z' | (('+' | '-') HH [mm])

 

Type conflicts

All use of date and time data in Amazon MWS should be expressed as type DateTime. However, there are a few parameters that express date or time as a simple string. Check the parameter type definition to be sure of which type to use. Using the wrong type will generate an error.

Timezone considerations

Time data is always based on a 24-hour timekeeping system and Coordinated Universal Time (UTC). UTC is also known as Zulu (Z) time. The character 'Z' is expected as the UTC designator in the date time string.

Local timezones can be used, but must be expressed using a UTC offset. Offsets can be expressed as ±hh, where +hh is the local time (in hours) ahead of UTC and -hh is the local time behind UTC.

For example: Z+02 means UTC plus two hours ahead, and Z-04 means UTC minus four hours behind.

If no offset is specified, the API will assume UTC time, even if the 'Z' is missing.

While UTC can be expressed as Z+00, it is not required. The use of a terminal Z and no offset is permitted.

Time intervals

While time intervals and durations are allowed under ISO 8601, they are not currently used in Amazon MWS.

Example:

  2016-03-16T14:32:16.50Z-07

This example is read as the 16th day of March in 2016 at 14hrs 32min and 16.50 seconds UTC. The local time is 7 hours behind UTC.

 

SQL Server Cross ref. Tables used in another database store procedures

----- Cross ref. Table used in another database



SELECT OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,

referenced_schema_name, referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE referenced_database_name ='XYZ' --- Target(Ref) DB

AND is_ambiguous = 0;