Limit number of user logins in SQL Server I have a user login named 'manager' currently logged in PC1, I want to prevent this user from login in PC2. So I created a trigger, and I got an error

  Msg 4621, Level 16, State 10, Line 2
  Permissions at the server scope can only be granted when the current database is master

It works well if I use database master, but I want to use database Employee. How can I fix it?

USE Employee;
GO

GRANT VIEW SERVER STATE TO manager;
GO

CREATE TRIGGER connection_limit_trigger2
ON ALL SERVER WITH EXECUTE AS 'manager'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'manager' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'manager') > 2
    ROLLBACK;
END;

Logon triggers are special. Not going too far into it, they must exist in the master database and not any other, thus the answer to your question is: "It can't."

https://msdn.microsoft.com/en-us/library/bb326598.aspx

-Sean


PostgreSQL : Prevent SQL Injection in pl/pgsql function I am trying to handle sql injection related to startdate and enddate in the below pl/pgsql function.

Let's say If I provide startdate as '2019-10-02' and end date as the following :

2019-10-16', 'YYYY-MM-DD') AND rid in ('R1857745') group by rid, dcid UNION ALL SELECT 'R1857745'::character(8) , 'D099901'::character(8), 1, 1, 1, 1 FROM pg_shadow --

The above value for enddate creates a serious SQL vulnerability.

create function A(
 in startdate varchar(10),
 in enddate varchar(10))
RETURNS setof AB AS $$
BEGIN
 return query execute
 'select rid,dcid,
        sum(case when id between 10 and 19 or id between 21 and 26 then noOfcaptured else 0 end) total_tilldate,
        sum(case when dateofevent>=to_date(''' || startdate || ''',''YYYY-MM-DD'') and (id between 10 and 19 or id between 21 and 26) then noofcaptured else 0 end) total_range,
        sum(case when id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_tilldate,
        sum(case when dateofevent>=to_date(''' || startdate || ''',''YYYY-MM-DD'') and id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_range
   from repostats
  where dateofevent<=to_date(''' || enddate || ''',''YYYY-MM-DD'') '
  group by rid, dcid';
END;
$$ LANGUAGE plpgsql;

As startdate and enddate are of variable type date, so we can do validation with date formats. But, I really don't want to do that because in this scenario I am more concerned about single quote.

I know that we can escape ' by using 2 single quotes i.e. ''

Is there any public function available in postgreSQL which can escape single quote, so that we can use the same for all type of variables in where clause or in other places.

I tried using quote_literal, but it's not doing the job for me

Use parameter placeholders instead of concatenating the input into the SQL string.

create function A(in startdate varchar(10), in enddate varchar(10))
  RETURNS setof AB AS $$
declare
  l_startdate date;
  l_enddate date;
BEGIN
 l_startdate := to_date(startdate,'YYYY-MM-DD');
 l_enddate := to_date(enddate,'YYYY-MM-DD');

 return query execute
 'select rid,dcid,
        sum(case when id between 10 and 19 or id between 21 and 26 then noOfcaptured else 0 end) total_tilldate,
        sum(case when dateofevent >= $1 and (id between 10 and 19 or id between 21 and 26) then noofcaptured else 0 end) total_range,
        sum(case when id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_tilldate,
        sum(case when dateofevent >= $2 and id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_range
   from repostats
  where dateofevent<= $3
  group by rid, dcid'
    using l_startdate, l_startdate, l_enddate;
END;
$$ LANGUAGE plpgsql;


Update dynamic jsonb array of objects I am trying to update a jsonb key with a new value, but I cannot update all the keys at once.

My json structure is something like this:

[{
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"

    }
}]

I want to update the align key with a new value, but right now my query does not update all the elements, it only updates one element

this is my query so far:

with align_position as (
    select
    ('{' || index-1 || ',content,align}' )::text[] as path,
    id
    from section, jsonb_array_elements(entities) with ordinality arr(entity, index)
    where entity->'content'->>'align' = 'leftAlign'
)
update myTable set entities = jsonb_set(entities, align_position.path, '"left"', false) from align_position where section.id = align_position.id;

How can I make the query to update all the elements?

Any thoughts?

I managed to solve this using this query:
update
   myTable
set
   entities =
   (
      select
         to_jsonb(array_agg (new_values))
      from
         (
            select
(
               case
                  when
                     arr -> 'content' ->> 'align' = 'leftAlign'
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "left"}'), true), '{}' ) )
                  when
                     arr -> 'content' ->> 'align' = 'rightAlign'
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "right"}'), true), '{}' ) )
                  when
                     arr -> 'content' ->> 'align' = 'centerAlign'
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "center"}'), true), '{}' ) )
                  else
                     arr
               end
) as new_values
            from
               jsonb_array_elements( entities ) as arr
               group by
                  arr -> 'content' ->> 'align',
                  arr
         )
         as new_values
   )
;


SQL Server compatibility level I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level.

How do I justify updating them to 110 compatibility level?

Let me rephrase my question:

The server is upgraded to SQL 2012 but user databases' compatibility levels were not updated to 110.  So tempdb etc system dbs are 110 but user databases are still 90.  I would like to update them all to compatibility level 110.  I wish it's as simple as ALTER database compatibility level. :) But I need to  justify use of resources to thoroughly test the applications out.

Am I right to say that even though the system databases were in compatibility level 110 but the user databases are not reaping the benefit of SQL Server 2012?  Which means the databases were not really upgraded?

Thanks!

When databases are upgraded either using backup restore method or via attach/detach process, the compatibility level of the databases do not get changed automatically, and show as old comp. level. However this is not a major problem, but point to note is that you may not be able to use various features , that are available on newly upgraded SQL server.

As mentioned by owen, you should refer the MSDN doc to refer the difference between the Compatibility-level setting of 100 or lower *with*
Compatibility-level setting of at least 110 .

Also,  running an upgrade advisor  will help you in identifying for any issues while you do that upgrade or change in compatibility level.

If you have the test environment you can always test the scenario before making any changes in prod and once you are good to go, just make sure you have the latest backup of system databases on production server. In case anything goes wrong, you can recover or rollback from the backups.

Also, an additional read here Bad habits : Clinging to old compatibility levels


Subtracting from multiple CASE statements I'm trying to get the net sales from the query below in SQL Server 2008.

We identify a Sale as Type 1 and a Credit as Type 8.

What I'd like to do is get the Sum of the Sales and Subtract the Sum of the Credits to get the NetSales, but I haven't had any luck.

I'm not really sure if I should be using the CASE statement to get the NetSales. I'd like to see the below from the query, or even with just the NetSales showing:

And here is the query:

SELECT TOP (100) PERCENT IM.IM_PROD_CODE, IV.IV_CUKEY, IM.IM_KEY, IM.IM_DESCR,
   SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END) AS Sales,
 SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END) AS Credits

FROM IV INNER JOIN
  SH ON IV_ENTITY_ID = SH.SH_IVENTITY_ID AND IV.IV_NUMBER = SH.SH_IVNUMBER INNER JOIN
   IM ON SH.SH_ITEM = IM.IM_KEY
WHERE
   (IV.IV_TYPE = 1 OR IV.IV_TYPE = 8) AND (IM.IM_PROD_CODE = 'ESY') AND   (SH.SH_DATE >= CONVERT(DATETIME, '2015-11-01 00:00:00', 102))

GROUP BY
   IV.IV_CUKEY, IM.IM_PROD_CODE, IM.IM_KEY, IM.IM_DESCR, IV.IV_TYPE
HAVING
   (IM.IM_KEY = 'A-05.000.007.01_LM')

So you want the result of the second SUM to be subtracted from the first SUM. One option would be to do just that:

SELECT TOP (100) PERCENT
  IM.IM_PROD_CODE,
  IV.IV_CUKEY,
  IM.IM_KEY,
  IM.IM_DESCR,
  Sales    = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END),
  Credits  = SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END),
  NetSales = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END)
           - SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END)
FROM
  … /* the rest of your query */

Yes, you are repeating the SUM expressions, but that is fine, there are only two of them. The DRY principle applicable to many other languages is less relevant in SQL, where duplication of code can be a perfectly normal way to achieve better performance.

In this case, however, the duplication can be avoided by using a derived table:

SELECT
  IM_PROD_CODE,
  IV_CUKEY,
  IM_KEY,
  IM_DESCR,
  Sales,
  Credits,
  NetSales = Sales - Credits
FROM
  (
    … /* the entirety of your current query */
  ) AS derived;

I would also like to suggest, if I may, that you be consistent in how you specify your constant literals. What I mean is sometimes in your query you are providing the matched values for IV.IV_TYPE as strings (IV.IV_TYPE = '1') and other times as numbers (IV.IV_TYPE = 1). You should really choose one way and, of course, it should be the one matching the column's actual type.

Also, the TOP (100) PERCENT in your query makes little sense. It looks as though it may be a remnant of an old technique for intermediate materialisation, which, however, may no longer be working in your version of SQL Server.

There is also this HAVING filter in your query (HAVING (IM.IM_KEY = 'A-05.000.007.01_LM')), which would work more efficiently if you moved the condition to the WHERE clause.

One last note concerns your second CASE expression (the Type 8 one). One of the columns it is referencing is qualified with an alias that is not found in your FROM clause: PK1.SH.SH_PRICE. I am assuming that is some kind of a copy-paste error posting your script here but I thought I would let you know so that you could correct it.


Disk Separation DB installation and data files I am wondering whether it is a good idea to separate the database installation media from the actual data files.

In our current environment we have:

C: - Windows Server
D: - MS SQL Installation & DB Data Files
E: - Backups
L: - Log files

Would it be a good idea to separate out our D:

Drive 1: MS SQL installation
Drive 2: DB Data Files

There are similar questions already posted, but nothing specifically relating to the separation of DB software and DB data files. (similar questions ask whether it is a good idea to separate OS and DB).

Why do we separate things onto different disks?
Performance and resilience.

From the Disaster Recovery perspective, if you lose any one of your disks, how much damage does it do?

Lose the operating system disk?  OK, that's a biggy. :-(

Lose the backup disk?  Non-event; you still have a running database instance.

