Can I set PostgreSQL "stringtype=unspecified" behavior as default? I am trying to get Hibernate to save Java enum fields as enum columns in PostgreSQL 9.6. If I map the fields as @Enumerated(STRING), Hibernate naively uses the enum name as the column contents. This works splendidly for reads (it just gets the string representation, matches up and returns), but the server complains about the type mismatch when writing: expression is of type character varying.

The PostgreSQL JDBC driver has a parameter stringtype that will tell the server to infer the appropriate column type for string values, and using stringtype=unspecified successfully gets enum values stored. However, I will be deploying my application in an environment where the JDBC connection string is supplied externally, and it's not practical to add the stringtype property to it.

Is there a way I can configure the server (such as with a SET command) to treat connections as stringtype=unspecified by default?

Unfortunately the answer is no.
stringtype is not server parameter. It's JDBC's own client parameter.
Check the function here;

org.postgresql.jdbc.PgConnection.getStringVarcharFlag()

and here

org.postgresql.jdbc.PgPreparedStatement.getStringType()

You will see that JDBC sets parameter type to VARCHAR or UNSPECIFIED type according to the stringtype property.


SQL Server Management Studio 2016 I'm having a strange problem with my SSMS 2016. Within the query window backspace, del, up, down, left, right keys have stopped working. When I reset my environment settings with "Import and Export Settings" to default setting it works but the changes don't persist when I exit the SSMS client. Every time I launch SSMS 2016 I have to do this exercise.

I also have SSMS 2014 on the same system, which works with out any problem, I tried to uninstall and install the SSMS 16 client but no luck.

Any one having the same problem?

Help please, how can I uninstall SQL Server Management Studio 2016 completely?

This happened in SSMS 2017.  There was a pop-under "Add table to view" dialog that I didn't know about.
When I went to close SSMS it warned me about the dialog (which I could not close/cancel), then gave me an ugly exception about protected memory.
After restarting SSMS it all worked again.


"Print" specific columns through foreign keys I have three tables:

Users
Users_request
Users_applications

Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id

I try to do:

Users table has a column called name and User_applications has application_name.
I want to "print" only those columns with their rows through JOIN clause.

Desired Output:

 user| application_name|
-----|------------------
user1| application1
user2| application2
userN| applicationN

I have the next, but I want improve it not using hardcode Users_applications.id

SELECT Users.id, Users_applications.name
  FROM Users, Users_applications
 WHERE Users.id = 'User1' AND User_applications.id = n ;

Is there any other way to accomplish it, through only Users.id using JOIN clauses?

Thanks in advance.

Table Users has the id column, that column is related on
  Users_request.id finally Users_request.id is related with
  Users_applications.id

So Users.id = Users_request.id and Users_request.id = Users_applications.id which means that Users.id = Users_applications.id

Then you can do:

SELECT Users.id, Users_applications.name
FROM Users INNER JOIN Users_applications  ON Users.id = Users_applications.id
WHERE Users.id = 'User1';


Can I limit SQL server one database resources I have SQL server 2014 with resources 90G of RAM for the whole database engine, engine contain around 15 database instance, I want to make databases X to have at max 5G of RAM while other databases still share resources. please advise how to make it if its applicable .

Since you are using Developer Edition (and is cheap as compared Standard or Enterprise), you are better off installing another instance with 5GB Max Memory. Put the databases that you need less memory on this instance.

The rest of databases you can put on the instance with more memory.

Read up Tibor's blog post on Restricting memory using Resource Governor which references the link that @shanky referenced.

  A misconception I see from time to time is that the memory setting is to restrict the Buffer Pool, BP, (mainly used for caching pages from disk). This seems difficult since a page in BP has a cost, but isn't owned by whoever brought it into cache or who referenced it last time. So, it seems difficult with current implementation of cache handling and ageing to govern the BP memory for RG. What RG does govern is "working memory" for a query. Think memory used for sorting, locks, hashing and such.

Read up on : Performance issues from wasted buffer pool memory from Paul Randal.


Dynamic SQL Server cross tab I would like request how to do cross tab in SQL using two table.

I want achieve result :

        amy           pal          chin
amy               2017-02-18   2017-02-18
pal                            2017-02-18
chin 2017-02-18

Below is my stored procedure:

