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.

EXECUTES AS in MS SQL

EXECUTES AS – SQL Serveris gali apibrėžti vykdymo kontekstą funkcijoms, procedūroms, eilėms ir trigeriams. Taip galima valdyti, kas prieina prie informacijos.

EXECUTES AS:

CALLER – default nustatymas.

OWNER – jeigu modulis neturi specifinio savininko, tai bus naudojamas schemos savininkas.

SELF – vykdo tas, kas sukūrė ir atnaujino.

SCHEMABINDING in MS SQL

SCHEMABINDING pribindina view prie lentelės ar lentelių, iš kurių sudarytas view. Tada pagrindinės lentelės negali būti atnaujinamos, atnaujinant view.

Jeigu UDF (User Defined Function) sukurta su SCHEMABINDING, tai useris turi turėti REFRENCES savybes į visus objektus, kurie naudojami UDF. Visi UDF ir view, jei jie naudojami šioje funkcijoje, taip pat turi būti sukurti naudojant SCHEMABINDING.

Implementing functions

Funkcijos padeda paslėpti dažnai naudojamą logiką. Funkcija gali turėti arba neturėti parametrų, o gražina – skaliarinę reikšmę arba lentelę. Nepalaiko output parametrų.



Fukcijų tipai:

- scalar functions (su RETURN grąžina vieną reikšmę. Negali grąžinti text, ntext, image, cursor ir timestamps tipo duomenų.)

- inline table-valued functions (grąžina lentelę – tai vieno SELECT rezultatas)

- multi-statement table-valued functions(grąžina kelios užklausos, panašu į stored procedūrą)

- build-in functions (naudoja build-in fukcijas. Negali būti keičiama. Gali būti deterministic arba nondeterministic tipo – priklausomai, kaip naudojama)



Function:

- Deterministic (visada grąžina tą patį rezultatą pagal skirtingus input parametrus);

- Nondeterministic (pagal specifinius input parametrus grąžina skirtingus rezultatus).



Execution context valdymas



EXECUTE AS – t.y. tam tikras fukcijas gali vykdyti tik tam tikri naudotojai.

EXECUTE AS USER

EXECUTE AS LOGIN

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.

Stored procedures in MS SQL

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

Views in MS SQL

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.

OLTP vs. OLAP

IT sistemas galime suskirstyti į transactional (OLTP) ir analytical (OLAP).

OLTP - saugo duomenis, vykdo daug on-line transakcijų (INSERT, UPDATE, DELETE).
OLAP - analizuoja, duomenis vaizduoja įvairiais pjūviais.

paimta iš: http://datawarehouse4u.info/OLTP-vs-OLAP.html

Skirtumai tarp OLTP ir OLAP:

	OLTP System Online Transaction Processing (Operational System)	OLAP System Online Analytical Processing (Data Warehouse)
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

source: www.rainmakerworks.com

OLAP systems really don’t like fragmentation, but OLTP systems do.

MS SQL indexing

Norint pagerinti užklausų veikimą, vienas iš svarbiausių aspektų - planuoti indeksų kūrimą. Norint indeksus naudoti - reikia suprasti tiek indeksų struktūrą, tiek kaip naudojami duomenys.

Pagrindiniai SQL serverio indeksai: heaps, clustered indexes ir non-clustered indexes. SQL Server 2008 atsirado full-text indexing.

SQL Sever duomenis nuskaito vienu iš šių būdų:
- nuskaitydamas visą lentelę (table scan);
- naudodamas indeksus (index).

Heap - lentelė be clustered index. SQL Sever naudoja Index Allocation Map (IAM) puslapius laikyti heap'ams. Heap'ai turi vieną eilutę sys.partitions sistemoje, kur index_id = 0. Pagal nutylėjimą heap turi single partition.

