What is the preferred / common way to separate "access-tokens" in a database design I'm currently having a case where a user has to verify their email, is able to reset his password and can log into a web-interface (so far and not limited to). There a now 3 places where some kind of token is used to give a user access to a specific action.

Web session - Access token used to access API endpoints.
Email verification - Access token used to verify a (new) email-address.
Password reset - Access token used to change a password.

So far I've come up with two possible solutions:
a) A separate table for each token:
There will be a web_session, email_verified and password_reset table.
Pros:

Each table can come with meta-unique properties
You can easily grab data from on of these tables based on their name

Cons:

All tables are kinda related but have very different names (and I couldn't come up with a naming-way to "group" them)
Almost all columns are equal across these tables (id, user_id, token, created_at)
Every time something like this gets added I'd have to create a seperate table with (always / almost?!) the same columns.

b) A unified table with a type / role column:
There's just one table with id, user_id, token, created_at and type (enum) - type can in this case be all sorts of access-type, in our case api-session, email-verification and password-reset. (Of course this could be improved in a way that a token will give general access to a specific thing)
Pros:

It's just one table
Can easily be extended without making it hard to use (I'll always be one table)

Cons:

Another conditional where you'll have to check the type before looking for the token itself
Can get confusing I guess as although it's only one table there are differences based on type

Is there a generally preferred way of doing this (I'm using mariadb if that's important) / is there a third, better way of doing it? Or does it really not matter that much and I'm just overthinking it?
I tried to find existing schemes / db-designs but I couldn't find anything useful as there is afaik no name for this whole thing (please tell me I'm wrong and I just cannot search correctly).

The answer is neither.  Since a user could have multiple tokens, the primary key is not the same and it's meaning within the context of the application is not the same as the tokens used for email verification/password reset.
So:

You have a table for session_token.  If you will search by token to validate a user then the primary key is token.  If you will primarily search by user the primary key will be (user_id,token) with an unique constraint on token.
You have a table for user_token or something similar.  The primary key is (user_id,token_type).  You may or may not need an alternate key on token depending on how things are used.  This is preferable to separate tables as you can add a new type without creating a new table.  You will need a table token_type that provide integrity to that column.  Use values that are easy to understand.

Do not slap an row identifier on each table (id) as:

It does not provide uniqueness
It is unnecessary to replace the existing primary key
It is counterproductive as it will never be used to access either table.


How do I export Postgres data in "Insert ... on conflict" format instead of "cp" format? I’m using Postgres 9.5 on Mac Sierra.  I want to export some records from my local db and import them into a db on a Linux machine (also running PostGres 9.5).  I’m using this command to export data from my local machine …

localhost:myproject davea$ pg_dump -U myproject mydb -a -t table1 -t table2 -t table3 > /tmp/pgdata.sql

The data is exported in a series of copy commands.  Is there a way to export the table data so that the file has a bunch of “INSERT ... ON CONFLICT DO NOTHING;” statements?  There are some duplicates in the origin database and the destination db but I don’t want that to derail the import of the non-duplicate data.

pg_dump won't allow you to do exactly what you ask for, but you have an option that might be good enough. According to the documentation of pg_dump, you have the --inserts option:

  --inserts

  Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

For instance, let's assume you use myhost and mydb.

We create and populate one table (in one schema):

CREATE SCHEMA s1 ;

CREATE TABLE s1.t1
(
   id serial PRIMARY KEY,
   column_1 text,
   column_2 text
) ;

INSERT INTO
    s1.t1 (column_1, column_2)
VALUES
    ('Some value', 'and another one'),
    ('Again some value', 'and some more') ;

At this point, we back it up:

pg_dump --host myhost --format custom --section data --inserts --verbose --file "t1.backup" --table "s1.t1" "mydb"

After the backup, we delete one of the rows at the table, but we still leave one:

DELETE FROM
    s1.t1
WHERE
    id = 1 ;

At this point, we do restore the backup (this is what you would normally do on your second database), and get the following messages:

pg_restore --host myhost --dbname "mydb" --section data --data-only --table t1 --schema s1 --verbose "t1.backup"

pg_restore: connecting to database for restore
pg_restore: processing data for table "s1.t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2759; 0 21286 TABLE DATA t1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (id)=(2) already exists.
    Command was: INSERT INTO t1 VALUES (2, 'Again some value', 'and some more');
pg_restore: setting owner and privileges for TABLE DATA "s1.t1"
WARNING: errors ignored on restore: 1

Process returned exit code 1.

The restore process generated one error (the row that was already on the table), but did insert the rest of the data.

Although this is not exactly what you were asking for, for all practical purposes you achieve the result you're looking for.


Unattended install to listen on specified interface I'm looking to install several versions of SQL Server side-by-side to run simultaneously. I'd like to have them listen on different IP addresses.

They will be running on Windows Server 2019 Core.

I currently install using something like this:

Setup.exe /qs /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SAPWD="MYSECRETPASSWORD" /SQLSVCINSTANTFILEINIT="True" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL

Am I right that there are no switches on Setup.exe that specify the IP address to listen to? If not what is the simplest way of configuring this from the command line after Setup.exe has run?

With SQL Server on Windows, the registry entries can be modified using PowerShell. Below is an example with SQL Server 2017 paths for the default instance. Of course, the instance will need to be restarted for changes to become effective.

Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpdynamicports -Value ''
Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpport -Value 1433

EDIT:

One can also target specific network interfaces by identifying the desired key and changing as desired. Below is an example PS function that finds the entry by IP address and modifies the values. This can be used to enable/disable TCP for each instance and interface as needed.

Function Set-SqlInterfaceProtocol($SQLInstanceTcpPath, $IPV4Address, $Enabled, $Active, $Port) {

    $interface = Get-ChildItem $SQLInstanceTcpPath `
        | ForEach-Object {Get-ItemProperty $_.pspath} `
        | Where-Object -Property IPAddress -EQ $IPV4Address

    if($interface -eq $null) {
        throw "No entry found for IP address $IPV4Address"
    }

    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Enabled" -Value $Enabled
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Active" -Value $Active
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "TcpPort" -Value $Port
}

$SQLInstanceTcpPath = "HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp"
Set-SqlInterfaceProtocol -SQLInstanceTcpPath $SQLInstanceTcpPath -IPV4Address "10.1.1.123" -Enabled "1" -Active "1" -Port "1433"

Note this script is not well-tested so you may need to tweak it for your scenario.


Multiset operators on nested tables with non scalar types I have a function, which performs multiset operation on two nested tables and returns result:

function odejmijEgz(tab typ_NT) return typ_NT is
  tab1 typ_NT := typ_NT();
  cursor c_osr_egz is select distinct o.id_osrodek oid, o.nazwa_o nazwa_o from egzaminy e
    right join osrodki o on e.id_osrodek = o.id_osrodek
    where e.id_osrodek is null;
  i number;
  begin
    for v_osr in c_osr_egz loop
      i := c_osr_egz%rowcount;
      tab1.extend;
      tab1(i) := v_osr;
    end loop;
  return tab multiset except tab1;
  end odejmijEgz;

where types are declared in pl/sql block:

type typ_Rec is record(Id osrodki.id_osrodek%type,
                        Nazwa osrodki.nazwa_o%type);
type typ_NT is table of typ_Rec;

Parameter of the function is nested table, populated with records.

When I use multiset except in function I got an error:

Error: PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'

But if I change it to multiset union it works. The tables are of the same type. Whats wrong with that?

MULTISET EXCEPT

  The element types of the nested tables must be comparable.

Comparison Conditions

  Two objects of nonscalar type are comparable if they are of the same
  named type and there is a one-to-one correspondence between their
  elements. In addition, nested tables of user-defined object types,
  even if their elements are comparable, must have MAP methods defined
  on them to be used in equality or IN conditions.

The problem is, the above can not be done with a record type. record types have only fields, and can not have member functions. Furthermore:

Record Comparisons

  Records cannot be tested natively for nullity, equality, or
  inequality.

  These BOOLEAN expressions are illegal:

  My_Record IS NULL
  My_Record_1 = My_Record_2
  My_Record_1 > My_Record_2

This fails:

create or replace package p1 as
  type typ_Rec is record(id customers.customer_id%type, name customers.cust_first_name%type);
  type typ_NT is table of typ_Rec;
  function f1 (tab typ_NT) return typ_NT;
end;
/

Package created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_EXCEPT_ALL'

UNION works, because you did not specify ALL or DISTINCT, and in this case, UNION ALL is the default. UNION ALL does not need to perform comparison. But if you try UNION DISTINCT (which removes duplicates), that will also fail:

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset union distinct tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_UNION_DISTINCT'

You need an object type for this.

create or replace type typ_Obj as object
(
  id number(6),
  name varchar2(20)
);
/

Type created.

create or replace package p1 as
  type typ_NT is table of typ_Obj;
  function f1 (tab typ_NT) return typ_NT;
end;
/

Package created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_EXCEPT_ALL'

Notice how it still failed, because I did not define the MAP function, I did not tell the database how it can compare two user-defined objects. Now try again, this time with defining MAP (and here I assume objects have unique identifiers and two objects equal if their identifiers equal):

create or replace type typ_Obj as object
(
  id number(6),
  name varchar2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER
);
/

Type created.

CREATE or replace TYPE BODY typ_Obj AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN id;
  END;
END;
/

Type body created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Package body created.

And finally it worked, no warning.


Call multiple functions from trigger? I run PostgreSQL-9.2.4

Is it possible to call 2 functions from a trigger?

Let's say I have two functions for two different tables to be executed if following trigger fires:

Trigger:

CREATE TRIGGER start ON system_status FOR EACH ROW
WHEN ((new.event = start_task))
EXECUTE PROCEDURE ...()

Function 1:
(When task starts => remove any previously assigned next task for this system)

CREATE FUNCTION void_next_task() RETURNS trigger AS $$