USE [IRIS]
GO
/****** Script Date: 02/20/2017 14:03:28 ******/
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[crosstab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[crosstab]
GO

CREATE PROCEDURE CROSSTAB
AS
DECLARE @SQL1 VARCHAR(8000)
DECLARE @VAR1 INT
DECLARE @VAR2 INT
DECLARE @VAR3 VARCHAR(100)
SET @VAR1=(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])
SET @SQL1='SELECT Mysingle, '
WHILE @VAR1 <> 0
BEGIN
SET @VAR3=(SELECT Mysingle FROM [IRIS].[dbo].[I_User] WHERE Id =@VAR1)
SET @SQL1=@SQL1 +' MAX(CASE Uids WHEN '+CONVERT(VARCHAR(10),@VAR1)+' THEN Dates  ELSE  '''' END) AS ['+CONVERT(VARCHAR(MAX),@VAR3)+'],'
SET @VAR1=@VAR1+1
END
SET @SQL1=LEFT(@SQL1,LEN(@SQL1)-1)
SET @SQL1=@SQL1+'
 FROM (SELECT
  A.UserId AS Uids,
  A.DateShcdule AS Dates,
  B.Mysingle AS Mysingle
  FROM [IRIS].[dbo].[NDRoaster] A LEFT JOIN [IRIS].[dbo].[I_User] B
  ON A.UserId=B.Id)AS MYTABLE GROUP BY Uids'
PRINT @SQL1
EXEC (@SQL1)
Go

I prompt error as below:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.  SELECT Mysingle   FROM (SELECT    A.UserId AS Uids,
  A.DateShcdule AS Dates,   B.Mysingle AS Mysingle   FROM
  [IRIS].[dbo].[NDRoaster] A LEFT JOIN [IRIS].[dbo].[I_User] B    ON
  A.UserId=B.Id)AS MYTABLE GROUP BY Uids  Msg 8120, Level 16, State
  1,Line 1 Column 'MYTABLE.Mysingle' is invalid in the select list
  because it is not contained in either an aggregate function or the
  GROUP BY clause.

I am following the article SQL Server: Dynamic Cross-Tab

There are 2 tables:

(1) user

[Id],
[Mysingle],
[Password],
[EmpId],
[LevelActive],
[DateCreated],
[LastLoginDate],
[DeptId] ,
[EmailId] ,
[Isactived],
[CommTypeId],
[IPAdd],
[HOD]

(2) Roaster

[NDR_Id],
[UserId] ,
[DateShcdule],
[DateCreated]

I'm still in learning process on SQL Server.

You are little modify the article example and modification contain error

@VAR1=(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])

will return more than 1 result, and use it as:

@VAR3=(SELECT Mysingle FROM [IRIS].[dbo].[I_User] WHERE Id =@VAR1)

in You case - WHERE Id =@VAR1
not correct

original article use MIN and MAX

@VAR1=(SELECT MAX(UserId) FROM [IRIS].[dbo].[NDRoaster])

You can use any other filter for warrant single result from subquery

Also - wrong loop,
in this case (for each UserID) define cursor as

(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])

and fetch records in loop

define cursor
tutorial


Cannot access mysql from a program outside I am running a VM that uses windows server 2012 R2 to host a mysql Database , the database works good , i can access it from inside or outside the VM, the problem is that once i try to use the database with Bdoc suite the connection is refused, here are some screens of my problem:

Is the problem comming from my database ? (despite the fact that i can access it using Navicat and the mysqlcmd and :

the firewall is turned off.

I've tried with other users that have the rights on the database.

I've tried using the IP of the VM , and it still did not work.)
Is there any other cause to this problem ?

From the screen shots it looks like Navicat is installed on the same machine as the MySQL server.
That jives with the messages we see there.
If Bdoc Suite is running from a machine outside the MySQL server, you would need to use either the ip address or the hostname to connect to your server.

There also may be an issue with the permissions on the account. MySQL has you specify not only the user, but the source of the connection to assign permissions.

If you want to connect from ANY other IP, you would want to create a user with a wildcard ip address and use it to connect.

To test this, you would want to log on locally with your root account and do the following:

    CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

    CREATE USER 'username'@'%' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

    FLUSH PRIVILEGES;

Then try connecting remotely with that new user.  If you prefer to scope your user permissions and know what ip they will be coming from, you can replace the '%' with the ip address you will be connecting from.


How to organize MySQL DB to allow an arbitrary counter for clicks, visits, etc in past 24 hours? If you're familiar with it, I used to use count.io which has been permanently taken down.   I basically want to create my own version for my own use.

Main question

How do I structure/organize my MySQL DB to allow to not only count page visits, but to also be able to tell how many of them are from the past 24 hours?

What I am trying to acheive

The way it'd work is, every time a user hits some page X, a "hit" is recorded.
The count.io API allowed me to record a hit with JavaScript, like this:

// Timestamp
var timestamp = Math.floor(Date.now());

// Grab product ID
var productNumber = $('#productDetails input[name=product_id]').attr('value');

// Record a hit to the DB under Product[ID]-group, as a timestamp
$.ajax({
    url:'http://count.io/vb/product' + productNumber + '/' + timestamp + '+',
    type: 'POST'
});

...I am not sure how the count.io DB was structured to allow this, but essentially, the count.io DB would receive/store my hits like this:

    - http://count.io/vb/product37/1468038731
    - http://count.io/vb/product37/1468048798
    - http://count.io/vb/product37/1468058931
    - http://count.io/vb/product37/1468069731
    ... potentially hundreds or thousands of such entries for any 1 product

When I'd retrieve it with JavaScript, I was then able to count how many timestamps there were within the week by doing something like this:

// Retrieve current product array
$.post(
    // 24 hour old timestamp
    var dayAgo = timestamp - 86400000;
    httpPrefix + 'http://count.io/vb/product' + productNumber,
    function (data) {
        var timedViewCounter = 0;
        var myStringArray = data.counts;
        var arrayLength = myStringArray.length;
        // Check each "timestamped hit" to see if it's older than 24 hours
        for (var i = 0; i < arrayLength; i++) {
            var itemTimestamp = data.counts[i].item;
            // Count up all visits that are 24 hours old or newer
            if (itemTimestamp >= dayAgo) {
                timedViewCounter++;
            }
        }
        // Show total count in past 24 hours
        var hotItem = '' + timedViewCounter + ' people have viewed this product in the last 24 hours
';
});

Question

How would I organize a MySQL DB that would allow this kind of functionality?  Would I create a new table for each page/product?  (that sounds crazy as there are over 1,500 relevant items)

OR

Would I just record the "timestamp hits" into the same field, and simply separate the values with a comma?

OR

Is there some magical functionality of MySQL that I am not aware of?

What count.io did

  Incrementing

http://count.io/vb/fruit/apples+

  Notice the + sign on the end of the URL.

  Returns this JSON:

{
   "item":"apples",
   "count":1,
   "group":"fruit"
}

  Use the "group" field to tie similar themed counts together. In this
  case it's "fruit."

  If you call the URL again, you'll notice the count is incremented by
  1.

{
   "item":"apples",
   "count":2,
   "group":"fruit"
}

  Get Count

http://count.io/vb/fruit/apples/

  Get Group Sum

http://count.io/vb/fruit/

  Returns this JSON::

{
   "group":"fruit",
   "sum":7,
   "counts":[
      {
         "item":"apples",
         "count":2
      },
      {
         "item":"oranges",
         "count":1
      },
      {
         "item":"bananas",
         "count":4
      }
   ]
}

The way I used it, is instead of "fruits", I'd have the group as the PorudctID and the items inside it were al unique timestamps with a count of 1.  I'd then run through all the timestamp items and see which are from within past 24 hours and that way, Id have the count of visitors in past 24 hours.

I want to recreate this functionality on my own server but have no idea how to structure the queries.   I'm leaning towards putting all timestamps into the same field but separated by commas... ?

3 suggestions...

Don't store all of "http://count.io/vb/product37/1468038731", it wastes space and time.  Trim it down to just the timestamp.

Don't store the time, bump a counter for the day (or hour, if you prefer).

Don't store anything, parse the web server logs later.

Now let me approach it a different way... What is the minimal information you want?  Exact times of all hits?  Running counter of hits?  Daily counts?  Keep in mind what information can be derived from what other info.  Hourly counts --> daily counts.  Running counter --> daily counts (if you grab the count every midnight).  Etc.


Oracle: database not showed I created an Oracle's container database (CDB) at CentOS named demo0, then I also created an user named c##demo.

The issue is that the dabase is not showed at using ORACLE_SID=demo0 but it's only showed with ORACLE_SID=ORCLCDB:

[oracle@centos-8 ~]$ echo $ORACLE_SID
ORCLCDB
[oracle@centos-8 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jun 13 09:00:43 2020
Version 18.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

ORCLPDB1
MOUNTED

DEMO0
MOUNTED

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

[oracle@centos-8 ~]$ export ORACLE_SID=demo0
[oracle@centos-8 ~]$ echo $ORACLE_SID
demo0
[oracle@centos-8 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jun 13 09:01:24 2020
Version 18.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

ORCLCDB and demo0 are 2 seperate instances based on your output..

You have an empty CDB called demo0 with the seed PDB only.

And you have another CDB called ORCLCDB, in which you created a PDB called DEMO0.

Of course DEMO0 PDB is not listed in demo0 CDB, because the DEMO0 PDB was created in the ORCLCDB CDB.


SQL Compare data from two joined tables i used SQL Server, and have two joined table

Select b.team, SUM(a.total) FROM data1 A
INNER JOIN team b ON a.teamID = b.teamID
WHERE month = 1 and year = 2019
GROUP by team

second one is like this

Select b.team, SUM(c.total) FROM data2 C
INNER JOIN team b ON c.teamID = b.teamID
WHERE Date BETWEEN '2019-01-01' AND '2019-01-31'
GROUP by team

and the result will be :

Data 1                   Data 2
TEAM   | Total        TEAM   | Total
Team 1 | 5            Team 1 | 4
Team 2 | 3            Team 2 | 3
Team 3 | 8            Team 3 | 9

i wanted to create query to get result like this :

Team   | Total
Team 1 | 1
Team 3 | -1

WITH
cte1 AS ( SELECT b.team, SUM(a.total) total
          FROM data1 a
          INNER JOIN team b ON a.teamID = b.teamID
          WHERE a.month = 1 and a.year = 2019
          GROUP by b.team ),
cte2 AS ( SELECT b.team, SUM(c.total) total
          FROM data2 c
          INNER JOIN team b ON c.teamID = b.teamID
          WHERE c.Date BETWEEN '2019-01-01' AND '2019-01-31'
          GROUP by b.team ),
SELECT COALESCE(cte1.team, cte2.team) team,
       COALESCE(cte1.total, 0) - COALESCE(cte2.total, 0) total
FROM cte1
FULL OUTER JOIN cte2 ON cte1.team = cte2.team
WHERE COALESCE(cte1.total, 0) != COALESCE(cte2.total, 0)

Of course it can be simplified - but it will decrease its clearness.


Rounding mixed data types I have a column with a mixture of letters and numbers, and I need to round only the cells that contain numbers using SQL. I currently have my data saved as strings only, and when I try to use a round function, my data containing no numbers gets lost. Is there any way around this issue?

One of the issues you face is that you can't mix data types within a single column. So while you can have numbers-as-strings in your varchar column, you can't easily do math-like functions on these strings-that-are-numbers.

However, with some casting and subqueries, you might be able to get the results you're looking for. The following works on SQL Server, though I'm not sure if it would behave the same on Access.

Here's a SQL Fiddle that demonstrates a solution, though it's probably far from ideal:
SQL Fiddle

If you run the inner query separately (

select
    value,
    case isnumeric(value)
        when 1 then round(cast(value as float), 0)
        else null
    end as valueint,
    case isnumeric(value)
        when 0 then value
        else null
    end as valuestring
from myvalues

) you'll see that it's selecting the raw value, a rounded number if applicable (otherwise null) and a string value if applicable (otherwise null), while the outer query is putting it all back together for you.

Hope this helps.


Retrieve parent and child data using child id I have a parent and child table, I am able to retrieve all data using UNION SELECT.

Parent Table:

id | parent_name
 1 | Parent A
 2 | Parent B

Child Table:

id | parent_id| child_name
 1 |        1 | Child A
 2 |        2 | Child B
 3 |     NULL | Child C

Using this query (http://sqlfiddle.com/#!9/f9f1c6/1)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name
  from Child
) parent on parent.parent_id = c.parent_id;

I get this result:

child_id| parent_id|         name|
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |
   NULL |        2 |    Parent B |
      2 |        2 |     Child B |
      3 |     NULL |     Child C |

This is where I'm kinda lost.
I want to retrieve a child that will still include the parent data.

Using WHERE ChildId = '1'; outside of join.

Expected result:

child_id| parent_id|        name |
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |

I was able to figure out what I wanted to achieve.

I just added an OR on right join. (http://sqlfiddle.com/#!9/f9f1c6/12)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name
  from Child
) parent on parent.parent_id = c.parent_id OR parent.child_id = c.id
WHERE c.id = '2';


Strange Availability Problem with MySQL I am trying to troubleshoot an e-commerce site problem that seems to be database related. In my investigation so far, I noticed that the server seems to have an unusually high numbers relating to Aborted_clients: 65206
and Aborted_connects: 394618

This is on a server that was restarted two weeks ago.

I'm not a DBA, but I suspect that this has something to do with the issue. Can anyone fill me in on what these values mean and if it could be causing availability issues?

Thanks

From the MySQL Documentation

Aborted_clients : The number of connections that were aborted because the client died without closing the connection properly.
Aborted_connects : The number of failed attempts to connect to the MySQL server

From the look of those two status variables:

Aborted_clients : This should make you look over how your clients are disconnecting. If you connect using mysqli_connect(), you should be closing using mysqli_close(). If you are not and just let the connections die on their own, that might be one probable cause.
Aborted_connects : You should check to see what max_connections is set to. It might be too low and connections are failing simply because of reaching this max setting.
You should raise the max_connect_errors and run FLUSH HOSTS; periodically.
You should investigate the other status variables related to connection errors. You might need to get your SysAdmin involved.


Multiple Replication Type Publications on Single DB I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as they are not allowed by the vendor DB1)

When considering replication options for a reporting server, I was limited with DB2 as a number of the custom tables which have been added over time do not have Primary Keys, so transactional was out, leaving really only Snapshot as an option. DB 1 is working well with transactional.

Having now run this set-up for a while, with the Snapshot of DB2 (around 1GB) running every 20 mins, I am getting some performance implications specifically around blocking when the snapshot is generating.

Ideally I would like some of the key tables to run transactionally, whilst a single snapshot takes care of the remainder of the DB out-of-hours, but cannot find much guidance on this anywhere.

My real question is, is it viable to run 2 types of replication on the same database as long as the articles do not cross-over?

Many Thanks in advance!

The following article appears to answer this question:

https://technet.microsoft.com/en-us/library/ms172911%28v=sql.105%29.aspx

Yes, you can run multiple publications from the same Db as long as there are no intersecting articles.


Postgres live backup Goal: Maintaining a constant live backup of a postgres cluster. In case of cluster failure, backup/Secondary will contain most up to date view of Primary cluster. This is not a hot standby circumstance. When the Primary postgres machine can be fixed/switched, etc. the new machine's Postgres cluster will be restored using the backup.

Current direction: Using postgres 9.3 streaming replication to a remote postgres server. (Currently unsure whether it will be synchronous or asynchronous, depending on consistency requirements of the system.)

Question: Is this a reasonable solution?

Given this solution, is there any way to tune the Secondary/Slave postgres cluster? For instance, since the backup server won't be responding to queries it doesn't need indices. Can I somehow disable these for the Secondary cluster?

In what ways can I save in disk space and memory on my Secondary/Slave cluster given the knowledge that it's only serving as a 'dumb' backup?

Given this solution, is there any way to tune the Secondary/Slave postgres cluster? For instance, since the backup server won't be responding to queries it doesn't need indices. Can I somehow disable these for the Secondary cluster?

  In what ways can I save in disk space and memory on my Secondary/Slave cluster given the knowledge that it's only serving as a 'dumb' backup?

If you do only logical backups (pg_dump), you'll save lots of space - but you'll have much larger data loss windows.

Currently your only other options are WAL archiving and streaming replication, and you're right that they have some big overheads.

You cannot exclude indexes or unwanted tables from the replica stream. Nor can you select only some databases to replicate. It's an all-or-nothing prospect. The only exception is unlogged tables - their contents don't get replicated, and neither do contents of their indexes. So you can use unlogged tables for transient data on the primary to save some I/O, WAL space, and disk space.

There are some logical streaming systems like Londiste and Slony-I, but they're only asynchronous. So you can have a potentially unbounded data loss window if the replica isn't keeping up with the primary.

There's ongoing work into adding synchronous streaming logical replication to PostgreSQL via the BDR project, but it won't be in 9.4 and might not be in 9.5 either, so it's a long-term thing.

Thankfully, a physical replica server has quite low CPU overheads and its disk I/O overhead is usually only moderate. So long as you throw enough spare disk space it it, it won't unduly burden your other application. There isn't really much you can do to tune it, though spreading out checkpoints longer on master can help reduce writes on the replica.

One option you may wish to consider is to sacrifice recovery time for lower overheads. Use WAL archiving with an archive_timeout on the master and monitor log shipping closely. Set up alerts to warn you urgently if logs stop arriving at the destination storage. Then just do a pg_basebackup and use WAL archiving to keep an asynchronous replica. It'll take a lot longer to start up if you ever need to restore it and it needs a ton of disk space, but it has near-zero CPU and RAM overhead and does very efficient sequential disk writes that the I/O subsystem barely notices. You can also have it write to any storage you feel like, not necessarily the spare server. The key thing with this is that you must test restores carefully - make sure you can deal with the long recovery times (and measure them for your data), ensure your base backup procedures are correct, rotate your base backups regularly, etc. I recommend PgBarman, which automates a lot of this for you. You must understand though that WAL archiving is asynchronous - there's always a data loss window of up to archive_timeout at any time, and it can get a lot bigger if your WAL archiving fails due to (eg) running out of disk space, as the master server will not stop or pause if the replication stops.

So for safety, stick with synchronous streaming replication. If you need to, buy a better server.


Different queries result in big difference in performance I have 2 similar functions, different way result in big diff in performance.

The PostgreSQL version: 9.2.1.

Function 1

create or replace function func_1()  returns text  as
$$
declare
   v_i integer;
   v_md5 varchar;
begin
   for v_i in 1..1000000 loop
       v_md5:='a'|| v_i::character varying;
   end loop;
   return 'ok';
end;
$$
language plpgsql;

Function 2

create or replace function func_select()  returns text  as
$$
declare
   v_i integer;
   v_md5 varchar;
begin
   for v_i in 1..1000000 loop
      select 'a'|| v_i::character varying into  v_md5;
   end loop;
   return 'ok';
end;
$$
language plpgsql;

Function Timings

francs=> \timing
Timing is on.
francs=> select func_1();
 func_1
--------
 ok
(1 row)

Time: 1467.231 ms
francs=> select func_1();
 func_1
--------
 ok
(1 row)

Time: 1383.424 ms
francs=> select func_select();
 func_select
-------------
 ok
(1 row)

Time: 22176.600 ms
francs=> select func_select();
 func_select
-------------
 ok
(1 row)

Time: 23265.755 ms

From the above, function func_1() only takes about 1383 ms, but function func_select() takes about  23265 ms, anybody can explain this?

The use of the pure SQL statement in the second loop makes the database engine compile the whole select 'a'|| v_i::character varying into  v_md5; on each loop iteration before evaluating it whereas the variable assignment is parsed easily into low level instructions once.


Is Vertica's COPY atomic? Seems like a simple thing, but I can't seem to find it in the docs for COPY.

Is Vertica's COPY atomic? If a data load is interrupted or somehow fails, does Vertica take care of the cleanup?

Vertica supports ANSI SQL-92 isolation levels with standard ACID properties. If a COPY is terminated, interrupted, or an error occurs, it will be rolled back.

By default, HP Vertica uses the READ COMMITTED isolation level.
The COPY command automatically commits itself and any current transaction (except when loading temporary tables). HP recommends that you COMMIT or ROLLBACK the current transaction before you use COPY. Please note that DDL statements are autocommitted.

Documentation


Best practices, MySQL table with 150+ columns, many null I have a table storing property data for thousands of properties, all coming from a feed. (updating at least once a day).

I'm certainly not an expert with databases and would appreciate some guidance on the best way to approach building the property table.  The challenge is that each property has a LOT of attributes which might or might not be included.  Each attribute value is potentially unique, so a relational table doesn't seem like it would provide any benefit.

Currently, my plan for the table is simple, creating a wide table with a lot of potentially NULL columns.  eg:

id - int(not null)
date - datetime(not null)
attribute1 - varchar(null)
attribute2 - varchar(null)
attribute3 - int(null)
attribute4 - bool(null)
ect..

Is there a better way to set this up?  Each attribute is uniquely associated with the property, so keeping it all in a single table make sense to me.

Will having that many columns, even if most are NULL, cause me issues when thousands of records exist in that table?  Each day, I need to do select several select queries on this table, returning hundreds to thousands of records each time.

Any advice or direction on what to research is greatly appreciated!

Consider an Entity-Attribute-Value design.  The general concept is that you put all of the data in a very long, narrow table which may take the form of:

CREATE TABLE dbo.PropertyAttributes
(
PropertyID   INT NOT NULL
             REFERENCES dbo.Properties(PropertyID),
AttributeID  INT NOT NULL
             REFERENCES dbo.Attributes(AttributeID),
StringValue  NVARCHAR(1024),
NumericValue DECIMAL(16,4),
DateValue    SMALLDATETIME,
ModifiedDate SMALLDATETIME NOT NULL
             DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (PropertyID, AttributeID)
);

Then you store metadata about the attributes.  At a minimum you'd need something like this:

CREATE TABLE dbo.Attributes
(
AttributeID INT PRIMARY KEY,
PropertyID  INT NOT NULL
            REFERENCES dbo.Properties(PropertyID),
[Name]      NVARCHAR(32) NOT NULL UNIQUE,
DataTypeID  TINYINT NOT NULL -- 1 = string, 2 = numeric, 3 = date
);

Taking this approach offers some benefits because when new attributes show up in the data, you don't have to modify the database structure to house the data and you don't have to construct new queries to get at the new columns you created.

Like other aspects of life, these benefits come with tradeoffs.  Creating a very wide view requires you to Pivot the table which can be an expensive query to run.

Aaron Bertrand, who posts here quite a bit, described his experience with this approach in 2009.  It's still worth a read today.

MDCCL's suggestion to review attributes and potentially decompose the most relevant ones is a good one, move the most commonly used attributes into the base Properties table so you pivot the EAV table less frequently.


Storing arrays of data in a time-series database I'm building an low utilization time-series database to capture yearly data points for a set of items fewer than 100,000.

My question has to do with storing arrays of data in a way that is easily queried later.

Right now the yearly_visits table looks something like:

visitID       MEDIUMINT primary key
userID        SMALLINT id of individual submitting yearly data
weight        SMALLINT weight of individual

The intake form also contains a checkbox with a list of favorite colors (via numeric value) from a lookup table.  Users can select one or more colors.

Should colors be stored in a separate visit_colors table that looks something like:

 visitID MEDIUMINT
 colorID SMALLINT

Or is there a better way of storing arrays of data in a time series?  I haven't written any code yet, so I want to design this in a way that doesn't bite me down the road when I'm asked to query against the color data down the road.

I've read about (but not implemented) Timescale, a time-series database over Postgres. It claims to support all the PostreSQL native datatypes, including arrays. In that product the array type is supported within the query language. I don't imagine Postres disappearing any time soon so a solution based on it would be somewhat future-proof.


Look for software that can convert sql (mysql) to erd I would like to have an ER Diagarm in phpmyadmin, however, its graph is not up to my expectation, the graph has no relationship indicator (1-1) (1-m) etc at all.

So i am wondering whether there is a software that can transfer the sql/ any kind of file exported from phpmyadmin to a diagarm? Thank you for your help.

You can use MySQL Workbench (http://www.mysql.com/products/workbench/) or Navicat for MySQL (http://navicat.com/en/products/navicat_mysql/mysql_overview.html)


Create junction table where one referenced table has a composite key as PK I have a category table which currently has only a title field (PK)

CREATE TABLE `category` (
  `title` varchar(20),
  PRIMARY KEY (`title`)
);

and a product table with a composite key as a primary key

CREATE TABLE `product` (
  `title` varchar(50),
  `vendor_name` varchar(50),
  `price` decimal(10,2) unsigned NOT NULL,
  PRIMARY KEY (`title`, `vendor_name`),
  CONSTRAINT `fk_product_vendor_name` FOREIGN KEY (`vendor_name`) REFERENCES `vendor` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
);

A product can have many categories so I created a junction table. I'm struggling with the composite key from the product table. In the junction table I need to reference the combination of the product title and the vendor name foreign key. I started with this

CREATE TABLE `category_product` (
  ``/* product composite PK */
  `category_title` varchar(20),
  PRIMARY KEY (``/* product composite PK */,`category_title`),
  KEY `fk_category_product_category_title` (`category_title`),
  KEY ``/* product composite PK */
  CONSTRAINT `fk_category_product_category_title` FOREIGN KEY (`category_title`) REFERENCES `category` (`title`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT ``/* product composite PK */ ON DELETE CASCADE ON UPDATE CASCADE
);

How do I handle the composite key from the product table?

Thanks in advance

You have also to define two columns for a double Primary KEY

CREATE TABLE `category_product` (
  `category_title` varchar(20),
    `product_title` varchar(50),
  `product_vendor_name` varchar(50),
  CONSTRAINT `fk_category_product_category_title` FOREIGN KEY (`category_title`) REFERENCES `category` (`title`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_category_product_product_title_vendor_name` FOREIGN KEY (`product_title`,`product_vendor_name`) REFERENCES `product` (`title`,`vendor_name`) ON DELETE CASCADE ON UPDATE CASCADE
);


how to do a count on a grouped by value in Postgres 9.4 I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid'

 id | fulfillment_status | created_at (timestamp witout time zone)
1                           2015-10-17 02:23:14.882131
2      paid                 2015-10-18 02:23:14.882131
3                           2015-10-17 02:23:14.882131

so that the output is:

date         total_orders total_paid_orders
2015-10-17     1             0
2015-10-18     2             1

I get this far:

 select count(*) from orders group by created_at ???

but ....

select created_at::date,
       count(*) as total_orders,
       count(*) filter (where fulfillment_status = 'paid') as total_paid_orders
from orders
group by created_at::date
order by created_at::date;

created_at::date converts the timestamp into a date (without a time) and thus groups by day.

count(*) filter (where fulfillment_status = 'paid') only counts rows where the condition is true.


delete hung soon after vacuum verbose analyse I have a fairly large java webapp system that connects to a postgres8.4 db on rhel.
This app only does inserts and reads.

This has been running without issue for 2.5 years and for the last year or so I implemented a data deletion script which deletes up to 5000 parent records and the corresponding child data in 3 tables.

This deletion script is called from a cron job every 5 minutes.  It has been working flawlessly for over a year.  This script always takes about 1-3 seconds to complete.

Also called from cron is vacuum verbose analyse script which is just called once a day (a few minutes before a deletion).  This also has been working flawlessly for over a year.  This takes about 15 minutes to complete.

Now last weekend (Saturday), the vacuum kicked off at 14:03, the deletion kicked off at 14:07 and did not complete.  The next deletion kicked off at 14:12 and encountered a deadlock.  The webapp at 14:14 hung.  At 14:16 everything resumed as per normal and has been running fine since.

Now

To add more confusion, this server has an almost identical setup (standby sever) however the vacumm cron is due to run at 02:03 in the morning.  When the vacuum kicked off on Sunday at 02:03, the same situation as above was encountered and at 02:14 the java app hung resuming at 02:15.

More

To further confuse me:
every time I go to the site I takes reading of df - this is always around 40% but after this happened, the df now reports 5% less

Any ideas?  Please let me know if I have left out any relevant information.

edit

This is the Postgresql-Sat.log

WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
ERROR:  deadlock detected
DETAIL:  Process 12729 waits for ShareLock on transaction 91311423; blocked by process 12800.
    Process 12800 waits for ShareLock on transaction 91311422; blocked by process 12729.
    Process 12729: delete from child1 where id in
    (
    select id from parent where date_collected < now() - interval '13 months' order by id limit 5000
    );
    Process 12800: delete from child1 where id in
    (
    select id from parent where date_collected < now() - interval '13 months' order by id limit 5000
    );

I'm not able to test this since I don't use postgresql 8.4 but i think you have to:

check which transaction(check Sql statement) is locked and which is locking.

to check it

SELECT
    kl.pid as blocking_pid,
    ka.usename as blocking_user,
    ka.current_query as blocking_query,
    bl.pid as blocked_pid,
    a.usename as blocked_user,
    a.current_query as blocked_query,
    to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a
        ON bl.pid = a.procpid
    JOIN pg_catalog.pg_locks kl
        ON bl.locktype = kl.locktype
        and bl.database is not distinct from kl.database
        and bl.relation is not distinct from kl.relation
        and bl.page is not distinct from kl.page
        and bl.tuple is not distinct from kl.tuple
        and bl.virtualxid is not distinct from kl.virtualxid
        and bl.transactionid is not distinct from kl.transactionid
        and bl.classid is not distinct from kl.classid
        and bl.objid is not distinct from kl.objid
        and bl.objsubid is not distinct from kl.objsubid
        and bl.pid  kl.pid
    JOIN pg_catalog.pg_stat_activity ka
        ON kl.pid = ka.procpid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

Note:this is for postgresql v9.1 i am not sure this work for 8.4 or not.

You mentioned about dead lock so it mean your transaction are waiting each other until occur deadlock(no transaction completed(commit or rollback) so make sure the locking transaction was completed(process 12729) and then transaction (Process 12800) will not wait anymore.
Vacuum is occur table lock level too. when u run vacuum command it will locked the target tables to reclaims storage occupied by dead tuples so that why when you check the df - your free disk space is higher than before.
about Privileges

WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it

you need to use supper user(postgres) to make vacuum verbose analyse for above tables (pg_*) or you just specified the target table only, it no need to use supper user privileges.

ex:

vacuum verbose analyse [table_name];


Passing a list to a SQL query in SSIS I need to be able to pass a list of undefined size that will contain numbers like so ('1900'),('1920'),... to and OLE DB source in SSIS.

as of right now I am trying a lot of different things that aren't working.

declare @fieldLogID nvarchar(256) = ?
declare @accountTypes int = ?

select
      fl.[Version]
      ,fl.ObjectID
      ,[FieldLogID]
      ,Cast(fl.WorkStartDateTime as date) as WorkStartDate
      ,Cast(fl.WorkStartDateTime as time) as WorkStartTime...

...
where fleja.FieldLog_FieldLogID IN (@fieldLogID)

The @fieldLogID will contain all the numbers that are passed in. The numbers format does not matter so much, they can be strings, ints, etc. Just as long as we are passing a list.

Any tips on how this is done?

Have you tried using dynamic sql?  Something like this:

declare @fieldLogID nvarchar(256) = ?
declare @accountTypes int = ?
declare @sql nvarchar(1000) = ''

SET @sql = 'select fl.[Version]
           ,fl.ObjectID
           ,[FieldLogID]
           ,Cast(fl.WorkStartDateTime as date) as WorkStartDate
           ,Cast(fl.WorkStartDateTime as time) as WorkStartTime...
           ...
           where fleja.FieldLog_FieldLogID IN (' + @fieldLogID + ')'
EXEC (@sql)

The syntax may be a little off and the @sql nvarchar variable type would probably need to be expanded more.   The format for @fieldLogID would need to be something like this though:

@fieldLogID = n'''1800'',''1900'',''2000'''

Instead of executing the @sql variable, you can always print it to the screen to verify your syntax.  Using this approach I would be a little worried about the @fieldLogID variable and I would like to validate that it is correct and nothing malicious is going in there.


Why are U locks required when using Read Committed Snapshot Isolation I consider myself a beginner at Sql Server locking.

My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From http://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx we can read the following about U locks:

  Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Knowing this, why does Sql Server need to issue U locks (when using RCSI)?
It seems to me that Sql Server could simply read the rows, and request a X lock directly if an update must be performed.

The reason I've been thinking about this is because of deadlocks I'm experiencing with 2 sessions updating the same table. Something like this (simplified for clarity):

Session 1:

BEGIN TRAN
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value1
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value2
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value3

Session 2:

BEGIN TRAN
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value4
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value5
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value6

From what I gather in the Profiler lock report, both sessions have an X lock on some rows of the table, and are requesting U lock on each other's X-locked row => deadlock.
However, I know for a fact that each concurrent session will always modify different rows because the value X will never be the same in 2 different concurrent sessions.
That is, if those sessions were able to get their requested U lock, sql server would realize it is not necessary to upgrade an X lock anyway.

In conclusion I feel like, even though U locks are supposed to reduce deadlocks, they actually are creating an unnecessary deadlock here.

I understand they can be valuable for other isolation levels, but for RCSI, I don't get it...

Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and
  request a X lock directly if an update must be performed.

Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly out-of date version. (In the absence of update conflict detection, performing updates based on out-of-date data could result in a "lost update".)

Taking update locks is normal behaviour for a non-row-versioning query that updates data. It is a protection against a common cause of conversion deadlock, but it does not guarantee deadlock avoidance in all cases, especially where a different access path (index) is used to qualify rows to change.

You can find more details about the exact behaviour of RCSI when modifying data in my SQLperformance.com article, "Data Modifications Under Read Committed Snapshot Isolation". There is further background on RCSI in general in the article, "Read Committed Snapshot Isolation".

If the updates really are disjoint, you might consider performing the change using Snapshot Isolation rather than RCSI (which admittedly has complex behaviour in this area).


How to Select Average from many Column with extrawhere from all Column? This Query is Simple, but I still don't know to handle it.

  I must Select Average From Many Column But Only Column with Integer
  Value.

like this

SELECT AVG(SET_O_1A) AS SET_O_1A,
       AVG(REAL_O_1A) AS REAL_O_1A,
       AVG(SET_O_1B) AS SET_O_1B,

       AVG(REAL_O_1B) AS REAL_O_1B,
       AVG(SET_PRE_1A) AS SET_PRE_1A,

       AVG(REAL_PRE_1A) AS REAL_PRE_1A,
       AVG(SET_PRE_1B) AS SET_PRE_1B,

       AVG(REAL_PRE_1B) AS REAL_PRE_1B,
       AVG(SET_O_2A) AS SET_O_2A,

       AVG(REAL_O_2A) AS REAL_O_2A,
       AVG(SET_O_2B) AS SET_O_2B,

       AVG(REAL_O_2B) AS REAL_O_2B

        FROM table
        WHERE ALL column > 0;

How to Fix Problem like this?

I have try like this but still not working

SELECT * FROM (SELECT AVG(SET_O_1A) AS SET_O_1A,
           AVG(REAL_O_1A) AS REAL_O_1A,
           AVG(SET_O_1B) AS SET_O_1B,

           AVG(REAL_O_1B) AS REAL_O_1B,
           AVG(SET_PRE_1A) AS SET_PRE_1A,

           AVG(REAL_PRE_1A) AS REAL_PRE_1A,
           AVG(SET_PRE_1B) AS SET_PRE_1B,

           AVG(REAL_PRE_1B) AS REAL_PRE_1B,
           AVG(SET_O_2A) AS SET_O_2A,

           AVG(REAL_O_2A) AS REAL_O_2A,
           AVG(SET_O_2B) AS SET_O_2B,

           AVG(REAL_O_2B) AS REAL_O_2B

            FROM table) as ORI
            WHERE ORI > 0;

Can Anyone help this?
Thanks in Advance

Tabel like this

ID   |  ph  |  ph2
========================
 1   | 10    |  20
 2   | NA    |  20
 3   | 11    |  10

and I Hope the result like this

ID   |  ph  |  ph2
========================
 1   | 10.5 |  16.67

so The query only average column with number only.


Streaming replication in postgres question We have recently setup streaming replication in our Postgres server (t01, t02). t01 is master and t02 is the slave. I want to understand the below two issues:

Recently our /var directory of t01 server got full and app team was not able to access the application. My understanding was if t01 /var was full, the connection should be made to t02 and application should start using that as t02 /var was not full.

If we shutdown t01 server, will my application automatically use the t02 databases, Streaming replication will provide HA in this case or not?

This is not an inherent feature of PostgreSQL.  Whether your application will connect to the replica (or even knows that a replica exists in the first place) is up to your application, framework, library, connection pooler, or networking.  And their respective configurations.

If it does connect to the replica, that connection will be read only, unless you do something to promote the replica.  Autopromotion is not an inherent feature of PostgreSQL, you would have had to install and configure something to make it happen.


Sqlite Affinity for hashvalues with certain size I must store many SHA256-values in a sqlite-database and now i wonder which sqlite-affinity would be the correct one. SHA256-values have an output-length of 256bit=32byte. I am looking for an sqlite-affinity which can handle this but which also does not "waste" diskspace since in my case i have very many datasets. I have read on this site many useful things about datatypes in sqlite but i still do not know which type would be correct for SHA256-values. i would prefer something like "bytearray(32)" so i thought varchar(32) could be the one. but then i remembered that i can probably not converting all of this bytes to printable characters to get them stored as character-array. So it seems that I must use Blob.

Unfortunately do not really find any useful information about limiting the Blob-size in sqlite and how sqlite handles blobs internally. I would like to use the information that my hash-values have always the same length to optimize the disk-usage of the database: I want sqlite to know that for this Blob-column it always have to reserve only 32 bytes, never more. So I would like to use something like Blob(32) as column-type. Is this possible? And if yes: Does it make sense or is there any fallacy?

You can use BLOB(32) as data type.

SQLite does not limit the size of text or blob values, and never allocates more storage than actually needed. The number is pretty much ignored by SQLite, and its primary purpose is to serve as documentation for you.

If you want to enforce the length, add a constraint: CHECK (length(my_column) = 32).


Migrate logins tagged to specific databases users We are doing migration from SQL2008R2 to SQL 2014 for some of the databases hosted on 2008R2 to 2014 and remaining DB's to other servers.

I am aware of migrating the login using dbatoo or using sp_helprevlogin..

But using that it would move all the logins from 2008R2 instance to 2014.

Since we are only moving some databases we only need to move their respective logins or i should say only those logins that are present under users in the database geting migrated to 2014 instance.

How can we achieve this intelligently via script, because doing manually is taking lot of time as we have hundred of logins and about 40 databases

I have a procedure called sp_SrvPermissions that has a @DBName parameter specifically for that.  You can do something like this:

EXEC sp_SrvPermissions @DBName = '<DBName>', @Output = 'CreateOnly'

This will give you not just the script to create the LOGIN associated with just that database but any server level roles they are a member of and any server specific permissions they might have as well.


GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin I'm trying to GRANT permission for an SQL login to access the functions fn_db_log and fn_dump_dblog but GRANTing SELECT on the function still fails with error:

Msg 9010, Level 14, State 1, Line 3
User does not have permission to query the virtual table, DBLog.
Only members of the sysadmin fixed server role and the db_owner fixed database role have this permission

Below are the steps I followed:

create database duumy;
GO
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [duumy]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [duumy]
GO
EXEC sp_addrolemember N'db_owner', N'test'
GO
USE [master]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [msdb]
GO
CREATE USER [test] FOR LOGIN [test]
GO

use master;
GRANT SELECT ON FN_DBLOG TO test;
GRANT SELECT ON FN_DUMP_DBLOG TO test;
GRANT VIEW SERVER STATE TO test;

I'm not sure what I'm missing here. Please assist.

Not sure how this would be affected (if at all) by Azure SQL Database, but in general you should use Module Signing to grant higher level permissions in the most targeted, secure manner.

You would create a stored procedure or multi-statement TVF containing the SELECT ... FROM sys.fn_... command. Then, you would sign the stored procedure or TVF with an Asymmetric Key or Certificate.

If you just need to make use of the db_owner role, then you can probably get away with an Asymmetric Key, then create a User from that Asymmetric Key, and finally add that User to the db_owner role.

A Certificate works in both cases. But, if you need to make use of the sysadmin server role, then you need a Certificate. So you would create a Certificate in the DB containing the stored procedure or TVF, sign that module, then create that same Certificate in master using only the public key, then create a Login from that Certificate, and finally add that Login to the sysadmin server role.

For details, please see the following posts:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level


Maximum number subscribers node in PostgreSQL logical replication? Does anyone know what is the maximum of subscriber nodes with PostgreSQL 11 logical replication ?

The limit built into PostgreSQL is 262143 (218 - 1).

So in theory you could bump up required parameters and have 200k logical rep subscribers.

In reality, you will start hitting performance issues much below this.

For the parameters - see documentation and also see this SQL query:

SELECT category, name, setting, min_val, boot_val, max_val
FROM pg_settings
WHERE category ~* 'replica'
AND name ~* 'max.*(workers|slots|senders)'

I get this:

           category            |               name                | setting | min_val | boot_val | max_val
-------------------------------+-----------------------------------+---------+---------+----------+---------
 Replication / Subscribers     | max_logical_replication_workers   | 4       | 0       | 4        | 262143
 Replication / Sending Servers | max_replication_slots             | 10      | 0       | 10       | 262143
 Replication / Subscribers     | max_sync_workers_per_subscription | 2       | 0       | 2        | 262143
 Replication / Sending Servers | max_wal_senders                   | 10      | 0       | 10       | 262143
(4 rows)


What reason is there to create a physical table for a report output? I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view or a stored procedure to collect the data. Occasionally I've seen reports use stored proceedures which then use physical tables rather than temporary tables to store and manipulate data sets. In these cases the report output often exists as a table like rpt_ap_vendors or similar, and it may or may not be devoid of data when not in use.

These are always cases where the report is generated on-demand, so this is not a case where a report could be generated once and served multiple times, and there are not multiple reports/stored procedures accessing this data at the same time.

What reason would there be for using physical tables for reports like this? Is there a logical, technical or performance related reason to do so? In generating reports I personally have always used views and stored procedures with temporary tables or better yet derived tables to avoid extra disc reads involving clearing out/deleting a temporary table.

(+) Reasons to create a physical table to store report data:

The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding window of old reports for the inevitable requests along the lines of: "Can you generate last year's report again? I can't find the CSV I extracted at the time."

This is probably the main reason it's set up that way at your site. Crystal Reports may not be smart enough to cache the report data as users paginate through it or change the report's settings. So in the worst case CR is regenerating your report with each of those actions--a costly and time-consuming operation. With the physical table it just re-queries the table as many times as it needs.
Setting permissions on the report is easy. You want to see this report?  Well, all you need is permission to read the results, NOT generate them. So here, have some read rights on this table in a locked down schema and filegroup/tablespace.

By manually caching the report, you control and isolate the process of generating it. You give your report readers more freedom to act and yourself less to worry about as a DBA.

(-) What you lose with a physical report table:

Flexibility. Wanna change the report?  Argh, need some DDL changes.
Storage space. You're persisting the data on disk, so duh.


SQL Server 2016: How to restrict memory and CPU for specific database SQL Server 2016 Standard Ed on Windows 2016

We have an 8 core, 128GB machine (a VM, actually). We max this out for our prod db. (122GB for the instance, 8 cores for the instance)

We are setting up a 2nd db, to be on its own spindle set (slower disks), and do not want it to impact the prod db. This will be on the same VM (as we understand it, we can use the existing SQL license this way).

Must this 2nd db be served by its own SQL instance in order to:

Restrict RAM consumed (to very little, just 4 or 8 GB)
Restrict to 2 CPU cores

Or can we restrict this DB within the main instance?

(Note, Express Edition is not an option, our db will be over 10 GB)

You can't. There's no such thing as per-database restrictions because a query can cross multiple databases (think joins and fully qualified objects.)

SQL Server's Resource Governor in theory exists to support this, but it only works on things like CPU count, query workspace memory, and disk IO. It doesn't work on the buffer pool, so it still won't meet your needs.


Slow Full Text When using multiple full text searches Thank you in advance for your help.   I have been going bonkers.

We recently made a change to our code by adding an additional contains to the below query.  This has had a real negative impact on performance.  When we remove contains or the other, the query returns right away.

select *
from dbo.company p with (nolock)
  join dbo.alt_company o  with (nolock) on p.org_id = o.org_id
where CONTAINS (p.description, '"phrase1*" OR "phrase2"')
  or  CONTAINS (o.description, '"phrase1*" OR "phrase2"')

Why would having the second contains create such havoc on the performance.   The problem is then made worse by the number of keywords.   I have re-written the query in such a way there is only one contains and it works pretty good.  I just want to make sure I understand what's happening.

Thanks,
Pete

One common work-around for slow OR performance is to separate the parts and UNION ALL them back together:

select *
from dbo.company p with (nolock)
  join dbo.alt_company o  with (nolock) on p.org_id = o.org_id
where CONTAINS (p.description, '"phrase1*" OR "phrase2"')

UNION ALL -- note the "ALL", this will prevent a "DISTINCT"

select *
from dbo.company p with (nolock)
  join dbo.alt_company o  with (nolock) on p.org_id = o.org_id
where CONTAINS (o.description, '"phrase1*" OR "phrase2"')

You may be able to optimise further by referencing dbo.company only once in each part; I'll leave that to you to decide according to your needs and business rules.

Oh, and stop using select * and WITH (NOLOCK).


Trigger to update a column in a new record to CURRENT_TIMESTAMP as it is inserted Ok, so I'm trying to update a column to a CURRENT_TIMESTAMP in a specific record whenever the record is originally inserted. This is what I've got but otherwise I am lost.

CREATE TRIGGER dbo.TrgUpnInsert
  ON [CNLH Security Authentication].dbo.DateTimestamp
    AFTER INSERT,
    INSERT INTO [CNLH Security Authentication].dbo.EntranceInformation (DateTimestamp) Values (CURRENT_TIMESTAMP);

Your trigger needs to leverage the 'virtual inserted' table as in the following example:

CREATE TRIGGER ModDate
    ON TableX
    AFTER INSERT
AS
BEGIN
    UPDATE X
    SET ModifiedDate = CURRENT_TIMESTAMP
    FROM TableX X
    JOIN inserted i ON X.key = i.key -- change to whatever key identifies
                                     -- the tuples
END

AS an alternative to a trigger, why not simply assign a default value to that column.  Example:

ALTER TABLE myTable
  ADD CONSTRAINT CONSTRAINT_NAME
    DEFAULT CURRENT_TIMESTAMP FOR myColumn

example:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

CREATE TABLE #temp (
    col1 VARCHAR(10)
    ,col2 DATETIME
    )

ALTER TABLE #temp ADD CONSTRAINT ConstraintForTemp DEFAULT CURRENT_TIMESTAMP
FOR col2

INSERT INTO #temp (col1)
VALUES ('abc')

SELECT *
FROM #temp


SQL Server add a linked server to postgres database I am trying to connect a PostgreSQL to SQL Server server as a linked server.

On the SQL Server, I have installed the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a specific database on the PostgreSQL. This DSN work correctly.

Using SSMS I created a linked server that created successfully.
When I test the connection it says successful and when I expand I can see all the tables from postgres.

However when I try selecting from one of those tables it goves me the error:
The OLE DB provider "MSDASQL" for linked server "ServerName" reported an error. The provider did not give any information about the error.

I googled around but did not find anything that was helpful for me.
Can someone please help me with this.

Thank you

PostgreSQL:

You  must provide a database name in the Data Source DSN.
Run Management Studio as Administrator
You must omit the DBName from the query:

SELECT * FROM OPENQUERY([LinkedServer], 'select * from schema."tablename"')


Order by bug on SQL Server 2005 vs SQL Server 2012 I have a query generated by entity framework that returns wrong results on a SQL Server 2005 database (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) but good on SQL Server 2012 (Microsoft SQL Server 2012 - 11.0.2100.60).

Here is the query :

SELECT
[Project2].[tableAid] AS [id],
[Project2].[rank1] AS [rank]
FROM ( SELECT
    [Extent1].[id] AS [id],
    [Extent2].[rank] AS [rank],
    [Extent4].[rank] AS [rank1]
    FROM    [dbo].[tableA] AS [Extent1]
    LEFT OUTER JOIN [dbo].[tableB] AS [Extent2] ON ([Extent1].[tableAid] = [Extent2].[tableAid]) AND (896 = [Extent2].[tableBid])
    INNER JOIN [dbo].[tableC] AS [Extent4] ON [Extent1].[tableCid] = [Extent4].[tableCid]
)  AS [Project2]
ORDER BY [Project2].[rank] DESC

I've simplified it but the model is :

TableA
int tableAid
int tableCid

tableB
int tableBid
int tableAid
int rank

tableC
int tableCid
int rank

There's a many to many relationship between A and B and a 1 to 1 between A and C.

On SQL Server 2005, this query is sorted by the rank column of the results, not by Project2.rank. If I replace the [Project2].[rank1] AS [rank] in the SELECT by [Project2].[rank1] AS [whatever], results are correctly sorted. But since these aliases are generated by EF, I can't easily change them.
On SQL Server 2012, the query works correctly as is.

Does anyone know this bug? Is there a patch or some settings for SQL Server 2005 to avoid this problem?

If you are running on compatibility level of 80 (SQL-Server 2000), then this is the expected behaviour. It was corrected in version 2005.

You can check the Compatibility levels page at MSDN where the differences are listed. In the section "Differences Between Lower Compatibility Levels and Level 90", one of the many items is:

  When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and
  column prefixes are sometimes ignored. This can cause the result set
  to return in an unexpected order.

  For example, an ORDER BY clause with a single two-part column
  (.) that is used as a reference to a column in a
  SELECT list is accepted, but the table alias is ignored. Consider the
  following query.

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

  When executed, the column prefix is ignored in the ORDER BY. The sort
  operation does not occur on the specified source column (x.c1) as
  expected; instead it occurs on the derived c1 column that is defined
  in the query. The execution plan for this query shows that the values
  for the derived column are computed first and then the computed values
  are sorted.

which is exactly what you describe.

The easiest workaround is to not use the same alias for one column in the subquery and for another in the main query.


Properly test queries that are slow on first run How can you test a query that's only slow on first run?¹ Are CHECKPOINT and DBCC DROPCLEANBUFFERS the right tool? If so, is running this as sysadmin all that's needed or there's something else involved?

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

I'm asking before I can't make the query slow any more, unless I restart the SQL Server instance (which is of course painfully slow).

(1) Nothing subtle: 35 s to 51 s on first run, around 0.02 s afterwards

Restarting Sql Server blows away the plan cache so the first time the query is executed, a new execution plan has to be built.  Check out Eight Different Ways to Clear the SQL Server Plan Cache.

Alternatively, you could try altering the text of your query by adding (or removing) some extra spaces or blank lines which should force a new plan to be built.

However, I'd be quite surprised if it took 35 to 51 seconds to build a new plan.

You can verify whether building a new execution plan is part of your extended execution time by issuing set statistics time on in a query session before you execute your query against an empty plan cache (or where you've made a slight change to the query text such that a new execution plan is required).  In the SSMS messages window, look for something like this:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

Additionally, have a look at Why is My Query Faster the Second Time it Runs?


Tablespace 'XXX_SML_INDEX' does not exist using impdb I am new in oracle. I am trying to import an Oracle database from one machine to another by using a dump file. I'm using the below command for the import

  impdp MOBILETEST/MOBILETEST directory=MOBILETEST
  dumpfile=MOBILETEST.dmp logfile=MOBILETEST.log full=y;

I get the error: "tablespace 'XXX_SML_INDEX' does not exist"

  ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
  ORA-00959: tablespace 'XXX_SML_INDEX' does not exist Failing sql is:
  DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR :=
  'ALTER USER "MOBILETEST" QUOTA UNLIMITED ON "XXX_SML_INDEX"';  EXECUTE
  IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041
  THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
  WHERE TABLESPACE_NAME = ''XXX_SML_INDEX'' AND CONTENTS =
  ''TEMPORARY''';      EXE ORA-39083: Object type TABLESPACE_QUOTA
  failed to create with error:

