How to improve performance of query for distinct suppliers in big sales table? I have two tables, one with store data:

              Table "public.store"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 id        | integer                | not null
 hul_code  | character varying      |
 latitude  | numeric                |
 longitude | numeric                |
 name      | character varying(100) |
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)
    "store-hlcode-idx" btree (hul_code)

Another with the sales data:

         Table "public.sale_data"
  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 id        | integer           | not null
 hul_code  | character varying |
 partyname | character varying |
 rscode    | character varying |
 rsname    | character varying |
 cluster   | character varying |
 channel   | character varying |
 basepack  | character varying |
 total_16  | numeric           |
 total_17  | numeric           |
 total_18  | numeric           |
Indexes:
    "sale_data_pkey" PRIMARY KEY, btree (id)
    "hul-code-idx" btree (hul_code)
    "sd-bp-idx" btree (basepack)
    "sd-rscd-idx" btree (rscode)

The second table has over 11 million rows.

I'm trying to get the list of all stores which had items coming from a particular Regional Supplier (identified by rscode) by using a query like:

Select s.hul_code, s.name, s.latitude, s.longitude
from store s
Where hul_code in
  (Select Distinct(hul_code) as hulcode
   from sale_data
   where rscode='133955')
AND s.latitude is not null;

This query takes more than 1.5 to 2 seconds on average.

How to increase the speed of this query?

For typical data distributions, emulating a loose index scan should be the magic trick for you.
Requires a multicolumn index to be fast:
CREATE INDEX ON sale_data(rscode, hul_code);

The variant with correlated subquery in the rCTE should be fastest for the simple case:
WITH RECURSIVE cte AS (
   SELECT min(hul_code) AS hul_code
   FROM   sale_data
   WHERE  rscode = '133955'    -- provide rscode here

   UNION ALL
   SELECT (SELECT hul_code
           FROM   sale_data
           WHERE  rscode = '133955'     -- repeat rscode here
           AND    hul_code > c.hul_code
           ORDER  BY 1
           LIMIT  1
           )
   FROM   cte c
   )
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM   cte c
JOIN   store s USING (hul_code);

Detailed explanation:

Optimize GROUP BY query to retrieve latest record per user

There may be faster alternatives for particular data distributions.
Asides:
DISTINCT might make sense for data distributions with very few rows per distinct (rscode, hul_code) (producing large numbers of resulting rows). But what you have doesn't make a lot of sense:
IN (Select Distinct(hul_code) as hulcode ... )

The column alias (as hulcode) is noise since IN ignores column names.
DISTINCT does not require parentheses. (hul_code) is interpreted as ROW constructor, short for ROW(hul_code). The row wrapper happens to be stripped automatically for the single column in this case. But you might introduce complications for cases with multiple columns.
There is the Postgres variant DISTINCT ON () which requires parentheses, but that's not used (nor needed) here. Related:

Select first row in each GROUP BY group?

But none of this makes a lot of sense with an IN construct, where you might just drop DISTINCT altogether. Better, yet, rewrite as:
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM  (
   SELECT DISTINCT hul_code
   FROM   sale_data
   WHERE  rscode='133955'
   ) sd
JOIN  store s USING (hul_code);

.. in the unlikely case that you should have such a data distribution.
And your columns rscode seems to hold numeric data. If so, it should be a numeric type, not varchar.


Oracle 11.2 using too few memory it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012).

The problem is that with more than doubled cpu power, about 3 times the ram and faster disks, my new server is slower than before!!!
I'm investigating on it but having the standard edition one license I haven't diagnostic pack or something else...

One thing that sounds me bad is that on old server, if I set SGA+PGA memory to 16GB, oracle allocate all 16GB immediatelly and I can check it also in windows task manager.
Now, my memory_target = memory_max_target = 20GB, but in windows task manager oracle doesn't use more than 10GB of ram. How is it possible? Can I force it to use more ram?

This is my pfile

sicor.__db_cache_size=5796528128
sicor.__java_pool_size=58720256
sicor.__large_pool_size=12582912
sicor.__pga_aggregate_target=12377391104
sicor.__sga_target=9097445376
sicor.__shared_io_pool_size=0
sicor.__shared_pool_size=3154116608
sicor.__streams_pool_size=25165824
*.db_block_size=8192
*.distributed_lock_timeout=300
*.job_queue_processes=1000
*.memory_max_target=21474836480
*.memory_target=21474836480
*.open_cursors=1500
*.parallel_automatic_tuning=FALSE
*.parallel_max_servers=80
*.parallel_servers_target=32
*.parallel_threads_per_cpu=2
*.pga_aggregate_target=0
*.processes=1200
*.sessions=1325
*.sga_max_size=12884901888
*.sga_target=0

This is the querty for DB Cache Advice starting from 1

5536____682312__1_______39052864
5984____737528__0,9056__35366617
6528____804576__0,8461__33043015
7072____871624__0,8149__31825392
7616____938672__0,789___30813474
8160____1005720_0,766___29912665
8704____1072768_0,7442__29064260
9248____1139816_0,7219__28193363
9792____1206864_0,6997__27324768
10336___1273912_0,6778__26468501
10880___1340960_0,6558__25610722

So it seems that doubling cache will improve drammatiacally the performance..

How can I set it in order to use at least 24-28GB of ram?
Thanks

Oracle doesn't pre-page the SGA by default, meaning that when you start the DB instance, it will not initially grab all the memory until it needs it.

See http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams201.htm#REFRN10174.

I'd try this:

Set memory_target = memory_max_target = 24GB.
Get rid of sga_max_size, sga_target, pga_aggregate_target in your init parameters, unless specifically needed by some crazy vendor app (in other words, let 11g AMM do its thing instead).
Also clear the db_cache_size from your pfile.  You were basically limiting your cache to 5.4 GB.
Bounce the instance.
Beat it up with workload for a few hours/days (SQL queries, gather stats, rebuild indexes, RMAN backups, expdp).
Check in Task Manager for memory utilization.


Keeping an updated balance (Accounting) I have been struggling trying to find a way to properly and reliably maintain a balance for accounts in an order/payment system.

Currently, the process consists of three tables. Below example is very simplified but gets the point across.

Table one contains the account ID, name, total paid, total orders, balance etc.

Table two contains payments, date, the account ID (FK) and amount.

Table three contains orders, date, the account ID (FK) and amount.

Calculating the balance is of course as easy as

SELECT SUM(payments.AMOUNT) FROM payments WHERE ID = [account]
SELECT SUM(orders.AMOUNT) FROM orders WHERE ID = [account]

Followed by some simple addition/subtraction math:

UPDATE accounts SET BALANCE = [balance] WHERE ID = [account]

All good - the problem is - how do I maintain this over let's assume years of activity and many, many transactions?

Currently the accounts table is updated every single time a transaction is made, meaning the above three statements run every time. There is of course an index on the ID column in all three tables, and everything is fast and neat for now, but I work with less than 100,000 transactions, meaning everything fits into the RAM and basically all I throw at the database gets executed immediately. Over time this will change, of course - so how do I handle this?

Is it better to mark the accounts as "dirty" and process balancing in a batch job later, or can I keep updating the balance real-time even when I reach the point where the orders and payments tables will not fit into the RAM?

Do I need to segment the orders and payments and "lock" everything before a given time, such as before last calculation? (I would rather not). I ask because then I could do a SUM WHERE DATE > [last lock timestamp] and update with UPDATE BALANCE = [balance] + [value for last lock] and prevent the problem from outgrowing resources.

I don't need a running balance for each transaction. That's all I can find results for when I try to research this problem. I just care about the total amount paid, the total amount ordered and the balance of accounts.

Why don't I calculate on the fly when I need the balance, you ask? Because one must be able to query the system and ask "Which of my (let's say) 50.000 accounts have a balance below 0?" - something we cannot do easily if we do not store the balance with every account.

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread trying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.


Aggregate values by interval range given by parameter I have a table containing an amount column and an index column.
I wish to build a single query, which sums up the amount for the given interval ranges for every resulting interval from given start index to end index - i.e. the 'interval range' slices the data according index column. For these groups, sum over amount is built.
Is this possible and how?

SELECT sum(amount) from mytable
WHERE index between <startindex> and <endindex>
GROUP BY ...

Example:

amount | index
55        1
88        5
45        6
86        7
87        10
88        11
57        16
58        17
59        20
60        21

A query with interval range 5, start index 5 and endindex 14 should result in two return values:

  88+45+86 => 219
  87+88    => 175

A query with interval range 4, start index 0 and endindex 15 should result in four return values:

  55       => 55
  88+45+86 => 219
  87+88    => 175
  --       => 0

Thanks for any help!

CREATE TABLE T(amount INT, index INT);
INSERT INTO T VALUES
(55, 1),(88, 5),(45, 6),(86, 7),(87, 10),
(88, 11),(57, 16),(58, 17),(59, 20),(60, 21);

You can take advantage of Postgres function generate_series().

generate_series(start, stop, step)

  Generate a series of values, from start to stop with a step size of step

The example below uses a series from 0 to 14 with an interval of 4.

SELECT generate_series(0, 14, 4) Serie;

| serie |
| ----: |
|     0 |
|     4 |
|     8 |
|    12 |

WITH CTS AS
(
    SELECT generate_series(0, 14, 4) Serie
)
SELECT    COALESCE(SUM(T.amount),0) AS amount, CTS.Serie
FROM      CTS
LEFT JOIN T
ON        T.index >= CTS.Serie
AND       T.index <  CTS.Serie + 4
GROUP BY  CTS.Serie
ORDER BY  CTS.Serie;

amount | serie
-----: | ----:
    55 |     0
   219 |     4
   175 |     8
     0 |    12

You can use a user defined function that allows you to use parameters

CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int)
RETURNS TABLE (amount int, interv int) AS
$$
BEGIN
    RETURN QUERY
    WITH CTS AS
    (
        SELECT generate_series(low, high, step) Serie
    )
    SELECT    COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv
    FROM      CTS
    LEFT JOIN T
    ON        T.index >= CTS.Serie
    AND       T.index <  CTS.Serie + step
    GROUP BY  CTS.Serie
    ORDER BY  CTS.Serie;