Lose the data disk?  Ouch!  But that's OK; you've got your backups ...
You just need to restore them into a running SQLServer instance ...

Ah.

But you had that on the same [data] disk that just failed, taking the data with it!

Keep the installation and data separate.

Whilst it's only semantics, I'd suggest keeping a closer "association" between the Data and Logs disks; they make up all the stuff you really care about.

Move the backups off to another server altogether.  (Whilst it's unlikely these days) If all these disks are local to the machine and the machine's motherboard were to fail, it wouldn't matter how many disks you had; they'd all be gone!


Storing arrays in MYSQL? I've googled. I keep getting answers that have to do with a PHP function named "serialize". I won't be working with PHP, so that solution won't work for me sadly.
I worked with a NoSQL db (MongoDB I think) before, and I basically define a collection as such
images [
    {"title": "Cute puppy", "file"="cute.png", "tags": ["cute", "puppy", "summer", "pool"]}
]

I'm wanting to store multiple values in one row for the tags column (honestly I don't know if I said that right, just began searching about MySQL)
+------------+----------+--------------------------+
| title      | file     | tags                     |
+------------+----------+--------------------------+
| Cute Puppy | cute.png | cute,puppy,summer,pool   |
+------------+----------+--------------------------+

The only idea that's coming to me is if I were to use MySQL to read the row, then just split the tags by a comma, that'd work most likely, but I'd be working with many more "tags" possibly in the millions.
My question really is, is MySQL a good choice if you want to store multiple values in one column?

My question really is, is mysql a good choice if you want to store multiple values in one column?

I would suggest that the answer to this question is no - there is a better choice on all 3 levels for the treatment of arrays in database servers.

1. Simple arrays:

MySQL doesn't really have an array datatype - the closest thing they have is the SET datatype, the functionality of which is very limited. As you note in your question, a search leads to lots of links to PHP code which implements array functionality in the app rather than the db.

PostgreSQL has a an ARRAY datatype with a large range of functions to deal with manipulating, comparing and finding elements in arrays.

2. JSON:

Nowadays, arrays have been largely superseded by JSON - hardly surprising since JSON is basially an array++ (but XML-- :-)). This is true for MySQL and for PostgreSQL.

There are a large number of JSON functions available on both systems - MySQL and PostgreSQL - but more on PostgreSQL. I'll use the terms JSON and JSONB (binary - example is for mongodb but applies to binary JSON generally) interchangeably. MySQL JSON is only binary, PostgreSQL has both - but JSONB is generally regarded as much more efficient. See this post for a good discussion of what PostgreSQL "NoSQL types" are good for. The "NoSQL" is a misnomer; you can do SQL JOINs using these types.

With respect to indexing - MySQL essentially implements this by means of a GENERATED column hack whereas PostgreSQL implements 3 sorts of index (Gin, Btree and Hash - see section 8.14.4. jsonb Indexing here) for JSONB.

Joins can be done between JSON and tables in both MySQL and PostgreSQL.

Overall, it is fair to say that JSON has been around longer and is more mature in PostgreSQL than in MySQL.

Classic SQL:

You can of course implement some "multi-element" functionality by using a classic Associative Entity approach. Associative entities are otherwise known as  joining, bridging, many-to-many or m-to-n tables. This strategy is outlined here with an example using students enrolling in university courses. 1 course can have many students and 1 student can attend many courses. The joining table contains the PRIMARY KEY from both the course and student tables and the PK of the joining table itself is the two together - a student can't be in the same course twice!

In favour of the classic approach, Erwin Brandtstetter (a guy well worth listening to about databases!) pointed out in his answer referring to a JSON solution: (Aside: A normalized DB design with basic data types would be way more efficient for this.). JSON is not a panacea and judgement must be exercised in its use. There's been a lot of bandwagon-hopping in recent years with the NoSQL "Cambrian Explosion" from ~ 2008 - 2015 (Mongodb especially springs to mind). Now virtually all of the NoSQL systems have or are trying to put SQL interfaces and instrumentation into their systems. For my money, if your arrays are relatively simple, stick with the classic approach, otherwise go with JSONB.

SQL wasn't designed for and is not good at manipulating comma-separated lists of values. In any case they are a breach of Rule 2 of Codd's Rules which states that Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.. With .csv lists a further positional parameter is required which SQL doesn't deliver but various systems have "tricks" to get round this. SQL's forté is "slicing and dicing" records made up of small simple atomic datums.

PostgreSQL is the F/LOSS (Free/Libre and Open Source Software) system which comes closest to offering the best for all 3 array methods. MySQL is more widespread and possibly has better inbuilt and 3rd party replication and/or sharding, but PostgreSQL is more standards compliant and has been making strides in those areas recently. It also has superior JSONB functionality.

p.s. - oh, yes, I forgot - welcome to the forum! :-)


How to roll back mysql 5.6 to sql 5.1 I recently updated mysql from 5.1 to 5.6 however it's causing too many issues with the websites hosted on my machine. Does anyone know how to remove mysql 5.6 and reinstall version 5.1.

Few things to keep in mind first, you're not using features from 5.6, that are not available in 5.1 like  utf8mb4 encoding (needed for emoji characters)

Take dump of all databases using mysqldump except mysql, is safer to recreate mysql.user table.

Try restoring on staging server first, to see if restored db works the way as you expect.


Same MySQL query is so slower on ubuntu than Windows I have a big select in MySQL 5.7.33. In ubuntu (real server) 20.04 it takes 600s for the answer but 1.2s on Windows about the same on the VM Ubuntu 20.04.
The MySQL configuration for all the os are the same.
Do you have any idea to resolve this problem?
Thanks.
Edited Part:
Explain Select ...
Slow:

Fast:

For 99% I bet it's a CPU TOPOLOGY (both HW and VM may have the same) vs OS LICENSING.
While LINUX KERNEL utilizes all CPU's the WINDOWS limit those number to valid license type. e.g. for W10  PRO AND STANDARD EDITION it is a 2.
So in short - your COMPUTING POWER is limited on WINDOWS OS
while ON LINUX KERNEL IT MIGHT BE ONLY A GOVERNOR to tune the speed of OP's.
Other thing to consider is the utilization of raw or shared mem under those OSes or setting for hyperthreading memory interleaving etc.
Generally all other OS implementation that tries to utilize all those "advanced hw speedup techs" to run OPs.
I have encountered such a situation myself - running a 64 core HP server on a VM with a badly copied topology under unlicensed Windows 10 queries results took very long time (about 5 minutes) when normally under full licensed (64cores) Windows server it took 10 seconds. it all depends on the operating software (use of MT, HT, governors, thread locking etc).


Oracle - create changelog query I came up with an interesting Oracle-db problem and keep thinking about a feasible solution to solve this task. Lets say we got three tables (bold columns are PKs):

A (Name, GroupID, versionID, Column1, Column2)
B (Name, GroupID, versionID, Column1, Column2)
C (versionID, date_created)

So it works as follows:

When a new batch of data is imported into the db, each record is tracked with a new unique versionID in table C
Table B holds all imports / changes while Table A only has one record (the newest)

What I'm trying to do right now, is get a sort of diff / changelog for the different batch uploads. This includes any differences in:

Name, GroupID, ColumnA, ColumnB, ColumnC between two specific dates.

I do this with two inner joins over A,B with Name&GroupID and then B and C over versionID and two dates. This works fine if the PKs Name & GroupID stay the same, see output 1:

|A.Name(new)|B.Name(old)|A.GroupID(new)|B.GroupID(old)|A.Column1(new)|B.Column1(old)|A.Column2(new)|B.Column2(old)|date_created(new)|date_created(old)
|Test1      |Test1      |37            |37            |Dog           |Cat           |Mouse         |Mouse         |2015-08-10       |2015-08-20
|           |Test3      |              |38            |              |Cat           |              |Mouse         |2015-08-10       |2015-08-20
|New record |           |38            |              |Mouse         |              |Mouse         |              |2015-08-10       |

output - A.Column1 changed from Cat to Dog
output - a record gets deleted
output - new record is added which did not exist before

My output works well for output scenario 1, however if the name or groupID gets changed / deleted, or a new record is insert this change obviously won't show up because the join does not work anymore. Does anyone have an idea to deal with this problem. My output should work as shown above in output 1 & 2 & 3.
I am sadly not able to implement any triggers to the db and my goal is to try to solve this via sql queries.

Thanks in advance for your input

There has to be some invariant between two versions to say the versions relate to the same thing. This is the job of the primary key. It exists to differentiate this thing from that thing and to say that all that stuff over there relates to this one thing over here, via the foreign key of stuff.thing_id.  If you change the primary key - Name, or GroupID in your case - you have a new thing, not an old thing with a new name. This is one of the reasons we have impersonal identification like social security numbers.

You have a couple of options. A) re-design your tables so there is an invariant identifier. An integer surrogate key works well for this. B) add two columns Previous_Version_Name and Previous_Version_GroupID. Populate these as you accumulate values for the new version. Use them in your join. "A" would be better.

You may also want to think about using a partitioned table and / or views rather than move data around between TableA and TableB yourself.


Are there any professional fulltime PostgreSQL DBA's? For my job I work on a JavaEE application with PostgreSQL as the database. Although we have a sysadmin for our productions servers, who also manages our database servers, we have no full-time DBA which makes me wonder if there are any. I would imagine any full-time dedicated DBA would work exclusively with Oracle database. Am I overlooking something or am I correct in assuming that there are no dedicated Postgres DBA's?

PS: I'm just asking this out of sheer curiosity.

PPS: I wanted to tag this question with DBA but apparently that would be a new tag. Could someone make this for me?

Judging from the activity on the PostgreSQL jobs mailing list I'd say it's not all that uncommon to find full-time PostgreSQL DBAs.

http://www.google.com/search?q=full+time+site:postgresql.org/pgsql-jobs


How to create data tree in MS Access I am trying to build a database in which I am going to need to have a tree to keep track of cars.

Unfortunately, I don't always know what level of detail I am going to have, and the level that I do have will vary.  So although I will presumably know the manufacturer, I may not always know the model, submodel or year.  In fact, the further I go down the tree, the less likely I am to know.