I am trying to use remap_tablespace but dont know where I can get
second TABLESPACE_QUOTA name, like remap_tablespace=XXX_SML_INDEX: ?

Find the default tablespace at database level (which is not necessarily the default tablespace of your user):

SQL>  select property_value from database_properties
      where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
--------------
USERS

Find the default tablespace for your user while logged in another user with adequate privileges for querying DBA_USERS:

SQL> select default_tablespace from dba_users
     where username = 'BP';

DEFAULT_TABLESPACE
------------------------------
USERS

Find the default tablespace for your user while logged in as that user:

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
USERS

Using that information, remap the tablespace as: remap_tablespace=XXX_SML_INDEX:USERS.


Slow insert in SQL Server 2008 Our products' database contains an audit trail table:

CREATE TABLE gn_AuditTable(
  gn_ObjectId int NULL,
  gn_Action smallint NULL,
  gn_Time datetime NULL,
  gn_UserId int NULL,
  gn_Login varbinary(16) NULL,
  gn_ExtraObjectId int NULL,
  gn_ExtraInfo int NULL
) ON [PRIMARY]

We add rows to this table with a simple insert statement - no stored procedures:

insert into gn_AuditTable (
  gn_ObjectId,gn_Time,gn_UserId,gn_Login,gn_Action,gn_ExtraObjectId,gn_ExtraInfo
) values (
  ?,?,?,?,?,?,?
)

