Comparing year over year data I am wanting to pull data for a date range in 2017 then pull the same date range but for 2016.  I thought I would get crafty and use DateAdd() feature, but I am missing something here as I get an error of

  Msg 241, Level 16, State 1, Line 14
  Conversion failed when converting date and/or time from character string.

What I want to do is get a COUNT(saledate) that fall between the time period which will give me the number of sales.  This is sample DDL

Declare @startdate varchar(100), @enddate varchar(100), @prevyrstartdate varchar(100), @prevyrenddate varchar(100)

SET @startdate = '01/01/2017'
SET @enddate = '01/31/2017'
SET @prevyrstartdate = CAST(DateAdd(yy, -1, @startdate) As Date)
Set @prevyrenddate = CAST(DateAdd(yy, -1, @enddate) As Date)

Declare @HoldingPattern Table
(
    salesman varchar(100)
    ,saledate date
)

Insert Into @HoldingPattern (salesman, saledate) VALUES
('BOB', '2017-01-23')
,('BOB', '2017-01-30')
,('BOB', '2017-01-24')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-02-30')
,('BOB', '2017-02-27')
,('BOB', '2017-03-31')
,('BOB', '2017-03-27')
,('BOB', '2016-01-23')
,('BOB', '2016-01-30')
,('BOB', '2016-01-24')
,('BOB', '2016-01-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-02-27')
,('BOB', '2016-02-31')
,('BOB', '2016-02-27')

Select
salesman
,[2017 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @startdate AND @enddate)
,[2016 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @prevyrstartdate and @prevyrstartdate)
FROM @HoldingPattern

Bad data, bad data types, and bad formatting aside, if you're getting a month of data for present and previous year, you don't need to have four variables; one will do. But it should be a date, not a string. I also recommend avoiding BETWEEN since it only works in an extremely narrow set of circumstances.

DECLARE @startdate date = '20170101';

DECLARE @HoldingPattern TABLE
(
  salesman varchar(100),
  saledate date
);

-- let's ignore the illogical dates
INSERT @HoldingPattern (salesman, saledate) VALUES
 ('BOB', '20170123'),('BOB', '20170130'),('BOB', '20170124'),
 ('BOB', '20170130'),('BOB', '20170130'),('BOB', '20170130'),
 /*('BOB', '20170230'),*/('BOB', '20170227'),('BOB', '20170331'),
 ('BOB', '20170327'),('BOB', '20160123'),('BOB', '20160130'),
 ('BOB', '20160124'),('BOB', '20160130'),('BOB', '20160330'),
 ('BOB', '20160330'),('BOB', '20160330'),('BOB', '20160227'),
 /*('BOB', '20160231'),*/('BOB', '20160227');

SELECT
  Salesman,
  [2017 Data] = COUNT(CASE WHEN saledate >= @startdate
    AND saledate < DATEADD(MONTH, 1, @startdate) THEN 1 END),
  [2016 Data] = COUNT(CASE WHEN saledate >= DATEADD(YEAR, -1, @startdate)
    AND saledate < DATEADD(YEAR, -1, DATEADD(MONTH, 1, @startdate)) THEN 1 END)
FROM @HoldingPattern
GROUP BY Salesman;

Result:

Salesman    2017 Data    2016 Data
--------    ---------    ---------
BOB         6            4

Further reading:

#BackToBasics : Dating Responsibly
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
What do BETWEEN and the devil have in common?


Process staging table by getting unique rows only (incremential) I had simple problem with etl on staging table that was answered here:

Process staging table by getting unique rows only

Now after apllying solution I spotted that will be unusable in production enviroment due to amount of data (around 1 mln rows daily) and that will be longering execution of etl statement every day by 1 minute at least on my dba infrastructure, then it will be unusable after few months.

Also staging table can't be locked in any time for INSERT operations.

How to do that etl in incremential fashion? like:

open transaction
take rows from staging
process that new rows (eliminate rows that exists in final table, not agaist staging table, like in non-incremential solution answered in thread linked above)
insert new rows into final table
delete rows from staging table taken at point 2.
commit transaction

Problematic is only 3 point, can anyone help?

OK, let's look at a simple change to the code snippet from the previous post.

-- Create indexes to support the unique merge of the two tables.
CREATE TABLE StagingTable ([Key] INT, A INT, B INT);
  CREATE INDEX Staging_MergeHelp ON StagingTable (A, B);
CREATE TABLE FinalTable ([Key] INT IDENTITY, A INT, B INT);
  CREATE INDEX Final_MergeHelp ON FinalTable (A, B);
--
--  A first run of your job to insert unique values
--
INSERT INTO STAGINGTABLE VALUES( 1,1,1)
INSERT INTO STAGINGTABLE VALUES( 2,1,2)
INSERT INTO STAGINGTABLE VALUES( 3,1,1)
INSERT INTO STAGINGTABLE VALUES( 4,1,2)
INSERT INTO STAGINGTABLE VALUES( 5,2,1)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--
--  First Answer
SELECT * FROM FinalTable
--
--  A second run of your job to insert NEW unique values
--
INSERT INTO STAGINGTABLE VALUES( 6,1,1)
INSERT INTO STAGINGTABLE VALUES( 7,1,2)
INSERT INTO STAGINGTABLE VALUES( 8,3,1)
INSERT INTO STAGINGTABLE VALUES( 9,1,2)
INSERT INTO STAGINGTABLE VALUES( 10,2,4)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--
-- Cumulative answer after including newer values
SELECT * FROM FinalTable
--
DROP TABLE FinalTable
DROP TABLE StagingTable

If this FinalTable gets really big, then there will be some serious overhead.  That is why this time I created indexes on both StagingTable and FinalTable for columns A and B.  This will help the joins run more efficiently.

However, there are also more powerful tools, such as SSIS which could be a big help as the job gets bigger.  The code above is just a simple picture of how to construct the logic.


How to create a correct entity-relationship diagram (ERD) about a video game profits scenario I'm starting to build my application database, and I'm really new to the this world, so I have a lot of doubts about my ERD and if its entity types and relationships are ok.

This is an application that calculates how much me and my friends profit in a video game and saves all the data so we can check it later. I'll explain it a bit so you can understand the ERD.

We want to save this data about every Hunt:

Where the HUNT was (RESPAWN)
How many people we were (1 to 4)
The date of the HUNT
How much everyone WASTE (Knight, Druid, Paladin, Sorcerer and the
Total)
The loot value
The HUNT balance
How many profit we did (each one of us)
The amount of money everyone have to recieve (paying waste + profit
-> TRANSFER)
And if the HUNT is already paid or not

I'm already doing all the math and saving this data in a .txt file for the moment, but I want to take the next step and build a decent database, and to make sure the following diagram is ok:

Are some relationships wrong? Do you think I should add more entity types, properties, etc.?

A question I have is what is the significance of the _EK/_ED columns? I can assume they are separate columns for each person in your party? If that is so, this design would have a lot of NULL values for whenever there isn't 4 people in a hunt, and will outright prevent accounting for a 5 person hunt. I hesitate to suggest a more flexible design until I can better understand what exactly each column is for. I would be happy to expand on what you have if you could post some sample data, such as from a hunt or two, along with a quick explanation of where the information is coming from.

I would also review the datatypes chosen. SMALLINT is fine for key id fields, but it would be best to choose a DATE or DATETIME for your hunt date, instead of varchar. The Paid column has two issues that I see. Including a question mark is not encouraged, instead keep it to alpha-numeric and maybe underscores. If the only two options for that field are Yes/No, then it can be a bit datatype. That way you only store 0 for no, and 1 for yes. That saves a lot of space compared to varchars, and prevents you from having "Yes","YES", and "yes", which are all seen as different depending on your chosen software.


Make to_tsquery return OR of lexemes instead of AND I'm using an dict_xsyn extension in postgres which returns multiple lexemes.

e.g.

CREATE EXTENSION dict_xsyn;

CREATE TEXT SEARCH DICTIONARY names_xsyn (TEMPLATE = 'xsyn_template');

CREATE SEARCH CONFIGURATION english_names ALTER MAPPING FOR asciiword, asciihword, hword_asciipart WITH names_xsyn, simple;

SELECT to_tsquery('english_names', 'ed')
-- returns: 'ed' & 'edward' & 'edwin' & 'edmond'

I would prefer if to_tsquery returned ORed lexemes so that I could match to other variants. e.g.

-- returns: 'ed' | 'edward' | 'edwin' | 'edmond'

I've tried ts_rewrite but that doesn't seem to support swapping logical operators.

--Edit
I'm doing this so that I can match partially matching sets.
e.g.
I want ed to match edward and edwin, but I don't want edward to match edwin. Here are my lexemes:

ed -> ed, edward, edwin

edward -> ed, edward

edwin -> ed, edwin

Using those lexmes to match ed to edward will only work with an or.

"ed, edward" @@ "ed | edward | edwin" will work

"ed, edward" @@ "ed & edward & edwin" won't work

I don't know how to do that exclusively with dict_xsyn, other than by manipulating the output as text and the converting back, but I don't think you want to do that anyway.  I think it would be a performance nightmare.

You probably want to reverse this so that all nicknames get canonicalized down to a single variant, rather than exploded out to all variants.

alter TEXT SEARCH DICTIONARY names_xsyn (KEEPORIG=true, MATCHORIG=true,
  KEEPSYNONYMS=false, MATCHSYNONYMS=true);

But I have no idea why that is not the default, so perhaps I just fundamentally misunderstand the point of that extension.

Both the tsquery and the tsvector would be canonicalized the same way, so a query with 'bobbie' would match a document with 'robert' and vice versa.

But perhaps I just don't understand your end goal.  You can do what you are directly asking for with ts_rewrite, but it is not done by building on top of dict_xsyn.  Is the example involving supernovae & crab not just what you want?


Changing primary key from varchar to int I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key.

So I would like to change my primary key to int. For that I have created another user table and copied the data to that table except the previous primary key value.

Up to this there is no problem.

Now my problem is that I have many tables which are related to the user table through the previous primary key. Now I want to relate the rows of the other tables through to new primary key value.

How to do this?

You do not have to make another table if you are changing the primary key.

The steps I would carry out are:

Add the new integer column that will become the primary key.
Populate it with unique values and make sure you have a unique constraint on it. (It must be unique to be certain it can become primary key)
In each child table add an integer column for the foreign key.
Populate the new foreign key by looking up the old foreign key.

e.g.

tblUser (
 oldID varchar(10),
 newID int, -- The new column added, I'm assuming at this point you have the column populated
 ...
)

tblUserLogin (
 keyUser_oldID varchar(10),
 keyNewID int NULL,
 ...
)

Run the following... I'm work mostly with SQL Server these days, so hopefully this pseudocode gives the right idea. The principle applies to any major table-based database system.