BEGIN
  DELETE FROM tasks_status ts
  WHERE ts.system = NEW.system
  AND ts.event = 'next_task';
  RETURN NEW;
  END;

$$

LANGUAGE plpgsql

Function 2:
(If inserted combination of task and system already presented in the table => mark any earlier records with this combination as deleted)

CREATE FUNCTION void_dup_task() RETURNS trigger AS $$

BEGIN
  UPDATE system_status ss
  SET deleted = 'TRUE'
  WHERE ss.system = NEW.system
  AND ss.task = NEW.task
  AND ss.deleted IS FALSE;
  RETURN NEW;
  END;

$$

LANGUAGE plpgsql

So I ended up with following ways to resolve it:

To have a trigger which calls two functions;
To have a function which performs update on one table and delete on another one;
To have two exactly same triggers and two different functions;

Before I will go ahead and implement solution 3 could you advice me if solution 1 or 2 are possible at all?

A trigger can only ever call one tigger function, so no to item 1.

The preferable form is item 2. IMO. You can put as many SQL statements into a single plpgsql function as you want.

Item 3. is possible, too. Well, not exactly the same trigger, the name would have to be different. Triggers on the same event fire in alphabetical order, btw. But I see no gain in two separate functions. Just more code and overhead and two function invocations, which is more expensive.

2. is the undisputed victor.


Problem trying to remove sub-query and use WHERE only to return certain rows I am extremely new to SQL queries. I am studying for Microsoft 70-461 and came to an example that I thought could be simplified, but my version is returning the wrong results, and I wanted to understand why.

This study guide show this query:

USE TSQL2012;
SELECT productid, productname, unitprice, P.supplierid
FROM Production.Products AS P
WHERE supplierid IN
    (SELECT supplier
     FROM Production.Suppliers
     WHERE country = N'Japan');

Which correctly returns 6 rows,
My query:

USE TSQL2012;
SELECT productid, productname, unitprice, P.supplierid
FROM Production.Products AS P, Production.Suppliers AS S
WHERE S.country = N'Japan':

Returns 231 rows, the production.products table only contains like 70 rows to begin with so I don't get it. Can somebody please tell me what I am overlooking. Thank you in advance.

The sample database, "TSQL2012", can be downloaded here.

Without a predicate to describe matches between the two tables, you're simply listing all product / Japanese supplier combinations, regardless of whether or not the supplier is the right one for each product.

USE TSQL2012
GO

SELECT
    p.productid ,
    p.productname ,
    p.unitprice ,
    p.supplierid
FROM    Production.Products p
JOIN  Production.Suppliers s ON p.supplierid = s.supplierid
WHERE   s.country = N'Japan';


Attach database failed for Server '{SERVER_NAME}\{INSTANCE_NAME}'. (Microsoft.SqlServer.Smo) Strange, I tried to attach the database AdventureWorks from this site - http://sqlserversamples.codeplex.com/

However, I encounter the following problem while attaching AdventureWorks2008R2_Data.mdf

  Attach database failed for Server '{SERVER_NAME}{INSTANCE_NAME}'. (Microsoft.SqlServer.Smo)

  Additional Information:

  An Exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  Directory Lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" failed with the operating system error 3 (The system cannot find the path specified.). (Microsoft SQL Server, Error: 5133)

You need the "AdventureWorks2008R2_Log.ldf" file.  And put it in this folder:
"C:\Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\DATA\"

The easiest thing to do is download the "full backup" file from that site and then restore it, (instead of attaching.)


Setting Up SQL Alerts I have turned on notifications via email on my SQL Server Agent if any of my scheduled jobs failed or is successful.

Can anyone help me if I want to set up Alerts, if in case one of the scheduled job is running longer than expected.  Best is probably thru SSMS.

One option would be to handle some of this logic inside your code running in the job... this works if you are doing things in batches, or if you are using a cursor or something similar.

Procedure to be Executed by Job

--create some_stored_procedure as

declare @procStartDate datetime = getdate()
declare @threshold int = 2

--doing something in batches, like batch delete, etc.
--could also uses a cursor here for reindexing, or what ever...

waitfor delay '00:00:05'

--after each batch, check the time that's expired
--if your threshold is met, put an informational message in the error log--or a fatal error if you want it to stop
if datediff(second,@procStartDate,getdate()) > @threshold
begin
    exec xp_logevent 50001,'Stored procedure: some_stored_procedure has been running past threshold', informational
end

select
    ProcStart = @procStartDate,
    CurrentDT = getdate()

Set Up Alert

General

Name it and select Type = SQL Server event alert
Set Severity = 10
Check "Raise Alert when message contains" and enter "some_stored_procedure" or something unique to the message in xp_logevent

Response

Check Notify operator and set the operator

That is, test it out! You can just create the alert and run the code above without creating a proc and it should email you.

Another Option

If this ins't feasible, and your threshold is high enough where you wouldn't mind running a job every X minutes, you could create a job to query the sys.jobactivity for jobs running that don't have a stop time and then send an email with sp_send_dbmail. Or, if your job has a lot of small steps, add this to the end of each step. What ever works for your environment.

declare @minuteThreshold int = 20

if exists(
SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
WHERE
    start_execution_date is not null
    AND stop_execution_date is null
    AND j.name = 'yourJobName'
    AND datediff(minute,ja.start_execution_date,getdate()) > @minuteThreshold)

exec sp_send_dbmail
    <your parameters here>


Regnerating an execution plan - is this a valid approach? If I am optimizing a query, in order to ensure that this query does NOT uses cached execution plan I use OPTION (RECOMPILE) with the query. Is it a good approach?

OPTION (RECOMPILE) can change the execution plan.

For example if you try the below you will see a different plan with and without the hint commented out (on SQL 2008 SP1 CU5 and later) .

EXEC sp_executesql N'
SELECT COUNT(*)
FROM master..spt_values
WHERE @number IS NULL OR number =@number
--OPTION (RECOMPILE)',
N'@number INT', @number=1

If you connect via the DAC then the plans seem to be neither used from the cache nor saved to the cache without this optimisation side effect of RECOMPILE (which may be a practical solution if you are developing against your own instance).

Or failing that on 2008 you can at least be more surgical about just removing the specific plan as shown below.

DECLARE @plan_handle varbinary(64),
        @RC INT

SELECT @plan_handle = cp.plan_handle
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE  t.text LIKE '%@number IS NULL%' AND t.text NOT LIKE '%this_query%'
/*Needs to match some unique text in the query. Add a commented out GUID to it
  if there is no obvious candidate.*/

SET @RC = @@ROWCOUNT

IF @RC = 1
    DBCC FREEPROCCACHE (@plan_handle);
ELSE
    RAISERROR('Cache not cleared, %d matching rows found',11,1,@RC)


Database : what design to point on two possibilities? I am designing a datamodel for a new project.

One of the requirements specifies that some objects can point either a person or a company.

What is the smartest way to achieve that?

I have thought about a table link "actor" like this (drawn with the excellent yUML.me BTW) :

In the actor table, according to actor_type, person_id or company_id is a foreign key on its corresponding table or is NULL. This way, when one_table wants to retrieve details about the actor, I start by checking the actor_type field and retrieve either person_id or company_id.

It is working, but I am looking for a better design.
Here is the link for editing the diagram

It seems, that you don't need the actor_type table at all, since there is just those two types. As the column and table names are already human readable, even more actor types could be added the same way.

The actor type is neither necessary for ruling out which one this actor is, since the other(s) can be left NULL. Only one being NOT NULL can also be made mandatory with constrains, e.g.

ALTER TABLE actor
ADD CONSTRAINT OnlyOneActor
CHECK (
   (CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN person IS NOT NULL THEN 1 ELSE 0 END)
   = 1
)

However, this won't actually work as supposed in MySQL: Reference Manual states that

  The CHECK clause is parsed but ignored by all storage engines.

You can achieve the same functionality in MySQL by using a TRIGGER instead, e.g.

DELIMITER //
CREATE TRIGGER OnlyOneActor BEFORE INSERT ON actor
FOR EACH ROW BEGIN
  IF (
     (CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN person IS NOT NULL THEN 1 ELSE 0 END)
     != 1) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Only one type of actor allowed.';
  END IF;
END//
DELIMITER ;


Add user to sql server with the required permission In my sql server express 2008 r2 i have about 15 login account. Now i want some about 5 users have full permission while some other 5 users with limited access to some database, and other users only read permission. In future i may add other users also. So i was thinking of doing it in simple way than giving permission for every user as it requires giving permission to each database every time a new user is created. So can i create a set of rules or group and then add a new user to that group or rules? Do i have any kind of facility in sql server which will make this happen?

