MS SQL: alter column type

Alter table [Table_test] Alter Column Name nvarchar(2000)


Darbas su replikuotom duomenų bazėm: https://msdn.microsoft.com/en-us/library/ms151870.aspx

MS SQL: LIKE

Problema:


Neveikia LIKE '%Dovilė%'






Sprendimas:


Reikia nurodyti, kad naudosime unicode LIKE N'%Dovilė%'


Tas pats su = ir <>

MS SQL View

VIEW kūrimas

Negali turėti daugiau kaip 1024 stulpelių

Negali naudoti COMPUTE, COMPUTE BY, INTO

Negali naudoti ORDER BY be TOP

Sys.views – sąrašas viewų duomenų bazėje

Sp_helptext – apibrėžia non-encrypted view

Sys.sql_dependencies – objektai (tame tarpe ir view), kurie priklauso kitiems

objektams

Duomenų keitimas view’e

Atnaujinat view, duomenys pasikeičia ir pagrindinėje lentelėje

Negalima keisti stulpelių, kurie sudaryti naudojant GROUP BY, HAVING,

DISTINCT

Indexed view

Indeksuotiems view’ams palaikyti reikia daugiau resursų, nei paprastiems indeksams.

Indeksuoti view’ai ir partitioned view gali pagerinti veikimą.

Norint indeksuoti view’ą, būtina naudoti SCHEMA_BINDING

Pirmasis indeksas view’w turi būti unikalus.

Indeksuoti viewai geriausiai veikia, kai būna retai atnaujinami duomenys lentelėse. Jei

duomenys bus labai dažnai atnaujinami, indeksuotą view’ą palaikyti gali tapti per

daug brangu.

Partitioned view

Į partitioned view galima sujungti horizontaliai padalintus duomenis iš vieno ar kelių

serverių.

Partitioned view neturi būti maišomi su indeksuotais viewais, sukurtais partitioned

schemoje.

Stored procedures

Stored procedūra –metodas, kuris inkapsuliuoja dažnai pasikartojančias užduotis. Jos

palaiko apibrėžtus kintamuosius, sąlyginius vykdymus.

Stored procedūrų kūrimas panašus į view kūrimą – pirma sukuriam norimą užklausą,

o po to – įdedame ją į procedūrą.

Jeigu procedūra sukurta naudojant WITH ENCRYPTION, tą pačią sintaksę naudoti

reikia ir atnaujinat procedūrą.

Parameterized stored procedures

Gali turėti trijų rūšių komponentus:

Input parameters,

Output parameters,

Return values.

Execution plans

Execution planai parodo, kaip naudojamos lentelės, view’ai, indeksai vykdant

užklausas.

Execution planai turi du pagrindinius komponentus:

- Query Plan

- Execution Context (specifiniai parametrai, pagal kuriuos vykdo užklausas. Šie

parametrai skirtigi kiekvienam naudotojui – nes norima skirtingų duomenų)

Execution planai nėra parodomi encrypted stored procedures ir trigeriams.

Užklausos kompiliavimas, kai nėra kešuojami execution planai:

Parsing-> algebrized Tree -> Compilation -> Optimization

Stored procedūras galima rekompiliuoti:

- Sp_recompile

- WITH RECOMPILE on execution

- WITH RECOMPILE at creation

Handling Exceptions

Struktūrinis klaidų gaudymas bendras daugelyje programavimo kalbų, tokių kaip

Microsoft Visual Basic, Visual C#. Transact-SQL dažnai naudojamas klaidų

gaudymas su TRY… CATCH.

Violation of Primary Key constraint. Cannot insert duplicate key in object error when using Item List

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2014/05/12/violation-of-primary-key-constraint-cannot-insert-duplicate-key-in-object-error-when-using-item-list.aspx

MS SQL Database Read Only, Read/Write

Make Database Read Only

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT
GO

Make Database Read/Write

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT
GO

MS SQL: DATEDIFF

Pvz., skaičiuoti dienoms

select DATEDIFF (DAY,'20130701','20140430')

MS SQL: ciklas, palengvinantis atnaujinimą

declare @lastint integer = 44
declare @currint integer = 12

WHILE (@currint<@lastint)
BEGIN

UPDATE [SERVERIS].DuomenuBaze.dbo.EIF_questions
SET QLTText=(SELECT QLTText FROM EIF_questions where ID=@currint)
WHERE ID=@currint;

SET @currint = @currint + 1

END

Atnaujinti dalį duomenų 