UPDATE tblUserLogin
SET keyNewID = (SELECT newID FROM tblUser WHERE oldID = keyUser_oldID

Add the foreign key between the two new columns.
Make sure that the new primary key is set to auto increment.
Once oldID and keyUser_oldID are no longer referenced anywhere you can drop those columns. But only after you are completely certain!

A few things to consider

That's the general principle, a few things to consider.

This works in simple cases and assumes you can stop people using the database for a while and that any code that uses these tables is ready to be switched to use the new column names.
Subqueries are not the most efficient way, but this is a one-off task and unless you have millions of rows and limited time to make the switch it should be fine.
You didn't say what you are storing in the varchar field? If it is a number (but in varchar format) you could always do UPDATE tblSomeTable SET keyNewID = CAST(keyOldID as int) which would be faster and easier.

Most importantly though...

..you don't mention if the database is used for anything important. Even if it isn't:

Back it up beforehand if you haven't already. If you make a mistake in an update statement you can always reference a backup copy.
Think the steps through, these are structural changes, 30 minutes spent planning could save hours of time later on.
Have a backup plan in case something goes wrong!


Postgresql 9.3: Add Unique constraint using an existing unique index I would like to add a unique constraint to a DB field in one of my production databases. Unfortunately, the following statement is blocking:

ALTER TABLE package ADD CONSTRAINT unique_package_id UNIQUE (package_id);

Reading the relevant part from the PostgreSQL manual:

  Note: Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time. To do that, create the index using CREATE INDEX CONCURRENTLY, and then install it as an official constraint using this syntax. See the example below.

I tried:

CREATE UNIQUE INDEX CONCURRENTLY package_tmp_id_idx ON answer (package_id);

Followed by:

prod_db=> ALTER TABLE package ADD CONSTRAINT
          unique_package_id UNIQUE (package) USING INDEX package_tmp_id_idx;

Which failed with:

ERROR:  syntax error at or near "package_tmp_id_idx"
LINE 1: ...T unique_package_id UNIQUE (answer_id) USING INDEX package_tmp_id_idx...
                                                             ^

I've tried playing with the syntax, but could not get it to work.

What's the right syntax to create a UNIQUE constraint using an existing UNIQUE index?

You don't specify the column for the unique constraint. That's not necessary because the column list is defined through the index:

ALTER TABLE package ADD CONSTRAINT
     unique_package_id UNIQUE USING INDEX package_tmp_id_idx;

There is an example for that in the manual:
http://www.postgresql.org/docs/current/static/sql-altertable.html

You need to scroll down to the end of the page


Vendor Wants to Run MSDB job every 5 minutes for Business Application We have a 3rd party vendor trying to integrate 2 different applications where both DBs reside on our SQL Server instance with 150+ other DBs, and they want to create a MSDB job to "synch" the 2 different applications every 5 minutes (at first they wanted to run it every minute).

My initial hunch is that they should instead do this somehow in the Application tier with a Windows scheduled job, or perhaps even a dreaded trigger (which we typically resort to in situations like this).

I prefer to keep MSDB jobs reserved for DBA tasks as much as possible to reduce the clutter there, and have also run into slow querying of MSDB when viewing job histories with super active jobs like this (which also drown and drop out important job histories of more important things like backup histories).  But then again, maybe my preferences are wrong and I need to make some space for the Application tier in MSDB and roll up my sleeves and fix problems of job histories taking forever to load when I need to retain a lot more history entries to capture the important stuff like backups (or purge the hyper active job entries).

Another issue I have is that now I need to give this vendor "sysadmin" rights instead of only "dbo" rights on only their DBs when they perform their upgrades via their GUI and hope they don't blow up the instance where my mission critical DBs are (one of the downsides of consolidation).

I guess I can put them on another "isolated" instance where we put all the vendors that don't play nice, but then we need reconfigure the Applications to point to the new SQL instance (sigh unfortunately not trivial in this case).

The vendor already pushed back on my concerns talking about how bad triggers are.  So, I "googled" on this a bit and came up empty.  Has anyone seen any link out there "authoritative looking" that this is a bad idea and I can refer them to it?  Or should I embrace their approach?

I don't believe I've ever posted in a sql forum before asking for help, so hopefully my inquiry is properly framed.

EDIT:  We're running SQL Server 2008 Enterprise R2 x64 SP1  (Thanks for pointing out that I forgot to mention version!).  Hmmm, hopefully they don't need to change their MSDB upgrade scripts when we go to a newer version.

Thanks for your time!
Rich

IMO your vendor is actually on the right track.

An application layer job would require him to redo a lot of out-of-the-box SQL Agent features (eg. logic not to run a job that is already running, come up with a security and credentials store solution, integrate job results with error reporting and results tracking in SQL etc etc etc etc). And, most importantly, provide a backup/HA/DC solution for the scheduling. You do not want your disaster recovery story to be 'and after you finish recovering the standby server go create these 50 NT scheduler tasks'. So he is right in pushing back against using the system scheduler, it has nothing of the features and capabilities the Agent has.

He is even more so right in pushing back against triggers. Replacing a periodic job with a synchronous trigger increases request latency, increases cohesion and coupling (think schema changes...), increases downtime risk due to sync issues (trigger error->app error vs. job error->fix and retry later), dramatically increases deadlock problems (due to cross updates between tracked/trackee) and has many many more problems.

The easiest solution is indeed a Agent job, and msdb is by no means reserved for DBAs. A more fancy solution would be to use conversation timers and internal activation, which would have some advantages over Agent jobs, primarily due to containment (everything is inside the app DB, think mirroring failover scenarios), but I can totally understand if your vendor is reluctant to try out something that requires a very specific know-how. BTW I do hope you don't mean one job every 5 min per DB.

As for sysadmin permissions: the name of the game is code signing. You can give any permission to specific procedures, inspected and validated by you, by signing them with your private certificate.


What's a way/ways to return Identity element (that is auto-generated) in a table from a stored procedure? I have tables in my db with auto generated primary keys that I want to get a hold of when inserting new rows from my Java calling code. I'm trying to create stored procedures that handle the insertion and return back the primary key (ie. the auto-gen key) back to the calling code. I'll only be inserting one row at a time.

I have come across the SCOPE_IDENTITY() function but it seems to not be working inside a stored procedure. I most probably am using it in an incorrect way so that may also be the problem.

Here's what I put together from different sources. This doesn't compile and is in no way a correct way to do it. I'm a newbie, so it may seem like someone trying to communicate in a broken language. Please bear with me.

CREATE OR REPLACE PROCEDURE SAVER(IN PARAMETER1 VARCHAR(30), OUT ID_PARM BIGINT)
BEGIN
DECLARE v_PARAM VARCHAR(30);
DECLARE v_ID BIGINT;
SET v_PARAM=PARAMETER1;
INSERT INTO TAB_1 (NAME) VALUES (v_PARAM);
SELECT SCOPE_IDENTITY() INTO v_ID;
SET ID_PARAM=v_ID;
END

Another thing I'm worried about is I read somewhere that Java and DB2 don't support out parameter so I'm not sure how to return back this value.

The Db2 function to obtain the most recent assigned identity value is IDENTITY_VAL_LOCAL().


SELECT COUNT(*) returning incorrect results I am seeing some very strange behavior on PostgreSQL 9.6.3 on RDS Aurora.

I am getting duplicate results from certain queries:

=> select count(id) from foos where id = 'deadbeef';
 count
-------
 2
(1 row)

=> select id from foos where id = 'deadbeef';
            id
--------------------------
 deadbeef
 deadbeef
(2 rows)

=> select id, created_at from foos where id = 'deadbeef';
            id            |         created_at
--------------------------+----------------------------
 deadbeef                 | 2018-01-01 10:00:00.000000
(1 row)

(id values, timestamps, and table names have been obfuscated)

I do not have table inheritance on this table nor any others.

This appears to only affect queries that hit exactly one index on the foos table.

Because this appears to be isolated to individual indexes, I imagine running REINDEX may resolve this issue.

However, I have no idea how many indexes exhibit this behavior.

For instance, here is similar behavior for the same record via a different index:

=> select bar from foos where bar = 'qux';
                  bar
-----------------------------------------
 qux
 qux
(2 rows)

=> select id from foos where bar = 'qux';
            id
--------------------------
 deadbeef
(1 row)

=> select bar, id from foos where bar = 'qux';
                  bar                    |            id
-----------------------------------------+--------------------------
 qux                                     | deadbeef
(1 row)

=> select bar, created_at from foos where bar = 'qux';
                  bar                    |         created_at
-----------------------------------------+----------------------------
 qux                                     | 2018-01-01 10:00:00.000000
(1 row)

Here are the table's relevant indexes:

Indexes:
    "pk_foos" PRIMARY KEY, btree (id)
    "index_foos_on_bar" UNIQUE, btree (bar)

Here's the explain plans of the first couple examples:

=> explain select id from foos where id = 'deadbeef';
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using pk_foos on foos  (cost=0.42..8.44 rows=1 width=25)
   Index Cond: (id = 'deadbeef'::text)
(2 rows)

=> explain select id, created_at from foos where id = 'deadbeef';
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using pk_foos on foos  (cost=0.42..8.44 rows=1 width=33)
   Index Cond: (id = 'deadbeef'::text)
(2 rows)

What is going on here?

Or, how can I figure out what is going on here?

This certainly looks like a corrupt index.  First things first, take a DB snapshot and store it someplace which no one has write access to.

This being RDS Aurora and not the community PostgreSQL, your first (and likely last) recourse would be to talk to AWS support.

You can try to reindex the entire database if you want, but if there is corruption in the indexes there might be corruption in the tables as well.  And of course if you executed any data modifications which failed to update all the rows they should have, or if you modified any data based on business decisions which relied on queries with the wrong results, this type of corruption will be invisible.  Re-indexing (or any continued use of the database) could destroy evidence that could be useful in forensic analysis of the corruption, hence the "first things first" above.

If you set up your data retention so that you can do point-in-time recovery into the far past, you could do tests to see how far back in time any specific instance of corruption appears.


Using `transactions` without `select ... for update`? I have a table of ranges that should never intersect:

id | from | to
--------------
 1 |  3   | 5
 2 |  12  | 14
 3 |  20  | 24
 4 |  27  | 30

I have an algorithm that does the following:

Find gaps (gaps in example would be 6-11, 15-19, 25-26,31-null)
Compute random ranges that do not intersect existig ranges from table
Insert these ranges  (for example insert into table (from,to) values (15,17) and insert into table (from,to) values (31,40))

Now if this algorithm happens at the exact same time it would be possible that the range 15-17 and 15-19 would be inserted and that the ranges of my table intersect.

I know that one usually uses transactions with select ... for update in such a situation. However, I am not really selecting any row that I want to update. Instead, I first compute gaps that do not exist in the database. The computation of the gaps is done with this SQL:

SELECT gap_starts, gap_ends  FROM
   (SELECT (t1.to + 1) as gap_starts,
    (SELECT Min(t3.from) - 1 FROM table t3 WHERE t3.from > t1.to) as gap_ends
    FROM table t1
    WHERE NOT EXISTS (SELECT t2.from FROM table t2 WHERE t2.from = t1.to +1 )
   )

How can I use transaction here to make sure that my inserted rows from step 3 are not intersecting?

You need a transaction and it needs to grab the gap with FOR UPDATE.  The problem is that you don't have an index to use to help with the task.

I suggest that having both a start and end in a row makes the task difficult.  If, instead, there were only a start (or, equivalently, end), you could have an index and use SELECT...FOR UPDATE successfully.

Since you have gaps in the table, you would need a way to represent "real range" versus "unused range".  That would involve another column (which you might already have).

Building such a structure, plus code to grab part of a gap, is found here , where I discuss (as an example) associating ranges of IP-addresses with their 'owner'.  I use a special owner_id to indicate that a range is 'unassigned'.

The code was designed with speed in mind, since searching on start..end performs miserably at scale.


Is SELECT COUNT GROUP BY more efficient than counting a result set? I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank.

From my perspective, the two ways to accomplish this would be:

Pull out the data with a normal SELECT and then run a second query to obtain counts:

SELECT rank, COUNT(id) FROM tablename GROUP BY rank

Pull out the data with a normal SELECT and then iterate twice over the result set I get: once to count the occurrences of each rank, and the second time to actually display the data.

Which way would be more performant? Does it depend on table size? I imagine for large tables the SELECT COUNT would be a tad slower than counting in (PHP, ASP.NET, Java), in particular if there's more than 3 discrete values I want to count.

The recommended approach to improve performance is to:

a) Use the database to do the heavy lifting for counting, computations (where possible)

