In addition to what Colin already cleared up:

SELECT * FROM crosstab(
   $$SELECT (applied_date AT TIME ZONE 'America/New_York')::date AS d
         ,applied_class
         ,sum(count(id)) OVER (PARTITION BY applied_class
                         ORDER BY (applied_date AT TIME ZONE 'America/New_York')::date)
   FROM   application_app
   WHERE  applied_class LIKE '%L13'
   GROUP  BY 1, 2
   ORDER  BY 1, 2$$

  ,$$VALUES ('BOSFAL13'::text), ('NYCFAL13'), ('CHIFAL13'), ('MULTIPLECITIESFALL13')$$)

AS ct ("Day" date, "Boston" int, "New York" int, "Chicago" int, "Any City" int);

Major points

The extracted day depends on the time zone setting of the current session. Your sample displays timestamp with time zone literals, which indicates you may be operating in multiple distinct time zones. To get the date for a particular time zone, you have to declare that, or the current setting will be applied - which is easily overlooked and therefore a loaded footgun: You would get different numbers, depending on where you run the query!
Details:

Ignoring timezones altogether in Rails and PostgreSQL
Accounting for DST in Postgres, when selecting scheduled items

Safe syntax would be:

to_char(applied_date AT TIME ZONE 'America/New_York', 'MM-DD')  -- your time zone

There is another trap. If you GROUP BY day and month only, you can easily aggregate dates from multiple years by accident. I use the full date in my example.
Plus, skipping the key word AS is fine for table aliases but not for column aliases. So:

(applied_date AT TIME ZONE 'America/New_York')::date AS d

See:

SELECT Query merge / join two tables in PostgreSQL

You don't need date_trunc() here. to_char() can work with the original timestamp directly.

to_char(applied_date, 'MM-DD')

You do not need a subquery. Window functions can be computed OVER aggregate functions. sum(count(id)) OVER ... in my query. More explanation in this related answer on SO:

Get the distinct sum of a joined table column

You'd have to test which is faster. I don't expect much of a difference.
It's more efficient to invert your use of dollar-quotes and plain quotes.
It's sufficient to declare the type in the first row of a VALUES expression.


Can I give the same name to a table and a stored procedure? Is it possible to name a stored procedure the same as a table? I have an existing table named 'buyers', and I'm trying to create a stored procedure named 'buyers', but I'm getting this error:

  There is already an object named 'buyers' in the database.

No. This is clearly stated in the MSDN documentation:

  Procedure names must comply with the rules for identifiers and must be
  unique within the schema.

Since you have a table with the same name, in that schema, you cannot create the stored procedure with that name.


What is the difference between an after update and a before update in PostgreSQL What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was always executed before update.

So I made the following example:

I made a function that updates a table when the status is typing but with a delay of 10 seconds.

CREATE OR REPLACE FUNCTION fai_prueba()
  RETURNS trigger AS
$BODY$
begin
    if new.status = 'Typing' then
        update image set status = 'ToTyping', path = 'Real path' from pg_sleep(5) where id = old.id;
    end if;
    return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fai_prueba()
  OWNER TO postgres;

then I have the following trigger

create trigger tai
after update on image
for each row execute procedure fai_prueba();

but when I run an UPDATE the query does not end until the delay ends

UPDATE image
    SET  path='fake path'
       , status= 'Typing'
WHERE id=5;

>Query returned successfully: 0 rows affected, 10042 ms execution time.

So is it possible that the update query ends before the trigger?

There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless.

In a BEFORE trigger, the trigger is executed BEFORE the DML statement is executed..  So you have a chance to modify the :NEW (old :OLD in case of DELETE) row BEFORE it is inserted/updated/deleted...  But anything you want to do you can do (like check data in a separate table and issue an update/insert/whatever in that table, etc).  If, for some reason, the trigger causes an EXCEPTION, then execution ceases and the DML statement is never executed.

In an AFTER trigger, the trigger is executed AFTER the DML statement is executed.  You have already lost any ability to modify the :NEW and :OLD records and have it actually mean anything.  However, you can still do whatever you want in the trigger.  If the trigger causes an exception, for PostgreSQL, this will cause a rollback of the original DML. I do not believe this is true for all RDBMSs - but am having a hard time finding an example right now.

A common use of a BEFORE trigger is to set a timestamp column to "now" before the data has been inserted.

A common use of an AFTER trigger is to populate an audit/history table with the changes.

In either case, you would usually want to issue a ROLLBACK if you find an exception has been raised.


slow load speed of data from mysqldump I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe 20 minutes. It generates about 15GB of data. The largest dumped files are in the 2GB range.

When I load the data into MySQL on another box, a six-core, 8GB machine, it takes forever. Easily 12 clock hours or more.

I'm just running the mysql client to load the file, i.e.

mysql database < footable.sql

directly with the file directly out of mysqldump

mysqldump database foo > footable.sql

Clearly I am doing something wrong. Where do I start so it can finish in a reasonable time?

I'm not using any switches on either the dump or the load.

Take these some points in your consideration they may help you in case of generating the dump and restoring it.

Use Extended inserts in dumps.
Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
Import with multiple threads, one for each table.
Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
Turn off foreign key checks and turn on auto-commit.
If you are importing to innodb the single most effective thing you can do is to put innodb_flush_log_at_trx_commit = 2 in your my.cnf, temporarily while the import is running. you can put it back to 1 if you need ACID

Give it a try..


Very large subpartitions after table redefinition I'm trying to redefine a table to be partitioned. The original table has about 1.5M rows and about 150MB size.

select segment_type, segment_name, bytes / 1014 / 1024 as SIZE_MB from user_segments where segment_type = 'TABLE' and segment_name = 'WERTEVAR';

segment_type | segment_name | SIZE_MB
TABLE          WERTEVAR       153.543.......

What I did:

CREATE TABLE WERTEVAR_TMP(
  ...column definitions
)
TABLESPACE "USERS"
PARTITION BY RANGE (WVDATZEIT) INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY HASH (WVTEIL, WVMASCHINE) SUBPARTITIONS 8
(
    PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000',     'DD-MM-YYYY'))
);

BEGIN
    DBMS_REDEFINITION.start_redef_table(
        uname         => 'CHYSTAT_STAGING_MONITORING',
        orig_table    => 'WERTEVAR',
        int_table     => 'WERTEVAR_TMP',
        options_flag  => DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

SET SERVEROUTPUT ON
DECLARE
    l_errors  NUMBER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname            => 'CHYSTAT_STAGING_MONITORING',
        orig_table       => 'WERTEVAR',
        int_table        => 'WERTEVAR_TMP',
        copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
        copy_triggers    => TRUE,
        copy_constraints => TRUE,
        copy_privileges  => TRUE,
        ignore_errors    => FALSE,
        num_errors       => l_errors,
        copy_statistics  => FALSE,
        copy_mvlog       => FALSE
    );

    DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      => 'CHYSTAT_STAGING_MONITORING',
    orig_table => 'WERTEVAR',
    int_table  => 'WERTEVAR_TMP');
END;

DROP TABLE WERTEVAR_TMP;

Now when I look at the table size, it gets HUGE: about 23GB!!

select segment_type, segment_name, sum(bytes) / 1014 / 1024 as SIZE_MB from user_segments where segment_name = 'WERTEVAR' group by segment_type, segment_name;

segment_type       | segment_name | SIZE_MB
TABLE SUBPARTITION   WERTEVAR       23525.743.......

Where I can see that every single subpartition has exactly 8M. Did I something wrong? Are those subpartitions supposed to be large like this?

I can also see that those subpartitions have set:

PCT_FREE       = 10
INITIAL_EXTENT = 8388608
NEXT_EXTENT    = 1048576

I would change that INITIAL_EXTENT AND NEXT_EXTENT to be two orders of magnitude smaller. Something like 83886 and 10485. But I don't know if that is ok, or totally useless.

I would appreciate any hint.

Yeah, it is recurring problem.Starting with 11.2.0.2, the default segment size of partitions was increased from 64K to 8M. This means, even if your partition is empty, it still consumes 8 MB space right after its creation.

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

You should specify the parameters you mentinoned when you create the new table, for example: INITIAL EXTENT 65536 NEXT EXTENT 65536

Alternatively you could set a hidden parameter, so the database reverts to the old behaviour, session or database level:

alter session set "_partition_large_extents" = false;
alter system set "_partition_large_extents" = false;

But I would suggest specifying the extent sizes manually over modifying a hidden parameter.


SQL Server 2008 R2 Transactional Replication "Cannot insert explicit value for identity column..." Right now I'm having an "identity crisis" with transactional replication in SQL Server 2008 R2. The database is running in compatibility 90.  There's a table that has an identity column and won't replicate over. The error is "Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)".

The "not for replication" is set to true for that table. I can't find any settings for the articles to specify this as well.

Any ideas are appreciated.

Just to add that you don't have to drop and re-create the replication just to change the "not for replication" bit.

You can do it using T-SQL without generating a snapshot or breaking your replication --

sys.sp_identitycolumnforreplication

1 = not for replication

0 = for replication and this causes the problems with Identity colums on subscriber side

To change it for all the tables :

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

To change it for just 1 table, first find out the object_id of the table and then run below

EXEC sys.sp_identitycolumnforreplication table_object_id, 1

EDIT

Below tsql will give you a nice output with command that can be reviewed before running for entire database :

if exists (select 1 from sys.identity_columns where is_not_for_replication = 0)
begin
SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) as SchemaName,
        QUOTENAME(t.name) AS TableName,
        c.name AS ColumnName,
        c.object_id as ObjectID,
        c.is_not_for_replication,
        'EXEC sys.sp_identitycolumnforreplication '+cast(c.object_id as varchar(20)) + ', 1 ;' as CommandTORun_SetIdendityNOTForReplication
    FROM    sys.identity_columns AS c
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
        and c.is_not_for_replication = 0
end
else
print 'There are no identity columns that needs NOT FOR REPLICATION set to 1'

For adventureworks database :


search for something in the first 1000 records in the table If I do the following MySQL query

select * from table where name Like '%john%' limit 5;

that mean the query will search all the mysql database table and return only the top 5 results that match the query where condition.

BUT, what if want to just search the top 1000 records in the database table that match the where condition and return only the first 5 rows , because I have 1,000,000 records in the table and i just want to search in the first 1000 records, what should i use?

this is the wanted query in human readable:

search the top 1000 records from the table which have the name like john and return only the top 5 records that match.

what is the corresponding in MySQL

