Sql Server
  • Serverul nu vrea sa execute comenzile de RECONFIGURE, plangandu-se ca "Ad hoc update to system catalogs is not supported"
    • RECONFIGURE WITH OVERRIDE
  • Ca sa putem face drop pe o baza cu conexiuni deschise
ALTER DATABASE [<DatabaseName>]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [<DatabaseName>];
  • Ca sa revenim la conexiuni multiple dupa un SET SINGLE_USER
ALTER DATABASE [<DatabaseName>] SET MULTI_USER;
  • Ca sa obtin id-ul unui item proaspat inserat
    • SCOPE_IDENTITY
  • Variabilele de tip tabela (@results) nu ar trebui folosite decat pentru pana in 20-30 randuri.
  • Enable CLR on a restored database
USE [<DatabaseName>]
GO
 
--Enable clr on the SQLServer instance
SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO
 
--Make sure the databases owner has the proper rights
EXEC SP_CHANGEDBOWNER 'sa' 
GO
 
--Make the database trustworthy, so itll accept unsafe assemblies 
ALTER DATABASE [<DatabaseName>] SET TRUSTWORTHY ON
GO
 
--Grant the Encryption assembly the unsafe permission set, needed to 
ALTER ASSEMBLY [Encryption] WITH PERMISSION_SET = UNSAFE
GO
  • Login failed for user '<username>'. The user is not associated with a trusted SQL Server connection.
    • Probabil ca serverul e setat sa permita doar autentificarea Windows.
    • Properties pe server -> Security -> SQL Server and Windows Authentication mode
  • Inner queries: WHERE (NOT) EXISTS versus WHERE <Field> IN
    • Este de preferat sintaxa WHERE (NOT) EXISTS, pentru ca ii permite SQL Server sa se foloseasca de indecsii existenti.
  • Object '<Table/Field>' cannot be renamed because the object participates in enforced dependencies.
    • De verificat daca e vreun obiect creat WITH SCHEMABINDING.
  • SELECT * FROM <view> intr-o functie are probleme cand se adauga coloane la tabelele apelate in view
    • In cazul meu, si view-ul facea SELECT * FROM <table>. Cand am adaugat o coloana la tabela, functia intorcea tot setul vechi de coloane.
    • Am rezolvat asta recreand functia.
  • Folosirea de JOIN in DELETE
DELETE titleauthor
    FROM titleauthor 
    INNER JOIN titles ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
  • Folosirea de JOIN in UPDATE
UPDATE tbl1 SET tbl1.Name = tbl2.OtherName
FROM MyTable1 tbl1
INNER JOIN MyTable2 tbl2 ON tbl1.CampX = tbl2.CampY
delete o1
from   @O as o1
where  ACount = 0 
       and exists ( select  Month 
                    from    @O o2 
                    where   o1.Month = o2.Month 
                            and o2.ACount > 0)
  • PIVOT/UNPIVOT
  • INSERT INTO <Tabela> OUTPUT <si aici pot selecta niste coloane pe care sa le insereze in alta tabela, de ex Id> SELECT <date>
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '<TEXT>'
 
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
 
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END
 
SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results
  • Metadate despre tabele/proceduri stocate/etc
    • query pe sysindexes/sysobjects
    • select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows from sysindexes a inner join sysobjects b on a.id = b.id
  • RowCount dupa un INSERT/SELECT
    • SELECT @@RowCount
  • Cand vreau sa fac filtrari dupa parametri care pot sau nu sa aiba valori
    • WHERE (@minValue IS NULL OR Value >= @minValue)
declare @n char(1)
set @n = char(10)
 
declare @stmt nvarchar(max)
 
-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
from sys.procedures
 
-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.check_constraints
 
-- functions
select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + schema_name(schema_id) + '].[' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )
 
-- views
select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + schema_name(schema_id) + '].[' + name + ']'
from sys.views
 
-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys
 
-- tables
select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + schema_name(schema_id) + '].[' + name + ']'
from sys.tables
 
-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + schema_name(schema_id) + '].[' + name + ']'
from sys.types
where is_user_defined = 1
 
exec sp_executesql @stmt
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License