END;
$$ LANGUAGE plpgsql;

✓

SELECT * FROM GroupIntervals(0, 14, 4);

amount | interv
-----: | -----:
    55 |      0
   219 |      4
   175 |      8
     0 |     12

SELECT * FROM GroupIntervals(5, 14, 5);

amount | interv
-----: | -----:
   219 |      5
   175 |     10

dbfiddle here

Update

As Evan Carrol has pointed out on him comments you can avoid use CTE and change function language from plpgsql to plain SQL.

CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int)
RETURNS TABLE (amount int, interv int)
AS $$
    SELECT    COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv
    FROM      generate_series(low, high, step) AS CTS(Serie)
    LEFT JOIN T
    ON        index >= CTS.Serie
    AND       index <  CTS.Serie + step
    GROUP BY  CTS.Serie
    ORDER BY  CTS.Serie;
$$ LANGUAGE sql;

SELECT * FROM GroupIntervals(0, 14, 4);

amount | interv
-----: | -----:
    55 |      0
   219 |      4
   175 |      8
     0 |     12

SELECT * FROM GroupIntervals(5, 14, 5);

amount | interv
-----: | -----:
   219 |      5
   175 |     10

dbfiddle here


T-SQL: Assign identifier to block of results I'm not sure exactly how to phrase this question but... I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each group of a the same block value in the results returned by my query.
An example might be helpful! If I have a SQL statement such as
SELECT
   Id,
   Title,
   Block,
   Order
FROM
   TableName
ORDER BY Order ASC

that returns the result:

Id
Title
BlockName
Order

100
Title A
Foo
1

101
Title B
Foo
2

102
Title C
Foo
3

103
Title D
Bar
4

104
Title E
Bar
5

105
Title F
Foo
6

Is there any fairly straightforward way to generate a result which gives a unique id to each block of results, something like:

Id
Title
BlockName
Order
BlockGroup

100
Title A
Foo
1
1

101
Title B
Foo
2
1

102
Title C
Foo
3
1

103
Title D
Bar
4
2

104
Title E
Bar
5
2

105
Title F
Foo
6
3

I hope this is clear enough :) Thanks in advance for any help!

Referencing SQL Server Lag Function to Group Table Rows on Column Value Changes:
--demo setup
drop table if exists Table1;
go

CREATE TABLE Table1
    ([Id] int, [Title] varchar(7), [BlockName] varchar(3), [Order] int)
;

INSERT INTO Table1
    ([Id], [Title], [BlockName], [Order])
VALUES
    (100, 'Title A', 'Foo', 1),
    (101, 'Title B', 'Foo', 2),
    (102, 'Title C', 'Foo', 3),
    (103, 'Title D', 'Bar', 4),
    (104, 'Title E', 'Bar', 5),
    (105, 'Title F', 'Foo', 6)
;

--solution
;WITH cte
AS (
    SELECT id
        ,Title
        ,BlockName
        ,[Order]
        ,lag(BlockName, 1, '') OVER (
            ORDER BY [Order]
            ) previous
        ,CASE
            WHEN (
                    lag(BlockName, 1, '') OVER (
                        ORDER BY [Order]
                        )
                    ) = BlockName
                THEN 0
            ELSE 1
            END ischange
    FROM Table1
    )
    ,tbl
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM cte
            WHERE id <= t.id
            ) groupno
    FROM cte t
    )
SELECT Id
    ,Title
    ,BlockName
    ,[Order]
    ,groupno AS BlockGroup
FROM tbl

Id          Title   BlockName Order       BlockGroup
----------- ------- --------- ----------- -----------
100         Title A Foo       1           1
101         Title B Foo       2           1
102         Title C Foo       3           1
103         Title D Bar       4           2
104         Title E Bar       5           2
105         Title F Foo       6           3


Should I add an arbitrary length limit to VARCHAR columns? According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.

Should I add an arbitrary length limit to a name or address column?

Edit: Not a dupe of:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I know the CHAR type is a relic of the past and I'm interested not only in performance but other pros and cons like Erwin stated in his amazing answer.

The answer is no.
Related advice in the Postgres Wiki.
Don't add a length modifier to varchar if you don't need it. (Most of the time, you don't.) Just use text for all character data. Make that varchar (standard SQL type) without length modifier if you need to stay compatible with RDBMS which don't have text as generic character string type.
Performance is almost the same, text is a bit faster in rare situations, and you save the cycles for the check on the length. Related:

Any downsides of using data type “text” for storing strings?

If you actually need to enforce a maximum length, varchar(n) is a valid choice, but I would still consider text with a CHECK constraint like:
ALTER TABLE tbl ADD CONSTRAINT tbl_col_len CHECK (length(col) < 51);

You can modify or drop such a constraint at any time without having to mess with the table definition and depending objects (views, functions, foreign keys, ...). And you can enforce other requirements in the (same) constraint.
Length modifiers used to cause problems like this or this or this ...
PostgreSQL 9.1 introduced a new feature to alleviate the pain somewhat. The release notes:

Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in
appropriate cases (Noah Misch, Robert Haas)
For example, converting a varchar column to text no longer requires a
rewrite of the table. However, increasing the length constraint on a
varchar column still requires a table rewrite.

More issues with varchar(n) have been fixed in later releases.


Ubuntu MS SQL Server RESTORE fails beacuse 'LOG_BACKUP' is full I have a MS SQL SERVER 2008 backup (all files .bak). I have a computer with Ubuntu and I want to restore the backup.

First I installed the SQL Server on Ubuntu with this link

Then I installed DBeaver

Now I am trying to restore the databases with this command

RESTORE DATABASE dfy2_alquiler
FROM DISK = '/var/opt/mssql/backup/dfy2_alquiler.bak'
WITH MOVE 'dfy2_alquiler_Data' TO '/var/opt/mssql/data/dfy2_alquiler_Data.mdf',
MOVE 'dfy2_alquiler_Log' TO '/var/opt/mssql/data/dfy2_alquiler_Log.ldf';

But I am getting this error

  SQL Error [9002] [S0002]: The transaction log for database 'dfy2_alquiler' is full due to 'LOG_BACKUP'.
    com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database 'dfy2_alquiler' is full due to 'LOG_BACKUP'.

I am not sure how can I solve this?

It sounds like the transaction log needs to grow during the upgrade portion of the restore from 2008 to vNext, but is unable to. Here are a few things to try:

Back up the log on the source database
Enable autogrowth on the source database log, if not already enabled
Make sure the autogrowth amount is set to a size that is smaller than the amount of free space on the target volume
Size the log on the source database so that it has some free space and won't have to autogrow during the restore
Take a fresh full backup and retry the restore to the target linux instance


SQL server AlwaysOn database not automatically seeding, cannot find logs with reason I've added a database to an availability group that already has a few databases, all of which seeded and syncing fine to the secondary.

This last one gets added to the availability group, it would seem, but is marked as "not synchronizing" and no errors are returned.  I've tried it multiple times with the same result.

I've looked in extended events, Event Viewer... nothing.  Clicking on "warnings" in the dashboard says nothing useful.  The database files do not exist on the secondary at all.  I suspect that it was blocked right at the start of its seeding process (but clearly after the initial validations in the "add to AG" wizard).

I have this distinct feeling SQL server must be logging a relevant failure message to some DMV I'm not aware of.

Is there a canonical guide to ALL the places one is supposed to look for AlwaysOn related errors?

On the secondary replica, you should be able to see the status / outcome of seeding attempts in the sys.dm_hadr_automatic_seeding DMV.  This includes columns like current_state, failure_state, and failure_state_desc, as well as timing related columns, that should give you a decent overview of seeding issues.

I have often seen seeding errors written to the SQL Server error log (also on the secondary replica), I don't think you mentioned looking there:

One thing I often forget to do is to run this on the secondary replica:

ALTER AVAILABILITY GROUP yourAgName GRANT CREATE ANY DATABASE;

Note: if you're interested in seeing which replicas have automatic seeding permissions, check out this blog post from Sean Gallardy: Automatic Seeding – Which Replicas Have Permission?

Once you've done that, you can attempt re-seeding by running this on the primary:

ALTER AVAILABILITY GROUP yourAgName
    MODIFY REPLICA ON 'SecondaryReplicaInstanceName'
    WITH (SEEDING_MODE = AUTOMATIC);

That normally gets me up and running.

As far as this question:

  Is there a canonical guide to ALL the places one is supposed to look for AlwaysOn related errors?

There is a great blog post from the SQL Server team here: Failover Detection Utility – Availability Group Failover Analysis Made Easy

It's about troubleshooting failovers specifically, but it lists out the main places where you'll see AG errors and diagnostic information:

SQL error logs
Always On Availability Groups Extended Event Logs
System Health Extended Event Logs
System log
Windows cluster log


Whats wrong with my query? I know that this works for a database called DB01_20161110:

ALTER AVAILABILITY GROUP AG1 ADD DATABASE DB01_20161110

I'm trying to add today's date automatically:

DECLARE @DateString   AS VARCHAR(256)
SET @DateString =     cast(year(getdate()) as varchar(4))
+     right('0' + cast(month(getdate()) as varchar(2)), 2)
+     right('0' + cast(day(getdate()) as varchar(2)), 2)
DECLARE @DB AS VARCHAR (40)
SET @DB = 'DB01_' + @DateString

ALTER AVAILABILITY GROUP AG1 ADD DATABASE @DB
GO

I get:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@DB'

Also tried:

DECLARE @DateString   AS VARCHAR(256)
SET @DateString =     cast(year(getdate()) as varchar(4))
+     right('0' + cast(month(getdate()) as varchar(2)), 2)
+     right('0' + cast(day(getdate()) as varchar(2)), 2)

ALTER AVAILABILITY GROUP AG1 ADD DATABASE 'DB01_' + @DateString
GO

Same error.

I use the same DECLARE part for other scripts so that's working.

Any advice?

You need to use dynamic SQL for this. The following example will work (as does a quick check to see that the database exists, you could add additional checks as well).

DECLARE @DateString AS VARCHAR(256) ,
    @ExecuteText NVARCHAR(4000);
SET @DateString = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + RIGHT('0'
                                                              + CAST(MONTH(GETDATE()) AS VARCHAR(2)),
                                                              2) + RIGHT('0'
                                                              + CAST(DAY(GETDATE()) AS VARCHAR(2)),
                                                              2);
DECLARE @DB AS VARCHAR(40);
SET @DB = 'DB01_' + @DateString;

IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name = @DB )
    BEGIN
        SET @ExecuteText = N'ALTER AVAILABILITY GROUP AG1 ADD DATABASE ' + @DB
            + ';';
        EXEC sys.sp_executesql @ExecuteText;
    END;