You can't do something like a 'first 1000' without an order, since each query could give you a different order if you don't specify it.
So, let's try this way:

select * from (select * from table order by PUTYOURORDERFIELDHERE LIMIT 1000) where name
like '%john%' limit 5;


Rights to grant for controlling an Availability Group We are having the following setting:
For an ERP Test / Accept SQL Cluster we want to follow the least privilege principle to give the external engineers rights on the SQL Server. We are running SQL Server 2016 in a cluster with two nodes. Synchronous commit, readable secondary on true. The engineers are added to an AD group. We grant permissions to that AD group.
The engineers need to be able to do the following on each cluster node concerning Availability Groups:

Remove database from availability group (for restore purposes)
Add database to availability group using automatic seeding

We have granted ALTER and CONTROL permissions on the Availability Group to the AD group on both nodes using the statement:
use [master]
GO
GRANT ALTER ON AVAILABILITY GROUP::[BSIBITSQLAG13] TO [BLIJDORP\DL_BSI_BiT_Red_SQL_DB_RW_Admins]
GO
use [master]
GO
GRANT CONTROL ON AVAILABILITY GROUP::[BSIBITSQLAG13] TO [BLIJDORP\DL_BSI_BiT_Red_SQL_DB_RW_Admins]
GO

After this the removing part works, the engineers are able to. But adding the database right back using automatic seeding is not working. Using the 'Add Database' wizard the validation step only shows green checkmarks. But when finishing the wizard the following error appears:

I tried giving the Availability Group create database permission because of this blog:
USE [master]
GO
ALTER AVAILABILITY GROUP [BSIBITSQLAG13] GRANT CREATE ANY DATABASE;
GO

This script worked, but didn't resolve the error at the other user (I'm sysadmin, they are not). I guess this is where the problem lies, we have to give the AD group rights to give rights to the Availability Group (to execute the above statement). I can't find out which statement to use. Also adding the securityadmins server role to the AD group doesn't work.

The Login must need following permissions to configure availability groups, if the login not already part of sys admin server role:

ALTER ANY AVAILABILITY GROUP
CONTROL AVAILABILITY GROUP
ALTER ANY DATABASE
CREATE ENDPOINT

For detailed information.. and Grant Server Permissions..


PostgreSQL 8.4 replication of more than 100 tables I am facing a problem with PostgreSQL version 8.4.

I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to replicate with some details and sequences separately..... Now imagine a database with more than 100 tables, that is my case, what should I do?

If I upgrade PostgreSQL to version 9X the database links with Oracle stop working, so I need to stay with 8.4 but I can't find a tutorial to replicate the entire database like I do in the mysql instances.

I will be more specific in the end of this tutorial learning how to use slony suggests:

set add table (id=1, set id=1, origin = @PRIMARY, fully qualified name = 'schema.tablename', comment = 'table');
set add sequence (id=1, set id=1, origin = @PRIMARY, fully qualified name = 'schema.sequencename', comment = 'seq');

(Link for the full tutorial: https://www.howtoforge.com/configuring-slony-i-cascading-replication-on-postgresql-8.3)

For each table i want, and don't say nothing about replicating the entire database, another tutorials says the same.

Please somebody know a good way to solve that? Maybe I will need some Shell Script?

The solution was to use Postgres version 9.1 and Oracle Version 11.2.0.1.0.

I am running oracle in Debian Wheezy all do i need is to do some upgrades in certain libraries and now it works good:

Version of ODBC:

ii  odbc-postgresql:amd64              1:09.01.0100-1+deb7u1         amd64        ODBC driver for PostgreSQL

Version of Libssl required by ODBC:

ii  libssl1.0.0:amd64                  1.0.1e-2+deb7u13              amd64        SSL shared libraries

Postgres version in the another server:

ii  postgresql-9.1                     9.1.15-0+deb7u1               amd64        object-relational SQL database, version 9.1 server

This is how do i install Oracle with PostgreSQL 9.1 datalink and its working very fine now, also with Mysql 5.5.

Think now i will be able with this new version to do easy replication just like i do in the Mysql instances!


Scheduled Job Does not work in oracle SQL*PLUS I am just a beginner in Oracle and can't seem to get this job running :-

I want to delete old rows from my database. And created a job as follows :-

CREATE OR REPLACE PROCEDURE delete_trips
AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Here');
        DELETE FROM trip y where sysdate > y.arrival_date;
    END;
/
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'delete_old_rows',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'EXECUTE delete_trips;',
        start_date      => sysdate,
        repeat_interval => 'FREQ=SECONDLY',
        end_date        => add_months(sysdate,12),
        auto_drop       => FALSE,
        enabled         => TRUE
    );
END;
/

After executing the above code I can see that the job 'delete_old_rows' is scheduled in DBA_SCHEDULER_JOBS, but neither the rows get deleted, nor output is produced in the terminal. I tried logging out and logging in again, and yet no row was deleted and no output was produced. And rows satisfying the condition for delete do exist. Please help. What am I missing ?

Try this:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'delete_old_rows',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'delete_trips;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=SECONDLY',
        end_date        => add_months(sysdate,12),
        auto_drop       => FALSE,
        enabled         => TRUE
    );
END;
/

You don't need the EXECUTE part in job_action.

You can check DBA_SCHEDULER_JOBS.NEXT_RUN_DATE to check when it's next due to be executed. Obviously you can also check the other relevant columns to see if it's actually executed and/or failed (LAST_START_DATE,FAILURE_COUNT etc). Table doc link.


It's possible to order alphabetically all columns in tables of a mysql db? It's a large db and it's really messy, I would like to order all columns in all tables, without resorting to manual drag and drop...

EDIT
I know the order doesn't matter. It's a design think. It will be much easier to read and to find things if they are ordered.
,I have also many columns like "is_visible", "is_editable" etc... if they are all together it looks better.

I would like to order all columns in all tables

The order of column [names] within a Table should not matter.

Every "select" statement that goes anywhere near your database should explicitly specify the columns that it wants, in the order that it wants them.  How the actual table is arranged matters not a jot.

If you have lots of columns with similar names, this may be indicative of a larger problem.


Using index on datetime field not working really Here is my query

select msisdn, sum(score) as score
from contest_player_update_history_ideacontest6_35
where request_date >='2015-01-14 00:00:00'
  and request_date <= '2015-02-03 23:59:59'
group by msisdn;

CREATE TABLE `contest_player_update_history_ideacontest6_35` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `msisdn` varchar(15) DEFAULT '',
  `request_id` bigint(255) DEFAULT '0',
  `transaction_id` varchar(255) DEFAULT '',
  `contest_id` int(10) DEFAULT '0',
  `request_date` datetime DEFAULT NULL,
  `zone` varchar(255) DEFAULT '',
  `operator` varchar(255) DEFAULT '',
  `circle` varchar(255) DEFAULT '',
  `short_code` varchar(255) DEFAULT '',
  `channel` varchar(255) DEFAULT '',
  `language` varchar(255) DEFAULT '',
  `score` double(10,2) DEFAULT '0.00',
  `request_type` varchar(50) DEFAULT '',
  `request_value` varchar(255) DEFAULT '',
  `ans_type` varchar(255) DEFAULT '',
  `qusetion_id` varchar(255) DEFAULT '',
  `time_taken` double(10,2) DEFAULT '0.00',
  `billing_amount` double(10,2) DEFAULT '0.00',
  `billing_validity` int(10) DEFAULT '0',
  `operation` varchar(255) DEFAULT '',
  `param` varchar(255) DEFAULT '',
  `param1` varchar(255) DEFAULT '',
  `param2` varchar(255) DEFAULT '',
  `param3` varchar(255) DEFAULT '',
  `question_level` varchar(255) DEFAULT '',
  `max_allowed_question` int(10) DEFAULT '0',
  `allowed_question_per_day` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mdn` (`msisdn`),
  KEY `score` (`score`),
  KEY `request_date` (`request_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Explain gives me

 1  SIMPLE       contest_player_update_history_ideacontest6_35  range   request_date   request_date  9        (NULL)  5365990  Using where

when date range is request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' Here key request_date key is being used.

and when I change the date range just includes another day
request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-04 23:59:59', No key is being used and whole table is being traversed which causing delay in query. And data selection goes from 5 million to 20 million just adding 1 day since whole table is being traversed.

explain gives me result as

1  SIMPLE       contest_player_update_history_ideacontest6_35  ALL     request_date   (NULL)  (NULL)   (NULL)  27221681  Using where; Using temporary; Using filesort

Using an index involves back-and-forth between the index BTree and the data BTree. So, if more than something like 20% of the table needs to be looked at, it is faster to simply scan the data (ignoring the index) and filter out those that fail the WHERE clause.


Update dynamic column names using keys from jsonb argument I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, but I'm trying to push as much code down into the db layer as possible.

This is the kind of thing I naively hoped might work:

CREATE PROCEDURE record_event(
  foo_arg integer,
  name_arg text,
  data_arg jsonb,
  occurred_at_arg timestamptz
)
AS $$
DECLARE column_name text;
BEGIN
  -- This part is incidental:
  INSERT INTO foo_event(foo, name, data, occurred_at)
    VALUES(foo_arg, name_arg, data_arg, occurred_at_arg);

  -- This is the part I'm struggling with:
  FOR column_name IN (SELECT * FROM jsonb_object_keys(data_arg)) LOOP
    PREPARE update_query(text, text, integer) AS
      UPDATE foo SET $1 = $2 WHERE id = $3;
    EXECUTE update_query(column_name, data_arg->>column_name, foo_arg);
  END LOOP;
END
$$ LANGUAGE plpgsql;

But pg doesn't like $1 in that position:

psql:src/db/migrations/foo/up.sql:43: ERROR:  syntax error at or near "$1"
LINE 15:       UPDATE foo SET $1 = $2 WHERE id = $3;

I also noticed there is a json_populate_record() function which seems like it should be helpful here, but I don't really understand how to apply it to my situation from the documentation.

Does what I'm trying to do make sense and is something like it possible?

Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling.

PROCEDURE calling UPDATE for each JSON key

Proof of concept to show what did not work in your attempt.

CREATE PROCEDURE record_event (
   foo_arg int,
   name_arg text,
   data_arg jsonb,
   occurred_at_arg timestamptz
) AS
$proc$
DECLARE
   column_name text;