passing the values of the columns as parameters to the query; there are also no triggers.

The problem is that sometimes this insert is super-slow - taking in some cases more than 30 seconds and timing-out.

We have multiple installations of the same product, and we are experienceing the problem in some - but not all - of them.

The audit table can be quite big - up to some million of rows. The very slow insert happens with databases that have big tables (quite naturally) - but we have other databases with simliar number of rows that run fine, and even the databases experiencing the problem have it intermittently, in most cases the insert is reasonably fast (sub-second).

The table has 6 non-clustered, non-unique indexes:

gn_Login;
gn_ObjectId;
gn_Action and gn_ExtraInfo;
gn_ExtraObjectId;
gn_Time;
gn_UserId.

Having all these indexes could cause the problem, but again, we have the same indexes everywhere and the problem only somewhere / sometimes.

We had the insert timing-out at least once on a test database with only one user, so the problem does not appear to be related to the overall load but to the insert and the table themselves.

We used to have a clustered index on gn_ObjectId, but we got rid of it as an attempt to solve this problem. gn_ObjectId is not sequential and so we thought that it is a poor choice for a clustered index, forcing SQL Server to re-order rows on inserts. Probably gn_Time - that is always increasing - would be a better choice.

There is no primary key because there is no column or combination of columns that  is 'naturally' unique - we would have to add an id column, and it seems just overhead.