ELSE
    BEGIN
        RAISERROR(N'Database: %s does not exist', 16, 1, @DB);
    END;
GO


Access table field on creating a trigger postgresql How can I access table field on creating a trigger?

CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter

Per documentation:

  The trigger function must be defined before the trigger itself can be
  created. The trigger function must be declared as a function taking no
  arguments and returning type trigger. (The trigger function receives
  its input through a specially-passed TriggerData structure, not in the
  form of ordinary function arguments.)

Bold emphasis mine.

To pass those parameters:

arguments

  An optional comma-separated list of arguments to be provided to the
  function when the trigger is executed. The arguments are literal
  string constants. Simple names and numeric constants can be written
  here, too, but they will all be converted to strings. Please check the
  description of the implementation language of the trigger function to
  find out how these arguments can be accessed within the function; it
  might be different from normal function arguments.

And finally, in the chapter Trigger Procedures:

  Note that the function must be declared with no arguments even if it
  expects to receive arguments specified in CREATE TRIGGER — trigger
  arguments are passed via TG_ARGV, as described below.

  ...

TG_ARGV[]

  Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

The final difficulty is that SQL only works with static identifiers. If you want to parametrize a column name, you need dynamic SQL. In a plpgsql function, use EXECUTE for that.

Example

CREATE OR REPLACE FUNCTION trg_foo()
  RETURNS trigger AS
$func$
DECLARE
   _result text;
BEGIN
   CASE TG_OP
   WHEN 'INSERT', 'UPDATE' THEN
      EXECUTE format('SELECT %I FROM %s WHERE tbl_id = $1', TG_ARGV[1]
                                                          , TG_ARGV[0]::regclass)
      INTO  _result
      USING NEW.sometbl_id;
   WHEN ...

   END CASE;

   -- do something with _result ...

   RETURN NULL;   -- since it's an AFTER trigger
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW
EXECUTE PROCEDURE trg_foo('foo', 'field'); -- two params: table, field

Answer on SO with details about the cast to regclass:
Table name as a PostgreSQL function parameter


Users vs. People - where to keep the email address? I'm designing an app similar to Facebook, called Headtome.  I want to have users that create public profiles, including email addresses.  Then, Mary can choose to message John, and that message goes straight to his publicly available email.  More creepily, if John and Mary mark themselves as in a relationship, then only John can view Mary's profile.

Here is (part of) the current database structure:

Table users:

id | username | password | person_id |
--------------------------------------

Table persons:

id | first_name | last_name | nickname |
----------------------------------------

Table person_emails:

id | person_id | email_address | is_main |
------------------------------------------

[I use a separate table for emails because a person may decide to show multiple email addresses.]

I argue that the users table should also have an email_address column.

The Pros

If a user forgets their password, we need an email address to reset it
A user might use a different email for logging in than the one they show on the profile
Or, they may choose not to show an email at all (don't message me)
A user's profile data is still available (through person_id)
A user email might be forever, whereas the public ones are temporary (job-related, etc.)
When Mary messages John, she's messaging John the Person, not John the User

The Cons

Managing two separate email addresses might get confusing for users
Or it could lead to duplication
The whole point is to create a profile, so persons is the main table
If John and Mary are dating, isn't it John the User who is allowed to see her profile?

What is the right approach here? Not that it should make a difference, but this is in MySQL.  Also, this is a fictional example exemplifying a real dilemma.

I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user.  The model would look like this:

This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the relationship.  It has the additional benefit also of ensuring you understand who is actually using a given email address, as you can place an alternate unique index on the email address text as a given email address in unique.  I think this kind of solution would eliminate the cons while still retaining all of the pros.


Why is my TRY CATCH block in SQL Server immediately exiting on error completely ignoring the remaining code in the block, including my error handling? Below is a snippet of my TRY/CATCH logic within a stored procedure with error handling (I did not include all of the DECLARE statements). This is being ran within a stored procedure, however I am simply testing this in a session via SSMS (would this affect the behavior?). I did stumble across this thread, but do I have to go to these lengths to simply capture errors?

BEGIN TRY
DECLARE @sql varchar(1000);
SET @sql = 'select 1/0';
EXEC(@sql);
-- On Error the remaining TRY/CATCH below is compeltely ignored I discovered.
DECLARE @error int;
SET @error = @@error;
IF @error > 0
 BEGIN
     SET @raisemessage = 'SQL Backup Error: ' + cast(@rc as varchar(10));
         RAISERROR (@raisemessage, 16,1);
 END
END TRY

BEGIN CATCH
    SET @errormessage = ERROR_MESSAGE();
    IF @errormessage is null
        SET @errormessage = ''
        SET @errorstring = @errorstring + 'Database: ' + @name + '  Error: ' + @errormessage + char(10) + char(10);
END CATCH

Can anyone explain the shortcomings of this TRY/CATCH block? Does this not work in T-SQL?

This is actually far more straightforward than I thought. Assuming that all of the variables used above are declared elsewhere, the simple answer as to why the catch isn't working is that it is working, but it has syntax errors internally that result in a return value of NULL.

Consider this line:

SET @errorstring = @errorstring + 'Database: ' + @name + '  Error: ' + @errormessage + char(10) + char(10);

Unless you declared @errorstring like this:

DECLARE @errorstring varchar(255) = ''

Or assigned @errorstring as some value, the variable is currently set to NULL. And, by default, concatenating anything to NULL returns... NULL.

Changing the code here to

SET @errorstring = 'Database: ' + @name + '  Error: ' + @errormessage + char(10) + char(10);

Sets the variable to the correct value.


"Cannot resolve table name" error when applying MySQL dump to RDS I'm setting up replication of an external MySQL instance to RDS.

Both the external MySQL instance and RDS run MySQL 5.7.10.

I've followed the procedure, but when dumping the database with mysqldump and sending it to mysql connected to RDS, I get the following error:

  ERROR 1215 (HY000) at line 2081: Cannot add foreign key constraint

SHOW ENGINE InnoDB STATUS on RDS shows:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-03-25 01:38:56 0x2ad07ddcf700 Error in foreign key constraint of table db/Prospect:
 FOREIGN KEY (`restaurantId`) REFERENCES `Restaurant` (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci:
Cannot resolve table name close to:
 (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

OK, indeed the dump file recreates the tables alphabetically, so the Prospect table appears before Restaurant and Zone, which can explain the error.

But NO, because mysqldump adds SET FOREIGN_KEY_CHECKS=0 to the top of the file, so it should work anyway.

So I decided to test it against a vanilla MySQL 5.7 installation, and no surprise, the dump is imported successfully with the exact same commands.

What causes this error specifically on RDS, and how to avoid it?

Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11.

Extract from the changelog:

  InnoDB: Creating a table with a full-text index and a foreign key constraint failed when foreign_key_checks was disabled. (Bug #22094601, Bug #78955)

It took me time to figure out that the full-text index was the source of the problem, I had to trim down my dump file line by line until the dump could be successfully imported.

Unfortunately RDS does not support MySQL 5.7.11 yet, so I had to work around this issue. Here is what I did:

created the dump file with mysqldump as usual
opened the dump file and commented out all FULLTEXT KEY lines
noted down these lines in notepad
imported the dump to the replica as usual
connected to the replica, and issued ALTER TABLE statements to add each full-text index I had in my notepad back to its table

This worked perfectly well and my tables are fully in sync now.


MySQL turn rows into columns I have a table that looks like this (d could be the day, and h1 h2 h3 the values of the single hours of the day(assuming the day has 3 hours)):

d  h1 h2 h3
1  1  2  3
2  4  5  6
3  7  8  9

and I what an output like this (one row with all the hours):

d h value
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9

Is there a way do this with MySQL?

Edit: Having to address every single column in the syntax should be avoided, because this is supposed to also work for a lot of columns.

What you describe is more like "turn columns into rows". You can do this with UNION [ALL].

The difference between UNION and UNION ALL is, that UNION does not return duplicates, it acts like DISTINCT. If you know, there won't be duplicates, you might as well use UNION ALL and spare your server the work to detect any.

SELECT
d, 1 as h, h1 as 'value'
FROM t
UNION ALL
SELECT
d, 2 as h, h2 as 'value'
FROM t
UNION ALL
SELECT
d, 3 as h, h3 as 'value'
FROM t
# optionally you can add
ORDER BY d, h, value

see it working live in an sqlfiddle


Changing mariadb port I'm trying to change the default port for mariadb to 52000. I've modifed the port in /etc/MySQL/mariadb.conf.d/50-server.cnf and restarted the service. However, each time it still starts up on the default port.

I'm running Debian. Is there another config somewhere that I need to modify?

The right configuration is:

port = 52000

under a [server] or [mysqld] section.

Your most likely problem is that either you are modifying a configuration file that is not being included, or it is being included, but other file is overriding your config (last parsed option wins). Look for other files containing the port definition and delete them, or make sure they are read in the right order.

To check which mysqld config files are applied, you can run mysqld --verbose --help to verify which config files are read. More suggestions at https://stackoverflow.com/questions/580331/determine-which-configuration-file-is-being-used


Simple join in postgresql I have a simple join query below :

select jobs.location_id
from companies,
     jobs
where jobs.company_id = companies.id
and companies.name = 'name';

it doesn't seem to get the job location of company has that name, but somehow all location_id in jobs table.

Meanwhile these two separate queries do the job:

select id from companies where name = 'name';
select jobs.location_id from jobs where jobs.company_id = [resulted id from query above];

What's wrong with the first query ?

Don't join tables like that. Using a comma means cross joining tables (cartesian product).

select j.location_id
from companies c
   join jobs j on j.company_id = c.id
and c.name = 'name'

These two queries should produce the same execution plan and therefore the same results but this one is much clearer.


Query to convert the count to million or thousands I am running a query on a database to return count(*) for number of rows.

If the count is greater than million say 2,250,304, I want the query to return 2.25M.
If the count is greater than 1000 but less than a million say 65130 I want the query to return '65.1k'.
If the count is less than 1000 then the query should return actual count.

Here is my query:

SELECT DATENAME(mm, CREATEDTS) + ', ' + DATENAME(yyyy, CREATEDTS) AS Month,
       CASE
         WHEN count(*) > 1000
              AND count(*) < 1000000 THEN ( CONVERT (VARCHAR(10), ( count(*) / 1000 )) ) + 'k'
         WHEN count(*) > 1000000 THEN ( CONVERT (VARCHAR(10), ( count(*) / 1000 )) ) + 'm'
         ELSE COUNT(*)
       END
FROM   USAGEDATA
GROUP  BY month(CREATEDTS),
          DATENAME(mm, CREATEDTS) + ', ' + DATENAME(yyyy, CREATEDTS)
ORDER  BY MONTH(CREATEDTS)

When I run it I am getting an error:

  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting
  the varchar value '165k' to data type int.

I am not sure where I am converting varchar to INT and getting this error.

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;


Unable to login to MySQL with new user I currently have a MySQL database which I have added a new user (abbvs) to identified by a password.

The users I have:

mysql> select user,host from mysql.user;
+-------+-----------+
| user  | host      |
+-------+-----------+
| abbvs | %         |
| stnd  | %         |
| stnd  | 127.0.0.1 |
| root  | 127.0.0.1 |
| root  | ::1       |
|       | ATTD      |
| root  | ATTD      |
|       | localhost |
| stnd  | localhost |
| root  | localhost |
+-------+-----------+
10 rows in set (0.00 sec)

I have added SELECT for all databases, and ALL PRIVILEGES to another followed by flush_privileges;

SHOW GRANTS gives me the following:

mysql> show grants for 'abbvs'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for abbvs@%                                                                                |
+-------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'abbvs'@'%' IDENTIFIED BY PASSWORD '####' |
| GRANT ALL PRIVILEGES ON `vardb`.* TO 'abbvs'@'%'                                                         |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

However, when I attempt to login via the CLI with the new user I get the following:

[root@ATTD ~]# mysql -u abbvs -p vardb
Enter password:
ERROR 1045 (28000): Access denied for user 'abbvs'@'localhost' (using password: YES)

Is there anything that could be causing this? All other logins work as expected.

This turned out to be the system lacking a localhost user as well as a remote (any host) user.

Once the additional user had been added for localhost everything worked as expected.


Error creating linked database to oracle CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD';

OPT=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

I get following error:

  A connection to a database or other service was requested using
             a connect identifier, and the connect identifier specified could not
             be resolved into a connect descriptor using one of the naming methods
             configured. For example, if the type of connect identifier used was a
             net service name then the net service name could not be found in a
             naming method repository, or the repository could not be
             located or reached

I can connect using sqlplus though and can get response from tnsping.

In a database link, you should use the net_service_name, i.e. the OPT string, instead of SERVICE_NAME identifier.

EDIT:

Example:

cat tnsnames.ora|grep TEST
TESTNS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=THE_HOST)(Port=THE_PORT)))(CONNECT_DATA=(SID=THE_SID)))