Use a database role.  Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once.  However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>.  Once it is created you can then grant the role permissions just like you would a user.  Then you add users to the role.  Again you can either use the GUI or the command EXEC sp_addrolemember '<rolename>','<username>'.  And last but not least there are some pre-defined roles at both the server and database level. (You can't create server roles in SQL 2008.)  You can not make changes to the pre-defined roles but among others there is db_datareader which grants SELECT permission to every table in the database.  Oh, and you can add your user defined role to the pre-defined role.  So you could create a role MyReadOnlyRole and add it to db_datareader and then grant it EXECUTE permissions to various stored procedures, functions etc.  Or EXECUTE permission to the database itself for that matter. (That gives permission to execute any SP, function etc in the database.)


Why does Oracle not support an autonumber data type? I know that Oracle implements autonumber functionality using sequences and triggers, but why doesn't it have a built-in autonumber data type as do most relational DBMSs? There are two specific parts to my question:

I know that sequences with triggers are generally more flexible than what autonumber fields in other DBMSs provide. However, what are the some particularly superior features of Oracle's implementation?
Considering that autonumber for primary key attributes is such a universal use case, what is the disadvantage of implementing it that keeps Oracle from adding such built-in functionality? After all, they can still keep sequences and triggers with any additional features they might provide.

I know that this question might be difficult, since it is not a "how-to" question but rather a question asking background behind Oracle's design decisions for their DBMS. However, I feel there is much to be learnt in understanding Oracle's thinking in this matter.

Well it does now - Oracle 12c introduced IDENTITY columns, see:

Identity Columns in Oracle Database 12c Release 1 (12.1)

e.g.

CREATE TABLE identity_test_tab (
   id          NUMBER GENERATED ALWAYS AS IDENTITY,
   description VARCHAR2(30)
);


Query to find GUID's "not in" null containing subquery returns no results When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and it only happens when using not in.

I know I can just do a not null check in my subquery, but I am curious why this does not work.

Query Example:

select a.guid from tableA a where a.guid not in (select b.guid from tableB AS b)

Working Test:

select 1 where newid() not in (select newid())

Broken Test:

select 1 where newid() not in (select null)

Just to provide some additional explanation to billinkc's answer.

If null is a trump card you might be wondering why doesn't WHERE 2 IN (2,3, NULL) exhibit the same behavior?

That one works as expected because it evaluates to (2=2) OR (2=3) OR (2=NULL).

Under the rules of three valued logic for Or-ed conditions if any of them evaluate to true the expression is true. Otherwise if any of them evaluate to unknown the expression is unknown. The only other possibility is that all are false in which case the expression evaluates to false.

In order for a row to be returned in SQL the WHERE clause must evaluate to true rather than false or unknown. The above does that.

The expression 1 NOT IN (2,3, NULL) evaluates to (1 <> 2) AND (1 <> 3) AND (1 <> NULL). When conditions are AND-ed all of them must evaluate to true in order for the expression to evaluate to true.

The presence of the NULL in the list guarantees that there will be at least one UNKNOWN and that this will never be the case. Hence the reason for the " NULL pooches it all" behaviour in this context.

To give an analogy here as to why the NOT IN behaviour makes sense.

  Three friends Tom, Dick, and Harry are sitting in a railway carriage
  with a complete stranger whose name is unknown to them.

  If Tom is asked "Is your name different from everyone else's in this
  carriage?" then it is impossible for him to answer with any certainty.

  Even though he knows that Tom <> Dick and Tom <> Harry (so the
  statement might be true) the veracity of the statement overall hinges on
  the stranger's name and this is not known.

  This is analogous to the SQL 'Tom' NOT IN ('Dick', 'Harry', Null)


Postgres 8.4 -- connection refused (remote) I'm new to postgres and looking to set up a small environment here with it.

I've installed PostgreSQL 8.4 onto an ubuntu server, able to login to the postgres, submit commands, do queries, etc... however, I'm looking at using pgAdminIII from my windows machine.

I've followed along with the instructions from the ubuntu help page and made the following two changes:

In pg_hba.conf I have added the line (using other IP information, which should be fine as it's tested elsewhere):

host    all         all         10.0.0.1 255.255.255.0    trust

in postgresql.conf I had originaly set listen_address = 'localhost,10.0.0.1', but changed to listen_address = '*' out of frustration after reading this guide.

How should I configure my listen files here so that postgres isn't listening to * ?

The 10.0.0.1 is the address of your client.  What you need to put into listen_addresses is an address on the server.  If you don't want to use *, then it should be the address of the server on the same network as the client.  In this case this would probably be some other 10.* address.  Check with /sbin/ifconfig or ip addr list what address that would be on your host.


Restoring SQL Server database with CDC I want to perform a database restore on SQL Server 2012 and copy the database to a different server. CDC is enabled on the original database.

I have managed to copy the database along with the CDC data using:

Restore Database dB4_res
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Backup\dB1_backup.bak'
with move 'dB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res.mdf',
move 'dB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res_log.ldf',
move 'dB1_CT.ndf' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res_CT.ndf'
with keep_cdc

where CDC data is stored in a different file db1_CT.ndf

This restores the CDC data in the new database. However it does not create the capture and cleanup jobs, required for future CDC operations.

So I create the capture and cleanup jobs as:

exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO

Now this operation deletes all past CDC data from the Change Tracking tables.

Please can anyone suggest how to fix this issue.

Thanks

Ok I got the answer.

I used the following:

EXEC sys.sp_cdc_add_job
 @job_type = N'cleanup'
,@start_job = 0
,@retention = 52494800;

This adds the cleanup job but does not start it immediately i.e. start_job set to 0 does the magic. Retention can be set accordingly (100 years in my case as we do not want data to be deleted)


How to remove triggers from the restored database in SQL Server I am using SQL Server 2008 Management Studio. I've got a .bak backup file from a client to resolve a specific issue.

Once I restore the database from the .bak file, I am unable to do any alter query or alter master key.

In my new database, all the triggers that were created at the client environment DB is attached to it. So getting the below error.

  Msg 208, Level 16, State 1, Procedure dbtrg_DBEventsmodel, Line 16
  Invalid object name 'DBMonitor.dbo.ddl_DBEvents'.

Is there any way to detach those triggers in the restored DB ?

You have a database trigger that logs DDL event to a table ddl_DBEvents in the database DBMonitor.

You can either delete or disable the trigger from SQL Server Management Studio in the Object Explorer under YourDatabase - Programmability - Database Triggers.

You could also disable or delete the trigger from code:

DISABLE TRIGGER dbtrg_DBEventsmodel ON DATABASE;
DROP TRIGGER dbtrg_DBEventsmodel ON DATABASE;

References:

DISABLE TRIGGER (Transact-SQL) - Books Online
DROP TRIGGER (Transact-SQL) - Books Online


Alternatives to SQL Server TDE Due to the high cost of SQL Server Enterprise Edition which includes the Transparent Data Encryption feature I am looking for an alternative product and have only found a couple of options:

DbDefence
NetLib Encryptionizer

Could anyone provide details of their experience with either of the above products (performance impact, ease of use, etc)?

Any other alternatives to SQL Server TDE?

Note:  We are currently using SQL Server 2008 R2 Standard Edition.

We have quite some mixed servers, using encryption, depending upon the need of business. For very critical servers, we decided to upgrade to Enterprise edition as it not only provides TDE but other benefits as well when it comes to performance or troubleshooting.

Yes, TDE is quite effective and very good, but since it comes with a cost we decided for medium and low priority businesses to use a third party tool like NetLib.

I would like to highlight a few of its benefits, per our usage:

Provides Transparent Data Encryption and Column Encryption for all
versions of SQL Server from 2000 through 2014, and for all editions of
SQL Server from Express through Enterprise. SQL Server's TDE is
available only in the Enterprise edition of SQL Server 2008 and
later.
Easy to set up and maintain. Took us hardly 5-6 minutes to do so,
once we were aware of what needs to be done.
Database keys are stored outside of SQL Server, including alternate
locations such as network, removable media, etc.
Encryptionizer's Transparent Data Encryption has virtually no impact
on database performance (<1%) on a properly sized server. Some
benchmark reports show SQL Server TDE to have greater impact on
performance between 5-10%.
Support for FILESTREAMS (SQL Server 2008 and SQL Server 2014).
Supports SQL Server Compressed Backups (WITH COMPRESSION).
Possible to encrypt system databases including the master database and the tempdb database.

I have not heard much about DbDefence, but yes I believe it supports Replication, Log Shipping and Mirroring. Read here for more info.

I believe you can have a trial of the above products and decide as per the business needs which suits your environment the best.


Is the default clustered index file physically the same file as the file containing the data records? I have read that the records in a sorted data file (for a DB table) are sorted in the same order as the keys in its corresponding clustered index -- and a leaf node of clustered index contains the actual data record corresponding to the key in the leaf node.

Does that mean that they are physically the same on-disk file ?

If yes , then is the clustered index's tree data structure actually physically part of the data file ?

If no, and they are physically separate on-disk files, then does the leaf node contain a pointer to the data record ? -- but that's what happens in a non-clustered index.
I can't find an explicit answer to this query. Kindly help.

You refer to "data file" as like the data (actual rows) for a table is in a file separate from other tables and indexes. That is not the case. I just like to point that out, since that might be a contributing reason for the confusion.

There is so much info available out there on physical structures for SQL server, so this will be super-brief:

Data (loosely speaking) is stored in pages (8KB) blocks, in a database data file. There are different type of pages.

The clustered index is the data. I.e., the leaf level of that index are the actual rows. It contains all columns, and the sort for that index is of course based on the index key for that clustered index. There's nowhere else/further to "point". When you navigated the tree, and are now in the leaf for the cl ix, you are indeed at the data row.

Also note that a data file (.mdf, .ndf) for a database (in contrast to a transaction log file, .ldf) can contain all types of pages. That include pages for non-clustered index, pages for clustered indexes, pages for heaps (data pages for tables that don't have a clustered index), etc.

So, at the file level, there is no separation between "data" and "indexes". You can achieve that, to some degree, using filegroups, but that is a separate topic.

Also note that I carefully didn't confuse the clustered index with the primary key. It just happens that the default index type for the PK is a clustered index. But that is just a default.


Will more indexes on a table affect performance? Possible Duplicate:
  How do indices impact query performance?
  How to know when/if I have too many indexes?

Say ‘X’ report using some "where" condition in the statement so we are creating an Index ‘A’ on table ‘T’.
Say ‘Y’ report using some "where" condition in the statement so we are creating an Index ‘B’ on table ‘T’..
Say ‘Z’ report using some "where" condition in the statement so we are creating an Index ‘C’ on table ‘T’.
Say ‘M’ report using some "where" condition in the statement so we are creating an Index ‘D’ on table ‘T’.

Now there are 4 indexes and this table has a huge volume of records.

When I insert or update rows in this table, do the indexes affect the performance of those operations?
If so, then how do I tune those queries to perform well?

An INSERT will touch the clustered index, or heap, and every nonclustered index.  An UPDATE will touch the clustered index, or heap, and every nonclustered index that contains, at either the key or the leaf level, the column being updated.  A DELETE will touch the clustered index, or heap, and every nonclustered index.  An exception to these statements is when a record is filtered out of the index.

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be.  This is why adding indexes for performance is a trade off, and must be balanced properly.

Hope this helps,

Matt


How do I determine the correct index on MS-SQL-Server based on a select? I have two questions about the index management of MS-SQL-Server 2019

If the term "table scan" appears in the execution plan, is that an indication of a "full table scan", and that means that no index has been used here?

I'm never sure if MS-SQL will use my indexes. But I also don't know how to find out which index it actually uses.

For this join, I create an index on the F_Skonto table with the three columns gueltig_ab, gueltig_bis and CD_Kunde. Is that correct?
LEFT OUTER JOIN dbo.F_Skonto
    ON S631_BSA.SPTAG >= dbo.F_Skonto.gueltig_ab
    AND S631_BSA.SPTAG <= dbo.F_Skonto.gueltig_bis
    AND S631_BSA.PKUNRG = dbo.F_Skonto.CD_Kunde

And for this join I create an index with the two columns Monat and CD_Kunde on the table F_Umstellkosten. Is that correct?
LEFT OUTER JOIN F_Umstellkosten AS u1
    ON CONVERT(varchar(6), v1.Tag, 112) = u1.Monat
    AND v1.CD_Kunde = u1.CD_Kunde

You are unlikely to ever see index use because of this in the query:
CONVERT(VARCHAR(6), v1.Tag, 112)

Because it has to calculate the values from v1.Tag, it can't simply use the histogram in the statistics to determine row counts. It must scan the entire table to find all the values that match, and it has to do the conversion each time.
If u1.Monat and v1.Tag are not the same data type, adding a column that is the same data type might be necessary (on either u1 or v1, whichever).
However, the bigger issue is, you're moving all the data. The query has no WHERE clause to filter anything. Without a filter, the most efficient way to move everything is through scans. Index seeks wouldn't make sense. You might see some index usage on the JOINs between tables (once you fix the CONVERT problem), but possibly not since all the rows are being moved.


How to monitor flat file for changes then run a SQL import job I have a file that is a flat text file that needs to be imported into my SQL Server every day.  The problem is that I have to wait for someone else to download and place the file before I can import it.  The download process is from within a proprietary application and not from a website.  How can I automate the process by monitoring for the file change and then starting import job?

Below is a SQL Table-Valued Function that I got from Jeff Moden, that will return the File Attributes of a single file:

/*
    Return File Attributes about a single file.

WARNING: Uses the sp_OACreate method, which should be SA use only.

(Note: much of this is copied from Jeff Moden's routines)

TEST:
  SELECT * FROM pps.fnFileInfo('C:\install.exe')

*/
ALTER FUNCTION [pps].[fnFileInfo]( @FileName As VARCHAR(255) )
RETURNS @FileInfo TABLE
(
    RowNum           INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Name             VARCHAR(128), --File Name and Extension
    Path             VARCHAR(128), --Full path including file name
    ShortName        VARCHAR(12),  --8.3 file name
    ShortPath        VARCHAR(100), --8.3 full path including file name
    DateCreated      DATETIME,     --Date file was created
    DateLastAccessed DATETIME,     --Date file was last read
    DateLastModified DATETIME,     --Date file was last written to
    Attributes       INT,          --Read only, Compressed, Archived
    ArchiveBit       AS CASE WHEN Attributes&  32=32   THEN 1 ELSE 0 END,
    CompressedBit    AS CASE WHEN Attributes&2048=2048 THEN 1 ELSE 0 END,
    ReadOnlyBit      AS CASE WHEN Attributes&   1=1    THEN 1 ELSE 0 END,
    Size             INT,          --File size in bytes
    Type             VARCHAR(100)  --Long Windows file type (eg.'Text Document',etc)
) As
BEGIN
    Declare @CurrentName As Varchar(255); SET @CurrentName = @FileName;
    DECLARE @ObjFile          INT          --File object
    DECLARE @ObjFileSystem    INT          --File System Object

    --===== Create a file system object and remember the "handle"
    EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT

    --===== These variable names match the sp_OAGetProperty options
    DECLARE @Attributes       INT          --Read only, Hidden, Archived, etc, as a bit map
    DECLARE @DateCreated      DATETIME     --Date file was created
    DECLARE @DateLastAccessed DATETIME     --Date file was last read (accessed)
    DECLARE @DateLastModified DATETIME     --Date file was last written to
    DECLARE @Name             VARCHAR(128) --File Name and Extension
    DECLARE @Path             VARCHAR(128) --Full path including file name
    DECLARE @ShortName        VARCHAR(12)  --8.3 file name
    DECLARE @ShortPath        VARCHAR(100) --8.3 full path including file name
    DECLARE @Size             INT          --File size in bytes
    DECLARE @Type             VARCHAR(100) --Long Windows file type (eg.'Text Document',etc)

    --===== Create an object for the path/file and remember the "handle"
    EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT, @CurrentName

    --===== Get the all the required attributes for the file itself
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Path',             @Path             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath',        @ShortPath        OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Name',             @Name             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName',        @ShortName        OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated',      @DateCreated      OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Attributes',       @Attributes       OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Size',             @Size             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Type',             @Type             OUT

    --===== Insert the file details into the return table
    INSERT INTO @FileInfo
           (Path, ShortPath, Name, ShortName, DateCreated,
            DateLastAccessed, DateLastModified, Attributes, Size, Type)
    SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated,
        @DateLastAccessed,@DateLastModified,@Attributes,@Size,@Type

    --===== House keeping, destroy and drop the file objects to keep memory leaks from happening
    EXEC sp_OADestroy @ObjFileSystem
    EXEC sp_OADestroy @ObjFile

    RETURN
END

The way I would use this is to make a table with a single column and record to keep track of the DateLastModified of your import file.  When that changes, run your import then, if successful, update the time value in the table.

Starting an Agent Job from SQL and monitoring its progress and result is actually something of a headache.  It would probably be easier to modify your job as follows:

Add a new step before the existing import step[s] that retrieves the current DateLastModified value from the file, compares it to the saved value in the table, and if the same (or earlier) exits with an error.  Then set the Job Step to "Exit With Success" in the event of an error.  If the date is greater (i.e., an updated import file) then the proc should exit normally and the Job should proceed to the next (import) step(s).
Add another step at the end of the job that reads the DateLastModified value again and updates it to the table.
Finally set the whole Job to run on some reasonable interval, such as every 5 minutes (or alternatively, define the first step to retry every 5 minutes).


Why isn't it possible to cast to a timestamp in an index in PostgreSQL? I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. So in other words, it can be casted to a timestamp type: (data->>'timestamp')::timestamp.

I want to use that timestamp property in an index. I tried the following:

create table t
(
    data    jsonb
);

create index on t(((data->>'timestamp')::timestamp));

I am getting:

ERROR:  functions in index expression must be marked IMMUTABLE

Why is this? I understood that casting to a timestamptz is not immutable, because of the localization configuration settings which are not static, but I can't apply that logic to a cast to timestamp.

Is there still a way to cast my timestamp property in the index here?

I am working with PostgreSQL 12, as released a few days ago.

Casts from text to timestamp without time zone are handled by calling the type input function timestamp_in, which is STABLE.

The reason is that timestamp_in supports some other formats too:

SELECT 'now'::timestamp;

         timestamp
----------------------------
 2019-10-09 09:53:32.026673
(1 row)

If you know that your input is always an ISO timestamp and this cannot happen, define your own conversion function:

CREATE FUNCTION text2ts(text) RETURNS timestamp without time zone
   LANGUAGE sql IMMUTABLE AS
$$SELECT CASE WHEN $1 ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d+)?$'
            THEN CAST($1 AS timestamp without time zone)
       END$$;

You can use that in your queries and indexes.

If you lack paranoia and are sure of your data quality, you can omit the test. That will improve performance, but you risk index corruption if somebody sneaks something like now into the data.


Function Variable Number of Arguments plsql I am looking to do a function with non fixed number of arguments like decode function.
The syntax for the DECODE function in Oracle/PLSQL is:

DECODE( expression , search , result [, search , result]... [, default] )

I want to do the same [] for example:

SUM(expresion1, expresion2, [expresion]...)

A practical result could be:

SELECT SUM(1,1) FROM dual;
SELECT SUM(1,1,1,1,1,1,1,1,1) FROM dual;
etc

Is that posible in PL/SQL or is this limited to Oracle built-in functions?

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements.

There is no nice and easy way for this. There is the method with collections, for example:

create or replace type t_numbers as table of number;
/

create or replace function sum_plsql (p_numbers t_numbers) return number
as
  rv number := 0;
begin
  for n in 1..p_numbers.count
  loop
    rv := rv + p_numbers(n);
  end loop;
  return rv;
end;
/

Then:

select sum_plsql(t_numbers(1, 1, 1, 1, 1, 1, 1, 1, 1)) from dual;

9

There are built-in, non-documented simple list types, so you do not need to define your own types at all circumstances, but relying on non-documented features is generally bad practice, as they may change in newer versions without warning.

If there is a reasonable limit for the number of arguments:

create or replace function sum_plsql2 (p1 number default 0, p2 number default 0, p3 number default 0, p4 number default 0) return number
as
begin
  return p1 + p2 + p3 + p4;
end;
/

select sum_plsql2(1, 1, 1) from dual;

3

Obviously, these are neither tidy nor effective.


MySQL: Which join is better between left outer join and inner join Which join is better peforming if all of them provides the same result?
For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). To get some specific results both inner join and left join gives the same result. But, I have still some doubts which is not limited to this question only.

which join is more efficient which should prefer in case of same result values ?
What are other factors which must be considered at the time of applying join?
Is there any relationship between inner join and cross join?

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it.
In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that employee, while a left join (whose first table is employees) would show all of them, and nulls on the fields from work_log if there are not matches.

Image by C.L. Moffatt on Code Project
Again, performance is a secondary thing to query correctness. Some people say that you shouldn't use LEFT JOINs. It is true that a LEFT JOIN forces the optimizer to execute the query in one particular order, preventing some optimizations (table reordering) in some cases. Here is one example. But you should not choose one over the other if correctness/meaning is sacrificed, as an INNER JOIN is not inherently worse. The rest of the usual optimizations apply as usual.
In summary, do not use LEFT JOIN if you really mean INNER JOIN.
In MySQL CROSS JOIN, INNER JOIN and JOIN are the same. In the standard, and semantically, a CROSS JOIN is an INNER JOIN without an ON clause, so you get every combination of rows between tables.
You have examples of all semantic types of join on Wikipedia. In practice, in MySQL, we tend to only write JOIN and LEFT JOIN.


Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE? I have a function which returns:

TABLE (id TEXT, collected TIMESTAMPTZ)

In another function, I would like to be able to pass the result of that first function as a parameter. For example:

CREATE FUNCTION my_func(devices TABLE(id TEXT, collected TIMESTAMPTZ)) ....

That syntax gives me an error:

ERROR: syntax error at or near "TABLE"

Is there some way to accomplish this in PostgreSQL?

OK, based on feedback from Erwin Brandstetter above, I came up with a VERY nice solution detailed below:

CREATE TABLE device_data (
  collected TIMESTAMPTZ,
  id VARCHAR(100),
  data JSON,
  PRIMARY KEY(id,collected)
);

COPY device_data (collected, id, data) FROM stdin;
2016-01-02 00:02:12+00  switch1.mycompany.com   {}
2016-01-02 00:02:12+00  switch2.mycompany.com   {}
2016-01-02 00:02:12+00  switch3.mycompany.com   {}
2016-01-02 00:02:12+00  switch4.mycompany.com   {}
2016-01-02 00:02:12+00  switch5.mycompany.com   {}
2016-01-02 00:02:12+00  switch6.mycompany.com   {}
2016-01-03 00:02:12+00  switch1.mycompany.com   {}
2016-01-03 00:02:12+00  switch3.mycompany.com   {}
2016-01-03 00:02:12+00  switch4.mycompany.com   {}
2016-01-03 00:02:12+00  switch5.mycompany.com   {}
2016-01-03 00:02:12+00  switch6.mycompany.com   {}
\.

CREATE OR REPLACE FUNCTION get_most_recent()
RETURNS TABLE(id TEXT, collected TIMESTAMPTZ) AS $$
SELECT
    id,
    MAX(collected) AS collected
FROM device_data GROUP BY id
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION get_data()
RETURNS TABLE(id TEXT, data JSON) AS $$
SELECT
  d.id,
  d.data
FROM device_data d
INNER JOIN get_most_recent() r ON r.id=d.id AND r.collected=d.collected
$$ LANGUAGE SQL;

SELECT * FROM get_data();


Must Declare Scalar Variable Error My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above.

  Msg 137, Level 15, State 2, Line 1
  Must declare the scalar variable "@id".

Here is my syntax, and if I include a print @id statement the proper value will be output, however I still get the above error?!

Create Table #temphold
(
  dateadded datetime
  ,dateupdated datetime
  ,id varchar(100)
)

Declare @id varchar(100), @sql varchar(max)
Set @id = '12345'

set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
PRINT @SQL
EXEC(@SQL)

Drop Table #temphold

@id as part of the execution variable @sql is doing nothing. It is not tied to the declared and set variable unless you build the string around it, i.e. concatenate it to the string like this:

set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(' + @id + ','''') '

Notice the + either side of the @id variable.

At the end of the day, @sql is just a string until it's executed using the EXEC() command. Simply treat it as such until it compiles as fully qualified T-SQL.


Alternatives to AdeptSQL Diff (for MS SQL Server 2012) It seems AdeptSQL Diff is not compatible with SQL Server 2012 (at this time). And it is my preferred tool for deploying changes. I realize this is a shopping list but I hope SE won't mind.

I am wondering what are the best alternatives out there and wanted to query the DBAs.

Currently I am evaluating RedGate SQL Compare. It doesn't include data comparison (separate product) though it does generate deployment scripts which is nice. Just want something quick and painless for launching schema changes to production servers. What do you recommend. Thanks!

ApexSQL Diff supports SQL Server 2012.
Haven't tested it on SQL Server 2012, but it works fine on 2008


The OLE DB provider "MSDASQL" for linked server "(null)" reported an error I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance via my linked server but when i try to run a query with OPENROWSET, it gives me an error message like below. I have searched it on google many times but i couldn't solve my issue. What is the problem? Why do i get this message and how to solve it?

  The OLE DB provider "MSDASQL" for linked server "(null)" reported an
  error. The provider reported an unexpected catastrophic failure. Msg
  7330, Level 16, State 2, Line 25 Cannot fetch a row from OLE DB
  provider "MSDASQL" for linked server "(null)".

I had same issue once, You may need to recreate the linked server with this set to false (or just change it in the linked server properties->server options):

EXEC master.dbo.sp_serveroption @server=N'servername',
    @optname=N'remote proc transaction promotion', @optvalue=N'false'

Additionally, try using OPENQUERY to run this against the link

syntax:

OPENQUERY ( linked_server ,'query' )

and example SQL:

SELECT * FROM OPENQUERY(server_name,'SELECT * FROM table_name');


How to represent bike components and their relationship to each other? So I hope my question here isn't too vague.

I'm currently thinking about how to model a bike with all its parts and their relationships to each other within a database.

I haven't chosen a specific database as of yet. So it could SQL, document or graph database, or whatever else I'm not aware of.

So let's say I want to represent a bicycle. A bike is composed of various parts (or components) which all relate to each other and all have various properties.

For instance a frame can have a 135mm rear dropout, which means it will relate to a rear hub that is 135mm wide. The hub itself has 36 holes which means it can be used with a 36 holes rim, and so on and so forth. However a frame could be disc specific (it has disc mount but no rim brake mount) which means it can only take a disc hub. However a disc hub can be mounted on a frame that does not have disc mount but uses rim brakes.

I have already explore the idea of using a realtional database (postgres) with a json type and single table inheritence. As in a frame and a hub are both component sharing common properties (model, make, material, year...) stored in a component table as well as specific value (size, number of spoke holes...) store in a json field within the component table. But the problem is to represent the relationship, I could either match unique properties or use foreign keys for each rows.

So my question here is, is there a better way to do that?

Are you documenting a tear-down of an specific existing bike or presenting options for the build of a new bike from available components?

For the former, a tree representing the bill of materials is the solution. There are several ways to represent a tree in a relational database. And, being a tree, a graph DBMS is a natural fit.

To build a bike from options, as in a shopping site for customizable products, a different approach is required. Here the basic units of design are the "precludes" and "requires" relationships.  For example, choosing disk brake hubs "requires" a disk-compatible frame. Choosing a 36 hole hub "precludes" a 20 hole rim. Often it is an attribute of a part that determines applicability rather than the full part number. So we don't say "part XY123 precludes ZQ987" rather we say "frames that are RED cannot have saddles that are leather."

You must also list the categories of items that must all be chosen to form a complete product and those that are optional e.g.

bike = frame + wheel (x2) + seat + chainset + handle bar (all "must")
       + bell + carry rack (both optional).

Given the likely combinatorial explosion it is best to start from a root category (the frame?) and define the choices from there, rather like those choose-your-own-adventure books of yore.

In the abstract this can be modeled as node-types (frame, hub, chainset) with specific instances of each type (Trek steel frame, Norco carbon frame). The connections between the nodes form as denser, more linked graph than in a tear-down.


Suddenly a massive amount of binlog files I've seen a sudden increase of binlog files, very large in number and size, in all three nodes of a Percona XtraDB cluster:

-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:11 binlog-32.000001
-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:36 binlog-32.000002
-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:48 binlog-32.000003
-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:59 binlog-32.000004
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:08 binlog-32.000005
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:26 binlog-32.000006
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:38 binlog-32.000007
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:47 binlog-32.000008
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:59 binlog-32.000009
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:10 binlog-32.000010
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:20 binlog-32.000011
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:29 binlog-32.000012
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:37 binlog-32.000013
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:45 binlog-32.000014
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:53 binlog-32.000015
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:01 binlog-32.000016
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:11 binlog-32.000017
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:19 binlog-32.000018
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:28 binlog-32.000019
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:38 binlog-32.000020
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:44 binlog-32.000021
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:52 binlog-32.000022
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:59 binlog-32.000023
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:08 binlog-32.000024
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:17 binlog-32.000025
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:24 binlog-32.000026
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:32 binlog-32.000027
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:40 binlog-32.000028
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:47 binlog-32.000029
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:56 binlog-32.000030
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:05 binlog-32.000031
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:15 binlog-32.000032
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:25 binlog-32.000033
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:36 binlog-32.000034
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:46 binlog-32.000035
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:48 binlog-32.000036
-rw-rw----  1 mysql mysql 254M 20 oct.  09:34 binlog-32.000037
-rw-rw----  1 mysql mysql  143 20 oct.  09:34 binlog-32.000038
-rw-rw----  1 mysql mysql  143 20 oct.  09:34 binlog-32.000039
-rw-rw----  1 mysql mysql  143 20 oct.  09:34 binlog-32.000040
-rw-rw----  1 mysql mysql  120 20 oct.  09:34 binlog-32.000041
-rw-rw----  1 mysql mysql 1,2K 20 oct.  09:34 binlog-32.index

It appears that an user lanced a script that does quite a lot of SELECTs and UPDATEs over many connections. However, it surprises me how he managed to DoS the server. Is this a reasonable cause, or might be something else, perhaps some misconfiguration?

Check server-id on in my.cnf on each server in your replication topology.  Probably there is a duplicate.  Server ids must be different.


Get value from jsonb_array How can I get values of array in my jsonb type column named 'body' in order to use the value to join another table.
example:-

SELECT U.*
from masters."USERS" as U
    left join masters."ROLE" R on U.body->'role' = R.id

Below is my column data.

{
    "live": "0",
    "role": [
        "1"
    ],
    "desig": "",
    "email": "chandan@logibricks.in",
    "empCode": "",
    "mobileNo": "",
    "password": "$2a$10$qDENcVR/ZDFIsp/blXK7h.h4xsmI18/LArvbqmtNfXxZ6jYO/s5YG",
    "userName": "chandan",
    "full_name": "Chandan ",
    "department": "",
    "updated_by": "rhlsoni",
    "is_access_results": "1",
    "updated_timestamp": 1550556834,
    "reporting_manager_id": "",
    "is_access_testscripts": "1",
    "is_access_workingpapers": "1"
}

You can expand the array using jsonb_array_elements_text() with a lateral join in this way:

select
    users.id,
    role.name
from
    users
join lateral
    jsonb_array_elements_text(data->'role') r on true
left join
    role
    on role.id = r.value::int

db<>fiddle here


20-40 ms performance difference during peak traffic on amazon RDS I am noticing during peak traffic the average mysql response time is 20-40 ms longer consistently as reported by new relic. I have tried turning on query cache and making the innodb buffer pool larger with no luck. The total response time is still around 125ms; but am concerned that I am missing a setting that could help.

I don't recall having this issue with Rackspace with the same set of data and traffic. Could it just come down to the fact there is just a bit more load on the database and is acceptable?

I have an 8GB Amazon RDS instance with a 4gb buffer pool and I turned on the query cache with a 75% hit rate which didn't move the needle.

Could the issue be related to being in a shared environment at all? My CPU usage is under 20% (usually a lot less).

here are my settings:
https://gist.github.com/blasto333/9e82b6261681303f7369c3a2652e03f2

I am just looking for ideas to try. My two ideas were making the buffer pool larger was 3gb now 4gb; and turning the query cache on (As I had with rackspace). So far those didn't move the needle.

20-40ms is about a thousand miles of latency.  Where are the client and server, relative to each other?  Is Amazon moving the Server around?  Or the Client?

innodb_buffer_pool_size is set reasonably by Amazon (I think).  Do not raise it without raising the amount of RAM?  There is no benefit in making the buffer_pool much bigger than the entire dataset (data & indexes).

For an 8GB VM (which is probably only 7.5GB), 6G is about the max that is safe -- assuming that only MySQL is running in that VM.

Turning on the QC is usually a waste on production systems (except on Aurora).  Also, do not set query_cache_size bigger than about 50M -- else it could slow things down.

20% CPU -- percent of one core?  Of all cores?  20% of all cores seems high.  What is your slowest query?  Also SHOW CREATE TABLE.


How to interpret this XML plan fragment? I am looking at the query plan produced by SQL Server 2008 R2 and I'm not sure I'm interpreting it properly. Examples:

<RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613"
 EstimateCPU="0.000100822" AvgRowSize="822" EstimatedTotalSubtreeCost="1.06489" Parallel="0"
 EstimateRebinds="0" EstimateRewinds="0">
 ...
  <MemoryFractions Input="0.5" Output="1" />
  <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="10190123" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>

and

<RelOp NodeId="6" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="1"
 EstimateCPU="0.0502411" AvgRowSize="24" EstimatedTotalSubtreeCost="1.05024" TableCardinality="4.93715e+009"
 Parallel="0" Partitioned="1" EstimateRebinds="0" EstimateRewinds="0">
 ...
  <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="10190123" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>

Specifically, seeing EstimateRows="1" and ActualRows="10190123" I suspect that the optimizer incorrectly estimates the number of rows that will be processed. Subsequently, underestimated row set for the sort might lead to a major sort spill.

Edit:

The query in question looks similar to

select whatever from mytable
where integer_column1 > 848484884 and
      integer_column1 < 949494949 and
      another_int_col in (5, 8, 15, 20)

where the cardinality of integer_column1 is in millions and that of another_int_col is a few dozen (a flag of sorts). There is a non-unique, non-clustered index on (integer_column1, another_int_col). I wonder if even in the presence of updated statistics the optimizer can use the histogram/density vector of that index though.

Does my reasoning make sense?

(I don't have access to the actual system or the Management Console for that matter -- only the XML plan)

To answer my own question, my original understanding was correct: the discrepancy between EstimateRows="1" and ActualRows="10190123" was an indication of incorrect optimizer estimates, caused by stale statistics (as suggested by commenters). With updated statistics the node in the plan corresponding to node 6 in the question looks like so:

<RelOp NodeId="25" PhysicalOp="Index Seek" LogicalOp="Index Seek"
 EstimateRows="1.25676e+007" EstimateIO="23.8363" EstimateCPU="1.78132" AvgRowSize="24"
 EstimatedTotalSubtreeCost="25.6176" TableCardinality="4.94087e+009" Parallel="1"
 Partitioned="1" EstimateRebinds="0" EstimateRewinds="0">
...
<RunTimeInformation>
  <RunTimeCountersPerThread Thread="2" ActualRows="3457399" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="8" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="7" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="5" ActualRows="8053198" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="6" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
  <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>

Although the problem could be seen in the XML plan representation directly, for those who want a graphical representation there exist a free tool that does not require the SQL Server Management Studio, SQL Sentry Plan Explorer, or even Windows for that matter -- it is an XSL stylesheet created by the StackOverflow user Justin.


Truncated Incorrect date value string with comma I have problem in date columns to insert from varchar column to date column.
I used str_to_date and Date_Format functions but it doesn't solved. How Can I

Simple Example:

update Uye_Uye_Liste
set    DogumTarihi=str_to_date(DogumTarihi,'%d.%m.%Y')
where  DogumTarihi is not null;

  ERROR 1292 (22007): Truncated incorrect date value: '23.07.2002 00:00:00'

alter table Uye_Uye_Liste modify DogumTarihi datetime;

  ERROR 1292 (22007): Incorrect datetime value: '13-04-1996' for column 'DogumTarihi' at row 2

select DogumTarihi from Uye_Uye_Liste where DogumTarihi = '13.04.1996';

+-------------+
| DogumTarihi |
+-------------+
| 13-04-1996  |
+-------------+

My guess is that you have one or more malformed values. Given the information you provided:

create table Uye_Uye_Liste ( DogumTarihi varchar(20) ) engine = innodb;
insert into Uye_Uye_Liste (DogumTarihi) values ('13.04.1996');

update Uye_Uye_Liste set DogumTarihi =  str_to_date(DogumTarihi,'%d.%m.%Y');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

alter table Uye_Uye_Liste modify DogumTarihi datetime;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

So if every row corresponds to this pattern you should be alright. I suspect that some rows does not, focus on identifying and transforming those rows.

For the row given in the original query it is sufficient to to manually truncate the row using substr:

insert into Uye_Uye_Liste (DogumTarihi) values ('23.07.2002 00:00:00');
update Uye_Uye_Liste set DogumTarihi = str_to_date(substr(DogumTarihi,1,10),'%d.%m.%Y');
select * from Uye_Uye_Liste;
+-------------+
| DogumTarihi |
+-------------+
| 1996-04-13  |
| 2002-07-23  |
+-------------+
2 rows in set (0.00 sec)

alter table Uye_Uye_Liste modify DogumTarihi datetime;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0


Change propagation among connections If I update a table using one connection and issue a query in another connection immediately after that, is there a chance that I can't see the changes even if it's extremely unlikely?

If the first transaction has committed and the second transaction started after the first one committed (more accurately, it obtained its snapshot after the first one committed), then everything that the first transaction wrote will be visible to the second one.


Find time between status changes for each unit on each incident I have a table called History that holds unit status changes when they are assigned to incidents. Each unit can change status from DP to AK to ER to AR. Statuses can be skipped.

The table is called history. The fields are Unit, Datetimestamp, Status, Incident.

I want to find the average time between each of the statuses for every unit for an incident. e.g. for the UNIT E04 on INCIDENT F141000001 I want to know the time between DP to AK, AK to ER, ER to AR. Then use this to derive the average time between each of these status changes for all the units in the table.

OK, try this.  I made some assumptions.

All statuses start as DP
Status changes are only one way as specified above and don't revert the other direction.
If a status change is listed twice, use the first earliest instance
If a status change is skipped, use the previous status time stamp to calculate the change
INCIDENT is not desired as part of the AVG status change report for UNIT
Averages in the final query are based off the actual number of status changes that occurred and is variable by UNIT/INCIDENT

With those assumptions in mind I present the following solution.  LAG() isn't an option in 2008, so you are left with making a bunch of datasets to realize the output you want.  I'm using CTEs to generate the lists of UNITS by INCIDENT and then creating temp tables to work with the timestamps.

I could have done a bunch of nested CTEs but then they'd be executed multiple times; same with doing this with sub queries. Finally, when reading the output for the status changes, if a change is preceeded by a NULL value, it is because the status was skipped.  In the case of E14, based off available sample data, there were no status changes from DP.

Hopefully this is what you need or at least enough to get you going.

GENERATE SAMPLE DATA

create table history
(
      UNIT CHAR(3)
    , DATETIMESTAMP CHAR(16)
    , STATUS CHAR(2)
    , INCIDENT CHAR(10)
)

INSERT history
values ('E04','20140617101703ED','DP','F141000001')
     , ('L24','20140617101703ED','DP','F141000001')
     , ('E04','20140617101845ED','ER','F141000001')
     , ('L24','20140617101848ED','ER','F141000001')
     , ('E07','20140617101955ED','DP','F141000002')
     , ('L17','20140617101955ED','DP','F141000002')
     , ('E04','20140617102029ED','AR','F141000001')
     , ('L24','20140617102038ED','AR','F141000001')
     , ('E07','20140617102235ED','ER','F141000002')
     , ('L17','20140617102238ED','ER','F141000002')
     , ('E14','20140617102501ED','DP','F141000003')
     , ('D03','20140617102626ED','DP','F141000002')
     , ('E07','20140617102712ED','ER','F141000002')
     , ('L17','20140617102717ED','ER','F141000002')
     , ('D03','20140617102740ED','ER','F141000002')
     , ('D03','20140617102744ED','aR','F141000002')

--DROP TABLE history

CODE

--GATHER DP AND AK STATUSES AND COMPARE
with
cte_dp as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'DP'
    group by UNIT, STATUS, INCIDENT
),
cte_ak as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'AK'
    group by UNIT, STATUS, INCIDENT
)
select dp.UNIT, coalesce(ak.DATETIMESTAMP, dp.DATETIMESTAMP) PreviousTimeStamp, DATEDIFF(second,dp.DATETIMESTAMP,ak.DATETIMESTAMP) DiffInSecs, dp.INCIDENT, case when ak.UNIT is null then 0 else 1 end as StatusCount
into #dk
from cte_dp dp
    left join cte_ak ak on ak.UNIT = dp.UNIT
        and ak.INCIDENT = dp.INCIDENT
        and ak.DATETIMESTAMP > dp.DATETIMESTAMP;

--GATHER ER STATUS AND COMPARE TO PREVIOUS STATUS CHANGE
with
cte_er as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'ER'
    group by UNIT, STATUS, INCIDENT
)
select dk.UNIT, coalesce(er.DATETIMESTAMP, dk.PreviousTimeStamp) PreviousTimeStamp, DATEDIFF(second,dk.PreviousTimeStamp,er.DATETIMESTAMP) DiffInSecs, dk.INCIDENT, case when er.UNIT is null then 0 else 1 end as StatusCount
into #kr
from #dk dk
    left join cte_er er on er.UNIT = dk.UNIT
            and er.INCIDENT = dk.INCIDENT
            and er.DATETIMESTAMP > dk.PreviousTimeStamp;

--GATHER AR STATUS AND COMPARE WITH PREVIOUS STATUS CHANGE
with
cte_ar as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'AR'
    group by UNIT, STATUS, INCIDENT
)
select kr.UNIT, DATEDIFF(second,kr.PreviousTimeStamp,ar.DATETIMESTAMP) DiffInSecs, kr.INCIDENT, case when ar.UNIT is null then 0 else 1 end as StatusCount
into #rr
from #kr kr
    left join cte_ar ar on ar.UNIT = kr.UNIT
            and ar.INCIDENT = kr.INCIDENT
            and ar.DATETIMESTAMP > kr.PreviousTimeStamp;

--REPORT THE STATUS CHANGE TIMES IN SECONDS FOR EACH STATUS CHANGE, PER UNIT, PER INCIDENT
select dk.UNIT, dk.DiffInSecs 'DP-AK', kr.DiffInSecs 'AK-ER', rr.DiffInSecs 'ER-AR', dk.INCIDENT
from #dk dk
    left join #kr kr on kr.UNIT = dk.UNIT and kr.INCIDENT = dk.INCIDENT
    left join #rr rr on rr.UNIT = dk.UNIT and rr.INCIDENT = dk.INCIDENT

--REPORT THE AVG TIME THE STATUS CHANGED FOR EACH UNIT
select dk.UNIT, (isnull(dk.DiffInSecs,0)+isnull(kr.DiffInSecs,0)+isnull(rr.DiffInSecs,0))/(case when (dk.StatusCount+kr.StatusCount+rr.StatusCount) = 0 then 1 else (dk.StatusCount+kr.StatusCount+rr.StatusCount) end) AverageSeconds_Between_StatusChange
from #dk dk
    left join #kr kr on kr.UNIT = dk.UNIT
    left join #rr rr on rr.UNIT = dk.UNIT

drop table #dk
drop table #kr
drop table #rr


Is there any reason to use index for very small tables (up to 1000 rows)? During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, red/green/blue for products.

I don't put this attributes to product table because electronic components can't be fresh, and oxygen gas can't be red and tables can't have unlimited rows count...

For storing attributes I use custom small tables where 2-3 field: id for linking, name for showing in application and sometime type if attributes groups in same category.

Primary "objects" is linked to attributes through intermediate many-to-many tables.

Is any reason to create and maintain indexes for those "small dictionaries" with less than 1000 items (usually 10-40)?

My target database is Oracle but I hope that answer vendor independent...

I fill - no, but have no technical skills to justify my filling...

Generally yes. In lack of an index the access pattern has to inspect every row, just to see if is the one you need or not. The issue is not the table size, but concurrency. Depending on your isolation level, your scans may block behind uncommitted transaction just to wait for rows that ultimately are 'uninteresting' to be unlocked. Because your scan is guaranteed to 'look' at every row, every scan will block behind any write (insert, delete or update). Oracle defaults to snapshot isolation which is OK in this situation (no blocking) but other vendors default to something else, eg. SQL Server will default to Read committed which does block.

With an index present your access pattern will only look at the relevant rows (within the desired key range) so the pure statistical chance of hitting a lock conflict is greatly reduced.


SQL Server Buffer Pool Utilization under VMWare I recently migrated a physical SQL Server box (2012) over to ESX (5.1) simply for ease of administration.  It is and will be the only guest on the server.  As expected there was a marginal performance hit that for the most part is only noticeable on startup.  Here in lies my issue.

I have noticed that under virtualization the buffer pool takes memory much much much slower then its physical counterpart.

Under the physical install within 2 days running SQL server had allocated all 120gb of memory allocated (max server memory).  However, with practically the exact same setting running in the hypervisor (I have also given the guest full reservation of the memory) it has taken 7 weeks to reach 50gb. This was also painfully slow something like a Gb rise a day, this translated into a few days of slow queries.

So while I have seen this on multi guest boxes and attributed it to memory pressure, I am confused at why it is happening on a single guest box.  I know vmware will compress and dedupe memory, however most of this is unique data.

So my question is:

1) Why exactly is this happening ? I'm interested in the exact mechanism that is causing this.

2) Is there a method to start SQL server with is full memory allocation ?  I remember something like a traceflag for this but havent been able to find anything for 64bit.  And is this necessary ?

Thanks in advance.

Another thing I noticed in the first few days after a restart is that the PLE stays very low in the 500 - 900 range this increases as the buffer pool grows.

Trace flag 834 can allow the maximum amount of memory to be allocated at startup for x64 machines.  The following blog has more detail but here are the basics:

Enterprise Edition needs to be installed
8GB of RAM or more needs to be present
Lock Pages in Memory needs to be on.

http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx

Read the full article as there are caveats and warning spread throughout as this can lead to a much longer startup time (or failed starts).  A big one being that it needs to allocate a contiguous chunk of memory and I'm not sure how that will work on VMWare.  Also, if the physical memory in the host is 128GB I would reduce max server memory some more to make sure there's enough space left for VMWare to do it's thing.


Availability Group Listener I'm looking at AlwaysOn availability groups. The more I look at it the more it appears that the availability listener group is a single point of failure. Where exactly does the listener actually run? A separate a server, the primary SQL server, all of them?

Say I have a whole application stack at my second data center. How do I configure the listener(s) such that they will be running at both sites and that the applications will point to their own local copies?

I'm sure I'm missing something here but I don't know what.

The AG listener is a virtual network name (VNN) or virtual IP (VIP).

  An availability group listener is a virtual network name (VNN) to
  which clients can connect in order to access a database in a primary
  or secondary replica of an AlwaysOn availability group.

You point applications to the listener (which is registered with DNS) and directs traffic in the AG.  So, unless all your DCs go down at the same time, your redundancy is the number of DCs/DNS servers.

  An availability group listener consists of a Domain Name System (DNS)
  listener name, listener port designation, and one or more IP
  addresses. Only the TCP protocol is supported by availability group
  listener. The DNS name of the listener must also be unique in the
  domain and in NetBIOS. When you create a new availability group
  listener it becomes a resource in a cluster with an associated virtual
  network name (VNN), virtual IP (VIP), and availability group
  dependency. A client uses DNS to resolve the VNN into multiple IP
  addresses and then tries to connect to each address, until a
  connection request succeeds or until the connection requests time out.

This MSDN article (which
i quoted from) should be a good starting point for what, exactly, an AG Listener is.

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


Table is marked as crashed and should be repaired I have MySQL and Nagios installed Linux PC (Centos5.5)
I have wrote some script to fetch the data from Nagios to Mysql DB (Exactly fetch data in Table)
The data fetching working properly (confirmed with 'COUNT' cmd)
The problem when i run the following command its through error as below,

mysql> select * from nagios_servicechecks;
ERROR 1194 (HY000): Table 'nagios_servicechecks' is marked as crashed and should be repaired

Except this command, all other commands working fine.

Please try the below query,

REPAIR TABLE nagios_servicechecks;

Try the above query in terminal or check the below link to repair table or databases via phpmyadmin

http://php.about.com/od/mysqladministration/qt/repair_mysql.htm


SQL Server CONTAINS ignores single quote before asterix Let's say I have table NamesTable, which has text index on Name column

DECLARE @String2 NVARCHAR(20) = '"SomeName''*"'
SELECT * FROM NamesTable
WHERE CONTAINS(Name, @String2)

What does single quote in CONTAINS string mean? Why is it ignored?

CONTAINS is used for full-text searching, and can search for:

for a word or phrase
the prefix of a word or phrase
a word near another word
a word inflectionally generated from another word, such as spoon and spooning
synonyms

Perhaps you should use:

DECLARE @String2 NVARCHAR(20) = '"SomeName''%"';
SELECT *
FROM dbo.NamesTable NT
WHERE NT.Name LIKE @String2;


MySQL Job failed to start after adding "log = /var/log/mysql/mysql.log" to my.cnf I'm trying to add "log = /var/log/mysql/mysql.log" to my /etc/mysql/my.cnf file. But after sudo service mysql restart I have Job failed to start.

If I delete line "log = /var/log/mysql/mysql.log" all work fine.

Set permissions like sudo chown -R mysql:mysql /var/log/mysql

What it can be?

Just change this

log = /var/log/mysql/mysql.log

to this

general_log_file  = /var/log/mysql/mysql.log


Restoring PostgreSQL tables faster There's a PostgreSQL PITR system running on a fairly large system, with hundreds of databases with hundreds of tables in each, and we're running into time constraint-based problems when trying to restore individual tables from a single db, or a set of dbs. PITR is being managed with Barman.

With this setup, we need to restore a whole cluster (which could contain dozens of dbs), then extract the single table and then restore the data - which can take hours.

Statistically, because of the number of dbs involved and the number of people involved, this happens often enough that it's becoming a problem.

Are there faster solutions which would give us PITR-like features? I've thought of doing manual pg_dump backups for every table individually every hour, but maybe there are better solutions?

I assume you have already tried the "shame people into being more careful" approach without success.

I think I would try for an in-database solution, like adding timestamp columns to the table and adding columns for "soft deletes" rather than doing hard deletes, in such way that you can run queries to restore a table to its previous state.  If forbidding user-level hard deletes is not an option, then you can keep some log tables around that allow you to craft such restorative queries.  I use this module extensively for that purpose, although if I were starting from scratch I might consider something more modern like this.  The triggers needed to execute this logging do impose a considerable overhead on any DML operations, so you will have assess whether you can tolerate this.

A benefit of this approach is that you can often come up with queries that will fix one person's mistake without all changes after that mistake being lost.  Of course if the bad data from the oopsie leads to cascading errors because later work depended on the bad data, this may not be possible.

If that won't work, perhaps a hybrid approach would be a good idea.  You could save hourly pg_dump snapshots every hour and keep them for 12 hours (or whatever amount of storage you are willing to dedicate to this purpose).  If you do rarely need to go back more than 12 hours, then fallback to the PITR recovery method instead.  If you go this route, you might want to use the -Fc or -Fd options for pg_dump.  That way you can take just one dump of the database per hour, but choose which tables to restore at restoration time, rather than dump time.

If you want to keep with your current practice, there are many ways to make it faster, but it depends on what the current bottleneck is.  If the bottleneck is restoring the base backup from archive before you can start recovery, then you can just keep some already-restored copies around so that you can initiate recovery quickly using a pre-staged backup and just editing the pg_restore.conf file and then starting.  Once used you would have to throw it away and recreate it in the background so it is ready to go for next time.  If the bottleneck is instead the time it takes to do the PITR itself, then you could take more frequent base backups and use the most recent one which is old enough to be usable for your desired restoration time.  That way there is less work to recover.  For example, take a base backup every day, but only keep one from the beginning of time, and one from today, and one from yesterday.  Everytime you successfully complete a base backup, delete the one from two days ago.

You can also keep a lagging hot standby with "recovery_min_apply_delay" that is always around 12 hours behind, but by the time you stop that server, change the config to fast forward it to the time you need, and once done throw it away and recreate it for next time, it will probably not be much faster than just keeping a recent base backup, but will add a bit of complexity.


PostgreSQL Locking Behavior w/ Partial Index For PostgreSQL, will create index foo on bar where condition=true, i.e. without concurrently, lock an entire table for a partial index?

Or will only the affected rows, i.e. where condition=true be locked?

I did not see it spelled out in https://www.postgresql.org/docs/9.6/sql-createindex.html.

The lock taken is on the table level.  It doesn't lock a specific WHERE clause.


Storing statistic results I'm doing a small database for statistics, it's a really simple statistic like calculating the cost of an event and divide it by the amount of people that went to get the cost per attendee, but it can end up having loads of event, and it will require to poll in a lot of results at once.

I know with MySQL I can use the database to do such simple calculation, so I could run a query every time is needed, but it can end up being costly, if there's a lot of events in say a year.

My question is if I were to store the statistic results on a different table, I see it a little bit as data duplication, is that acceptable? or should I do it in a different manner?

Thanks

Data duplication as your are proposing is certainly not illegal.  You can do what you like with your data.

You need to weigh the benefits of having the statistic statically stored and using up storage space, or having to calculate it each time.


how to automate DB restore to another server? The plan is to have 1 backup set that will contain full and transactional log backup. (Server1)

There is already a scheduled job that will append any transactional log to the file after 30 minutes. (Server1)

There is a shared network drive between Server1 and Server2. But the problem is automating it via T-SQL. Here is the script:

RESTORE LOG [db_test_rep] FROM  DISK = N'Z:\db_rep.bak' WITH  FILE = 19,  NORECOVERY,  NOUNLOAD,  STATS = 5

The issue with this is that everytime the scheduled job executes, it will update the backup file numbering + 1. So in this case on the next transaction log backup, when I script into action the restoration using SSMS, it will generate FILE = 20

Can you guys give me a simple workaround for this?

The best suitable solution in your scenario is LOG SHIPPING which is available in Web, Standard and Enterprise editions. I assume that your SQL server is not express as you mentioned There is already a scheduled job

If you want work with T-SQL, i don't think appending backup files is good idea, so you may want to change schedule job to create separate file for each backup. Then you can start with following query to get dynamic restore command based on backup history:

SELECT      [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            f.physical_device_name,
            ((b.compressed_backup_size / 1024) / 1024) as CompressedSize_MB,
            CASE    WHEN Type = 'L'
                        Then 'RESTORE LOG '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
                    WHEN Type = 'D'
                        Then 'RESTORE DATABASE '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
            END as Script,
            b.is_single_user,
            b.user_name
FROM    [msdb].[dbo].[backupset] AS [b]
            LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
where   b.type in ('D', 'L')
        and b.database_name = 'TestDAG' --- add your database name here
order by b.backup_finish_date desc


Compare values in SQL having dots I'm comparing values from two different database.
In one table the name is stored with dots(i.e A.B C) and in another table the same value is stored as AB C(i.e in second table none of the names contain dots)
In SQL how can I compare the values? Dot can come anywhere in the name.


Join table into different columns I have two tables (simplified).

table1:
 - id (serial)

table2:
 - table1_id (integer)
 - value (text)
 - type (integer)

I want to join these two tables, but depending on the type, I want to join the "value" of table2 into different columns. Result should look like this:

id | table2value0 | table2value1 | table2othervalues

table2value0 is the value from table2 where type is 0.
table2value1 is the value from table2 where type is 1.
table2othervalues are the remaining values where type is something else, as an array.

If there are more than one value for table2value0 and table2value1, I only want the first. Order doesn't matter, so the order it was inserted.

If some entries in table1 do not have matches in table2, they should appear in the output as NULL.

Assuming Postgres 9.4 or later:

SELECT id, t2.table2value0, t2.table2value1, t2.table2othervalues
FROM   table1 t1
LEFT   JOIN (
   SELECT table1_id AS id
        , min(value) FILTER (WHERE type = 0) AS table2value0
        , min(value) FILTER (WHERE type = 1) AS table2value1
        , array_agg(value) FILTER (WHERE type IN (1,2) IS NOT TRUE) AS table2othervalues
   FROM   table2
   GROUP  BY table1_id
   ) t2 USING (id);

The aggregate FILTER clause was introduced with pg 9.4. There are various less elegant alternatives for older versions:

Postgres Nested WHEN Aggregate Function

This returns one row for every row in table1, missing types in table2 result in NULL values.

  If there are more than one value for table2value0 and table2value1, I only want the first.

You seem to be under the impression that there is a natural order of rows, but there is not:

Does Postgres preserve insertion order of records?

I picked the minimum value instead, since your requirement is effectively undefined.

The best query depends on your actual table definition (including all constraints).

In particular, the expression type IN (1,2) IS NOT TRUE also catches type IS NULL, which may not be necessary, depending on your missing table definition.

Related:

Select column to be inserted based on input column value
How to pass a parameter into a function

Aside:
Your desire to return a single (scalar) value for types 1 and 2, but an array for the rest makes it harder to solve with crosstab() (but still possible). Basics:

PostgreSQL Crosstab Query


How do I extract row/column elements from multiple tables with same schema in single query My question is as follows: Given that I have three tables with exactly the same column names and exactly the same keys for the "name" column e.g.

Table r
name | A | B | C
-----------------
elem1| 12| 41| 23
elem2|  5| 16| 94
elem3| 42| 93| 11

Table t
name | A | B | C
-----------------
elem1|  9|  8|  1
elem2|  3|  6|  5
elem3|  2|  1|  3

Table p
name | A | B | C
-----------------
elem1| 21| 14| 32
elem2|  5| 61| 49
elem3| 24| 36| 11

how can i merge all these tables into a set of rows as follows in a single query:

output
name | column | value
--------------------------
elem1|      A | "12,9,21"
elem1|      B | "41,8,14"
elem1|      C | "23,1,32"
elem2|      A | "5,3,5"
...  | ...    | ...
elem3|      C | "11,3,11"

or if it is simpler:

output
name |       A |       B |       C
------------------------------------
elem1|"12,9,21"|"41,8,14"|"23,1,32"
elem2|  "5,3,5"|"16,6,61"|"94,5,49"
elem3|"42,2,24"|"93,1,36"|"11,3,11"

Simplest way would be to use UNION:

SELECT * FROM A
     UNION
SELECT * FROM B
     UNION
SELECT * FROM C

or use a JOIN:

SELECT *
FROM A
FULL OUTER JOIN B
ON < A.name=B.name>;

Read more about joins

Hope this helps.


SQL Server 2016 login language display is wrong Even after changing the language for a login from Arabic to us_english it doesn't save it. It continues to show Arabic. The server default language is us_english. How do I fix this. Thanks !

If the change (Alter login LoginName  with DEFAULT_LANGUAGE  = English) applied successfully (Commands completed successfully.) the language must be changed though server default is Arabic.

However, you have to disconnect the session and connect with new session then try select @@language.

If you have already done so, and the problem still persist, check SQL Error log if there is any issue reported while changing language for particular login.