b) Reduce the number of trips to the database and the amount of data brought back on each trip

In that light, I am not sure what you want to use the rank count for, but the query below will return the data ordered by rank, but a "repeated" column with the number of records for each rank is also included

SELECT t.rank, col1, col2, rankcount
FROM tablename t
  INNER JOIN
     (SELECT rank, COUNT(id) as rankcount FROM tablename t2 GROUP BY rank) AS t3
    ON t.rank = t3.rank
ORDER BY t.rank


Cursor Performance in New Server I bought a new server with spec :
HP ProLiant ML310e Gen8 v2
8 GB Single Memory, Intel Xeon E3-1220v3 3,1 GHz, 500 GB SATA 7.5K RPM, Windows Server 2012 R2 x64, SQL Server Standard 2014 x64, RAID 0

Compared with my notebook :
ASUS X200CA
2 GB Single Memory, Intel Celeron Dual Core 1.5 GHz, 500 GB SATA 7.5K RPM, Windows 7 Ultimate x64, SQL Server Express 2014 x64, AHCI

I just compared this notebook because the different too much compared with my old server (old server faster). I've setting few configuration with my new server and getting a little better from this post SQL Server 2012 slower than 2008

Testing : (5 Tries)

select top 9000 * from faktur_detail

New Server :

CPU time = 20 ms,  elapsed time = 756 ms.
CPU time = 12 ms,  elapsed time = 720 ms.
CPU time = 8 ms,  elapsed time = 840 ms.
CPU time = 14 ms,  elapsed time = 689 ms.
CPU time = 17 ms,  elapsed time = 740 ms.

My Notebook :

CPU time = 250 ms,  elapsed time = 2403 ms.
CPU time = 172 ms,  elapsed time = 2541 ms.
CPU time = 219 ms,  elapsed time = 2848 ms.
CPU time = 140 ms,  elapsed time = 2426 ms.
CPU time = 172 ms,  elapsed time = 2410 ms.

I had try update, insert and other normal commands and showed that new server faster than my notebook. The problem is with Cursor :

Testing : (5 Tries)

declare @ketqty varchar (50),
        @ketqtyjual varchar (50),
        @ketqtybonus varchar (50),
        @kd_barang varchar (10),
        @kd_rec int,
        @qty numeric,
        @qtyjual numeric,
        @qtybonus numeric

  DECLARE @RPREKAPJUALALL_Cursor CURSOR
  SET @RPREKAPJUALALL_Cursor = CURSOR SCROLL DYNAMIC FOR

  select kd_rec, qtyjual , qtyBonus , Qty, kd_barang  from RPREKAPJUALALL where NamaKomputer  ='serverhp'

  OPEN @RPREKAPJUALALL_Cursor
  FETCH NEXT FROM @RPREKAPJUALALL_Cursor INTO @kd_rec, @qtyjual,@qtybonus, @qty, @kd_barang
  WHILE @@FETCH_STATUS = 0
  BEGIN
      EXEC sp_countsatuandetail  @KD_BARANG, @qty , @RESULT = @ketqty   OUTPUT
      EXEC sp_countsatuandetail  @KD_BARANG, @qtyjual  , @RESULT = @ketqtyjual    OUTPUT
      EXEC sp_countsatuandetail  @KD_BARANG, @qtybonus , @RESULT = @ketqtybonus   OUTPUT

      update RPREKAPJUALALL set ketqty =@ketqty , ketqtyjual  =@ketqtyjual ,ketqtybonus =@ketqtybonus where NamaKomputer ='serverhp' and kd_rec =@kd_rec

     FETCH NEXT FROM @RPREKAPJUALALL_Cursor INTO @kd_rec, @qtyjual,@qtybonus, @qty, @kd_barang
   END

  CLOSE @RPREKAPJUALALL_Cursor
  DEALLOCATE @RPREKAPJUALALL_Cursor

New Server :

86 seconds.
93 seconds.
85 seconds.
83 seconds.
81 seconds.

My Notebook :

16 seconds.
12 seconds.
14 seconds.
18 seconds.
16 seconds.

I know cursors is really bad for SQL Server, but I can't switched all cursors into normal update soon, because previous programmers left so many cursors in stored procedure.

What possible caused this problem? I've struggling with this 2 weeks now. I've been read so many articles. I come into conclusion that this type of server is not fit become Database Server, because compared with old server, this server slower about 2,8 times even with SELECT command.

OLD Server :
Intel Core i3, 1TB SATA HDD 7.5K RPM, 16GB Dual Memory, Windows 7 Ultimate x64, SQL Server 2008 Express x86.

For some reason, I must use this new server for now. Please help me with this Cursor problem for a while. This new server so slow for now even showing a week data. I can't imagine how this ended after 1 month, years.

Any Help will be appreciated. Thank you very much.

Im sorry so long not update this question. I solved this case 2 months after this question.

After few times trial and errors, my biggest impact in performance is using separated harddisk between Operation System and Data.

I try to bought new harddisk and look like changing storage system from RAID to AHCI give quite difference in performance for data processing. Well, we do 1 hour backup in Maintenance Job for safety to online cloud service.

I do some changes in BIOS settings which also gives many impacts to performance descried in this pdf http://h20195.www2.hp.com/v2/GetPDF.aspx%2F4AA3-6555ENW.pdf

Im also upgrading memory to 4x8GB

Comparing to my old server its 8 times faster now.

Thank you for helps.


Switch is an extremely efficient way to add or remove data to your table.

You can switch in data, which is the preferred way to upload data in an ETL process. By preparing the data in a staging table (completely unrelated to your actual data) you can prepare it w/o concurrency issues (your ETL won't block reporting) and present the new data in one single switch, thus becoming visible all at once.

You can switch out data, which is the preferred way of removing data past the mandated retention period. This allows to TRUNCATE the data after switch out, efficiently and minimally logged. In many environments this is the only way to delete data due to high concurrency.

Basically any operation for which enabling partitioning makes sense requires SWITCH. If you have already enabled partitioning and still look for an explanation for SWITCH then is is almost certain you did not need partitioning to begin with.


select from mulitple values using ANY in PostgreSQL I am running a query on Postgres table as below:
      Column      |            Type             |     Modifiers
------------------+-----------------------------+--------------------
 status           | character varying(16)

select FROM incoming_requests WHERE COALESCE(TRIM(status), '') IN ('','OK','ERROR');

This seems to working fine but below not
select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY ('','OK','ERROR');

ERROR:  syntax error at or near ","
LINE 1: ...quests WHERE COALESCE(TRIM(status), '') = ANY (" ","OK","ERR...

select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY (" ","OK","ERROR");

Also suggest if there is any difference in performance using ANY or IN

No there is no difference

CREATE TABLE incoming_requests ("status" character varying(16) )

✓

select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY (array['', 'OK', 'ERROR']);

✓

db<>fiddle here


Zip code range lookup (I use sql server 2015)
In my Orders table Postal Code is a varchar.

I want to do a search like this:

SELECT distinct email
  FROM [dbo].[tb_Order]
  where postalCode in (20004,20528,20260,20529,22060,22181,20708)

(with many more zip codes)

I do not want to have to convert the list of codes into ("20004","20528","20708").

What is the best way to handle this?

If you're actually using Sql Server 2016, you can probably use the new STRING_SPLIT command to create a 'table' to join against.  Just put your comma delimited integers in the STRING_SPLIT command.  Here is an example.

declare @tb_Order table (col1 int, postalcode varchar(5))
insert into @tb_Order values(1,'11111')
insert into @tb_Order values(2,'22222')
insert into @tb_Order values(3,'33333')
insert into @tb_Order values(4,'44444')
insert into @tb_Order values(5,'55555')
---------------------------------------
SELECT a.*
FROM @tb_Order a
JOIN STRING_SPLIT('11111,22222,33333,44444,55555',',')
    ON convert(varchar(5),value) = a.postalcode;


MySQL not using index on large WHERE IN clause I am trying to retrieve chunks of records from my table via a generated SELECT WHERE IN statement. If the statement is small, it will use the index, if the statement is large (1000+ IN items) it performs a full table scan. The table is 17 million rows long, so this gets expensive when I only want to look for 10k records.

I have a unique index covering (in this order) date, world, player, city_id. I also have an index for the date and player. The table is also partitioned by world.

Example query:

SELECT * FROM mytable
WHERE
    (date, world, player, city_id)
IN (
    ('2017-05-30',4,'player',15466761),
    ('2017-05-30',4,'player',567599046),
    ('2017-05-30',4,'player2',34567866),
    ('2017-05-30',4,'player',2342467),
    ('2017-05-30',4,'player3',92341268),
    ... etc
)

If the query is small, it will use the index, if the query is large it performs a full table scan. Even adding FORCE INDEX (unique_index) doesn't do anything for the large queries.

Execution plan output:

 "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "20928166.60"
    },
    "table": {
      "table_name": "city_data",
      "partitions": [
        "w3",
        "w4",
        "w5",
        "w6",
        "w7"
      ],
      "access_type": "ALL",
      "possible_keys": [
        "unique_index"
      ],
      "rows_examined_per_scan": 17440138,
      "rows_produced_per_join": 8720069,
      "filtered": "50.00",
      "cost_info": {
        "read_cost": "19184152.80",
        "eval_cost": "1744013.80",
        "prefix_cost": "20928166.60",
        "data_read_per_join": "3G"
      }
    }

SHOW CREATE TABLE for city_data:

CREATE TABLE `city_data` (
   `date` date NOT NULL,
   `world` int(3) NOT NULL,
   `player` varchar(45) NOT NULL,
   `alliance` varchar(45) NOT NULL,
   `score` int(11) NOT NULL,
   `continent` int(11) NOT NULL,
   `castle` tinyint(1) NOT NULL,
   `water` tinyint(1) NOT NULL,
   `temple` tinyint(1) NOT NULL,
   `x_coord` int(11) NOT NULL,
   `y_coord` int(11) NOT NULL,
   `city_name` varchar(45) NOT NULL,
   `city_id` int(20) NOT NULL,
   `entry_id` int(55) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`entry_id`,`world`),
   UNIQUE KEY `unique_index` (`date`,`world`,`player`,`city_id`) USING BTREE,
   KEY `world_index` (`world`) USING BTREE,
   KEY `date_index` (`date`) USING BTREE,
   KEY `player_index` (`player`(8))
 ) ENGINE=MyISAM AUTO_INCREMENT=17999069 DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY LIST (`world`)
 (PARTITION w3 VALUES IN (3) ENGINE = MyISAM,
  PARTITION w4 VALUES IN (4) ENGINE = MyISAM,
  PARTITION w5 VALUES IN (5) ENGINE = MyISAM,
  PARTITION w6 VALUES IN (6) ENGINE = MyISAM,
  PARTITION w7 VALUES IN (7) ENGINE = MyISAM) */