However, my best data would be the most complete, so I don't want to just throw that away.  I would like to have a place in my DB where data on the cars is stored that contains all this, but while I might see a 2017 Nissan Maxima SL, most of the time I might only be able to make entries into my DB about a Nissan Maxima (no year or submodel).

It's also really important that I minimize the number of fields in the main table.  How would I structure this?  Is there a way to set it up so I don't have to have separate fields for year, make, model and submodel?

If I do, and all those fields exist in my models table, would I have to bring them all into my main table if that's where most of my records are being stored?

Keep in mind I will be making multiple entries of 2017 Nissan Maxima SLs (for example) in my main table.

I believe you are asking about a table design or schema.

Whether you use 1 or many tables is up to you. The increased complexity of normalisation is a trade off between efficiency and convenience.

The easiest option to begin with is to use a single table to describe the car, and create a field for each piece  of information you want to store. Start with number plate, make, model, year...

If you want to limit or restrict the data that can be entered you can add reference tables with foreign key constraints.

If your model is going to be be detailed enough to describe things like engine models, individual parts or a detailed service history then you will probably want to use seperate tables.

The key question here when thinking about the data is: "When i think about one car, does it have one or many of these things?" That will give you a good indication if you will need to add a table for that 'thing'

Sometimes we dont have all the information we would like to have. Thats just a fact of life. You can either collect the missing info, or design your table(s) so that the data is not mandatory.

You need to decide if the lack of detail is going to be a problem for you. Can you achieve want you want with the data you have? Will more data improve the accuracy or output of your database?


MySQL - Optimal indexing for a lookup table. HASH index, BTREE index or composite PK? Let's say I have a very large lookup table that looks like this:

   CREATE TABLE `MyLookup` (
      `FKToTableA` bigint(20) NOT NULL,
      `FKToTableB` bigint(20) NOT NULL,
      `Count` bigint(20) NOT NULL,
      `Bytes` bigint(20) NOT NULL,
      `Packets` bigint (20) NOT NULL,
   ) ENGINE=InnoDB;

The first 2 attributes are FKs to Table A and B respectively. But doesn't need to be. the FKs can be dropped if it's more optimal not to have it. FKToTableA and FKToTableB is the candidate key for this table. Thus FKToTableA and FKToTableB could be a composite primary key if needed.

My question is what's the most optimal way of indexing this table? Storage size and insert times are not a concern.
Most of the time my queries will be joining on to MyLookup on both FK columns and SUM the Count, Bytes and Packets columns.

select
    a.something, b.something, SUM(c.Count), SUM(c.Bytes), SUM(b.Packets)
from
    A a
    inner join MyLookup c on a.Id = c.FKToTableA
    inner join B b on b.Id = c.FKToTableB
where
    a.something = 1 and
    a.Time >= 'blah' and
    a.Time <= 'blah'
group by
    a.something, b.something

I see three options.

1) Put a HASH index on FKToTableA and FKToTableB.

2) Put a composite BTREE index on all columns.

3) Put a Primary Key on FKToTableA and FKToTableB.

I'm kind of leaning towards 1. From what I gather HASH indexes are great at equality comparisons and that's all joins are is a big equality comparison is it not? Still has to go hit the table again to sum the other columns though which may be a bottle neck. Not sure.

Or maybe it's possible to have a hash index with Count, Bytes, Packets? Not sure how composite hash indexes works though.

Has anyone dealt with this type of thing before and can shed some knowledge and suggestions?

EDIT:
Here's my schema for Table A and B to make it a bit more clear

CREATE TABLE `B` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `CustomerIdent` int(11) NOT NULL,
  `MetaData1` varchar(256) NULL,
  `MetaData2` varchar(256) NULL,
  `MetaData3` varchar(256) NULL,
  `MetaData4` varchar(256) NULL,
  `MetaData5` varchar(256) NULL,
  `Time` datetime NOT NULL
  CONSTRAINT `PK_A` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;

CREATE TABLE `B` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `SourceIp` bigint(20) NULL,
  `DestinationIp` bigint(20) NULL,
  `SourcePort` int(11) NULL,
  `DestinationPort` int(11) NULL,
  CONSTRAINT `PK_B` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;

Basically the lookup table is in charge of telling me how many times an row in B happened for one row of A.

a: Satisfy the WHERE with INDEX(something, time)
MyLookup -- If the pair (FKToTableA, FKToTableB) is unique, then make that the PRIMARY KEY, and put the columns in that order so that your SELECT can quickly get into MyLookup.
Don't use BIGINT (8 bytes) unless you expect to exceed 4 billion, the limit for INT UNSIGNED, which takes only 4 bytes.
IP addresses -- for the old IPv4, there are convenient routines for converting to INT UNSIGNED.  For the new IPv6, it won't fit into BIGINT.  See 5.6.3.
Do those Metadata columns need utf8?  Can they be combined into a TEXT column?  And other questions.
Build and maintain a "Summary table" rather than scanning large chunks of the "Fact" table to get this "report".
Do the SUMs before JOINing to b.
Do you really need a many-to-many mapping table?  Seems like this is 1:many.
More tips on many-to-many.


How do I login to SQL Server via group membership in another domain When attempting to connect to SQL Server (2012), I get a login failed error message. The error log on the server reports:

Logon        Error: 18456, Severity: 14, State: 5.
Logon        Login failed for user 'MYDOMAIN\mswart'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.---.--]

However, I expected to be able to login based on my membership to a group MYDOMAIN\MYTEAM

I'm able to login when MYDOMAIN\mswart is created as a login.

The SQL Server machine has joined domain OTHERDOMAIN and the SQL Server service account is also a member of OTHERDOMAIN.

Maybe I'm not looking at the right thing. I'm looking for troubleshooting steps I can take to narrow down the issue so I can understand why I can't log in.

Aaron Bertrand's post Troubleshooting Error 18456 was almost useful... in the comments, it looks like a Gary Mazzone experienced the same issue.

In my case I was able to narrow down my trouble by working with my sysadmin about how the group was defined.

Knowing that the SQL Server was hosted on a machine that had joined another domain and was running with a user account on that other domain, we looked at the Group Scopes in active directory.

My user could authenticate properly after the scope was changed to universal.

If I find an authoritative source on SQL Server's behavior with respect to these domain group types, I'll be sure to update this answer.


How to remove Parameters and Filters in a T-SQL? I created a sql trace and my task is build a report listing the 10's most used commmands.
My problem is: I have the same procedure 100 times but my problem is it is using different filter and parameters. I need to remove them for using count functions (pe, 100 occurrences of this procedure is, true of fact, just one for my report).

There is any program or TSQL code for this removal?

Thanks!

ClearTrace will do the thing you need. Its free to download as well.

  Normalized SQL Statements
  ClearTrace "normalizes" the SQL statements to eliminate transient differences in SQL statements.  For example, the following statement

  Select * From qryLinksByNewsletterID Where NewsletterID=19

  is normalized to

  SELECT * FROM QRYLINKSBYNEWSLETTERID WHERE NEWSLETTERID={##}


How to insert UNIQUE IDs values from several tables into events table I have a table called events defined as follow:

rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables.

event_type should be kind of array containing this values:

[
    "rep_login",
    "rep_share",
    "email_viewed",
    "brand_page_opened",
    "media_viewed",
    "rep_contacted",
]

I need to insert 10k rows on events table but compliance with these conditions:

Each row should contains UNIQUE values for each column
event_type should be randomize for populate the table with several event types

This data is for testing purpose. Can this be done from MySQL query? How?

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions.

    DELIMITER $$
DROP PROCEDURE IF EXISTS Events_insertion$$
CREATE PROCEDURE Events_insertion()
BEGIN
    DECLARE i  INT;
    DECLARE dTime DATETIME;
    SET i = 1;
    SET dTime = NOW();
    WHILE i  <= 10000 DO
       INSERT INTO EVENTS_TEST VALUES
        (
        null,
        (ELT(0.5 + RAND() * 6, "rep_login",
            "rep_share",
            "email_viewed",
            "brand_page_opened",
            "media_viewed",
            "rep_contacted")),
        LAST_INSERT_ID()+2,
        LAST_INSERT_ID()+3,
        LAST_INSERT_ID()+4,
        LAST_INSERT_ID()+5,
        LAST_INSERT_ID()+6,
        (dTime + interval (LAST_INSERT_ID()) second),
        (dTime + interval (LAST_INSERT_ID()) minute)
        );
       SET  i = i + 1;
    END WHILE;
END$$
DELIMITER ;

You can call this procedure like this

call Events_insertion();

and also try not to use name events as your table name since it is a reserved word.
Hope this could help you :)


SQL Server Workspace Memory Does Workspace memory (part of buffer pool) is limited for only Hash Join, Sort etc. only?

If I have a query that does use a nested loop join (wihtout a sort or hash), would it not use workspace memory?

Where would worktable for a simple query that return 100 rows will be created? in Tempdb..

If a query does not have a memory grant then it cannot request memory from this 'workspace'. You can see which queries request memory in sys.dm_exec_query_memory_grants. As you already noticed only certain operators request memory grants, namely the operators that require some significant intermediate storage. A nested loop does not require any storage, as it operates one-row at a time, therefore it will not request a memory grant. A hash requires memory to built the hash tables, and a sort obviosuly requires sort tables. Queries without a grant still need some memory, but that adds up to trivial ammounts (maybe few KBs) and such memory is obtained form ordinary execution allocators. A grant is measured usually in MBs and can go into the 100s of MBs.

The memory reserved for grants is not exactlty 'part of the buffer pool', that makes it sound like is a separate portion of the buffer pool. Is more of a gray area. The memory grant is backed by the buffer pool. A query that is granted X amount of memory places a reservation on the BP. It does not 'steal' the pages from BP right away. But since the (unstolen part of) BP contains data pages, the BP can make good on the reservation promise because it can evict data pages to disk anytime it likes. As the query makes progress it may request pages from the BP from the grant it acquired (from the reservation) and as the BP fulfills the request, the pages handed out to the query become really 'stolen', ie. the BP can no longer use them for data until the query release them back. In order to ensure all grants can be honored the sum of reservations cannot exceed the BP size (ie. there is no VM style over-allocation). These grant reservations tend to err on the overallocation side by a generous margin as things can turn out quite ugly if a query consumes all the grant and styll needs memory: spills occur (extremly slow) or the query may give up and error.

