Category: SqlServer

TILT- tables–part 4

I will keep very simple the database schema.

I do not want for the moment an users table, so I just create

  1. a table with url

  2. a table with the tags
  3. a table with notes

Azure Data Studio

Trying Azure Data Studio – good point that it is integrated with Azure. Bad point – finding it misses diagrams – https://docs.microsoft.com/en-us/sql/azure-data-studio/faq?view=sql-server-2017#database-administration

Not good – I do prefer making relations between tables. I can drop later, but for now let’s go with SSMS

And this is the diagram conceived


Tools used:

Azure Data Studio Sql Server Management Studio

Use the right language for the job–if you know

Many years ago I have done a site that lists the exchange rates from BNR and BCE ( www.infovalutar.ro ). It reads the exchanges from BNR html page and then put into a database. The use is for programmers – there are many methods to find latest exchange rates ( RSS, SOAP, url, by MVC, JSON… –  see  all there : http://infovalutar.ro/programatori )

I keep the values in a simple table like

IDMoneda    Valoare    DataValorii
EUR    3.5842    2006-07-07 00:00:00
EUR    3.5892    2006-06-29 00:00:00
EUR    3.5887    2006-07-13 00:00:00
EUR    3.5942    2006-07-19 00:00:00
EUR    3.5642    2006-07-20 00:00:00
EUR    3.5672    2006-07-24 00:00:00

As you see . the value of the date is not contiguous – in the weekends is not a new exchange rate.

Now, the problem:  How can we detect if there is an error with parsing the values  ? The usual error is putting different values from NBR ( maybe they have also some csv file to parse ?). For example take this

IDMoneda    Valoare    DataValorii
RSD    0.0396    2019-01-17 00:00:00
RSD    0.0397    2019-01-18 00:00:00
RSD    0.0398   2019-01-21 00:00:00
RSD    3.98E-06    2019-01-22 00:00:00
RSD    4.02E-06    2019-01-23 00:00:00
RSD    4.02E-06    2019-01-25 00:00:00

How can we find where the difference starts for the currency ? ( We assume a relatively stable Romanian economy …. not like Venezuela )

The answer how to calculate where the problem is relatively easy: the percentage of how much the value has varied : ( CurrentValue  – Last_Value)/ Last_Value * 100 .  It should not pass 1 or , maximum 2 % .

But how to calculate this ?

For C# – or any other OOP programs- it is normal to read record by record , order by IDMoneda and DataValorii – and calculate the percentage. However , C# is not made for this kind of calculus.

Then we can see that is a database involved – and we  think about SQL . It is a complicated SQL –because of the fact that the dates are not contiguous – because of the Weekends. However , there is a simple SQL construct, LAG:https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2016

select
abs(LAG(Valoare,1,0)Over(Partition BY IDMoneda Order BY DataValorii) – Valoare)/Valoare *100 ,Valoare , from CV_Valori

order by 1 desc

This query executes in seconds and we can see the problems fast

Sq- if you know the tool , please use in your current task!((

If you ask me about functional programming(F#) my answer it will be DSL)

Import large Excel file into SqlServer

I have to import the list of NGO from Romania to SqlServer.  Before seeing my solution , please do it yourself: the file could be found at http://www.just.ro/registrul-national-ong/ and the name is Asociatii

I have tried with SSMS – no luck ( text will be truncated, even when I put nvarchar(max))

The I tried with flat file from CSV  – same

Finally, I have solved with a little bit of VBA : first I have tried to write the insert into file and execute with sqlcmd. The file was too large, so sqlcmd does not report any errors( and not insert anything)

So the solution was to generate a batch file that have this

sqlcmd  -S . -E -d InfoRo -Q “INSERT INTO [dbo].[Asociatii$]  ([Denumire]  ,[Numar inreg Reg National],[pozitie inchisa (da/nu)],[Starea actuala],[Judet],[Localitate],[Adresa],[Asociati/Fondatori],[Scop],[Consiliu director],[Apartenenta federatie],[HG utilitate publica],[Data HG utilitate publica],[F14])values( ////)”

generated by this VBA script

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Option Explicit
 
Sub x()
Dim startInsert As String
 
startInsert = "sqlcmd  -S . -E -d InfoRo -Q ""INSERT INTO [dbo].[Asociatii$]"
         startInsert = startInsert & "  ([Denumire]"
         startInsert = startInsert & "  ,[Numar inreg Reg National]"
           startInsert = startInsert & ",[pozitie inchisa (da/nu)]"
           startInsert = startInsert & ",[Starea actuala]"
           startInsert = startInsert & ",[Judet]"
           startInsert = startInsert & ",[Localitate]"
           startInsert = startInsert & ",[Adresa]"
           startInsert = startInsert & ",[Asociati/Fondatori]"
           startInsert = startInsert & ",[Scop]"
           startInsert = startInsert & ",[Consiliu director]"
           startInsert = startInsert & ",[Apartenenta federatie]"
           startInsert = startInsert & ",[HG utilitate publica]"
           startInsert = startInsert & ",[Data HG utilitate publica]"
           startInsert = startInsert & ",[F14])values("
            
            
           Dim i As Long, j As Integer, text As String
           Dim insert As String
           For i = 2 To 86813
            insert = insert & startInsert
            For j = Asc("A") To Asc("M")
                text = Sheet1.Range(Chr(j) & i).text
                text = Replace(text, vbCr, " ")
                text = Replace(text, vbLf, " ")
                text = Trim(text)
                 text = Replace(text, "'", "''")
                text = Replace(text, "  ", " ")
                 
                insert = insert & "'" & text & "',"
            Next j
            insert = insert & "null)" & """"
            insert = insert & vbCrLf
            If (i Mod 150 = 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
           Next i
           If (Len(insert) > 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
           
            
           
            
      
End Sub

Maintaining history of data

 

What is this about?

I tend to quote Fowler: “Usually when we see properties on a class, they represent questions we can ask of an object now. However there are times when we don’t want to just ask questions about a property of an object now, we also want to ask these questions about some point in the past when things may have changed. “

For example , we want to track every change that occurs at an Employee ( changing name, salary) and see the previous versions( what was the salary 2 years ago ?)

So what are the software solutions to do an audit log ? First, we should have a history table for each table that we want to maintain an audit log. E.g. Employee => EmployeeHstory

Solution 1 : Coding / Event Sourcing

That means code should maintain history himself – i.e. adding to the history table every modifications.

For Entity Framework this can be done easy, by overiding SaveChanges.

Demo at https://youtu.be/YZ9GDjFApns 

Solution 2 : Table Triggers

This can be done from database . There are triggers for insert , update, delete – and those triggers insert the data in the history table

Demo at https://youtu.be/9xsvCJ-1f7Q

Solution 3, Temporal Tables, Microsot  Sql Server 2016 :

Documentation at https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables 

Limitations at https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations 

Demo at https://youtu.be/1Q3DmXJI5h8

Monitor SqlServer Stored Proc execution time

I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see http://msdn.microsoft.com/en-us/library/ms178598.aspx )
The code is for sql server 2005 and I have not realized without the help of http://sqlserver.ro/forums/thread/8332.aspx
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :

?
1
2
3
4
5
6
CREATE TABLE [Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProcName] [varchar](128)
    [IdentifierAccess] [varchar](max)
    [DateAccess] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]

2. Stored proc to insert data :

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
alter proc dbo.proc_Log(@procname varchar(100))
 
as
begin
declare @userid varchar(100)
BEGIN TRY
select @userid=client_net_address from sys.[dm_exec_connections] with(nolock)
where session_id = @@SPID
End TRY
BEGIN CATCH
--GRANT SELECT ON sys.dm_exec_connections TO the user or
--grant view server state to the user ! http://msdn.microsoft.com/en-us/library/ms186717.aspx
set @userid = SUSER_NAME()
End CATCH
 
INSERT INTO [Audit]
(
[ProcName]
,[IdentifierAccess]
)
VALUES
(@procname,
@userid
)
end

3. In each stored procedure put this at beginning

?
1
2
3
4
5
DECLARE @ProcName nvarchar(128)
 
    SET @ProcName = OBJECT_NAME(@@PROCID)
 
    exec proc_Log  @ProcName

And this at the end of stored proc :

?
1
exec proc_Log  @ProcName

4. See what the procedure calling are and the time (basic)

?
1
2
3
4
5
select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess)
from [Audit] a1  inner join [Audit] a2 on
a2.ID =
( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID )
order by 1

From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)

Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)

Sql Server Management Studio free awesome addons

The two addons that I can not live without in Sql Server and that, more , are free :

  1. SSMS Tools Pack – now 1.7.5.1 , http://www.ssmstoolspack.com/Download
  2. SQL Search – now1.0 http://www.red-gate.com/products/SQL_Search/

 

The SSMS Tools Pack maintains a history of your commands . More, it saves on the C:\SSMSTools\SQLQueryActionLog

image

 

The Sql Search finds a text in the objects in the database – very useful if you decide to change a column name and find all stored procedures that have this reference

image

Andrei Ignat weekly software news(mostly .NET)

* indicates required

Please select all the ways you would like to hear from me:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.