oracle@hostname> export ORACLE_SID=ANOTHER_SID
oracle@hostname> sqlplus username
Enter password:
Connected to:
<few lines of sqlplus banner>
SQL> create database link lll connect to <the_username> identified by <the_password> using 'testns';
Database link created.
SQL> select * from global_name@lll;

GLOBAL_NAME
--------------------------------------------------------------------------------
<the_global_name>

SQL>


MySQL Performance comparison between loading comma separted vs fixed-width large file I have a file with 50 Gb, 7 columns and fixed-with. I couldn't succeed to load the data in my database (MariaDB 5.5.31, Windows 7, MYISAM) that is: after 15 hours I stopped the loading process. Does comma seperated file may perform better than fixed-width?

First of all, don't use myisam.
Csv or fixed width, performance will be same. But if you load the dump in chunks it will be faster. Check out pt-fifo tool from Percona toolkit. It doesn't work well though if the dump contains newlines.


How SQL Server caches data in memory I have couple of questions on how caching of data works
Imagine we have below situation:

Server restarted or we just ran DBCC DROPCLEANBUFFERS

We have a Table1 that is 50 GB and has columns A, B, C, D, E.
Column A is clustered index key, columns B and C have nonclustered indexes on them.

When we do

select top 100 * from Table1

is the whole clustered index (table) being read from disk to memory, even if we need just 100 rows?
Or are just 100 rows (their data pages) read from disk to memory?
Same with the nonclustered index, when we do

select top 100 * from Table1 where column B = 'some value'

does the whole non-clustered index + clustered index get loaded into memory?
Or just 100 rows from the non-clustered index and 100 rows from the clustered index?

Test Data

CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL
                     ,B varchar(255),C int,D int,E  int);

INSERT  INTO dbo.Table1 WITH(TABLOCK)
(B,C,D,E)

SELECT TOP(1000000) 'Some Value ' + CAST((ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 400) as varchar(255))-- 400 different values
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

CREATE INDEX IX_B
On dbo.Table1(B);
CREATE INDEX IX_C
On dbo.Table1(C);

Q1

  When we do "select top 100 * from Table1" - whole clustered index
  (table) is being read from disk to memory, even if we need just 100
  rows ? or just 100 rows (their data pages) are read from disk to
  memory ?

Only 100 rows from the clustered index  are read.
As a result only their data pages are cached into  memory.

Example

In my case it reads downward from the clustered index, 1 - 100 values for the column A.

SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1

The reads:

Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

4 logical reads = 4 pages

Why are we still seeing a scan count = 1, but only 4 logical reads you might ask?

Because it is executing a range scan:

to satisfy the top operator.

Q2

  Same with nonclustered index, when we do "select top 100 * from Table1
  where column B = 'some value'", whole nonclustered index + clustered
  index gets loaded into memory ? or just 100 rows from nonclustered
  index + 100 rows from clustered index ?

Example 1

SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1
WHERE B='Some Value 200'

You might expect for the nonclustered Index to be used here, but in fact the clustered index is still used:

With after clearing the cache 287 logical reads & 2528 read ahead reads.

Table 'Table1'. Scan count 1, logical reads 287, physical reads 1, read-ahead reads 2528, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

We are caching these read ahead reads after reading them from disk to then read 287 pages from memory.

  The read-ahead mechanism is SQL Server’s feature which brings data
  pages into the buffer cache even before the data is requested by the
  query.
  Source

If we checked the cached pages:

cached_pages_count  objectname  indexname   indexid
2536    Table1  PK__Table1__3BD019AE039DA497    1

We see this also shown.

So in this case we are caching some more pages to satisfy the query faster, since we are reading more rows to apply a residual predicate on them:

But we are only reading these pages from the clustered index.

Example 2

You could change the query in the hope of utilizing the nonclustered index:

select top 100 B from dbo.Table1
WHERE B='Some Value 200';

Which it does:

This again gives a small range scan:

Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Only the nonclustered index pages are loaded into memory here.

Example 3

We might force a key lookup to satisfy query from example 1 by using an index hint:

SET STATISTICS IO, TIME ON;
select top 100 *
from dbo.Table1
WITH(INDEX(IX_B))
WHERE B='Some Value 200';

This again results in some read ahead reads and more logical reads than example1:

Table 'Table1'. Scan count 1, logical reads 684, physical reads 3, read-ahead reads 465, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Since we are doing a key lookup from the nonclustered to the clustered index we will be caching pages from both indexes:


MongoDB - temporarily turn shard to standalone for local user creation Following question Mongodb sharded cluster backup user issue
I have a sharded mongodb cluster, with 4 shards, and I'm required to stop the mongod service on each shard, and start it not as a part of a shared cluster, but as a standalone with no authentication.

Today, each shard is configured with the options:

shardsvr=true
keyFile=/etc/mongodb.key

If i comment out these 2 options, will I be able to start the service as standalone with no auth?

Moreover, is there any possible impact on the data/configuration once i finish what I need to do and uncomment the both configuration options?

Thanks in advance

If you comment out keyFile (assuming you have not specified auth explicitly) you will turn off authentication.  Basically keyFile implies auth but not the other way around.

When you comment out shardsvr, the port will change from 27018 to the default port number - 27017.  That is actually a good thing.  When you take a node out of a replica set or a sharded cluster you want to change the port so that the rest of the set/cluster does not attempt to continue to communicate with the node you are working on.  Then, once you restore the original configuration, it will rejoin the set and cluster as normal.

Although the port will change when you do this, I tend to prefer explicitly specifying the port number to use for maintenance (30000 is my preference) rather than rely on default settings.  Also, I tend to have plenty of instances running on my hosts, so 27017 may well already be in use.

Once you have made your changes, all you have to do to restore the node in question is restart and switch back to your original settings.  This should be a relatively quick maintenance, so you should not have to worry about a node going stale (can be a concern with prolonged work).


Out of memory mysql was aborted I am using windows 2003 server, with 8G of RAM and MySQL was installed on It.

I am getting error as follows

out of memory.[ERROR] mysqld: Out of memory (Needed 523712 bytes).

The configuration which i have given are as