insert into [SERVERIS].DuomenuBaze.dbo.EIF_questions(ID, Col1, Col2, Col3)(select ID, Col1, Col2, Col3 from dbo.EIF_questions where ID>43)

Kaip konvertuoti vieną stulpelio tipą į kitą tipą

ALTER TABLE table_name ADD new_column_name decimal(18,2)

update table_name
set new_column_name = convert(decimal(18,2), old_column_name)

ALTER TABLE table_name DROP COLUMN old_column_name

decimal(4,1) vs. decimal(4,2)

(4,1): 4 - kiek pozicijų iš viso, 1- kiek pozicijų iš jų skirta po kablelio. t.y. max skaičius 999,9.  (4,2): max skaičius 99,99

Drop column from replicated table

Naudojame sp_repldropcolumn:

sp_repldropcolumn 'TableName', 'ColumnName' 

http://technet.microsoft.com/en-us/library/ms190489.aspx

Collation types

SQL Serveris turi dviejų tipų collations: Windows collations and SQL Server collations.

BIN2 sorts and compares data in Microsoft SQL Server tables based on Unicode code points for Unicode data. For non-Unicode data, binary-code point uses comparisons that are identical to binary sorts. The advantage of using a binary-code point sort order is that no data re-sorting is required in applications that compare sorted SQL Server data. As a result, a binary-code point sort order provides simpler application development and possible performance benefits.

BIN_CS sorts and compares data in SQL Server tables based on the bit patterns defined for each character, however re-sorting is required on the application end.

CI_AS_KS and CI_AI do not provide automatic sorting on the application end.

http://msdn.microsoft.com/en-us/library/ms175194%28v=sql.105%29.aspx

http://msdn.microsoft.com/en-us/library/ms143515%28v=sql.105%29.aspx - Collation sorting styles

Pvz., jeigu buvo pakeista duomenų bazės collation, tada papildomai reikia perkurti visus ryšius ir indeksus, atitinkamai, jeigu keitėsi stulpelių collation.

Changing the collation at the column level requires some work, particularly if the tables have indexes and other constraints defined. If indexes and other constraints exist on the target column, those objects must be dropped before changing either the collation property or the data type.

Dropping indexes and constraints of pre-existing tables, changing column collation, and implementing required indexes and constraints again is the correct choice.

Dropping indexes and constraints of the preexisting tables where collation needs to be changed does not implement any change to the collation of data.

Auto-indexing is not an option.

Norint padaryti JOIN tarp dviejų lenetelių, reikia kad abu stulpeliai turėtų tą patį collation.

To make a join, the collation and data types of both columns should match.

The JOIN clause in

EUROPE  INNER JOIN US ON EUROPEREVENUE.ProductType = USREVENUE.USProductType COLLATE Greek_CI_AS

has the same column name, the same collation, and the same data types on both columns used for the join.

NOEXPAND

NOEXPAND  - naudojamas indeksuotuose viewuose, užtikrinti, kad vykdant užklausą bus lengvai prieinamos viewui reikalingos lentelės.

For the query optimizer to consider indexed views for matching or use an indexed view that is referenced with the NOEXPAND  hint, the following SET  options must be set to ON:

 * ANSI_NULLS
 * ANSI_PADDING
 * ANSI_WARNINGS
 * ARITHABORT
 * CONCAT_NULL_YIELDS_NULL
 * QUOTED_IDENTIFIERS

The NUMERIC_ROUNDABORT  option must be set to OFF.

Ranking functions

NTILE - atrinktas eilutes išskirto į grupes

DENSE_RANK - grąžina rinkinį eilučių be tarpo rikiavime. Jei bus dvi lygiavertės eilutės, tai rikiavimo rezultatas būtų toks: 1, 1, 2, 3...

ROW_NUMBER - grąžina tiesiog išrikiuotas eilutes

RANK - jeigu bus dvi lygiavertės eilutės, jos gaus tą patį numerį, bet atitinkamai atsiras tarpai rikiavime (pvz. bus dvi eilutės su numeriu 1, tai rikiavimo rezultatai: 1, 1, 3)

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

CTE

CTE – pirmiausia atsirado Server 2005.

WITH < CTE name > [  ( < column name > [ ,...n ]  )  ]

AS

(  < query returning tabular data >  )

< statement that will make use of the CTE >

CTE negali būti naudojami:

❑ COMPUTER and COMPUTE BY

❑ ORDER BY

❑ INTO

❑ The FOR XML, FOR BROWSE, and OPTION query clauses