BEGIN
   INSERT INTO foo_event
        (foo    , name    , data    , occurred_at)
   VALUES(foo_arg, name_arg, data_arg, occurred_at_arg);

   -- works, but inefficiently:
   FOR column_name IN
      SELECT * FROM jsonb_object_keys(data_arg)
   LOOP
      EXECUTE format('UPDATE foo SET %I = $1 WHERE id = $2', column_name)
      USING data_arg->>column_name, foo_arg;
   END LOOP;
END
$proc$  LANGUAGE plpgsql;

Call:

CALL record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

Column names cannot be dynamic, so format the query (with format() for convenience) and use EXECUTE. But values are better provided with the USING clause.

Note the format specifier %I, but the parameters $1 and $2 refer to values provided by the USING clause (not to function parameters!).

But I would not use it. Multiple UPDATE commands are pretty inefficient. Instead use a ...

Function with a single UPDATE

Should be much more efficient.

CREATE OR REPLACE FUNCTION record_event(
   foo_arg int,
   name_arg text,
   data_arg jsonb,
   occurred_at_arg timestamptz
)
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN
   INSERT INTO foo_event
          (foo    , name    , data    , occurred_at)
   VALUES (foo_arg, name_arg, data_arg, occurred_at_arg);

   SELECT INTO _sql
          'UPDATE foo SET '
       || string_agg(format('%I = %L', key, value), ', ')
       || ' WHERE id = ' || foo_arg
   FROM   jsonb_each_text(data_arg);

   IF _sql IS NOT NULL THEN
      -- RAISE NOTICE '%', _sql; -- uncomment instead of EXECUTE to debug
      EXECUTE _sql;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

We could pass values with another USING clause. But simply concatenate the whole command for convenience.

Using a FUNCTION as there is nothing that would require a PROCEDURE (new since Postgres 11). Would work as procedure too, though.

As always, properly quote identifiers and values to defend against possible SQL injection. I only dare to concatenate foo_arg directly, as an integer value is safe in this regard and works as unquoted numeric literal.

Note that column names are treated as case-sensitive.

Are PostgreSQL column names case-sensitive?

Related:

SQL injection in Postgres functions vs prepared queries
When to use stored procedure / user-defined function?
How to create a table dynamically based on json data?


Database design with different user roles connected by different relationships to one organizaiton table An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee.

One employee can only be a part of one organization and if he is an employee then he can't be a freelancer. Whereas a freelancer can be connected to multiple organization and he also can't be an employee.

In short, Freelancer and Employee both are users. Freelancer is connected to Organization via many to many relationship. Employee is connected to Organization via one to many relationship.

What I have thought:

user{
   id,
   name,
   other_fields,
}

organization{
    id,
    other_fields
}

freelancer{
    user_id,
    organization_id,
    Primary_Key(user_id, organization_id)
}

employee{
    user_id,
    organization_id,
    Primary_key(user_id),
}

There is one catch here, though. The constraint of being either a freelancer or an employee but not both has to be implemented at the application level.

What are the pros and cons of above schema?
What are other possible alternatives to design schema?
What if there are more category of users? Current schema requires addition of new table if a new role of user s created. Is it ok or bad?
Database maintenance is also a concern that making a schema which should not make maintenance a problem

What are the pros and cons of above schema?

The major con that I see is that freelancer and employee have the same fields, but are different tables. The difference is what primary_key is composed of, but I wonder if you could normalize this as

emplpoyee
{
    user_id,
    employee_type, (could be "FREELANCER" or "FULLTIMEEMPLOYEE")
    organization_id,
    Primary_key
}

For primary_key, you might need some application logic to create a string based on user_id and employee_type, and optionally include tha value of organization_id if the employee is a freelancer.

You could even go a step further and have a table employee_type:

employee_type
{
    id,
    description
}

and then reference that ID in employee.employee_type. This would deal with your problem of adding new tables every time there is a new type of user.

Also, Have you considered merging employee with user? This would simplify your schema even more, but you would lose the ability to have a user_id associated with multiple employees, which I think is important for this case.


Can I pass data between stages in a maintenance task I have a Maintenance plan that I wish to execute nightly. The purpose is to set an archived bit against records that are more than 60 days old. This bit all works fine, however I'd like to log (and email an operator) about how many records were affected.

I have a stored proc that does all the heavy lifting, and it is defined in an execute T-SQL task with an out parameter that contains the number of records affected.)

So how do I pass that value to a notify task, or log it?

Using an SSIS expression on the Notify Operator task properties for the message subject or body, you can construct whatever you want from variables written earlier.


Will adding features pick up previously installed service pack updates? We have SQL Server 2008 R2 SP3 installed. Need to add a feature to the installation, Business Intelligence Development Studio. The question is do features installed after the application of a service pack pick up that service pack? If not does the service pack have to be installed again?