max_connections=5000
query_cache_size=16M
table_cache=256
tmp_table_size=103M
thread_cache_size=12
myisam_max_sort_file_size=1M
myisam_sort_buffer_size=205M
key_buffer_size=175M
read_buffer_size=512k
read_rnd_buffer_size=1M
sort_buffer_size=2M
innodb_additional_mem_pool_size=12M
innodb_log_buffer_size=8024K
innodb_buffer_pool_size=1024M
innodb_log_file_size=170M
innodb_thread_concurrency=24

Can any one please tell me the solution.?why this is happening.

The main problem most of the time is buffer_pool_size, key_buffer_size , etc in My.cnf
This in depth post from @RolandoMySQLDBA should solve your problem:

Why does MySQL say I'm out of memory?


SQL Server 2016 still uses mixed_extent_allocation in TempDB Today I was troubleshooting a problem on TempDB on SQL Server 2016 SP2 using extended events and added the mixed_extent_allocation and transaction_log event to my trace.

I expected mixed_extent_allocation wouldn't show up in my results because following query returned 0 as result:

select is_mixed_page_allocation_on
from sys.databases
where database_id=2

But to my suprise this event showed up several times. This was preceded by a transaction_log event with SGAM as context and operation LOP_SET_BITS.

This made me curious and I checked the content of the first SGAM page of TempdB:

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

This is a snippet of the result:

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

This made me conclude that mixed extents are still used.
I thought SQL Server 2016 only uses uniform extents (except for master, msdb and model). Or is my conclusion wrong?

Why am I still seeing the mixed_extent_allocation event popping up?

According to this post, IAM pages are still allocated from mixed extents, even in SQL Server 2016.
So this will be the mixed_extent_allocation I saw in my trace.

The blog post by Paul Randal provides some more internals explanation.
Thanks to Scott Hodgin for pointing me to the article.

I did soms more research about the content of de pages allocated in the first SGAM page and found out that SQL Server also uses mixed extents for system tables (object_id lower than 100).


Update a table with data from another table I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. Nothing I have tried has worked. Any thoughts?

Table1

  user_id|contact_email |contact_name
 =======================================
 123     |test@test.com |deft
 124     |test3@test.com|deft3

Table2 (User table)

 user_login|user_email     |display_name
 =======================================
 123       |test@test.com  |deft
 124       |test3@test.com |deft3

I have tried:

        UPDATE Table1
        SET contact_email = (SELECT Table2.user_email
                 FROM Table2
                 WHERE Table2.user_login = user_id )
        WHERE EXISTS (SELECT Table2.user_email
                 FROM Table2
                 WHERE Table2.user_login = user_id );

You can use next syntax:

update tbl1
join   tbl2
on     tbl1.user_id = tbl2.user_login
set    tbl1.contact_email = tbl2.user_email;

select * from tbl1;

user_id | contact_email  | contact_name
------: | :------------- | :-----------
    123 | test@test.com  | deft
    124 | test3@test.com | deft3

dbfiddle here


Display a one-to-many relationship in one row I have a "one to many" relationship in two tables:

Table "products" contains: product_id, product_descr
Table "packing" contains: product_id, package_id, package_desc

One product has often more than one package. I want to create a query in Microsoft Access, where I get all the package options for one product in one row:

product_id package_id1 package_desc1 packageid2 package_desc2 packageid_n package desc_n

The products do have a different number of packages, it's not a fixed number.

I could achieve your query in three steps. Assuming your table values are like the values listed below:

Step 1: In SQL View paste this query and save it as part1, This is for package_id transpose

TRANSFORM First([packing].package_id)
SELECT [packing].[product_id]
FROM packing
GROUP BY [packing].[product_id]
PIVOT [packing].package_id;

Step 2: In SQL View paste this query and save it as part2, This is for package_desc transpose

TRANSFORM First(packing.package_desc)
SELECT packing.product_id
FROM packing
GROUP BY packing.product_id
PIVOT packing.package_id;

Step 3: Join above two queries based on product_id to get your result


How do I find gaps between ranges of numbers? Consider the following table:

T_ID | T_START | T_END
-----+---------+------
   1 | 0.25    | 0.5
   2 | 0.8     | 1
   3 | 0.4     | 0.6
   4 | 0.2     | 0.3
   5 | 0.7     | 0.8

T_ID is unique. Each row represents a continuous range of numbers and is a subset of 0 to 1. T_START is less than T_END.

I need to identify any ranges not included between 0 and 1. Note that some ranges do overlap. The exclusivity of the endpoints is not relevant for my use case; I only need to identify what the endpoints of the gaps are. (As such, single point gaps are not a consideration.)

For this specific data set, I'd expect the result to be

GAP_START | GAP_END
----------+--------
0         | 0.2
0.6       | 0.7

The actual data set is large and will be aggregated over some other data (hundreds of thousands of rows, with possibly 100 row per aggregate group), so performance is important. (Answers that are not performant but may be improved are welcome, though.)

I considered trying to first determine what the covered ranges are and then trying to reverse that, but I haven't been able to figure out how to even compute the covered ranges. A simple GROUP BY is insufficient, since we have chains of overlapping ranges that would be merged into a single range, even though not all ranges overlap each other. I am of the mind that a recursive query might be helpful, but I haven't been able to figure out the logic for it yet.

I've created a SQLFiddle with this example dataset.

I am, sadly (and unproductively), not at liberty to modify the underlying representation.

After a pretty good amount of digging, I found this blog post on doing this for date ranges, and it turns out to work equally well on numeric ranges, with a little modification for handling the 0 and 1 endpoints.

Taking the query from there and renaming the columns to be a bit more clear, we have

SELECT * FROM
  (SELECT MAX(T_END) OVER (ORDER BY T_START) GAP_START,
          LEAD(T_START) OVER (ORDER BY T_START) GAP_END
   FROM T)
WHERE GAP_START < GAP_END

This warrants quite a bit of explanation. Step by step:

For each row, it gets the maximum T_END from all rows where T_START is less than or equal to the current row's T_START. This gives us the biggest range end of all the ranges that overlap this row's range start.
For each row, it gets the T_START of the first row where the T_START is greater than the current row. This gives us the next biggest range start.
It filters out any rows where the maximum T_END is greater than or equal to the next T_START. This means that the biggest overlapping range end extends out to or beyond the next range start. In other words, there is no gap between this row and the next, either because this row ends where the next begins or because other overlapping rows cover all points between the two.

This doesn't quite handle the requirement of all gaps over the range of 0 to 1, though. It only picks the gaps between the minimum range start and the maximum range end of all the rows. To check for gaps at the ends of the 0 and 1 range, we can use the following queries.

For gaps starting at 0:

SELECT 0 AS GAP_START, MIN(T_START) AS GAP_END
FROM T
HAVING MIN(T_START) > 0

For gaps ending at 1:

SELECT MAX(T_END) AS GAP_START, 1 AS GAP_END
FROM T
HAVING MAX(T_END) < 1

Both of these queries will return no rows if there is no gap at the endpoint.

Putting it all together into a single query:

SELECT * FROM
  (SELECT MAX(T_END) OVER (ORDER BY T_START) GAP_START,
          LEAD(T_START) OVER (ORDER BY T_START) GAP_END
   FROM T)
WHERE GAP_START < GAP_END
UNION ALL
SELECT 0 AS GAP_START, MIN(T_START) AS GAP_END
FROM T
HAVING MIN(T_START) > 0
UNION ALL
SELECT MAX(T_END) AS GAP_START, 1 AS GAP_END
FROM T
HAVING MAX(T_END) < 1

The result is exactly as stated in the question, ignoring order. SQLFiddle of the query.


The transaction log for database 'database' is full due to 'LOG_BACKUP'. Nothing works I have about 30 databases on SQL server 2012. One of the databases is erroring with "The transaction log for database 'database' is full due to 'LOG_BACKUP'."

I have spent several hours googling and implementing the suggestions.

Here are the things I have tried:

Changed recovery to simple.
Did a shrink files on log file type: now it shows allocated is 1mb and freespace is 60%.
Ran DBCC OPENTRAN: no open transactions
Restored the database from backup
Detached then deleted the log file and attached without a log so it created a new log file.
Check disk space: 35 gigs free.

None of that worked I still get the same error. Any ideas? I am desperate!!!!

Log file growth is set to unlimited and auto growth is enabled. There are no relevant file system errors or SQL Server errors in the Event Viewer. I only have one drive on this server so I cannot move to another disk.

Results of DBCC LOGINFO():

@@VERSION:

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
      Dec 28 2012 20:23:12
      Copyright (c) Microsoft Corporation
      Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: )  (Hypervisor)

The error occurs constantly, always the same error even after reboot.

Overnight the database came back online and all is working again! I did nothing to it, it just started working. Needless to say I am baffled and weary. I would like to keep this topic open for a while in case it happens again.

UPDATE:

The issue reocurred on the 19th, this is close to one week after it originally occurred. This time I noticed an error in the system events around the time it went offline:

sqlservr (1376) An attempt to open the file "C:\Windows\system32\LogFiles\Sum\Api.log" for read only access failed with system error 5 (0x00000005): "Access is denied. ".  The open file operation will fail with error -1032 (0xfffffbf8).

Performing full database backups does not truncate the transaction log.  In order to truncate the transaction log you have to perform a transaction log backup.  If you changed the recovery model to simple and still received the error, then this means you had active transactions that could not be cleared by checkpoint.

Relying on autogrow for capacity planning is not a good idea.  A better idea is to set your transaction log large enough so that auto grow does not occur.  I don't see where you tried to manually increase your transaction log size in your troubleshooting steps, nor do I see where you tried to perform a transaction log backup.

Another thing you could have tried is to add another transaction log file, just to see if that would resolve the error and allow processing to continue.  I would remove that additional file after completing my troubleshooting.

There are a lot of possibilities here, so I would recommend setting the log size larger than it was before, change the autogrow settings larger than 1mb and set up frequent, regular transaction log backups to keep the tranlog size on the smaller side.

If you don't want to mess with tranlog backups, then keep the recovery model on simple, but realize this means you will only be able to restore back to your last full backup, and it is still possible for a tranlog to fill up when in simple recovery mode.