How can I get it to use the index instead of relying on a full table scan?

I have read this post: https://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause

I think there is an issue with large IN clauses and PARITIONing -- Beyond some not-so-large-number, the Optimizer say "punt, I'll just send this query to every partition".  (This may be part of what you are seeing.)

But more than that.  Picking partitions is costly, not cheap.  If you end up with all the partitions, the query will be slower than if you did not have partitioning.  (I recommend removing partitioning.  I have found no use case where BY LIST helps performance.)

Most importantly...  "Tuples" are not optimized.  That is, the Optimizer does a terrible job with WHERE (a,b) IN ((1,2), (3,4)), regardless of the indexes.  Don't use it if you want performance.  (Or you could plead for the optimization feature at bugs.mysql.com).)  (This is the real answer to the question.)

So, what to do?  One approach is to create a temp table with all those tuples.  Then JOIN from that tmp table to the real table and have a composite index (as you have with unique_index) to make the lookups fast.

Some other notes.

The number in INT(55) means nothing.  INT is always 4 bytes, with a range up to a few billion.  world int(3) should probably be TINYINT UNSIGNED (0..255) or SMALLINT UNSIGNED (0..65535).
"Prefix" indexing is rarely beneficial:  KEY player_index (player(8)); remove the (8).
KEY date_index (date) is redundant since there is another index (unique_index) starting with the same column.
Recommend moving to InnoDB before MyISAM is deprecated.


can't install citext extension: could not access file "$libdir/citext": No such file or directory I'm trying to get citext available as a type in a specific database. The Postgres database server is 9.4. I see it on the server in the pg_catalog's pg_available_extensions view.

From the psql console I try to install it and get this error:

create extension citext;

ERROR:  could not access file "$libdir/citext": No such file or directory

Is there a configuration problem? I'm not sure why it would not have the $libdir variable populated.

Did you perhaps install multiple instances of Postgres on the same server? Standard Postgres is not built for that, $libdir is compiled into the program. If so, you could fix your problem by uninstalling all other instances and and re-installing your current Postgres version.

Related:

Could not access file “$libdir/plpgsql”: No such file or directory


High Availability Options for 2008 R2 or 2014 to 2016 "Mirror" for Reporting? I'm a BI Developer investigating options for this scenario.

I'm going to preface this question by saying I don't know the proper terms, so I will put "quotes" around terms that are probably not the official SQL terms.

Let's say I have a SQL 2008 R2 SP1 Standard instance that runs a 3rd party vendor's transactional application.
We are unable to create or modify indexes on this database to performance tune for reporting queries, due to it being a vendor-controlled database (a common problem, yes?).

We want to have a "mirror" (for lack of better/accurate SQL term) of just the DATABASE (not the whole application) from the source instance onto a SQL 2016 Enterprise instance.

The 2016 "mirror" would have INSERTS/UPDATES/DELETES that happen on the 2008 R2 source database "pulse copied" to it on a very frequent basis, say every, 1 minute (could be 5 minutes, could be 10, don't know what's feasible yet).

The 2016 "mirror" would be used for reporting purposes, that we could tune and hit without negatively impacting the source database with a bunch (potentially up to 2,000) of queries simultaneously.

The "mirror" would therefore become the data source for reports (no load balancing from mirror versus source is necessary), instead of the live database.

Failing Over from 2008 R2 [source] to 2016 [target] is irrelevant, and is not the goal.

The goal is a "copy" or "mirror" of the source database to a target "mirror" that is 1-10 minutes behind the live database, and that "mirror" is to be used for reporting (SSRS, SSAS Tabular, Power BI, etc.) and that allows us the freedom to performance tune via indexes, et cetera that would otherwise not be allowed on the vendor's database.

Given this info, does the "mirror" must be READ-WRITE for the index creates / alters / rebuilds / reorgs, or can it be READ ONLY?

What are our valid options for this mixed SQL Server version scenario?
Log Shipping?
Replication?
Database Mirroring?
Availability Groups?
Others??

Does the answer change if the source system gets upgraded from SQL 2008 R2 Standard to SQL 2014 Enterprise?

I consider my question to be distinct and different from How to accomplish SQL Server 2012+2014 high availability since the requirements are different, and thus is not a duplicate.

Your only valid options would be transactional replication. No changes since your downstream version is 2008 R2. When dealing with features like AGs, it generally is same MAJOR version of SQL Server.


What would be a good name for a job schedule? I have encountered many different names for job schedules in SQL Server, ranging from the job name where it is used to a short recap of the schedule itself. I would like to give a clear, descriptive and consistent name to new job schedules, so it could be re-used easily and it's easy to see what kind of schedule it is.

Examples of encountered job schedule names:

Run every night
Every 4 weeks on Monday
Schedule job X
21.00hr
DAILY (which then runs only 6 days a week)

I find it hard to think of a good way to name them. So: what would be smart, consistent names to give to a job schedule? How do you name them? Why?

It seems to me that using a job name in the schedule name prevents it from being re-used, so I've chosen to focus on a shorthand for the schedule contents. I'm now using the following naming convention:

times per week - days of the week - run time

So for the examples in the question the new names would become:

Run every night -> 7xPW - MTWTFSS - 22.00
Every 4 weeks on Monday -> 0xPW - Mxxxxxx - 6.15
21.00hr -> 5xPW - MTxTFSx - 21.00
DAILY (which then runs only 6 days a week) -> 6xPW - MTWTFSx - 0.00

This seems to work for now because we have lots of weekly and once-a-day jobs. I'm not utterly happy with the 0xPW for schedules that occur every 4 weeks or monthly (what are we gonna do with the job that runs every 4th day of every month?), but it's not a big deal and the 0xPW at least tells us to look to the description for more info.

The daily jobs that run every xx minutes are not straightforward to name in the above manner - although 2016xPW - MTWTFSS for a once-every-5-minutes schedule is pretty telling.


Performance issues since migrating from SQL Server 2012R2 to 2019 I've migrated my database from a SQL Server 2012R2 to a new SQL Server 2019. I've just backupped the database and restored it on new sql server. After that I've imported and updated some additional data (quotes) with IMPORT, INSERT, UPDATE or MERGE. And in between some testers have used the system for some testing. Now performance is on some actions slower than on old server.

First I've done some index tuning. Here are some questions regarding this topic:
Questions on updating statistics and index maintenance jobs
Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? [closed]

Second I've done the same import done on our test environemnt with SQL Server 2012R2. And there I don't have the performance issue.

Query
(deleted)

Execution Plans

[Execution plan on Server 2012R2]
(deleted)
[Execution plan on Server 2019]
(deleted)

@user1673665 ok, this definitelly looks like cardinality issue, prolly statistics.
You have SQL Server expecting few row, ie for scMat_MaterialText 4, but you get 62000. Thats way off.
Try to run a select on this table, then review estimates vs actual rows read. And try to update statistics. I would say thats the culprint


Transaction with lock on a specific table NOTE : If there is similar question, please refer me to it.

I have two tables Request & Receipt.

I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. I need the transaction be in a Stored Procedure.

Is it impossible? How?

Why should Receipt remain locked?
Cause an internal request for insert or delete Receipt will be unable to manipulate it! Note that I need ReceiptId's be consecutive per each Request from its start, to its finish!

Use a key table to allocate receipt numbers in a way that is concurrency friendly, and still guarantees receipt numbers will never be re-used, and will (almost) never contain gaps.

I've created a minimally complete verifiable example that you could use as a basis for learning about concurrency that should set you on a good path.

Do this work in tempdb so we don't kill anything important in your actual work:

USE tempdb;

If the objects we're creating already exist, then we'll delete them first.
This allows us to easily modify the code below and re-run it multiple times
for testing.

IF OBJECT_ID(N'dbo.Requests', N'U') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.CreateRequests;
    DROP TABLE dbo.Keys;
    DROP TABLE dbo.Receipts;
    DROP TABLE dbo.Requests;
END
GO

Requests table - add columns as required:

CREATE TABLE dbo.Requests
(
    RequestID int NOT NULL IDENTITY(1,1)
        CONSTRAINT Requests_PK
        PRIMARY KEY CLUSTERED
    , RequestDate datetime NOT NULL
    , Notes nvarchar(100) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Receipts table.  ReceiptID is generated by the system, ReceiptNum is the number we'll show to users.

CREATE TABLE dbo.Receipts
(
    ReceiptID int NOT NULL IDENTITY(1,1)
        CONSTRAINT Receipt_PK
        PRIMARY KEY CLUSTERED
    , RequestID int NOT NULL
        CONSTRAINT Receipt_Request_FK
        FOREIGN KEY
        REFERENCES dbo.Requests(RequestID)
    , ReceiptNum int NOT NULL
    , ReceiptCreatedOn datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

The Key table, where we store the most recently allocated ReceiptNum value:

CREATE TABLE dbo.Keys
(
    KeyName varchar(100) NOT NULL
        CONSTRAINT Keys_PK
        PRIMARY KEY CLUSTERED
    , LastUsedKeyValue int NOT NULL
) ON [PRIMARY];
GO

The CreateRequests stored procedure:

CREATE PROCEDURE dbo.CreateRequests
(
    @NumReceipts int = 1 /* default to 1 receipt */
    , @RequestNote nvarchar(100) = N''
    , @RequestID int OUTPUT /* this will contain the newly created RequestID */
)
AS
BEGIN
    SET NOCOUNT ON; /* prevent stored procedure from displaying "x records affected" */
    DECLARE @msg nvarchar(1000) = N''; /* used to display error messages */
    DECLARE @ret int = 0; /* stored procedure return value */
    DECLARE @LastUsedKey int;
    DECLARE @keys TABLE
    (
        KeyValue int NOT NULL
    );
    DECLARE @req TABLE
    (
        RequestID int NOT NULL PRIMARY KEY
    );

    IF @NumReceipts > 0 AND @NumReceipts <= 10000 /* 10,000 is the arbitrarily chosen
        maximum number of receipts this stored proc supports */
    BEGIN
        /* Atomically allocate @NumReceipts keys to use for Receipt Numbers */
        UPDATE dbo.Keys
        SET LastUsedKeyValue = LastUsedKeyValue + @NumReceipts
        OUTPUT inserted.LastUsedKeyValue INTO @keys (KeyValue)
        WHERE KeyName = 'ReceiptNum';
        SELECT @LastUsedKey = k.KeyValue
        FROM @keys k;
        IF @LastUsedKey IS NULL /* the first time we run this code, we insert a new Key */
        BEGIN
            INSERT INTO dbo.Keys (KeyName, LastUsedKeyValue)
            VALUES ('ReceiptNum', @NumReceipts)
            SET @LastUsedKey = @NumReceipts;
        END

        /* Create a new Request row, outputting the inserted RequestID
            into a temporary table for use in the Receipts table */
        INSERT INTO dbo.Requests (RequestDate, Notes)
        OUTPUT inserted.RequestID INTO @req(RequestID)
        SELECT GETDATE(), @RequestNote;

        /* Create the new Receipts rows */
        ;WITH src AS ( /* this is configured to support a maximum of 10,000 new receipts */
            SELECT TOP(@NumReceipts)
                ReceiptNum = (v5.Num + (v4.Num * 10) + (v3.Num * 100) + (v2.Num * 1000)
                     + (v1.Num * 10000)) + 1 + (@LastUsedKey - @NumReceipts)
            FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(Num)
            ORDER BY v1.Num
                , v2.Num
                , v3.Num
                , v4.Num
                , v5.Num
        )
        INSERT INTO dbo.Receipts (RequestID, ReceiptNum, ReceiptCreatedOn)
        SELECT r.RequestID, ReceiptNum, GETDATE()
        FROM @req r
            CROSS JOIN src
        ORDER BY src.ReceiptNum;

        /* we'll return the newly created RequestID in @RequestID */
        SELECT @RequestID = r.RequestID
        FROM @req r;

        /* -1 indicates success */
        SET @ret = -1;
    END
    ELSE
    BEGIN
        SET @msg = N'Invalid number of receipts requested.';
        RAISERROR (@msg, 14, 1);
        SET @ret = 1;
    END
    RETURN @ret;
END
GO

Here, we add three different requests, with a varying number of receipts to show the functionality of the stored procedure:

DECLARE @ReqID int;
DECLARE @ReturnValue int;
EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 19
    , @RequestNote = N'Test Request #1'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     1 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║         1 ║         1 ║          1 ║ 2017-06-01 09:04:37.107 ║
║         2 ║         1 ║          2 ║ 2017-06-01 09:04:37.107 ║
║         3 ║         1 ║          3 ║ 2017-06-01 09:04:37.107 ║
║         4 ║         1 ║          4 ║ 2017-06-01 09:04:37.107 ║
║         5 ║         1 ║          5 ║ 2017-06-01 09:04:37.107 ║
║         6 ║         1 ║          6 ║ 2017-06-01 09:04:37.107 ║
║         7 ║         1 ║          7 ║ 2017-06-01 09:04:37.107 ║
║         8 ║         1 ║          8 ║ 2017-06-01 09:04:37.107 ║
║         9 ║         1 ║          9 ║ 2017-06-01 09:04:37.107 ║
║        10 ║         1 ║         10 ║ 2017-06-01 09:04:37.107 ║
║        11 ║         1 ║         11 ║ 2017-06-01 09:04:37.107 ║
║        12 ║         1 ║         12 ║ 2017-06-01 09:04:37.107 ║
║        13 ║         1 ║         13 ║ 2017-06-01 09:04:37.107 ║
║        14 ║         1 ║         14 ║ 2017-06-01 09:04:37.107 ║
║        15 ║         1 ║         15 ║ 2017-06-01 09:04:37.107 ║
║        16 ║         1 ║         16 ║ 2017-06-01 09:04:37.107 ║
║        17 ║         1 ║         17 ║ 2017-06-01 09:04:37.107 ║
║        18 ║         1 ║         18 ║ 2017-06-01 09:04:37.107 ║
║        19 ║         1 ║         19 ║ 2017-06-01 09:04:37.107 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

Run #2:

EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 3
    , @RequestNote = N'Test Request #2'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     2 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║        20 ║         2 ║         20 ║ 2017-06-01 09:04:37.160 ║
║        21 ║         2 ║         21 ║ 2017-06-01 09:04:37.160 ║
║        22 ║         2 ║         22 ║ 2017-06-01 09:04:37.160 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

Run #3:

EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 9999
    , @RequestNote = N'Test Request #3'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     3 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║        23 ║         3 ║         23 ║ 2017-06-01 09:04:37.210 ║
║        24 ║         3 ║         24 ║ 2017-06-01 09:04:37.210 ║
║        25 ║         3 ║         25 ║ 2017-06-01 09:04:37.210 ║
║        26 ║         3 ║         26 ║ 2017-06-01 09:04:37.210 ║
.....
║     10021 ║         3 ║      10021 ║ 2017-06-01 09:04:37.210 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

To verify concurrency, and that the allocation of Receipt Numbers is reliable, I ran the following code simultaneously in three separate SSMS windows, creating nearly 30,000 receipts:

DECLARE @note nvarchar(100);
DECLARE @ReqID int;
DECLARE @ReturnValue int;
DECLARE @loop int = 1;
DECLARE @maxloops int = 1000;
WHILE @loop < @maxloops
BEGIN
    SET @note = N'Concurrency request loop ' + CONVERT(nvarchar(10), @loop);
    EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 5
        , @RequestNote = @note
        , @RequestID = @ReqID OUT;
    SET @loop += 1;
END;


Job waiting for resources - is it better to kill it or let it wait Our server is very busy sometimes and I can see my jobs are waiting for resources. Is it better to kill it and start it after e.g. one hour or let it wait until it finishes?
Right now there is a rule that kills the job if it has not finished within a specified amount of time and then it tries this four times. I think it is best to let it wait, because when it gets killed it looses its place in the "line" and when it starts again it has to go to the end of the queue.
Extra info: the jobs are from ETL with analytical queries and the big jobs have many joins on large tables. Tableau also sends queries directly to the server...
Thanks

The best option is to resolve the resource contention when possible. Based on your question, you seem to be seeing waits for memory grants. In the comments, you stated that your server has 4 TB of memory. By default, SQL Server will allow a single query to get a grant up to 750 GB. Are there any queries that really need that much memory? Keep the following in mind:

By design, SQL Server asks for a generous amount of memory for its memory grants.
Queries may ask for extremely large grants if the queries have poor cardinality estimates. I've seen queries ask for more than a TB of memory when less than 20 GB was sufficient.
Even if a query gets a huge memory grant it may still spill to tempdb. This is because a query's memory grant need to be shared between different operators and the split is determined before the query runs.

I suggest looking into the queries that request the most memory and using Resource Governor or the MAX_GRANT_PERCENT hint to reduce the size of the largest grants. This is a trade off. You may see worse performance for some queries if they start spilling more data to tempdb. However, you may be able to reduce the grants without degrading performance at all. It all depends on the queries that run on your system and how many are running at a time.


does using optimizer_mode=first_rows_n for application that using Oracle database is a best practice? So I'm working in application that involving display of data in table/grid. And the grid will invoke paging query to oracle. So does by tuning my application to get the best query cost with optimizer_mode=first_rows_10 is always good and consider best practice with this kind paging search with html table or any table based approach application.

If you're not ready to make this a system-wide setting, you could try it in an isolated fashion by using a query hint:

SELECT /*+ FIRST_ROWS(10) */
       first_column,
       second_column
  FROM my_table_or_view

This is less risky, since you don't need to consider the effects on the entire system.


Maintenance plan don't stop & don't backup all databases I have a server with 100 databases,
I have a maintenance plan (MP) for backups.
I noticed that the maintenance plan don't backup all databases when I'm looking at the backup folder destination I can see that it stops after 50 databases +-.
The job is still in running mode - it doesn't stop.

So in one hand, I can see that there are no new backups files (after the first 50) and on the other, the job is not stopping.

If the job is still running, check what command it's currently executing with Adam Machanic's excellent free stored procedure, sp_WhoIsActive. After installing it (typically in the master database), you can run sp_WhoIsActive to list the running queries and see what command they're executing right now.

There's even a Percent Complete column that gets populated for backups and restores.

You might have a particularly large database (or log file) that's just in the midst of getting backed up.

For further followups & clarification, try taking a picture or copy/pasting the sp_WhoIsActive results in, showing what the maintenance plan's currently doing.


Limiting Common Table Recursion I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.

 SELECT id,MK, pid,AFI
   FROM M A
  WHERE LEVEL <= 2 -- limiting iterations here
  START WITH pid = 1
CONNECT BY Pid = PRIOR id;

We have used recursive common expression table but we are unable to restrict the number of iterations to 2 (LEVEL <= 2). We don't want to set the system variables like cte_max_recursion_depth. How else can we limit recursion depth?

Please note while @Kondybas' answer will produce expected results on correct data sets it will NOT restrict the number of iterations.
All rows will still be read but only 2 of them will be returned. That means:

Drop in performance
You will NOT avoid infinite loops that way!

If there is a loop in the tree of pid-id links in the table data due to some mistake such query can last forever (which might event corrupt the entire server by flooding the disk with endless temporary result data - checked in MariaDB 10.3.15.)
So you have to put a restricting condition inside the WITH clause as follows:
WITH RECURSIVE cte AS
  ( SELECT 1 AS lvl -- UDV initialization
         , A.id
         , A.MK
         , A.pid
         , A.AFI -- initial portion
      FROM M AS A
     WHERE A.pid = 1

     UNION

    SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
         , z.id
         , z.MK
         , z.pid
         , z.AFI -- recursive portion
      FROM cte -- referring to the already gathered recursion
      JOIN M AS z ON z.pid = cte.id
      WHERE cte.lvl < 2        -- *** the correct condition ***
  )
SELECT *
  FROM cte
;

P.S. I guess it is also a good idea to set max_recursive_iterations to some reachable value like 10000 to prevent MariaDB from getting unresponsive.


Use of NVL not considering Indexes Using NVl in a query didn't considering indexes . But after creating below index it using newly created index.Please enlighten me.

CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1));

