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

Aggregate functions cannot be used in SET list of update statements
- In loc de agregate, folosesc subqueries
- Vezi http://www.swissql.com/products/sybase-to-sqlserver/help/manual_migration_assistance.html

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 database’s owner has the proper rights
EXEC SP_CHANGEDBOWNER 'sa' 
GO
 
--Make the database trustworthy, so it’ll 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.

Cautarea unei coloane cu un anumit nume intr-o baza
- http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1089808,00.html
- De uitat in view-urile de sistem ( SELECT name FROM sys.all_views WHERE is_ms_shipped = 1 )
- Poate merge un join intre sys.all_objects si sys.all_views)

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

ROW_NUMBER
- http://msdn.microsoft.com/en-us/library/ms186734.aspx

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 
    FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 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>

Cautare text in toata baza de date
- http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

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)

Drop all objects from database
- from http://blog.falafel.com/Blogs/AdamAnderson/09-01-06/T-SQL_Drop_All_Objects_in_a_SQL_Server_Database.aspx , updated to support other schemas than dbo.

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

page revision: 15, last edited: 16 Nov 2011 10:33

Edit Tags History Files Print Site tools + Options

My Wiki