Can SQL Server Transactional Replication do this? We have a production server and a reporting server, with transactional replication in place to move all inserts/updates/deletes from production to reporting. We're using SQL Server 2008 R2.

We have one table with ~2.5B rows in it, and we need to add a new column to the table, and then put a calculated value in the column for each of the 2.5B rows. We'll create a T-SQL script that populates the new column with values. We want the new column and values replicated to reporting.

What we initially considered doing is to create a SQL job that executed every 30 seconds, and the job would have an embedded T-SQL statement that updated a batch of 25,000 rows for each iteration. At this rate, we determined we wouldn't overwhelm replication, even though it would take a long time to backfill all the rows. But this isn't a problem.

We just had one of our DBAs tell us that if we put the T-SQL that updated the 25,000 rows each time it's run, into a stored proc, and put the stored proc on both the production server and reporting server, then replication could be set up so that when the stored proc is run on the production server, the 25K rows that are updated will not replicate to reporting, but the stored proc on the reporting server will get called and update the same batch of rows on the reporting server, thus, essentially, eliminating any chance replication will get backed up on this table because, essentially, nothing is getting replicated.

I'd never heard of this being possible before. Can this be done? The DBA that is recommending this works remotely (out of the country) and I'm not able to talk to him about this at the moment (he's off for a while). But he is an experienced DBA. If replication cannot do this, what might he have in mind?

I believe your DBA is referring to Publishing Stored Procedure Execution in Transactional Replication.

With Transactional Replication you have the option to published the definition of the stored procedure or the execution of the stored procedure.  Publishing stored procedure execution can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.

If you have anymore questions please let me know. I hope this helps.


Append Records to a fixed number of columns using dynamic data I have a table with 22,000 records formatted like so:
Input
[ID],[Cost Center],[Function]
1,01A1001710,GF000001
2,01A1001710,GF000802
3,02R1012000,GF000001
4,02R1012000,GF000059
5,02R1012000,GF000013

What I would like to do is append these to a new table formatted like so:
Output
[Cost Center], [Node1], [Node2], [Node3], [Node4], [Node5], [Node6]
01A1001710,GF000001,GF000802
02R1012000,GF000001,GF000059,GF000013

What is unknown is the number of [Function] per [Cost Center] it could range from a single function to a maximum of 6.
I tried to pivot but wound up with 60 different columns representing [Function]

[], pivot are hints it's MS SQL. Then

select [Cost Center]
  , [Node1] = max(case rn when 1 then [Function] end)
  , [Node2] = max(case rn when 2 then [Function] end)
  , [Node3] = max(case rn when 3 then [Function] end)
  , [Node4] = max(case rn when 4 then [Function] end)
  , [Node5] = max(case rn when 5 then [Function] end)
  , [Node6] = max(case rn when 6 then [Function] end)
from (
    select [Cost Center],[Function]
     , rn = row_number() over(partition by [Cost Center] order by [Function])
     --  or may be ..order by [id]) depending on column sequence you need.
    from youTable) t
group by [Cost Center];


SQL Server 2012 Rebuild RAID Array We had a bunch of disks fail in the RAID 10 that holds the data, logs, and backups drives on a SQL Server 2012 on Win 2012R2 OS. The OS and DB Server instance are in a separate RAID array.

Working with Dell, they recommend replacing the failed drives and then rebuilding the RAID array because the array has 300+ consecutive bad blocks. At this time, there is no obvious infidelity of data.

What would be the best steps to save my data, rebuild the array and restore the data? Would it be better to restore from backups or attach physical files? What happens to your SQL Server instances if there is no Master DB? It seems to me there are 2 possible scenarios

SCENARIO 1

Take the DBs offline or single user, backup all the DBs off the server, rebuild the RAID, reinstall SQL Server if necessary and restore all the DBs from backups.

SCENARIO 2

Turn off SQL Server services. Change SQL Server services to manual start. Copy all the log (.ldf) and data (.mdf, .ndf) files off the server, rebuild the array, copy the files back, and restart SQL services. Maybe I should take the backups first in this scenario as well.

What do you think?

It is a bad thing to put backups and live files on the same san array (or even on the same SAN). Your first step should be to allocate new storage for the backups, copy the backups on to that storage and then verify at least one of those backups.  Then you should run check DB on the existing database to ensure it is not corrupt. After that create a fresh backup onto the separate storage for backups.  For extra safety I would also take the databases offline, copy the MDF and ldf files onto the backup storage or another separate storage unit and then, after the array rebuild, see if the databases will come back up and pass another check DB. If not, then you can restore the backup or copy the file copies you made from the separate storage back to the live disk. Oh and you should enable checksum on the backup if you havent already done that.  Having copies of the mdf and ldf files will give you a last resort option of data repair if the database was already corrupt.


Insert openquery results into an existing table I have a stored procedure that queries a linked DB2 server in this fashion:

SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')'
exec sp_executesql @sql

I am trying to store that result into a new table via another stored procedure

    INSERT INTO [schema].[Table]
               ([column1]
               ,[column2])
    EXEC('schema.StoredProc')

Where StoredProc contains the aforementioned openquery

However, when I execute the second Stored Procedure (INSERT INTO), the output window prints out the text of @sql from my first stored procedure, and gives me the message:

SELECT * FROM openquery(DB2,'SELECT column1, column2 FROM table')
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Any help is appreciated

I will give you an example that has been working for me, it will give you an idea:

DECLARE @Radhe  TABLE(
    [n] [bigint] NOT NULL,
    [Login_Name] [nvarchar](128) NOT NULL,
    [Login_From] [nvarchar](128) NULL,
    [Account_Type] [nvarchar](128) NULL,
    THE_ORDER INT NOT NULL
    PRIMARY KEY CLUSTERED (Login_Name,N)
)

        SELECT @SQL = 'SELECT
                        [n],
                        [Login_Name],
                        [Login_From],
                        [Account_Type],
                        the_order
                      FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO @Radhe
        exec ( @sql)


Selecting rows from same name table from multiple databases in single result set For some reason, I have a table Table_1 in n databases (same name and schema structure) instead of one single databases. The columns are like this:

  | Column_1 | Column_2 |

I can select data from those n databases by running this query on each of them:

use Database_n
select * from Table_1

Problem: This will require me to run this query n times. Which will return n result sets as shown in the below image:

Question: Is there a way to get the data from all those databases in one single result set like this:

Sure :

    select * from database_n.dbo.Table_1
    UNION ALL
    select * from database_n+1.dbo.Table_1
    UNION ALL
    select * from database_n+2.dbo.Table_1
    -- etc.


Do SQL OS Background Tasks Yield to Normal Tasks? I have a SQL Server 2019 server that is running at about 22% CPU.  I look at what's running and I see that about half of that 22% is coming from background tasks (i.e. the status is "background") such as QUERY STORE APRC, GHOST CLEANUP, and TASK MANAGER.
So, if the server were to become busy running queries for the application, would those background tasks receive a lower priority or a smaller quantum or something that would see them give back the majority of the CPU they are using?
Basically, I keep getting asked what's using the CPU and I answer that about 50% of the CPU usage comes from background tasks.  Then I start getting asked how to turn them off.  If I can say that the background tasks are safe to ignore, I can make that stop.  Unfortunately, I have not been able to find anything definitive to support that assertion.

The background tasks are there for a purpose & are generally necessary so everything else being equal I wouldn't try and turn them off. I believe that some of them at least do act as you describe and reduce their pressure when CPU load is high - ie Query Store.
A detailed answer to this question would be level 500 internals and extremely long - probably better to wait until your CPU is stuck with a way higher load than 22%. There is undoubtedly lower hanging fruit to optimize


Problem with Script to Synchronize Login on AlwaysON I'm trying to resolve an issue. I've a two nodes with Always On enabled, I want to synchronize user over the nodes. I see that fantastic script.

https://maq.guru/synchronizing-sql-server-logins-in-an-always-on-availability-group/

Only one problem, when an user SQL have Policy enabled tghi script can't change password on other node.

Error message:

  Invoke-Sqlcmd : A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
  A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
  At C:\Users\Administrator\Desktop\AutoCloneLogins.Or.ps1:343 char:17
  + ...             Invoke-Sqlcmd -Query $Query -ServerInstance $SecondaryRep ...
  +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
      + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand**

Please help me to resolve this issue.

I would suggest using dbatools - Sync-DbaAvailabilityGroup with -Exclude <-- exclude the settings that you dont need to sync.

Honestly, you should sync all e.g. sp_configure, logins, jobs, etc


Getting related data from an uncertain column I am working on a friendship logic, the table friendship has this structure:

`friendship_id` INT NOT NULL ,
  `user1_id` INT NOT NULL ,
  `user2_id` INT NOT NULL ,
  `status` INT NULL ,
  `created` DATETIME NULL ,
  `message` VARCHAR(255) NULL ,

And the profile table.

