Is select case when null better than iif(exists)? I try to reduce huge performance issues on our application database. We use SQL Server 2014 and we have a lot of views.

I wonder if there is a significant impact of rewriting left joins to use exists in projections ?

My question is: is there any best practices concerning performance when constructing queries that project different data depending on related matches existence ?

I mean,
which of these queries could be considered better ?

1) :

select
        a.Id,
        case when b.Id is not null
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a
left join B b on a.Id = b.Id;

2) :

select
        a.Id,
        iif(exists(select top 1 1 from B where Id = a.Id),
            convert(bit,1),
            convert(bit,0)
           )  IsActive
from A a;

By the way, if you have any comments on the form of the queries, or general advice, please feel free to comment.

First of all, the two queries are only equivalent if and only if there is a UNIQUE or primary key constraint on B (id). If there isn't, there is no point in using one instead of the other or in comparing their efficiency.

Now, assuming there is such a constraint, the question is confused about CASE and EXISTS. The IIF vs CASE choice is orthogonal to the LEFT JOIN vs EXISTS subquery choice. Meaning, we could easily rewrite the first query with IIF() and the second with CASE. See:

(by the way, I removed the TOP 1 as it is redundant in an EXISTS subquery)

-- query 2a
select
        a.Id,
        case when exists (select 1 from B where Id = a.Id)
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a ;

There is also a 3rd way to rewrite the query, similar to the 2nd query, using a correlated subquery but without the EXISTS. This could also be written with either CASE or IIF() but I'm writing only the case here:

-- query 3
select
        a.Id,
        case when (select B.Id from B where B.Id = a.Id) is not null
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a ;

So we have 3 ways to write the query (and 6 if we count the IIF versions) and there are possibly more ways. Using OUTER APPLY comes to mind so lets have a 4th way:

-- query 4
select
        a.Id,
        case when b.Id is not null
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a
  outer apply (select B.Id from B where B.Id = a.Id) b ;

The real question is of course which is more efficient. Regarding CASE vs IIF() there is probably no difference at all and both ways produce identical plans in all 4 cases. IIF() was added in recent versions of SQL Server but can't do anything that wasn't already possible with CASE. So I can't find any real use of it, unless to make a query friendly to developers coming from MS-Access.

For the 4 different ways though, there is no better answer than testing it yourself! In your environment, with your tables/views, your data and sizes, your hardware, your settings, etc. All these may matter in one way or another.

If I were to blindly guess without testing, I'd choose the LEFT JOIN way, since correlated subqueries (like the EXISTS or the 3rd subquery) may not always yield the best plan. The optimizer is pretty clever at understanding that all the queries would be equivalent but that also depends on the complexity of the queries. If A and B are base tables, ok. But if they are complex views over complex views over ..., then it may not.

And if you plan to define this query as a view and then use it in queries like this:

select *
from my_view
where IsActive = convert(bit, 0) ;

you might fall into performance problems. Hiding the values into computed columns inside views will prohibit the optimizer from using indexes on the underlying tables.


How to allow an SQL-ID to access one database through a view in another? I have 2 databases in MS-SQL Server 2012. The first database, ‘DatabaseA’, is accessed by multiple databases including my second database ‘DatabaseB’. I have a view called ‘vwDB_B’ in ‘DatabaseB’ that joins/filters some information from both ‘DatabaseA’ and ‘DatabaseB’ (The view is set to run under an SQL-id that has access to both ‘DatabaseA’ and ‘DatabaseB’. Both DBs are on the same server. DatabaseB accesses objects in DatabaseA through their fully qualified names.

Now, we have an SQL-id that will be used by external users called ‘ExternalID’. This SQL-id should be able to retrieve data from ‘vwDB_B’. How can I achieve it?

If I give SELECT access to the ‘ExternalID’ on the ‘vwDB_B’ view, I get an error message that the ID doesn’t have the access to ‘DatabaseA’. I don’t want to grant read-only access to ‘ExternalID’ (or add it to a read-only role) on ‘DatabaseA’ because this DB has some sensitive information and I only want this id to access the information provided by my ‘vwDB_B’ view.

You can grant access to the view without granting any rights on the underlying tables using Ownership Chaining.

In this scenario you would need to enable Cross Database Ownership Chains, and grant the user access to the target database, but not SELECT on any of the tables.


Cannot remotely connect to oracle express I have installed Oracle Express in a oracle-6.5-x64 virtual machine (let's called it host). The installation went fine, I am able to connect and run commands using SQLPlus from the host.

Now I'm trying to use SQLPlus from my Mac notebook (let's call it client) to access that remote database. I have installed the client packages and set the proper environment variables. I have tried connecting using EZConnect:

sqlplus system/<password>@<host-ip>[:1521]/XE

and also with TNS:

sqlplus system/<password>@XE

Both commands give me the following message:

ERROR:
ORA-12541: TNS:no listener

For the last option I have created a tnsnames.ora file in the client and exported a TNS_ADMIN variable pointing to the folder containing that file. The contents of tnsnames.ora are:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I have tried using the host's IP instead of the hostname:

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.33.1)(PORT = 1521))

But that failed as well.