See Understanding SQL server memory grant


SSRS 2012, push permission changes down the structure? I want to make a permission change for someone at the top level of SQL Server Reporting Service and have it trickle down to each folder and report, this is automatic if that folder/report is set to inherit but if it is set to override then it does not. We have hundreds of reports that this 1 permission has to be changed for and I don' want to revert customer permissions on the reports. How can I push this permission change to all the folders and reports?

@Matthew,
Given that you do not want to revert to parent security on your sub-folders and children reports, I think your only option will be to write some custom code which accesses the SSRS Web Services API.  I've done something similar using powershell for one project and a C# script task from SSIS for a different project and should work well although it won't be accessible from the SSRS management portal.

I'm 99% certain that there is no functionality within SSRS that will directly handle your request out of the box.


Finding AD group permissions source We have an AD group as a Login in several servers, usually as sysadmin,
but I've found one server that this login has no access to any DB and has a server public role only.
I've talked to a member of this group about this issue,
and he told me that he has an access to this Server's DBs.
And endeed by opening SSMS as different user(using his AD credentials) at my local station,
he could select, update tables in this server, even shrinking a log file(but cannot see logins except sa and this group).
I cannot find a source for those permissions:
There is no Login\User with his credentials,
there are other AD groups, but if I revoke connect from this group then
he cannot longer connect to this server.
Public server role has only view any database permission
Public DB role has no permissions on dbo schema
Database is not in partial containment....
Where else can I search for a source of this Login's permissions?
*SQL Server 2012 on my and server's computers.

I used this query to fing this login

   USE [master]
GO

DECLARE @username sysname
DECLARE @objname sysname
DECLARE @found integer
DECLARE @sql nvarchar(4000)
DECLARE @results TABLE (Login sysname)

SET @username = ' '
WHILE @username IS NOT NULL
BEGIN
SELECT @username = MIN(name)
FROM master.dbo.syslogins WITH (NOLOCK)
WHERE sysadmin = 0
AND securityadmin = 0
AND serveradmin = 0
AND setupadmin = 0
AND processadmin = 0
AND diskadmin = 0
AND dbcreator = 0
AND bulkadmin = 0
AND name > @username
AND Name NOT LIKE N'NT %'
AND Name NOT LIKE N'##%'
-- this is the list of non system logins
-- ids in server roles may not have corresponding users
-- any database but they should not be removed
SET  @found = 0

IF @username IS NOT NULL
    BEGIN
    --  now we search through each non system database
    --  to see if this login has database access
    SET @objname = ''
    WHILE @objname IS NOT NULL
    BEGIN
        SELECT @objname = MIN( name )
        FROM master.dbo.sysdatabases WITH (NOLOCK)
        WHERE
        name > @objname
        AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

        IF @objname IS NOT NULL
        BEGIN
            SET @sql = N'SELECT @found = COUNT(*) FROM [' + @objname
            + N'].dbo.sysusers s WITH (NOLOCK) JOIN master.dbo.syslogins x WITH (NOLOCK)
            ON s.sid = x.sid WHERE hasdbaccess = 1 AND x.name = '''+ @username + ''''
            EXEC sp_executesql @sql,N'@found Int OUTPUT',@found OUTPUT
            --SELECT @found, @objname, @username
            IF @found IS NOT NULL AND @found > 0
                SET @objname = 'zzzzz'  -- terminate as a corresponding user has been found
        END
    END

    IF @found = 0
    BEGIN
    INSERT INTO @results
    SELECT @username
    END
END
END

SELECT SERVERPROPERTY('ServerName') ServerName ,Login
FROM @results r
ORDER BY Login
GO

Login as the mystery user. Then run:

select * from sys.login_token

Somewhere in the output list will be token that grants you access. For instance you can try this (not guaranteed to work):

select *
from sys.login_token lt
join sys.server_principals sp on sp.sid = lt.sid;


Equality in JOIN or WHERE clause? Efficiency and style Of the two queries below, which is the most efficient for postgresql?
And which has better style in terms of readability, etc.

The difference is the placement of the statement doctor.type != 'surgeon'

Inside the WHERE clause:

SELECT practice.name, doctor.name
FROM doctor
JOIN practice ON (doctor_code = code)
WHERE doctor.name LIKE '%son'
AND (doctor.type != 'surgeon');

or inside the JOIN clause:

SELECT practice.name, doctor.name
FROM doctor
JOIN practice ON (doctor_code = code AND doctor.type != 'surgeon')
WHERE doctor.name LIKE '%son';

Speaking of style, you can improve in several places:

In addition to what Craig already wrote.

It's inconsistent to have one condition that only involves the doctor table in the JOIN clause, while the other one is in the WHERE clause. Be consistent, both or none. Best to put these in the WHERE clause, while the condition that links both tables goes into the JOIN clause - possibly simplified using USING. See below.
It's also inconsistent to table-qualify some columns (doctor.name, doctor.type), but not others (code). In bigger queries its best to always table-qualify all columns to avoid naming collisions.
"name" is not a good name. You should rarely use it. Use meaningful identifiers that you don't repeat in every table. Once you join a couple of tables (which is what you do in SQL all the time), you end up with multiple columns named "name" and have to deal out column aliases to even work with it. "id" or "code" are similarly bad choices.
Use descriptive identifiers as suggested below.
It's an antipattern to use doctor.code and practice.doctor_code. Some not so smart ORMs are in the habit of using this - doesn't make it a good idea. In addition to "code" being a bad identifier, it's good practice to use the same name for columns with identical data. Use doctor_code in both places. Or better yet: doctor_id, because what's in the column is probably an "identifier" not a "code" (wrong term). Such a naming convention also allows to use the convenient USING construct in joins.
Use the SQL standard <> instead of the equivalent !=.
Use table aliases to shorten code and maintain readability.

Everything put together, it could look like this:

SELECT p.practice, d.name  -- for a person, "name" is halfway sensible
FROM   doctor d
JOIN   practice p USING (doctor_id)
WHERE  d.name LIKE '%son'
AND    d.doc_type <> 'surgeon';


Postgres : Select items for an interval from the max date in store I have a database with items looking like this :

 id , item_id , date_item , price
 0  , 3232    , 2019-04-22, 10
 0  , 3232    , 2019-04-23, 4
 0  , 3232    , 2019-04-24, 1
 0  , 3232    , 2019-04-25, 3
 0  , 3232    , 2019-04-26, 4
 0  , 3232    , 2019-04-29, 5
 0  , 3232    , 2019-04-30, 8
 0  , 3232    , 2019-05-01, 9
 0  , 3232    , 2019-05-02, 54
 0  , 3232    , 2019-05-03, 43
 0  , 3232    , 2019-05-06, 7
 0  , 3232    , 2019-05-07, 56

and more ( many item_id  and date_item )...

I'm trying to select the last 30 days of items in database but can't figure out the exact query.

currently i'm doing this :

select *
from items_prices
where date_item > current_timestamp - interval '30 day'
  and item_id = 3232;

Which output the above result because we are the 05-20 and the last item in store is the 05-07.

What im trying to do is to always select the lastest date item in DB and go 30 days from there.

I've been trying to do it with max() but i can't really figure it out and my query doesn't work:

select *
from items_prices
where date_item > max(date_item) - interval '30 day'
  and item_id = 3232;

or something like this :

select *
from items_prices
where my_max_date=(SELECT id, MAX(date_item) AS "Max Date"
                   FROM items_prices
                   where item_id = 3232) > my_max_date - interval '30 day'
  and item_id = 3232;

i can't figure it out.

Editing your last query to the solution:

select *
from items_prices
where date_item >= ( SELECT MAX(date_item) - interval '30 day'
                     FROM items_prices
                     where item_id = 3232 )
  and item_id = 3232;

The subquery can be moved to CTE:

WITH cte AS ( SELECT MAX(date_item) - interval '30 day' lastday
              FROM items_prices
              where item_id = 3232 )
select items_prices.*
from items_prices, cte
where items_prices.date_item >= cte.lastday
  and items_prices.item_id = 3232;

or (to select last records for more than one item_id, maybe all)

WITH cte AS ( SELECT item_id, MAX(date_item) - interval '30 day' lastday
              FROM items_prices )
select items_prices.*
from items_prices, cte
where items_prices.date_item >= cte.lastday
  and items_prices.item_id = cte.item_id
--  and items_prices.item_id = (one item_id value, for example, 3232)
--  and items_prices.item_id IN (item_id list, for example, 3232, 3233)
--  none for all item_id
;


linked server to specific access table I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 table. So my question is how to build a linked server (from SQL Server) connected only to one specific table in Access?

Setting up an Access DB as a linked server gets pretty ugly, but if you absolutely have to do it, you can split that one table into a separate database file, and create a linked table pointing to it from the original Access DB file. Then set up a linked server in SQL Server pointing to the Access database file with that one table.

Better option: Build an SSIS package that periodically imports/synchronizes data from your Access database.

Best option: Use the upsizing wizard in Access to migrate the data to SQL Server, and continue using Access as the front-end application, but not as the storage engine.


Is there a way to do data export from Amazon Web Services RDS for SQL Server to On-Premises SQL Server? We have a large Amazon Web Services RDS for SQL Server instance and we would like to do incremental data transfers from RDS to On-Premesis SQL Server on a regular basis.

The On-prem server will be used to feed information into other systems on an acceptable 1-day delay.

However reading through the docs and searching on google, forums etc, we have not found a seamless way to do off-AWS data transfers using RDS for SQL Server.

Built-in SQL Server functions such as Change Data Capture (CDC) are turned off as well as Replication and off-site Backup/Restore services.

Is there a way to do this or is it a limitation of using RDS?

Best is to load the data into a staging database on premise and then incrementally load the data to the main database.

You can use : SQL Database Migration Wizard found on codeplex.

Even though it is for Azure to onpremise sql server and vice versa, it will work for Amazon RDS as well.

Other alternative is to use SSIS as mentioned by @sqlreader.