CREATE TABLE TEST_TABLE (
ID NUMBER,
NAME VARCHAR2(20)
) ;

INSERT INTO TEST_TABLE VALUES(1,'A');
INSERT INTO TEST_TABLE VALUES(2,'A');
INSERT INTO TEST_TABLE VALUES(3,'A');
INSERT INTO TEST_TABLE VALUES(4,'A');
INSERT INTO TEST_TABLE VALUES(5,'A');
INSERT INTO TEST_TABLE VALUES(6,'A');
INSERT INTO TEST_TABLE VALUES(7,'A');
INSERT INTO TEST_TABLE VALUES(8,'A');
INSERT INTO TEST_TABLE VALUES(9,'A');
INSERT INTO TEST_TABLE VALUES(10,'A');

SELECT * FROM TEST_TABLE
WHERE NVL(ID,1) = 1;

CREATE INDEX IDX_1 ON TEST_TABLE (ID);

CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1));

Thanks,

Whenever you use functions on columns that are associated to indexes, the engine MIGHT NOT use the indexes, due to the computation it needs on top of the column values. If you create a function -based index (like you made), it's being created with the already computed values from the function. The caveat with this, is that this index is now usable only when the column is also being used with the function.


Postgres complex insert or update with 2 unique constraints I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,
external_id: string
disabled_by: string

Additional notes:

All columns have the not null constraint.
unique constraint ('external_id_constraint_1') (external_id, disabled_by)
unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.
update does not have the ability to do the insert if there is nothing to update
update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.

You can combine multiple data-modifying statements by putting them into WITHs:

WITH constraint_1 AS (
  SELECT 1
  FROM external_ids
  WHERE external_id = $2
    AND disabled_by = ''
),
constraint_2 AS (
  UPDATE external_ids
  SET disabled_by = ''
  WHERE user_id     = $1
    AND external_id = $2
    AND disabled_by <> ''
  RETURNING 1
)
INSERT INTO external_ids (user_id, external_id, disabled_by)
SELECT $1, $2, ''
WHERE NOT EXISTS (SELECT * FROM constraint_1)
  AND NOT EXISTS (SELECT * FROM constraint_2);

Like the equivalent stored procedure, this might have races with concurrent updates, depending on the transaction isolation level.


Efficient way to see what pattern matches a single word I'm creating a function to find out what search pattern would best fit a single string.  The string to match will be compared against a table of what could be possibly hundreds of possible patterns to see if it matches any of them.

I've got a not-so-elegant function working right now that will read all patterns into a memory table and iterate though them to see if each matches.  It works but makes me sick to my stomach to think of using this in a production environment, especially if the pattern table grows to thousands of patterns.

Example string to match: 'Master'

Possible patterns:

m%r
abc%
xyz%
a%bcd

The outcome would be a match to pattern #1.

In the scenario, I need to be able to match potential part numbers, brands, and other possibly misspelled phrases.  I'm incorporating Double Metaphone but when it comes to other items that only a pattern will catch, I need to know what pattern was the match among many. The "StringToSearch" would only return 1 matched pattern.

Does anyone have theory or other code types that can help me with this reverse pattern matching process?

Without knowing anything about what your actual scenario looks like, I'd perhaps do something like:

USE tempdb;

IF OBJECT_ID(N'dbo.Strings', N'U') IS NOT NULL
DROP TABLE dbo.Strings;
CREATE TABLE dbo.Strings
(
    TargetString varchar(46) NOT NULL
);

IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
CREATE TABLE dbo.Matches
(
    PossibleMatch varchar(46) NOT NULL
);

INSERT INTO dbo.Strings(TargetString)
VALUES ('Master');

INSERT INTO dbo.Matches(PossibleMatch)
VALUES ('m%r')
    , ('abc%')
    , ('zyx%')
    , ('a%bcd')

SELECT *
FROM dbo.Strings s
    INNER JOIN dbo.Matches m ON s.TargetString LIKE m.PossibleMatch

╔══════════════╦═══════════════╗
║ TargetString ║ PossibleMatch ║
╠══════════════╬═══════════════╣
║ Master       ║ m%r           ║
╚══════════════╩═══════════════╝

You'll get a better set of answers if you more accurately describe exactly what your scenario is by explaining how the data is designed, and what you exact design goals are.

To see if this methodology is effective enough, I've extended the MCVE above by adding a couple of indexes and a bunch of data:

The tables now have clustered indexes:

IF OBJECT_ID(N'dbo.Strings', N'U') IS NOT NULL
DROP TABLE dbo.Strings;
CREATE TABLE dbo.Strings
(
    TargetString varchar(46) NOT NULL
        PRIMARY KEY CLUSTERED
);

IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
CREATE TABLE dbo.Matches
(
    PossibleMatch varchar(46) NOT NULL
        PRIMARY KEY CLUSTERED
);

This inserts 50,000 words into the Strings table:

;WITH src AS (
    SELECT v.val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(val)
)
, src26 AS
(
    SELECT TOP(26) Num = (v.val * 10) + v1.val
    FROM src v
        CROSS JOIN src v1
    ORDER BY v.val, v1.val
)
, words AS
(
    SELECT c1 = CHAR(65 + s1.num)
        , c2 = CHAR(65 + s2.num)
        , c3 = CHAR(65 + s3.num)
        , c4 = CHAR(65 + s4.num)
        , c5 = CHAR(65 + s5.num)
    FROM src26 s5
        CROSS JOIN src26 s4
        CROSS JOIN src26 s3
        CROSS JOIN src26 s2
        CROSS JOIN src26 s1
)
INSERT INTO dbo.Strings(TargetString)
SELECT TOP(50000) words.c1
    + words.c2
    + words.c3
    + words.c4
    + words.c5
FROM words
ORDER BY CRYPT_GEN_RANDOM(5);

A sample of the rows from that table:

╔══════════════╗
║ TargetString ║
╠══════════════╣
║ UKMBL        ║
║ ASOCG        ║
║ XWACG        ║
║ NHRXQ        ║
║ LFMBR        ║
║ EQELO        ║
║ PRHCM        ║
║ IIFPD        ║
║ OCLJQ        ║
║ NJBMB        ║
╚══════════════╝

Next, we'll create 1,000,000 rows in the Matches table with wildcards:

;WITH src AS (
    SELECT v.val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(val)
)
, src26 AS
(
    SELECT TOP(26) Num = (v.val * 10) + v1.val
    FROM src v
        CROSS JOIN src v1
    ORDER BY v.val, v1.val
)
, words AS
(
    SELECT c1 = CHAR(65 + s1.num)
        , c2 = CHAR(65 + s2.num)
        , c3 = CHAR(65 + s3.num)
        , c4 = CHAR(65 + s4.num)
        , c5 = CHAR(65 + s5.num)
    FROM src26 s5
        CROSS JOIN src26 s4
        CROSS JOIN src26 s3
        CROSS JOIN src26 s2
        CROSS JOIN src26 s1
)
INSERT INTO dbo.Matches(PossibleMatch)
SELECT TOP(1000000) REPLACE(words.c1, 'A', '_')
    + REPLACE(words.c2, 'E', '_')
    + REPLACE(words.c3, 'I', '_')
    + REPLACE(words.c4, 'O', '_')
    + REPLACE(words.c5, 'U', '%')
FROM words
WHERE NOT (words.c1 = 'A' AND words.c2 = 'E' AND words.c3 = 'I' AND words.c4 ='O' AND words.c5 <> 'U');

SELECT TotalMatches = (SELECT COUNT(1) FROM dbo.Matches)
    , TotalStrings = (SELECT COUNT(1) FROM dbo.Strings)

Here's a sample of those rows:

╔═══════════════╗
║ PossibleMatch ║
╠═══════════════╣
║ LDBFB         ║
║ IWLMB         ║
║ FNBUA         ║
║ _ZEVC         ║
║ BSZDB         ║
║ TTRIB         ║
║ XVLNA         ║
║ VLPDB         ║
║ UI_IA         ║
║ FNLCB         ║
╚═══════════════╝

As you can see from the sample above, some of the rows have a T-SQL single-character wildcard, _, and some rows don't have wildcards.

Now, we'll run some code to see if we can find a row from Matches that matches up to a row in Strings:

SELECT *
FROM dbo.Strings s
    LEFT JOIN dbo.Matches m ON s.TargetString LIKE m.PossibleMatch
WHERE s.TargetString = (
    SELECT TOP(1) Strings.TargetString
    FROM dbo.Strings
    ORDER BY CRYPT_GEN_RANDOM(5)
    )

The WHERE clause randomly chooses a word from the Strings table.  You may need to run this several times before you get a word with matching values in the Matches table.

Looking up any given word in the Strings table, with associated Matches:

SELECT *
FROM dbo.Strings s
    LEFT JOIN dbo.Matches m ON s.TargetString LIKE m.PossibleMatch
WHERE s.TargetString = 'JRZPC'

The plan for this query:

On my system, this query executes in ~100 milliseconds.

The results of that query:

╔══════════════╦═══════════════╗
║ TargetString ║ PossibleMatch ║
╠══════════════╬═══════════════╣
║ JRZPC        ║ ___PC         ║
║ JRZPC        ║ __Z_C         ║
║ JRZPC        ║ __ZPC         ║
║ JRZPC        ║ _R__C         ║
║ JRZPC        ║ _R_PC         ║
║ JRZPC        ║ _RZ_C         ║
║ JRZPC        ║ _RZPC         ║
╚══════════════╩═══════════════╝

If you were going to run hundreds or thousands of these queries per minute, you might want to re-think the strategy, but that's putting the horse before the cart.

If you want to implement this as a function, I'd consider this:

CREATE FUNCTION dbo.FindMatches
(
    @TargetString varchar(46)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
(
    SELECT m.PossibleMatch
    FROM dbo.Matches m
    WHERE @TargetString LIKE m.PossibleMatch
);
GO

That code creates a schema-bound table-valued-function that gets "inlined" with the target query, and is about as efficient as you can get for this type of thing.

This would be an example of how you'd call the function:

SELECT s.TargetString
    , fm.PossibleMatch
FROM dbo.Strings s
    CROSS APPLY dbo.FindMatches(s.TargetString) fm
WHERE s.TargetString = 'AMLAA'


What could cause problematic queries to disappear from QueryStore? I have a log table of "Execution Timeout Expired" error messages. The timeouts occurred at a certain time (say 11/17, 1:23 pm). A couple weeks ago, I was able to go into Query Store, "Top Resource Consuming Queries", click on "Configure", go to the Custom time setting, and pick that exact minute where the timeouts occurred. I found 1 procedure and 1 function that were consuming lots of resources - a very clear spike.  I can't remember exactly what it was (CPU, Logical reads) but I think it may have been all of them.
And there seemed to be an obvious problem that I could fix (basically the function was in the join clause, and the SP could be re-written to avoid the function).
But now I'm trying to validate this finding, that the specific sp and function were the problem. And when I do, in Query Store, what I believe is the exact same thing I did a few weeks ago, the sp and function doesn't show up at all.
Is there something obvious I'm missing? Could something have been dropped from Query Store? Other stored procedures and functions are still showing up for that time period, just not the ones that showed as spiked when I checked last time.

QueryStore has a variety of settings for configuration but you need to be aware that there is a cleanup job that removes things that are too old or exceed one of the other configuration thresholds - read more about them here:-
https://www.sqlskills.com/blogs/erin/query-store-settings/


How to get the basetype of a domain in pg_type? Basically,

if typbasetype = 0, the oid gives the base type.
Else
   recusively look at typbasetype joined with pg_type oid till we end up at the basetype.

How can this be done?

You can use format_type() to get the correct base data type.

Something along the lines of:

SELECT t.typname as domain_name,
       pg_catalog.format_type(t.typbasetype, t.typtypmod) as data_type,
       pg_catalog.pg_get_constraintdef(c.oid, true) as constraint_definition
FROM pg_catalog.pg_type t
  LEFT JOIN pg_catalog.pg_constraint c ON t.oid = c.contypid
WHERE t.typtype = 'd'
  and t.typname = 'your_domain_name'


postgres authentication external I have to implement some safety authentication rules on postgresql DB  with a windows server. For example, rules may be "users not used for a period longer than three months will be locked" or "postgres enforces account lockout after a set number of failed login attempts."

Using authentication "standard" ( md5 on pg_hba.conf) I think is not possible.  I think that with PAM it would work. However,  PAM does not exist on windows server. Any ideas?

Postgres has an option "valid until" that specifies user's password expiry Date&time. but, I believe, what you are asking can be done as an application feature not the Database feature.


Show master status returns empty set of rows Hi currently I am working on implementing data replication on MYSQL for my project. While working on master file configurations on windows when I am trying to check the status of master, it returns empty set. It means it is not locating mysql-bin file and its position. I have made changes in ini file but the changes are not getting reflected. Can you please advise how I can enable binary loggings in windows configuration Please review and advise

Currently it shows like this

It should be like this

There are many variables for MySQL that are not dynamic (cannot be changed inflight) and require a service restart. This includes some of the binary log specific variables. Consult the MySQL documentation for further info on each variables as and when you discover you need to update their values.

Restarting the process from the service manager should allow these new settings to take hold.


How to hide rows in SSRS report? I'm trying to figure out, how I can hide those rows in my report where Total Allocation and Total Cost IN BOTH COLUMNS TOGETHER are 0 in SSRS 2008.

For example:

Total Allocation    Total Actual Cost
0                     0                <---- hide
100,00                0                <---- don't hide
0                     50,0000          <---- don't hide

Here is the screenshot:

Thank you

Unless I'm missing some nuance, you will simply set the Row Visibility based on the value.

Right click on the detail line and select Row Visiblity...

In the window that pops up, select Show or hide based on an expression

Within that expression, you'll use logic like

=Fields!Total_Allocation.Value = 0 And Fields!Total_Actual_Cost.Value = 0


SQL 2000: change collation of model database only- not master One of our production SQL 2000 server has windows collation for master and msdb but SQl collation for model and tempdb.
We are trying to have the same setup on a test environment which at the moment has windows collation for all system databases
So, I got a copy of the model database with SQL collation from another server and did the following:

restored it as another user database (model2).
shut down the SQL service
moved the model database files (model.mdf and modellog.ldf) to another location
Renamed model2 database files as model.mdf and modellog.ldf so that SQL will think that this is the existing database.

It did change the collation but when I right click on model database and click on properties...it shows the following error:

  "Cannot resolve collation conflict for equal to operation"

Which it doesn't show in the existing production server.

Please help
Regards

Manjot

I was restoring database using management studio and not enterprise manager.
I could restore model using enterprise manager without any problem.


How To return null value if no records present in select query I am querying in DB for last 72 records in Db
in my Db it may happens all data will not be present
for that I want to add dummy row in which I will add rank and adding null value to remaining data.

Query:

select (@rownum := @rownum + 1) AS rank,
date, day(date), hour(date),Sum(Ifnull(impressions, 0)) AS
total_Impressions,
ROUND(Sum(Ifnull(clicks, 0)) * 100.0 / Sum(Ifnull(impressions, 0)),10)
AS CTR_percentage,
ROUND(Sum(Ifnull(dv_viewed, 0)) * 100.0 / Sum(Ifnull(dv_measured,
0)),1)    AS Viewability_percentage,
ROUND(Sum(Ifnull(dv_measured, 0)) * 100.0 / Sum(Ifnull(dv_impression,
0)),1) AS Measurability_percentage
FROM (SELECT @rownum := 0) t,rhino.delivery_snapshot_summary where
adgroup_id IN(SELECT id FROM ad_group where campaign_id = 2467 AND
bundle = 0)
and date <='2018-11-26 23:59:59' and date >'2018-11-22 23:59:59'  group
by day(date), hour(date) order by date DESC limit 72;

and I'm getting
following response :

I want total 72 rows with all null data in response.

Database : Mysql
using MySQL workbench 6.3.6
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Add 72 records with all fields NULL, get first 72 records (you sort in DESC, so NULLs will be last).

( SELECT .....
  ........
  order by date DESC limit 72 )
UNION ALL
( SELECT x.*
  FROM ( SELECT null, null, ... null ) x,
       ( SELECT 1 UNION SELECT 2 ... UNION SELECT 72 ) y
)
order by date DESC
/* , COALESCE('all another resultset fields list') IS NULL */
limit 72

COALESCE needed if date field can be NULL.

PS. Pseudotable with numbers from 1 to 72 can be generated using 2-3-4 small tables. Or in recursive CTE if DBMS allows...

PPS. In recursive CTE you can generate the required number of records up to 72 total records at once.


INSERT INTO selected columns creates new column from JOIN I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the query also creates additional columns in the existing tables to (what appears to be) accommodation for the JOIN... ON statements. Simplified example follows:

TableA

UID   Text_GUID
1     abc123
2     abc345
3     abc456
4     abc678

TableB

Text_GUID   Col_A   Col_B   Col_C   Col_D
abc123       100     200     300     400
abc345       111     211     311     411
abc456       122     222     322     422
abc678       133     233     333     433

Execute the following query:

INSERT INTO [TargetTable] (UID, Col_A, Col_B, Col_C, Col_D)
SELECT A.UID, B.Col_A, B.Col_B, B.Col_C, B.Col_D
FROM [TableA] AS A
INNER JOIN [TableB] AS B
ON A.Text_GUID = B.Text_GUID;

I expect this:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D
1       100     200     300     400
2       111     211     311     411
3       122     222     322     422
4       133     233     333     433

I get this:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D   Text_GUID
1       100     200     300     400     NULL
2       111     211     311     411     NULL
3       122     222     322     422     NULL
4       133     233     333     433     NULL

A new column is being created with the column name of the JOIN. Each row value for the new column is inserted with a NULL. I don't get it. First of all I don't want the new column created, second I have no idea why the new column is populated with NULL (not that it matters if it isn't created, but I don;t understand why it isn't populated with the value of the JOIN... ON statement if it is created.

My understanding was that by specifying the target column names with the appropriate SELECT I should only INSERT into the appropriate columns.

When I do this with a statement that has more than one criteria for the JOIN, two columns (or more) get created. For example, if the JOIN... ON statement was something like the following:

ON A.Text_GUID = B.Text_GUID AND A.Another_Col = B.Another_Col

I would end up with:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D   Text_GUID   Another_Col
1       100     200     300     400     NULL        NULL
2       111     211     311     411     NULL        NULL
3       122     222     322     422     NULL        NULL
4       133     233     333     433     NULL        NULL

This particular query runs against anything from 150 million rows to 8 billion rows, so if I can avoid it I do not want to put everything into a temporary table first as this is part of a migration from a rather poorly designed structure to a far more normalized one (where ID's are int's instead of text GUIDs for one thing).

Server is MS SQL Server 2008.

Any and all help would be very much appreciated. Thanks.

Argh.. Stupidity reigns supreme. In response to Aaron's comment (which deserved an up-vote for stating the obviously overlooked), I traced back through the nested stored procedures. Turns out there was an ALTER TABLE that was adding the columns with NULL values from some old legacy script that was an attempt at trying to do what the INSERT INTO was able to accomplish all along.

@Aaron, thanks for stating the obvious which lead me to check the code path to this point.


How can I force the MySQL client to output to stdout or a log file when importing a SQL file? Let's say I am importing a small SQL file using the MySQL client like so:
mysql < 10k-file.sql

The mysql client does not output anything. How can I force it to show how many rows are affected for each query, errors, so on?

You can use the  general log
so enable general log
but be careful this will log everything what happens and gets fast big


How should I handle constraining options in a database? For sake of simplicity,
I have a Gender table with only 2 values:
Male, Female

I have another table called Activity with a field called restrict_to_gender. The values of this field can be any one of the following
Male, Female, DoNotLimit

I know I can simply create another table called ActivityGenderLimit to host these values, but it seems like I am having redundant values, since the Gender table already has the genders.
How do database people handle situations like this?
[Note: i am willing to change my question title, but I didnt know a better one to describe it]

Constraint is the exact keyword you'll want to look into (so it was coincendetaly good verbiage you used).
It depends on the database system you end up using (as they each have their minute and not so minute differences) but in Microsoft SQL Server, for example, they call it a "Check Constraint".
You can read about Check Constraints and how they limit a field to specific values here: Create Check Constraints
Specifically it sums it up by saying:

You can create a check constraint in a table to specify the data
values that are acceptable in one or more columns.

For further supplemental reading, W3Schools has a succinct page on them here: SQL CHECK Constraint
They also provide some examples of code implementation across multiple database systems.
Also, if you wanted to use a Table to still hold the values that are enforced by your Check Constraints, in some database systems this is possible. Here's a StackOverflow post with a good example using a Function in the Check Constraint that can reference your values Table: Can a Check constraint relate to another table?


Connect Oracle Database 12c via SQL Developer when it is in restricted mode I would like to connect to database 12c via SQL Developer(in same VM where db is hosted) when it is in restrict mode. Tried below approaches but nothing worked.
Kindly help !

Added (UR=A) in tnsname.ora as below:
OBIEE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1521))
(CONNECT_DATA =
  (UR=A)
  (SERVER = DEDICATED)
  (SERVICE_NAME = OBIEE)
)
)
Also, defined this in sqlnet.ora:
tcp.validnode_checking = YES
tcp.invited_nodes = IP address