`profile_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `first_name` VARCHAR(255) NULL ,
  `last_name` VARCHAR(255) NULL ,
  `image_url` VARCHAR(255) NULL ,
  `gender` INT NULL ,
  `city_id` INT NULL ,
  `country_id` INT NULL ,

I am trying to get a list of the the friends of a certain user, but I want related info also, for example their city.

The problem is the user_id could be either in user1_id or user2_id.

I can try with a left join but the results are not ok:

select
        city.name,
            p1.first_name as 'user1_first_name',
            p1.last_name as 'user1_last_name',
                p1.user_id as 'user1_id',
                p1.profile_id as 'user1_profile_id',
                p1.image_url as 'user1_image',
                p2.first_name as 'user2_first_name',
                p2.last_name as 'user2_last_name',
                p2.user_id as 'user2_id',
                p2.profile_id as 'user2_profile_id',
                p2.image_url as 'user2_image'
            from friendship f
            left join profile p1 on p1.user_id = user1_id
            left join profile p2 on p2.user_id = user2_id
            left join city on p1.user_id = 1 and city.city_id = p1.city_id or city.city_id = p2.city_id
            where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

Note: Probably my sql is not the best thing you'll ever see :)

What's the best way to get the information?

It looks like some of your difficulty may be order of operations related. For example, your

where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

is evaluated as

where f.user1_id = 1 or (f.user2_id = 1 and f.status = 2)

It may be more straightforward to start from the profile table of the user you're starting from :

from [profile] p1
left join city c
    on p1.city_id = c.city_id
left join friendship f
    on p1.[user_id] in (f.user1_id,f.user2_id)
    and f.[status] = 2
left join [profile] p2
    on p2.[user_id] in (f.user1_id,f.user2_id)
    and p1.[user_id] <> p2.[user_id]
where p1.[user_id] = 1


Monitoring PostgreSQL backup I am new to PostgreSQL and have questions regarding the backup procedure.

How do I check whether a PostgreSQL database was backed up successfully?
And how do I monitor the backup?

I have tried the following command to backup the database, but there's no output:

pg_dump [connection-option...] [option...] [dbname]

The test is to restore the backup somewhere else.

Monitoring can be done via the return code of pg_dump: if that isn't 0, something went wrong.


Is it possible to set timezone for a schema - Postgresql Is it possible to set the timezone for one schema? Do not want to set Postgresql(v.12) server level.

Please suggest if any ways to this.

No.
You can either set the timezone at database level, or at session level (... or directly when accessing a date-like datatype, using the at time zone construct).
From the documentation:

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19. There are also some special ways to set it:

The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.


Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing? Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column because the NEW.*/OLD.* format can't be used to compare json columns.
CREATE OR REPLACE FUNCTION on_insert_update_modified()
RETURNS TRIGGER AS $$
BEGIN
  IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
    NEW.modified_at = now();
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$ language 'plpgsql';

CREATE TRIGGER mytable_insert_update
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE on_insert_update_modified();

When I insert into the table which contains json and jsonb columns, I get the following error:
ERROR:  operator does not exist: json = json
LINE 1: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
CONTEXT:  PL/pgSQL function on_insert_update_modified() line 3 at IF

Is there a better way out of this?

Immediate problem: type json
There are no equality or inequality operators defined for the Postgres data type json. See:

How to query a json column for empty objects?
How to remove known elements from a JSON[] array in PostgreSQL?

Hence row comparisons involving a json column are also bound to fail - with notable exceptions, see below.
There are (imperfect!) workarounds, but don't bother and switch to jsonb, which allows these checks.
Fix function & trigger
You also cannot compare OLD and NEW for INSERT operations, where there is no OLD - only for UPDATE or DELETE. I suggest a column default for modified_at, or a separate trigger function and trigger BEFORE INSERT to overwrite any input with now() unconditionally.
And this function & trigger for UPDATE:
CREATE OR REPLACE FUNCTION on_update_modified()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW IS DISTINCT FROM OLD THEN
      NEW.modified_at = now();
      RETURN NEW;
   ELSE
      RETURN NULL;  -- skip empty update, see below
   END IF;
END
$func$;

CREATE TRIGGER mytable_update  -- doesn't work!
BEFORE UPDATE ON tbl_js
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();

db<>fiddle here (Postgres 9.6)
Nothing different in Postgres 13: db<>fiddle here
RETURN NULL; is optional. If the row has not changed at all (and while running the trigger function already anyway) you might as well skip the UPDATE for the row if it does not change it. That save the considerable cost of writing a new row version in vain. If other triggers depend on it, or for other exotic edge cases, you may still want to go through with it. Then RETURN NEW; - unconditionally at the end of the trigger function. RETURN OLD; would have no benefit after we have established both row values are identical.
Row comparison with json column can still work?
Oddly, yes. Row comparison is processed left-to-right. For the expression NEW IS DISTINCT FROM OLD Postgres can return true immediately on finding the first mismatch (before encountering the problem with json). This may or may not happen, depending on the code path. Quoting the manual on row-wise comparison :

Note
Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.


How can I keep two tables in two different databases with different schemas the same in real-time? I have two databases A, and B, which will reside on the same server. A and B have different schemas. Database B will contain different numbers of tables, and sometimes tables with the same name will contain extra columns in database B compared to A.

For some of the tables in Database B, Whenever its corresponding table in Database A is changed, the table in B needs to be updated immediately to reflect that change. In the case of extra columns in the Database B table, I might need to write additional logic to populate them.

If Database B cannot be updated, I want the update to Database A to fail too.

How could I set up such a scheme? Can I set up a trigger to automatically send the same update/insert/delete statement to Database B? Is there another way?

BTW, Database A = Sql server 2000, Database B = Sql server 2008.

Thanks in advance!

Background: There is a huge database of ~500 tables. There are many frontends that use this big database. I'm replacing the frontends one at a time. To prove that it integrates with all the legacy frontends I need to run a second system in parallel with their production system. To keep double data entry to a minimum I want to copy table modifications from the production system for all tables that aren't updated by my replacement frontend.

As there are two servers there are no real good options. Pretty much your only choice will be to use triggers in both databases that write to the remote database. You'll need logic in the trigger so that the trigger does nothing if the call is coming from the other SQL instance. Otherwise you'll end up with a deadlock every time.

If one instance is down than nothing will work until that server is back up and running.


SQL Server 2008: The login is from an untrusted domain and cannot be used with Windows authentication The question here is different from 1 & 2

When trying to connect to a SQL Server database that is on another system in the network both are in workgroup, when attempting to connect to a SQL Server 2008 instance using SQL Server Management Studio with Windows authentication, I get connected.

But when trying to connect from my application, I get the following error

  (Login failed. The login is from an untrusted domain and cannot be
  used with Windows authentication.) Native=18452).

If SQL Server Management Studio can connect, what would be required for my application to get connected?

If you are using Windows 7, you might have to "run as administrator".  This will allow the app to reuse your windows credentials and pass them to the SQL Server.  Otherwise, you will pass guest credentials to the SQL Server.


CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table @@Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a Geography datatype?

DECLARE @xPolygon NVARCHAR(MAX)
     = '53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670'

CREATE TABLE #xSpatialTable (
     id         INT IDENTITY (1,1)
    ,GeogCol1   geography
);

INSERT INTO #xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

The Second Compute Scalar operator in the actual execution plan for Expr1005 shows this

Scalar Operator(CONVERT_IMPLICIT(geography,[geography]::STPolyFromText((N'POLYGON(( '+[@xPolygon])+N'))',(4326)),0))

There is no such CONVERT_IMPLICIT if I change to using a user table instead of a temp table.

CREATE TABLE dbo.xSpatialTable (
     id         INT IDENTITY (1,1)
    ,GeogCol1   geography
);

INSERT INTO dbo.xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

Here's the actual execution plan

If I try the same test on a SQL 2014 database on the same machine, I don't get a convert_implicit whether I use a temp table or a user table.

Why the difference on 2008R2? and how do I get rid of the Convert_Implicit when using a temp table on 2008R2?

I don't think this has anything to do with the spatial extensions. I think this is this problem here,

From that post, all these work without generating CONVERT_IMPLICIT

SELECT * FROM [Table] WHERE IsSomething = 'TRUE';

SELECT * FROM [Table] WHERE IsSomething = Cast(1 as bit);

DECLARE @TRUE bit;
SELECT @TRUE = 1;
SELECT * FROM [Table] WHERE IsSomething = @TRUE;

You can read about the reasons for this problem here.

If it is the spatial type you may try a CAST to VARBINARY(max).. CAST( geography::STPolyFromText('') AS VARBINARY(max))

If you can't afford to upgrade to a newer version of SQL Server, PostgreSQL with PostGIS is perhaps an option as it is free and open source and has the best GIS implementation.

CREATE TEMP TABLE xSpatialTable (
     id         int           PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     GeogCol1   geography
);

INSERT INTO xSpatialTable (GeogCol1)
VALUES ( 'POLYGON((53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670))' );


SQL Server 2012 Can't log in with machine account We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, giving an error saying:

  Login failed for user 'DOMAIN\COMPUTER$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

I've verified that the account exists, and even deleted and recreated it

create login "DOMAIN\COMPUTER$" from windows

If I do it again, I get the response:

  Msg 15025, Level 16, State 2, Line 1
  The server principal 'DOMAIN\COMPUTER$' already exists.

(DOMAIN\COMPUTER is substituted for the real domain and computer name)

So it appears that the site is querying the SQL Server, but the SQL Server isn't looking at its logins very hard.

What have I missed?

It is most likely not the DOMAIN\computer$ at all. This message is known to be a misleading. I think all it wants is that  "NT AUTHORITY\NETWORK SERVICE" be granted the server access. Open SQL Server login properties and add this account. Also, check what user is used in the IIS to impersonate anonymous user, it should be something like "IUSR_ServerName". Grant access to this user as well


Long Running Query - Query Optimization I am running this syntax below on roughly 30,000 records.  The syntax is run 4 - 5 times a day and takes anywhere between 5 and 10 minutes to execute.  Below is sample DDL with a subset of data (not the 30,000 records) and I can upload my XML if someone will let me know how so you can see query execution.

If any one sees ways of optimization to reduce that lengthy time to return the data I will greatly appreciate it.

Declare @NumOfLeads Table
(
    clientname varchar(500)
    ,numoflicensesrequested int
    ,calldate date
)
Declare @PurchasedLicenses Table
(
    clientname varchar(500)
    ,numoflicensespurchased int
    ,purchasedate date
)
Insert Into @NumOfLeads (clientname, numoflicensesrequested, calldate) Values
('Client A', 10, '2017-01-01'), ('Client B', 5, '2017-01-02')
,('Client C', 7, '2017-01-01'), ('Client D', 12, '2017-01-03')
Insert Into @PurchasedLicenses (clientname, numoflicensespurchased, purchasedate) Values
('Client A', 10, '2017-01-10'), ('Client C', 5, '2017-01-15'), ('Client E', 10, '2017-01-15')
,('Client F', 11, '2017-01-15'), ('Client G', 5, '2017-01-22')

Select clientname, numoflicensesrequested
INTO ProdData
FROM @NumOfLeads
WHERE calldate BETWEEN '2017-01-01' AND '2017-01-31'

ALTER TABLE ProdData
Add numlicensespurchased int NOT NULL DEFAULT(0)

MERGE INTO ProdData pd
USING (
    SELECT clientname, numoflicensespurchased as CNT
    FROM @PurchasedLicenses
    WHERE purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
) pl
    ON pd.clientname = pl.clientname
WHEN MATCHED THEN
    UPDATE SET pd.numlicensespurchased = pl.CNT
WHEN NOT MATCHED BY TARGET THEN
  INSERT (clientname, numlicensespurchased, numoflicensesrequested)
  VALUES (pl.clientname, pl.CNT, '0');

Select * FROM ProdData order by clientname asc

EDIT
SQL XML Plan is here
https://www.brentozar.com/pastetheplan/?id=SJ16cKXkZ

It looks like for your final result set you want one row for each distinct value of clientname in @NumOfLeads and @PurchasedLicenses. If the client is in both tables then populate the numoflicensesrequested and numlicensespurchased columns. If not then populate the one column that you have. To me, that business logic sounds straightforward enough that you don't need to split it up into multiple queries. Sample data:

CREATE TABLE #NumOfLeads
(
  clientname varchar(500)
, numoflicensesrequested int
, calldate date
, PRIMARY KEY (clientname)
);

CREATE TABLE #PurchasedLicenses
(
  clientname varchar(500)
, numoflicensespurchased int
, purchasedate date
, PRIMARY KEY (clientname)
)

Insert Into #NumOfLeads (clientname, numoflicensesrequested, calldate) Values
('Client A', 10, '2017-01-01'), ('Client B', 5, '2017-01-02')
,('Client C', 7, '2017-01-01'), ('Client D', 12, '2017-01-03');

Insert Into #PurchasedLicenses (clientname, numoflicensespurchased, purchasedate) Values
('Client A', 10, '2017-01-10'), ('Client C', 5, '2017-01-15'), ('Client E', 10, '2017-01-15')
,('Client F', 11, '2017-01-15'), ('Client G', 5, '2017-01-22');

This query returns the same results as yours:

SELECT
  nol.clientname
, nol.numoflicensesrequested
, COALESCE(pl.numoflicensespurchased, 0) AS numoflicensespurchased
FROM #NumOfLeads nol
LEFT OUTER JOIN #PurchasedLicenses pl ON nol.clientname = pl.clientname
    AND pl.purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
WHERE nol.calldate BETWEEN '2017-01-01' AND '2017-01-31'

UNION ALL

SELECT
  pl.clientname
, 0
, pl.numoflicensespurchased
FROM #PurchasedLicenses pl
WHERE pl.purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
AND NOT EXISTS (
    SELECT 1
    FROM #NumOfLeads nol
    WHERE nol.clientname = pl.clientname
)
order by clientname asc;

I believe that the query could also be written with a FULL OUTER JOIN, but I'm not personally a fan of that syntax.

A few things worth mentioning:

Don't use table variables unless you need the functionality that they provide. Table variables don't have statistics so they can cause poor query performance if you use them in the wrong way. Personally, I only consider table variables if I need to persist data through a transaction rollback or if I have code that will run thousands of times per second (or more).

The fact that you're updating by clientname implies that it's the primary key of the data. Why not define it as the primary key?

For complex queries you can see performance gains by splitting them up into multiple steps by saving intermediate results to temp tables. I would only try that if I couldn't get good enough performance with a single query. For this data and business logic I think that you can just write a single SELECT query. For other queries which are too complex, try doing inserts into temp tables. That will give you minimal logging and other benefits. Avoid doing updates (when practical), and avoid using MERGE unless you really need its functionality.


Can the subquery be simplified/optimized? Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the ARRAY function; but the ARRAY function and everything inside is fair game. I want to remove the subqueries if at all possible; and if not speed up the query as much as possible.

SELECT json_build_object('last', max(day), 'missing_days', ARRAY(
             SELECT distinct to_char(dates, 'YYYY-MM-DD')
             FROM generate_series('2009-12-20', CURRENT_DATE, interval '1 day') AS dates
             WHERE dates NOT IN (SELECT day FROM records))
       ) AS jsobj
FROM records;

I thought I remembered from my database design classes that you should be able to use columns from the records table in the sub-query but I could be mistaken, or that might be database dependent, or I could have been trying the wrong syntax; anyway it didn't work when I tried it.

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row.

It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which is cast to date (I assume) and this cast depends on your current timezone setting. Results for the same query may be different with a different setting for the calling session.

It's a bit cleaner and cheaper to work with timestamp to begin with. See:

Generating time series between two dates in PostgreSQL

And NOT IN (<subquery>) is almost always a bad choice. There are faster options which are less error-prone. NOT IN carries traps for NULL values.

Select rows which are not present in other table

Each of these queries should be faster. The winner depends on data distribution in your table. Test both:

Query 1 (returns text like your original):

SELECT to_char(day, 'YYYY-MM-DD')
FROM   generate_series(timestamp '2015-12-20', LOCALTIMESTAMP, interval '1 day') d(day)
WHERE  NOT EXISTS (SELECT 1 FROM records WHERE day = d.day);

Query 2 (returns date):

SELECT generate_series(timestamp '2015-12-20', LOCALTIMESTAMP, '1 day')::date AS day
EXCEPT ALL
SELECT day FROM records;   -- data types must match


How to categorize records in groups in PostgreSQL? Here is a same schema with data,

CREATE TABLE groups (
  id       int            PRIMARY KEY,
  name     varchar(30)
);
CREATE TABLE emp (
  id       int            PRIMARY KEY,
  name     varchar(50),
  group_id int            REFERENCES groups
);
INSERT INTO groups(id,name) VALUES
  (1, 'Managers'),
  (2, 'Teachers'),
  (3, 'Cleaners'),
  (4, 'Drivers');
INSERT INTO emp(id,name,group_id) VALUES
  (1,'john',1),
  (2,'Smith',1),
  (3,'Evank',2),
  (4,'Doe',3),
  (5,'Jane',4);

Now I need to categorize groups by the title of Site and have the following result:
I want to count all managers and teachers as site1, cleansers as site2, Drivers as site3 and so on.

Site      Employees
Site1         3
Site2         1
Site3         1

There is no "sites" field in your SQL statements.

I did the following to solve your problem:

(You'll notice that I've taken many more fields than are necessary to do the operation - it's so that you understand where the data is coming from. I often find it very helpful to include far more fields than required in my initial attempts at formulating SQL and then I pare them down as required by the problem!).

WITH emp_group_join AS
(
  SELECT e.id AS eid, e.name AS ename, e.group_id AS egid,
         g.id AS gid, g.name AS gname
  FROM emp e
  JOIN groups g ON
     e.group_id = g.id
),
sites AS
(
  SELECT egj.*,
    CASE
      -- This is necessary to distinguish between Managers + Teachers
      -- and other categories of worker - of course, if you add a site
      -- field, you may not have to do this!

      WHEN gname = 'Managers' THEN 'White Collar'
      WHEN gname = 'Teachers' THEN 'White Collar'
      ELSE gname
    END AS work_type
  FROM emp_group_join egj
)
SELECT work_type, COUNT(work_type) FROM sites
GROUP BY work_type
ORDER BY work_type DESC;

and the result is:

  work_type   | count
--------------+-------
 White Collar |     3
 Drivers      |     1
 Cleaners     |     1
(3 rows)

This is how to do it using a subSELECT (same result):

SELECT work_type, COUNT(work_type) AS wt_count
FROM
(
  SELECT e.group_id,
         g.id,
         g.name,
         CASE
           WHEN g.name = 'Managers' THEN 'White Collar'
           WHEN g.name = 'Teachers' THEN 'White Collar'
           ELSE g.name
         END AS work_type
  FROM emp e
    JOIN groups g ON
    e.group_id = g.id
) AS join_tab
GROUP BY work_type
ORDER BY wt_count DESC;

You could try running the WITH clause SELECT and/or the subSELECT statements on their own to see in detail what's happening! p.s. +1 for providing DDL and DML!


Check if relation is in BCNF I have the following relation and function dependencies.

R = ABCDE
F: C->AB, D->A, BE->CE, E->B

I have to check whether this relation is in BCNF.

Wikipedia states:

  ...if and only if for every one of its dependencies X → Y, at least one
  of the following conditions hold:[4] X → Y is a trivial functional
  dependency (Y ⊆ X) X is a superkey for schema R

So first I went on to find the superkeys.

Since D is not on the right side, I'm assuming the superkey must contain D in it.

What I thought is that the only superkey is DE. Is this correct?

And if the only superkey is DE, then the relation is not in BCNF because none of the functional dependencies have the superkey on the left.

Can anyone point out if I'm mistaken?

"Armstrong's axioms are a set of axioms (or, more precisely, inference rules) used to infer all the functional dependencies on a relational database"(link).

R: ABCDE
F: C->AB, D->A, BE->CE, E->B

BE->CE can be split in BE->C and BE->E. The trivial functional dependency BE->E can be skipped. BE->C can be replaced by E->C because from BE->C and E->B one can deduce E->C. Therefore the set of functional dependencies can be reduced to:

R: ABCDE
F: C->AB, D->A, E->C, E->B

A and B cannot be member of a key because they cannot be found on the left site of a functional dependency. So it is sufficient to search all keys of the relational system (remove the Attributes A B from R and all functional dependencies containing A and B)

R: CDE
F: E->C

C is not on the left side of a functional dependcy so again one can reduce the system to

R: DE
F:

So the list F of functional identities is empty. The only key of this relational system is DE which is also the only key of the unreduced systems. So your answer of your first question is true.

You can use this page to find your candidate keys

To check if the system is in BCNF it is not necessary to find all candidate keys. It is sufficient to find one functional dependency which has a left side that is no a key.

C->AB is such a functional dependency: C is not a key because the closure of C is C. This means that no further attributes can be generated by applying functional dependencies on C.

You can use this page to find check if a relation is in normal form.