How can I ensure that only one user at a time can call a stored procedure? Can I lock an SP? I have a procedure that generates reports basis user requests. The request is such that at a time only one  user can call the procedure. Is there any way I can handle this using DB2? Like place a lock on a procedure similar to how we lock tables?

If you are appropriately licensed (Advanced Workgroup or Advanced Enterprise edition -- or Performance Management Option, HT to @IanBjorhovde) you can use a DB2 Workload Manager threshold to accomplish something like that. First you create a service class for your procedure:

CREATE SERVICE CLASS sc_report

You then define a workload that will assign the appropriate activities to the service class:

CREATE WORKLOAD sp_workload
  CURRENT CLIENT_APPLNAME('SP_REPORT') ENABLE
  SERVICE CLASS sc_report

Here I'm identifying activities by the value of the client application name, which can be set by calling the system stored procedure WLM_SET_CLIENT_INFO() in the session, e.g.

CALL WLM_SET_CLIENT_INFO(null,null,'SP_REPORT',null,null);
COMMIT;

You can use other parameters, such as the user ID, client IP address etc. to identify the workload -- check the manual for CREATE WORKLOAD. After that you define the threshold:

CREATE THRESHOLD report_thresh
  FOR SERVICE CLASS sc_report ACTIVITIES
  ENFORCEMENT DATABASE ENABLE
  WHEN CONCURRENTDBCOORDACTIVITIES > 1 STOP EXECUTION

which says that, if an activity falls into the previously defined service class sc_report and there is already one such activity in the database, this new incoming activity should be stopped. The application will then receive SQLCODE -4712. Alternatively, you can define the threshold to queue concurrent requests instead of stopping them:

CREATE THRESHOLD report_thresh
  FOR SERVICE CLASS sc_report ACTIVITIES
  ENFORCEMENT DATABASE ENABLE
  WHEN CONCURRENTDBCOORDACTIVITIES > 1
  AND QUEUEDACTIVITIES > 10
  STOP EXECUTION

The above means that up to 10 concurrent activities matching the workload definition will be queued and executed one after another.

You will need to make sure that the workload definition identifies your report stored procedure and nothing else, because everything that falls into the defined service class will be subject to the threshold.


SQL Server BULK IMPORT Truncation error I am trying to import a simple CSV file into a table on SQL Server 2017.  The CSV file and table match each other in number of columns and data types.  This is an extract of the CSV file:
UID,customerID,DateT,TypeID,AssociatedID,AttributeID,LandingPage,jsonT,ClientIPAddress
9150,2345,2020-09-01T00:00:36Z,1,,859,campaign01.html,{'product': 'string01', 'type': '', 'string011'},192.168.200.2
9151,2356,2020-09-01T00:02:30Z,1,,640,campaign02.html,{'product': 'string02', 'type': '', 'string022'},192.168.200.1

This is my table:
CREATE TABLE [dbo].[activity](
    [UID] [bigint] NOT NULL,
    [customerID] [bigint] NOT NULL,
    [DateT] [datetime] NULL,
    [TypeID] [bigint] NULL,
    [AssociatedID] [float] NULL,
    [AttributeID] [bigint] NULL,
    [LandingPage] [varchar](256) NULL,
    [jsonT] [varchar](1024) NULL,
    [ClientIPAddress] [varchar](64) NULL,
)
GO

This is my import statement:
BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

I keep getting an error saying the last column (the client IP) is to be truncated.  I checked all the values and they have nothing but IPs. Also the error  is not on all rows and I cannot see for my life any difference between one IP format or the other, they are just IPs! I tried simply making the ClientIPAddress field bigger (just for the sake of it) but I just keep getting the same error.
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 9 (ClientIPAddress).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any idea on what I could check or what I am doing wrong would be highly appreciated.
Thank you!

The import worked for me with your sample data.  But if the real data has commas in that "jsonT" column, it's going to get the data in the wrong columns.  BULK INSERT interprets that comma as part of the CSV format, and then starts putting everything after it in the last column (ClientIPAddress):

If you have control over the creation of the file, you could use a different FIELDTERMINATOR, like pipe (|).  You'll need to pick something that you're sure won't be in the source data, per the docs:

To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

Data fields never contain the field terminator.
Either none or all of the values in a data field are enclosed in quotation marks ("").

UID|customerID|DateT|TypeID|AssociatedID|AttributeID|LandingPage|jsonT|ClientIPAddress
9150|2345|2020-09-01T00:00:36Z|1||859|campaign01.html|{'product': 'string01', 'type': '', 'string011'}|192.168.200.2
9151|2356|2020-09-01T00:02:30Z|1||640|campaign02.html|{'product': 'string02', 'type': '', 'string022'}|192.168.200.1

Then your BULK INSERT statement looks like this:
BULK INSERT dbo.activity
FROM 'C:\temp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
);

If you don't have control over the creation of the export file, you'll unfortunately need to:

do some custom pre-processing on it to be able to use it with BULK INSERT
use a different import technology


Allow filtering not working as expected I'm having issues with getting ALLOW FILTERING to work. In our Cassandra 1.2 database, we have a table like this:

CREATE TABLE some_table
    partition_key text,
    other_key     text,
    non_key       text,
    PRIMARY KEY (partition_key, other_key)

Based on the documentation, this query ought to work:

SELECT *
FROM some_table
WHERE
    partition_key = 'foo'
    AND other_key > 'bar'
    AND non_key = 'baz'
LIMIT 100
ALLOW FILTERING

However, I get this error:

  Unable to execute CQL script on 'Your Keyspace': No indexed columns present in by-columns clause with Equal operator

Even this simpler query fails with the same error:

SELECT *
FROM some_table
WHERE
    partition_key = 'foo'
    AND other_key = 'bar'
    AND non_key = 'baz'
LIMIT 100
ALLOW FILTERING

Yes, I'm aware that you can add secondary indexes to tables, but I thought the whole point of ALLOW FILTERING is to enable exceptions when you're ok with the fact that a potentially large amount of scanning is possible.

It seems like ALLOW FILTERING is completely broken. Why they heck doesn't this work?

Appears this feature is deceptive and what you're trying to do is not yet possible. Slated for v3.

https://issues.apache.org/jira/browse/CASSANDRA-6377


Data Warehouse Creation I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me.

Situation

We have a Cobol "database" (I use the term database lightly...). It's a proprietary Hierarchical Database. We have a secondary software solution that provides ODBC SQL layer on top of it for outside access via a standard'ish interface. It's got what I think is SQL 99 compliant commands.

COBOL Database to SQL leaves a lot to be desired. Index's are set in stone. So if I want to do any query that isn't keyed on the one or two fields that are indexed (Index = Patient numbers. So if I want to query on Admit/Discharge Dates... forgetaboutit)... it's dog slow. Joins turn a 5 minute query into a 45 minute query. To many queries slow the REST of the system down causing bad things.

While the system DOES have an internal query system, it is by no means something I'd consider complete. It has it's strengths and it's weaknesses, but the main problem is co-operation with the outside world that I need to send reports too.

Question

What I'm trying to build... is a TSQL Data Warehouse. I want to create a process that pull the data into a SQL environment that I can control (add index's, use modern stuff like case statements, doesn't take 15 minutes for "simple" queries).

I'm wondering what are some good resources, tutorials, tools that the experts would suggest. My ideal situation would be a "historical" load of all data into a T-SQL database, followed by weekly loads of new data. It wouldn't be ideal for "real time" reporting, but the bulk of my reporting needs would be met with "slightly" old data.

I've been messing with ##Temp Tables, Merge into, update, etc trying to see what the times are for loads and updates and what not... but before I get too deep, I'd like to just get some general advice.

I would recommend Building a Data Warehouse With Examples in SQL Server by Vincent Rainardi, as this covers SQL Server specifics.  The Data Warehouse Toolkit is also an excellent and practical guide, but isn't platform specific.


Does Oracle randomly switch execution plans to check if there is a better solution? I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time.

I don't talk about filling the caches. What I mean is that oracle tries different execution plans and keeps the one with best performance. This means a query send the 100th time is probably less performant than a query sent the 10000th time.

The Query has to be 100% the same, so index is defined and no parameters change or bind variables are used. Oracle only optimizes by sometimes trying a different execution plan and comparing its performance to the previously executed plan.

I would really appreciate if some of the Oracle pros could make this clear for me because I doubt this statement. Does Oracle really randomly switch execution plans to check if there is a better solution?

Whenever the optimizer looks at a query to produce a plan the relevant statistics are examined and it will compare the cost of various plans and pick the cheapest.
It doesn't look at all possible plans as it can 'shortcut' some options.

An execution plan will be produced whenever a hard parse occurs and this only happens when the query to be executed does not already exist in the shared pool.

So . . . the execution plan might change over time as your database may change over time, e.g. that small history table you had last year now has 20million rows rather than 10,000 so the optimiser decides to use an index instead of the full scan.

I've come across many 'tipping points' for the optimizer in the past but they are hard to predict at a granular level because we don't know exactly what algorithms the optimizer uses.

Oracle wont however change its execution plan 'just to see' if it already has a plan for the query.

Maybe the person you spoke to was referring to the new adaptive plans in Oracle 12c but that still doesn't mean Oracle will sometimes try a different plan. Look here for info on adaptive plans https://blogs.oracle.com/optimizer/entry/what_s_new_in_12c


Is PostgreSQL replication production ready? How does PostgreSQL native replication compare to MySQL?

I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?

Production ready?

Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example, as are AWS RDS standbys and read replicas. Streaming replication is used almost universally with PostgreSQL.

Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's improving slowly with each major release. The ability for pg_basebackup to take a copy of the system using streaming replication (and do so from another standby) is a big help.

In general, a feature simply won't be released in PostgreSQL until it's production ready. Bugs happen, like in any software, but they're typically fixed shortly after they're identified. Really major new features sometimes have bugs and issues discovered after the .0 release, but if so fixing them is a high priority; bugs aren't just left around.

I'm not aware of any serious issues with streaming replication - sync or async - nor have I seen any reported for quite a while. They were less stable than Pg's usual standard in the .0 releases of the major versions they were introduced in, but both matured quickly and are thoroughly production-ready.