The lsnrctl status command on the host returns the following:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2014 11:39:22

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Estabelecendo conexão com (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS do LISTENER
------------------------
Apelido                   LISTENER
Versão                    TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Data Inicial              09-JUL-2014 11:31:24
Funcionamento         0 dias 0 hr. 7 min. 58 seg
Nível de Análise     off
Segurança              ON: Local OS Authentication
SNMP                      OFF
Serviço Default           XE
Arq. Parâm. Listn.    /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Arq. Log Listener    /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/oraclehost/listener/alert/log.xml
Resumo de Atendimento...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Resumo de Serviços...
O serviço "PLSExtProc" tem 1 instância(s).
  Instância "PLSExtProc", status UNKNOWN, tem 1 handler(s) para este serviço...
O serviço "XE" tem 1 instância(s).
  Instância "XE", status READY, tem 1 handler(s) para este serviço...
O serviço "XEXDB" tem 1 instância(s).
  Instância "XE", status READY, tem 1 handler(s) para este serviço...
O comando foi executado com êxito

As a last resource I have changed added oraclehost to the client's /etc/hosts file:

192.168.33.1    oraclehost

So I am able to successfully ping the host machine from the cliente by IP or hostname.

More often than not, a firewall on the database vm is the culprit.
You can test this with

telnet oraclehost 1521

from your mac. This should give a quick response. If it does not give a quick response, like not response at all, a firewall is blocking the request. In that case allow traffic on port 1521, or disable the firewall.


SQL Server - Select last value given a date To make it easier, I have data that looks like this as an example:

What I am trying to return is the Amount of the Balance Update as of a specified Date for each Investor_ID.  For example, if my date is 12/31/15, It would return two rows:

My query is:

SELECT Investor_ID,
(SELECT Max(CASE WHEN Type = 'Balance Update'
                 THEN Amount else 0 END)) as Val
FROM Table1
WHERE Date <= '12/31/2015'

However this returns the maximum value of "Amount" for the date range (before 12/31/15) as opposed to the Amount at the Maximum date, 12/31/15.

How can I restructure the query to where the Date can be changed, and return the LATEST "Amount" of "Balance Update" for each Investor_ID?

Thank you!

You can do this for all databases (even MySQL - which doesn't
have Window functions or CTEs - i.e. WITH clauses).

To solve this, I did the following:

Created a table

CREATE TABLE table1 (Investor_ID int, my_date DATE, type VARCHAR(20), amount int);

Note, never have a column named "date" (or any other SQL keyword).

Populated the table:

INSERT INTO table1 VALUES (1, '2015-03-31', 'CONTRIBUTION'   ,  3);
INSERT INTO table1 VALUES (1, '2015-06-30', 'Balance Update' , 20);
INSERT INTO table1 VALUES (1, '2015-09-30', 'CONTRIBUTION' ,    5);
INSERT INTO table1 VALUES (1, '2015-12-31', 'Balance Update' , 10);
INSERT INTO table1 VALUES (2, '2015-03-31', 'CONTRIBUTION'   ,  8);
INSERT INTO table1 VALUES (2, '2015-06-30', 'Balance Update' , 38);
INSERT INTO table1 VALUES (2, '2015-09-30', 'CONTRIBUTION' ,    1);
INSERT INTO table1 VALUES (2, '2015-12-31', 'Balance Update' , 50);

And then ran the following query:

SELECT t1.my_date, t1.Investor_ID, t1.type, t1.amount
FROM table1 t1
WHERE my_date = (SELECT MAX(my_date) FROM table1 t2 WHERE  t1.Investor_ID = t2.Investor_ID)

And the result was as desired:

my_date   |investor_id|               type|amount
--------------------------------
2015-12-31|1               |Balance Update    |10
2015-12-31|2               |Balance Update    |50

If you want to use Window functions, use this query:

SELECT main.* FROM
(
  SELECT Investor_Id
  ,my_date
  ,type
  ,amount
 ,Row_Number() OVER (PARTITION BY Investor_ID ORDER BY my_date DESC) AS RowNum
 FROM table1
) main
WHERE main.type = 'Balance Update'
AND rownum = 1;


MySQL data type for 128 bit integers I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers.

Right now, I'm using binary(16) but that involves a lot of conversion function pack(/huge number in hex .../).

Is there a best datatype to store a 128 bit unsigned integer?

I don't know what the best way necessarily is to store it -- but there's at least a better option than using a varchar(39) (or varchar(40) if you needed it signed) ; instead use a decimal(39,0).  From the mysql docs:

  Fixed-Point (Exact-Value) Types

  The DECIMAL and NUMERIC types store
  exact numeric data values. These types
  are used when it is important to
  preserve exact precision, for example
  with monetary data. In MySQL, NUMERIC
  is implemented as DECIMAL, so the
  following remarks about DECIMAL apply
  equally to NUMERIC.

  MySQL 5.1 stores DECIMAL values in
  binary format. Before MySQL 5.0.3,
  they were stored as strings. See
  Section 11.18, “Precision Math”.

  In a DECIMAL column declaration, the
  precision and scale can be (and
  usually is) specified; for example:

salary DECIMAL(5,2)

  In this example, 5 is the precision
  and 2 is the scale. The precision
  represents the number of significant
  digits that are stored for values, and
  the scale represents the number of
  digits that can be stored following
  the decimal point.

  Standard SQL requires that
  DECIMAL(5,2) be able to store any
  value with five digits and two
  decimals, so values that can be stored
  in the salary column range from
  -999.99 to 999.99.

  In standard SQL, the syntax DECIMAL(M)
  is equivalent to DECIMAL(M,0).
  Similarly, the syntax DECIMAL is
  equivalent to DECIMAL(M,0), where the
  implementation is permitted to decide
  the value of M. MySQL supports both of
  these variant forms of DECIMAL syntax.
  The default value of M is 10.

  If the scale is 0, DECIMAL values
  contain no decimal point or fractional
  part.

  The maximum number of digits for
  DECIMAL is 65, but the actual range
  for a given DECIMAL column can be
  constrained by the precision or scale
  for a given column. When such a column
  is assigned a value with more digits
  following the decimal point than are
  permitted by the specified scale, the
  value is converted to that scale. (The
  precise behavior is operating
  system-specific, but generally the
  effect is truncation to the
  permissible number of digits.)

It's stored packed, so it'll take up less space than the varchar (18 bytes, if I'm doing my math right), and I'd hope you'd be able to do math on it directly, but I've never tried with that large of a number to see what happens.


Select most occurrences of a varchar2 in Oracle We need to group data in an Oracle database and must get a ProductNumber and a Description. The Description needs to be the one with the most occurrences inside the table. Both are a varchar2. See below for the raw data:

ProductNumber
Description

ABC
Product ABC (with discount)

ABC
Product ABC

ABC
Product ABC

DEF
Product DEF

Expected  result:

ProductNumber
Description

ABC
Product ABC

DEF
Product DEF

We've tried this but don't find a way to include the Description with most occurences.
select distinct cnt1.ProductNumber
from (select COUNT(*) as total, ProductNumber
      from Inventory
      group by ProductNumber) cnt1,
     (select MAX(total) as maxtotal, ProductNumber
      from (select COUNT(*) as total, ProductNumber
            from Inventory
            group by ProductNumber)
      group by ProductNumber) cnt2
where cnt1.total = cnt2.maxtotal;

Here you could find a fiddle.
Do you know how we could do it?


The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT' I have a question about XTP_CHECKPOINT.

I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated.

There are no open transactions. I've run DBCC OPENTRAN and it returns:

  "No active open transactions."

But I keep getting this message whenever I try to create or drop a table or delete data:
(I've replaced my actual database name with the word database_name)

  "The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'"

Does anyone know why this might be happening, and, more importantly, how can I make it stop?

And yes, the database really is in SIMPLE recovery model mode. i.e. The transaction log should truncate automatically.

Incidentally, another database that I have in full recovery mode did the same thing, started returning the same error:

  The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

I tried to change the log growth settings to unlimited growth, but it wouldn't let me, returning the same error.

I can reproduce the problem without any XTP stuff at all, except for just the filegroup. Here's how: http://pastebin.com/jWSiEU9U

First make sure that replication is not causing this, as stated in the connect item the "log_wait_reuse_desc=XTP_CHECKPOINT does not necessarily mean that the XTP checkpoint worker is holding up log truncation." so start by running sp_repltrans and make certain that all data has been distributed.
Then there is this little snippet here:

"it happens on a database, which has memory optimized file group, no matter there has memory optimized tables or not.
The current workaround is set the AutoGrown to be a fixed size. Or, changing the recovery mode to be Simple, and shrinking the log."

So if cleaning up replication does not work try the following:
checkpoint;
dbcc shrinkfile (Logfile, truncateonly)
alter database [database] modify file (filename = 'TRANSACTIONLOG', FILEGROWTH = 5MB)

It is not stated if this is for the log file or the database files but lets start by trying the log files and if not then try setting the database files to fixed growth:


Verbose Oracle RDBMS error log output examples? I'm working on a project and my boss has asked that I find an actual example of the "verbose" output of an Oracle RDBMS query error. We want to see what they actually look like. I know that there are, of course, a number of different queries that can be ran against a database, so my request more pertains to error log messages that can be used to reveal information about a database's data structures.

Any information on where to find examples of such errors would be greatly appreciated!

I'm not sure what you really want but the official documentation is a good place to start, all 86 pages of it.

Just thinking about Oracle error codes gets me all nostalgic for those cryptic messages

ORA-00600: internal error code  (something happened but we can't tell you)
ORA-03113: end-of-file on communication channel (you, or your database or your connection are down, somewhere...)
ORA-12154: TNS: could not resolve the connect identifier specified (your database does not want to talk to you)

You also may be thinking of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which will tell you more about errors in PL/SQL programs.  This is not the same as your question about verbose messages for SQL queries.


Query profiling shows "Waiting for query cache lock" but query_cache_size is 0 We have a statement based replication server that has been experiencing slowdowns and during the event SHOW FULL PROCESSLIST showed the replication query stuck on "Waiting for query cache lock" which was surprising because the server query_cache_size is set to 0.
Profiling the offending query does show this step in every update to the table.

Is this typical to see in the profile for an update even if the query_cache_size is 0? Is this really just the check for query caching and not the query waiting to obtain a true lock?

The message means that you are trying to acquire a Query Cache Mutex even though the query_cache_size is 0 (Note that akuzminsky mentioned that the MySQL for Oracle still allows this) You will need to set query_cache_type to 0 as well because as the MySQL Documentation says:

  If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

In MySQL 5.6, query_cache_type is 0 by default. It's 1 for MySQL 5.0, 5.1, and 5.5

Regardless of the MySQL version, make sure my.cnf has these

[mysqld]
query_cache_size = 0
query_cache_type = 0

You do not need to restart mysql. Just login to mysql and run the following:

mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_type = 0;

Give it a Try !!!


How to implement UNIX like ACL for table rows EDIT: I can't create or use native MySQL users. I have to use users stored in table with their password hash.

I am new sql stuff so please forgive me for asking dumb question. I am creating my first real life application for college project.

At its core, It need to handle more than thousands of users which should not able to read or write to each others data unless given privileges. like Linux does with user and groups.

in below schema which I tried , a user can view(read) and edit(write) other users if they have read permissions.( r=2 w=1 r+w=3 ).

for example if cgroup_1 is admin and cgroup_2 is managers and unixperm is 32 then it means users in admin group can read+write(3) and users in managers group can only read(2)

create table cgroups
(
    id int unsigned primary key auto_increment,
    title varchar(100) not null unique,

    cunixperm  tinyint unsigned not null default 32 ,# r=2 w=1
    cgroup_1   int unsigned not null default 1 references cgroups (id) on delete cascade on update cascade,
    cgroup_2   int unsigned references cgroups (id) on delete cascade on update cascade
);

create table users
(
    id          int unsigned auto_increment primary key,
    username    varchar(255) not null unique,

    cunixperm  tinyint unsigned not null default 30, # r=2 w=1 3=r+w
    cgroup_1   int unsigned default 1 not null references cgroups (id) on delete cascade on update cascade ,
    cgroup_2   int unsigned references cgroups (id) on delete cascade on update cascade
);

create table many_users_in_many_cgroups
(
    user_id int unsigned references users(id),
    cgroup_id int unsigned references cgroups(id),
    primary key (user_id,cgroup_id)
);

insert into cgroups(title)
values ('admins'),('managers'),('writers');

insert into users(username, cunixperm, cgroup_1, cgroup_2)
values ('user1',30,1,null),
       ('user2',30,1,2),
       ('user3',22,2,2),
       ('user4',02,3,3);

insert into many_users_in_many_cgroups
values (1,1),(2,2),(3,3),(4,4);

Now suppose user 2 has logged into my app, How can I only show the user rows where he has read (2) or read+write(3) permissions.

if above schema is not (probably) appropriate pls give me an example with appropriate scheme

I am currently using MariaDB but open for solutions for others too.

I'd strongly consider using PostgreSQL, as it supports Row Security (as well as Column Security), and is free and well made

You should also consider using real database users, but you don't have to. For example, you could set the current user into a temp table upon sign-in, and reference that username in your RLS policy


Open Source Business Intelligence/DWH solutions I wonder that this question hasn't already been asked. Google only has very few results for me that don't show a high quality tool

What are some Open Source (also free is ok) solutions for Data Warehouses and more specifically Business Intelligence Tools? What are your experiences with them. I have had a course in my masters programm and we worked with MS Business Intelligence and MSSQL as Data Warehouse storage. Now I want to get more into this topic with tools that are "open".

Are there any compareable tools for Business Intelligence (mostly database independent) and do you have any experience with them?

edit With Marian's comment to Stephanie's answer, I see that I have formulated the question wrong. I am aware that DWH are just "reporting-optimized" databases, Stephanie's explanation was very clear on that. I am more interessted how to get the data into such optimised forms with WHAT kind of BI software/tools/other techniques.

I have been working with Pentaho for about a year now.

Pentaho is a full Open Source suite for Business Intelligence.

It's strenght is that it relies on independently managed project :

Pentaho Data Integration (Kettle) ->ETL
Pentaho Report Designer (PRD) -> Report designer
Mondrian -> R-OLAP cube
and much more..

You can use them as a whole (Pentaho BI Server) or apart.

Why would I recommend it ? The community is really active and provide plugins on a weekly basis. A group of community members provided a framework for building interactive dashboard with a click&go ui.

It is database independant. If you have some free time just take a look at it.

I did a research before using Pentaho, I did test IceCube and Palo even JasperSoft but Pentaho was from far better according to my needs.


Automatic replication on slave node after service restart I am new to mysql administration. we have master - slave replication environment.

I added a 2nd slave node to existing env [master-slave] and replication is successful. Whenever I restart the mysqld service on 2nd slave node, replication is not starting automatically on the slave. That's what I am trying to accomplish.

I am executing these queries to re-sync again with master node on 2nd slave

2nd slave mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
2nd slave mysql>mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)

2nd slave mysql> SET GLOBAL server_id=3;
Query OK, 0 rows affected (0.00 sec)

** I did specify in /etc/my.cnf file, server-id as 3, but its not taking it after restarting the mysqld service.

2nd slave mysql> show slave status\G
*************************** 1. row ***************************
           Slave_IO_State:
              Master_Host: masterhost
              Master_User: mysql
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000012
      Read_Master_Log_Pos: 17203734
           Relay_Log_File: mysqld-relay-bin.000002
            Relay_Log_Pos: 616388
    Relay_Master_Log_File: mysql-bin.000012
         Slave_IO_Running: No
        Slave_SQL_Running: No
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 17203734
          Relay_Log_Space: 616669
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
1 row in set (0.00 sec)

** from the above command I take master_log_file and MASTER_log_pos to resync.

mysql> CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='mysql', MASTER_PASSWORD='!adbcd', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=17203734;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

and it starts replicating again from the master. How can I avoid this manual steps in future and replicate automatically ?

Here is my /etc/my.cnf file.

[root@2nd slave ~]# more /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections
max_connections=400
symbolic-links=0
server-id=3
#skip-slave-start
log-bin=/data/mysql/mysql-bin
#binlog_format=ROW
max_user_connections = 400
key_buffer = 64M
max_allowed_packet = 32M
sort_buffer_size = 64M
net_buffer_length = 64K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 64M
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 100M
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16
[root@2nd slave ~]#

Thanks,

Move server-id=3 from under [mysqld_safe] and put under [mysqld]

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
server-id=3
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections
max_connections=400
symbolic-links=0
#skip-slave-start
log-bin=/data/mysql/mysql-bin
#binlog_format=ROW
max_user_connections = 400
key_buffer = 64M
max_allowed_packet = 32M
sort_buffer_size = 64M
net_buffer_length = 64K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 64M
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 100M
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16

Then restart mysql

Perhaps [mysqld_safe] does not understand server-id. Normally, options not understood by [mysqld_safe] are supposed to passed to [mysqld]. That's probably broken in MySQL 5.1.


Language difference in full text search for numeric strings If I create this table on SQL Server 10.50.2550 (SQL Server 2008 R2):

CREATE TABLE test(
  id BIGINT IDENTITY NOT NULL,
  number VARCHAR(10),
  CONSTRAINT test_pk PRIMARY KEY (id)
);

CREATE FULLTEXT CATALOG test_catalog;

CREATE FULLTEXT INDEX ON test
  (
    number Language 0
  )
  KEY INDEX test_pk
  ON test_catalog;
  ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;

INSERT INTO test (number) VALUES ('1111111111');
INSERT INTO test (number) VALUES ('1111111112');

And when the index is populated perform the query:

SELECT * FROM test WHERE CONTAINS(*, '1111111111');

I get this:

id                   number
-------------------- ----------
1                    1111111111
2                    1111111112

(2 row(s) affected)

If I change the language to English (1033), if I only use 9-digit numbers or if I use SQL Server 11.0.3128 (SQL Server 2012) I only get the expected one row.

Do I do anything wrong or is this a bug?

Seems that this is the bug: SQL Server 2008 full-text query may return false positives using number with more than 9 digits as search predicate condition.

Unfortunately the resolution does not work for Danish language.


Configuring MySQL slave to use SSL I am trying to set up a MySQL slave to use replication via SSL.

The my.cnf file contains the following:

server-id = 1012

master-host = host
master-port = port
master-user = user
master-password = password

ssl-ca=/etc/mysqld/ssl/ca-cert.pem
ssl-cert=/etc/mysqld/ssl/client-cert.pem
ssl-key=/etc/mysqld/ssl/client-key.pem

Starting the slave fails with SLAVE STATUS showing

Master_SSL_Allowed: No

When I manually set up the slave as follows:

CHANGE MASTER TO
     MASTER_HOST='host',
     MASTER_USER='user',
     MASTER_PASSWORD='secret',
     MASTER_SSL=1;
START SLAVE;

Everything works.  Any idea what parameter I need to enter into my.cnf in order to force the slave to use SSL?  The obvious choice - "master-ssl=1" did not work.

Older versions of MySQL allow declaring the CHANGE MASTER TO parameters in my.cnf. Newer version may not. In fact, according to the MySQL Documentation concerning setting replication options in my.cnf:

  Before MySQL 5.1.17, these options are silently ignored if given
  unless there is no master.info file. If that file exists, the MySQL
  server has already previously been configured for replication, so the
  information in the file is used instead. Because the server gives an
  existing master.info file precedence over the startup options just
  described, you might elect not to use startup options for these values
  at all, and instead to specify the replication parameters associated
  with them by using the CHANGE MASTER TO statement. See Section
  12.5.2.1, “CHANGE MASTER TO Syntax”.

  Beginning with MySQL 5.1.17, these options are deprecated and have no
  effect when mysqld is started. If they are used, an appropriate
  warning is written to the error log. Instead, you must use CHANGE
  MASTER TO to set the values corresponding to the deprecated options.
  These options are removed in MySQL 5.5.

Naturally, running CHANGE MASTER TO generates a master.info file, which includes master_ssl. No harm, no foul. Relying on my.cnf for older versions of mysql results in option being ignored when no master.info exists. They are no longer valid.


Sharing a single primary key sequence across a database? Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)?  If so, is it objectively better than using a single primary key sequence across tables.

I'm a junior software developer, not a DBA, so I am still learning many of the basics of good database design.

Edit:  In case anyone is wondering, I recently read a critique of a database design by one of our company's DBAs who mentioned it was a problem that the design didn't use a single primary key across the entire database, which sounded different than what I've learned so far.

Edit2:  To answer a question in the comments, this is for Oracle 11g, but I was wondering on a non-database specific level.  If this question does depend upon the database, I would be interested to know why, but in such a case I would be looking for an answer specific to Oracle.

Acceptable? Sure. Common? No. Beneficial? Doubtful.

At my old job we inherited a system where they had a central sequence generator (this was a SQL Server system long before SEQUENCE was introduced in SQL Server 2012). It wasn't really a performance bottleneck and shouldn't be unless you're generating hundreds of thousands of values per second. But it made all of the code a lot more complex than it had to be, for no good reason. The intent of the design was to be sure that if something in the system was assigned an ID value of 12, only one thing in the system could have the ID 12. This seemed quite obtuse to me and I never understood it. If I have a customer with CustomerID = 12, why does that preclude me from having an order with OrderID = 12?

I do see the usefulness of a central sequence generator if you have multiple systems and you are generating IDs for a certain type of entity (say, a customer or an order) from these multiple systems. A central sequence can dole out new values to multiple systems without being a bottleneck (just a single point of failure) and without fear of two systems generating the same ID.


DATA BACKUP from SQL Server Express to SQL Server 2012 Can I restore the backup data from SQL Server Express to SQL Server, both 2012 versions?

Yes. The database format is identical across all editions. An Express Edition database for sure can be restored on a non-Express Edition (you fail to mention whether is a Standard or an Enterprise Edition). The other way around it may be subject to restrictions, Express Edition will refuse to attach or restore a database that:

exceeds the size limitation of Express Edition
contains Enterprise Edition only features, like partitioned tables. On SQL Server 2012 anything from sys.dm_db_persisted_sku_features  will block an Express Edition. This restriction was lifted on SQL Server 2016 SP1.

It is important that both servers (the Express Edition and the non-Express one) be at the same version.


How to get the total days per month between two dates? Given two dates like 20120302 and 20120605

I need to get a list of Months and the total days in those months that fall between those two dates, like so:

March 28
April 30
May   31
June  03

Here's my attempt. Still think there are unanswered questions in your sample data, though.

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20120302',  @e = '20120605';

;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

Results:

Month  Days
-----  ----
March    29
April    30
May      31
June      5


How do I split a long PL/pgSQL line of code over multiple lines? Is there a way to split a long line of PL/pgSQL code over multiple lines? My context is a trigger function where I log inserts into a table as per:

INSERT INTO insert_log (log_time, description)
VALUES (
    now()
    , 'A description. Made up of 3 semi long sentences. That I want to split, in the code, not in the log table, over 3 lines for readability.'
);

String constants can be split over multiple lines as documented in the manual

INSERT INTO insert_log (log_time, description)
VALUES (
    now()
    , 'A description. Made up of 3 semi long sentences. '
      'That I want to split, in the code, not in the log table, '
      'over 3 lines for readability.'
);


How to send mail from a restricted SQL Server I have created an DB refresh job on a pre-prod SQL server, with latest successful Prod backup to refresh pre-prod DB every monday morning. However, i want to send a status mail post refresh. Due to compliance and security risks in our financial company, we can not send mails from pre-prod server. I was able to send mails successfully from Prod.

Is there any method to send mail with DB refresh status. My thoughts are
1. Refresh script writes the log to a folder in Dev server - should i write a windows batch/powershell script in Prod server to access the folder in dev and read the file to send a mail post refresh with the status?
2. I can not use any linked server from Pre-prod to Prod.

Any other thoughts?

So, I used the below script.

Prerequisites are that the refresh script will write the log to a shared location accessible from a Prod Server.

$date = Get-Date -Format d
$dir = "\\xxxxx\g$\QARefreshLogs"
$recipient = "xxxxxx"
$smtpserver = "xxxxxxx"
$smtpport = "xx"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = new-object Net.Mail.MailMessage
$msg.From = "xxxxxxxxxx"
$msg.To.Add($recipient)

---Retrieving the latest file

$latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Where-Object {$_.Name -ilike "<db_name>_refresh_log_*"} | Select-Object -First 1
$LatestRefreshLog = $latest.Name
$LatestRefreshLogDate = $latest.LastWriteTime.ToString('M/dd/yyyy')

--Validating if the log file generated today?

if (($LatestRefreshLogDate) -notmatch $date)
     {
      #Write-Host "WARNING: There is no today's restore log file!"
      $msg.Subject = "SQLServer QA Refresh Status"
      $msg.Body = "QA MSSQL DB refresh is having issues.Please validate."
      $smtp.Send($msg)
     }
     else
     {

--Validating if refresh is success or not?

      If(Get-Content -Path $dir\$LatestRefreshLog | %{$_ -match "RESTORE DATABASE successfully processed"})
      {
       #Write-Host "The latest restore log file is $LatestRefreshLog"
       $msg.Subject = "SQLServer QA Refresh Status"
       $msg.Body = "QA DB is refreshed successfully."
       $smtp.Send($msg)
      }
      else
      {
       #Write-Host "The latest restore log file is $LatestRefreshLog"
       $msg.Subject = "SQLServer QA Refresh Status"
       $msg.Body = "QA DB refresh is having issues.Restore failed."
       $smtp.Send($msg)
     } }

This may not be an optimal script. It served my purpose....thanks for all your inputs. This script could be helpful to others, have a PoSh time!!!


Check if there are similar rows in a table As a Rookie in SQL I have a problem creating a query.

Here is the problem : I would like to check on a table if there are lines that share the same DATES.

For example I would like to know if in TABLE A, there are cases like this :

ID | LIB | START_DATE | END_DATE
01 | A1  | 01-01-2016 | 05-03-2016
02 | A2  | 03-03-2016 | 05-06-2016

Those lines are SURVEYS and I would like to know if an admin created a survey that is starting during an other survey. I did some searches on AUTO JOIN but this wasn't really what I was looking for.

My case is a bit more complicated but I will find out myself if you explain  how to proceed on this simple case.

this would be exact

select min(id), max(id)
from table
group by start_da, end_date
having count(*) > 1

start in

select t1.*, t2*
from table t1
join table t2
      on t1.startdate between t2.startdate and t2.end_date


High Write Count/sec in my RDS server Recently we have encountered seriously problem since we moved our mysql database from EC2 to RDS.

We do have high cpu usage before moved to rds, but our website has a good speed for our customers.  After set up instance in RDS, our speed dramatically down without any reasons.

We checked our metrics on RDS, then we found that we have almost 100 write count/sec in anytime of day, even we do not have too many customers at nighttime, but the write count/sec is still remain around 100 count/sec.

And we have checked our general_log and slow_log files, but we do not where the write operations from ?

Anyone experience similar problems ?

Our configuration of rds:

Mysql 5.6.23 db.r3.large

Our metrics snapshot:

Many Thanks

The poor performance of this is due to the pager of view, just modify the view setting to "Mini Pager" or others except "Full". Your database performance will boost twice times as before.


MySQL - Split results of count function into columns derived from a separate column The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just trying to use one.
The table named service_data has the following columns:

Services_ID|Service_Type|Day|Time|Customer_ID(FK)
1001       |SERVICE1    |Mon|0950|1
1002       |SERVICE2    |Tue|1032|65
1003       |SERVICE3    |Wed|0859|4

The table contains approx 200 records, my aim is to group the timings together, which I have managed to achieve by doing this:

select
case
WHEN (Delivery_Time between '08:00:00' and '09:00:00') then '0800-0900'
WHEN (Delivery_Time between '09:00:00' and '10:00:00') then '0900-1000'
WHEN (Delivery_Time between '10:00:00' and '11:00:00') then '1000-1100'
WHEN (Delivery_Time between '11:00:00' and '12:00:00') then '1100-1200'
WHEN (Delivery_Time between '12:00:00' and '13:00:00') then '1200-1300'
WHEN (Delivery_Time between '13:00:00' and '14:00:00') then '1300-1400'
WHEN (Delivery_Time between '14:00:00' and '15:00:00') then '1400-1500'
WHEN (Delivery_Time between '15:00:00' and '16:00:00') then '1500-1600'
WHEN (Delivery_Time between '16:00:00' and '17:00:00') then '1600-1700'
WHEN (Delivery_Time between '17:00:00' and '18:00:00') then '1700-1800'
WHEN (Delivery_Time between '18:00:00' and '19:00:00') then '1800-1900'
WHEN (Delivery_Time between '19:00:00' and '20:00:00') then '1900-2000'
WHEN (Delivery_Time between '20:00:00' and '21:00:00') then '2000-2100'
      else 'Outside Opening Hours'
  end as `Time Period`,
   count(0) as 'count'
from service_data
group by `Time Period`
order by count desc
limit 20;

Which produces the below result:

TimePeriod  Count
1700-1800   24
1500-1600   21
1200-1300   19
1400-1500   19
1800-1900   17
1100-1200   17
1300-1400   16
1600-1700   16
1000-1100   16
1900-2000   12
0800-0900   12
0900-1000   11

What I am now trying to do is split the count up so that there are 4 columns labelled SERVICE1 SERVICE2 SERVICE3 and SERVICE4 (the values within the Service_Type column), so that it looks something like this:

TimePeriod|SERVICE1|SERVICE2|SERVICE3|SERVICE4
1700-1800 |   6    |   7    |    10  |   1
1500-1600 |   5    |   9    |    1   |   6
1200-1300 |   0    |   4    |    2   |   13`

Is this possible!?  Any help would be appreciated.

'Bug':  Since BETWEEN is inclusive, a 10:00 delivery will be in the '0900-1000' bucket.

See the [pivot] tag for discussion of how to solve your problem.  Here's a crude attempt:

SELECT  CONCAT(hr, "-", hr+1, "00") AS TimePeriod
        SUM(IF(service_type = 'SERVICE1', ct, 0)) AS SERVICE1,
        SUM(IF(service_type = 'SERVICE2', ct, 0)) AS SERVICE2,
        SUM(IF(service_type = 'SERVICE3', ct, 0)) AS SERVICE3,
        SUM(IF(service_type = 'SERVICE4', ct, 0)) AS SERVICE4
    FROM (
        SELECT  HOUR(Delivery_Time) AS hr,
                service_type,
                COUNT(*) as ct
            FROM service_data
            GROUP BY 1,2
         ) AS x

Notice that I approached the hours in a different way.

To do "outside", change CONCAT(hr, "-", hr+1, "00") AS TimePeriod to

IF ( hr BETWEEN 8 AND 20,   -- assuming hours are 08:00 to 20:59
     CONCAT(hr, "-", hr+1, "00"),
     'Outside Opening Hours' )  AS TimePeriod


Oracle update join tables I want to update my db records, which are related one to many. Please consider the following example :

A person can have many contacts

Stating above implies that contact table has a person_id column. And also adding to the context the contact table has current_year_id column. Table person has phone and fax columns.

Now I added phone and fax columns to the contact table and I want to copy the values of fax and column to the contact table from person table. However I want to set these values to the contact associated with person which has current_year_id from current_year table.

Here is what I tried so far, and am surprised why is not working :

UPDATE CONTACT SET PHONE = p.PHONE, FAX = p.FAX
      FROM PERSON p
      INNER JOIN CONTACT on CONTACT.PERSON_ID = p.PERSON_ID
      WHERE CONTACT.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
      )

However select brings back the records :

SELECT * FROM PERSON p
INNER JOIN CONTACT on CONTACT.PERSON_ID = p.PERSON_ID
WHERE CONTACT.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
      );

Tried another variant :

UPDATE CONTACT
        SET CONTACT.PHONE = p.PHONE,
            CONTACT.FAX = p.FAX
        FROM PERSON as p
        WHERE p.PERSON_ID = CONTACT.PERSON_ID
        AND CONTACT.CURRENT_YEAR_ID = (
            SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
          );

Another one :

UPDATE CONTACT
        (SELECT PERSON.PHONE as PHONE, PERSON.FAX as FAX
         FROM PERSON
        ) p
        SET CONTACT.PHONE = p.PHONE,
        CONTACT.FAX = p.FAX
        WHERE CONTACT.CURRENT_YEAR_ID = (
                SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
              );

And get very informative error from sql developer :

SQL Error: ORA-00933: SQL command not properly ended

What can I do to achieve this?

The error points to a syntax problem. When you want to update multiple columns at once, you list all of the columns to be updated first, followed by all of the values to use (in the same order that the columns are listed). For example:

UPDATE CONTACT C
SET (C.PHONE, C.FAX) = (
    SELECT P.PHONE, P.FAX
    FROM PERSON P
    WHERE P.PERSON_ID = C.PERSON_ID
)
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

(You'll have to check my logic, because I'm not entirely clear on your relationships.)

Or, instead of doing an update with a correlated sub-query (as above), you can write it as a merge:

MERGE INTO CONTACT C
USING (
    SELECT PERSON_ID, PHONE, FAX
    FROM PERSON
) P
    ON (P.PERSON_ID = C.PERSON_ID)
WHEN MATCHED THEN UPDATE
    SET C.PHONE = P.PHONE, C.FAX = P.FAX
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

And another way:

UPDATE
    ( SELECT c.PHONE, c.FAX,
             p.PHONE AS P_PHONE, p.FAX AS P_FAX
      FROM PERSON p
        INNER JOIN CONTACT c ON c.PERSON_ID = p.PERSON_ID
      WHERE c.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
        )
    ) x
SET
    PHONE = P_PHONE, FAX = P_FAX ;


How to set a trigger on delete multiple parent and child rows? Below are the tables of relevance:

Table_B
B_ID(PK)  Name  A_Id
1         A     1
2         B     1

Table_C
C_ID   B_Id(FK)
1      1
2      1

Now I want to delete rows from Table_B and from its child table Table_C.
Below is the query I am firing:

delete from Table_B where A_Id = 1

I want to delete all rows from its child too. Is it possible with a Trigger? I have made a Trigger on Table_B, but it deletes only one row; so I have to run a loop.

Thanks.

I don't see the need to use a trigger. You could use two DELETEs:

DELETE C
FROM Table_C C
INNER JOIN Table_B B
    ON C.B_ID = B.B_ID
WHERE B.A_ID = 1;

DELETE FROM Table_B
WHERE A_Id = 1;

If you want to use a trigger, you can use this code inside:

DELETE C
FROM Table_C C
INNER JOIN DELETED B
    ON C.B_ID = B.B_ID;

Justa as a note, remember that if you have a foreign key in that table, you could configure it ON DELETE CASCADE (but be careful with your deletes)


Storing blobs outside the database I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, there was a high rate of duplication, not major but 5-10%.

I wanted to store those documents outside of the database so they could be backed-up via incremental tar, and reduce the size of the pg_dump database backup.

I came up with a solution I want to share below, using plperlu. Any comments or further optimisation ideas will be appreciated!

The following solution allows for storing/retrieving blobs externally, avoiding any duplications, stored in a tree of folders and files named after the blobs' own md5 hashes, in such a way the can be queried and manipulated as if they were simple database bytea columns. All files are stored in the folder $PGDATA/extstore.

Documents are saved to $PGDATA/extstore/db/zzz/yy/xxxxxx, where db is the current database name, zzz is a specific separate folder (optional), yy are the first X digits of the blob's md5 hash, and xxxxxx is the blob's md5 hash. The higher the value of X, the smaller the number of files per folder, i.e. for X=1, all files are distributed in 16 folders (named 0 to f), for X=2, all files are distributed in 256 folders (named 00 to ff), and so on.

There is a chance that two different files will produce the same MD5 hash (and therefore one would overwrite the other), however the chance is indeed very small, 1 in 2^128: not impossible, but incredibly unlikely.

I've created all of the following functions in a separate schema, called extstore:

CREATE SCHEMA IF NOT EXISTS extstore;

The following functions save a file inside the "extstore" repository:

SET ROLE postgres;
CREATE OR REPLACE FUNCTION extstore.f_save_file_pl(text, int, text, bytea) RETURNS void AS $$
  use strict;

  my($folder, $level, $md5, $contents) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  $md5 = lc $md5;
  $md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
  $level >= 0 && $level < 4 or die 'Invalid level';

  my $err = 'Cannot create folder ';

  my $dir = 'extstore';
  if (! -d $dir) { mkdir $dir or die "$err$dir"; }

  $dir .= "/$db";
  if (! -d $dir) { mkdir $dir or die "$err$dir"; }

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    if (! -d $dir) { mkdir $dir or die "$err$dir"; }
  }

  if ($level) {
    my $subf = substr $md5, 0, $level;
    $dir .= "/$subf";
    if (! -d $dir) { mkdir $dir or die "$err$dir"; }
  }

  my $file = "$dir/$md5";
  if (! -f $file) {
    open(my $out, '>:raw', $file) or die "Unable to create file $file: $!";
    print $out decode_bytea($contents);
    close($out);
  }

  return;
$$ LANGUAGE plperlu SECURITY DEFINER;

CREATE OR REPLACE FUNCTION extstore.f_save_file(p_folder text, p_level int, p_data bytea) RETURNS text AS $$
SELECT md5
FROM (
  SELECT md5, extstore.f_save_file_pl($1, $2, md5, $3) AS none
  FROM (
    SELECT md5($3) AS md5
  ) a
) a;
$$ LANGUAGE SQL SECURITY DEFINER;

The following functions read a file from the "extstore" repository:

SET ROLE postgres;
CREATE OR REPLACE FUNCTION extstore.f_read_file_pl(text, int, text) RETURNS bytea AS $$
  use strict;

  my($folder, $level, $md5) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  $md5 = lc $md5;
  $md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
  $level >= 0 && $level < 4 or die 'Invalid level';

  my $dir = 'extstore';
  if (! -d $dir) { return undef; }

  $dir .= "/$db";
  -d $dir or return $dir;

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    -d $dir or return undef;
  }

  if ($level) {
    my $subf = substr $md5, 0, $level;
    $dir .= "/$subf";
    -d $dir or return undef;
  }

  my $file = "$dir/$md5";
  -f $file or return undef;

  open(my $out, '<:raw', $file) or die "Unable to open file $file: $!";
  local $/ = undef;
  my $contents = encode_bytea(<$out>);
  close($out);

  return $contents;
$$ LANGUAGE plperlu SECURITY DEFINER;

CREATE OR REPLACE FUNCTION extstore.f_read_file(p_folder text, p_level int, p_md5 text) RETURNS bytea AS $$
SELECT extstore.f_read_file_pl($1, $2, $3);
$$ LANGUAGE SQL SECURITY DEFINER;

The following functions obtain information of files stored in extstore:

CREATE OR REPLACE FUNCTION extstore.f_get_file_size(text, int, text) RETURNS int AS $$
  use strict;

  my($folder, $level, $md5) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  $md5 = lc $md5;
  $md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
  $level >= 0 && $level < 4 or die 'Invalid level';

  my $dir = 'extstore';
  if (! -d $dir) { return undef; }

  $dir .= "/$db";
  -d $dir or return $dir;

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    -d $dir or return undef;
  }

  if ($level) {
    my $subf = substr $md5, 0, $level;
    $dir .= "/$subf";
    -d $dir or return undef;
  }

  my $file = "$dir/$md5";
  -f $file or return undef;

  return -s $file;
$$ LANGUAGE plperlu SECURITY DEFINER;

CREATE OR REPLACE FUNCTION extstore.f_get_files(text) RETURNS SETOF text AS $$
  use strict;

  my($folder) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  my $dir = 'extstore';
  if (! -d $dir) { return undef; }

  $dir .= "/$db";
  -d $dir or return $dir;

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    -d $dir or return undef;
  }

  open(my $out, '-|', "/bin/find '$dir' -type f -printf '%f\\n'") or die "Unable to list folder $dir: $!";
  while(<$out>) {
    chomp;
    return_next($_);
  }
  close($out);

  return undef;
$$ LANGUAGE plperlu SECURITY DEFINER;

For example, the following small text document

abcd1234
ASDF

has an md5 hash of '8cfc4f81e28149a4d4c79dae83d7ceb3', and the following function call from the database template1

SELECT extstore.f_save_file('scans', 2, 'abcd1234\015\012ASDF'::bytea);

saves it in $PGDATA/template1/scans/8c/8cfc4f81e28149a4d4c79dae83d7ceb3.

The file can then be read via:

SELECT extstore.f_read_file('scans', 2, '8cfc4f81e28149a4d4c79dae83d7ceb3');

The size of the blob can be queried without having to read the file to memory and calculate the size of the blob, by simply getting the file size from the filesystem:

SELECT extstore.f_get_file_size('scans', 2, '8cfc4f81e28149a4d4c79dae83d7ceb3');

Finally, the following function will produce a list of all file names (i.e. the blobs' MD5 hashes) within a folder (in this case $PGDATA/extstore/template1/scans):

SELECT * FROM extstore.f_get_files('scans');


ClearDB Granting Created User Access I added the ClearDB addon to Heroku, but upon using the created username to create a new database, the error appeared:

Executing:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8 ;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1044: Access denied for user 'bcb4810d4a34ac'@'%' to database 'androidchatterdatabase'
SQL Statement:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8

Viewing the GRANTS for this user, it notes:

Given the readout notes on database heroku_09ace026abf6348, I have ALL PRIVILIEDGES

Attempting to access the Users and Privileges tab to alter the user, the below returns:

At present, I am stuck.

How can I alter the access so I can create a database?

Old question, but never answered.
The correct answer is that ClearDB does not allow certain functionality on  multi-tenant cluster, including create database, views, UDF's and triggers.

You would need to upgrade to a dedicated cluster in order to perform this statement.


Retrieve value of select query in a table column I need an alternative to cursor/while loop for below query:

We have a table as given in below screen shot containing the following columns: ID, Module, Query.  The table contains nearly 100,000 records.  Currently we are using cursor and a while loop to insert the output of the select query to a temporary table which we are joining with the actual table to update based on ID.

I need a help in writing a query that takes the Query column as input and gives the result as value like a function.

I tried creating a function which executes dynamic SQL but it failed because EXEC and sp_executesql aren't allowed within a function. Attempts to use a stored procedure also didn't work because we can pass only one query as a parameter from this table to get output as value.

Each query returns a single value (one column, one row). Different queries may return values with different datatypes; all should be converted to nvarchar.

Any solution would be greatly appreciated.

I've tested the following (in SQL Server 2014, but it should work at least as far back at 2008), and I believe it does what you want:

CREATE TABLE QueryList (Id int, Name nvarchar(128), Query nvarchar(4000));

INSERT INTO QueryList
VALUES (1, 'Date', 'SELECT GETDATE()')
      ,(2, 'String', 'SELECT ''What''''s this? Just a varchar string...'' as StrVal')
      ,(3, 'Server', 'SELECT @@SERVERNAME')
;

CREATE TABLE results (Id int, Name nvarchar(128), Result nvarchar(4000));

-----

DECLARE @stmt nvarchar(max);

SELECT @stmt = stuff( (SELECT N'UNION ALL SELECT TOP (1) ' + CAST(Id as nvarchar(10))
                             +N' as Id, ''' + Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
                             +N'FROM (' + Query + N') AS qr ([Result])
'
                         FROM QueryList
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                     ,1,9,'INSERT INTO results') + ';'
;

SELECT @stmt; -- FOR TESTING - displays the resulting query

EXECUTE sp_executesql @stmt;

SELECT * FROM results;

The central query takes all the records from #QueryList, and builds an INSERT statement to place the id, name, and the query result from each row into the #results temp table. Each row's values are in their own SELECT statement, and all the statements are combined into a single result set with UNION ALL. I have taken text files with thousands of rows, converted each one into a similar SELECT ... UNION ALL, and used that to successfully insert thousands of records into SQL Server tables; I don't expect the number of statements being unioned together to be an issue (but I can't guarantee there isn't a limit at some point).

If you haven't seen it before, this uses FOR XML PATH to concatenate the UNION ALL SELECT ... from each row's values into a single string, the STUFF replaces the first UNION ALL with the actual INSERT INTO #results. See this article for more information on aggregating string data across multiple rows (or, move to SQL 2017 and use STRING_AGG()).

Something else that you may not have seen before is FROM (<subquery>) AS qr ([Result]). Just as the AS qr makes qr and alias for the subquery, ([Result]) provides a list of aliases for the columns (in our case, column) in the subquery.

Here's the query that's built from the above data:

INSERT INTO #results SELECT TOP (1) 1 as Id, 'Date', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT GETDATE()) AS qr ([Result])
UNION ALL SELECT TOP (1) 2 as Id, 'String', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT 'This is just a varchar string' as StrVal) AS qr ([Result])
UNION ALL SELECT TOP (1) 3 as Id, 'Server', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT @@SERVERNAME) AS qr ([Result])
;

And, the contents of #results after the query is executed:

Id   Name     Result
---- -------- --------------------------------------
1    Date     Jul 14 2017 12:19PM
2    String   What's this? Just a varchar string...
3    Server   MYSERVERNAME\INSTANCE

Notes:

If your query cannot trivially be used as a subquery (for instance, if you had a query in #QueryList that used a CTE), this will fail.
If you included a closing ; at the end of a query in #QueryList, it will fail (special case of not being trivially used as a subquery; I almost included this in my #QueryList queries, by force of habit).
To be on the safe side, this allows for the possibility that some query may return multiple rows, and simply selects the first row as the "right" value, at random.
If you do run into an issue with the number of statements being combined via UNION ALL, you can break this down into smaller chunks. Assuming that Id and Name taken together, are unique in #QueryList, then:

SELECT @stmt = stuff( (SELECT TOP (10000) N'UNION ALL SELECT TOP (1) ' + CAST(ql.Id as nvarchar(10))
                                        +N' as Id, ''' +ql. Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
                                        +N'FROM (' + ql.Query + N') AS qr ([Result])
'
                         FROM #QueryList ql
                                LEFT  JOIN #results r ON (ql.Id = r.Id AND ql.Name = r.Name)
                        WHERE r.Id IS NULL
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                     ,1,9,'INSERT INTO results') + ';'
;

would let you grab 1000 #QueryList rows at a time, and only grab ones where the Id and Name don't already exist in #results. You'd have to put this in a loop, and keep going until there were no more rows left in #QueryList. @stmt is NULL if there are no more rows to process, so you could use that to break out of the loop.

Again, I don't expect this to be an issue. And, even if it is, while it takes you back to a WHILE loop, you're dealing with 10,000 (or more) rows at a time, not one. It should be faster than your current cursor implementation.

And - if you find that there are some queries that cause problems, this could be useful for testing; go through subsets of data until you find a set that doesn't work, then check those queries to see if there's an issue with one.


oracle 11g Grant on dbms_lock not persisting I am using oewizard from swingbench to create an order-entry like schema on oracle 11g. When I run the wizard I get the following errors:

ORA-01031: Cannot grant execute on dbms_lock to soe: Insufficient privilege
ORA-01031: Cannot GRANT SELECT ON SYS.V_$PARAMETER to SOE: Insufficient privilege

Now I am using the SYSTEM user as the DBA to log into the account from oewizard. Now my first question:

Does the SYSTEMuser have SYS_DBAprivilege from the beginning

I explicitly grant DBA privilege to SYSTEM from sqlplus (GRANT DBA to SYSTEM; Grant Succeeded) but while connecting via SYSTEM (the same user) through JDBC (oewizard's back end), I still get the same error. So my next question is:

When logged in from sqlplus (as SYSDBA), don't the grants assigned to a given user persist even after we exit sqlplus?
If so what would be the best way to assign GRANTs to a given user so that they persist?
Is there no way we can log in as SYSDBA via JDBC since when I tried it, it didn't allow me

Keen to hear from you

From Oracle documentation-

  When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

SQL> conn system
Password:

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA

SQL> show user;
USER is "SYSTEM"

SQL> grant execute on dbms_lock to jay;
grant execute on dbms_lock to jay
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

The error has occurred because the SYSTEM user has no 'EXECUTE' privilege with GRANT option on DBMS_LOCK.

SQL>conn / as sysdba

SQL> select * from user_tab_privs where grantee='SYSTEM' and table_NAME='DBMS_LOCK';

no rows selected

Also, the DBA role has no 'EXECUTE' object privilege on DBMS_LOCK package.

SQL>  select * from role_tab_privs where role='DBA' and table_name='DBMS_LOCK';

no rows selected

In order to grant execute privilege, you need to connect as SYSDBA which connects to SYS schema, owner of DBMS_LOCK, or grant 'EXECUTE' privilege with GRANT option to the user performing the grant.

[oracle@myserver ~]$ sqlplus system as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 2 14:45:27 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show user;
USER is "SYS"

SQL> grant execute on dbms_lock to jay;

Grant succeeded.

  When logged in from sqlplus (as SYSDBA), don't the grants assigned to a given user persist even after we exit sqlplus?

Yes, they persist.

  Is there no way we can log in as SYSDBA via JDBC since when I tried it, it didn't allow me.

Here is a sample connection string which works fine for me.

Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@host:port/SID","sys as sysdba","password");


Sort products by image first and by name ASC I have a table with products, some products have images, some of them don’t. I have to sort these products by name ASC but first products should be these with an image (field isn’t empty string), and after that it should list the products without image, also name ASC. Thanks for help in advance!

Suggest an additional sorting column, imgsort, used as the first
criteria for the ORDER BY clause. Something along these lines:

   SELECT
       fields, ...
       , IF(image="",0,1) as `imgsort`
   FROM ...
   ORDER BY `imgsort` DESC, ...
   ;


How best to store multi-dimensional timeseries data in a (R)DBMS environment? Any suggestions on how multi-dimensional time-series data should be stored and organized in an RDBMS environment? For example, we have multiple experiments from multiple customers. This translates well to say a NetCDF file type structure, where one NetCDF can be for one customer project, and it contains several data variables overtime. This image explains the concept.

From my preliminary research, this seems like an unsolved problem or one with a complicated solution. Are there any recent (not necessarily optimal) attempts at this?

I think you're over-complicating things.  For this example

Modeling this as a relational table is simple:

create table Forecast
(
  reference_time datetime,
  latitude float,
  longitude float,
  precipitation float,
  temperature float,
  constraint primary key (reference_time,latitude,longitude)
)

It may not be extremely efficient for storage space and processing, depending on data structures and algorithms used by
the RDBMS that implements it.  But it's logically very straightforward to model this as a relation.


DBCC DBREINDEX in MS SQL Server 2008 We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER INDEX command with rebuild, also, we have performance problems right now with database, could it be related to using old method for reindexing ? We have MS SQL Server 2008 (11.2)

As it states on the ALTER INDEX documentation, using ALTER INDEX ...REBUILD is equivalent to DBCC DBREINDEX.

ALTER INDEX has far more functionality in terms of other arguments and DBCC DBREINDEX is deprecated, however the performance issues you face should not  be related to using the old method of re-indexing.


Connecting to server which located remotely throws timeout exception Below are the steps which I follow while connecting manually:

Connect to VPN
RDP to the remote machine using my credentials
Open SQL Server Management Studio
Connect to Server > database engine, Server Name > "Name", Authentication >  Windows Authentication
Run query

And the connection string that I am using is:

ConnectionString = "Data Source =tcp:<IPAddress of RDP Machine>\<ServerName>;
Initial Catalog =<DBName>; User Id = <Username>;
Password = <Password>; Trusted_Connection = True;
Integrated Security = SSPI; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False"

In your manual procedure you state that you specify "name" in the server name, not an instance.

However in your connection string you specify

Data Source =tcp:<IPAddress of RDP Machine>\<ServerName>;

Which indicates <servername> should be used as an instance name.

You should probably remove \<servername> part of your connection string.

You should also verify the necessary ports are open on the firewall and make sure SQL Server is configured to allow remote connections as well as make sure tcp/ip is in the enabled protocols.

There is a decent MSDN blog post covering a lot of other steps you can take to troubleshoot your connectivity issues: Troubleshoot Connectivity/Login failures (18456 State x) with SQL Server


How can I get the correct offset between UTC and local times for a date that is before or after DST? I currently use the following to get a local datetime from a UTC datetime:

SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)

My problem is that if daylight savings time occurs between GetUTCDate() and @utcDateTime, the @localDateTime ends up being an hour off.

Is there an easy way to convert from utc to local time for a date that is not the current date?

I'm using SQL Server 2005

The best way to convert a non-current UTC date into local time, prior to SQL Server 2016, is to use the Microsoft .Net Common Language Runtime, or CLR.
The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...
For one of the many examples, check out Harsh Chawla's blog post on the topic.
Unfortunately, there is nothing built-in prior to SQL Server 2016 that can handle this type of conversion, save for CLR-based solutions.  You could write a T-SQL function which does something like this, but then you'd have to implement the date-change logic yourself, and I'd call that decidedly not easy.


Monitoring Log-Shipping for Multiple Databases We are setting up log-shipping for multiple databases hosted at different server and while process began, I noticed that only one SQL job is being retained at Monitoring Server.

Our plan was to have one monitoring server which will be monitoring all the replication between Production and DR for databases and we can set-up alert in case any of the replication goes bad.

Kindly suggest how to make multiple jobs possible on the monitoring server.

After spending considerable amount of time in setting up log-shipping for multiple database and trying to figure out why only one alert job gets created - had no clue on why however one thing became clearer as we kept on configuring databases and jobs were getting over-written that even though there is only one job being retained at SQL Server Agent Job level, it does its job for all databases and alerts us in case any of the underlying database replication fails or has some issue in back-up, copy or restoration.

I am not sure why MSSQL doesn't support having multiple jobs created however its doing its intended job internally. I also have been checking "Transaction Log Shipping Status" report can monitor the status of all databases.

I also checked two tables from msdb database at monitoring server and ensured that log-shipping is working as expected.

Thank you for your support. This could be helpful to somebody who is setting up log-shipping for the very first time and wants to use monitoring server.


How to get only month from MySQL date/time? In my MySQL server, I have rows that have the date of my created content.
Example: 2013-03-17 02:53:47

For now, I am using:

$date = $fetch[thedaterownamegoeshere];

<?php echo $date; ?>

But instead of getting the full content of the date (2013-03-17 02:53:47), I'd like to get only the month – in this example, I wish to have the number "03" as the result. How can I do that?

SUGGESTION #1
You could use the MONTH() function
mysql> select MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            3 |
+--------------+
1 row in set (0.03 sec)

mysql> select MONTH('2013-03-17 02:53:47');
+------------------------------+
| MONTH('2013-03-17 02:53:47') |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.04 sec)

mysql>

You would have to put the MONTH function into the SQL command you are running.
SUGGESTION #2
You could also try the PHP Date Function
$month = date("m",$date)

Also DATE_FORMAT(date_column, '%c') to get '3' as result and DATE_FORMAT(date_column, '%m') to get '03' as result.


SQL large table design I have a general question about SQL Server 2008 table(s) design.  We currently have a table that is over 600GB and grows at about 3GB a day.  This table has the appropriate indecies but is becoming a major hangup when running queries and just because of its size.  The question is should I split the table into multiple tables by year and month (this would fit how other departments split their large data sets up) or should we leverage the partitioning that is built into SQL Server.  It appears that using the partitioning would require less code changes.  From what I read when partitioning you still just query one table and the server handles how to get the data.  If we went the multiple table route, we would have to handle pulling data from multiple tables.

"This table has the appropriate indecies but is becoming a major hangup when running queries"

Partitioning alone doesn't help query performance unless SQL Server is able to eliminate partitions when running a query.  Your WHERE clause needs to line up with the way you partition.  We only get one field to use as a partitioning field, so if that field isn't included in your WHERE clause, you're still likely to scan the entire table despite having partitions.

"and just because of its size."

Partitioning can make certain maintenance operations easier, but there's still things we can't do on a partition-by-partition basis.  If index maintenance and stats updates are causing you problems, you're better off splitting the design into an archive table and a live-updated table.  When you need to periodically move data from the live table to the archive table, you do that, rebuild the indexes with 100% fill factor, update stats with full scan, and then set its filegroup to read-only.  Partitioning can help with archive table loads - but partitioning the live table may not.  (I'm tossing out several advanced concepts here as if it's quick and simple, but I'm just sketching out some background here.)

"It appears that using the partitioning would require less code changes."

Sorta kinda - it looks that way at first glance, but the more you get into it, you've got options like partitioned views.  You can rename the existing table, put in a view in its place, and then you can make your own changes to the underlying tables (and add multiple tables) without changing your app.

I've written more about the pitfalls of partitioning here:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/


Index creation leads to large transaction log growth I have an approximately 1 billion row table. I then create an index as follows:

CREATE NONCLUSTERED INDEX [IX_Index1]
ON [dbo].[MyTable]( [CustomerID] ASC )  -- CustomerID is uniqueidentifier
WITH (DATA_COMPRESSION=PAGE);

This results in a 9.15GB index (compressed), however the transaction log went from empty to 158GB while the index was being created (no other DB activity). Why is the amount of log space used that much higher than the resulting size of the index?

The database is SQLServer 2014 Enterprise in full recovery mode in an availability group.

Update
I dropped and recreated the index with the option SORT_IN_TEMPDB = ON. The resulting transaction log writes were 10.4GB for the DB's tlog and 1.27GB for the tempdb tlog.

Referring to MS documentation here. Also Creating a 60GB Index on SQLFool.

What I believe is happening is that when the index is being built with SORT_IN_TEMDB=OFF, the entire index build processed is logged via the DB's transaction log (DB is set to full recovery). (In addition, I expect there to be a high number of page splits due to the scattergun-nature of the GUID column that is being indexed)

When the index is built using SORT_IN_TEMPDB=ON, then most of the transaction log activity is going to be against tempdb which is in SIMPLE recovery mode.


Non-clustered index covering a clustered index I have a table that has the following indexes:

IXC clustered index that includes a single column : A asc.

IX1 that includes two columns : A asc, B asc.

In this case ICX seems like a duplicate index of IX1. Is it a good idea to delete ICX and make IX1 the clustered index in that case? Or is there scenarios where this would be a bad idea?

DDL example :

CREATE TABLE [dbo].[foo] (
    [A]      NVARCHAR (20)  NOT NULL,
    [B]      INT           NULL,
    [C]      NVARCHAR (20)  NOT NULL,
    [D]      NVARCHAR (20)  NOT NULL,
    [E]      NVARCHAR (20)  NOT NULL,
);

GO
CREATE CLUSTERED INDEX [IXC]
    ON [dbo].[foo]([A] ASC);

GO
CREATE NONCLUSTERED INDEX [IX1]
    ON [dbo].[foo]([A] ASC, [B] ASC);

Take a look at this article on SQLShack which gives a great description of what to consider when selecting clustered index keys. To summarise:

Keep it short - the clustered index key is included in all non-clustered indexes, so a wide key means more data space required on disk, and in the buffer when reading pages.
Keep it static - If your clustering key values are constantly changing, SQL will be forever re-sorting the clustered index to insert in the correct place.
Use an incrementing value - An IDENTITY column or sequential value helps speed up the INSERT processes by ensuring the new values are always being added to the end of the index, rather than somewhere in the middle.
Make it Unique - If the key column(s) are not unique, then SQL Server will add a uniqueifier. This only adds more space and requires more effort by the DB engine to maintain the index.
Sorting - The clustering key also defines the sort order for the data as it is stored. This means picking a clustering key that can or will be used reasonably frequently for sorting will only aid performance down the track and eliminate costly sort operators in query plans. NOTE: Character fields aren't great for sorting performance, so your current clustering key (A) doesn't fare well on this point.

So how do we apply this to your example?

You need to answer the following:

Is A+B short enough for a key? Probably, because just A is <= 22 bytes, so A+B is only <=26 bytes. Unless we're talking billions of rows, this is probably fine.
Is it static? Only you can answer this. Does the value in A or B change frequently? If so, then they're probably not good candidates for the clustering key.
Is A or B an incrementing value? I'm guessing A is unlikely to be an incrementing value as it is an NVARCHAR field, however, B might be since it is an INT.
Is A unique? Is B unique? Is A + B unique? If none of those is a yes, then probably not suitable as a clustering key.
What order do your queries normally sort in? If its A or B or A+B then these columns may be a good clustering key.

Once you've answered those questions, you should know if A or A+B is the better choice for a clustering key. If A or A+B still seem like equally valid choices for a clustering key, then considering the fact that you have an NCI with A + B would indicate you have a reasonable number of queries using both A and B as a predicate. Given that and the conditions above, A+B is likely a good candidate for a clustering key over just A if they equally satisfy the points from above.


Find current owner of custom data type? In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x.

I know this can be solved by running ALTER TYPE x OWNER TO <database owner> but what I'm curious about is how I can check the current owner of this specific datatype. \dT+ x does not give me information about the current owner.

How can I extract more information about custom data types, including the current owner?

Up until 9.4

You can go and ask the system catalogs (pg_type, to be precise):

SELECT rolname
  FROM pg_type t
  JOIN pg_authid r ON typowner = r.oid
 WHERE typname = 'bla';

 usename
─────────
 dezso

From PostgreSQL 9.5 on

From this version on, there is a new object identifier type called regrole, and it makes the query a bit simpler:

SELECT typowner::regrole
  FROM pg_type t
 WHERE typname = 'bla';

But there is more to it - the new 9.5 version of psql already displays the owner when using \dT+:

      Schema       | Name | Internal name | Size | Elements |  Owner   | ...
-------------------+------+---------------+------+----------+----------+ ...
 zel_api_r14_00_09 | bla  | bla           | 4    | a        | postgres | ...


Alternative to RDBMS: Modeling Exam/Testing data I'm a born and bred relational database guy and C# programmer dude. IBM DB2 & VB.Net in school. TSQL & C# at work.

I'm planning a project for fun: Designing a Testing platform that goes from DB through application/website.

When you think of "Exam" or "Test" Questions/Answers, there are many question types (not my picture, but good example of the multitude of question types):

I want:

Test (ex: Comptia A+)
Classification of question (This question meets objective or domain X of test Y. Test Y needs 3 questions for domain X.)
Multiple question types (not necessarily limited to those in the picture above)
Notes about correct/incorrect answers (This is why ___ is correct. Reference link here.))
History/reversioning

I know I can do all of this in Sql Server, but I don't feel as if it would be the most efficient choice. I can't bring myself to like options that come to mind: Tables per question type. EAV tables. Embedded data structures of some sort.

I've never felt the need to learn "NoSQL" and was wondering if there are any "platforms" that would be a stronger candidate for this than TSQL? Or should I stick to what I know and do what it takes to make it work in a traditional RDBMS?

Using an RDBMS is fine, but the sticky part is how to store the parameters needed to render each type of question.

I would caution against having separate table definitions (or lots of type-specific columns) for each type of question. While these details (such as the list choices for a multiple-choice question) can be seen through a relational lens, it would, IMHO, be overkill to try to do so.

Instead, I would suggest having a simpler Question table design for that is agnostic to the type of question (other than a field indicating the type, of course), and store the information that is specific to the question type (a list of choices for a multiple-choice question and which is correct, etc.) in an xml field (or a varchar() with JSON, HTML, or some other semi-structured format that meets your needs).

This will allow you to add and refine the supported question types without having to change the table definitions, and CRUD operations for every question type will be essentially the same. So you get the flexibility of a NOSQL approach for the portions of your model that are quite variable without losing the relational structure of the tests and questions overall.


Delete and archive old data from several related tables Our schema looks like this:

table tblTrip: stores start and end trip date and time
table tblTripData: keeps all the trip data
table tblTripEvent: stores events related to the particular trip.

Then we have two more tables, tblTripEmail and tblTripSms, which store the email and SMS that have been sent according to the events.

We want to keep just 3 months old trip data, we would like, to from time to time, to delete and archive the rest to be stored in offline database.

What is the best mechanism to store in an offline database? I am not sure how partitioning would be helpful because the tables are linked to each other here.

Below is the table desc for tblTripData

CREATE TABLE IF NOT EXISTS `tblTripData` (
  `tripDataID` int(11) NOT NULL AUTO_INCREMENT,
  `tripID` int(5) NOT NULL,
  `deviceSerialNumber` varchar(20) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `speed` float NOT NULL,
  `course` int(3) NOT NULL,
  `gpsdateTimer` datetime NOT NULL,
  `insertDateTime` datetime NOT NULL,
  `odoMeter` float NOT NULL DEFAULT '0',
  `driverID` int(5) NOT NULL,
  `eventAlertID` int(11) NOT NULL DEFAULT '0',
  `tripDataInsertDateTime` datetime NOT NULL
  PRIMARY KEY (`tripDataID`),
  KEY `dateTime` (`gpsdateTimer`),
  KEY `tripID` (`tripID`),
  KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Try pt-archiver from Percona Toolkit, it permits to transfer data on the fly between two MySQL instances.

You can for instance use the --where option to filter result set by date.

It's particularly well designed for your needs, because you can transfer data and delete it from source in the same command.

Official page is here : pt-archiver

An example :

pt-archiver --source h=<server_source>,D=<database_source>,t=<your_table> --dest h=<server_target> --where "date_field < DATE_SUB(NOW(), INTERVAL 3 MONTH)" --limit 1000 --txn-size 1000 --statistics

Add the option --dry-run if you want to test.

Add the option --no-delete if you want to keep data on source DB.


Different time in different sessions I am connecting to same db from 2 different machines using SQL Developer and querying a table which stores timestamp.
But to my surpise the time value returned by query has a gap of one hour on different machine. How is this possible as we are conencting to same DB? and whats the solution.

Probably different timezone is configured on those machines. Check:

SELECT SESSIONTIMEZONE FROM dual;


Overwrote database with old mysql dump. How can I restore it with a more recent backup Have a server with multiple databases.  Imported wrong .sql from over the top of existing database.  I Have a rsync copy of the up to date files for that database.  What do I need to do to just restore that database without impacting the others?

You won't get a much better answer than here.
Basically, the part of this document that you want is here:

Restoring your MySQL Database
Above we backup the Tutorials database into tut_backup.sql file. To
re-create the Tutorials database you should follow two steps:

Create an appropriately named database on the target machine

If, as is your case, you only wish to restore 1 database of many in a schema, pay particular attention to the line above - it could save you a lot of time and trouble!

Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Note in particular the space between -u and uname and the missing space between -p and your password. This threw me for quite a while.
If you're in any sort of DBA role, this really should be your bread and butter. Many backup strategies also feed the backup into gzip or similar. The way to restore these is also covered.

To restore compressed backup files you can do the following:
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

Again, if you are in any sort of DBA role, you should be doing this at least every three months in order to ensure that all of your backup steps

a) work, and

b) can be performed efficiently with minimum service disruption to business/users.


Designing table for document annotation with future columns I am working on a web application that allows students/researchers to annotate PDF documents shallowly by (manually) counting interesting phenomena. Right now, there is a total of about 45 of these phenomena, most of which are nominal values, booleans or integers. The database is a MySQL database, and the amount of documents would be between 100 and 500, so the database is quite small.

The application adds these documents and their respective annotations to a corpus which will also be searchable (and filterable based on a large subset of these phenomena).

In the future, some phenomena may be added, or existing ones may be disabled or automatised through automatic tagging, etc.

This could be done by, for example:

1. Altering the table and adding columns via the web application

Ultimately the table might become too big (column-wise), but it is easy to implement, even if it seems clumsy to have a table with 60 columns. I'm not sure if it is as inefficient as it seems.

2. Storing the annotation part of the web application externally

For example, as JSON documents or in a nonrelational database such as MongoDB. This intuitively makes sense as I'm presenting the need for a flexible (and thus nonrelational/schemaless) solution for annotation. But connecting two databases together might unnecessarily increase the complexity of the application. Also, using foreign keys might become impossible this way (?).

3. Storing the annotations as one blob in a MySQL column

I have virtually no experience with blobs, as I've always avoided them. I read it in a different question

4. Storing all/new attributes in an EAV table in the MySQL database

Seems like a hacky way to mimic nonrelational behaviour in a relational database. I also read that it could cause performance issues.

I'd love some advice on which method is better or if there is something entirely different I should have a look at.

JSON documents are ideal for this purpose. MySQL can store JSON documents via its JSON datatype, and it has a variety of function for extracting information in such documents. However, MySQL doesn't currently have an effective native way of indexing JSON documents for fast search. Googling for "mysql json index" will render several strategies for doing so, some using triggers and external tables, others using indexes on computed columns.

PostgreSQL, however, has very good support for JSON datatypes including indexing, opening up documents as recordsets (using LEFT JOIN LATERAL in combination with jsonb_each is very powerful), and GIN indexes that support JSON documents (for both tags and contents). Unless you're heavily invested in MySQL already, PostgreSQL would be a great option for your application. Alternatively, you may want to consider PostgreSQL instead of MongoDB if you'd use it for this purpose alone. Additionally, PostgreSQL can easily access MySQL tables with via Foreign Data Wrappers, it has good support for regex text manipulation, and you can do all sorts of magic on JSON documents through standard perl libraries, using pl/perl stored procedures.


Small Squares or Blocks after string in table field My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query OPENQUERY command (displayed below).

I ran view A as a SELECT script in a new query window. Assuming the field to be varchar data type but not knowing the length, the LEN(VendorNo) returns 6 to 8 chars on Dev and 21 chars on Prod (with the remaining 13, or so, invisible).
When using SQL BIDS or exporting to Excel (on Prod), I see a series of small squares or blocks after the string. The ASCII equivalent is CHAR(32).

The service pack versions on both servers were different. The Dev server: Product Version - 9.00.4229.00, Product Level - SP3 and Edition – Standard.
The Prod server: Product Version - 9.00.1547.00, Product Level – RTM and Edition is Standard.

On a side note, I included a GROUP BY clause for “view A” in a project and noticed the characters appeared, but by removing GROUP BY also removed the square blocks. Any help or direction would be appreciated. Maybe updates to the SP on Prod? We also have a second test server that shows the same results as the Prod server.

-- My code --

CREATE view nav.vw_Vendor
AS
SELECT * FROM  OPENQUERY(NAVISION,
'SELECT Vendor.No_ AS VendorNo
FROM Vendor Vendor')

-- View A

SELECT VendorNo FROM nav.vw_Vendor

Some checks

Do both your Dev and Prod link to the same Navision DB?
Compare the rows in sys.servers, especially the is_collation_compatible column
Is the linked server via an ODBC/DSN or is it simple "SQL Server"? If ODBC then are settings the same in the DNS?

I suspect number 2 or 3 if the all your servers link to the same box.


Discover who changed SQL Server service state There's any way to find out who stopped\restarted\started the following services ?

SQL Server Database Engine
SQL Server Agent
SQL Server Analysis Services

Thanks !

This appears to be answered here:

https://stackoverflow.com/questions/496632/is-it-possible-to-log-who-started-or-stopped-a-windows-service/496675

Moral of the story is event viewer/ system logs should show you


Access denied for user 'root'@'localhost' I'm trying to login into fresh install mysql with:

sudo /usr/local/mysql/bin/mysql -u root

But getting following error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

The mysql server started with:

sudo /usr/local/mysql/bin/mysqld --user=root

What is the default login/password for mysql root user?

Thank you.

You can reinit the password :

1.stop mysql

/etc/init.d/mysql stop

2.start mysql safe :

mysqld_safe --skip-grant-tables &

3.connect to mysql

mysql -u root

4.change the password

mysql> use mysql;
mysql> update user set password=PASSWORD(”YOUR_PASSWORD”) where user=’root’;
mysql> flush privileges;
mysql> quit

5.stop mysql

/etc/init.d/mysql stop

6.start mysql

/etc/init.d/mysql start

Then you can connect to mysql with login root and the new password.


How SQL Server transaction works with log records My textbook says:

Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it looks real to you, only you can see that
result. Anyone else attempting to look, they could see the old value, or if they’re daring they could get a dirty read.

I am confused, so I will use a picture below to raise some questions:

and the t-sql code is

BEGIN TRAN
UPDATE checking
 SET Balance = Balance - 1000
 WHERE Account = 'Sally'    // original balance is 2000
--------------a checkpoint occurs ---------
UPDATE savings
 SET Balance = Balance + 1000
 WHERE Account = 'Sally'
COMMIT TRAN

and we know that a new log record describing the COMMIT will only be created in the log buffer after 'COMMIT TRAN' has been executed.

Q1- Why the result is only visible for me before COMMIT?
let's say a checkpoint happens after checking for sally has been minus -1000, and let's the the updated record is in page 4(in memory), since it hasn't been commit yet, so there won't be a log record in the log buffer, therefore there won't be a corresponding log record in disk(.ldf), but the cache page 4 will be written to disk (.mdf), so sin't that any user can see the latest change for the record?

Q2-How transaction control maintains atomicity in this case?

As I have discussed, when the checkpoint happens,  page 4(in cache, and the containing record's latest balance value: 1000) will be written to disk, and if there is a system failure immediately. After SQL server restarts, how does it know how to restore the record's balance(currently 1000) back to its original value(balance is 2000), since there is no log record in .ldf file?

Regarding visibility, SQL Server uses locking and/or row-versioning to implement isolation levels. Changes are always visible to the current session. Uncommitted changes are visible to other sessions only they are using the UNCOMMITTED ("daring they could get a dirty read"). Normally, other sessions will be blocked (due to locking) or will see the original value before changes (due to row versioning) until the transaction is committed.

Transaction isolation levels, and their implementation, are not related to transaction durability. It doesn't matter when log and data file writes occur for isolations levels to behave properly.

To guarantee durability and atomicity, log records are always written to the log before the corresponding data files. Importantly, this includes changes for both uncommitted and committed transactions. This write-ahead logging allows SQL Server to roll forward all transactions and then rollback uncommitted ones during recovery as well as when a ROLLBACK is performed.


Read GPS Coordinates into LineString from Table I found this beautiful piece of code which takes a linestring and
I can get the points from it:

DECLARE @GeographyToConvert geography
SET     @GeographyToConvert = geography::STGeomFromText('LINESTRING (26.6434033 -81.7097817, 26.6435367 -81.709785, 26.6435783 -81.7098033, 26.6436067 -81.709825, 26.6435883 -81.709875, 26.64356 -81.7100417, 26.6434417 -81.710125, 26.6433167 -81.7101467, 26.643195 -81.7101033, 26.6431533 -81.7099517, 26.643175 -81.7097867, 26.643165 -81.7097917, 26.6431633 -81.7097367, 26.6431583 -81.7097083)',4326);
select @GeographyToConvert.STPointN(1)
select @GeographyToConvert.STPointN(2)
select @GeographyToConvert.STPointN(3)

Returns:

0xE6100000010C273339106D6D54C073A32314B6A43A40
0xE6100000010C3F8C101E6D6D54C0984638D2BEA43A40
0xE6100000010CF205D26A6D6D54C050EA268CC1A43A40

From here, I can do what I need to do via the mentioned piece of code.

Instead of the prepopulated demo Linestring however, I have a table with GPS Coordinates
which contain the data from which I need to populate the @GeographyToConvert geography

declare @Geo table(long float, lat float)
insert into @Geo(  long, lat ) values
               ( -121.527200, 45.712113 ),
               ( -121.517265, 45.714240 ),
               ( -121.511536, 45.714825)

Select * from @Geo

Can I please ask help in getting the @Geo table values
into the @GeographyToConvert geography

You can use dynamic SQL to build the string to feed into STGeomFromText(). I don't think you'll be able to do it relationally (though, to be fair, this is not my expertise). This will get you going until (if) something better comes along.

declare @Geo table(long float, lat float)
insert into @Geo(  long, lat ) values
               ( -121.527200, 45.712113 ),
               ( -121.517265, 45.714240 ),
               ( -121.511536, 45.714825)

DECLARE
  @cols NVARCHAR(MAX) = N'',
  @sql NVARCHAR(MAX) = N'DECLARE @g GEOGRAPHY;
     SET @g = GEOGRAPHY::STGeomFromText(''LINESTRING (';

SELECT @cols += N',' + RTRIM(long) + N' ' + RTRIM(lat) FROM @Geo;

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql += @cols + N')'', 4326);'

SELECT @sql += N'SELECT @g.STPointN(' + RTRIM(ROW_NUMBER() OVER (ORDER BY long))
  + N');' FROM @Geo;

PRINT @sql;
EXEC sys.sp_executesql @sql;