CTE nekontroliuoja, kokia infomacija grąžinama – į ją tik sudedama informacija. Norint kontroliuoti, ką grąžiname, rašome atskirą SELECT’ą iš CTE ir išvedame kokius norime stulpelius, rikiuojame pagal poreikį.

Pavydžiui, jeigu turime lentelę Employees, kurioje yra du stulpeliai:

empName,

department 

Reikia išvesti empName, department tik tų skyrių, kuriuose daugiau nei 10 darbuotojų. Su CTE tai padaryti lengviau - reikės mažiau resursų:

WITH DepCounts (department, employees)
AS ( SELECT department, COUNT(*)
        FROM Employees
        GROUP BY department)
SELECT e.empName, e.department, d.employees
    FROM Employees AS e
    JOIN DepCounts AS d ON e.department = d.department
    WHERE d.employees > 10;

Tą patį galime padaryti su laikina lentele, bet reikės daugiau resursų:

SELECT empName, department,
    (SELECT COUNT(*) FROM Employees WHERE department = e.department)
    FROM Employees AS e
    WHERE 1 < (SELECT COUNT(*) FROM Employees
        WHERE department = e.department);

CTE naudojamas kas kart, kai vykdoma užklausa. Jeigu bus vykdoma daug SELCT ar INSERT operacijų - geriau naudoti laikinąsias lenteles.

Query hints

<  query_hint   > : : = 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | EXPAND VIEWS 
  | FAST number_rows 
  | FORCE ORDER 
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | MAXDOP number_of_processors 
  | MAXRECURSION number 
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | USE PLAN N'xml_plan'
  | TABLE HINT ( exposed_object_name [ ,  [ [, ]...n ] ] )
}

OPTIMIZE FOR - nurodo optimizuoti užklausos vykdymą, kai nurodoma konkreti reikšmė

OPTIMIZE FOR UNKNOWN - optimizuoja užklausas naudojantis statistika

KEEP PLAN - užtikrina, kad užklausa nebus perkompiliuojama taip dažnai, kaip dažnai vykdomi lentelės atnaujinimai: UPDATE, DELETE, MERGE, INSERT.

KEEPFIXED PLAN -užtikrina, kad užklausa bus perkompiliuota, jeigu pasikeis lentelės schema arba bus vykdoma sp_recompile.

PARAMETERIZATION { SIMPLE | FORCED } - veikia vykdymo planą, bet ne pačią užklausą.

 FAST number_rows - užklausa optimizuojama greitai grinti pirmuosius rezulatus. Kai grąžinami pirmieji rezultatai, užklausa vykdoma, kol bus grąžinti visi likę rezultatai.

MAXDOP number - optimalus paralelizmo lygis.

HASH GROUP - query hint specifies that aggregations described in the GROUP BY, DISTINCT, or COMPUTE clause of the query should use hashing.

ROBUST PLAN  vykdo planą maksimaliam potencialių eilučių dydžiui (? forces the query optimizer to try a plan that works for the maximum potential row size).

CUBE ir ROLLUP

CUBE http://msdn.microsoft.com/en-US/library/ms175939%28v=sql.90%29.aspx

ROLLUP http://msdn.microsoft.com/en-US/library/ms189305%28v=sql.90%29.aspx

Service Broker

Service Broker

Service broker palaiko infrastruktūrą, kuri padeda konstruoti sprendimus:

• Database oriented
• Saugumas
• Patikimumas
• Scalable – keičiamo dydžio
• Loosely coupled – laisvai kartu
• Distributed

Service Broker sujungia komponentus, kad įvykdytų užduotį.

ServiceBroker turi prioritetus. Prioritetai priskirti prie conversation endpoint, kai sukuriami endpointai. Žinutės su dideliais prioritetais užklausose įvykdomos greičiau.

Transactions and locks

Transactions and locks

Transakcija – tai seka operacijų, vykdomų kaip vienas vienetas. Transakcija turi keturias savybes:

• Atomicity – vykdomi kaip vienas vienetas
• Consistency – kai transakcija baigta, duomenys turi būti nuoseklūs
• Isolation – pakeitimai, daromi transakcijos, turi būti izoliuoti nuo kitų operacijų.
• Durability  - pakeitimai turi būti ilgalaikiai

Visi transakcijų pakeitimai rašomi į logą, kad esant reikalui būtų galima atstatyti pradinius duomenis.

Lock tipai:

• Read
• Write

Concurency control:

• Pessimistic
• Optimistic

Type of locks: (10-29,10-30)

Shared(S) – skaitymo operacijoms

Update(U) – resursams, kurie gali būti atnaujinami

Exclusive (X) – naudojami duomenų modifikavimo operacijoms, tokioms kaip INSERT, UPDATE arba DELETE

Intent – naudojama norint įkurti lock hierarchiją

Schema – naudojama, kai opracija priklauso schemai, kurioje yra naudojama lentelė

Bulk Update (BU) – naudojama, kai bulk kopijuoja duomenis į lentelę ir TABLOCK hint naudojamas.

Key-range – saugo tam tikrą rėžį eilučių nuo skaitymo, kol vykdoma transakcija.

Lock compatibility:

http://msdn.microsoft.com/en-us/library/ms186396(SQL.105).aspx

Transaction isolation level:

• Read uncommitted
• Read committed (default)
• Repeateble read
• Serializable - visas kitas transakcijas sustabdo ir vykdo skaičiavimus. Laikinai duomenys kitiems atnaujinimams ir įterpimams neprieinami.
• Snapshot

Lock escalation – lock paaštrėjimas – t.y. procesas, konvertuojantis ribojimų grandinėlę ir taip padidina konkurenciją tarp resursų (?)

Jeigu būna deadlockai, tai SQL serveris automatiškai nutraukia vieną transakciją, o kitai leidžia tęsti darbą.

Dynamic management view:

SELECT * FROM sys.dm_tran_locks

Managed Databases Objects, aggregates and user-defined types

Managed Databases Objects

Namespace->Classes->Methods

CREATE PROCEDURE Person.UpdatePhoneList AS

EXTERNAL NAME Contacts.SaveList – nuoroda į assembly

Duomenų bazės objektą atnaujiname normaliai:

EXEC Person.updatePhoneList

Managed aggregates and user-defined types

CREATE AGGREGATE Concatenate (@input nvarchar(4000))

RETURNS nvarchar(4000)

EXTERNAL NAME Utilities.[Utilities.Concatenate]

Duomenų bazės objektas naudojamas normaliai:

SELECT AccountNumber, dbo.Concatenate(SalesOrderNumber) Orders

FROM Sales.SalesOrderHeader

GROUP BY AccountNumber

Su CLR aggregatais galima sukurti sudėtingus skaičiavimus (ne tik SQL galimus MAX, SUM, AVG) ir duomenų tipus.

Su CLR tipais galima saugotiir valdyti sudėtingus kintamuosius ir spatial data.

Implementing Managed Code in database

CLR (Common Language Runtime) – sluoksnis .NET Frameworke, kuris leidžia kurti programas ir procedūras bet kuria .NET kalba ir tai vykdyti.

SQL CLR Integration leidžia sukurti duomenų bazės objektus, kurie pagerina funkcionalumą.

Managed Code vs. Transact-SQL (9-9)

Darbas su eilutėmis CLR yra greitesnis nei T-SQL.

Transact SQL:

• Efektyvus priėjimas prie duomenų
• Nedidelė logika
• Gali vykdyti tik su SQL Server aplinka
• Gali išplėsti stored procedūras

Managed code:

• Sudėtingi skaičiavimai
• Sudėtinga procedūrinė logika
• Vykdo su SQL serverio arba kliento procesu
• Palaiko aukštesnį sistemos integralumą, negu išplėstos stored procedūros

Assembly:

• sukompiliuotas kodas, kuris palaiko tipus, metodus ir meta duomenis;
• vienas, naudojamas įdiegimui ir kodo naudojimui;
• Vykdomas per SQLCLR.

Assembly – tai DLL failai, kurie sukuriami kompiliuojant .NET kalbos, tokios kaip VB arba C# failus, palaikančius logiką.

Assembly trust levels:

• Machine policies
• User policies
• Host policies

Permissions:

• SAFE
• EXTERNAL_ACCESS
• UNSAFE

Trust lygiai gali būti priskirti Windows accountams.

Trust lygiai kontroliuoja priėjimą per assembly prie išorinių resursų.

CREATE ASSEMBLY assembly_name

[AUTHORIZATION owner_name]

FROM {}

[WITH PERMISSION SET = {SAFE | EXTERNAL_ACCESS | UNSAFE}]

[;]

TRUSTWORTHY – duomenų bazių savybė, rodanti, kad galima pasitikėti išoriniais šaltiniais. Dažnai naudojama su EXTERNAL_ACCESS.