(Update: There was a specific bug in the new 9.3 version prior to 9.3.4 that did cause replication issues in some cases; users of 9.3 should update to 9.3.4 immediately. Older versions are not affected.)

The only caveat I want to mention is a a minor detail with synchronous replication: If you commit on the master, then cancel the query after it commits while it waits for the replica to confirm, it's treated as commited on the master even before it's replicated. You get the same effect by restarting the master while waiting for the replica to reply. In practice this is an irrelevance, but it's about the only problem I can think of.

Compare to MySQL?

Pg's native replication is quite different to MySQL's.

MySQL uses logical replication where it sends the logical changes made to table data, table structure, etc, and the replica applies those changes.

PostgreSQL's replication is lower level (in 9.5 and below; future versions may also add logical replication). It sends the blocks that changed in the tables. It's simpler, easier to get right, and imposes lower load on the replica server, but consumes more network bandwidth and requires more storage on the master to hold not-yet-replicated changes. It's best configured to use streaming replication with WAL-archiving fallback, making it more complex to configure than MySQL's. It replicates low-level changes like VACUUM activity, not just tuple changes, keeping the on-disk state of the replica the same as that of the master. It is incapable of replicating only one database; the whole system must be replicated, which can be frustrating if you have one big, high churn and unimportant database and one small, low-churn and vital database.

All in all, it depends on what you want to do with it.

I view PostgreSQL's replication as considerably better for replicas used for backup, high availability and disaster recovery. Particularly so when combined with point in time recovery (PITR).

On the other hand, it's not as good for read-only reporting replicas because the need to delay the application of replicated data while running long transactions means that you need to either let it cancel very long running queries or fall greatly behind the master, consuming more disk space on the master and forcing it to work harder to keep up.

There's ongoing work to enable logical replication in PostgreSQL, where the logical changes to table structure, table contents, etc are replicated, rather than their on-disk state. Pg's catalog design and support for user-defined everything makes this quite a complex task. Some of the groundwork has been put in place for 9.4, but full logical replication is unlikely to be usable before 9.6 or later.


How do I install pgcrypto in PostgreSQL 8.4? I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it.

There is no pgcrypto if I try to install it using apt-get install pgcrypto and I don't find any files starting with pgcrypto in my system (I tried find / -name "pgcrypto*").

How do I install pgcrypto so I can use the digest('word-to-hash','sha1') function in my database queries?

Update: I'm struggling to install pgcrypto on another Ubuntu machine. After installing the package using sudo apt-get install postgresql-contrib-8.4 how do I install it to my current PostgreSQL database?

PostgreSQL 9.1+

Note that I'm working on Ubuntu 12.04, which uses postgresql 9.1.

There, I needed to:

sudo apt-get install postgresql-contrib

And then in my database:

postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

And now I can use pgcrypto functionality, gen_random_bytes():

test=# create table test (
  id
    text
    not null
    default encode( gen_random_bytes( 32 ), 'hex' )
    primary key,
  value
    text
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# \d test
                            Table "public.test"
 Column | Type |                         Modifiers
--------+------+------------------------------------------------------------
 id     | text | not null default encode(gen_random_bytes(32), 'hex'::text)
 value  | text |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

test=# insert into test (value) VALUES ('scoobydoo');
INSERT 0 1
test=# select * from test;
                                id                                |   value
------------------------------------------------------------------+-----------
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo


Insert in child table prevents lock on parent table I'm a software developer, and although I've written a query or two, I'm not DBA, so please bear with me if I use the wrong terminology or plainly sound like a n00b.

We've recently had some big issues, which was tracked down to a foreign key which didn't have a supporting index. When deleting from the parent table, it needs to get a lock on the child table.
This makes sense, and it well described.

When diving in to this, I also found out that inserts on a child of the child (grandchild) will use the child in such a way, that no table lock can be aquired until the transaction is closed. For this behaviour I can't find much documentation. The closest I get is this forum post which basically says 'it is how it is', without providing much context.

So, let me share an example to reprocuce this. I got a Task table, which contains tasks for employees to perform (like call a customer, find a product). These tasks can be linked to a number of entities, including on OrderLine. OrderLine, obiously contains lines of orders with a products, quantity and so on.
Then there is a logging table, ActionHistory, which contains information about all kinds of changes which happened on various entities, like product, orders, customers, and tasks.

So basically, ActionHistory is a child of Task, which in turn is a child of OrderLine. Script to create this situation:

-- CREATE Parent table (orderline)
create table TBL_OrderLine(
  OrderLineID integer
);
alter table TBL_OrderLine
  add constraint PK_OrderLine primary key (OrderLineID);

-- CREATE Child table (Task)
-- Task gets a new column, orderlineid, which has an FK, but is not indexed.
-- Because of this, the table is locked
create table TBL_Task(
  TaskID integer,
  OrderLineID integer
);
alter table TBL_Task
  add constraint PK_Task
    primary key (TaskID);
alter table TBL_Task
  add constraint FK_Task_OrderLine
    foreign key (OrderLineID)
    references TBL_OrderLine(OrderLineID);

-- CREATE Log table (child of child, Action history)
-- Action history (since only recently, by the way) has an FK to Task, which is propertly indexed.
create sequence SEQ_ActionHistoryID;

create table TBL_ActionHistory(
  ActionHistoryID integer,
  TaskID integer,
  Message varchar2(4000)
);
alter table TBL_ActionHistory
  add constraint PK_ActionHistory
    primary key (ActionHistoryID);

alter table TBL_ActionHistory
  add constraint FK_ActionHistory_Task
    foreign key (TaskID)
    references TBL_Task(TaskID);

create index IDX_ActionHistory_TaskId on TBL_ActionHistory(TaskID);

-- Insert a bunch of orderlines
insert into TBL_OrderLine values (1);
insert into TBL_OrderLine values (2);
insert into TBL_OrderLine values (3);

-- Insert a bunch of (unrelated) tasks.
insert into TBL_Task values (101, null);
insert into TBL_Task values (102, null);
insert into TBL_Task values (103, null);

commit;

The FK on TBL_Task.OrderLineID isn't indexed.
The FK on TBL_ActionHistory.TaskID does have a proper index.

In session 1, I add something (completely unrelated) to my action history. It doesn't even have a TaskID.

insert into TBL_ActionHistory(ActionHistoryID, Message)
values (201, 'Something');

Then, in session 2, I try to remove an order line.

delete from TBL_OrderLine where OrderLineId = 2;

This second statement will wait until the transaction of the first statement is closed. This indicates that the insert into ActionHistory is blocking the other session from getting a lock on the Task table.

This surprised me, because nothing is being modified in the Task table at all. It may be used just for checking, but after that, it could be done. The new ActionHistory record is not referencing any row in the Task table, so no data lock needed there, so why is this happening?

I hope someone can provide not only the theory, but also a reference to Oracle documentation that explains this.
And of course I'm open to suggestions to tweak this even more apart from just adding the index.

We're using Oracle 11g (11.2.0.4.0).

The first statement,

insert into TBL_ActionHistory(ActionHistoryID, Message)
    values (201, 'Something');

