Metody pobierania daty i czasu w SQL Server

Silnik Sql Server posiada kilka metod do pobierania daty i czasu. Część z nich jest już przestarzała i nie powinno się z nich korzystać. SQL Server 2008 wprowadził kilka dodatkowych funkcji: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME. Wszystkie one pobierają czas z dokładnością do 100 nanosekund. Aby przekonać się co dokładnie zwracają najlepiej wywołać je:

select SYSDATETIME() as 'SYSDATETIME',SYSDATETIMEOFFSET() 'SYSDATETIMEOFFSET', SYSUTCDATETIME() as 'SYSUTCDATETIME';

image

Jak widać SYSDATETIEM zwraca po prostu systemowy czas (z dokładnością do 100 nanosekund). Z kolei SYSDATETIMEOFFSET oprócz czasu zwraca również przesunięcie strefy czasowej (w tym przypadku +2). Z kolei SYSUTCDATIME zwraca czas w systemie UTC.

Jeśli nie potrzebna jest nam aż tak wysoka dokładność lepiej skorzystać z funkcji GETDATE, GETDATEUTC lub CURRENT_TIMESTAMP. GETDATE i CURRENT_TIMESTAMP działają identycznie, różnią się wyłącznie nazwą oraz sposobem wywołania:

select GETDATE() as 'GETDATE',GETUTCDATE() as 'GETDATEUTC',CURRENT_TIMESTAMP as 'CURRENT_TIMESTAMP'

image

T-SQL i Grouping Sets

Prawdopodobnie wszyscy znają już klauzulę group by w zapytaniach SQL – nie stanowi żadnej rewelacji. Myślę jednak, że znacznie mniej popularniejszą klauzulą jest GROUPING SETS. Rozważmy następujące zapytanie:

select * from Products GROUP BY ProductCategory
union all
select * from Products GROUP BY ProductSubcategory

Zapytanie zwróci wiersze pogrupowane po kategorii produktu oraz podkategorii. Należy podkreślić, że grupowania wykonywane są niezależnie od siebie i nie mają charakteru hierarchicznego (tak jak to byłoby w przypadku umieszczenia kolumn w jednym GROUP BY). Używając Gruping Sets, powyższe zapytanie można zapisać:

select * from Products GROUP BY  GROUPING SETS ((ProductCategory),(ProductSubcategory))

Podobnie zapytanie:

select * from Products GROUP BY ProductCategory,ProductSubcategory;

można zastąpić:

select * from Products GROUP BY GROUPING SETS ((ProductCategory,ProductSubcategory));

Ponadto w nawiasach można wykorzystać ROLL UP oraz CUBE.

Porównywanie wartości NULL w SQL Server

Dzisiejszy post ma charakter raczej ciekawostki– w praktyce nic przydatnego nie wnosi:). Wiemy, że wartości NULL mają specjalne znaczenie – podstawowe operacje matematyczne zachowują się inaczej niż na zwykłych liczbach. Podobnie jest z porównywaniem NULL ponieważ służy do tego specjalny operator (IS NULL). Istnieje jednak w SQL Server zmienna, której ustawienie pozwala porównywać wartości NULL za pomocą klasycznych operatorów ‘<>==’. Rozważmy następujące zapytanie:

select * from Persons WHERE FirstName is null;

Ustawiając zmienną ANSI_NULLS na OFF można wykorzystać do tego celu operator ‘=’:

SET ANSI_NULLS off
select * from Persons where FirstName=null

Powyższe dwa fragmenty kodu działają identycznie. Bez ustawienia ANSI_NULLS na off zapytanie zwróciłoby zero wierszy ponieważ standardowo do porównywania NULL’i służy wyłącznie operator IS NULL.

W praktyce jednak uważam, że eleganckim rozwiązaniem jest wykorzystywanie is null zamiast kombinowania ze zmienną ANSI_NULLS. Post miał na celu wyłącznie pokazanie alternatywy :).

Suma kontrolna zwróconych wierszy

Czasami warto sprawdzić, czy wartości w podanych kolumnach zostały zmienione. Najłatwiejszym sposobem jest porównanie sumy kontrolnej. Jeśli stara wartość jest różna od aktualnej oznacza to, że któreś pole zostało zmienione. W T-SQL służy do tego funkcja CHECKSUM_AGG:

select CHECKSUM_AGG(Credit) from Persons GROUP BY Persons

Podobną funkcją jest CHECKSUM – liczy sumę kontrolną, jednak dla każdego wiersza a nie dla całych grup.

Analiza wydajności zapytań, część I

W SQL Server istnieje kilka narzędzi pozwalających określić wydajność wykorzystywanych zapytań. Podstawowym i najbardziej chyba znanym jest SQL Server Profiler. Można go włączyć z poziomu Sql Server Management Studio (menu główne->Tools->Sql Server Profiler). Po uruchomieniu pojawi się okienko w którym można określić m.in. zdarzenia, które powinny być monitorowane.