*Note: I am able to connect to it via cmd.

The above connection string should work with the addition of (UR=A). Seems like your SQL Developer connection is configured differently.

Alternatively, you can define the connection type as Advanced, and use the below custom JDBC url:

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1521)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME = OBIEE) ) )


Copy DB from SQL Server 2008 to SQL Server 2012 I have several databases on SQL Server 2008 that I wish to transfer to SQL Server 2012.

The two servers are on the same network but separate domains.

I am attempting a DB Copy from the one server to the new one and it connects successfully
I just am not allowed to select any of the DB instances to copy to the new server.

I have been attempting the connection from the 2012 end. I have DB sysadmin IDs at both
ends.

Any advice is appreciated.

Lou

DB Copy has a number of limitations as describe at: http://msdn.microsoft.com/en-us/library/ms188664.aspx

This includes not supporting the copying of System databases, Databases marked for replication, or any database not in a normal status.

However, it does support moving logins, jobs, and so forth.

The typically best way to copy a database with the fewest issues is to do a BACKUP from the source server and a RESTORE on the target server.  This is mentioned here in the discussion of strategies for copying databases:

http://msdn.microsoft.com/en-us/library/ms189624.aspx

If you also need to copy logins from the old to the new server, there are a couple of scripts documented at:  http://support.microsoft.com/kb/246133

SQL Agent Jobs can be scripted and recreated on the target server as well.


Query to update the JSON that does not contain a specific key I want to query a table that has JSON data. The data of the column detail in the table details are as follows.

id     | 1

detail | {
   myCompany: [{ email: 'company@company.com', inProfit: true }],
   myData: [
      { email: 'example@example.com', personalUse: true },
      { email: 'test@test.com', personalUse: false },
      { email: 'test@example.com' },
   ],
};

The JSON value of the column detail is as follows

{
   "myCompany":[
      {
         "email":"company@company.com",
         "inProfit":true
      }
   ],
   "myData":[
      {
         "email":"example@example.com",
         "personalUse":true
      },
      {
         "email":"test@test.com",
         "personalUse":false
      },
      {
         "email":"test@example.com",
      }
   ]
}

I want a query that updates the myData. If the myData field doesn't contain personalUse then update that field with personalUse:true. In the given example { email: 'test@example.com' } field should update as { email: 'test@example.com', personalUse: true }

You can use the below queries to try it locally.

CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail)
VALUES
  ('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],
     "myData":[{"email":"example@example.com", "personalUse":true },
               {"email":"test@test.com", "personalUse":false },
               {"email":"test@example.com"} ] }');

Please, someone, help me out.

I had fun with this one :) This leaves personalUse as-is when present, otherwise sets it to true, and should preserve all other key/values in in the toplevel object besides myData and all keys other than personalUse in the innermost objects. What it is not - and I'm pretty sure cannot be - is remotely efficient, but that can't really be helped without using jsonb, adding indexes, and otherwise doing a lot of high-touch things. This should also work back to at least PG9.5, although I only tested as far back as 10.

testdb=# CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail)
VALUES
  ('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],
     "myData":[{"email":"example@example.com", "personalUse":true },
               {"email":"test@test.com", "personalUse":false },
               {"email":"test@example.com"} ] }');
CREATE TABLE
INSERT 0 1
testdb=# with
explode_fields as (
select id, key as details_key, value as details_value from details, json_each(detail)
),
only_mydata as (
select * from explode_fields where details_key='myData'
),
explode_mydata as (
select only_mydata.*, row_number() over (partition by id) as item_idx, item as mydata_item
FROM only_mydata, json_array_elements(details_value) as item
),
explode_mydata_fields as (
select explode_mydata.*, key as mydata_item_key, value as mydata_item_value
from explode_mydata, json_each(mydata_item)
),
only_mydata_personaluses as (
select * from explode_mydata_fields where mydata_item_key = 'personalUse'
),
/* Need a true for every inner item to use as the 2nd argument to coalesce() */
personaluse_all_trues as (
select id, details_key, details_value, item_idx, mydata_item, 'personalUse' as mydata_item_key, 'true'::json as mydata_item_value
from explode_mydata
),
merged_personaluses as (
select t0.id, t0.details_key, t0.details_value, t0.item_idx, t0.mydata_item, 'personalUse' as mydata_item_key, coalesce(t1.mydata_item_value, t0.mydata_item_value) mydata_item_value
FROM personaluse_all_trues t0
left join only_mydata_personaluses t1
on t0.id=t1.id and t0.details_key=t1.details_key and t0.details_value::text=t1.details_value::text and t0.item_idx::text=t1.item_idx::text and t0.mydata_item::text=t1.mydata_item::text
),
include_other_item_fields as (
select id, details_key, details_value, item_idx, mydata_item, mydata_item_key, mydata_item_value from merged_personaluses
union all
select id, details_key, details_value, item_idx, mydata_item, mydata_item_key, mydata_item_value from explode_mydata_fields where mydata_item_key <> 'personalUse'
),
agg_mydata_fields as (
select id, details_key, details_value::text, item_idx, json_object_agg(mydata_item_key, mydata_item_value) as item
from include_other_item_fields
group by 1, 2, 3, 4
),
agg_mydatas as (
select id, details_key, json_agg(item) as new_details
from agg_mydata_fields group by 1, 2
),
include_other_fields as (
select id, details_key, new_details as details_value
from agg_mydatas
union all
select * from explode_fields where details_key <> 'myData'
),
agg_records as (
select id, json_object_agg(details_key, details_value) as detail from include_other_fields
group by id
)
update details set detail=agg_records.detail
from agg_records
where details.id=agg_records.id;
UPDATE 1
testdb=# select id, jsonb_pretty(detail::jsonb) from details;
 id |                jsonb_pretty
----+---------------------------------------------
  1 | {                                          +
    |     "myData": [                            +
    |         {                                  +
    |             "email": "example@example.com",+
    |             "personalUse": true            +
    |         },                                 +
    |         {                                  +
    |             "email": "test@test.com",      +
    |             "personalUse": false           +
    |         },                                 +
    |         {                                  +
    |             "email": "test@example.com",   +
    |             "personalUse": true            +
    |         }                                  +
    |     ],                                     +
    |     "myCompany": [                         +
    |         {                                  +
    |             "email": "company@company.com",+
    |             "inProfit": true               +
    |         }                                  +
    |     ]                                      +
    | }
(1 row)


Does SQL Server 2008 have an equivalent to Oracle's “table%rowtype” syntax or (Data Type)? In Oracle PL/SQL scripts, you can declare a variables using the syntax tablename%rowtype for its type. This will make that variable have the same "type" as a row of that table

(i.e. a it will be a structure with the same field names and types).

This is a convenient way to transfer data between database tables and PL/SQL.

I am looking at doing the same thing (Data Type) in Microsoft SQL Server 2008. Is there an equivalent feature?

No version of SQL Server has this. It would be useful (PostreSQL has the same and I like it).

In SQL Server, there are table types (CREATE TYPE), temporary tables (CREATE TABLE #foo) and table variables (DECLARE @var TABLE), but you can't mimic or inherit a table definition using %TYPE or CREATE TABLE .. INHERITS/OF


Database mirroring with fixed set of tables without sql server replication We have requirement of mirroring the database with some fixed set of tables. We do not want to use sql server replication for that purpose.

Do we have any solution for that or any software for this purpose which we can use for the replication of the set of tables to remote location?

We have some set of tables around 6 to 7 that needs to be replicated in remote location so that we can have offline website setup when our live system went down. Which only shows important bit of information online for temporary purpose.

IMHO, Replication is your best choice.

Alternatively, you can look into SSIS and do an incremental load to your remote database for the tables you need. If they have foreign key dependencies then make sure to load them in order.

Also, you can look into logshipping (this is entire database) and delay the restore of transaction logs, so that you can use secondary as stand by for running reports as well.


Removing records from database periodically that surpass timeline I have a reports table which has literally millions, if not hundreds of millions of report records, which date back to 2011. I think it's slowing down my queries exponentially. I only want to keep reports from the past 6 months. I have a time column in reports table which gives the current time so I can use that to measure reports older than 6 months. So I am thinking about running some type of cron job on my ubuntu server in which the postgresql server is installed on. But I am looking for some guidance about going about this.

Recently I set up a similar archiving solution.  The numbers and the target are a bit different (the latter being moving old data into an archive partition), but the idea is the same.

First, you will need an index on the timestamp, for quickly finding the rows to be deleted.  To avoid excessive locking and blocking other processes as a consequence, this is preferably done with the CONCURRENTLY option.

Second, you have to keep your table tidy by vacuuming it regularly - if you have a relatively recent PostgreSQL version, there are chances that the database will take care of it with autovacuum.

The actual DELETE statement will be a rather simple one.  In my case, I use a function for this, so that we can keep the database logic under version control.  This may look like

CREATE OR REPLACE FUNCTION delete_function()
RETURNS void
LANGUAGE sql
AS $body$
DELETE FROM big_table
WHERE report_timestamp < now() - interval '6 months';
$body$;

You will need a script (say a bash script), which simply calls the statement, like

#!/bin/bash

psql {connection parameters} -c 'SELECT delete_function()'

The only thing that remains is setting up a cron job which calls this script at the desired intervals.

There is still one thing to consider, namely that the first delete would be a huge one.  I would prevent this by deleting the big amount of old data in suitably sized chunks, again for avoiding blocking other processes that want to access the table.  The size of these chunks can be very different based on your circumstances.  Note that this is more or less a manual process, unless you have already a 'chunker' in use (as we have).


Update nested table item I'm really new on oracle db. I'm trying to update specific column on this table.

CREATE TYPE ABSTRACT_SALARY AS OBJECT (
startDate DATE,
endDate DATE,
salary NUMBER );

CREATE TYPE SALARY_TYPE AS TABLE OF ABSTRACT_SALARY;

CREATE TABLE Person(
---
---
---
---
salaryType SALARY_TYPE
)
NESTED TABLE salaryType STORE AS person_salaryTypes;

How can I update salary column %10 higher. Thanks in advance.

Update all rows in the whole nested table:

update /*+ NESTED_TABLE_GET_REFS */ person_salaryTypes
set salary = salary * 1.1;
commit;

Update all rows in the nested table for 1 person:

update table(select p.salarytype from person p where p.id = 1)
set salary = salary * 1.1;
commit;

Update 1 row in the nested table for 1 person:

update table(select p.salarytype from person p where p.id = 1)
set salary = salary * 1.1
where startdate = ...;
commit;