Does anyone have any suggestion at what we could look at to diagnose the problem? What is an insert doing that can take so much time - intermittently?

Why no clustered index? Why no primary key?

This is most likely your problem: you don't have any order to the table (in the sense of, say, an IDENTITY column) this you are inserting into a heap

See

https://stackoverflow.com/q/5094400/27535 (SO)
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/14/are-inserts-quicker-to-heap-or-clustered-tables.aspx (Tibor Karaszi)

Edit, after update.

Having a non-unique clustered index requires a uniquifier (DBA.se links) per duplicate gn_ObjectId entry. This is not a good clustering key, which is best when it is

numeric
monotonically increasing
narrow

I also assume you haven't tried a IDENTITY column then...? Try it, report back..

Edit 2, as @JNK says, an ID column is not an overhead in this situation because of how your writes will be managed on disk


SQL Server - what isolation level for non-blocking select statements? I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select statements from this table.

Under default isolation settings (READ COMMITTED I think?), T1 blocks any select statements from running until the transaction commits or is rolled back.

What I'd like to see is for the select statements to function on consistent data even while the transaction is underway. I believe SNAPSHOT isolation can help, but am not sure if I'm going in the right direction. Would this be the best isolation level for this application?

Secondly, I don't have any control over the process that is calling the select statements, but I do have control over the .NET application that calls T1. Would any isolation level changes be required on both the select statements and T1, or would it be sufficient to mark  just T1 as having a different isolation level?

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.


