working on it ...

Filters

Explore Public Snippets

Sort by

Found 63 snippets

    public by sjschmalfeld  308  1  4  0

    Null parameters in Stored Procedure

    Do not use coalesce methodology, causes table scan
    // Dont do this: LastName = COALESCE (@LastName, LastName) 
    
    
    WHERE (LastName = @LastName OR @LastName IS NULL) 						

    public by sjschmalfeld  3298  9  5  2

    Rename Logical Name Database SQL Server

    Changes the logical name of a database
    // Enter here the actual content of the snippet.            
    
    -- Rename logical names (only needed if restoring from a backup for a Different database):
    ALTER DATABASE MyDatabase 
    MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabase_data')
    GO
    ALTER DATABASE MyDatabase 
    MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabase_log')
    GO                        

    public by juozaslt  2081  0  3  0

    ca bug manual doc create

    declare @p3 nvarchar(200)
    set @p3=N'                                                                                                                                                                                                        '
    declare @p4 nvarchar(255)
    set @p4=N'                                                                                                                                                                                                                                                               '
    declare @p5 nvarchar(50)
    set @p5=N'                                                  '
    declare @p6 nvarchar(20)
    set @p6=N'                    '
    declare @p7 nvarchar(50)
    set @p7=N'                                                  '
    declare @p8 nvarchar(50)
    set @p8=N'                                                  '
    declare @p9 nvarchar(6)
    set @p9=N'      '
    declare @p10 int
    set @p10=0
    declare @p11 datetime
    set @p11='2016-06-21 09:08:37.100'
    declare @p12 real
    set @p12=0
    declare @p13 int
    set @p13=0
    declare @p14 real
    set @p14=0
    declare @p15 datetime
    set @p15='2016-06-21 09:08:37.100'
    declare @p16 datetime
    set @p16='2016-06-21 09:08:37.100'
    declare @p17 datetime
    set @p17='2016-06-21 09:08:37.100'
    declare @p18 real
    set @p18=0
    declare @p19 int
    set @p19=8
    declare @p20 datetime
    set @p20='2016-06-21 09:08:37.100'
    declare @p21 float
    set @p21=0
    declare @p23 int
    set @p23=0
    exec GUI_doc_manual_init @contract_id=1418,@invoice_id=-1,@client_name=@p3 output,@pro_address1=@p4 output,@pro_comp_code=@p5 output,@pro_vat_number=@p6 output,@agg_number=@p7 output,@invoice_number=@p8 output,@invoice_serie=@p9 output,@sf_status=@p10 output,@sum_payed_on=@p11 output,@invoice_debt=@p12 output,@invoice_delay=@p13 output,@invoice_delay_sum=@p14 output,@invoice_generated=@p15 output,@invoice_from=@p16 output,@invoice_to=@p17 output,@invoice_foreign_amount_total=@p18 output,@doc_type=@p19 output,@doc_dt=@p20 output,@max_amount=@p21 output,@userid=73,@errorcode=@p23 output
    select @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p23

    public by cghersi  4768  1  6  1

    Remove Duplicate rows in SQL Server

    This script removes the duplicate rows in table Customers detecting the rows with the same contactname
    SET NOCOUNT ON
    SET ROWCOUNT 1
    WHILE 1 = 1
      BEGIN
        DELETE  
        FROM Customers
        WHERE contactname IN
            (SELECT  contactname
             FROM    Customers
             GROUP BY contactname
             HAVING  COUNT(*) > 1)
          
        IF @@Rowcount = 0
          BREAK ;
     END
     SET ROWCOUNT 0          

    public by cghersi  3540  3  6  1

    Check Recovery status of DB in SQL Server 2012

    This query allows you to check the status f your recovery in SQL Server. More information on http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
    DECLARE @DBName VARCHAR(64) = 'databasename'
     
    DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
     
    INSERT INTO @ErrorLog
    EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
     
    SELECT TOP 5
    	 [LogDate]
    	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
    	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
    	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
    	,[TEXT]
     
    FROM @ErrorLog ORDER BY [LogDate] DESC         

    public by SpeedOfSpin  2372  0  4  0

    SQL Bounds

    SELECT
      geometry::EnvelopeAggregate(geometry::STGeomFromWKB(geom.STAsBinary(), 4326).MakeValid()).STPointN(1).STX AS MinX,
      geometry::EnvelopeAggregate(geometry::STGeomFromWKB(geom.STAsBinary(), 4326).MakeValid()).STPointN(1).STY AS MinY,
      geometry::EnvelopeAggregate(geometry::STGeomFromWKB(geom.STAsBinary(), 4326).MakeValid()).STPointN(3).STX AS MaxX,
      geometry::EnvelopeAggregate(geometry::STGeomFromWKB(geom.STAsBinary(), 4326).MakeValid()).STPointN(3).STY AS MaxX
    FROM FrontierFiber WHERE id = 1;                        

    public by cghersi  4134  4  6  0

    Query XML fields' content in MS SQL Server

    This is the right syntax to perform XQuery queries for XML columns in a SQL Server table
    SELECT MyField.query('
      declare namespace s2c="http://www.snip2code.com/namespaces/s2c";
    	(/s2c:myroot/myelem/fieldIwant[1]/text())')
    FROM  MyTable

    public by cghersi  4278  6  6  2

    Move a file to another location in SQL Server

    Here is the procedure to move a file to another location (e.g. for the database called MyDB). First you need to execute the first part of this snippet (until the comment '/**** Now you need to physically move the file to the other location ***/'). Then you need to move the file and eventually you can execute the last two statements.
    ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    
    ALTER DATABASE MyDB SET OFFLINE;
    
    ALTER DATABASE MyDB MODIFY FILE 
    (
       Name = MyDB_file1,
       Filename = 'c:\DB\MyDB.mdf'
    );
    
    /**** Now you need to physically move the file to the other location ***/
    
    ALTER DATABASE MyDB SET ONLINE;
    
    ALTER DATABASE MyDB SET MULTI_USER;

    public by cghersi  2333  2  6  0

    Find columns used in indexes for SQL Server

    Retrieves a number of information related to the structure of the available indexes
    SELECT 
         TableName = t.name,
         IndexName = ind.name,
         ColumnName = col.name, FGName = f.name,
    	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc, ic.*
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
    ORDER BY 
         t.name, ind.name, ind.index_id, ic.index_column_id

    public by cghersi  3211  5  6  2

    Detect the worst SQL queries in avg CPU time in SQL Server

    Detect worst performing sql queries which is slowing down Microsoft SQL Server, this script return top queries taxing sql server CPUs. Thanks to Patrick Akhamie: http://www.sqlservercentral.com/scripts/TOP+CPU+Queries/110012/
    use tempdb
    go
    
    IF object_id('tempdb..##FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set1]
    GO
    
    declare @ServerTime datetime = getdate()
    
    , @ConvertMiliSeconds bigint = 1000
    
    , @FilterMoreThanMiliSeconds bigint = 1
    
    , @FilterHours bigint = 2
    
    , @execution_count bigint = 2
    
    , @debugFlg bit = 0
    
    
    
    if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds
    
    , @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours 
    
    , @execution_count as execution_count
    
    
    
    
    
    select TOP 300
    
    @@servername as servername,@ServerTime as runtime
    
    ,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName 
    
     ,SUBSTRING(QueryText.text, (QueryStats.statement_start_offset/2)+1, 
    
    (isnull((
    
    CASE QueryStats.statement_end_offset
    
     WHEN -1 THEN DATALENGTH(QueryText.text)
    
     WHEN 0 THEN DATALENGTH(QueryText.text)
    
     ELSE QueryStats.statement_end_offset
    
     END - QueryStats.statement_start_offset),0)/2) 
    
     + 1) AS QueryExecuted
    
    ,total_worker_time AS total_worker_time
    
    ,QueryStats.execution_count as execution_count
    
    ,statement_start_offset,statement_end_offset
    
    ,(case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end) as ObjectName
    
    ,query_hash
    
    ,plan_handle
    
    ,sql_handle
    
    into ##FindTopCPUQueries_set1
    
    from sys.dm_exec_query_stats as QueryStats
    
    cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText
    
    where QueryStats.query_hash IN 
    
    (
    
    select QueryStatsBaseTable.query_hash 
    
    from sys.dm_exec_query_stats QueryStatsBaseTable
    
    where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE())
    
    group by query_hash
    
    having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count
    
    )
    
    ORDER BY total_worker_time/execution_count DESC;
    
    
    
    if @debugFlg=1 select * from ##FindTopCPUQueries_set1 order by QueryExecuted
    
    
    
    IF object_id('tempdb..##FindTopCPUQueries_set2') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set2]
    
    
    
    select 
    
    servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime
    
    ,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName
    
    into ##FindTopCPUQueries_set2
    
    from ##FindTopCPUQueries_set1
    
    group by query_hash,servername,runtime
    
    order by AvgCPUTime desc
    
    
    
    
    
    select * from ##FindTopCPUQueries_set2
    
    --where QueryExecuted like 'select TOP 300%'
    
    order by AvgCPUTime desc
    • Public Snippets
    • Channels Snippets