I would always err on the side of just installing the service pack again (it will know which components, if any, to update). Especially when adding client tools to a workstation - where this is not a major disruption. (And if you're adding BIDS to a production server, you should reconsider IMHO.)


Summary over sets of points Using postgresql 10

I have a table gpspoints which among other things contains

id    dist
 1    NULL
 2     2.3
 3     4.4
 4     1.7
 5     1.2
 6     2.7

and so on

Dist is distance in meter since the previous point
In addition, I have a table trip that gives the first and last id from the gpstable for a trip. Each point may belong to several overlapping trip.

The trip table is then like:

 id start  end
  1   1     4
  2   2     3
  3   3     5

When I do my query I know that start < end and there will never be more than one defined trip for each gpspoint

I would like to add up the length of the trip for each point that is defined as a start point, i.e.

start  length
 1       9.4   # (2.3 + 4.4 + 1.7)
 2       6.1   # (4.4 + 1.7)
 3       7.3   # (4.4 + 1.7 + 1.2)

Writing this, I realize that a possible solution would be to make a new table containing the trip id and the gpspointid for each trip. But as the gpspoint table has a few millions rows and there may be up to a few hundred trips that each point is a member of (Each of the trips may contain a few hundred points) and the trips are clearly defined by their start and end point, my question is still

Is there a way to sum a number between two given id with varying distance between the ids? (if there always had been the same number of points in a trip, I could have made the sums using windowing functions)

I am not entirely sure I understood you, but maybe something like this:

select t.id,
       sum(p.dist) as trip_distance,
       array_agg(p.dist) as dist_values,
       array_agg(p.id) as points
from gpspoints p
  join trip t on p.id between t.start_point and t.end_point
group by t.id;

For the following sample data:

create table gpspoints  (id int primary key, dist numeric);
create table trip (id integer primary key, start_point int, end_point int);

insert into gpspoints
values
  (1, NULL),
  (2,  2.3),
  (3,  4.4),
  (4,  1.7),
  (5,  1.2),
  (6,  2.7);

insert into trip
values
  (1, 1, 4),
  (2, 2, 3),
  (3, 3, 5);

The query returns:

id | trip_distance | dist_values        | points
---+---------------+--------------------+----------
 1 |           8.4 | {NULL,2.3,4.4,1.7} | {1,2,3,4}
 2 |           6.7 | {2.3,4.4}          | {2,3}
 3 |           7.3 | {4.4,1.7,1.2}      | {3,4,5}

The columns dist_values and points are just there for debugging purposes to validate that the correct points were included.


Any tips to watch out for when setting one of my databases to Read Only? Our front end application uses\ pointed to a different database now in SQL Server 2008R2 in the same instance, so to prevent users from accidentally updating data in the old production database, is it simply just changing the Database Read Only State to "True"? As ADMIN users may still need to access it in case, to verify migrated data to the new DB matches the Old DB but not be able to modify anything.

Also, is it a good idea to change the recovery model for this old production database to Simple vs Full? and no need to run its Jobs or maintenance plans ?

In worse cases, I always can set it back to Read and Write at anytime with no issues correct? Thanks for any advice.

is it simply just changing the Database Read Only State to "True"?

yes, you can use below t-sql

alter database db_name
set read_only with rollback immediate

  Also, is it a good idea to change the recovery model for this old production database to "Simple" vs "Full"?

Yes, a read-only database wont be having any DML operations. So best practice is to change the recovery model to SIMPLE. Make sure that you take regular FULL backups (and most important is testing your restore) - just incase if the server goes down.

  In worse cases, I always can set it back to Read and Write at anytime with no issues correct?

Yes, you can just run below tsql

ALTER DATABASE db_name SET READ_WRITE
with rollback immediate
GO


Is it ALWAYS possible to avoid cursor? I'm looking for any example where using a cursor is unavoidable.
I'm pretty sure there are some rare cases like that, but I'm unable to come up with any scenario. Any help would be wonderful. Thanks!

Sure (and let's assume that a while loop and a cursor are basically the same thing for the purposes of the discussion, throwing out any arguments that using a while loop avoids a cursor, which is simply not true except in the most pedantic way):

You need to execute a stored procedure (say, a vendor stored procedure that you aren't allowed to touch, and that you must use to stay in compliance/support). For every row in some set.
You need to send a distinct e-mail to each of a set of users (as opposed to just building a massive To/CC list - not only because that doesn't scale but also because you may need to personalize the e-mail for each user).

There are also some metadata operations where you might want to use a cursor, e.g. to disable and re-enable all foreign key constraints on every table. Personally, I've always preferred building a string in a query that I can execute at once, but you may not want to do this for various reasons. As an example, I didn't use a single cursor in this answer, though I could have and it would have been simpler to write. (Also note that solutions like that one - and many seemingly set-based queries - still technically use a cursor under the covers - you just don't actually write DECLARE CURSOR...)

It can also be quite complex to build a set-based query that does exactly what a cursor does, and often isn't worth the time investment or the impact to future maintainability due to query complexity, when the cursor is actually much more straightforward (even if it is less efficient). And for efficiency, if you're comparing a cursor solution to a set-based solution, make sure you use the most efficient cursor options - these can make a big difference.

For normal queries you're building from scratch and without any of these constraints, I don't know that there is a situation where you have to use a cursor, especially as SQL Server continues to add windowing functions and other features that assist in more advanced set-based programming that used to typically require cursors.

If you're writing a cursor, you should be asking yourself, "Is there a set-based way to do this?" Depending on the purpose of the code, how much it will be re-used, whether it is a one-off task, etc. the answer is almost always, unilaterally, "Yes!" But a perfectly valid answer to that question might also be, "Yes, but who cares?"

Would an example where a cursor is actually the best performing, supported, documented and guaranteed solution to a problem suffice? One such example is calculating running totals in SQL Server < 2012. I blogged about it here - and actually demonstrated that in my use case a cursor was faster than all the other common approaches, even the often recommended (but not guaranteed, documented or supported) "quirky update" method. (Only if the new 2012 LAG feature is not an option, in which case, it runs circles around everything else).


MySQL: can a table get stuck in the table cache? I've asked this question on the ServerFault stackexchange, someone directed me here.

I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that has a MySQL server (version 4.1.14-standard). Before anyone makes the remark: the decision to use a MySQL 4 server was not made by me, and any decision to upgrade is unfortunately also not mine to make.

Last January, I added a table to the database, and updated the PHP script so that each time the application is started, a row is inserted into this table. I set up the table so it started with a number of rows already in it. In the mean time, our client has been merrily using their application, causing several rows to be added to the table.

Last week, I wrote a PHP script that fetches all rows from the table and builds a report from the result, and ran it, only to find that several rows were missing. In fact, only the rows I started off with, seemed to be present in the report. I attributed this to no-one having used the application in the mean time. Yesterday, however, I decided to check if this was actually true. So I fired up phpMyAdmin, exported the database, and ran the report locally, only to find new data present and accounted for. So I checked if the code on the live site was up to date, and this is the case. Stumped, I ran the report on the live site again, and presto: there the missing rows were.

My reasoning:

The issue has to be some kind of cache thing, because the table has two DATE columns, they are filled with INSERT queries, and possibly updated with UPDATE queries, each time with NOW() as the value, never any other value, and the dates in the table were spread across the past half year. Last week I saw no new rows since January, but yesterday morning I saw all these rows with these dates in them: the new rows cannot all have been added in the past week.
I did not publish any code, or push data to the MySQL server, since I first ran the report.
It can't be a browser cache issue, because I wrote the report script last week: if it were a browser cache issue, it would have been a cache entry from January. Since the script did not exist then, the information can't come from my browser cache; and besides, we web developers clear our browser caches weekly, if not several times per day and I am no exception.
If it's a cache issue and it's not a browser cache issue, it has to be a MySQL cache issue.

However, it can't be a MySQL cache issue either, because the query cache gets invalidated as soon as a new row is inserted, which has happened several times the last half year. It also can't be the table cache, because table_cache is set to 64, open_tables is 64, and opened_tables is about 13 million. So if there is something wrong with the table cache at all, I'd expect "too new" data, instead of "too stale" data.

My working theory, for lack of a better one, is that somehow, despite my reasoning, the table got "stuck" in the MySQL table cache, and that exporting the table data with phpMyAdmin caused the table to "unstick".

All database queries the application does, are done with PHP's mysql_* functions: again, not my decision: I'd much rather use PDO, but am stuck with PHP 4. Anyway, the application does not use Memcached, so that's ruled out.

My question is: why did I get the older version of the table data, and is there any way I can prevent this from happening again?

The table in question:

CREATE TABLE `offset` (
  `id` int(10) NOT NULL auto_increment,
  `version` int(10) NOT NULL default '0',
  `user_id` int(10) NOT NULL default '0',
  `client` varchar(255) NOT NULL default '',
  `created_on` datetime default NULL,
  `modified_on` datetime default NULL,
  `hidden` tinyint(1) default '0',
  PRIMARY KEY  (`id`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=302 ;

The table cache does not store data, only MySQL table structs. The query cache cannot return old results because all the queries sing it are invalidated on write, and selects are blocked until that happens (actually causing some contention problems).

I will not discard a MySQL bug, as you are using a 9-year old unsupported version of the server, but you were too quick to discard cache issues/errrors on the other many layers of your application (client, server code, connector, your report code running on the wrong server, etc.).

To solve problems like this, you need to monitor queries sent and results returned at MySQL level and others, so you have enough information to identify the problem. I also recommend you to get familiar with the mysql command line client, as it is the best way to debug server issues.


Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it).

Some of our tables have millions of rows, and they are running fine.

What causes problems is a tiny little table that is only ~2000 rows.
This table is used to store current status reported from remote devices. These devices are sending their updated status every few seconds, so there are quite a few updates in that database each second. Nothing is being removed though, the rows are kept intact they are just being updated every time a device reports new status.

Some time ago I have noticed huge performance drop on our Apache server that uses this database. What I saw was that the status table was >3GB in size while having only 2000 rows. I read a lot on the internet, tried normal VACUUMs but they didn't do anything.

I finally went for VACUUM FULL as I saw that it is safe to use it on versions 9.0 and above. That really helped, the table size dropped just to a few KBs!

However, the problem is happening periodically every few days, and now when I try to run VACUUM FULL it takes a very long time, and our whole service is unusable during that time.

What could I do to prevent this from happening? Autovacuum is turned on, but it seems to do nothing in this particular case.

Output of VACUUM VERBOSE:

username=# vacuum verbose status;
INFO:  vacuuming "public.status"
INFO:  index "status_pkey" now contains 3295731 row versions in 11704 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.01u sec elapsed 0.14 sec.
INFO:  "status": found 0 removable, 4472204 nonremovable row versions in 400557 out of 400558 pages
DETAIL:  4470176 dead row versions cannot be removed yet.
There were 23081 unused item pointers.
0 pages are entirely empty.
CPU 2.87s/0.70u sec elapsed 3.64 sec.
INFO:  vacuuming "pg_toast.pg_toast_16753"
INFO:  index "pg_toast_16753_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_16753": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

As  you can see, there are 0 removable row versions, despite the table has only 2039 physical rows.

SELECT n_live_tup, n_dead_tup
from pg_stat_user_tables
where relname = 'status';

 n_live_tup | n_dead_tup
------------+------------
       2039 |    4473416

At that moment,

SELECT pg_size_pretty(pg_total_relation_size('status'))

reported a size of 3221 MB, for only 2039 rows in the table. That is hilarious.

Something is keeping autovacuum from removing those dead tuples.

These can be only three things that keep VACUUM from doing its job:

A long running concurrent transaction. Check with

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

An abandoned replication slot. Check with

SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;

An orphaned prepared transaction. Check with

SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

But all that would also block VACUUM (FULL) from shrinking the table.  So there must be something extra you are doing when you run VACUUM (FULL).

You have autovacuum enabled, right?

This is what you should do:

Remove whatever is blocking VACUUM.
Make sure autovacuum runs fast enough on that table:

ALTER TABLE status SET (autovacuum_vacuum_cost_delay = 0);

If the data modifications are caused by UPDATE you could also try to get HOT updates. These remove the need for VACUUM. A prerequisite is that the updated column or columns are not indexed.


ESCAPED BY clause in LOAD DATA INFILE Can somebody please help me understand what is ESCAPED BY clause means in LOAD DATA LOCAL INFILE statement MySQL 5.5.  I am a newbie to MySQL so please bear with me.

Generally the defaults cause LOAD DATA INFILE to act as follows when reading input:

Look for line boundaries at newlines.
Break lines into fields at tabs.
Do not expect fields to be enclosed within any quoting characters.
Interpret occurrences of tab, newline, or \' preceded by\' as literal characters that are part of field values.
Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

Write tabs between fields.
Do not enclose fields within any quoting characters.
Use \' to escape instances of tab, newline or\' that occur within field values.
Write newlines at the ends of lines.
Note that to write FIELDS ESCAPED BY '\\', you must specify two backslashes for the value to be read as a single backslash.

For Further Details Please Have a Look at following Link :-
dev.mysql.com/doc/refman/5.1/en/load-data.html


mysql replication delay very serious We use a ssd disk for master database and SATA disk for slave.

On high write load (300 writes/s), slave delay is very serious. I have set:

innodb_flush_log_at_trx_commit = 0
increased innodb_log_file_size

on the slave, but the delay continues.

the slave is for backup only, no read or write request.

Seconds_Behind_Master: 92265, continue increasing.

some conf:

innodb_buffer_pool_size=15G
innodb_log_file_size=1G
innodb_log_buffer_size=32M

du -hs mysql
338G    mysql

iostat
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sde               0.00    73.00   34.50  185.00   552.00  7086.00    69.59     5.63   25.63   19.19   26.83   3.65  80.20

I wrote about something similar on MySQL replication: most important config parameters for performance on slave server?
Basically you should focus on the follow variables on slave (see the other post for more information):

innodb_buffer_pool_size
innodb_log_file_size
innodb_flush_log_at_trx_commit
innodb_doublewrite
innodb_flush_method
innodb_max_dirty_pages_pct
sync_binlog
sync_master_info
sync_relay_log
sync_relay_log_info

One thing to have in mind is that the slave have just a single thread to process all writes from master, let's say you master are able to handle 300 connections simultaneously, it's 300 threads writing at the same time (respecting locking issues, disk, ...)  but on the slave you have just a single one to deal with all this transactions.
Suggestion:
If you use MySQL 5.6 and have your data partitioned per database, you can take advantage of slave-parallel-workers


Duplicated rows (x) amount of times in a table I have a table that looks like the following:

╔══════════╦═══════════╦════════╦
║      SO  ║   SO_Line ║  ...   ║
╠══════════╬═══════════╣════════║
║      ABC ║        1  ║        ║
║      ABC ║        3  ║        ║
║      ABC ║        5  ║        ║
║      DEF ║        1  ║        ║
║      DEF ║        2  ║        ║
╚══════════╩═══════════╝════════╝

What I'd like to do is duplicate a result set (x) amount of times.  For instance, given this result set: SELECT * FROM Table WHERE SO = 'ABC', I'd like to duplicate that result set 10 times.  Basically I want to keep all of the data in all of the other columns the same, but I'll change the primary key to be something else (for instance, ABC-1).  My final table after duplicating that set would be like this:

╔══════════╦═══════════╦════════╦
║      SO  ║   SO_Line ║  ...   ║
╠══════════╬═══════════╣════════║
║      ABC ║        1  ║  ...   ║
║      ABC ║        3  ║  ...   ║
║      ABC ║        5  ║  ...   ║
║    ABC-1 ║        1  ║  ...   ║
║    ABC-1 ║        3  ║  ...   ║
║    ABC-1 ║        5  ║  ...   ║
╚══════════╩═══════════╝════════╝

Normally the way I would do this is by inserting the records from the result set into a temporary table and then assigning a row number to each row.  Then I'd iterate through the temporary table using something like WHILE @i < @RowCount and do an insert into the main table using @i to increment the PK.  I'm basically asking if A) that's the most reasonable way to accomplish this, and if not, B) is there a more efficient way of handling this?

You can get it using a table value or a value list, and a CROSS JOIN.

INSERT INTO foo
SELECT CONCAT(SO, t.x) as SO, SO_Line
FROM   foo
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x)
WHERE  SO = 'ABC';
GO

15 rows affected

SELECT * FROM   foo;
GO

SO    | SO_Line
:---- | ------:
ABC   |       1
ABC   |       3
ABC   |       5
DEF   |       1
DEF   |       2
ABC-1 |       1
ABC-2 |       1
ABC-3 |       1
ABC-4 |       1
ABC-5 |       1
ABC-1 |       3
ABC-2 |       3
ABC-3 |       3
ABC-4 |       3
ABC-5 |       3
ABC-1 |       5
ABC-2 |       5
ABC-3 |       5
ABC-4 |       5
ABC-5 |       5

dbfiddle here

Instead of use a value list, you could use an INLINE User defined function like this:

CREATE FUNCTION tvValues(@Num int)
RETURNS table
AS
RETURN
(
     SELECT TOP (@Num) ROW_NUMBER() OVER (ORDER BY S.[object_id]) [item]
     FROM    sys.all_objects S
)
GO

Then convert your query to:

INSERT INTO foo
SELECT CONCAT(SO, '-', t.item) as SO, SO_Line
FROM   foo
CROSS JOIN tvValues(7) t
WHERE  SO = 'ABC';
GO

NOTE: I've used tvValues(7) but you can use other values.

This is the result:

SELECT * FROM   foo;
GO

SO    | SO_Line
:---- | ------:
ABC   |       1
ABC   |       3
ABC   |       5
DEF   |       1
DEF   |       2
ABC-1 |       1
ABC-2 |       1
ABC-3 |       1
ABC-4 |       1
ABC-5 |       1
ABC-6 |       1
ABC-7 |       1
ABC-1 |       3
ABC-2 |       3
ABC-3 |       3
ABC-4 |       3
ABC-5 |       3
ABC-6 |       3
ABC-7 |       3
ABC-1 |       5
ABC-2 |       5
ABC-3 |       5
ABC-4 |       5
ABC-5 |       5
ABC-6 |       5
ABC-7 |       5

dbfiddle here


Database: One table or many I am working on a project of classified ads site.

I have 12 main categories, and their items are 142, Like vehicles have cars, scooters, bikes, etc. Mobiles Phones have mobiles, tablets, accessories.

Now should i use few tables and use Json ?

Or should i create separate tables for each item (142 tables) ?

Currently i have decided 142 tables to save myself from relations and joins and heavy coding.

Which way is correct or suggest another one. What are the pros and cons ? I have search a lot but didn't got anything that answers my question.

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc.

Storing JSON blobs in the database will kill performance when you're performing searches, so I wouldn't go with that either.

A good relational table design (yes, with joins) is really the best approach when you're using a relational database system. For instance, one table with categories (12 records), joined to one table with items (142 records), joined to a table of ads.


How do you use COLUMN_JSON with COLUMN_GET on dynamic columns in MariaDB? When I do the following query;

SELECT name, COLUMN_GET(attr, 'appearance' as char) as 'attributes' from items;

I see the 2 columns including the blob data with the weird formatting.

Is it possible to include the COLUMN_JSON function to format the data inside of the select query?

So I figured out how to do this.

In the first part of the SELECT, is where you would do the COLUMN_JSON portion and the COLUMN_GET is done in the WHERE section.

Example;

SELECT COLUMN_JSON(attr) as 'attribute' FROM items WHERE COLUMN_GET(attr, 'name' AS CHAR) like 'name%' ;


SQl Server 2008 R2 - sa can login but not access database Up until this morning I had no problems accessing our database.

This morning, something has changed and I can no longer access our database (called EMS_Main) using the sa login. I can log into the instance quite happily, but cannot access the database itself. This goes for both my C# application and SQL Server Management Studio. In SSMS I get "The database EMS_Main is not accessible. (Object Explorer)" if I try and expand the icon for EMS_Main.

Seems to make no difference whether I'm logging in via LAN, VPN, or even remote desktop on the server itself.

The logs don't show much out of the ordinary other than last night around 22:05 I got the following:

Four "IO is frozen on database" messages for master, EMS_Main, msdb, and model.

Four "IO was resumed on database" messages for the above.

Then (with the same timestamp):

"A timeout occurred while waiting for buffer to latch -- type 2"

"Error: 3041, Severity: 16, State: 1"

"BACKUP failed to complete the command BACKUP DATABASE EMS_Main. Check the backup application log for detailed messages."

Then nothing until this morning when I got:

"Error: 18456, Severity: 14, State: 38"

"Login failed for user 'sa'. Reason: Failed to open the explicitly specified database."

Any idea what is going on/how I can rectify this?

It smells like a permissions issue to me; I've tried creating a new login in SSMS so I can do a "GRANT VIEW ANY DATABASE TO sa" (a solution suggested on another website), but creating a new user hangs in SSMS (clicking the 'User Mapping' page also hangs it as well, not sure if that's related) - the 'Progress' pane just shows 'Executing' with the progress indicator going round indefinitely.

If I double click on the sa login, I can see (after a bit of a wait) that it has permission to connect to the database engine, the login is enabled, the box for 'Login is locked out' is not checked (but is greyed out), it only has 'public' and 'sysadmin' checked for Server Roles, default database is set to 'master'.

The server is running MS Windows Server 2003 for Small Business Server SP2. We're on SQL Server 2008 R2.

I am new to being a dba (got thrown into it by my employer) - my background is more in coding business apps, so go gentle on me! I don't necessarily know all the dba jargon too well ;)

Thanks in advance.

Fixed this - VSS Writer had fallen over for some reason, and the upshot of that was that a load of stuff (BACKUP amongst them) could not complete (BlkBy column in sp_who2 showed this up). A reboot of the server fixed this issue (services.msc wouldn't let me kill VSS Writer, and neither would the KILL command)...only thing left to do is find out WHY it happened.

And sort myself a disaster recovery plan ;)


Users who are connected into the database I need some help on how to find all the users that are connected to one of my database. I should check this on weekends to find out all the users that connected during the week.

Thank you.

You can check connected logins with the following query:

SELECT
    sdes.session_id,
    sdes.login_time,
    sdes.last_request_start_time,
    sdes.last_request_end_time,
    sdes.is_user_process,
    sdes.host_name,
    sdes.program_name,
    sdes.login_name,
    sdes.status,
    sdec.num_reads,
    sdec.num_writes,
    sdec.last_read,
    sdec.last_write,
    sdes.reads,
    sdes.logical_reads,
    sdes.writes,
    sdes.client_interface_name,
    sdes.nt_domain,
    sdes.nt_user_name,
    sdec.client_net_address,
    sdec.local_net_address
FROM
    sys.dm_exec_sessions AS sdes
    INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id

However, this query will only retrieve currenty connected sessions. if you would like to know which logins were logged into the server, you need to set up a trace, enable the audit for error log or use extended events.

Here's a solution to enable audit for error log:

Using SSMS, right click your server and go to Properties.
On Security, below Login auditing click either "Successful logins only" or "Both failed and successful logins", depending if you also want failures or not.
Restart the SQL Server service (unfortunately).
Information will be stored on the Error Log. Data will be deleted over a time so make sure to review it's retention policy. To read the information on the error log you can use sys.sp_readerrorlog. The first parameter is the error log number (0 = current, 1 = first archive, etc.), second parameter determines the source to read (1 for error log) and the third parameter is the literal you want to filter info from:

EXEC sys.sp_readerrorlog 0, 1, 'login'

A more visual alternative to read the log is going to Management (on SSMS), then SQL Server Logs and double click on any log.

To see amount of logins by time you can use the following script (I'm using the "failed login" event because it's the one my server is tracking):

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

CREATE TABLE #FailedLogins (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    Message VARCHAR(MAX))

INSERT INTO #FailedLogins (
    LogDate,
    ProcessInfo,
    Message)
EXEC sys.sp_readerrorlog 0, 1, 'login failed'

SELECT TOP 5 * FROM #FailedLogins AS F

Result:

LogDate                 ProcessInfo Message
2018-07-16 09:47:52.240 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-16 12:31:51.930 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-17 13:38:05.520 Logon       Login failed for user 'User2'. Reason: Could not ...
2018-07-17 13:42:14.130 Logon       Login failed for user 'User3'. Reason: Password ...
2018-07-17 13:42:28.690 Logon       Login failed for user 'User3'. Reason: Password ...

How to obtain user from these messages (this will highly depend on the message being shown on the error log):

DECLARE @Message VARCHAR(MAX) = 'Login failed for user ''User1''. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]'

SELECT
    Original = @Message,
    UserStartPoint = SUBSTRING(@Message, 24, 100),
    TrimmedUser = LEFT(
        SUBSTRING(@Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(@Message, 24, 100)) - 1)

Result:

Original: Login failed for user 'User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]
UserStartPoint: User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]
TrimmedUser: User1

Group by and count between a period:

DECLARE @PeriodStartDate DATE = '2018-07-16'
DECLARE @PeriodEndDate DATE = GETDATE()

SELECT
    Amount = COUNT(1),
    TrimmedUser = LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)