image

Następnie aby rozpocząć analizę należy wybrać Start Selected Trace (ikonka zielonej strzałki w ToolBar). Od tego momentu wszelkie zapytania wykonywane przez serwer bazodanowy będą wyświetlane w raporcie.

image

Podstawowa konfiguracja Profiler’a wyświetla m.in.:

  1. nazwę zdarzenia,
  2. treść zapytania,
  3. źródło zdarzenia (np. zewnętrzna aplikacja lub Management Studio),
  4. zużycie procesora,
  5. liczbę operacji odczytu,
  6. liczbę operacji zapisu,
  7. czas trwania zdarzenia (czyli np. wykonania zapytania),
  8. czas rozpoczęcia i zakończenia.

Jeśli któraś z metryk jest zbyt wysoka należy zastanowić się nad optymalizacja bazy lub zapytań. Na przykład w sytuacji gdy liczba odczytów jest zbyt wysoka można spróbować stworzyć dodatkowe indeksy. Warto regularnie uruchamiać profiler i monitorować ruch – pozwoli to zachować maksymalną wydajność. Szczególnie istotnymi pomiarami są te dokonywane już w realnym środowisku, na prawdziwych użytkownikach, ponieważ odzwierciedlają one najbardziej sposób wykorzystania aplikacji.

Porównywanie struktur baz danych

Często zdarza się, że mamy dwie takie same bazy: jedną lokalną do testów oraz drugą produkcyjną, umieszczoną na zdalnym hoście. Rozwijając aplikację, naturalne jest, że będziemy zmuszeni modyfikować bazę danych (dodanie nowych tabel, kolumn itp.). Wgrywając nową wersję na serwer łatwo zapomnieć o dokonanych zmianach na bazie. Na szczęście istnieją programy, które pozwolą nam zsynchronizować obydwie struktury. Jednym z nich jest dbForge Schema Compare for SQL Server.

Po uruchomieniu programu i wybraniu opcji “New Schema Comparison” określamy parametry baz (loginy, hasła, adresy).

image

Po dokonaniu porównania (przycisk Compare) zostanie wyświetlony szczególny raport:

image

Łatwo przejrzeć, które tabele się różnią oraz oczywiście ostatecznie zsynchronizować je.

Zasadniczą wadą programu jest fakt, że jest on płatny:). Można oczywiście ściągnąć wersję trialową. Jeśli ktoś zna jakieś darmowe alternatywy to proszę o sugestie w komentarzach.

ASP.NET MVC – pole autocomplete czyli Text Field z sugerowanymi wartościami

Czasami warto zasugerować użytkownikowi dozwolone wartości w TextBox. Przykładem jest pole edycyjne w Google, które pokazuje najczęściej wyszukiwane frazy. W tym poście stworzymy podobną kontrolkę, wykorzystując do tego bibliotekę jQuery. Całość działa oczywiście na Ajaxie. Użytkownik wpisuje jakieś wartości w polu, zapytanie jest w tle wysyłane do serwera a ten z kolei zwraca listę podpowiedzi. Należy wprowadzić również pewne opóźnienie przed dostarczeniem zapytania ponieważ w przeciwnym razie zostanie wysłanych zbyt wiele niepotrzebnych pakietów.

Najpierw umieszczamy pole edycyjne, które następnie będziemy rozszerzać o możliwość sugerowania podpowiedzi:

<input type="text" id="itemName" name="itemName" value='<%=Request.Form["itemName"] %>' />      

Następnie wystarczyć wstawić poniższy kod w jQuery:

<script type="text/javascript">
    $(function () {

        $("#itemName").autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: "/Tags/FindItems", type: "get", dataType: "json",
                    data: { term: request.term, maxResults: 20 },
                    success: function (data) {
                        response($.map(data, function (item) {
                            return { label: item.Title, value: item.Text, url: item.Url, type: item.Type, id: item.Id }
                        }))
                    }
                })
            },
            select: function (event, ui) {

         

            }
        });

    });
</script>

Jak widać większość rzeczy robi za nas jQuery. Warto zwrócić uwagę na argument url, który zawiera adres zapytania ajaxowego. To właśnie /Tags/FindItems zostanie wywołane w tle w celu pobrania sugerowanych wartości z serwera. Z kolei w ‘select’ możemy zapieścić kod JS, który będzie wywoływany po wybraniu przez użytkownika pozycji w liście.

Na zakończenie jeszcze treść metody FindItems:

public JsonResult FindItems(string term)
{
  Repositories.Search.SearchRepository repository = new Repositories.Search.SearchRepository();
  List<Repositories.Search.SearchResult> results = new List<Repositories.Search.SearchResult>();
  results.AddRange(repository.SearchPoliticians(term));
  results.AddRange(repository.SearchParties(term));

  return Json(results, JsonRequestBehavior.AllowGet);
}

Warto zwrócić uwagę na zwracany typ – JsonResult.