PostgreSQL external sort performance I'm trying to figure out external sort performance and it's dependence on WORK_MEM value.
I've tested that increasing WORK_MEM doesn't always speed up external sorting (example below, tested on PostgreSQL 9.6).
Is there any guidance on setting WORK_MEM, say what value of WORK_MEM is optimal to sort 4GB table using external sort?
Is it possible to estimate number of IOs from table size and WORK_MEM setting?

I understand that i can study tuplesort.c and underlying algorythms, is there ready results?

create table t1
as
select
  c as key
, cast('value' as char(5)) as val
from generate_series(1, 100000000) c;

--4223 MB
select pg_size_pretty(pg_total_relation_size('t1'));

\timing

--time - 146725.833 ms
SET WORK_MEM = '128MB';
create table t_128 as select * from t1 order by key;

--time - 148889.655 ms
SET WORK_MEM = '1024MB';
create table t_1024 as select * from t1 order by key;

Yes. You can see the output of external sorts when doing EXPLAIN ANALYZE (and adding in BUFFERS and others according to the EXPLAIN documentation, so you don't really have to estimate if you can run these. Using your examples:

postgres@[local]:5432:postgres:=# create table t1
postgres-# as
postgres-# select
postgres-#   c as key
postgres-# , cast('value' as char(5)) as val
postgres-# from generate_series(1, 100000000) c;
SELECT 100000000
Time: 162609.861 ms
postgres@[local]:5432:postgres:=# SET work_mem="128MB";
SET
Time: 0.317 ms
postgres@[local]:5432:postgres:=# EXPLAIN (ANALYZE, BUFFERS) create table t_128 as select * from t1 order by key;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=13476557.48..13672503.59 rows=78378445 width=28) (actual time=38358.696..66282.785 rows=100000000 loops=1)
   Sort Key: key
   Sort Method: external merge  Disk: 1954200kB
   Buffers: shared hit=2083 read=538461 dirtied=508619, temp read=244397 written=244397
   ->  Seq Scan on t1  (cost=0.00..1324325.45 rows=78378445 width=28) (actual time=0.108..17185.459 rows=100000000 loops=1)
         Buffers: shared hit=2080 read=538461 dirtied=508619
 Planning time: 74.660 ms
 Execution time: 206961.514 ms
(8 rows)

Time: 207239.156 ms
postgres@[local]:5432:postgres:=# SET WORK_MEM = '1024MB';
SET
Time: 0.192 ms
postgres@[local]:5432:postgres:=# EXPLAIN (ANALYZE, BUFFERS) create table t_1024 as select * from t1 order by key;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=16537253.59..16787253.81 rows=100000088 width=10) (actual time=30379.785..51937.967 rows=100000000 loops=1)
   Sort Key: key
   Sort Method: external merge  Disk: 1954944kB
   Buffers: shared hit=540541, temp read=244382 written=244382
   ->  Seq Scan on t1  (cost=0.00..1540541.88 rows=100000088 width=10) (actual time=0.013..7570.784 rows=100000000 loops=1)
         Buffers: shared hit=540541
 Planning time: 0.121 ms
 Execution time: 172406.698 ms
(8 rows)

Time: 172527.526 ms
postgres@[local]:5432:postgres:=#

We can see that the sorts going on are external (on-disk), and they take up roughly 2GB. Now disk is much, much slower than memory, so there might be more of a speedup if we get it doing the whole sort in memory.

Now, if we go a really ludicrous route, we can change the sort characteristics, like this:

postgres@[local]:5432:postgres:=# SET work_mem='20GB';
SET
Time: 0.188 ms
postgres@[local]:5432:postgres:=# EXPLAIN (ANALYZE, BUFFERS) create table t_20g as select * from t1 order by key;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=14828266.59..15078266.81 rows=100000088 width=10) (actual time=20532.705..31367.756 rows=100000000 loops=1)
   Sort Key: key
   Sort Method: quicksort  Memory: 7833229kB
   Buffers: shared hit=540541
   ->  Seq Scan on t1  (cost=0.00..1540541.88 rows=100000088 width=10) (actual time=0.013..7758.434 rows=100000000 loops=1)
         Buffers: shared hit=540541
 Planning time: 0.044 ms
 Execution time: 139461.981 ms
(8 rows)

Time: 139503.860 ms
postgres@[local]:5432:postgres:=#

Which shaves 67499.533ms (or about 67.5 seconds) off the total update from the 128MB version of the sort mem, for a difference of about 32% or so. This changed from the external merge (intermediate results sorted on disk), to quicksort in memory.

The IO that is being done to execute this query is shown by adding the  BUFFERS parameter to EXPLAIN, which gives you hit (read from shared block cache), read (read from disk), dirtied (changed), and written (written to disk).

The optimal work_mem depends on a lot of factors. It's easiest to log_temp_files and use that as a starting point for adjusting the work_mem per query, and set it to something like 2-4x the size of your largest temp file. Though the setting is applied per each query (to sorts and hash tables as well), so don't make it too large unless you have to. Setting it per query on something really large like this is probably more appropriate.

tuplesort.c does actually go into detail about how it's sorted, and if you want to delve deeper than what is shown in EXPLAIN ANALYZE, then you can look at things with dtrace/systemtap/eBPF or other dynamic tracing facilities that would show you more in-depth how PostgreSQL is sorting. Or you could add a bunch of ereport lines in a custom compiled version depending on what you're interested in.

You can also get a lot more detail on work_mem effects in this post Understanding postgresql.conf : work_mem, if you're interested in additional tests and their results.


To find SQL Server is actually used or not? I have an SQL Server 2012 and a 2008R2 running in my environment. I need to decommission the server if the server is not actually being used.

I did go through this and this where connection count is one of the parameters to check the same, and I did run that and the result was 0 but is that concrete enough for to me do away with this server? Thing is I do not have any ownership details of this server to even proceed further, so I will have to see in the server itself.

There are chances the database could have been restarted sometime back, which I'm not sure. Auditing and logon triggers have been disabled at present. Should I enable them? If yes, which auditing specifically I will have to enable? And what logon trigger?

There aren't many databases in the instance, about 3-4 only.
What can be done to come to a concrete conclusion to stop this instance from running?

SQL Server can log failed logins, successful logins, both or none to the SQL server log.  Check your current setting and if it is not collecting both failed and successful logins change it so that it is.  Once it's been running that way for a few weeks (preferably over a month end & quarter end) check to see if there has been any activity.  If no one is logging in then you can run a final backup of your databases (you should probably include the master and msdb databases as well as the user databases).  Move those backups to long term storage and shut down the instance.  If no one complains after a while then you can de-com.

Under the server properties:


Enabling page compression on 1.5TB table I need to come up with the best approach possible to enable page compression on a table 1500 GB in size, with 363,957,740 rows. The database size itself is 1.71 TB and holds archive data.

If I understand correctly, it needs disk space (possibly the same amount, just to be on the safer side) so that it can create a copy of the table with page compression enabled and release the space. This is in FULL recovery model so, it will be heavily logged.

I have spoken to my capacity planning resource and he agreed to give additional temporary required space for this maintenance, and take back the space once this activity is complete. Having said this, do you think the best approach to be:

Take a full backup
Restore the backup onto new temp drives
Change the recovery model to SIMPLE
Enable page compression
Take a full backup after compression and restore original database
Change recovery model to FULL

Also, instead of enabling page compression. After Step #3, truncate the largest table, enable page compression and then do a

SELECT * INTO ReplicaDB.dbo.ReplicaTbl

Does this enable page compression on existing indexes?

I do not have Test environment to test the above steps. Alternatively, if any better approach is available then please let me know.

The goal is to minimize the future disk space required at its current growth. We are an ERP software company and we have licensed Enterprise Edition. This table is only for archive when some checks are performed and all the data resides in this table. I have 2 indexes (1 CI, 1 NON-CI). None of the columns are VARCHAR (MAX), they are either NVARCHAR, int or date type.

Your plan sounds well except for this one:

  Also, instead of enabling page compression. After Step #3, Truncate
  the largest table, enable page compression and then do a SELECT * INTO
  ReplicaDB.dbo.ReplicaTbl ?

If you truncate a table and enable a page compression, you must do INSERT and not SELECT INTO and at this point you should use TABLOCK to get page compression and not row compression only.

Besides this, rebuilding with compression is much faster that an insert into a compressed table, we have a difference in 3-5 times on large tables.

Have a look here: The Data Loading Performance Guide, section Data Compression and Bulk Load:

  When data is bulk loaded into a compressed table or partition, both
  page-level and row-level compression are generally done at bulk load
  time. However, you should be aware of an exception: When bulk loading
  into a page compressed heap, you must use the TABLOCK hint to achieve
  page compression. If the TABLOCK hint is not used, only row level
  compression is done on the heap. Using page compression on a bulk
  target will generally slow down bulk load speeds – especially if the
  I/O system is able to supply enough speed to saturate the CPUs.


How do I make Postgres use an index when using IN or ANY? I have a query such as:

select col1 from mytable where col1 in('a','b','c')

But running such a query yields a Seq Scan per EXPLAIN

If I have an index on col1, why does Postgres not do an index scan?

I don't think that it's failing to use the index, but that it has a lot to do with a combination of factors:

number of rows in the table
number of unique values in col1
query planner tuning (seq_page_cost,random_page_cost,etc.)

It could very well be that the lowest-cost plan involves a Sequential Scan, at least for this situation.

On my machine, I get the following:

edb=# create table mytable (col1 text);
CREATE TABLE
edb=# create index mt_idx on mytable(col1);
CREATE INDEX
edb=# insert into mytable values ('a'),('b'),('c');
INSERT 0 3
edb=# explain analyze select col1 from mytable where col1 in ('a','b','c');
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on mytable  (cost=8.62..18.90 rows=20 width=32) (actual time=0.087..0.131 rows=3 loops=1)
   Recheck Cond: (col1 = ANY ('{a,b,c}'::text[]))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on mt_idx  (cost=0.00..8.62 rows=20 width=0) (actual time=0.058..0.071 rows=3 loops=1)
         Index Cond: (col1 = ANY ('{a,b,c}'::text[]))
 Planning time: 0.124 ms
 Execution time: 0.290 ms
(7 rows)

edb=# insert into mytable values (generate_series(1,1000000)::text);
INSERT 0 1000000
edb=# analyze;
ANALYZE;
edb=# explain analyze select col1 from mytable where col1 in ('a','b','c');
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using mt_idx on mytable  (cost=0.42..13.33 rows=3 width=6) (actual time=0.039..0.105 rows=3 loops=1)
   Index Cond: (col1 = ANY ('{a,b,c}'::text[]))
   Heap Fetches: 0
 Planning time: 0.223 ms
 Execution time: 0.211 ms
(5 rows)

If you think that the query planner is erroneously choosing a Sequential Scan, please share more details about your data and test case (though from my experience, whenever I question the query planner's choices, I'm usually wrong).

You may also wish to SET enable_seqscan = off and try the EXPLAIN again, and see the cost of using the Index, and compare with the cost of the Sequential Scan.


Error while restoring database in SQL Server 2008 R2 This error occurs while I restore database from .bak file

  A system assertion check has failed. Check the SQL Server error log
  for details. Typically, an assertion failure is caused by a software
  bug or data corruption. To check for database corruption, consider
  running DBCC CHECKDB. (Microsoft SQL Server, Error: 3624)

After this the Database goes offline and it property sets to Single User.

If SELECT @@VERSION < 10.50.1746.0 then it is possible that the issue you are coming across has been fixed in a specific cumulative update, and can be averted by updating your instance.

The particular issue is described here:

KB #980832 : FIX: "A system assertion check has failed" error message...

The cumulative update is here:

Cumulative Update #4 for SQL Server 2008 R2 RTM

But I recommend applying the most recent service pack and cumulative update available (since they come with a slew of other fixes as well):

SQL Server 2008 R2 Service Pack 2

Cumulative Update #4 for SQL Server 2008 R2 Service Pack 2


How fast can I pull data through a SQL Server client connection? What is the highest throughput possible? Has anyone seen anything above 25MB/sec?

I've been trying to figure out what is even possible.

I know there are tuning parameters like packet size, and rows per batch and so one, plus there is overhead involved if going over TCP.
I can copy a file at sustained 250MB/sec.
I haven't found any way to pull > 16MB/sec or so through a query.

I got bcp to do about 25MB/sec by saving to a flat file. But no luck on a query that is simply selecting from a large table (53GB).
I've been experimenting on Google Compute instances with various versions of SQL Server, using maxed out machines and SSDs etc. (32 cores, 200GB mem, and so on)

Is there some theoretical maximum SQL Server can handle on one connection? 25MB/sec seems stupidly slow.

Some things to try:

Wider data sets - if you're using a narrow table (just a few fields), SQL Server may be waiting for the client app to ack the data and request the next set of rows. Since you're doing experimentation, try building a table with just two fields: an identity, and a CHAR(7000) filled with a string.
Pull data directly from cache - size your VM so that it can hold, say, 32GB of data in the buffer pool (try 64GB memory, for example), read the table up into cache first, and then try to pull it across the network. This isn't a practical long-term fix, obviously - but if you're bottlenecked reading your data files from disk, then it's time to start tuning that.
Avoid intermediate sorts - try pulling table contents directly with a SELECT rather than joining multiple tables together and/or ordering the result sets. Again, not a practical fix, just showing how you can get higher throughput for bandwidth testing.
Try your tests locally first - which just helps to rule out that it's not a bottleneck on the app you're using to pull data across the wire, or network settings between VMs. (Plus, there are limits on network throughput on client VM instances, too - make sure you're using a large enoguh client VM.) If the local test runs fast but the network test is slow, try copying a file locally from the client VM to a shared drive on the SQL Server. If that's slow, then there's your gotcha.
Check your top wait type - use sp_BlitzFirst @ExpertMode = 1 (Github repo) (disclaimer: I'm the author), and look in the Wait Stats section. If you see ASYNC_NETWORK_IO as your top wait type, the client is the bottleneck (or a slow network). If it's PAGEIOLATCH*, then it's waiting to read the data pages from the data file.


Oracle Script For Getting Long Active Sessions Is there any script that you could provide for v$session? I seem to be encountering an issue in which my oracle active sessions are filled up. This is when I do

SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE';

Here is my problem : I need a script which could determine how long the connection was held and who was holding it.

I wonder where I'm getting it. If possible, it would be really nice if I could autodial these sessions. I'd like to know how long they were held since the last command was done. Another thing to note: There is no current statement associated with the SQL sessions I'm seeing.

Thanks

Presumably they are active and doing things so looks like you might need to get more info about your sessions before killing them off. I usually use a query like this to get important info about active sessions including how long they have been logged on (oldest at top), and also how long they have been running the current SQL.

SELECT USERNAME,
       TERMINAL,
       PROGRAM,
       SQL_ID,
       LOGON_TIME,
       ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
       ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
  From v$session
 WHERE STATUS='ACTIVE'
   AND USERNAME IS NOT NULL
ORDER BY MINUTES_LOGGED_ON DESC;


I accidentally set password to blank for a PostgreSQL database, how do I change it? When I set the password to blank, it didn't give any warnings. Now when I try to log in with a blank password, it gives me an error "fe_sendauth: no password supplied". So now I can't log in - any ideas how I can get access to the db again?

This blogpost should answer. Full disclosure: it's my blogpost.

Summary:

  If you have access to shell account on the machine PostgreSQL is running, and your shell works as the same user as Postgres itself, or root – solution is easy.

  Edit your pg_hba.conf file, and at the beginning of it put:

  local all all trust

  or (depending on your paranoia):

  local all postgres ident

  And restart your PostgreSQL (usually something like /etc/init.d/postgres restart).

  Afterwards you should be able to connect to Postgres as postgres user without password.

  You should note, that if you have choosen the option with “ident” you will be able to connect without password only from shell account named “postgres”.

  don't forget to reverse the pg_hba.conf settings when you have reset the password.


How to use a list in a list in report builder? I'm currently using Microsoft SQL Report Builder to create a custom report for my data. I have a table of customers, a table of devices and a table of jobs (each customer has a couple of devices, each device has a couple of jobs).

What I want is a list of customers, and when I click on a customer it should expand and show all associated devices, and when I click on the device it should expand and show all jobs.

Should I just use a list in a list in a list? Or should I embed a subreport inside my list? Should I split all 3 tables in different datasets? Or just use 1 dataset?

I imagine there are a lot of different options (I did my research online) but there seems to be little consensus about whether it's a good idea to use subreports or not for example.

In my humble opinion, it depends on the complexity of the details view.

If I'm showing a sample list of values in each view then I'd rather use a List inside a List, inside a List.

For example:

[+] Customer Id + Customer Name
    [+] DeviCe Id + Device Name
        |------------|-----------------|
        | Job Id Job | Job Description |
        |------------|-----------------|
        |------------|-----------------|
        |------------|-----------------|

But, if the details view have a more complex structure, like graphics + tables or matrix, then I use a subreport.


MySQL 5.7 not accepting values of innodb_buffer_pool_instances greater than 1 I've been tweaking the values of the buffer pool size (innodb_buffer_pool_size) and the number of buffer pool instances (innodb_buffer_pool_instances) in the my.ini initialization file to optimize heavy load handling with MySQL. I can successfully change the buffer pool size, both dynamically and with the my.ini file, but no matter what I change the innodb_buffer_pool_instances value to, it is always 1 when I query it with SELECT @@innodb_buffer_pool_instances;

What I've already tried:

I made sure to set the buffer pool size > 1GB. I've set it to 24GB and verified that it is indeed that big.
Made sure I'm using the correct my.ini file location.
After changing the value in my.ini I restart the MySQL server.
I've even tried restarting my computer after changing the value, but it still always remains 1.

What am I missing? Why can't I change the value of innodb_buffer_pool_instances?

Fixed it. I just reinstalled MySQL and now the variable updates just like it should. Don't know why it wasn't working, but I don't really care since it works now.


How to create SQL Server Agent Queued jobs? There is a perfmon counter "Queued jobs" in perfmon object "SQLAgent:Jobs".

How to create a job queue that will reflect in this perfmon counter?

Can I get this queued job info from somewhere else like DMV?

The SQL Server Agent has a maximum worker thread setting that limits the total number of threads that can run jobs at any given time. When you hit this limit, you will get a warning message that the thread limit has been hit and the job queued for execution. When this occurs, that counter increments to record the fact that there is a queued job.

This is also true for job steps, which have their own worker thread limit for each subsystem and a performance counter under SQLAgent:JobSteps to record the queued steps.

This queueing mechanism is not something you can manually initiate or control, it is an in-built queueing mechanism to manage the worker threads used by SQL Agent.

See here for more info. The max worker thread count is # of CPUS*100. Run sp_enum_sqlagent_subsystems to see the max worker threads for individual subsystems.

You can test this out yourself, though it is easier to test the subsystem queue behaviour than the SQL Agent job queue behaviour.

Create a number (6 or more, depends on the limit for the PowerShell subsystem) of one-step SQL Agent jobs to run a PowerShell command (Start-Sleep -Seconds 60) and execute them simultaneously. Job Activity Monitor will show something similar to below

And if you're monitoring the performance counter SQLAgent:JobSteps - Queued Steps, you will see it increase:


Error: FATAL: no pg_hba.conf entry for host - PostgreSQL - pg_hba.conf is empty I upgraded the PostgreSQL database from version 10 to 11. As I tried to access the database from a client pgadmin4 console, I got the below error:

I checked the two file postgresql.conf and pg_hba.conf in the system. However, when I tried to open the pg_hba.conf file, it comes up blank. I followed the steps for the upgrade (for Ubuntu users)in the link below:

https://stackoverflow.com/questions/24379373/how-to-upgrade-postgresql-from-version-9-6-to-version-10-1-without-losing-data

I know this error might get resolved if I allow privileges in the pg_hba.conf file. But that file does not show any data in it. I am a beginner in setting up database servers and could have unknowingly skipped something which I cannot figure out. Can anyone please help me with this?

Adding the image for pg_hba.conf file:

Upgrading a database with either dump/restore or pg_upgrade will not copy the configuration files, so you are left with whatever pg_hba.conf was there right after initdb created the new cluster.

You have to edit and adapt postgresql.conf and pg_hba.conf and other configuration files you use manually on the new cluster. With pg_hba.conf copying the file over is usually good enough, but postgresql.conf requires more attention.


I don't know how to fix ORA-04091 mutating trigger error I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table.

The error is:

  error ORA-04091: table USUARIO.LINEAS is mutating, trigger/function may not see it ORA-06512: at "USUARIO.TRG_ACTUALIZARPEDIDO", line 5 ORA-04088: error during execution of trigger 'USUARIO.TRG_ACTUALIZARPEDIDO'

I've learning about it, but can't find a solution or understand by myself what's the problem.

CREATE OR REPLACE TRIGGER trg_actualizarpedido
AFTER INSERT OR UPDATE OR DELETE ON LINEAS
FOR EACH ROW

DECLARE
    sumaImporteLineas NUMBER := 0;

BEGIN
    SELECT SUM(IMPORTE) INTO sumaImporteLineas FROM LINEAS WHERE NUMPEDIDO = :NEW.NUMPEDIDO;
    UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas WHERE ped.NUM = :NEW.NUMPEDIDO;
END;

Thanks!

ORA-04091

This is Oracle's way of saying:

  you really don't want to do that.

Here is my short-list of workarounds.

Option 1

Use a VIEW.

This is the preferred method.

Option 2

Use a Materialized View.

Yes, you can Fast Refresh SUM() on commit.

Option 3

Use a TAPI (Table API). (a Package)

All DML operations must go through this Package; never against the actual table.

Option 4

3-Trigger solution.  (This could be a single Complex trigger.)

Without proper locking, you could have "lost updates".

Even with proper locking, Dead Locks could occur.


How is it possible we have rows in the database that violate a unique constraint? Recently We meet a mysql error on slave, because a transaction can not be executed on slave.

We found two records have the same unique in master, but inserted successfully.

I have post two screenshot of my situation, Could you give me any clues about how can this be happened?

My thought is that the constraints were disabled during the load. This allows for faster insert of data but can cause issues like this.

If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.


Preventing SQL injection in dynamic SQL Let's imagine a stored procedure that retreives data and do some kind of pagination. This procedure has some inputs describing which set of data we want and how we sort it.

Here is a very simple query, but let's take it as an example.

create table Persons(id int, firstName varchar(50), lastName varchar(50))
go
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+'
order by '+@orderBy+' '+@orderDir

exec(@sql)

It is supposed to be used like that:

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id', @orderDir = 'desc'

But a smart guy could launch:

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id)a from Persons)t;delete from Persons;print''', @orderDir = ''

... and drop data

That's obviously not a secure situation. And how could we prevent it ?

Note: this question is not about "is it a good way to do pagination ?" nor "is it a good thing to do dynamic sql?". The question is about preventing code injection when building sql queries dynamicaly in order to have some guidelines to make the code a bit cleaner if we have to do similar stored procedures again in the future.

Some basic ideas:

Validate inputs

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName')
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+'
order by '+@orderBy+' '+@orderDir

exec(@sql)

Pass ids instead of strings as inputs

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy tinyint = 1, @orderDir bit = 0
as

declare @orderByName varchar(50)
set @orderByName =  case @orderBy when 1 then 'id'
                        when 2 then 'firstName'
                        when 3 then 'lastName'
                    end
                +' '+case @orderDir
                        when 0 then 'desc'
                        else 'asc'
                    end

if @orderByName is null
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+'
order by '+@orderByName

exec(@sql)

Any other suggestions?

In your example code, you are passing three categories of "things" into your dynamic SQL.

You pass @OrderDir, which is a keyword to signify ASC or DESC.
You pass @OrderBy, which is a column name (or potentially a set of
column names, but based on the way #1 is implemented, I assume you
expect a single column name.
You pass @PageNumber and @PageSize, which become literals in
the generated string.

Keywords

This is really straightforward--you just want to validate your input. You're spot-on that this is the right thing for this option. In this case, you are expecting either ASC or DESC, so you can either check that the user passes one of those values, or you can switch to a different parameter semantic, where you have a parameter that is a toggle switch. Declare your parameter as @SortAscending bit = 0, then within your stored procedure, translate the bit into either ASC or DESC.

Column names

Here, you should use the QUOTENAME function. Quotename will ensure that objects get properly [quoted], ensuring that if someone tries to pass in a "column" of "; TRUNCATE TABLE USERS", it will get treated as a column name, and not an arbitrary piece of injected code. This will fail, rather than truncating the USERS table:

SELECT [; TRUNCATE TABLE USERS]...
FROM...

 Literals & parameters

For @PageNumber and @PageSize, you should be using sp_executesql to pass parameters properly. Properly parameterizing your dynamic SQL allows you to not only pass values in, but also to get values back out.

In this example, @x and @y would be variables scoped to your stored procedures. They aren't available within your dynamic SQL, so you pass them into @a and @b, which are scoped to the dynamic SQL. This allows you to have properly typed values both inside and outside the dynamic SQL.

DECLARE @i int,
 @x int,
 @y int,
 @sql nvarchar(1000),
 @params nvarchar(1000);

SET @x = 10;
SET @y = 5;
SET @params = N'@i_out int OUT, @a int, @b int';
SET @sql    = N'SELECT @i_out = @a + @b';

EXEC sp_executesql @sql, @params, @i_out = @i OUT, @a = @x, @b = @y;
SELECT @i;

Even with varchar values, keeping the value as a variable prevents someone from arbitrarily passing code that gets executed. This example ensures that the user input gets SELECTed, and not arbitrarily executed:

DECLARE @UserInput varchar(100),
         @params nvarchar(1000) = N'@value varchar(100)',
         @sql nvarchar(1000)    = N'SELECT Value = @value';

SET @UserInput = '; TRUNCATE TABLE USERS;'
EXEC sp_executesql @sql, @params, @value = @UserInput;

My code

Here's my version of your stored procedure, with table definition & a few sample rows:

CREATE TABLE dbo.Persons
       (
        id INT,
        firstName VARCHAR(50),
        lastName VARCHAR(50)
       );
GO

INSERT INTO dbo.Persons(id, firstName,lastName)
VALUES (1,'George','Washington'),
       (2,'John','Adams'),
       (3,'Thomas','Jefferson'),
       (4,'James','Madison'),
       (5,'James','Monroe')

ALTER PROCEDURE dbo.GetPersons
       @pageNumber INT = 1,
       @pageSize INT = 20,
       @orderBy VARCHAR(50) = 'id',
       @orderDir VARCHAR(4) = 'desc'
AS
       SET NOCOUNT ON;

--validate inputs
IF NOT EXISTS ( SELECT   1 FROM     sys.columns
                WHERE    object_id = OBJECT_ID('dbo.Persons')
                AND name = @orderBy )
BEGIN
        RAISERROR('Order by column does not exist.', 16,1);
        RETURN;
END;

IF (@orderDir NOT IN ('ASC', 'DESC'))
BEGIN
        RAISERROR('Order direction is invalid. Must be ASC or DESC.', 16,1);
        RETURN;
END;

--Now do stuff
--sp_executesql takes in nvarchar as a datatype
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT id, firstName, lastName
FROM (
    SELECT id, firstName, LastName, ROW_NUMBER() OVER(ORDER BY '
           + QUOTENAME(@orderBy) + N' ' + @orderDir + N') AS rn
    FROM dbo.Persons
    ) t
WHERE rn > ( @pageNumber-1) * @pageSize
        AND rn <= @pageNumber * @pageSize
ORDER BY ' + QUOTENAME(@orderBy) + N' ' + @orderDir;

EXEC sys.sp_executesql @sql, N'@pageNumber int, @pageSize int',
                   @pageNumber = @pageNumber, @pageSize = @pageSize;

GO

You can see here, that the code is functional & gives you the proper ordering & pagination:

EXEC dbo.GetPersons @OrderBy = 'id', @orderDir = 'DESC';
EXEC dbo.GetPersons @OrderBy = 'id', @orderDir = 'ASC';
EXEC dbo.GetPersons @OrderBy = 'firstName';
EXEC dbo.GetPersons @OrderBy = 'lastName';
EXEC dbo.GetPersons @PageNumber = 2, @PageSize = 1, @OrderBy = 'lastName', @orderDir = 'ASC';

And also see how the input handling protects against someone trying to do strange stuff:

EXEC dbo.GetPersons @OrderBy = 'lastName', @orderDir = 'UP';
EXEC dbo.GetPersons @OrderBy = ';TRUNCATE TABLE Persons;';

 Additional Reading

sp_executesql example

Aaron Bertrand's Bad Habits to Kick : Using EXEC() instead of sp_executesql

Aaron Bertrand's kitchen sink procedure


Problem with foreign key between 3 tables. (PgAdminIII) I'm using pgAdmin III for PostgreSQL but I have a problem with foreign keys between 3 tables. My tables are:

Locale with a primary key IDnegozio (integer type, no need to make it serial type)
Orario with a primary key IDorario (integer type, no need to make it serial type)
OrarioToLocale with two columns: IDlocaleT & IDorarioT (both integer).

I'm trying to make a foreign key between IDlocale -> IDlocaleT and IDorario -> IDorarioT so, I can have the relation that a Shop can have multiples time.
The problem is that when I try to make these foreign keys, and an error occurs me:
"no unique constraint matching is given keys for referenced table" and I don't understand why. I tried to google but I didn't find any answer!
How can I do?

This is the code of the three tables:

CREATE TABLE public."Locale"
(
  "IDnegozio" integer NOT NULL DEFAULT nextval('"Locale_IDnegozio_seq"'::regclass),
  CONSTRAINT "Locale_pkey" PRIMARY KEY ("IDnegozio"),
)

CREATE TABLE public."Orario"
(
  "IDorario" integer NOT NULL DEFAULT nextval('"Orario_IDorario_seq"'::regclass),
  CONSTRAINT "Orario_pkey" PRIMARY KEY ("IDorario")
)

CREATE TABLE public."OrarioToLocale"
(
  "IDlocaleT" integer NOT NULL,
  "IDorarioT" integer NOT NULL
)

How can I do? Thank you advice!

It would have been good if you provided the foreign keys that failed. The following should work:

ALTER TABLE public."OrarioToLocale"
    ADD CONSTRAINT FK_... FOREIGN KEY ("IDorarioT")
        REFERENCES public."Orario" ("IDorario");

and a similar constraint for Locale. Why do the attributes in OrarioToLocale end with a T?


Lossless join in 3NF I was just studying about the 3NF and the BCNF and then encountered a statement that:

  3NF ensures lossless decomposition while BCNF does not.

How can we verify this using the basic definition of BCNF and 3NF?

The statement:

3NF ensures lossless decomposition while BCNF does not.

is incorrect, since both BCNF and 3NF produce decompositions that have the Lossless-join Decomposition property, that says that:

(R1,R2) is a lossless-join decomposition of R with respect to a set of FDs F if for every instance r of R that satisfies F:
πR1(r) ⋈ πR2(r) = r.

This can be seen since there is a theorem that says that a simple criterion for checking whether a decomposition (R1,R2) is lossless-join is that either:

R1 ∩ R2 → R1 ∈ F+, or
R1 ∩ R2 → R2 ∈ F+,

and both the algorithms for BCNF and 3NF produce decompositions that satisfies this property (see the literature on the subject, for instance the demonstration on Chapter 3 of: “Garcia-Molina, Hector. Database Systems: The Complete Book, Pearson Prentice Hall”).
What is true, and that differentiate 3NF from BCNF, is that the synthesis algorithm that produces the 3NF always preserves the dependencies of the original relation, while the analysis algorithm for the BCNF does not.

A decomposition (R1, R2) of R, given
FR1 ={X → Y | X → Y ∈ F+ ∧ XY ⊆ R1}, and
FR2 ={X → Y | X → Y ∈ F+ ∧ XY ⊆ R2}
preserves a dependency f iff f ∈ (FR1 ∪ FR2)+.


How performance schema in mysql 5.6 improve performance? Since MySQL 5.5 performance_schema database has been added likewise information_schema.
As we know that tables in information_schema contains statistical information like tables, plugins, partitions, processlist, status and global variables etc.
Just like, performance_schema contains a lot of tables.

But, how can we use these tables to improve performance of an
application based on MySQL 5.6?

Please note the difference between the information_schema and performance_schema databases
INFORMATION_SCHEMA
The information_schema database is an inventory of all objects within the MySQL instance
Such objects include:

databases
tables
columns
constraints
indexes (called statistics)
processlist
locks

I wrote a nice post about this 3 years ago : How is INFORMATION_SCHEMA implemented in MySQL?
PERFORMANCE_SCHEMA
The performance_schema database is an instrumentation platform that collects statistics about phases of a running query. Such info can give you hints to tune the query or the environment. You only need to enable it when debugging and troubleshooting queries in conjunction with adding indexes (may or may not be necessary) and adjusting buffer settings (again, may or may not be necessary).
I have not written much about performance_schema since I do not use it that often (Mentioned in my old post Is there any way to get the estimate cost of executing a query in MySQL?).
Derek Downey also wrote a quick post on it : What is the usage of performance_schema in MySQL 5.5?
There is a wealth of information on how to use the performance_schema I think you will enjoy

PERFORMANCE_SCHEMA vs Slow Query Log
PERFORMANCE_SCHEMA in MySQL 5.6: The Good, The Bad and The Ugly
A Visual Guide to the MySQL Performance Schema


Sql Server Agent is part of the sysadmin role even tough Sql Server says it's not I created a Windows account with no extra rights other than domain user and set it as the SQL Server Agent service account with the SQL Server Configuration Manager.

The BoL/MSDN says that this user should be part of the sysadmin role to work but at this point I haven't taken any action to make this happen.

I expected that the account could do nothing but it appears it's ... a sysadmin! If you put the following statement in a job step it will confirm this:

IF IS_SRVROLEMEMBER ('sysadmin') = 1 print 'sysadmin'

The job step is a t-sql step with sa as owner so the SQL Server Agent service account is used.

Now for the question. It seems SQL Server Configuration Manager does something that makes this a sysadmin or at least when it is executing the step. BUT at the same time it does not show up in the list of the sysadmins in the GUI.

All the reports or queries I come up with (including SSMS) do not list this Windows account as part of the sysadmin role.

I have the feeling I am overlooking something.

After some reading I think I got it.

"NT SERVICE\SQLSERVERAGENT" is a member of the local group "SQLServerSQLAgentUser$RINUS$MSSQLSERVER".
This group has al the right that a sql server agent account needs

"NT SERVICE\SQLSERVERAGENT" is a part of the sql server fixed sysadmin role

"NT SERVICE\SQLSERVERAGENT" can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.

The dedicated account that I have created for the agent is set for the windows service

If you connect the dots you will get a chain that explains this. The part thats new for me is that the NT SERVICE\SQLSERVERAGENT can represent the dedicated agent account.

Sources :

http://blogs.msdn.com/b/varun_sharma/archive/2007/08/30/how-to-run-sql-server-agent-and-sql-server-jobs-with-least-privilege-in-sql-server-2005.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e6bb2de-8fd0-45de-ab02-d59bbe05f72e/servicedatabase-accounts-nt-servicemssqlserver-nt-servicesqlserveragent-what-are-they-for?forum=sqlsecurity


Copy 8 Million Records I have a table in a local MS SQL Server database that has 72 columns and over 8 million records.
Most of the columns are floats except for the primary key which is an INT Identity column.
The table also has 3 indexes.

These records are the saved output from a utility I wrote that processed around 100 Billion calculations on different target audiences. I have saved the best and worst results for each target audience for future lookup.
I would now like to move all these records into my staging server's database.

What is the best way to copy transfer these records?

I tried Red Gate, and it just dies and says out of disk space.
My disk has 22 GB free.
Thanks.

Your operation will most likely be a logged operation as you're performing an insert.

Monitor the log file growth and you should see that's what is utilizing your remaining disk space.

Consider inserting using a minimally logged approach, these include:

BULK INSERT
INSERT...SELECT
MERGE with Trace Flag 610

I have also found an insert to a column with an identity specification will also cause logging to occur.

Links:
http://blogs.msdn.com//b/sqlserverstorageengine/archive/2008/02/05/bulk-logging-optimizations-minimal-logging.aspx
http://www.sqlmag.com/article/tsql3/minimally-logged-inserts

Also: I would recommend inserting into a heap and then apply your indexes unless your data has been pre-sorted in the source database.

Consider defining the identity value in your source database, select all the data into the destination and then apply the identity specification and set the seed.


Flash Recovery Area full I have oracle 11g that runs on Linux serve.
I have a Flash Recovery Area, I backup my archive logs to another location.

presently I have very little space left in my Flash Recovery Area (FRA) thereby resulting in my archivelog getting filled up quickly and making my DR unable to receive Redo logs.

I wish to free some space in the FRA.
please which of the directories in the FRA Can I touch in order to free some space.

Here is the information of the directories in my FRA
autobackup   3150
archivelog   6962
controlfile  81
datafile     567774
onlinelog    13338

I know  I can't touch the control file.
am new here and afraid to mess things up.

if I can't delete files from any of these directories to free some space, what is my possible alternative of getting some more space in my FRA.

Keep in mind that Oracle manages the FRA space and will delete files if they are no longer needed, to make space.  You can have FRA 90% used, and it's OK.  For example, archive logs may be backed up to tape, but they will remain in the FRA for faster access if the space is not needed by other files.

First - check for reclaimable space with SELECT * FROM V$RECOVERY_FILE_DEST;. The NAME field will either be a disk path, or an ASM disk group.  If space is reclaimable, then the files should automatically be cleaned up.

Second - check for free space on the device that stores your FRA (NAME from V$RECOVERY_FILE_DEST).  If you have space, you can increase the amount of space you allow FRA to use (see DB_RECOVERY_FILE_DEST_SIZE).  https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams061.htm#REFRN10235

  DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the flash recovery area.

More information is needed to know if any of the files can be cleaned up.


MySQL Conversion to DATE Type What is the difference between the return values for these two expressions in MySQL:

date('2011-01-01')

cast('2011-01-01' AS DATE)

There is no difference between the two, you get a date field in both instances


MYSQL - Retrieving weeks from month I'm trying to list all weeks in a month, (e.g., 2015-02-01 to 2015-02-28) in the following format:

Tried using WEEK e.g., SELECT WEEK(2015-02-01) - WEEK(2015-02-28); but this just gave me the number of numbers in the month - 4.

What is the proper MYSQL statement to achieve this

I have written a stored routines called month_weeks. It accepts two parameters, the month and the year and output a list of weeks. It uses a temporary table to calculate the start and the end of weeks. For example:

call month_weeks(1,2015);

returns:

# WeekFrom, WeekTo
2014-12-29, 2015-01-04
2015-01-05, 2015-01-11
2015-01-12, 2015-01-18
2015-01-19, 2015-01-25
2015-01-26, 2015-02-01

The text of routine is:

DELIMITER $$
CREATE PROCEDURE month_weeks(
  IN month INT,
  IN year INT)
BEGIN
  drop table if exists tmp;
  create temporary table tmp(WeekFrom date, WeekTo date) engine=memory;
  set @s_month:=STR_TO_DATE( concat('01-', convert(month, char), '-', convert(year, char)),'%d-%m-%Y');
  set @e_month= last_day(@s_month);

  set @s_week:=date_add(@s_month, interval (-dayofweek(@s_month)+2) DAY);
  set @e_week:=date_add(@s_week, interval 6 DAY);
  while datediff(@e_month, @s_week) >= 0 do
      insert into tmp values(@s_week, @e_week);
      set @s_week:=date_add(@s_week, interval 7 DAY);
      set @e_week:=date_add(@e_week, interval 7 DAY);
  end while;
  select * from tmp;
END $$
delimiter ;


SSIS flat file import - Issue with datetime format I have a flat file with datetime format "5/19/2017 05:22:23.777 PM", and when I try to insert this data into a table using SSIS transformation I am getting these errors:

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column ""DT_Date"" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Flat File Source.Outputs[Flat File Source Output].Columns["DT_Date"]" failed because error code 0xC0209084 occurred, and the error row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns["DT_Date"]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

The target column is defined as datetime.
Please help me on this, any help is really appreciated.
How can I solve this issue?

Try adjusting your input column type to DT_DBTIMESTAMP2.  I've seen this fix similarly annoying Date conversion issues.  Here's a full list of what those input types mean.


Should I define a FOREIGN KEY column as NULLable? I've been developing some database structure to cover the following business rules:

An invoice has many invoice_items.
An invoice may have many refunds
A refund has many invoice_items.
All items should be from the same invoice.
A refund should always have items.

So I came up with the entity-relationship diagram (ERD) shown bellow:

I'd like to see which invoice_items are included in the refund.
However, in this case, it would not be possible to add a FOREIGN KEY (FK) (InvoiceItems => refunds). The reason is that an invoice may or may not have refunds.
So I modified the diagram as follows:

Here I could add the FK (refund_invoice_items => invoice_items)
Questions
I think this can work around the FK problem, so: Do you think this is an appropriate approach or have any ideas about it?
The main question is that if I could store NULL marks in the FK column, the first example would be better? (In my case I always add NOT NULL constraint to columns defined as FKs)

Yes.  Relationships (in ER modeling) can be optional.  There is no better way to represent a non-existent relationship in a foreign key than by putting in a NULL.  When you do a join, exactly the right thing happens.  When you do  a count, exactly the right thing happens.

The only reason to make a foreign key not NULLable is to make the relationship mandatory.  This rule might come from the subject matter, as captured by an ER model.  It depends on the situation.

In the question you posed, you already had a case of an optional relationship.  Not all invoices have any refunds.


SQL Server database backup file is double final size until backup completes I've just completed writing a backup statement for a reasonably large database (1.3 TB), and I've been testing it out using a much smaller database (10 GB + 10GB log file). The backup writes to three destinations. During the testing I noticed that whilst the backup is running each backup file is about 1.2 GB in size, but once the backup process has completed the files shrink to less than 600 MB.

I'm interested in knowing:

why the files are bigger during the backup process and then shrink?
whether there is a way to prevent this,
how big the files are likely to be when I back up that 1.3 TB database - if the files are again going to be double their final size then I may have to ask for some more disk space!

Here's the backup statement that I'm using:

BACKUP DATABASE @DatabaseName
TO  DISK = @backupMedia1,
    DISK = @backupMedia2,
    DISK = @backupMedia3
WITH COMPRESSION, RETAINDAYS = 0, NOFORMAT, INIT, NOSKIP, NAME = @DatabaseName

This is a known issue and expected behavior (it is by design). Essentially, the backup reserves the total amount of space that it thinks it might need, then shrinks as the last step. To change the behavior you can experiment with trace flag 3042. From http://msdn.microsoft.com/en-us/library/bb964719.aspx#Allocation :

  For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.
  To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).


Data warehouse support tickets I'm starting to design a data warehouse for a company. The first questions we're trying to resolve are regarding their support ticketing system. My initial schema is as follows

Now one of the questions we want to ask is historically how many tickets were active at anyone time.

The problem is that a ticket will be created one day but maybe open over several days / weeks / months without being updated or created again, meaning we only have one fact record when the ticket was created even though the ticket was opened every day.

I'm not sure what the best way of handling this is, a thought that's just come to me is this.

At the start of the day any ticket which hasn't been marked as resolved has another ticket entered into the fact table at the start of each day regardless of whether there's any updates? Does that seem like a sensible solution? Or am I missing something simpler?

Any feedback on the schema as it is would also be greatly appreciated, as we still have time to change it and get it right from the start.

You might want to decompose the support tickets fact table into transactions; User w on Date x moved ticket y to state z etc. This would facilitate metrics like "bounced tickets" etc. which service desk managers always seem to be keen on. This could be supplemented with the Accumulating Snapshot table you already have. Look here http://www.kimballgroup.com/2012/05/01/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/ for a couple of options to implement this scenario.


What is the difference between bridge table and helper table in data warehouse's context? From what I could understand :- Bridge tables are used when a dimension table can not be directly associated with a fact table.

e.g. In a bank's data warehouse a fact table of balance of a customer can't be stored with a customer ID as link between fact table and customer dimension due to the fact that multiple customers can be associated with same bank account. (i.e. joint account)

So fact table store account ID and bridge table between account dimension and customer dimension is used to distinguish.

But how are they different from helper tables which resolve many to many relationships between fact table and another dimension?

Bridge tables are the official name according to Dimensional Modeling as defined by the Kimball methodology.

Helper tables as far as I know have been used in many (often incorrect) contexts, one of which are bridge tables by people who fail to use the correct terminology.

Some people use helper table as a synonym for bridge tables, but helper tables also seem to have gotten misused in the context of a staging database or datawarehouses to name any table that helps something.

However, in dimensional modeling "helper table" isn't really defined, if it's used it's mostly as a synonym for "bridge table" (which is an actual term) or could be used to name about any table which "helps" achieving a goal but then there isn't a real definition.


User-Defined ORDERing in SQL I have just read an interesting blog article regarding User-Defined resultset ordering.

  Some applications, such as todo lists, need to maintain a user-defined
  order of items. The challenge is that the order is arbitrary and can
  change when the user rearranges items

An example table could be like so:

create table items
(
  itemid int primary key,
  itemdata varchar(200),
  ...
  ...
  userorder ???
);

With a possible retrieval query of:

select * from items order by userorder asc;

Are there any approaches that I should consider given I'd like to keep updates to a minimum, updates atomic, and retrieval queries as simple and "performant" as possible?

My initial thought was to use an extra TIMESTAMP column, and only update userorder and the timestamp values for a single row (with the retrieval query using ORDERing by userorder asc, usertimestamp desc), but that fails when you need to move a row in between two other rows that have the same value.

No specific database in mind, as i use a broad spectrum myself.

Are there any approaches that I should consider given I'd like to keep updates to a minimum, updates atomic, and retrieval queries as simple and "performant" as possible?

I think of this as an esoteric variation of the "True Fractions" approach in that blog post — the advantage being that there is no need to introduce a user-defined type (at least if you are using Postgres).

This works using the natural ordering properties of varbit and mapping a sequence of them onto a binary tree such that it is always possible to generate another varbit value between any two existing adjacent values:

                          _____ 1 ____
                   ______/            \______
              _ 01 _                          11
            _/      \_                    _/      \_
        001            011            101            111
       /   \          /   \          /   \          /   \
    0001   0011    0101   0111    1001   1011    1101    1111

create table foo(id serial primary key, userorder varbit unique);

create function adj(orig varbit, b varbit) returns varbit language sql as $$
  select substring(orig for length(orig)-1)
       ||b
       ||substring(orig from length(orig));
$$;

create function f(l varbit, h varbit) returns varbit language plpgsql as $$

begin
  if l is null and h is null then return B'1'; end if;
  if l is null then return adj(h,B'0'); end if;
  if h is null then return adj(l,B'1'); end if;
  if length(l)>length(h) then return adj(l,B'1'); end if;
  return adj(h,B'0');
end;
$$;

insert into foo(userorder) values(f(null,null));
select * from foo order by userorder;

id | userorder
-: | :--------
 1 | 1

insert into foo(userorder) values(f(null,B'1'));
select * from foo order by userorder;

id | userorder
-: | :--------
 2 | 01
 1 | 1

insert into foo(userorder) values(f(B'01',B'1'));
select * from foo order by userorder;

id | userorder
-: | :--------
 2 | 01
 3 | 011
 1 | 1

insert into foo(userorder)
values(f(B'01',B'011')),(f(B'011',B'1')),(f(B'1',null));

select * from foo order by userorder;

id | userorder
-: | :--------
 2 | 01
 4 | 0101
 3 | 011
 5 | 0111
 1 | 1
 6 | 11

dbfiddle here

If you initially load a lot of ordered rows, you may want to use some other algorithm for generating the initial userorder values as you will hit the worst case for space usage (each row will use one more bit for userorder than the row before). You could step through same-length values for a sufficiently large number of bits (eg for 8 values: B'0001', B'0011', B'0101', B'0111', B'1001', B'1011', B'1101', B'1111').


Use sp_Blitz to gather information on all servers in a centralized server/database I am using sp_Blitz, and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.

Do I need to create linked servers on the central instance to connect to the other instances, or is there some other way to input the server names into the sp_Blitz stored procedure?

Brent Ozar folks already have got you 50% - Inserting sp_Blitz® Output Into a Table

My steps would be :

Log the output to a local dbastats or some dba database.
Have a list of all sql servers in your central server.
use sqlcmd and opendatasource to query remote servers and dump all the info into centralized server .. make sure you include something like collection_datetime and server_name. You can use powershell (Write-DbaDataTable) as well.
schedule above logic using sqlagent job as per your needs.
Make sure you have proper purge policy defined locally per server. I would add custom column like notified = 0 (as default) and update to 1 when you dump the data into central database. The collection_datetime and server_name will help you find out what has changed or do aggregation per server instance.


Querying XML nested nodes I need to integrate a daily XML file into my system.

The XML file has an schema similar to this:

create table txml(id int identity, data xml);
insert into txml (data) values ('
<order>
    <trans DTrans="20170102" HTrans="10:52"></trans>
    <head Id="552665566"></head>
    <lin headId="552665566">
        <dLin Id="00001" CoArt="1111111" CoArtCust="05220001" NameArt="Product1" dateVal="20161115" />
        <acum DCalAcm="20170101" DIniAcm="20161115"><qtyAcm Qty="1101163.00" /></acum>
        <lastOrd Id="95767" QtyLastOrd="12000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="20000.00"/><dIni Date="20170109"/><dEnd Date="20170112"/></pLin>
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170113"/><dEnd Date="20170116"/></pLin>
    </lin>
    <lin headId="552665566">
        <dLin Id="00002" CoArt="2222222" CoArtCust="05269958" NameArt="Product2" dateVal="20161101" />
        <acum DCalAcm="20170101" DIniAcm="20161101"><qtyAcm Qty="552652.00" /></acum>
       <lastOrd Id="49956" QtyLastOrd="5000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="2200.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="3000.00"/><dIni Date="20170109"/><dEnd Date="20170116"/></pLin>
    </lin>
</order>');

As you can see for each <lin> section you can find an unknown number of <pLin> lines. By now I'm doing this job using C# and a .Net console app, but I'd like to do it by using SQL.

Till now I can get a rowset of <lin> tags using the next query:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]',  'decimal(18,2)') QtyLastOrd
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin);

That produces next result:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd
-: | :------ | :-------- | :---------- | :---------
 1 | 1111111 | 05220001  | 95767       | 12000.00
 2 | 2222222 | 05269958  | 49956       | 5000.00

But I should add nested lines corresponding to <pLin> tags and produce the next result:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd | DIni       | DEnd       | NextQty
-: | :------ | :-------- | :---------- | :--------- | ---------- | ---------- | ---------
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 04/01/2017 | 08/01/2017 | 24000.00
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 09/01/2017 | 12/01/2017 | 20000.00
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 13/01/2017 | 16/01/2017 | 24000.00
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 04/01/2017 | 08/01/2017 | 2200.00
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 09/01/2017 | 16/01/2017 | 3000.00

I've set up a dbfiddle here

You can add an additional CROSS APPLY to break out the rows in the way you require:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]', 'decimal(18,2)') QtyLastOrd,
        T3.lin.value('(./dIni/@Date)[1]', 'date') DIni,
        T3.lin.value('(./dEnd/@Date)[1]', 'date') DEnd,
        T3.lin.value('(./uni/@Qty)[1]', 'decimal(7, 2)') DIni
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin)
CROSS APPLY T2.lin.nodes('pLin') as T3(lin);

I had to guess the datatype for DIni, correct as required.


How can CREATE OR REPLACE fail with already exists? DETAIL:  Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS

How on earth is that even possible? How can CREATE OR REPLACE fail with already exists? This is in a docker container, completely pristine environment and this is the only that code runs. Yes, it might run repeatedly but that's why the OR REPLACEis there. I do not get it. Here's the full command:

  if (!$connection->query("SELECT COUNT(*) FROM pg_proc WHERE proname = 'rand'")->fetchField()) {
    $connection->query('CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS
      \'SELECT random();\'
      LANGUAGE \'sql\'');
  }

The OR REPLACE clause in CREATE FUNCTION is not meant for seamless parallel execution, it's meant to avoid dropping the function when we just want to update the body. From the doc:

  If you drop and then recreate a function, the new function is not the
  same entity as the old; you will have to drop existing rules, views,
  triggers, etc. that refer to the old function. Use CREATE OR REPLACE
  FUNCTION to change a function definition without breaking objects that
  refer to the function.

Now if several sessions issue the same CREATE OR REPLACE FUNCTION in parallel, there is indeed a potential race condition that is dealt with by relying on the unique index on (proname, proargtypes, pronamespace), and comes back to the caller as an error.

The error mentioned in the question happens if, for instance:

session #1 starts CREATE OR REPLACE and finds the function does not exist, so it creates it, inserting into pg_proc and locking the corresponding (proname, proargtypes, pronamespace) entry in the index.
session #2 starts CREATE OR REPLACE, finds the function does not exist (because #1 has not committed yet) and is put to wait for the index lock.
session #1 commits.
session #2 tries to insert and fails.

Note that not using explicit BEGIN/COMMIT does not change the race condition: the BEGIN/COMMIT pairs are just implicit around each SQL statement.

To have this parallel sequence work seamlessly, OR REPLACE would need to be to CREATE FUNCTION what ON CONFLICT is to INSERT, but that's not the case.

To deal with conflicting or identical DDL that might run in parallel, probably you should use an explicit lock at the beginning of the sequence to put it as a whole in a critical section. Advisory locks might be used for that. Or you need to implement a "retry on failure" strategy with savepoints around individual statements.


MySQL looking up more rows than needed (indexing issue) In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows:

+----------------+-------------+-----------------+--------+
| participant_id | question_id | given_answer_id | status |
+----------------+-------------+-----------------+--------+
|       500      |      12     |        25       |    0   |
+----------------+-------------+-----------------+--------+

The combination participant_id + question_id + given_answer_id is unique.

At the moment we have following keys:

PRIMARY KEY (in this order)

participant_id
question_id
given_answer_id

INDEX KEY

question_id

For this table, we have two kinds of select queries in our application:

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

and

[...] WHERE question_id = x;

Normally, every participant_id has between 0 and <100 rows with different question_ids. The other way around, every question_id can have unlimited (usually not more than 100 000) rows with different participant_ids. The first query is executed way more often than the second one.

When we execute following query, it shows us that 32096 rows where looked up:

EXPLAIN SELECT * FROM example WHERE question_id = 500;

+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows  | Extra |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| 1  | SIMPLE      | example   | ref  | question_id   | question_id | 8       | const | 32096 |       |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+

Whereas, when we execute the same query without EXPLAIN only 18732 rows are returned.

What indexes do we need on this table in order to prevent this overhead but still performing for both kinds of queries?

Here's is the code to create this table:

CREATE TABLE `example` (
    `participant_id` BIGINT(20) UNSIGNED NOT NULL,
    `question_id` BIGINT(20) UNSIGNED NOT NULL,
    `given_answer_id` BIGINT(20) UNSIGNED NOT NULL,
    `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`participant_id`, `question_id`, `given_answer_id`),
    INDEX `question_id` (`question_id`)
)
ENGINE=InnoDB;

Your indexes are fine for the two types of queries you mentioned.
This query will be satisfied by traversing the clustered index on the primary key...
[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':
[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine.  For InnoDB these are based on index statistics.

rows
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.
— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost.  The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.
When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.
Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either.  Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.
It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.
It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.


Does trigger "see" all changes done in the transaction it was fired from? Let me elaborate on the title.

Suppose we have two tables, t1 and t2. t2 has a trigger t2_trgon it. Same transaction inserts/updates data in t1, then does insert/update in t2. t2_trg is fired and starts doing its thing. Is this trigger able to see all the changes done in t1?

P.s. I found other topics on this problem, but they we're mostly for SQL Server.

Regular trigger  will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.


possible to query a database for an empty jsonb I have a table in Postgres 9.6 db that is structured like this:

Table "public.pricings"
Column           |            Type             |                       Modifiers
---------------------------+-----------------------------+-------------------------------------------------------
id                        | integer                     | not null default nextval('pricings_id_seq'::regclass)
unconfirmed_matrix_prices | jsonb                       | not null default '"{}"'::jsonb

I'm new to using jsonb.

I'd like to search for any unconfirmed_matrix_prices that are empty(ie the default). I see I can do something like this:

solar_dev=# select count(*) from json_object_keys('{"f1":1,"f2":2}');
 count
-------
     2
(1 row)

Is there a way I can do a where phrase where json_object_keys is equal to 0? I know a bit of a runaround way - Is there a better way?

This is a very bad idea.

not null default '"{}"'::jsonb

That's not storing an empty object. That's storing a JSONB string literal of "{}". You never want that. You normally want to confine it to a specific subtype instead, something like this..

not null default '{}' CHECK (jsonb_typeof(unconfirmed_matrix_prices) = 'object')

You also normally don't want to make it not-null. I'd rather have nullable and prevent empty objects if I need that kind of constraint. That simplifies your query substantially (as seen here).


SQL Server injection - how much damage in 26 chars? I'm testing for resilience against injection attacks on an SQL Server database.

All table names in the db are lower case, and the collation is case-sensitive, Latin1_General_CS_AS.

The string I can send in is forced to uppercase, and can be a maximum of 26 characters in length. So I can't send in a DROP TABLE because the table name would be in uppercase and thus the statement would fail due to the collation.

So - what's the maximum damage I could do in 26 characters?

EDIT

I know all about parameterised queries and so forth - let's imagine that the person who developed the front end that builds the query to send in didn't use params in this case.

I'm also not trying to do anything nefarious, this is a system built by somebody else in the same organisation.

Easy:

GRANT EXECUTE TO LowlyDBA

Or, I guess in this case it'd be

grant execute to lowlydba

Take your pick of variations on this.

In all likelihood you may be able to test this now against your current system, but any number of small changes in the database over time could invalidate your testing. The character string could change, someone could create a lower case stored procedure that has destructive potential - anything. You can never say with 100% confidence that there isn't a destructive 26 character attack someone could construct.

I suggest you find a way to make the developer follow basic industry standard best security practices, if only for your own sake as someone who I presume is at least partially responsible should security breaches happen.

Edit:

And for maliciousness/fun, you could try enabling every trace flag. This would be interesting to observe. Feels like a blog post Brent Ozar would make...

DBCC TRACEON(xxxx, -1)


Next quarter without using Quarter function I have a query which runs on particular dates. Whenever the query runs, it has to pick up the next quarter dates.

1st quarter Jan-March
2nd quarter April-June
3rd quarter July-September
4th quarter October- December

Suppose  my query runs in April. I need to pick the data from July 1st to September 30th, without using the MySQL QUARTER function.

mysql> SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * QUARTER(NOW()) + 1 - MONTH(NOW())
       MONTH;
--> 2016-07-01

If you don't need the date of the beginning of the next quarter, but need the string '3rd quarter July-September', then use a CASE clause.

Without QUARTER:

SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * FLOOR(MONTH(NOW())/3+1) + 1 - MONTH(NOW())
       MONTH;

Because QUARTER(x) is the same as FLOOR(MONTH(x)/3+1).


Create LINKED SERVER to MYSQL using SSH Is there any way to create a MS SQL Server Linked Server to connect to MySQL through SSH?
I managed to connect to MySQL through SSH using other database management tools like HeidiSQL or DBeaver, however I'd like to have this setup up for SSMS. I downloaded the MySQL ODBC driver but I can't find where to setup the SSH connection

HeidiSQL or DBeaver manage the SSH connection and not ODBC. You'll need to create your SSH connection with a port forward first.
Then test if you can connect a mysql client to the local ssh forwarded port.
Then attempt to create your linked server using ODBC to local end of the ssh forwarded port.


Is there a rule of thumb for including the value of a foreign key in a table to avoid large joins? Example:

We have a table with 500 rows in table "Cars". There is also a table "Colors" which has roughly 20 rows. Table "Cars" has a foreign key reference to table "Colors". To get the color of the car we would need to inner join on table "Colors":

select car.Name,
color.Name as Color
from dbo.Cars as car
inner join dbo.Colors as color on color.ID = car.ColorID

The result would look something like this:

+-------------+--------+
|    Name     | Color  |
+-------------+--------+
| Rolls Royce | Red    |
+-------------+--------+

But lets say you have 10,000,000 rows in Cars and 2,000,000 rows in Colors; the inner join would cause quite a performance hit. It would probably be better to include the column "Color" and "ColorID" in table "Cars" to avoid this inner join. So you query would look like this:

select Name,
Color
from dbo.Cars

Is there a rule of thumb to rather include the foreign key value inside your table once your table reaches an x-amount of rows? Or would it be better to remove the foreign key completely and just have a column called "Color" in table "Cars" thus causing table "Colors" to be redundant?

Don't denormilize, keep 2 tables (cars and colors) whatever tables size.
I think that you don't need to select 10 millions with one query but if you realy need it, you can do it by batch.
I don't know your problematic but by default, never denormilize.

If you have a problem with big tables you can expose it but i am pretty sure that denormalization will be not the solutiuon.


MariaDB Inserts are getting slower and slower (7x tables, ~ 2.8M and 200MB) I have auto increment on each of a table.
One unique ID that is consistent of 10 numbers, and each table have ~ 6 big int columns (values are small from 1-60k), and from 0 to 4 var chars (~ up to 500 characters, on average from 5 to 50 characters).

Quickly tested and with fresh database (dump and clean all tables) I started with 1000/s, then within seconds dropped to 400 => 200/s, after ~ just 116MB & ~ 500k records in 5 main tables it dropped to 159/s and keeps dropping. (memory usage 1.6%, disk writes ~ 1.2MB/s, reads => 0B/s).

Removed all other keys other than auto increment, so not sure why it is so slowing down.

This is nothing as I've worked with DB that was holding trillions of columns and auto increment and huge varchars. (however paid solution :().

I already tweaked the config so many times but still getting to the point where server is using ~ 950% & .net core 25% (of all cores).

Machine has i9 9900k 8c/16t, 64GB RAM, 2x NVME 2TB

I can't even run @ 5 minute API test as it won't be able to process all data from a queue :( (API can accept ~ 20k/s).

Buffers, read io, inno_db other tweaks for a commit etc. were applied, nothing seems to be working.

Looks like it cannot for some reason handle just so little data and I cannot figure out why (I never had any real experience with free databases, so I only assume that it should be able to insert 300k records within 60 seconds and sustain this for ~ 10TB).

I feel embarrassed :/, turned out that there were missing important indexes :(

Run for around night with ~ 1800k/s inserts (for endpoint, from mysql there would be probably 6 inserts + 4 reads for each endpoint insert, so ~ 10k/s of inserts + 8k/s reads).

In total over 150M records were created, > 30GB of data, no slow downs, still going fast.

RAM usage ~ 100% that was set for MySQL (32GB).

Really happy with it now :)

Of course replication will be another though decision, but for anyone else.

Make sure that proper indexes are in place, performance here degrade quickly.


Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an We have a db with around 150 tables in, of which about 30 or so are partitioned as such :

ALTER TABLE `myTable`
  REORGANIZE PARTITION PFUTURE INTO (PARTITION P201910 VALUES LESS THAN ('2019-11-01'),
             PARTITION PFUTURE VALUES LESS THAN MAXVALUE);

We've been automatically partitioning these tables on a monthly basis for around 3 years now without any issue.  Recently on 1 of the tables, after a partitioning exercise (with no errors/warnings reported) we see this :

  Got error 190 "Incompatible key or row definition between the MariaDB
  .frm file and the information in the storage engine. You have to dump
  an

from storage engine MyISAM" whilst running a show table status.  I have restored from a backup (after stopping/starting mariadb) and re-issued the command, and the same error is reported.

Repair table fails with the same message.

Is there a process I can follow to un-corrupt the table and make the partitioning process complete?

We're using MariaDB 10.4.8.  We are not in a position to use any other table format, MyISAM is what we have and what we have to stick with for now.

What I should mention is that we recently upgraded to MariaDB 10.4.8, and although I'd instantly jump on that as being the cause, the other 29 tables partitioned perfectly.

Try the following:

First check for all running queries and possible Connections your table might use. You can do this by

SHOW PROCESSLIST;

which gives you all running queries/Connections. You can terminate a query/Connection by

KILL <id>;

After that repair your table

REPAIR TABLE myTable;

Try to run this more than once until your Status Returns as ok. After that

OPTIMIZE TABLE myTable:

Then you can use the table again.


Database-backed website configuration I'm planning on adding storage for configuration options to the website I'm working on (php + MySQL).

The problem is that I cannot decide between having a 2-column table (with key and value fields) that contains multiple rows, one for each option; or an n-column table with one row.

It seems like using the single row format is superior, because each field can have the proper data type (INT, VARCHAR, etc.) and would make fetching all configuration values easier (single-row), but adding a new option would mean a new field every time.

At the same time, though, it seems like having a key-value table would possibly be a performance issue since you'd have to fetch all rows to get the full configuration, as well as updating having to find and seek the proper row to update; you also don't get actual typing (and would have to rely on VARCHAR or TEXT only).

I'm not planning on having a lot of configuration options (maybe 20 at the most, more likely 5-10), so I'm not sure if key-value or multi-field is the way to go, here.

I did this with a project recently, only I had even fewer settings to store. I opted for using a two column table, with each setting being it's own row.

I think there are pluses and minuses to each, but if you're dealing with such a small set of configuration options, there is not going to be a huge performance hit. To put into perspective, phpBB's config table has almost 270 rows of config options.


how can I ignore duplicate inserts? I basically have the same question posed here - Optimal way to ignore duplicate inserts? - except in my case it's SQL Server.

I have a table that is a list of person IDs who are deceased.  There are many places in the source database that a person can be indicated as deceased.  I would like to do INSERTS from each of these locations into this main table, but a person may be indicated as deceased in multiple places.

Ideally (I think) I just want to ignore duplicate key errors - in the most efficient way possible.  (Thus I don't want to have to check the main table for the IDs as part of my INSERT statement.)

(related to solving my question need suggestions to improve view performance)

The solution to this lies in the table creation itself.  If I set IGNORE_DUP_KEY = ON on the index on my person ID column, the duplicate values are simply ignored and the rest are inserted appropriately.


PostgreSQL - automating database grants We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc.

Is there some kind of event-based automation, call it a trigger or so, that can handle this automatically? Manual grants are not an option - automation is the only possible solution.

Whenever a new database is created, a new schema is created, or anything and everything that is created - the "DBA" users are granted access to it.

What solutions are there for postgres 9.6 or 10?

Example use case:

RDS instance "dev1" has 10 postgres databases
So we go in and grant access to everything in those 10 databases to our dbauser
Next week, a new deployment takes place that creates an 11th database
Following that we get a request to export a table from that 11th database and our user doesn't have access to it.

We want to eliminate that.

You can define event triggers on many DDL events, but there is no event trigger for CREATE DATABASE.  This means you will have to include the access rights in the database definition.  From this point, there are two ways: one is again event triggers, the other is setting default privileges for the different object types you have.  I prefer the latter in any case, for being much cleaner (objects and privileges are defined the same way, that is easy to grasp for developers and other humans, too).

For an example how it could work, see one of my older answers, and there the ALTER DEFAULT PRIVILEGES bits.  It's about tables, but you can easily generalize it to other object types.


Solution for the deletion of old Database Backups for Linux SQL Server 2017 after Backups created with Ola Hallengren scripts? I have a problem when I want to delete older backups, I've created with Ola Halengreen scripts.

USE Maintenance
EXECUTE dbo.DatabaseBackup
 @Databases = 'USER_DATABASES',
 @Directory ='/mssql/backup/',
 @DirectoryStructure ='${InstanceName}{DirectorySeparator}{DatabaseName}',
 @Filename='{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
 @BackupType = 'FULL',
 @Compress = 'Y',
 @CleanupTime = 3

I got the following error message:

  The value for the parameter @CleanupTime is not supported. Cleanup is not >supported on Linux.

So far so good, but whats the best way now to delete old Backups without to destroy the Backup chain?

My frist thought was to have a script to delete

#!/bin/sh
find /mssql/backup -name "*.bak" -type f -mtime +3 -exec rm -f {} \;
find /mssql/backup -name "*.trn" -type f -mtime +4 -exec rm -f {} \;

The internal system of the SQL Server Instance will not recognize the clean up after the script was executed. The Backup History in the internal Backup Management will not updated and in the case of a recovery the system wants to have backups which are not existing anymore.

So does anyone have a solution to the problem?

I don't know if you already found a solution to your problem, but as of this moment, the extended system stored procedure xp_delete_files is supported on SQL Server 2017 when hosted on a Linux machine.

You can edit Ola's script for database backup and remove the check when the cleanup parameter is being verified, to allow usage on a Linux machine.

I've tried that and the removal of old backups functions just ok, as when run on Windows.


Upgrade SQL Server database Using Compatibility Level I've made a backup of a SQL Server database running on SQL Server 2008.
I've restored that backup into a SQL Server 2012 instance.
I've set the compatibility level on the restored instance to SQL Server 2012 (110).

What does this do to the database?

Does it, for example,

cause stored procedures to be examined for incompatibilities
cause any updates to all of the data pages in the database
Cause updates to to datapages as the are accessed in the future
Cause errors in the future if stored procedures or views are executed that use incompatible syntax

Where in books on line, or else where, can I find more detail?

Compatibility level simply changes the language syntax that is supported by the SQL Statements.  This will cause all the execution plans to be expired and force them to be recompiled.  It has nothing to do with the data storage engine or the way that the data is written to the disk.

Once a database is attached to a SQL 2012 instance, it is in the SQL 2012 storage format and can't be moved back to an older version of SQL (no matter what setting the compatibility level is set to).

The link provided by @Shark (http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx) in the comments provides some additional reading on the subject.


Unable to create read only user-- postgresql 9.4 I am using PostgreSQL 9.4, I want to make a user with permanent read only access on public schema.
I have tried many different ways to get this but using

REVOKE ALL on schema public to user1;
GRANT SELECT on all tables in schema PUBLIC to user1;

doesn't make any sense for me, user user1 is not able to change any table but is still able to create new tables in public schema.

You're misunderstanding how REVOKE works.

You can't REVOKE a right that was never GRANTed. It doesn't create a deny ACL (a negative right), it removes a granted right if it exist. You didn't GRANT rights to user1 on the schema; instead that user inherited them from the public role everyone's a member of. So you can't revoke it.

You want to revoke the right given to public, then grant it to roles who should have read/write. quick and dirty example:

CREATE ROLE read_write_user;
GRANT read_write_user TO user2;
GRANT read_write_user TO user3;
GRANT ALL ON SCHEMA public TO read_write_user;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO user1;


Capitalize first letter with exceptions I need to properly format some European addresses.
One of the steps is to capitalize the first letter, but avoiding some specific words, like 'on', 'upon', 'von', 'van', 'di', 'in', 'sul'.
Therefore, although my skills are scarce, I thought it was a good idea to use a RegEx-based function.

After some googling I found this here:

CREATE FUNCTION InitialCap
(
    @String nvarchar(max)
)
    RETURNS nvarchar(max)
AS
    BEGIN
        DECLARE @Position INT;

        SELECT
            @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
            @Position = PATINDEX('%[^A-Za-z][a-z]%',@String COLLATE Latin1_General_Bin);

        WHILE @Position > 0
            SELECT
                @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
                @Position = PATINDEX('%[^A-Za-z][a-z]%',@String COLLATE Latin1_General_Bin);

        RETURN @String;
    END

That seems to search for a sequence of a 'non-letter' + a lowercase 'letter'

[^A-Za-z][a-z]

OK, I think I've understood how it works, and I modify it to best match my needs.

I supposed it was better to search for a space or ' or - and a lowercase letter, therefore I changed it to

[\s'-][\w]

Then, after several attempt, I built this RegEx on regexr.com, that seems to catch the required sequence:

[\s](?!di\s|in\s|sul\s|on\s|upon\s|von\s|uber\s|ueber\s)[\w]

But when I put that into the above function, the result is not as expected.

What's wrong?

SQL Server does not internally support Regular Expressions. LIKE and PATINDEX both support very limited wildcards, including a single-character range match [...] or exclude [^...] that are of similar syntax to RegEx, and are to a degree functionally similar, but are certainly not RegEx.

If you want / need Regular Expressions in SQL Server, you need to use SQLCLR. You can either code this yourself, or use pre-built functions such as the ones available in SQL# (which I wrote). Most of the RegEx functions are available in the Free version. I think you could use RegEx_Matches to return a result set of words that are not in your exclude list, and then combine that with the String_ToTitleCase4k function (also available in the Free version) to do the InitCap.

For example:

DECLARE @Input NVARCHAR(MAX) =
               N'santacroce sull''arno o''sullivan suLL sUlLiVan gsantacroce',
        @Expression NVARCHAR(4000) =
               N'[\s](?!di\s|in\s|sull\s|on\s|upon\s|von\s|uber\s|ueber\s)[\w]';

-- show matches for debugging
SELECT word.[StartPos],
       word.[EndPos],
       word.[Value] AS [Original],
       SQL#.String_ToTitleCase4k(word.[Value], N'') AS [TitleCased]
FROM   SQL#.RegEx_Matches(@Input, @Expression, 1, N'ignorecase') word;

SELECT @Input = STUFF(@Input,
                      word.[StartPos],
                      ((word.[EndPos] - word.[StartPos]) + 1),
                      SQL#.String_ToTitleCase4k(word.[Value], N'')
                                                COLLATE Latin1_General_100_BIN2)
FROM SQL#.RegEx_Matches(@Input, @Expression, 1, N'ignorecase') word;

SELECT @Input AS [Fixed];

Returns:

StartPos    EndPos    Original    TitleCased
--------    ------    --------    ----------
11          12        s           S
21          22        o           O
32          33        s           S
37          38        s           S
46          47        g           G

Fixed
-------------------------
santacroce Sull'arno O'sullivan SuLL SUlLiVan Gsantacroce

The reason that it doesn't work entirely correctly is due to your regular expression being incorrect:

It only matches a single letter.
It won't exclude any of the fragments if they are at the end of the string, but that might be ok if that never occurs in actual use.
It won't include the first word of the string (due to the required white-space to the left), but that might be ok if that never occurs in actual use.

UPDATE:
I was able to fix your regular expression by changing it to be as follows:

\b(?!(?:di|in|sull|on|upon|von|uber|ueber)\b)\w+

Main differences from the original:

I am using \b (word boundary) instead of \s (white-space) as it handles beginning and ending of line/string. It also doesn't capture the white-space, which \s does, and if it isn't visually obvious, each of the matched strings above is prefixed with the space that matched. While that space wouldn't affect the replacement as it would still be a space, it did prevent the first word in the group from matching, unless the entire string was preceded by some white-space. In the case of using this with addresses, if they always start with a number then perhaps there will always be that preceding space, but best not to include it in the match.
I added the + (one or more) quantifier to the \w so that it will pick up more than just the first character
I simplified the exclusion list by moving the common \b at the end of each fragment to just outside of a new, inner non-capturing group. This is a non-functional difference. It just makes it easier to read and deal with.

New output:

StartPos    EndPos    Original      TitleCased
--------    ------    --------      ----------
1           10        santacroce    Santacroce
17          20        arno          Arno
22          22        o             O
24          31        sullivan      Sullivan
38          45        sUlLiVan      Sullivan
47          57        gsantacroce   Gsantacroce

Fixed
-------------------------
Santacroce sull'Arno O'Sullivan suLL Sullivan Gsantacroce

UPDATE 2:

If the desire is to have a list of exclude words that is updateable without needing to update the function containing the regular expression, that is fairly easy to accomplish by doing the following:

Create a table to hold the exclusion words:

CREATE TABLE #ExcludeWords ([Word] NVARCHAR(50) NOT NULL);

Populate / manage-over-time the words in that table:

INSERT INTO #ExcludeWords ([Word]) VALUES
   (N'di'), (N'in'), (N'sull'), (N'on'), (N'upon'), (N'von'), (N'uber'), (N'ueber');

In whatever code you have that does this data-cleansing, dynamically build the regular expression from the table of words to exclude:

DECLARE @Expression NVARCHAR(4000) = N'\b(?!(?:';

SELECT @Expression += ex.[Word] + N'|'
FROM   @ExcludeWords ex

SET @Expression = LEFT(@Expression, LEN(@Expression) - 1) + N')\b)\w+';

SELECT @Expression; -- JUST FOR DEBUG
--\b(?!(?:di|in|sull|on|upon|von|uber|ueber)\b)\w+

UPDATE 3:

Originally no sample data was provided, so both answers tested with a simple list of words, some of which were the exclusion words. But now some test cases have been provided and in testing with one of those, I found a few issues with my implementation:

I had forgotten to specify the "case-insensitive" option for the RegEx function
I was replacing all occurrences of the entire match within the original string. This works fine when no matches are substrings of other strings within the entire string. But when including "O'Sullivan", the "O" is a substring of a few items and hence produced erroneous results.

So, I have adjusted the code and test case and results above to account for these issues. The main differences are:

Added the RegEx option of N'ignorecase'
Switched the REPLACE function with STUFF which allows me to use the starting and ending positions of each match to replace just that one item

PLEASE NOTE:

There is a minor discrepancy between the exclusion list in the question and the sample data provided in a comment on the question: question uses sul while the comment uses sull. I have adjusted my answer to work with the sull (two "L"s) shown in the test case provided in the comment.
This is reason number 5,235,948,567 for why it is critically important to have and/or provide actual test data ;-).