FROM
    #FailedLogins AS F
WHERE
    F.LogDate BETWEEN @PeriodStartDate AND @PeriodEndDate
GROUP BY
    LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)

Result:

Amount  TrimmedUser
7       User1
3       User3
1       User2


sql server management studio 2008 sucks up with memory yielding .net runtime failure PROBLEM
sql server management studio 2008 sucks up with memory yielding a .net runtime error. It crashes with an empty message in an error-dialog but there can be found a little eventlog entry, reading:

<EventData>
  <Data>.NET Runtime version 2.0.50727.5446 - Fatal error in the execution engine (7269AECA) (0).</Data>
</EventData>

Original Eventlog Entry (German):

<EventData>
  <Data>.NET Runtime version 2.0.50727.5446 - Schwerwiegender Fehler im Ausführungsmodul (7269AECA) (0).</Data>
</EventData>

RECREATING THE ERROR

After about every 10 Million-th Insert-Statement Management Studio 2008 executed in a loop against a remote instance of SQL Server 2005, the 4GB of memory on my dev-workstation are up which leads to the stated error above. Why the hell i'm executing insert-statements in a loop? - Since SQL Server doesn't allow one to set the actual TIMESTAMP of a DB manually, dummy-data-manipulation-statements are needed to help one out (the TIMESTAMP-manipulation-lock is no bad idea but on a dev-environment sometimes needed).