Heap in single partition (paimta iš 

 http://msdn.microsoft.com/en-us/library/ms188270(v=sql.105).aspx)

Clustered index - gali būti vienas lentelėje. Įgyvendintas kaip B-tree (balanced). Pagal šį indeksą būna surikiuoti duomenys. Kad geriau suprastume, tai knygoje atitinka turinį.

Clustered indeksai turi vieną eilutę sys.partitions sistemoje, kur index_id = 1.

Clustered indekas in single partition (paimta iš 

http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx)

Non-clustered index - irgi palaiko B-tree struktūrą. Gal lengviau būtų suprasti, knygoje non-clustered indeksus atitinka "rodyklė", t.y. kur pagal terminą nurodyta, kokiame puslapyje jis paminėtas.

Non clustered index in single partition (paimta iš http://msdn.microsoft.com/en-us/library/ms177484(v=sql.105).aspx)

Lentelė gali turėti 249 non-clustered indeksus. Non-clustered indeksai neįtakoja duomenų rikiavimo (duomenys būna surikiuoti pagal clustered index).

Composite index - naudojamas, kai dažnai ieškoma informacijos keliais būdais. Tokį indeksą gali sudaryti iki 16 stulpelių. Composite indeksas su stulpeliais (col1, col2) ne tas pats, kaip (col2, col1).

Partitioned index - indeksas horizontaliai padalijamas pagal rėžius.

Unique index - užtikrina stulpelio reikšmės unikalumą.

Full-text indexing - palaiko išplėstinės paieškos galimybes. Gali būti vienas indeksas lentelėje arba viewe. Jis gali turėti savyje 1024 stulpelius, kurių tipai: char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary(max).

Full-text indexing:
http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

Papildomi nustatymai

Kad indeksai sėkmingai veiktų ir gerintų užklausų veikimą, būtina pakankamai laisvos vietos. Jeigu bus mažai vietos, įsiterpus naujam įrašui, turinys gali pasiskirstyti dviejuose puslapiuose. Tam, kuriant indeksus, nurodomos dvi savybės:

FILLFACTOR - nurodo, kiek vietos procentais reikia lapų viršūnėms (tikslas - sumažinti page splitting):
- naudojamas mažas FILLFACTOR OLTP aplikacijose;
- naudojamas aukštas FILLFACTOR OLAP aplikacijose.

PAD_INDEX - apibrėžia, kiek vietos reikia neindeksuotų lapų viršūnėms.

PAD_INDEX naudojams tik kartu su FILLFACTOR.

How to add global.asax file

http://www.aspdotnet-suresh.com/2011/05/how-to-add-globalasaxcs-file-in-aspnet.html

Upload large files in ASP.NET

<requestLimits maxAllowedContentLength="1024000000" />

http://www.telerik.com/help/aspnet-ajax/upload-uploading-large-files.html

Kaip nuskaityti XML dokumentą?

Dim oDoc As New XmlDocument()

oDoc.Load(fuXmlFile.FileName)

Dim pNum As String = oDoc.DocumentElement.GetElementsByTagName("title")(0).InnerText

Dim arMovies = oDoc.DocumentElement.GetElementsByTagName("movie")

For i As Integer = 0 To arMovies.Count - 1

    Dim oMovie = arMovies.Item(i)

    oMovie.Attributes.GetNamedItem("src").Value

Next

JavaScript: onFocus, onBlur

onfocus priešingas onblur

$(document).ready(function () {

            $('#txtHours').val("Hours");

        });

function onBlur() {

            if ($('#txtHours').val() == "") {

                $('#txtHours').val("Hours");

            };

        };

function onFocus() {

            if ($('#txtHours').val() == "Hours") {

                $('#txtHours').val("");

            };

        };

<asp:TextBox ID="txtHours" runat="server" Width="70px" AutoCompleteType="Disabled" Visible="false" onfocus="onFocus();" onblur="onBlur();"></asp:TextBox>

Kaip nutraukti sesiją ASP.NET?

Paleisti issreset

https://support.gearhost.com/KB/a191/how-to-reset-iis-with-the-iis-reset-command.aspx

Lentelės replikuojamoje ir nereplikuojamoje DB

Nereplikuojamoje:

 Replikuojamoje duomenų bazėje atsiranda papildomas stulpelis rowguid.