Change existing column in PG to auto-incremental primary key I have a database in Postgresql, which was migrated from SQL Server (only data).
On SQL Server, a table from this database has these columns:

measure_id
datum
measure

where measure_id is auto-incremental primary key, datum is datetime and measure is float.
After migration in Postrgresql, measure_id is column of type bigint.

How can I change this column (measure_id) to bigserial and assign it as primary key, now that my table is full of data?

Create a sequence and use it as the default value for the column:

create sequence measures_measure_id_seq
   owned by measures.measure_id;

alter table measures
   alter column measure_id set default nextval('measures_measure_id_seq');

commit;

That essentially what serial does.

See the manual for details:
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL


Transfering a table with millions of rows in a fast efficient way without blocking Are there any solutions more efficient than simple INSERT INTO or exporting and importing files to move millions of rows from a table with 3 columns to a similar table in another database, without blocking the destination database and not losing any of the records in transactions that can be rolled back in case of failure?

Are there any solutions more efficient than simple INSERT INTO or
  exporting and importing files to move millions of rows from a table
  with 3 columns to a similar table in another database,

Your best bet for this is going to be SSIS.

  without blocking the destination database

Inserting into one table won't block the database, but it will cause blocking on the table to one degree or another. The length and severity of the blocking will largely be determined by the number of indexes and how well they suit your queries.