CREATE TABLE __DUMMY (I INT, TS TIMESTAMP)
DECLARE @FROM INT
DECLARE @TO INT
SET @FROM = 666
SET @TO = 666666666
WHILE (@FROM < @TO)
BEGIN
    INSERT INTO __DUMMY (I) VALUES(@FROM)
    SET @FROM = @FROM +1
END

On another session to SQL Server I truncate the __DUMMY-table from time to time:

SELECT @@DBTS, CONVERT(INT, @@DBTS), 'SERVER'
UNION
SELECT CONVERT(TIMESTAMP, 666666666), 666666666, 'CLIENT'

EXEC sp_spaceused '__DUMMY'
TRUNCATE TABLE __DUMMY

--writes    ~10.000 Tuple/Second
--allocates ~100.000 Byte/10.000 Tuple
--      ==> ~10 Byte/Tuple (are "roughly" made up of: INT = 4 BYTE, TIMESTAMP = 8 BYTE)
--insert/truncate is ~100.000 times more performant than doing a insert/delete in the loop or a permanent update on one tuple (haven't tried indices yet)

See http://www.flickr.com/photos/74806161@N03/6732847259/in/photostream for image showing sucker-process-descriptions
and http://www.flickr.com/photos/74806161@N03/6732846981/in/photostream for image showing the memoryconsumption of the sucker-process

Anyone any Ideas?

Looks like up until SQL Server 2000 there was an undocumented command DBCC CHECKDBTS that did exactly what you want but it is no more. It looks like it is stored on the database boot page with no (safe, see below) way of manually setting it.

I'm not sure why you want to do this and I suggest that you don't bother but if you must then you will likely find set based inserts considerably quicker.

(NB: The below might need tweaking to avoid overshooting the target as I found that @@DBTS incremented by 1003921 for each million row insert but should be an improvement...)

CREATE TABLE __DUMMY (I TINYINT, TS TIMESTAMP)

DECLARE @Target INT, @Batch INT
SET @Target = 666666666

WHILE @Target >  CONVERT(INT, @@DBTS)
BEGIN
RAISERROR ('@@DBTS = %d',0,1,@@DBTS) WITH NOWAIT;

SET @Batch = @Target - CONVERT(INT, @@DBTS);

IF @Batch > 1000000
    SET @Batch = 1000000;

   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 INSERT INTO __DUMMY (I)
  SELECT 1
   FROM cteTally
  WHERE N <= @Batch;

TRUNCATE TABLE __DUMMY
END

DROP TABLE __DUMMY

Or alternatively if this is only a development database, you have backups and you aren't going to come complaining to me if it all goes wrong you could do some experimenting with the undocumented and highly warned against DBCC WRITEPAGE command.

USE tempdb;

IF DB_ID('TestDBTS') IS NOT NULL
    DROP DATABASE TestDBTS;

CREATE DATABASE TestDBTS;

USE TestDBTS;

DBCC WRITEPAGE(TestDBTS, 1, 9, 412, 8, 0xAA86BC2700000000);

ALTER DATABASE TestDBTS SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE TestDBTS SET ONLINE WITH ROLLBACK IMMEDIATE;

USE TestDBTS;
SELECT @@DBTS;

DBCC TRACEON (3604);
DBCC DBINFO ('TestDBTS');
DBCC TRACEOFF (3604);


Best practices for backing up a MySQL DB I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience with MySQL DB's. Any best practices for using 'mysqldump' or any other DB backup tools?

I'll probably cron job the schedule so that it's done nightly and then backup the files with my backup system.

Thanks.

Best Practices to take MySQL server backup:
MySQL Replication
Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server.
Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on Master without any downtime.
Using MySQL Dump
If your data set is small (I realize "small" is a relative term.. to qualify it, let's say <10GB), then mysqldump will probably work great. It's easy, it's online and it's very flexible. Just a few things mysqldump can do:
backup everything or just certain databases or tables
backup only the DDL
optimize the dump for a faster restore
make the resultant sql file more compatible with other RDBMSes
and many more things.
However, the most important options are related to the consistency of your backup. My favorite options are:
--single-transaction : this option gives a consistent backup, if (and only if) the tables are using InnoDB storage engine. If you have any non-read-only MyISAM tables, then don't use this option when backing them up.
--master-data=2 : this option will make sure your dump is consistent (by doing a lock-all-tables unless you've added the option --single-transaction). The --master-data option also records the binary log position in the resulting dump file (=2 causes this line to be a comment in the dump file)
Final note about mysqldump: keep in mind that the restore time may be significantly longer than the backup time. It will depend on several factors, for example, how many indexes you have.
LVM snapshot
For those that have larger datasets, a physical backup is the way to go. While you could take a cold backup (i.e., shutdown the MySQL service, copy the data directory, restart the service), many people do not want downtime. My favorite solution is snapshots. This can be hot (for InnoDB) or require a brief lock (for MyISAM). Don't forget to include all your data (include the ib_logfiles). Lenz provides a nice utility to help with this: http://www.lenzg.net/mylvmbackup/
Using MySQL Enterprise Backup
Advantages of using MySQL Enterprise Backup:

"Hot" Backups of InnoDB tables takes place entirely online, without blocking
Backup only particular tables or tablespaces
Only backup the data that changed since a prior backup
Compressed Backup - Saves storage upto 90% and many more..

Reference:
http://www.mysql.com/products/enterprise/backup/features.html
http://www.mysql.com/products/enterprise/backup.html


Repeatable Read: Which one is correct? Which one is correct?

With transaction 1 as Repeatable Read, other transactions can not update a row after it has been selected by transaction 1.
With transaction 1 as Repeatable Read, other transactions can update a row, but transaction 1 does not take into account those changes.

I before believed that (1) is right. But when I tested sample transactions on machine, (2) is correct. I am using MySQL version 5.1.2.re Community.

While many database systems will actually behave like your first version, for MySQL, version 2 is the expected behaviour, see the documentation about repeatable read:

  This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

This means: if you select the same row, you get the same data (from the snapshot), even if the row has been deleted by another transaction. This is what the sql standard requires: it has to prevent "non-repeatable reads" (in addition to "dirty reads" of lower isolation levels), but every database system can implement it differently in details.

In MySQL, if you want to block another transaction from updating the rows in repeatable read, you need to lock them by e.g. using select ... for update. A simple select will not place a lock unless you are in serializable isolation mode.


Get latest status of an item in the database I have this table:

ID|Item-FK |timestamp|status
=============================
1 | 123    | ...     | OK
2 | 123    | ...     | FAILED
...

I want to query get all items which last status was FAILED.

If an item failed yesterday, and is OK today, I don't want to see it.

Use Partition by to get last record where Status is failed.

    ---Test table
    CREATE TABLE [dbo].[items](
        [id] [int] NULL,
        [Item-FK] [int] NULL,
        [timestamp] [datetime] NULL,
        [status] [varchar](200) NULL
    ) ON [PRIMARY]
    GO

--dummy data
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (1, 123, CAST(0x0000A3E0006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (2, 123, CAST(0x0000A3E1006DB876 AS DateTime), N'FAILED')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (3, 124, CAST(0x0000A3E1006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (4, 124, CAST(0x0000A3E1006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (5, 125, CAST(0x0000A3E1006DB876 AS DateTime), N'FAILED')

--- Query to get all items who's last status is 'Failed'
    SELECT  *
    FROM    items
    WHERE   [Item-FK] IN (
            SELECT  [Item-FK]
            FROM    ( SELECT    row_number() OVER ( PARTITION BY [Item-FK] ORDER BY [timestamp] DESC ) iid,
                                *
                      FROM      items
                    ) item
            WHERE   iid = 1 AND [status] ='FAILED')

EDIT: you can use EXISTS instead on IN

SELECT  *
FROM    items itm
WHERE   EXISTS ( SELECT [Item-FK]
                 FROM   ( SELECT    row_number() OVER ( PARTITION BY iitm.[Item-FK] ORDER BY iitm.[timestamp] DESC ) iid,
                                    *
                          FROM      items iitm
                        ) item
                 WHERE  item.iid = 1
                        AND item.[status] = 'FAILED'
                        AND itm.[Item-FK] = item.[Item-FK] )


Why does this 120 GB insert grow the log by over 780 GB? A table [tbl_old] has about 3 billion rows and about 120 GB for disk space, and then created a [tbl_new] with the same schema (actually, just dumped the script of [tbl_old], then rename the table name to [tbl_new], then create it).

But when running this SQL script:

insert [tbl_new]
   select * from [tbl_old]

After the transaction log file used up about 780 GB and hung.

My questions are:

Is it reasonable for just such inserting script for about 120 GB, more than 780 GB transaction log is not enough?
What is in the log file?

Background:

the DB is in simple recovery mode, and no index on these [tbl_old] and [tbl_new]
SQL Server 2008 R2 Enterprise on Window Server 2008 64bit.
And Intel Xeon CPU E5645 @2.4GHz (2 processors), 24 CPUs, 64 GB memory

The log contains an exact description of every inserted record. That is at least 120GB, but must be multiplied by some factor for additional info. 1.5x is a good estimate => ~180Gb. Every record must reserve in the log space for compensation on rollback. 1:1 ratio between do and undo is a good estimate => ~360Gb. Now 780Gb is more than double this estimate. There could be explanations for such high size, like any other concurrent activity in the DB. You can use fn_dblog to investigate how exactly the log is used, if you must know.

Remember that SELECT ... INTO ... FROM ... is an operation that can be minimally logged but INSERT ... SELECT ... FROM ... is actually not, unless the special trace flag 610 is used, see Minimal Logging changes in SQL Server 2008 for details on all requirements to achieve minimally logging with INSERT ... SELECT.


I think you're after something like this:

  SELECT id,
         start_of_week,
         sum(hours) as "Overall",
         sum(hours) filter (where "dow" = 'sunday   ') as "sunday   ",
         sum(hours) filter (where "dow" = 'monday   ') as "monday   ",
         sum(hours) filter (where "dow" = 'tuesday  ') as "tuesday  ",
         sum(hours) filter (where "dow" = 'wednesday') as "wednesday",
         sum(hours) filter (where "dow" = 'thursday ') as "thursday ",
         sum(hours) filter (where "dow" = 'friday   ') as "friday   ",
         sum(hours) filter (where "dow" = 'saturday ') as "saturday "

    FROM newtable

   WHERE id is not null

GROUP BY id,
         start_of_week

ORDER BY start_of_week;

I added an overall sum as well.


What options are there for security on shared data sources for SSRS? While trying to automate deployments that include SSRS reports and SSRS shared data sources/data sets we have been trying to configure security appropriately. Particularly the problem is with shared data sources/data sets and automating their deployment across all environments. The only solution that has come up is to create a single SQL Server Login and use the same username password as the data source for the shared data sources in SSRS. The account would be the same from Dev to Prod.

The newly created SQL Login will need EXECUTE on stored procedures in all these environments. I am against having one SQL login with EXECUTE permissions in ALL environments.

Prior to a more automated deployment, we would have an account in all 3 environments but a different password in Production. The DBA team would manually change the shared data source/data set's password after deployment. The goal is to move away from the manually intervention while also not creating a large security hole.

SSRS config file is encrypted nonetheless I would like to not create a SQL Login with so many privileges. Are there any other options?

We are in the exact same scenario.

What you could do here is to deploy the projects into different environments / targets on the report server. For example you could create different "folders" in the reportserver and deploy it multiple times. Then configure the sql login with different passwords there once and prevent to overwrite the datasources during deployment to protect it from getting overwritten. This can be configured in the project's settings.


Adding 4th table to join jumps processing time from seconds to over 20 minutes I am trying to join a total of 4 tables together. When I only join 3 of the tables together I get a result set of about 220,000 records in about 2 seconds, but as soon as I throw the 4th table in, the script will run for an extremely long time (over 20 minutes during one test) and will return million of rows.

I can't figure out why adding this last table causes this performance hit and all these extra rows to show up. As far as I can tell none of the rows are duplicates, but I have only spot checked, and never been able to run the script to completion.

SELECT  e.ID AS EmployeeID,
        NULL AS TimesheetHeaderID,
        ts.StartDateTime AS StartDateTime,
        ts.EndDateTime AS EndDateTime,
        pc.ID AS ProjectID,
        tc.ID AS TaskID,
        ts.[Description] AS WorkDescription,
        ts.UserID AS Name,
        ts.WeekEnding AS WeekEnding
FROM JobsDB.dbo.Timesheets AS ts
    INNER JOIN dbo.Employee AS e
        ON e.ADUserName = ts.UserId
    INNER JOIN dbo.ProjectCode AS pc
        ON pc.Code = ts.JobCode
    INNER JOIN dbo.TaskCode AS tc
        ON tc.Code = ts.JobSubTypeId
WHERE ts.WeekEnding >= '2014-01-01';

It is only when I include TaskCode table that I have the problem.

Link to execution plan
Table structure and indexes

There could be a task with code "Admin" a dozen times because a dozen projects have an "Admin" task.

I have no idea why Timesheets is a heap. It is the old database I am migrating info from, and was created in the manner  of "just make it work and don't care about doing it right".

I have triple confirmed the join between Code and JobSubTypeId. Looking at the schema for Timesheets the Id column is not a PK, so I could possibly make it one.

I noticed something that might work and generated this code:

SELECT  e.ID AS EmployeeID,
        NULL AS TimesheetHeaderID,
        ts.StartDateTime AS StartDateTime,
        ts.EndDateTime AS EndDateTime,
        pc.ID AS ProjectID,
        tc.ID AS TaskID,
        ts.[Description] AS WorkDescription,
        ts.UserID AS Name,
        ts.WeekEnding AS WeekEnding
FROM JobsDB.dbo.Timesheets AS ts
INNER JOIN dbo.Employee AS e
ON e.ADUserName = ts.UserId
INNER JOIN dbo.ProjectCode AS pc
ON pc.Code = ts.JobCode
INNER JOIN dbo.TaskCode AS tc
ON tc.Code = ts.JobSubTypeId
AND tc.ProjectID = pc.ID --Works but not all rows return
WHERE ts.WeekEnding >= '2014-01-01';

This code works, mostly. With it the query runs in seconds and returns 163,222 rows of data. Not as much as the 3 table join so I need to dig though the data to see if this is really working and I am just missing some task codes or if this is a false positive.

SQL is not making up rows.
If that join is producing more rows than you expect then figure it out.
Are you sure ts.JobSubTypeId is a task code?
That name does not sound like a task code.

Try this - it will show you were the volume is coming from
In your query each count is a row

  -- this is the raw count from Timesheets
  SELECT ts.JobSubTypeId, count(*) as countRaw
  FROM JobsDB.dbo.Timesheets AS ts
  WHERE ts.WeekEnding >= '2014-01-01'
  GROUP BY ts.JobSubTypeId
  ORDER BY count(*) desc;

  -- this is the count brought in by the join to TaskCode
  SELECT ts.JobSubTypeId, count(*) as countJoin
  FROM JobsDB.dbo.Timesheets AS ts
  LEFT JOIN dbo.TaskCode AS tc
         ON tc.Code = ts.JobSubTypeId
  WHERE ts.WeekEnding >= '2014-01-01'
  GROUP BY ts.JobSubTypeId
  ORDER BY count(*) desc;

  -- this is the raw counts in TaskCode
  SELECT tc.Code, count(*) as countCode
  FROM dbo.TaskCode AS tc
  GROUP BY tc.Code
  ORDER BY count(*) desc;

The fact that when you finally ran the second query and it ran fast is a very strong indication you have a volume and not an index issue.  That count(*) had to touch those rows.

Even if JobSubTypeId is Code that is clearly not a sufficient join

ON tc.Code = ts.JobSubTypeId

TaskCode has a number of very detailed columns
Your are clearly bring in massive data from other projects


When To Update Statistics? I've inherited a Maintenance Plans that does the following:

Cleanup old data
Checks DB integrity
Performs Database and Transaction Log Backups
Reorganizes Our indexes
Updates Statistics
Delete old backups and Maintenance Plan files

Of the 23 minute Maintenance Plan, Updating the Statistics takes a staggering 13 minutes. During this 13 minute period, access to the database is blocked (or at least, replication from this DB to our others is paused).

My Question Is:

When should we be Updating the Statistics, and why?

This seems like the kind of thing we should do less frequently than every day. I'm trying to get us out of the "just because" mind-set of doing unnecessary Maintenance.

If you don't have the maintenance window for it, updating statistics daily is probably a little overkill.  Especially if you have Auto Update Statistics turned on for the database.  In your original post, you said that users are seeing a performance degredation due to this maintenance plan.  Is there no other time to run this maintenance plan?  No other window?  I see that your plan encompasses index reorganization, when are you rebuilding indexes?  When that operation happens, statistics are automatically updated (provided that isn't turned off for the index).

Exactly how often you should be updating statistics depends greatly on how much data modification your indexes and data is receiving.  If there is very little modification (INSERT, UPDATE, DELETE) to the data, then you could have a more infrequent schedule for the update statistics job.

One way to find out if your statistics are stale is to look at the execution plans and if you estimated rows greatly differ from your actual rows returned then that is a good indication that the interval needs to be upped.  In your case, you're going the other way and a bit of trial may be in order for you.  Update statistics weekly, and if you're starting to see the tell-tale signs of stale stats then go from there.

If you are using Auto Update Statistics for your database, see this reference for the threshold of when statistics are updated.


Inserting a list of emails into multiple rows on the same column I have a list of emails of staff members and would like to:

insert them into multiple rows on the same column (single column only for emails)
the same question above but would like to replace them with old ones.

You should provide details about:

The table you want to update
The format of the data of the email list.

The obvious problem is that just a list of email adresses wont't help much, since you don't know who they belong to. So what is the unique key for the table identifying the employee, and is that key also in the list of email adresses?

If the list provides the key, what you can do is load that list into a temp table (using sql loader), and then run a procedure or script that updates the table based on the values and unique key of the employee from the temp table, and then when done and no errors, remove the temp table.


How do I unload data from a database to a flat file? I am aware of the concept of bulk insert wherein I load data from a flat file to a
database.

Now I want to unload data from a table to a flat file, i.e. I want to export data in a table to a flat file. Can anyone tell me how to do this?

I am using SQL Server 2008 R2.

Right click on the database, under "Tasks" look for "Export Data", here the selected database is preconfigured as "Data Source" on the next screen you define the "Destination", change this to Flat File Destination and go from there. Later on you can use this as a template for SSIS if you need to do it often.


LOG backup Always ON using Ola Hallengren database backup script does not carry out a backup I am trying to run a LOG backup on the primary node of an Always ON SQL Server 2012 environment. The secondary server is only used for replication. no read or other access permitted. (licensing of microsoft EE).

When I do a full backup using Ola Hallengren scripts, all goes well. A full backup is placed in the location I need.
But when I run the LOG backup, it does not carry out the backup at all.
Testing a Differential backup also went fine.
Doing a LOG backup manually, without Ola's script it does work too.

The backup I try is

USE [master]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[DatabaseBackup]
        @Databases = N'TESTAlwayON',
        @Directory = N'c:\backup',
        @BackupType = N'LOG'

SELECT  'Return Value' = @return_value

GO

The return value is 0, and the message shows all (default) values back, but not the actual backup statement, like it does when doing a FULL backup.

Any thoughts, what I do wrong?

Jimmy

UPDATE : Fixed.
I ran the command

ALTER AVAILABILITY GROUP AG1 SET (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)

And now it keeps the setting on primary and my log backups are running fine.

Thanks for the push in the correct direction.

Your availability group is set to prefer backups on the secondary, and by default Ola's script is going to only run log backups on the node that is preferred.

Use the @OverrideBackupPreference='Y' parameter in the backup command to override that setting so that it will take the log backup on the node where the script runs regardless of the backup preference setting of the availability group.


How to see profile assigned to a user Let's say i have created a user as 'TEST_USER', and then i assigned a profile to it.

Now how can i see for TEST_USER which profile has been assigned?

I want some query that will check what profile a user is assigned with.

In order to find out the users and the profile assigned you can use the commands below.

DESC DBA_USERS;

This will show you all the fields name for which you want to query

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

And this command will show you the user name, profile and account status i.e. which profile is assigned to which user


MSSQL query to select top 2 values I am not from a DBMS background and I couldn't find much help from reading some resources once.
I have a requirement like:  There are 5 interfaces with some execution time. My goal is to find the top 2 interfaces which took the most time.
  RowNum   Interface    TIME
  1        Intf1        0
  2        Intf2        3
  3        intf5        1
  4        Intf2        4
  5        intf3        3
  6        Intf2        2
  7        intf3        5
  8        intf3        2
  9        inft4        2
 10        Intf2        0
 11        intf5        1

The max time taken is 5 which corresponds to Intf3 (so print all intf3  with their times) and the second max time taken is 4 corresponding to  Intf2(so print all intf2  with their times). So my expected output is:
RowNum  Interface   TIME
  7       intf3      5
  5       intf3      3
  8       intf3      2
  4       Intf2      4
  2       Intf2      3
  6       Intf2      2
  10      Intf2      0

I tried something like
select Row,Interface,Time
from (select Row,Interface,Time from NAMES order by Time desc)

but it didn't seem to work as I was expecting.

There are different ways to do this ...
Here is one
CREATE TABLE Table1
    ([RowNum] int, [Interface] varchar(5), [TIME] int)
;

INSERT INTO Table1
    ([RowNum], [Interface], [TIME])
VALUES
    (1, 'Intf1', 0),
    (2, 'Intf2', 3),
    (3, 'intf5', 1),
    (4, 'Intf2', 4),
    (5, 'intf3', 3),
    (6, 'Intf2', 2),
    (7, 'intf3', 5),
    (8, 'intf3', 2),
    (9, 'inft4', 2),
    (10, 'Intf2', 0),
    (11, 'intf5', 1)

SELECT t.*
FROM table1 as t
  INNER JOIN
  (
     SELECT Interface, Time ,
         ROW_NUMBER() OVER(ORDER BY a.Time dESC) as rn
     FROM Table1 as a
  ) AS P
  ON t.Interface = p.Interface
WHERE
  p.rn <=2
ORDER BY p.Time DESC,p.Interface,t.Time DESC

And output:
RowNum  Interface   TIME
7   intf3   5
5   intf3   3
8   intf3   2
4   Intf2   4
2   Intf2   3
6   Intf2   2
10  Intf2   0

dbfiddle


Select only neighbour rows who pass a where-test I want to apply a selection process to my data, that will not include rows which should be accepted by my WHERE clause but who follow after a row which fails the WHERE.

Example. I have a list of gps locations and want to put them in a tour entity, if they surpass a speed limit.

SELECT _id, speed FROM location;

results in (sqlite3 syntax):

1|0
2|0
3|3
4|3
5|3
6|0
7|0
8|0
9|3
10|3
11|3
12|0

So, if I SELECT for WHERE speed > 2 now, I will get 3,4,5 and also 9,10,11. These both clearly should be separated tours. Can I formulate my SQL in a way that it only results in 3,4,5 because the following entity will result in a failed WHERE speed > 2 test?

First I thought, that I can simply return all these entities, but now I think there will be 2 problems with that. First there might be a lot of these, when this SELECT is called. And second there might be deleted rows. So instead of the 11 row there might be a lot higher number as _id which still should belong to the same tour, because there are no rows between 10 and this row and both pass the WHERE speed > 2. That there are no rows failing the WHERE between 10 and the next row is totally unknown the Application waiting for the result of this SELECT.

It is on purpose that I don't ask for a database specific answer. If you know an easy solution for database X or Y there is no problem adding it as a solution. The real question is, how to solve it with standard SQL, though. I think every well known database should have some way of solving any task out of the standard solvable task set.

I don't know if this will work in sqlite3, but you didn't tag the question with that, so here is an Oracle solution.

SELECT ID, Speed FROM
(
    SELECT ID, Speed, MIN(Speed) OVER (ORDER BY ID) InSequence
    FROM
    (
        SELECT ID, Speed, SUM(Speed) OVER (ORDER BY ID) SpeedSum
        FROM location
    )
    WHERE SpeedSum > 0
)
WHERE InSequence > 0
ORDER BY ID;

The SUM(Speed) analytic removed the leading zero speed rows and the MIN(Speed) analytic allows us to eliminate rows starting with the first zero speed row remaining.  The sample data can be created as follows:

drop table location;

create table location as (select level id,
   case when level between 3 and 5 then 3
        when level between 9 and 11 then 3
        else 0 end speed from dual connect by level <=12);

select * from location;

It seems to behave correctly through all the following variations, but there could be a pattern I've missed:

UPDATE location SET Speed=1 WHERE ID=2;
UPDATE location SET Speed=2 WHERE ID=3;
UPDATE location SET Speed=1 WHERE ID=6;
DELETE FROM location WHERE ID IN (7,9);

UPDATE location SET Speed=0 WHERE ID=2;
UPDATE location SET Speed=5 WHERE ID=1;

This solution only requires one full table scan rather than the two Noam's solution requires, but it only finds the first set of values whereas Noam's finds all the sets.  It also assumes a threshold of zero, but could be modified for any threshold.


Can lowering fill factor result in more page splits? We've been having a problem with page splits on a table that is a particular nuisance - its an audit log of activities in the database and has grown over 1TB.  The main indexes are on the record type, which is an NVARCHAR(100) - because you need that when there are 5 record types - it makes much more sense than a TINYINT, and a record id - which is an NVARCHAR(200) instead of the integer key of the record.

They are also covering indexes, with the key, old value, new value, etc - very wide.

This is an old system, and unfortunately the code for this auditing is everywhere rather than centralized in a procedure. It can't be changed, and we're going thru the painful process of a long micro-services re-write.

So, I have reduced the fill factor on two of the indexes from 100% to 85%.

And the page splits have gotten worse. I'd say around 3x more page splits.

Is this a common outcome? Most advice says to reduce fill factor to improve page split performance.  I can understand why it may do this, because of the width of the data in the keys.

Would the advice be to reduce the fill factor further, or to put it back to what it was?

When you have lots of page splits caused by random middle-of-BTree inserts, your pages will naturally average around 65%* full.  Split pages start at 50% full and any that fill up past 100% get split back down to 50%.

So rebuilding your index with a fill factor of 85% might temporarily increase page splits, just not as much as rebuilding with a fill factor of 100%

*Why not 75%?  I think it's because each split page covers half the value range of the original page, the new pages fill up half as fast as the original page.


How to change default engine in WAMP to InnoDB Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.

Find mysql configuration file my.ini, find default-storage-engine setting line in [mysqld] section, insert/edit it to default-storage-engine=INNODB, save changes, restart server service.


Connect to SQL Server using OLEDB or ODBC? We have a desktop application written in Visual Basic .NET. One our clients needs to use ODBC instead of OLEDB to connect to the SQL Server 2008 R2 Database. The company has several databases including Oracle, IBM DB2, and PostgreSQL.

We can migrate the connections, but we need to know what is the better: OLEDB or ODBC when connecting to SQL Server 2008 R2?

For most applications hitting SQL Server 2008 R2, you would be fine going either way, I think.

OLEDB support is being removed after SQL Server 2012 but until it is, you'd likely be fine either way based on your standards and your needs. That said, I'd look to future supportability and consider going ODBC with the Native Client.

I would say a more important decisions would be to consider using the Native Client for your .net apps if you aren't already.


Invoke-SQLCmd Date output format I’m having a problem with a result set that I am getting from a stored procedure when running a PowerShell script (Using Invoke-SQLCmd) to get the results and send the output to a .csv file.

The date column is bringing back the time as well, see below. I have setup a simple example in Adventure works.

My Command is to execute a simple stored proc to just get the Top 10 From the Sales Order Header in Adventureworks.

Invoke-Sqlcmd -ServerInstance "MY-PC\SQL2016" -Database "AdventureWorks2016" -Query "EXEC[AdventureWorks2016].[dbo].[SOH]" | Select-Object -Property SalesOrderID, OrderDate

That’s fine and I can filter out the columns I want, but I have the time 00:00:00 in the order Date Column. And I just want the date. I have googled and I am still having problems with this one. See the result set below. I need the OrderDate to just be the date. Not append 00:00:00 for the time.

SalesOrderID OrderDate
------------ ---------
43659 31/05/2011 00:00:00
43660 31/05/2011 00:00:00
43661 31/05/2011 00:00:00
43662 31/05/2011 00:00:00
43663 31/05/2011 00:00:00
43664 31/05/2011 00:00:00
43665 31/05/2011 00:00:00
43666 31/05/2011 00:00:00
43667 31/05/2011 00:00:00
43668 31/05/2011 00:00:00

If you are on SQL-Server 2012 or above you can use FORMAT function in your stored procedure.
FORMAT ( value, format [, culture ] )

DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'

If you use an older version you can use CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT CONVERT(varchar(10), @d, 103);

| DateTime Result |
| :-------------- |
| 31/05/2011      |

| (No column name) |
| :--------------- |
| 31/05/2011       |

db<>fiddle here


Impact of "_oracle_script"=true in Oracle database 18c I just installed Oracle XE 18c (I migrated from 11g XE) ... After running the installer, I saw new features; for example, Oracle now supports a multitenat model and a concept called pluggable databases ...

The first problem that I found was creating a new user ... it appears that in order to support the concept of pluggable databases, user names must comply with some naming rule prefixes (i.e. c##) ... Searching about the problem, I found this link  ... They suggest that to create a user using the old syntax (the one used on non-CDB), we should alter the session using:

alter session set "_ORACLE_SCRIPT"=true;

But, they warn that the user cannot be used in any pluggable database ...

So, this is my question: If a create the user after submitting the command, is the user created in the ROOT Container (CDB$ROOT)? Such user will be local to the Oracle instance and can be used to administrate all other pluggable databases, including the CDB (assuming that such user has all required permissions)?

Thanks!

Disclaimer: I'm not a DBA, I'm just a software developer with advance knowledge about databases ...

Yes, common users, those starting with C##, will be created in the CDB.  They will available in all PDBs (assuming they have the correct permissions).

If you need to create a user in the PDB, make sure you connect to the PDB ( SID=XEPDB ) not the CDB (SID=XE )


How to select multiple columns but only group by one? I have a problem with group by, I want to select multiple columns but group by only one column. The query below is what I tried, but it gave me an error.

SELECT Rls.RoleName,Pro.[FirstName],Pro.[LastName],Count(UR.[RoleId]) as [Count]
from [b.website-sitecore-core].[dbo].[aspnet_UsersInRoles] UR
inner join [b.website-professional-au].[dbo].[Profile]  Pro
on UR.UserId = Pro.Id
inner join [b.website-sitecore-core].[dbo].[aspnet_Roles] Rls
on Rls.RoleId = UR.RoleId
inner join [b.website-professional-au].[dbo].[Gender] Gn
on gn.Id = pro.GenderId
GROUP BY Rls.RoleName;

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options:

Add the additional columns to the GROUP BY clause:

GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]

Add some aggregate function on the relevant columns:

SELECT Rls.RoleName, MAX(Pro.[FirstName]), MAX(Pro.[LastName])

The second solution is mostly a workaround and an indication that you should fix something more general with your query.