puts TM locks on TBL_TASK and TBL_ACTIONHISTORY tables to enforce FK constraint.
The second DELETE successfully locks row in TBL_ORDERLINE, but it also has to lock the whole TBL_TASK table (because you don't have index on TBL_TASK.OrderLineID) .  From docs :

  Locks and Unindexed Foreign Keys When both of the following conditions
  are true, the database acquires a full table lock on the child table:

  No index exists on the foreign key column of the child table.

  A session modifies a primary key in the parent table (for example,
  deletes a row or modifies primary key attributes) or merges rows into
  the parent table. Inserts into the parent table do not acquire table
  locks on the child table.

  Suppose that hr.departments table is a parent of hr.employees, which
  contains the unindexed foreign key department_id. Figure 9-3 shows a
  session modifying the primary key attributes of department 60 in the
  departments table.


import text file getting command is not allowed with this MySQL version here is my table

CREATE TABLE cities (
    country       CHAR(2),
    city_ascii    VARCHAR(100),
    city          VARCHAR(255),
    region        CHAR(2),
    population    INT UNSIGNED,
    latitude      DECIMAL(10, 6),
    longitude     DECIMAL(10, 6),
    INDEX idx_lat_long (latitude, longitude),
    INDEX idx_country (country),
    INDEX idx_region (region)
);

I'm trying to import a text file to my database following tutorial here.

LOAD DATA LOCAL INFILE 'worldcitiespop.txt' INTO TABLE cities
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(country, city_ascii, city, region, population, latitude, longitude);

but I'm getting the following error message.

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Any ideas?

Thank you

From the MySQL Documentation on Security Issues with LOAD DATA LOCAL

If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version

How ? The server option local_infile must be disabled. Check your my.cnf for load_infile. If you are using a web hosting service, they may or may not allow it. For example, you cannot do that with Amazon RDS for MySQL. You might be able to get away with using MySQL in Amazon EC2. Check with your administrators in that respect.


Error updating zoned field from SQL Server stored procedure linked server to iSeries I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned.
In my stored procedure the value is defined as integer, and on the iSeries is defined as Zoned 6 long with no decimals.
I've tried casting the integer as follows:
cast(@SysDate as NUMERIC(6))

but I'm still getting error:

Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "PD999 F98OWSEC" could not UPDATE table.
The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

I don't get this error when removing the zoned fields from the update so I'm pretty sure it's a type conversion issue.
Linked server is defined as OLE DB Provider for ODBC drivers and provider string is MSDASQL.1
Any help here would be greatly appreciated

IBMi DB2 Numeric Data Types:
RPGLE - DESCRIPTION - DB2 SQL
P - Packed decimal - DECIMAL - DEC
S - Zoned decimal - NUMERIC - NUM


Fetch Data from same table name across schema I have this multitenancy database let say of hotels. Each hotel Company is a schema in the database and all have same structure and table names.

Now I want to present menus from all available hotels. Basically I want something like

____________________________________________
| schema_name | menu_id | menu_name | cost   |
--------------------------------------------
| hotel_1     | 1       | Ugali     | 4000TZS|
--------------------------------------------
| hotel_2     | 1       | Ugali     | 3500TZS|
--------------------------------------------
| hotel_3     | 1       | Samaki    | 8000TZS|
--------------------------------------------

I can get menu_id, menu_name and cost from menus tables. How do I achieve that (if it is possible at all) ?

SET SEARCH_PATH TO hotel_1, hotel_2, hotel_3 does not work and brings results from only one schema.

The best way is writing your schema names explicitly like below;

select menu_id, menu_name, cost from hotel_1.menus
union all
select menu_id, menu_name, cost from hotel_2.menus
union all
select menu_id, menu_name, cost from hotel_3.menus

On the other hand, you can write a set returning function (or table returning function) and use execute
to run dynamic query. Here is a sample set returning function for your case;

CREATE OR REPLACE FUNCTION public.get_all_menus (
  out menu_id integer,
  out menu_name TEXT,
  out cost DECIMAL(10,2)
)
RETURNS SETOF RECORD AS
$$
DECLARE
  l_query TEXT;
BEGIN
  -- Create dynamic query
  SELECT
    array_to_string(array_agg(format('select menu_id, menu_name, cost from %I.%I', n.nspname, c.relname)) , E'\nunion all\n')
  INTO
    l_query
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE
    c.relkind = 'r' AND
    n.nspname NOT IN ('pg_catalog', 'information_schema') AND
    n.nspname NOT LIKE '^pg_toast' AND
    c.relname = 'menus';  --the table name which is common for all schemas

  --run the query by using execute.
  RETURN QUERY EXECUTE l_query;

END;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

You can use this function as if its a table. The usage is;

select * from get_all_menus();


Copying a subscribed database to the publisher server I have two servers.
I have installed a merge replication in the publisher (Server A) and I have added two subscriptions with the same database name.
- One on the publisher itself and the seconde one on the subscriber (Server B).So far so good. The replication is working well.
I wanted to delete the subscibed database on the publisher (Server A) and replace it by a copy of subscribed database from Server B.

I thought the publisher will continue synchronizing on the newly attached database, but unfortunately, it didn't work as expected, it started aplying snapshot etc.. instead.
Is there anything to modify on the the copied database to make the publisher reconise it as the deleted one and continues synchronizing  ?

Is there anything to modify on the the copied database to make the publisher reconise it as the deleted one and continues synchronizing ?

This is not possible. The subscription is now broken and should be removed / recreated per your requirements. There are unique identifiers in play beyond the DB name which is why it didn't work as you intended.


Store geo location data in MySQL I would like to know how should i store longitude and latitude values in a mysql database, maybe the questions should be: Decimal vs Point (Spatial Data)

I found this question where the question is almost the same, but what i want to know unlike that question is how will i use the data when using each of the options?
I know that when using decimal i'll simple retrieve the columns data and use them, but what when using Point?

Also i would like to know about the benefits of using any of them, and if there's a better way to do it i would like to know it. Also i'd like to know why google used float in this example?

As they stated 6 positions is all you need for Google maps - likely enough to not lose precision with a float. They may have correcting logic and likely don't have any mission critical apps running off standard API access (maybe??). Good answer concerning precision of coordinates here https://gis.stackexchange.com/questions/8650/how-to-measure-the-accuracy-of-latitude-and-longitude.

Decimal is a basic numeric field... your queries will be restricted to operations appropriate to decimals. This might be all you need if you're just storing the data and have an application layer that does any additional logic with the locations. If you're just appending Lat/Long to another table then you may already have all the indexing you need. For example, a Property table might include geo-coords, but it is primarily accessed by Property Name, Property Codes, etc. by users and systems. This should be simpler to read, maintain, and doesn't require any specific query/spatial knowledge.

If you want the benefits of GIS then you should store data in it's spatial specific data types. This will allow geospatial indexing, and direct, DB level manipulation of the coordinates. You can write queries that provide bounding boxes or calculate distances. This may be more efficiently handled at the DB level, especially for set selection. Instead of dumping a lot of unnecessary decimals to an app and expecting a mobile phone to do routing or distance calculations (as if decimals is all it would need!) you can use SQL spatial implementation to answer questions and find locations.

In SQL for spatial data you could create queries like this:

DECLARE @g geography = 'POINT(-121.626 47.8315)';
SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address
ORDER BY SpatialLocation.STDistance(@g);

To do the same with decimal points you would either need an API to feed that into or create your own logic to determine distance between lat/long pairs.

You can still get your spatial data out in the form of a lat/long numeric pair, but if that is all you're going to be using (e.g. feeding into an API) then just store it as a decimal.

DECLARE @g geometry;
SET @g = geometry::Parse('POINT(3 4 7 2.5)');

SELECT @g.STX; --3
SELECT @g.STY; --4
SELECT @g.Z; --7
SELECT @g.M; --2.5

The above code creates a point with the geometry data type and each SELECT returns the requested part of the point definition.


How can I find out if any cluster member is offline in mongodb cluster? I have a mongodb cluster with 1 master machine(running mongos and config service) and 3 slave machines(used as data store).But now,I find that one of 3 machines (192.168.10.11) is down because I can neither  ping to it nor telnet to it.Of cource,only one machine is down will not lead to data loss because each replication set has three replication members which is distributed to three different machines. I select a database and run db.stats(),I cannot see anything wrong ,every shard is there.I use db.runCommand({listShards:1}),I cannot see anything wrong either,no shard is wrong.

So , my question is ,how can I find out any exception message from mongodb if any of cluster members is down or offline?

If you run the db.printSlaveReplicationInfo() or rs.status() as well as the db.printShardingStatus() commands you should be able to determine who can see who in the cluster and what state each member is in.


Incorrect Syntax With Dynamic SQL I am trying to run the below statement, but I keep getting an error of

  Msg 102, Level 15, State 1, Line 23
  Incorrect syntax near '+'.

What do I change so this becomes valid syntax?

    Declare @salesman varchar(max) = 'Rich'

Select * FROM OPENQUERY(XXX.XXX.XXX.X,'Select
        cm.salesman salesmanName
        ,iv.saledate InvoiceDate
        ,CAST(COALESCE(jc.salecost,0) AS DOUBLE PRECISION) As totalsalecost
        ,jc.saleid saleid
FROM jobcost jc
        INNER JOIN arc ac
        ON jc.jcid = ac.jcid
        LEFT OUTER JOIN rama wp
        ON wp.jwid = ac.jwid
        Left join ira iv
        ON jc.saleid = iv.saleid
        LEFT JOIN sales jb
        ON jc.saleid = jb.saleid
        INNER JOIN salesman cm
        ON cm.arid = jb.arid
        WHERE wp.jwid IN (1,3,4,5,6) AND (ac.includeascost)
        AND jc.saleid IN (Select saleid from ira)
               AND cm.salesman IN ('+@salesman+')
               AND iv.saledate BETWEEN ''01/01/2016'' AND ''12/31/2016''')

You can't, the query portion of the OPENQUERY must be a string literal, so no variables passed in.  You can make the whole thing dynamic SQL though.

 Declare @salesman varchar(max) = 'Rich'

 DECLARE @Query NVARCHAR(4000) = '

Select * FROM OPENQUERY(XXX.XXX.XXX.X,''Select
        cm.salesman salesmanName
        ,iv.saledate InvoiceDate
        ,CAST(COALESCE(jc.salecost,0) AS DOUBLE PRECISION) As totalsalecost
        ,jc.saleid saleid
FROM jobcost jc
        INNER JOIN arc ac
        ON jc.jcid = ac.jcid
        LEFT OUTER JOIN rama wp
        ON wp.jwid = ac.jwid
        Left join ira iv
        ON jc.saleid = iv.saleid
        LEFT JOIN sales jb
        ON jc.saleid = jb.saleid
        INNER JOIN salesman cm
        ON cm.arid = jb.arid
        WHERE wp.jwid IN (1,3,4,5,6) AND (ac.includeascost)
        AND jc.saleid IN (Select saleid from ira)
               AND cm.salesman IN ('+@salesman+')
               AND iv.saledate BETWEEN ' + QUOTENAME('01/01/2016', '''') + ' AND ' + QUOTENAME('12/31/2016', '''') + ')'

EXEC sp_ExecuteSQL @Query


Postgres 9.1 wait events Is there anything in Postgresql  9.1 similar to wait events dynamic views of oracle?
I need to find queries which are waiting for a long time, and the events they are blocked on.

There isn't such a fine grained tracking available in Postgres (other than using DTrace which apparently gives a lot of information).

There is a whole chapter in the manual "Monitoring Database Activity"
http://www.postgresql.org/docs/current/static/monitoring.html

Another option is the module pg_stats_statements:
http://www.postgresql.org/docs/current/static/pgstatstatements.html


How can I track down an issue with a locked table, endless query? I have a table that is periodically getting locked in a manner that I don't quite understand. I cannot do the following:

select * from thetable
select count(*) from thetable

There are roughly 2,000 records.

I can do the following:

select top 2000 * from thetable
select * from thetable where ID = etc.

Going backwards to find a new record that perhaps was problematic, incrementing the count in chunks until I can finally reproduce it again:

select top 1500 * from thetable order by ID desc
select top 1550.... etc. and eventually it gets locked and never finishes the query.

Query never finishes... have waited 10 minutes. Only resolution is to restart the service.

The related stored procedure that I thought caused the problem I ran manually (it interacts with this table) and the longest time it took was roughly 45 seconds. This particular procedure goes through many phases and is wrapped in a transaction with a try/catch/rollback/commit. There is no explicit locking set on the procedure.

Any direction or guidance to track down the root issue is greatly appreciated.

As mentioned by others use spwhoisactive or spblitzwho to find the behavior.

However I am suspecting you do not have sufficient index on that table. A better index will just make the lead blockers query run fast and avoid blocking the query scanning same table.

Also check for open transactions with sleeping status . Sometimes they create those blocking scenarios when application did not close the connection well


Changing SQL Server installation - Will it bring the server down? I need to install SSIS on our server, one concern that I have is the database being brought down temporarily since it's mission critical that the server is always up.

Does changing the SQL Server installation disable the server?

If it does bring the database down, is there any means of installing SSIS without touching our main installation? I could always create a fresh install if this is more suitable.

Adding SSIS to your server should not cause the running SQL Server to be affected, stopped, restarted, etc. I have never seen this happen with adding SSIS to a server.

However, if you want to make absolutely sure, you could test by installing SQL Server on a test server.  Then come back and install SSIS and see what happens.


Reasons to Backup (or not backup) QA and Dev Databases Pretty simple question: is it standard/best practice to backup dev and QA instances of the database?

Assume that the schema information is kept in some form of source control.

QA and dev databases (I will refer to these as non-prod[uction] to encompass all life cycles) are still databases.  So the business (in this case, might be the app dev team) needs to determine what type of disaster recovery they require.

In my opinion, SLAs don't just apply to production databases.  They apply to any data store that requires a certain RPO/RTO.  In the case of development databases, DML might not be a huge concern, but the loss of DDL could be application-breaking.

The point I'm trying to get at, is we here can't answers whether or not you'll want to back up your non-prod databases.  You will need to find out what type of disaster recovery is required of these databases.  There's a chance that database creation scripts generated weekly is enough.  But there's also a possibility they may require native database backups.

Personally, I always resort to some form of backups.  Even if it's simple recovery and just daily full backups.  These non-prod databases are typically not gigantic (although there are definitely exceptions to that rule), and end-user impact isn't usually a big concern in lower lifecycle environments (again, there are exceptions here as well).  I typically err on the side of more DR than less DR.  But I'd still recommend talking to the business group to get clearly defined and documented guidance for disaster recovery.  You don't want to be the reason that a week of development was just lost because you haven't been taking backups.


Attempt to decompose up to second normal form (2NF) in a scenario about students and courses 1NF:

(student_id, name, start, program, course_code, course_name, faculty, credits, time, grade)

Minimal functional dependencies (identified):

{student_id} → { name, start, program }
{course_code} → { course_name, faculty, credits, time }
{student_id, course_code} → { grade }

2NF:

student (student_id, name, start, program)
course (course_code, course_name, faculty, credits, time)
student_course(student_id, course_code, grade)

Is this 2NF decomposition really correct, or am I completely out of scope?

There is no non-prime attribute in any of the relations that is functionally dependent on a proper subset of any candidate key. That is obvious from the functional dependencies - only the third table has to be checked, and your third functional dependency covers that.

Consequently your result is in 2NF.


Creating Database Mail with T-SQL returns "principal name is not valid" Configuring Database Mail is so long.

So I want to create a script that automatically create it for me

DECLARE @YourEmail NVARCHAR(50) SET @YourEmail = 'MyEmail@mydomain.com' --Put here your E-mail
DECLARE @YourPassword NVARCHAR(50) SET @YourPassword = 'MyPassword' --Put here your E-mail
DECLARE @YourSMTPSserver NVARCHAR(50) SET @YourSMTPSserver = 'MySMTPserver' --Put here your SMTP Server
DECLARE @YourPort int SET @YourPort = '25' --Put here your SMTP port

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL_Spring_Cleaning_Account',
    @description = 'Mail account for SQL_Spring_Cleaning.',
    @email_address = @YourEmail,
    @replyto_address = @YourEmail,
    @display_name = 'SQL_Spring_Cleaning Mailer',
    @mailserver_name = @YourSMTPSserver,
    @port = @YourPort,
    @use_default_credentials = 0,
    @username = @YourEmail,
    @password = @YourPassword;

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SQL_Spring_Cleaning_Profile',
    @description = 'Profile used for SQL_Spring_Cleaning mail.' ;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SQL_Spring_Cleaning_Profile',
    @account_name = 'SQL_Spring_Cleaning_Account',
    @sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'SQL_Spring_Cleaning_Profile',
    @principal_name = 'ASPIRES3\franc',
    @is_default = 0 ;

But unfortunately SSMS returns me:

Msg 14607, Level 16, State 1, Procedure sysmail_verify_principal_sp, Line 49 [Batch Start Line 0]
principal name is not valid

The error is obviously on line

@principal_name = 'ASPIRES3\franc',

but I don't understand what is wrong with that

Is the name you're using a user in the msdb database?

  [ @principal_name = ] 'principal_name'
  The name of the database user or role in the msdb database for the association. principal_name is sysname, with a default of NULL. Either principal_id or principal_name must be specified. A principal_name of 'public' makes this profile a public profile, granting access to all principals in the database.


Migrating from SQL Server 2008 R2 to SQL Server 2014 I'm migrating from SQL Server 2008 R2 to SQL Server 2014. Can someone tell me if my approach will work?

Current Environment:

SQL Server 2008 R2
Windows 2008 R2

Steps to Migrate from SQL Server 2008 R2 to SQL Server 2014:

Get clean install of Windows Server 2012
Install SQL Server 2014 Standard x64
Backup and restore SQL Server 2008 R2 databases to the 2014 instance
Import SSIS packages into SQL Server 2014
Do conversion of DTS packages to SSIS packages in SQL Server 2014

Done.

In theory will this work? I don't want to do an in-place upgrade. I can't because I'm going from a physical server to a virtual server as well as doing an OS upgrade.

Your plan sounds OK to me. I have written a detailed answer describing each step at upgrading from lower version of sql server to higher version.

To help automate most of the process use PowerShell dbatool written by Chrissy LeMaire [migrates over 25 components including databases, jobs , logins, etc]. Here is a small video as well to get you started and how it works.

make sure you do full regression testing as moving to sql server 2014, we got hit by the new cardinality estimation (CE) bug and had to use TF 9481 to use the old CE.


Using a SELECT command to retrieve data on MySQL Say I have three tables - pupils, classes and entries.

Table pupils:

pupil_id (primary key)
first_name
middle_name
last_name
email (unique)
password
reg_date

Table classes:

class_id
subject_name
level_of_entry
exam_board

Table entries:

entry_id (primary key)
exam_date
class_id (foreign key)
pupil_id (foreign key)

Now, for example, let me say that a pupil with the name of Jonathan Parker Smith (pupil_id = 1) was taking a Chemistry exam (class_id = 3) which was 'A2' level_of_entry and part of the 'WJEC' exam board. The entry_id would be 5 and let us say that the exam was to be taken on the 25/06/18. There are other pupils like Jonathan who are taking exams in the 'WJEC' board but with other subjects. How would I create a query script (MySQL) that looks at the 'entries' table and lists the names of all pupils, class name and level of entry for the exams entered with the 'WJEC' exam board? I have done some research and that has led me to believe that I should use a SELECT command...

Any help appreciated,
Thanks.

The SELECT starts off by saying "give (select) me these columns" from this table that connects or joins to these other two tables in this way and where this rule is true.

So you figure out what columns names you need for the results.

Then you figure out how to connect all of the pieces together (foreign key here connects to some primary key there).

Then you add a where clause to filter out unwanted data.

Select p.first_name, p.middle_name, p.last_name,
  c.subject_name, c.level_of_entry
From classes c
Inner Join entries e on e.class_id = c.class_id
Inner Join pupils p on p.puil_id = e.pupil_id
Where c.exam_board = 'WJEC'


Authoritative source that  and != are identical in performance in SQL Server Consider this answer on SO that reassures the asker about the <> operator that:

<> is ... the same as !=.

But then a commenter pipes up and says:

It's true that they are, functionally, the same. However, how the SQL optimizer uses them is very different. =/!= are simply evaluated as true/false whereas <> means the engine has to look and see if the value is greater than or less than, meaning more performance overhead. Just something to consider when writing queries that may be expensive.

I am confident this is false, but in order to address potential skeptics, I wonder if anyone can provide an authoritative or canonical source to prove that these operators are not just functionally the same, but identical in all aspects?

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present:

This occurs well before anything in the optimizer gets involved.

  From Comparison Operators (Transact-SQL)

Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register eax** as follows:

╔════╦══════╗
║ Op ║ Code ║
╠════╬══════╣
║ <  ║    1 ║
║ =  ║    2 ║
║ <= ║    3 ║
║ !> ║    3 ║
║ >  ║    4 ║
║ <> ║    5 ║
║ != ║    5 ║
║ >= ║    6 ║
║ !< ║    6 ║
╚════╩══════╝

!= and <> both return 5, so are indistinguishable in all later operations (including compilation & optimization).

Though secondary to the above point, it is also possible (e.g. using undocumented trace flag 8605) to look at the logical tree passed to the optimizer to confirm that both != and <> map to ScaOp_Comp x_cmpNe (not equal scalar operator comparison).

For example:

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID != 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID <> 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

both produce:

LogOp_Project QCOL: [P].ProductID
    LogOp_Select
        LogOp_Get TBL: Production.Product(alias TBL: P)
        ScaOp_Comp x_cmpNe
            ScaOp_Identifier QCOL: [P].ProductID
            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
    AncOp_PrjList

Footnotes

* I use WinDbg; other debuggers are available. Public symbols are available via the usual Microsoft symbol server. For more information, see Looking deeper into SQL Server using Minidumps by the SQL Server Customer Advisory Team and SQL Server Debugging with WinDbg – an Introduction by Klaus Aschenbrenner.

** Using EAX on 32-bit Intel derivatives for return values from a function is common. Certainly the Win32 ABI does it that way, and I'm pretty sure it inherits that practice from back in the old MS-DOS days, where AX was used for the same purpose - Michael Kjörling


Oracle over-indexed tables I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more then 80 different indexes. Besides that I suspect the database server has a lack of memory.
I know that many indexes can harm performance of UPDATE , INSERT AND DELETE actions.
I wonder if so many indexes might decrease the performance of complicated SELECT query?

For me too many indexes means that it is more expensive to insert, update, or delete rows. Because, if the indexes are of any use they need to be maintained. Find all of the indexes with columns that you either don't query, or don't query very often and drop them. Find all of the indexes where the columns are the same, but in a different order drop all but one version of those. For example a good index would start with low cardinality and get more specific. For example country, state, city, zip code. Another good method would be that if you search by date and other fields lead with the date column to eliminate the largest number of rows first, then choose the values where the other fields match. Try to get the number of indexes down to less than 10 per table.

Although the number of indexes that are useful depends on whether this is OLTP, DSS or data warehouse.