If you want to reduce blocking, insert data in smaller chunks. I usually go for around 1000 rows at a time to avoid lock escalation.

Another way to reduce blocking is to use an optimistic isolation level on queries that read data from the able.

  and not losing any of the records in transactions that can be rolled
  back in case of failure?

This is another reason why smaller batches is a good idea. It will make it easier for you to figure out the offending row, retry the transaction, etc.

There are also some neat trick shots in this post, by Michael Swart: Modifying Tables Online – Part 1: Migration Strategy


MySQL InnoDB constraint does not work I stumble upon strange behavior with innoDB constraint, and cannot find cause of it.
I have tables with data.
Below listed their structures:

CREATE TABLE `contents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `type` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nameUNIQUE` (`name`),
  KEY `name` (`name`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dataTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `value_UNIQUE` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `content_data` (
  `content_id` int(10) unsigned NOT NULL,
  `field_id` int(10) unsigned NOT NULL,
  `data_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`content_id`,`field_id`,`data_id`),
  KEY `fk_content_data_2_idx` (`field_id`),
  KEY `fk_content_data_3_idx` (`data_id`),
  CONSTRAINT `fk_content_data_1` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_content_data_2` FOREIGN KEY (`field_id`) REFERENCES `fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_content_data_3` FOREIGN KEY (`data_id`) REFERENCES `dataTable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now, let's execute two queries:
First query:

mysql> SELECT * FROM `dataTable` where id=21318;
Empty set (0.00 sec)

We got empty set and that is ok since dataTable in fact has no any row with id=21318
Second query:

mysql> SELECT * FROM `content_data` where data_id=21318;
+------------+----------+---------+
| content_id | field_id | data_id |
+------------+----------+---------+
|        552 |       35 |   21318 |
+------------+----------+---------+
1 row in set (0.00 sec)

Here, last query give us result with data_id=21318. WTF!

How to explain this situation?
Why constraint does not work?
Any idea, thanks.

It could be related to the value of foreign_key_checks variable. Try the following statement, and check the value:

mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+


AlwaysOn Architecture - SQL Server 2012 for creating a dev environment? I was thinking if the AlwaysOn architecture in SQL Server 2012 can be leveraged somehow to create a replica for DEV and QA environment properly in addition to setting up HADR using the same architecture?

As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities.

If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be useful - but you can achieve pretty much the same thing with log shipping.

You can build out your dev environment from a standby server - whether it uses AlwaysOn, Mirroring or Log Shipping - by using a Storage Appliance that supports writeable snapshots, or a server that can present writeable snapshots via iSCSI (e.g. a ZFS server). However, you'll probably need extra servers for this, some latitude to mess around with your storage configuration, and possibly someone's shoulder to cry on while you're trying to set it up.

The little "Cloning" video on NetApp's SnapManager website provides a useful overview of how this works - unfortunately I can't seem to find a nice description that's not vendor-specific.


Attaching a database in SSMS gives error I am accessing SQL Server using SSMS, and a user with adequate permissions.

When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), they get an error message:

  Cannot show requested dialog.
  Parameter name: nColIndex

  Actual value was -1. (Microsoft.SqlServer.GridControl)

    at Microsoft.SqlServer.Management.UI.Grid.GridControl.SetColumnWidthInternal(Int32 nColIndex, GridColumnWidthType widthType, Int32 nWidth)
  ...
    at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SetGridColumnWidth(SqlManagerUIDlgGrid grid, Double[] columnWidthRatio, Int32 columnIndex)
  ...

If the same user uses a T-SQL command such as below, then it works fine:

CREATE DATABASE [hgnx] ON
( FILENAME = N'E:\db\hgnx.mdf' ),
( FILENAME = N'E:\db\hgnx_log.ldf' )
FOR ATTACH

This doesn't look like a permissions issue at all, but rather a bug in the UI.

If you are using Management Studio Express, then you should consider two options:

Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well.
Many of the bugs in the 2008/2008 R2 version of the tools have yet to be fixed, and likely never will be. Never mind that the Express version has limited functionality. So stop using the crippled Express version from 2008 R2; the fully functional version of Management Studio is now absolutely free as of SQL Server 2012 Service Pack 1. Yes, you can manage all of your 2005, 2008, 2008 R2 and 2012 instances with the newest version of the tool.

In fact, as it turns out (thanks @Justicator), this bug has only been fixed in the 2012 version of the tools, so looks like option (2) is your best bet (or, just not using the UI for this type of task).


How can I update the roles for all logins using T-SQL? I need to update the roles for all database logins, but without using SQL Server Management Studio. I can update a single user with the following example SQL.

EXECUTE sys.sp_addsrvrolemember
    @loginame = N'User1',
    @rolename = N'dbcreator';

But how I can do this for all users? Is there a way to select the names of all the users and then loop through the list and update them all?

You can use dynamic sql.

declare @sqltext nvarchar(max) = N''

-- here you are adding all the logins to dbcreator fixed server role ...
-- this is just an example to get you started ...adjust it as per your needs !!
select @sqltext += 'ALTER SERVER ROLE dbcreator ADD MEMBER '+QUOTENAME(name)+';'+char(10)+'go'+char(10) from sys.server_principals
where type_desc ='SQL_LOGIN' -- only for sql logins
and name <> 'sa' -- not sa
and name not like '##%' -- not special logins like certs etc
and is_disabled = 0 -- not disabled

print @sqltext
--once you are fine with the output then uncomment below
-- exec sp_executesql @sqltext -- this will actually run the sql !!


SQL Server 2016: Adding temporal tables to existing application I have a big PHP application, developed by an external company, which is being completed right now; I cannot ask for changes to it.
It uses a database Microsoft SQL Server 2016, which is accessed by some users via SSMS, including me, and by some scheduled tasks at specific times of day.
Sometimes I find unexpected content in some tables, and it is frustrating because I don't know when it got there.
I just discovered about temporal tables: knowing the exact date and time when a record changes would be extremely useful.

Is it possible to add temporal tables to 2-3 key tables in the application, without changing the PHP code? Are there risks of compatibility or performances?

Is it possible to add temporal tables to 2-3 key tables in the
  application, without changing the PHP code?

You should be able to achieve that goal (assuming the application is written in a way that adding additional columns to the table doesn't cause a problem).  Of course, test, test, test.

Using the example code provided in Greg Larsen's post
Altering an Existing Table to Support Temporal Data, let's work through an example.

Here is our current table.

DROP TABLE IF EXISTS dbo.Product
DROP TABLE IF EXISTS dbo.ProductHistory

CREATE TABLE dbo.Product (
    ID INT Identity
    ,ProductName VARCHAR(50)
    ,ProductPrice DECIMAL(20, 2)
    );

INSERT INTO dbo.Product (ProductName, ProductPrice)
VALUES ('Widget',33.49),('Doo-Hickey',21.76),('Thing-A-Ma-Jig',20.16);

SELECT * FROM dbo.Product

| ID | ProductName    | ProductPrice |
|----|----------------|--------------|
| 1  | Widget         | 33.49        |
| 2  | Doo-Hickey     | 21.76        |
| 3  | Thing-A-Ma-Jig | 20.16        |

We now want to track versions.

  In order to start collecting historical information for my dbo.Product
  table I will need to alter the table so it will support temporal data.
  A SQL Server 2016 temporal table requires a table to have a primary
  key and a couple for date/time columns.  The two date/time columns
  will be used to determine the period of time for when the record is
  valid.   Therefore the first thing I need to do is alter my Product
  table to meet the temporal data table requirements.  To do that I will
  run the following code:

ALTER TABLE dbo.Product
    ADD CONSTRAINT PK_ProductID PRIMARY KEY (ID),
    BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT SYSUTCDATETIME()
,
    EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
    PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);

There is some very good information in Creating a System-Versioned Temporal Table that addresses Altering Non-Temporal Table to be System-Versioned Temporal Table.

  When converting an existing table, consider using the HIDDEN clause to
  hide the new PERIOD columns to avoid impacting existing applications
  that are not designed to handle new columns.

  The next step in setting up a temporal table is to identify a history
  table that goes along with my dbo.Product table.  To do that I will
  run the following code:

ALTER TABLE dbo.Product
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

Now the Product table looks like this

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 33.49        | 2017-09-29 14:28:04.8327739 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |

You also find that a new ProductHistory table has been created that looks just like the Product table.  The ProductHistory table is currently empty because we haven't updated or deleted anything from the Product table

Now, let's run an update to generate some history

UPDATE dbo.Product
SET ProductPrice = 34.65
WHERE ProductName = 'Widget';

select * from dbo.Product
select * from dbo.Producthistory

The product table has the current data

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |

And now the ProductHistory table has a versioned row

| ProductName | ProductPrice | BeginDate                   | EndDate                     |
|-------------|--------------|-----------------------------|-----------------------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 |

You can even add a column to the Product table and the ProductHistory table automatically gets the new column added as well

ALTER TABLE dbo.Product ADD ProductColor VARCHAR(10)

Product

| ProductName    | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|----------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 | NULL         |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |

ProductHistory

| ProductName | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|-------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 | NULL         |

  Are there risks of compatibility or performances?

You would certainly need to test this in a development environment first.  You may have unique situations that have an impact on your decision to use Temporal tables.

Here are a few important links with additional information regarding  Temporal tables

Getting Started with System-Versioned Temporal Tables

Temporal Table Considerations and Limitations

Manage Retention of Historical Data in System-Versioned Temporal Tables

Stopping System-Versioning on a System-Versioned Temporal Table


Why am I getting a deadlock for a single UPDATE query? I have two processes which execute code like this in parallel:

begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;

This results in deadlocks.

My understanding of what causes deadlocks is like the scenario described in this question, with "interwoven" UPDATE statements updating two different rows in a different order. I don't understand how a single UPDATE statement could result in a deadlock. I'm not able to replicate the deadlock scenario using two parallel psql sessions in my dev environment. My guesses for why I can't replicate it:

I'm misunderstanding my code which creates the deadlock error, and there are actually multiple UPDATE statements in each transaction
The "interwoven" aspect is happening, but "within" the UPDATE statement which covers multiple rows, so it's difficult to replicate.

Is it possible for this single UPDATE to be creating the deadlock?

Your statement modifies several rows. Each of these rows is locked when it is updated.

It is well possible that a statement in a concurrent transaction has already locked one of these rows, blocking your UPDATE. If the concurrent transaction then tries to lock one of the rows that your UPDATE has already locked, you get a deadlock.


Can SQL Server 2016 R Services be used to connect to Hadoop data? I've only been experimenting with SQL Server 2016 recently. So, please correct me if my assumptions are incorrect:
From some research on SQL Server R Services, I see that RxHDFSConnect and RxHDFSFileSystem functions help load data from Hadoop directly into a SQL Server 2016 database.

Can these functions still work without a Polybase connection to Hadoop?
If yes, why use the Polybase connection at all?

The fastest way to import data into SQL Data Warehouse is to use PolyBase...

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-overview-load/

I do not think the same recommendation exists for SQL Server 2016, but it still makes sense.  The two options are slightly different in that Polybase allows you to access Hadoop or Azure Blob storage directly and has been optimised for scale, eg PolyBase scale-out groups.   You write ordinary T-SQL to access these external tables which is one of the main advantages - no Map Reduce jobs for you!  Using R in SQL Server 2016, you would have to use stored procedure sp_execute_external_script to call these libraries, create the connection, and import the data into a dataframe.  You would then have to expose the dataframe back to SQL Server as an output resultset, then write it to a table, something like ...

--NB This is pseduo-code / will not execute!

INSERT INTO dbo.someTable ( ...
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @script = N'
library(RxHDFSConnect)
OutputDataSet <- some code that allows RxHDFSConnect to get some data ...'
WITH RESULT SETS ( ( col1 INT NOT NULL, col2 FLOAT NOT NULL ... etc ) );

Just in terms of number of steps it feels more complicated.  I haven't done any scale testing on this but instinct tells me this is not meant for massive volumes.  Interestingly I could only find this page on importing data with RevoScaelR in googlecache.

What kind of use cases are you looking out, just out of interest?


Sql Server Brute Force Attack I am running MS Sql Server 2008 on a Windows Server 2008 box. I noticed a slow down and random moments where my Sql instance failed to connect, and looking at the event log showed repeated (1-2 times per second) attempts from an IP address in China to access my "sa" account.

I added the IP address to the block list in my firewall, but the next day the IP address changed and the attack resumed.

Is there any way to automatically add an IP address to my block list if it attempts multiple failed logins? I cannot create a white list, because there are valid connections that come from unknown IPs.

I've never attempted to block traffic at the SQL Server level.  In my experience that is handled via a hardware firewall, Windows Firewall, or both.  I find it interesting that you allow connections to SQL from unknown IP addresses.  I would focus creating a white list of valid, known IP addresses and block everything else as others suggested.

To answer your question:  I did some searching and here is a linked response on writing a SQL query used as a SQL Agent job to block traffic after X number of failed login attempts.  StackOverflow Answer


Does joining to the same table twice have a predictable performance impact I have a simple dimension, for example a date dimension, which is static and unchanging over time. In the fact table, I have several dates.

Is it different from a performance perspective to join to one date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on?

If I have a fact table that looks like this:

FactId   FactNumber  FactDate  FactOtherDate
1        10          1/1/2016  12/31/2015

...and I want to produce a query like this:

Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month
From Fact
Inner Join DateDim On DateDim.Date = FactDate
Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate
Group By DateDim.Month, DateDim2.Month

...would it be better to make a second actual table and join to that, rather than joining to the same table twice?

I ask because indexed views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently. I'm not looking to use an indexed view, just wondering if that restriction provides any insight into this question.

There are numerous restrictions to what can be an indexed view. In a data warehouse setting those restrictions will make it impossible for just about every standard star join view to be an indexed view. Which is really a shame, since despite what the comments say, [materialized] indexed views are magic!

As for whether joining to the same table twice is an issue, it is not. In a data warehouse you'll often find yourself joining to time and date dimensions dozens of times.

Also, I was interested to see that you made the primary key on the date dimension the date itself. In my last data warehouse we ended up needing to use an INT column as the primary key, because we needed to save indeterminate dates (like 2016/1/??) where all of the date parts were not known.

Also, when writing your views, you should always check to verify whether a LEFT JOIN will have better performance than an INNER JOIN, even if they produce the same results. Back when our data warehouse was closer to a snowflake schema, the joins were into the hundreds, and many of the views did better with all LEFT JOINs as opposed to INNER JOINs where INNER JOINs were appropriate.