texts:
I created a scalar valued function that pauses for as many seconds as passed in. This function won't be used in production. I am trying to test how SQL server evaluates and creates plans if I add a UDF to a query. I am very puzzled by the difference in how it evaluates the last of my 3 queries. First here is the code to create the test function:

CREATE FUNCTION dbo.[sleep](@seconds int)
RETURNS datetime
as
BEGIN

DECLARE @sleepUntil datetime
DECLARE @dummy int
SET @sleepUntil = DATEADD(s, @seconds, getdate())
WHILE getdate() < @sleepUntil
    SET @dummy = 0
RETURN getdate()
END

Now to create a table to test queries

CREATE TABLE #table (num int)

and to insert values

INSERT INTO #table
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)

The first query takes 2 seconds to run - which makes sense assuming that SQL server stores the value returned from the scalar function.

   SELECT num
   FROM #table t
   WHERE dbo.sleep(2)=0

The second one still takes 2 seconds - so my assumption is that it does the same.

 SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1 )
   OR dbo.sleep(2)=0

The third one is puzzling - it takes 20 seconds to run, why would sql server not store the value here instead it runs it for each row? An interesting point I noticed was that when I clicked to see live query statistics it took 20 seconds to start showing the plan.

SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1
                WHERE t.num = t1.num) dbo.sleep(2)=0


texts: SQL Server (2016) Setup removed permission to drives

texts:
I recently installed a new Sql Server 2016 and selected the root of drive N:\ for transaction logs and drive Z:\ for tempdb.
In the end the server was succesfully installed and running. But it was not possible to access the drives N or Z anymore.

I suspected Sql Server to expect a folder instead of the whole drive and to change the security settings / permissions on this folder - which in my case was performed on the drive's root and may have caused the symptoms

On the other hand I am pretty sure that I have already sees Sql Server installations using with the root of drives.

Now I wonder if there is a bug in the setup or if it was my fault to configure it that way. And how this can be fixed? Meanwhile IT has modified and opened access to the drives for me, so I can access them now, but Sql Server Service actually is stopped. Do I have to reinstall the sql server or do I have to change all the database's path / log path to a subfolder that has to be created?

Does anyone know what went wrong here?


texts:
I'd like to INSERT data from one table to another, where the destination table has all the source columns with a few others in between. The tables have a large number of columns (plus this can get repeated now and then), so I'd rather not explicitly write the column names.

Along the lines of (doesn't work):

INSERT INTO dest_table
(SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'source_table')
SELECT * from source_table

The context is that I'm trying to alter a column's position.


texts: What's the difference between Linked Server solution and SSIS solution?

texts: When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit using Linked Server? Is there any use of Linked Server if I have SSIS in my hand?


texts: Change Data Capture for Asynchronous Secondary Replica

texts:
MSDN states the following in Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for SQL Server 2014:

  Limitations and Restrictions:

  Change tracking and change data capture are not supported on secondary
  databases that belong to a readable secondary replica:

  Change tracking is explicitly disabled on secondary databases.
  Change data capture can be enabled on a secondary database, but this is not supported.

This confuses me: You can not track the changes. However you can enable CDC?

The scenario I am trying to achieve is to use SSIS CDC components on an asynchronous secondary replica. Is this possible? If not what would be other viable approaches?


texts: Error when trying to deploy SSIS package

texts: I am trying to deploy my 2012 SSIS project to the Integration Services catalog on the SSIS server (also 2012).  I am getting the error below.  I checked that there is plenty of space in SSISDB and msdb databases as well as on the disk.  Any ideas what might be causing this?


texts:
To allow any host to connect to PostgreSQL we can use the IPv4 wildcard subnet mask: 0.0.0.0/0

I need this to setup my PostgreSQL's pg_hba.conf file correctly.

What is the IPv6 equivalent?


texts: Should we really turn on trace flag 1118 for every SQL instance?

texts:
It has been recommended that every SQL instance have TF 1118 turned on

  What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.
  Paul S. Randal

It is documented in KB 2154845  while it does impact all databases, it's biggest impact is on tempdb

  This means that each newly allocated object in every database on the instance gets its own private 64KB of data. Tempdb is usually the place where most objects are created, so it makes the most difference there.
  Kendra Little

I have looked around and have not found anyone recommending against its use.  So should we make it part of our process to turn it on when we create new instances, as well as adding it retroactively to existing SQL instances as opportunities present?

I am using SQL 2000 through 2014, have not gone live with any 2016 yet.  We follow the directions to use 1 tempdb per processor up to 8.


texts:
Does anybody know where I can find a list of all the parameter options for sp_configure?  It isn't really jumping out at me when I search online for possible variations and the capabilities of sp_configure.

Thanks!


texts:
I am wondering if anyone can provide information on taking a differential backup and then restoring that differential backup to another SQL Server database?

I have a production environment that is connected to a SQLVault backup system that takes nightly backups.  I also have a development environment that is not connected to the SQLVault backup system.

The production environment is in a remote datacenter.  The development environment is hosted locally.  The WAN linked between the datacenter and the local office is slow, when I try to copy over the 400 GB of backup to my development server.

Currently I need to take a laptop with external HDD to the datacenter and execute the backup process.  Then pickup the laptop and external HDD and bring them back to the office.  Followed by executing the restore of the backup to the development server.

I would like to be able to perform a nightly differential backup and ship that across the WAN and then restore it on top of the previous full backup.  So, when I want to bring my development environment into sync with my production environment...I execute the full restore with differentials from the previous several days/weeks.

I tried taking a full backup yesterday.  I then took a differential backup today (appending it to the backup I took yesterday).  I tried to restore both, but get an invalid state error message when it moved on the second backup file.

I have also looked into log shipping, but that looks like it is configured more for a standby server and not another environment.

Thanks
Jlimited.


texts: Oracle EM 11g - query to find out CPU utilization

texts:
in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization.

my question is:

what is the script to see CPU-Utilization history ?

Thank you


texts: Visual Studio SSDT Unresolved reference to SSISDB

texts: I have a stored procedure in Visual Studio SSDT Project which use SSISDB database. And I get a warnings like "unresolved reference to SSISDB". I tried to create a new reference but I can only select "master" or "msdb" database. Any ideas how to repair this reference to SSISDB issue ?


texts: Insert rows that match a condition, and return rows that don't

texts:
I am using Sql Server 2014

I have an expensive query. I want to insert the results of that query into a table.

However the results should match a certain condition, if they don't I don't want to insert them, but I do want to return them so that my application can warn/record these violations.

So essentially I want to run both of the following:

INSERT INTO MyTable
SELECT ExpensiveQuery
WHERE MyCondition;

SELECT ExpensiveQuery
WHERE Not MyCondition;

But I don't want to run the expensive query twice.

Is this possible?


texts: Data type for currency 'type' column, e.g. usd, cat, etc.

texts:
I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details:

No more than 10 different currencies will be used
All currencies have three-letter ISO names
The currency column will sometimes be used in an CASE statement, and sometimes in a WHERE statement
The table will be quite large so I want to make the optimal choice. Storage size is not an issue. Speed and to a lesser extend ease of use is.

I am considering:

Using a char (3) field and saving the currencies as usd, cad,
etc.
Using an enum field and defining the 10 currencies as
options
Using a tinyint field and relating this to another
database that holds the iso code for each currency.
Using a
tinyint field and instead of relating a database (and having to do
JOINs), I just save the list of very static and       non-changing
currencies in a PHP array. Saves me some JOINing, but       still
allows me to use tinyint.

Anybody any suggestions as to what would be best?


texts: Streaming replication and failover on PostgreSQL

texts:
I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is not providing automatic failover for streaming replication. We can switch the slave to master using a trigger file but it is not manageable. So I'd like a solution with automatic failover and high availability.

Different solutions are available:

Repmgr
Linux Heartbeat
Pgpool-II (for automatic failover only)
Any other tool in case you used.

My question is which solution should be used?


texts: Postgres stops using a (btree_gin) index when select clause includes a column not part of the index

texts:
I am seeing some strange behavior with the btree_gin extension

I have the following table

create table my_table(id INTEGER, a TEXT, b text);

CREATE EXTENSION btree_gin;

CREATE INDEX idx ON my_table USING gin (id, a);

If I run the query :

select t.id, t.a
from my_table t
where t.id = :someNumber and t.a like 'someString'

the index gets used.

If I add another column in the select clause that is not part of the index, the index is no longer used, ex:

select t.id, t.a, t.b
from my_table t
where t.id = :someNumber and t.a like 'someString'

I'm using PostgreSQL 9.6.3

One thing that is very strange, is that the index does get used if I join the table to anotherone, and add a col from the other table in the select, ex:

select t.id, o.a_col_from_other_table
from my_table t
join another_table o on t.id = o.the_fk
where t.id = :someNumber and t.a like 'someString'

However, if the join clause uses a column not part of the index, it will will resort to seq scan.

Update 1

For some reason, the query optimizer does not even use the btree_gin anymore. The only way I can get it to use the (btree_gin) index is with

SET enable_seqscan = OFF;. It turns out that even by using this index, it is quite slow, so the optimizer is probably correct in not using it, but the question is then why is it slow ?   I've used indexes for like query and they work fine, in this case there is not even a wild card.

the real query is :

 EXPLAIN
    SELECT
      b.transcript_translation_accession
    FROM r_prot_search b
    WHERE
        b.transcript_translation_accession like 'IP_304743.1'
        and b.assembly_id = 12
        and b.openprot_release_id = 28
    limit 30;

The index:

CREATE INDEX idx_r_prot_search2_transcript_translation_accession3
ON r_prot_search USING gin (openprot_release_id, transcript_translation_accession);

and the plan :

"QUERY PLAN"
"Limit  (cost=27633.94..593461.27 rows=30 width=13)"
"  ->  Bitmap Heap Scan on r_prot_search b  (cost=27633.94..2366386.90 rows=124 width=13)"
"        Recheck Cond: (openprot_release_id = 28)"
"        Filter: (((transcript_translation_accession)::text ~~ 'IP_304743.1'::text) AND (assembly_id = 12))"
"        ->  Bitmap Index Scan on idx_r_prot_search2_transcript_translation_accession3  (cost=0.00..27633.91 rows=3079721 width=0)"
"              Index Cond: (openprot_release_id = 28)"

I should probably add that the query returns 5 rows, and the table has 10 millions rows.

update 2

Here's an EXPLAIN ANALYZE, with SET enable_seqscan = ON:

"QUERY PLAN"
"Limit  (cost=1000.00..537006.24 rows=30 width=13) (actual time=15164.523..15610.792 rows=6 loops=1)"
"  ->  Gather  (cost=1000.00..2216492.44 rows=124 width=13) (actual time=15164.521..15610.788 rows=6 loops=1)"
"        Workers Planned: 4"
"        Workers Launched: 4"
"        ->  Parallel Seq Scan on r_prot_search b  (cost=0.00..2215480.04 rows=31 width=13) (actual time=15514.285..15603.515 rows=1 loops=5)"
"              Filter: (((transcript_translation_accession)::text ~~ 'IP_304743.1'::text) AND (assembly_id = 12) AND (openprot_release_id = 28))"
"              Rows Removed by Filter: 1948958"
"Planning time: 0.370 ms"
"Execution time: 15628.514 ms"

Now with SET enable_seqscan = OFF;

"QUERY PLAN"
"Limit  (cost=27633.94..593461.27 rows=30 width=13) (actual time=3406.041..8350.549 rows=6 loops=1)"
"  ->  Bitmap Heap Scan on r_prot_search b  (cost=27633.94..2366386.90 rows=124 width=13) (actual time=3406.038..8350.544 rows=6 loops=1)"
"        Recheck Cond: (openprot_release_id = 28)"
"        Filter: (((transcript_translation_accession)::text ~~ 'IP_304743.1'::text) AND (assembly_id = 12))"
"        Rows Removed by Filter: 3228843"
"        Heap Blocks: exact=320353"
"        ->  Bitmap Index Scan on idx_r_prot_search2_transcript_translation_accession3  (cost=0.00..27633.91 rows=3079721 width=0) (actual time=640.817..640.817 rows=3228849 loops=1)"
"              Index Cond: (openprot_release_id = 28)"
"Planning time: 0.394 ms"
"Execution time: 8350.618 ms"


texts: GIST exclude constraint with a specific value

texts:
Is there a way to represent a constraint, where I prevent date range overlaps, but only when a column equals a specific value? So I can have zero or many inactive, but only ever one active?
I'm running into a problem where i need a gist constraint to check date range overlaps but only if a column equals a specific value ('active' in this case).
We have a table which has dates and there can be overlap, if a column equals a specific value (active can only ever have one, but inactive can be zero or many).
GIST seems to be able to say prevent any duplicate (2 active, but also excludes 1 active, 1 inactive, or inactive > 1).
Putting this in two constraints fails, because the gist only covers the range overlap and join id.
e.g.
ALTER TABLE "a_table"
  ADD CONSTRAINT "CHK_some_name"
  EXCLUDE USING gist ("a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

ALTER TABLE "a_table"
  ADD CONSTRAINT "CHK_some_name"
  EXCLUDE USING gist ("an_enum" with =, "a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

Any help/insight would be greatly appreciated


texts:
I have a table foo

with the content

So the records of the table foo can graphically be represented with this graph.

When I run a procedure that should return ancestors using @starting_id=9
(represented with a circle in the graph), I get this result

9

instead of

9
3  and
1

Why?
CREATE PROCEDURE [dbo].[GetParents]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.id = foo.parent_id
    )

    SELECT id FROM chainIDsUpwards
END

Fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=81be3d86dc7581eb60bc7af4c09077e4


texts: SQL Server 2005 Analysis Services won't start

texts:
We use SQL Server 2005 and sometimes our Analysis Services won't start. A simple reboot solves the problem, however, I'm trying to find a solution that does not rely on that.

If I try to start it from the "SQL Server Configuration Manager", I get a timeout error. When starting from windows' services manager, it states that the service was "started and then interrupted".

The windows application log has three entries:
1 - The flight recorder was started.
2 - The service cannot be started: The following system error occurred: . The following system error occurred: .
3 - Service stopped.

I have also tried to start "Analysis Services" with every single logon option. The same error occurs.


texts: How to create index on long data type field

texts:
I have a MySQL table with a long data type field:

`create_date` long NOT NULL,

I want to index it:

CREATE INDEX ix_logs_create_date ON `logs` (create_date) USING BTREE;

But I encounter the following exception:

Error Code: 1170. BLOB/TEXT column 'create_date' used in key specification without a key length 0.049 sec

Is it possible to index a long data type field?  I have searched but cannot find anything discussing this particular issue


texts: Use of MySQL trigger to insert row's week number

texts:
I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This Week."

My question is this: Can a trigger be used to take the datetime from a just-inserted order and insert the week number of that date into another column? If so, can I also use SQLAlchemy to match the order's week number to Python's strftime('%U') to see orders in a given week?


texts: What is the use of the transaction log buffer in SQL Server?

texts:
I have a question like why do we use in transaction log buffer.

Like SQL Server puts the transaction log in the Transaction log buffer, when the checkpoint runs it transfers the dirty pages(committed) as well as logs to the disk (.mdf/.ldf).

So there must be no active log in the actual transaction log file, all the transactions are waiting for backup process.

Thanks in Advance.


texts: Unable to alter authorization on an mirroring endpoint to a login with a slash in the name

texts:
I am trying to run the following statement to alter the authorization on an endpoint as the current login used for authorization is about to be deleted (left the company).

alter authorization on endpoint::Mirroring to domain\sys-admin-group

We use Windows authentication, so all our server logins have the backslash "\" character in it.

Here is the error I received:

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

I have also tried to enclose the login in single quotes but received the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'domain\sys-admin-group'.

I can't find any useful info on the MSDN ALTER AUTHORIZATION page (http://msdn.microsoft.com/en-us/library/ms187359.aspx). Any ideas?


texts:
My database size is approx 1.5 TB and I have approx 2TB free on backup mount point. But after 10 hours my backup fail due to lack of storage and its just 60% complete. Is their any why through which i can resume my backup from the failure point??

mongodump -u admin -p admin -d tex --host xx.xx.xx.xx --authenticationDatabase admin

Do you have any other option then please also share with me.


texts: PostgreSQL: count datasets / lines containing NULL values

texts:
I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved.

For example:

CREATE TABLE tab1 (
id INTEGER PRIMARY KEY,
dat1 VARCHAR,
dat2 VARCHAR);

INSERT INTO tab1 VALUES
(1, NULL, 'abc'),
(2, 'abc', NULL),
(3, 'abc', 'abc');

SELECT count(*)
FROM tab1
WHERE id IS NULL OR dat1 IS NULL OR dat2 IS NULL;
-- 2

(I know that Primary Keys are not likely to habe NULLs ;)

My current solution uses R to build the query.
Any suggestions?


texts:
I've seen the message SQL2043N Unable to start a child process or thread occur every once in a while since upgrading to rhel7 and db2 10.5. db2diag.log does not reveal much out of interest, beside:

FUNCTION: DB2 UDB, oper system services,
sqlo_waitlist::initialize, probe:10
MESSAGE : SysV semget failed with errno:
DATA #1 : Hexdump, 4 bytes

The target machines are used for unit-testing and development and relevant kernel parameters seems appropriate:

]$ cat /proc/meminfo
MemTotal:       12140640 kB

]$ ipcs -l
[...]
------ Semaphore Limits --------
max number of arrays = 3072
max semaphores per array = 250
max semaphores system wide = 256000
max ops per semop call = 32
semaphore max value = 32767

At the moment there are no databases on the machine, yet - what I believe - a huge number of semaphores is associated with db2fenc1:

]$ ipcs -s | grep db2fenc1 | wc -l
2800 [1]

[1] The number 2800 is not the exact number, but it was close to this

]$ db2stop force

has no effect on the number of semaphores in ipcs -s. To release the semaphores I had to do ipclean

The only thing I can think of that might be causing db2fenc1 to grab a lot of semaphores is a fenced java function:

CREATE FUNCTION NYA.REMOVE_DIACRITICS( S VARCHAR(50))
    RETURNS varchar(50)
    FENCED THREADSAFE
    DETERMINISTIC
    NO SQL
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    RETURNS NULL ON NULL INPUT
    EXTERNAL NAME
    'StringUtil:se.vhs.nya.framework.common.util.db.DiacriticUtil!removeDiacritics'
NO EXTERNAL ACTION @

The functions is used in an generated column:

CREATE TABLE ...
( [...]
, <COLNAME2> VARCHAR(25) NOT NULL
    GENERATED ALWAYS AS ( NYA.REMOVE_DIACRITICS( <COLNAME1> ) )
, [...] ) @

but there is no massive insert/updates against this table during testing.

Has anyone experienced something similar where semaphores are not released, and in that case, what was the solution?

PS. Not sure if it is relevant, but the JAVA_HEAP_SZ is:

(JAVA_HEAP_SZ) = 2048


texts: ERDs and modelling ownership of roles within Job table

texts:
I'm currently in the process of modelling what I see as a relatively
  simple relationship through my ERD. I'm not even certain that strictly
  speaking this is Many-to-Many but nevertheless there are Many
  Employees who are associated with Job and many Jobs with which an
  Employee is associated.

  I think that this differs from what I'd assume is a "real"
  Many-to-Many relationship (and by that I mean one which requires a
  link table in the database for example: Students to Courses), as I'm
  really talking about two different roles. One employee being
  responsible for the Authorisation of Many jobs and One employee being
  responsible for the Administration of Many jobs (although, that could
  potentially be the same Employee)

EDIT: Trying to clear up any ambiguity, @Avarkx got me thinking and what I really need to be able to do is to control the overall permissions via some sort of "CanManage" table for allowing functionality to Invoices, Quotes, Drawings & Jobs; rather than allowing an Employee certain functionality on a Job by Job basis (which would be the case in the diagram below)

But, I also need to be able to record which Employee actually carried out the action within Job, which is what I was trying to get across in the paragraphs above (albeit in a roundabout way... Sorry!)

Here's my attempt at modelling the previous suggestion:


texts: Query with different time period in same query ( WHERE )

texts:
I compare the some total numbers between today and 1 months ago.

I created a query like this. Below you will see "commented" Where statements since I cannot get both results in same query

SELECT
  COUNT(DISTINCT a.order_id) AS total_orders,
  SUM(invoice_amount)        AS total_amount
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id
--WHERE CURRENT_DATE >= b.order_creation_date;
--WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date
AND b.seller_id IN(1,3)

I wrote this query that would solve my problem but I think it is too much complex. I wish there was a better one.

SELECT
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_amount_until_now,
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (1, 3)) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (1, 3)) AS total_amount_until_a_month_ago
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id


texts: Discarding results from CTE based on another CTE

texts:
WITH R1 AS
(
    // some complex query
),
R2 AS
(
    // uses R1
    // another complex query
),
// here I already have dataset with Id, Name, ProductReference and other columns
R3 AS
(
    select  Name, ProductReference, Count(*) as Count
    from Products
    group by Name, ProductReference
    having Count(*) > 1
)

Now I need to filter R2 results based on R3, so that if Name and Reference combinations ARE present in R2 then only select where ID is minimum, discard rest of them

R2
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
2       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data
5       n1          someguid1           some data
6       n1          someguid2           some data

R3
Id      Name        ProductReference    Count
1       n1          someguid1           4

Expected Output
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data


texts: SSMS constraint script: Purpose of second alter table statement?

texts:
SSMS scripts Foreign Key constraints as two statements:

ALTER TABLE {table}
WITH CHECK
ADD CONSTRAINT {constraintname} {constraint spec}
GO

ALTER TABLE {table}
CHECK CONSTRAINT {constraintname}
GO

What is the purpose of the second statement?


texts:
I come from SQL Server and I need to restore a oracle .bkp file in my database. I'm using RMAN and the path and name for the .bkp file is:

F:\Oracle Back Ups\Back.bkp

The command that I use in RMAN is:

RMAN> restore controlfile: "F:\Oracle Back Ups\Back.bkp"

But it shows an error:

ORA-19870 error while restoring backup piece F:\Oracle Back Ups\Back.bkp
ORA-19690 backup piece release 11.2.0.4.0 incompatible with oracle release 11.2.0.2.0

I need to know the proper commands to successfully complete this operation.

Note: The guy that provided the back up data gave me two files: One with the extension .bkp, and the second with .ctl. I don't know how the .ctl file is supposed to be used.

I'm using Oracle XE 11.0.0.2.0


texts: Sum values from child tables and ignore equal rows

texts:
I have two child tables with a value that must be summed separately and at the end the totals must be subtracted. The problem is that Child Table A and Child Table B can have 0 or more rows associated to the same Parent Table row, then some rows are calculated twice.

Here is the SQL to create the sample DB:

CREATE TABLE Project (
  id int,
  name varchar(50),
  CONSTRAINT PK_Project PRIMARY KEY (id)
  );

  CREATE TABLE ProjectBenefitCost (
    id int,
    project_id int,
    CONSTRAINT PK_ProjectBenefitCost PRIMARY KEY(id),
    CONSTRAINT FK_BenefitCost_Project FOREIGN KEY(project_id) REFERENCES Project(id)
    );

  CREATE TABLE Cost (
    id int identity(1,1),
    project_cost numeric(9,2),
    benefitcost_id int,
    CONSTRAINT PK_Cost PRIMARY KEY(id),
    CONSTRAINT FK_Cost_BenefitCost FOREIGN KEY(benefitcost_id) REFERENCES ProjectBenefitCost(id)
    );

    CREATE TABLE Benefit (
    id int identity(1,1),
    project_benefit numeric(9,2),
    benefitcost_id int,
    CONSTRAINT PK_Benefit PRIMARY KEY(id),
    CONSTRAINT FK_Benefit_BenefitCost FOREIGN KEY(benefitcost_id) REFERENCES ProjectBenefitCost(id)
    );

    INSERT INTO Project (id, name) values (1, 'Project A');
    INSERT INTO Project (id, name) values (2, 'Project B');

    INSERT INTO ProjectBenefitCost (id, project_id) VALUES (1,1);
    INSERT INTO ProjectBenefitCost (id, project_id) VALUES (2,2);

    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (5006.20, 1);
    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (10000.10, 2);
    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (2000.10, 2);

    INSERT INTO Benefit (project_benefit, benefitcost_id) VALUES (3000.00, 1);
    INSERT INTO Benefit (project_benefit, benefitcost_id) VALUES (50000.00, 2);

If I use this SQL:

SELECT P.id, P.name, SUM(C.project_cost) AS TOTAL_COST, SUM(B.project_benefit) AS TOTAL_BENEFIT
FROM
  Project AS P INNER JOIN ProjectBenefitCost AS BC ON
    P.id = BC.project_id
    LEFT JOIN Cost AS C ON
      BC.id = C.benefitcost_id
    LEFT JOIN Benefit AS B ON
      BC.id = B.benefitcost_id
GROUP BY P.id, P.name

It will return 100000.00 as the TOTAL_BENEFIT for the Project B, because this Project has two related rows in the Cost table.

This is just the first step that needs to be solved before I can subtract TOTAL_COST from TOTAL_BENEFIT.

How can I fix this SQL statement?


texts: Weird order by behavior on SQL Server 2014 SP2

texts:
I am having an issue with a existing query running on a 2008 R2 instance, migrated to 2014 SP2 (No CU).

So far I have tried to use from position, alias, etc. So far I have narrow the event on the DATEPART any ideas?

SELECT DISTINCT CAST(DATEDIFF(HH,DATE_RECEIVED,TEST_DATE)  / 24.0 AS
DECIMAL(5,2))   AS TAT,
DATENAME(MONTH,TEST_DATE) AS MONTHNAME,
DATEPART(WW,TEST_DATE) AS WEEK,
DATEPART(YYYY,TEST_DATE) AS YEAR,
DATEPART(MM,TEST_DATE) AS MONTHNUMBER,
PRODUCT_CODE,TEST_PLAN_NAME,COUNT(PRODUCT_CODE) AS COUNT
FROM CO_SAMPLES
WHERE SAMPLE_TYPE IN ('CA')
AND PROFILE_NAME <> 'Shelf Life' AND RECLASS_NUMBER IS NULL
GROUP BY DATEPART(WW,TEST_DATE),
DATEPART(YYYY,TEST_DATE),
DATEPART(MM,TEST_DATE),
PRODUCT_CODE,TEST_PLAN_NAME,TEST_DATE,DATE_RECEIVED
ORDER BY  DATEPART(YYYY,TEST_DATE)
,DATEPART(MM,TEST_DATE)
,DATEPART(WW,TEST_DATE),PRODUCT_CODE

The return error is as follows

  Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the
  select list if SELECT DISTINCT is specified.


texts:
I have created this index

ORACLE/VIDEO# create index titolo_index
  2  on titolo(titolo)
  3   indextype is ctxsys.context
  4  ONLINE
  5  ;

I do a research

select * from generale where CONTAINS(titolo, '%ragazzi%')>0 order by titolo;

   159|I ragazzi della roma violenta                   |1976   |Drammatico    |1:30    |Vhs       |Italiano

I change from roma to Roma

update titolo set titolo = 'I ragazzi della Roma violenta' where idtitolo = 159;

And it disappear from index!
Why?


texts:
Note:-Might be the question sounds too broad. But, I believe ,it's very good platform(stackexchange) and I can get advice from worldwide expert DBA here. Since, I'm not able to make decision or choose the right option.

Que: I'm new to postgres and need to make PITR solution for our continuously growing database. Need to plan incremental backup solution as well since database size is increasing at rapid speed. If we can't afford to lose any data and also need to have PITR, which one should be best solution:

Have an standby and in case of any failure/disaster take full backup(pg_basebackup) of the standby db cluster and do PITR from that backup(recovery.conf -time).

continuously taking incremental backups+log backups.
use any 3rd party tool to take incremental as well as log backup of postgres db.

Please let me know which option should be better or any other option if anybody has then suggest that option as well.Also, let me know if any performance impact can happen with a database in case we have multiple standby for that database or any negative impact of keeping a standby.

-Thanks in advance!


texts: Optimistic Paging and separate query for COUNT?

texts:
I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count.

There are 2 ways to achieve this:

Paging query can include COUNT(*) OVER() column
Separate query

Which one will be best? Here is my SQL:

ALTER PROCEDURE [dbo].[GetAllProducts] (@PageNumber int,
@PageCount int)
AS
BEGIN
    SELECT
        Products.[ID] AS [ID1],
        Products.[Name] AS [Name],
        Products.[Price] AS [Price],
        Products.[NewPrice] AS [NewPrice],
        Products.[ShortDescription] AS [ShortDescription],
        Products.[SKU] AS [SKU],
        Products.[ProductTypeID] AS [ProductTypeID],
        Products.[ImageID] AS [ImageID],
        Products.[Promotion] AS [Promotion],
        Products.[ParentID] AS [ParentID],
        Products.[Attributes] AS [Attributes],
        ROW_NUMBER() OVER (ORDER BY Products.[ID] ASC) AS [row_number]
    FROM [dbo].[Products] AS Products
    INNER JOIN [dbo].[Images] AS Images
        ON Products.[ImageID] = Images.[ID]
    ORDER BY Products.[ID] ASC
    OFFSET ((@PageNumber - 1) * @PageCount) ROWS
    FETCH NEXT @PageCount ROWS ONLY;
END

The Products table has more than 50,000 rows, sometimes static, sometimes changing a lot.


texts: How to encrypt/secure single columns ins SQL Server?

texts:
I would like to secure some columns in my data warehouse. People who are generally allowed to query the database should get returned the columns but encoded. For example:

SELECT PERSON_ID FROM PERSON

PERSON_ID
-----------
yba986SDOAH

When some else who has the key queries in some way the table/columns then he/she should get the correct value.

Which options do i have in SQL Server > 2016?


texts: What are the risks of copying MDF/LDF files without detaching the DB first?

texts:
Where DB backups are not available, our support team has been trained to restore using the following steps:

Get a copy of the MDF/LDF from a file system backup.
Stop the instance.
Overwrite the existing MDF/LDF files.
Restart the instance.

This is without a detach. The MDF and LDF files are simply overwritten and the instance restarted in the hope that the new files can be initialized correctly.

I'm currently trying to write a document describing the appropriate use of detach/attach in this scenario. While I know this is the correct approach, I can't find any resources which describe the risks of the other approach.

Can anyone provide any advice?


texts: What's the best practice to avoid collisions in throwaway temp tables?

texts:
In SQL Server, I'm used to creating a #temp table for temporary storage in a complex stored procedure. Not wanting to clutter up the system, I DROP the table when done.

However, if I do the same in Oracle:

CREATE GLOBAL TEMPORARY TABLE Temp.RecordsToBeDeleted
    (PK INT NOT NULL PRIMARY KEY, Value VARCHAR2(10) NOT NULL)
-- Populate Temp.RecordsToBeDeleted
-- Use Temp.RecordsToBeDeleted
DROP TABLE Temp.RecordsToBeDeleted

...in one stored procedure, and another stored procedure does something similar and also uses the name Temp.RecordsToBeDeleted, they could collide.

The actual data in the table will not be visible across stored procedures, but the schemas could be different, or I could DROP one while the other is running.

Is there an established best practice for this? I could prefix each table name with the name of the stored procedure, but that's clumsy. I could treat GTTs as first-level objects, created with the rest of the schema rather than being created and dropped as needed, but that would increase maintenance; stored procedures would no longer be wholly self-contained.


texts: Automate backup using Windows Task Scheduler

texts:
I want to run an automatic backup of SQL Server 2012 Express using Windows Task Scheduler.

I have 3 user databases. I want to backup all 3 user databases through a single stored procedure and a single batch file.

My environment is:

Windows Server 2012 R2
SQL Server 2012 Express
Windows domain

I have gone through some related articles:

How to schedule and automate backups of SQL Server databases in SQL Server Express
Automated backups utilizing SQL Server and Windows Task Scheduler
Schedule a daily backup with SQL Server Express

However, it is not clear to me how to make a stored procedure and run that stored procedure using a batch file?


texts: In Subselect, using GetDate instead of a @variable leads to worse execution plan

texts:
Execution plans

This is a simplified version of the real-life problem. When using GetDate in subselect, the plan is worse and different. the 2 queries:

select * , (select count(1)
            from hamsfa.customer as sub
            where sub.tstamp_nosync > @old
              and sub.cus_id > main.cus_id) as x
from hamsfa.customer as main ;

select * , (select count(1)
            from hamsfa.customer as sub
            where sub.tstamp_nosync > dateadd(year, -100, getdate())
              and sub.cus_id > main.cus_id) as x
from hamsfa.customer as main ;

What I don't fully understand is why.

The variable should be by my understanding be worse.

There is another query where this leads to a difference of almost 700% (1s vs 7s).

So the question is: Why does a variable produce better plan even through it should be worse by my experience?

EDIT: Statistics are up to date and correct


texts: SELECT multiple values from one table having matching record in another table in one row

texts:
I have two tables as follows:

create table first (id int, name varchar(20));
create table second (id int, value char(1),fid int);

These tables have the following records:

insert into first values (1,'Ahmad'),(2,'Sami'),(3,'Khan');
insert into second values
  (1,'a',1)
, (2,'b',1)
, (3,'c',2)
, (4,'d',1)
, (5,'e',2)
, (6,'f',3);

I want to select those records from table second which have matching records in table first in a single row.

The desired output would be:

 id | name  | value | value | value
 ---+-------+-------+-------+-------
  1 | Ahmad |   a   |   b   |   d
  2 | Sami  |   c   |   e   |
  3 | Khan  |   f   |       |

The number of values for a single record of table first is not limited and there can be n number of values for a single record of table first.

n is unknown, but I need to repeat the column value for each value in table second which has matching record in table first. I need the whole table like above The desired result. I need only those rows referenced by table second.
The version of Postgres will be the latest one.


texts:
I have a table that contains XML data in a varchar type field.  This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table.

I recently created some views which parse the XML and in some cases make a pivot of some of the values.  The views provide excellent data for my reports but come with a serious performance hit.  I'd like to find out if I can improve performance with indices.

Here is an example of a view:

create view StudentHours as
  select
      x.TableRecordId as Id
    , x.RecordXml.value('(/TableRecord/LOGDate)[1]', 'DateTime') as LogDate
    , x.RecordXml.value('(/TableRecord/LOGHours)[1]', 'float') as Hours
    , x.RecordXml.value('(/TableRecord/LOGApproved)[1]', 'varchar(10)') as Approved
    , y.PKTableRecordId as CourseId
  from
    (select TableRecordId, Cast([Schema] as Xml) as RecordXml
     from TableRecords where TableSchemaId = 1857) as x
  join TableRecordRelations as y on x.TableRecordId = y.FKTableRecordId

I then will use this view in other views which do aggregates and such.

The table called TableRecords has an index on its unique id TableRecordId and the TableRecordRelations also has indices on its important fields as well.

Will adding an index or two help this view's performance? Is more data needed to determine this?


texts: show grants for someuser not working for root

texts:
I have two servers both running on Version(): 10.3.21-MariaDB

On server1 when I log into mysql as root and execute the command: show grants for someuser; It shows me all the grants for that user.

On server2 when I log into mysql as root and execute the command: show grants for someuser; I get this error:

  ERROR 1141 (42000): There is no such grant defined for user 'someuser'
  on host '%'

On both servers when I execute the command:

select user from mysql.user;

I get a list of the users. So the users that I am trying to see their grants are there ...

What is would be different with server2 that root can not see the grants ?

on all the servers is_role = N

but I did notice another difference among the 3 servers - the root user has different host entries
Also I have added the vm type that the server is running
also have results for a 3rd server

server3 (virtuozzo)
show grant must have the @somehost
using all of the different host entries give a result with the only difference being the @somehost is whatever was used in the for someuser@somehost

GRANT USAGE ON . TO 'someuser'@'somehost'  indentified by ....
GRANT USAGE ON . TO 'someuser'@'ip address'  indentified by ...
etc.

root user entries:
| root      | localhost             | N       |
| root      | server.example.com    | N       |
| root      | 127.0.0.1             | N       |
| root      | ::1                   | N       |

server2 (vmware)
show grant must have the @host
only the @ip address gives a result

root user entries:
| root                | localhost             | N       |
| mysql.session       | localhost             | N       |
| mysql.sys           | localhost             | N       |

server1 (kwm)
show grant does not have to have the @host
using all of the different host entries give a result with the only difference being the @somehost is whatever was used in the for someuser@somehost

GRANT USAGE ON . TO 'someuser'@'somehost'  indentified by ....
GRANT USAGE ON . TO 'someuser'@'ip address'  indentified by ...
etc.

root user entries:
| root               | localhost                         | N       |
| root               | cent6base-cpanel.pxe.bluehost.com | N       |
| root               | 127.0.0.1                         | N


texts: Database structure, relational or data warehouse?

texts:
Good morning,

Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing  the following columns:

Date (DateTime), Value_of_rainfall (Float)

Each of these tables are for a specific location.

It has been suggested i create another table with the following columns:

LocationID (tinyInt), LocationName(char(6))

and insert into the first table a new column called LocationID(tinyInt).

Now the confusion i have is regarding that the data i have stored in the rainfall data datatables. Its been suggested that all the data from each location be contained in one data table. The statistical analysis im looking to carry out on the info is very (as far as i have envisioned thus far) location specific and wont require querying multiple locations at once. A couple months worth of data for one location = nearly 3 million rows and im looking to set up long running calculations on the data. therefore would a data warehouse be more appropriate? If so, could someone give me some pointers on how i should lay it out?

Thanks for your time.

Note: Im using Sql Server 2008


texts: Temp table lives after being explicitly dropped

texts:
I'm creating a post-deployment script in VS2017 database project.
In this script, I'm using single temp table, which is re-created frequently to adjust to different data structures.

Below you can see a sample excerpt:

    SELECT
        *
    INTO #tmp
    FROM dbo.SourceTable1
    WHERE
        1 = 0

    drop table #tmp

    SELECT
        *
    INTO #tmp
    FROM dbo.SourceTable2
    WHERE
        1 = 0

Unfortunately, when I execute this code, I receive an exception:

Msg 2714, Level 16, State 1, Line 15 There is already an object named '#tmp' in the database.

It looks like the DROP statement is not working here as expected to.
Do you have any idea why does it behave like this?


texts: Multiple SQL Server Indexes, which ones to keep?

texts:
I am looking at my database that several people have worked on. A particular table is responding slowly when queried on a certain join.  I notice some of the parameters of the join are not indexed.  So I look at the indexes that are on the table and there are currently 10 of them, a lot with duplication it looks to me:

NonClustered Indexes:

Index1: ManufacturerID, IsDeleted
Index2: IsDeleted, ManufacturerID
Index3: CustomerId
Index4: IsDeleted
Index5: ManufacturerId, IsDeleted, Exported
Index6: ManufacturerId
Index7: ManufacturerId, ModifiedOn
Index8: ManufacturerId, OrderGUID
Index9: SalesRepId

ClusteredIndex: OrderId

The 2 columns I do join on are RepNumber and WrittenForRepNumber.

So my confusion here is why do I have so may indexes that seem to contain the same columns?

Do I need an index for each of these as they look like some of them are geared towards specific queries?


texts:
I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. When I do:

DROP DATABASE db1;
DROP DATABASE db2;

I get following error:

  DROP DATABASE cannot be executed from a function or multi-command string

Is there any way around this problem? I.e., is there any way to run each command separately (like when using "GO" statement on MSSQL), or some other sensible way to easily drop multiple databases? I need this functionality for maintenance scripts during development.


texts: Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

texts:
I want to insert into a database with a query that looks something like the following:

INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>);

As I mentioned on a previous answer here. The answer to that question was to create a string of values and then treat that string as a comma separated list. Just before I implement that, I want to know if building up a TVP is a viable solution. Two questions:

QUESTION 1: What to watch out for WRT type-coercion?

The TVP will have a schema of:

| colName (nvarchar) | value (nvarchar |

And the values will be of type text, bool, int, decimal, date, etc. Is there anything I have to watch out for when converting types to nvarchar?

I will then by dynamically building up a sql query from the values in the TVP. Are there any potential problems with type coercion from nvarchar to any other SQL Server type?

QUESTION 2: Is this a good idea?

The answer to my previous question (as linked to above) suggests using NVARCHAR strings as a list of cols and vals. Between these two approaches, which would be considered preferable?


texts: T-SQL to Group time interval change by date range in sql server

texts:
The original table has the column Timestamp with Interval

Interval: difference in minute between the current and previous Timestamp when sorted by Timestamp itself

Timestamp               Interval(InMinute)
2016-12-31 00:28:00     NULL
2016-12-31 00:29:00     1
2016-12-31 00:30:00     1
2016-12-31 00:45:00     15
2016-12-31 01:00:00     15
2016-12-31 01:15:00     15
2016-12-31 01:16:00     1
2016-12-31 01:17:00     1
2016-12-31 01:18:00     1
2016-12-31 01:19:00     1

I want to detect a time interval change using T-SQL and produce the output

StartDate                EndDate                Interval
2016-12-31 00:28:00      2016-12-31 00:30:00    1
2016-12-31 00:30:00      2016-12-31 01:15:00    15
2016-12-31 01:15:00      2016-12-31 01:19:00    1

I wanted to tell for how long an Interval stayed the same. The second row says that from 2016-12-31 00:30:00 to 2016-12-31 01:15:00, the Interval stayed the same i.e. 15. But then it changed back to 1 after 2016-12-31 01:15:00


texts:
I am defining a new data type tpointp for storing trajectories of moving objects. One function associated to this data type is atPeriod(tpointp, tsrange) which projects the full trajectory to a time period represented as a timestamp range (tsrange).

CREATE FUNCTION atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'MODULE_PATHNAME', 'temporalp_at_period'
    LANGUAGE C IMMUTABLE STRICT;

This function can be used in queries like the following one

EXPLAIN
SELECT atPeriod(Trip, Period) AS Trip
FROM QueryPeriods, berlinmod_trip
-- Call the temporal index if any
WHERE getTime(Trip) && Period

Nested Loop  (cost=0.14..475.50 rows=898 width=32)
  ->  Seq Scan on queryperiods  (cost=0.00..3.00 rows=100 width=24)
  ->  Index Scan using berlinmod_trip_temporal_gist_idx on berlinmod_trip  (cost=0.14..2.30 rows=18 width=104)
        Index Cond: (gettime(trip) && queryperiods.period)

As it is done in PostGIS, I would like to use inlining for implicitly calling any potential indexes. That would allow the user to remove the WHERE clause in the above query. I rewrote the definition of the function atPeriod() above as follows.

CREATE FUNCTION _atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'MODULE_PATHNAME', 'temporalp_at_period'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'SELECT @extschema@._atPeriod($1,$2) WHERE getTime($1) OPERATOR(@extschema@.&&) $2'
    LANGUAGE 'sql' IMMUTABLE;

However with the new definition there is no implicit index call

EXPLAIN
SELECT atPeriod(Trip, Period) AS Trip
FROM QueryPeriods, berlinmod_trip

Nested Loop  (cost=0.00..47224.47 rows=179700 width=32)
  ->  Seq Scan on berlinmod_trip  (cost=0.00..49.97 rows=1797 width=104)
  ->  Materialize  (cost=0.00..3.50 rows=100 width=24)
        ->  Seq Scan on queryperiods  (cost=0.00..3.00 rows=100 width=24)

In PostgreSQL there are several conditions to enable inlining SQL functions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

However, I was not able to understand why this doesn't work for the function atPeriod().

Notice that this works for boolean functions. I have another function intersects() which generalizes the ST_Intersects topological operator:

CREATE OR REPLACE FUNCTION _intersects(tpointp, geometry)
    RETURNS boolean
    AS 'MODULE_PATHNAME', 'intersects_tpointp_geom'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION intersects(tpointp, geometry)
    RETURNS boolean
    AS 'SELECT trajectory($1) OPERATOR(@extschema@.&&) $2 AND @extschema@._intersects($1,$2)'
    LANGUAGE 'sql' IMMUTABLE;

In this case implicit index calls are performed as shown in the following query.

EXPLAIN
SELECT *
FROM QueryRegions R, berlinmod_trip T
WHERE intersects(R.Geom, T.Trip)

Nested Loop  (cost=0.14..278.50 rows=60 width=959)
  ->  Seq Scan on queryregions r  (cost=0.00..13.00 rows=100 width=847)
  ->  Index Scan using berlinmod_trip_spatial_idx on berlinmod_trip t  (cost=0.14..2.65 rows=1 width=112)
        Index Cond: (r.geom && trajectory(trip))
        Filter: _intersects(r.geom, trip)


texts: difference between UPDLOCK and FOR UPDATE

texts:
given the following code:

Set conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
conn.BeginTrans
...
//This is the line it is about
RS.Open "SELECT a,b FROM c FOR UPDATE", conn, adOpenDynamic, adLockPessimistic
...
RS.Close
conn.CommitTrans
conn.Close

What is the difference when I use:

RS.Open "SELECT a,b FROM c (UPDLOCK)", conn, adOpenDynamic, adLockPessimistic

UPDATE:

Right now we use FOR UPDATE but it looks like the table is not locked. The SQL Profiler shows us this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

declare @p1 int  set @p1=NULL  declare @p3 int  set @p3=229378  declare @p4 int  set @p4=163842  declare @p5 int  set @p5=NULL  exec sp_cursoropen @p1 output,N'SELECT a,b FROM c FOR UPDATE',@p3 output,@p4 output,@p5 output  select @p1, @p3, @p4, @p5

UPDATE c SET a = a + 1 WHERE b = 'Value'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

IF @@TRANCOUNT > 0 COMMIT TRAN

The update statement we use:

SQL = "UPDATE c SET a = a + 1 WHERE b= 'Value'"


texts: Temporarily disable indexes before updating whole table

texts:
For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. However, a query like below updates over 20M records in a table that has ~10 indexes (created during pg_restore).

UPDATE users SET email = users.id || '@example.com',
phone_number = NULL;

This takes a few hours on current setup.

To speed things up, I wanted to disable the table's indexes just for running the update and then enable them again and reindex the table. How can I do this? Any other ways to speed up this UPDATE?

Notes:

I've tried

UPDATE pg_index
SET indisready=false
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='users'
);

but got the error below.

ERROR:  permission denied for relation pg_index

Apparently you can't do this as superuser in AWS RDS PostgreSQL instance.

Dropping the indexes and then creating them again wouldn't be convenient, because I would have to know what columns they cover in advance. Ideally I would have a command that store existing index configuration in some temporary variable and then recreate them again using this info.


texts: Data Tier Applications (DAC) on replicated databases

texts:
We are exploring options to accomplish 3 things: 1) source control the database, 2) centralize and coordinate all database changes and tie them to application changes, and 3) automate database deployments.

I'm considering using DACPACS for this but we have 4 production databases that have replication between themselves set in place. The replication was set up a long time ago and was implemented in an odd fashion. Essentially, not all 4 databases are exactly the same. Some data is replicated and some is not.

My question is about applying DACPACS. If we were to apply the DACPACs to each database during a deployment, how would this affect replication?

Thanks


texts: Is it possible to view LRU-K values in SQL Server?

texts:
In SQL Server's sys.dm_os_memory_cache_entries, it is possible to view both the original cost of an entry in the cache as well as the current cost of the cache entry (original_cost and current_cost respectively). The DMV sys.dm_os_buffer_descriptors contains a record of the pages that are currently in memory as well as some metadata about the pages. One interesting chunk of info not available in the DVM are the LRU-K values for the data pages.

Is it possible to get the LRU-K values for data pages in buffer pool in SQL Server? If so, how?


texts: What perfmon counters should I use to get the fastest restore possible on an idle system?

texts:
I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk.

I want to restore database as quickly as possible as part of a "reset my dev environment" process. The system is mostly idle when I do this. The system runs SQL Server and Tomcat 7. I usually have an RDP session which has one or more of the following open Perfmon, SSMS, Visual Studio 2013, Visual Studio 2010. Process Monitor. I'm willing to tune this restore to the point where this RDP session becomes completely unresponsive during the restore process. No one but me is using this server.

Using Nic Cain's post I was able to get my restore process from 6xx seconds to ~100-120 seconds with the following parameters:

RESTORE DATABASE [XXXX]
    FROM DISK = N'$(BackupFile)'
        WITH
            REPLACE,
            MOVE N'RMS_SIMULATION' TO N'$(MdbPath)\XXXX.mdf',
            MOVE N'RMS_SIMULATION_log' TO N'$(LDBPATH)\XXXX.LDF',
            STATS = 5,
            MAXTRANSFERSIZE = 4194304,
            BUFFERCOUNT = 72

By adding MAXTRANSFERSIZE and BUFFERCOUNT I was able to peg the I/O Data bytes/sec and I/O Data operations/sec counters to the top of the perfmon chart. Before that, bytes/sec would spike to the top, but spend more time at the bottom of the scale, and operations/sec was spikey in the middle of the scale.

Now I know I can tweak with the numbers and run the restores 100 times at different numbers and measure the restore time. While, there is a certain scientific vigor in that approach, I'd like something more data-centric.

I know I don't want SQL Server page faulting during the restore, because hitting virtual memory is a big slow down. However, what other perfmon counters should I be looking at?


texts:
I have an installation of SQL 2016 Enterprise and my boss want to deploy TDE.  I googled online and all the instruction said about "you can use a self sign certificate for test/dev purpose".  Most guide just said backup the master key and cert and that's it.

I have two SQL Server, they sit on two separate domain.  I want to make sure the database encrypted on one server can be restored on the other server.

What is the proper way and step that should be applied to a production server.  I think there is more than just creating a self sign cert and back it up somewhere safe.


texts:
I'm writing a console application that fetches data from database (SQL) and writes to an excel sheet. Now I want to add a chart in the workbook according to the datatable values. How can I create chart in the sheet before saving? How will I use dataset values to create chart?

Thank you.


texts: XE_FILE_TARGET_TVF WaitType in Azure SQL

texts:
Recently I started exploring Azure Sql and came across waittype XE_FILE_TARGET_TVF. I tried searching online to get more info but didn't get enough information.

I am just curious to know:

What does this waittype mean ?
Is it only specific to Azure SQL ?
How does it affect sql query performance ?


texts: Is the subscription of merge replication only for SQL Server Compact Edition?

texts:
While creating a publication I saw this

Does this mean the subscribers can't be other editions of SQL Server but Compact?
Do other editions need to use Peer-to-Peer replication instead?
Thanks


texts:
We created a Windows Failover Cluster, then added two instances of SQL Server as nodes of a SQL Server failover cluster.

We set the servers to use "AlwaysOn Availability Groups" in SQL Configuration Manager.

To test a failover, I loaded up and ran a long query, then brought the active node down by using Failover Cluster Manager to stop the cluster service on the active node.

The query broke with no connection, and the server showed as unavailable for about 20 seconds before the node was drained and the new node took over.

Did I do this wrong? How should I have configured this so that there was little-to-no connectivity loss?

Is AlwaysOn not always on?


texts: Reclaim Transaction Log space of SQL Server

texts:
I have a SQL Server databases for which log files have grown huge. I have log backup scheduled to run only once a day. But now all the disk space is used by these log files.

Can anyone help me figure out what could be possible ways to reclaim that used space again. Here's what I understood and aware off.

I need to take more frequent log backups. As, SQL Server will internally reuse that logspace as and when required.
I have DB's in Full Recovery Model and under AlwaysOn setup. Hence, if I want to shrink them, I'll need to first remove them from AlwaysOn and then put them in Simple Recovery mode and then Shrink the Log File to desired size limit.

Is there any other way apart from the two mentioned above. Can I create a new log files with fresh required size on some other drive and make my db's point to that new location. Is it a doable task? need more clarity on this.

Any inputs on this would be a great help.


texts: Why does PostGIS create an ugly table in my database called "spatial_ref_sys"?

texts:
I need to use PostGIS, and do use it, but it bothers me that it created this ugly "spatial_ref_sys" table in my database, in the main (public) schema, where I keep all my own tables. It looks ugly.

It also has added a trillion "functions", which is not a big problem to me since I don't have any "functions" of my own, or use them, but the same principle stands for this as well.

Why, given that it's an extension/"PG library" of sorts, doesn't it at least use its own schema, as to separate itself from my own stuff? I assume that there is a very good reason, but I can't figure it out.


texts: Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

texts:
I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes.  My problem comes to figuring out which other tasks I want to do and how often I should do them.

So, so far I have this in mind.  Correct me if there are any flaws in my thinking or a better way to do this.

Backup - All Tables, Full Backup (daily)
Backup - Selected Tables, Full Backup (hourly)
Backup - Transaction Logs (every 15 minutes)
Check database integrity (daily)
Reorganize index (daily)
Update statistics (daily)
Shrink database (weekly)
Rebuild index (weekly)
Maintenance cleanup (daily)

I remembered reading some time ago (when I set up a similar plan at another job) that some of these tasks don't need to be run on a daily basis or should not be run daily.  As to which ones, it escapes me.  I could use a little guidance on creating a better maintenance plan that will reduce data loss in an disaster, but won't tax the system when running during peak hours (and also increase the performance).


texts: SQL Only display data with Max latest date date


texts:
I want to select last X rows from a table, so what I did was:

SELECT * FROM table ORDER BY id DESC LIMIT 120

and it worked, but I want it to be ordered ascending, so I tried with:

SELECT * FROM table ORDER BY id DESC LIMIT 120 ORDER BY id ASC

expecting that it would reorder selected rows, but it didn't work.

So how can I achieve it?


texts:
I have a database in SQL Server. I need to make a join query on a table itself, the menutable.

However, the left table is missing altogether from the result set. I only get the results from the right table.

Also I cannot aggregate the right table into an array like object as I can do in PostgreSQL and cannot have a group byed left table id result set, I use group by for left table id.

I can achieve this in PostgreSQL I do the following query and get what I expect and want. I make use of PostgreSQL's json_agg aggregate function:

select mt1.*, json_agg(mt2.*)
from menutable mt1
join menutable mt2 on mt1.id = mt2.üstmenuid
group by mt1.id;

Result:

    {
      "results": [
        {
          "id": 4,
          "menuadi": "Ortak Haberler",
          "üstmenuid": null,
          "json_agg": [
            {
              "id": 5,
              "menuadi": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
              "üstmenuid": 4
            },
            {
              "id": 6,
              "menuadi": "Sed eget ipsum sit amet massa varius commodo",
              "üstmenuid": 4
            }
          ]
        },
        {
          "id": 1,
          "menuadi": "Ortak Konulu Mansetler",
          "üstmenuid": null,
          "json_agg": [
            {
              "id": 2,
              "menuadi": "In hac habitasse platea dictumst",
              "üstmenuid": 1
            },
            {
              "id": 3,
              "menuadi": "Nullam molestie augue sit amet magna bibendum imperdiet.",
              "üstmenuid": 1
            }
          ]
        }
      ]
    }

In SQL Server, I run the following, almost identical query apart form table names cases, lacking an aggregate function for the rows from right table:

SELECT  mt1.*, mt2.*
FROM dbo.MenuTable AS mt1
JOIN dbo.MenuTable AS mt2 ON mt1.ID = mt2.UstMenuID;
GROUP BY mt1.ID, mt1.MenuAdi, mt1.UstMenuID, mt2.ID, mt2.MenuAdi, mt2.UstMenuID

Result set (left table is missing altogether):

 {
  "results": [
    {
      "ID": 2,
      "MenuAdi": "In hac habitasse platea dictumst",
      "UstMenuID": 1
    },
    {
      "ID": 3,
      "MenuAdi": "Nullam molestie augue sit amet magna bibendum imperdiet.",
      "UstMenuID": 1
    },
    {
      "ID": 5,
      "MenuAdi": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
      "UstMenuID": 4
    },
    {
      "ID": 6,
      "MenuAdi": "Sed eget ipsum sit amet massa varius commodo",
      "UstMenuID": 4
    }
  ]
}

What aggregation method should I do to have my intended result form the JOIN I have constructed in above PostgreSQL example?

Note: Menu names differ a bit in terms of case. Now I am trying to use Sequelize, but it gives the warning SQL Server does not support associating a table to itself, which I need to have JOIN.


texts: Why are parallel writes in MySQL as slow as sequential writes?

texts:
I have many MySQL write-only processes. They all go to the same MySQL master. I run them sequentially to get a baseline time. I basically chain together several mysql command-line clients, one-after-another. Later, I parallel fire up several mysql clients at the same time to get an experimental time. Why are the times the same?

More facts:

Each client hits a separate schema. This eliminates the possibility of table or row locking contention between clients.
I have tried this on Win32 XP with a couple of cores and 4GB RAM vs. RedHat 5.6 64-bit with 24 virtual cores and 32GB RAM. The baseline vs. experimental is the same on both.
On the RedHat I've tried all TCP clients vs. all SOCKET clients. No difference probably because the server isn't under load.


texts: SQL Server 2008 R2 Performance Degradation - High Lazy writes/sec and free list stalls/sec

texts:
I am supporting a customer of ours who has an enterprise edition of our web application living on virtualized servers at their location. They have 3 web servers and 1 DB server. All 4 servers are virtualized with vCenter and have Windows Server 2008 R2 installed.

The DB server is running SQL Server 2008 R2 with 36 GB of RAM, and separate drives for the DB, logs, backups, etc. (7 virtual in total) on a SAS setup, though the backups occur onto a SSD drive.

The web servers are 4 GB RAM each and load balanced. We have a few processes that import data from third parties into our application for end-users, but recently one of these processes have bottle-necked on the server and caused issues. A process that should take hours takes days, and often times fails out, so I have to manually queue up the process in SQL and let it run.

After much investigation, I'm at a loss as to what the issue it. I've confirmed all of the Web Application settings are the same as our hosted environment, which houses customers much larger than this and our DB server is more robust, but the overall load onto the server is great.

Some metrics that I have included cause me to worry about the processes that are actually occurring on the server. I've included these below but I am specifically worried about the Lazy writes/sec and free list stalls/sec, as to me this is indicating that the process is overloading the server in memory and dumping the page to hard disk, which would drive down performance. Is this correct? Can anyone give me ideas on where to go from here? The customer definitely is not liking the idea of adding more resources to the DB server, so I want to be able to prove, definitively, where the issue is at.

Since this is in a virtualized environment, could it also be that the resources just happen to be shared, driving down the performance of the VM and causing this to be flushed to the drive? Any help would be greatly appreciated. My hardware experience has met its limits and I am not a DBA, so I am trying to wrap my head around what all actually goes on in the background with SQL Server.

Thanks!

-Buffer cache hit ratio           1363657/sec
-Buffer cache hit ratio base      1363687/sec
-Page lookups/sec                 28043473454/sec
-Free list stalls/sec             621/sec
-Free pages                       1438/sec
-Total pages                      3932160/sec
-Target pages                     3932160/sec
-Database pages                   3846600/sec
-Reserved pages                   0/sec
-Stolen pages                     84122/sec
-Lazy writes/sec                  77354/sec
-Readahead pages/sec              15305687/sec
-Page reads/sec                   16859120/sec
-Page writes/sec                  7751703/sec
-Checkpoint pages/sec             5408194/sec
-AWE lookup maps/sec              0/sec
-AWE stolen maps/sec              0/sec
-AWE write maps/sec               0/sec
-AWE unmap calls/sec              0/sec
-AWE unmap pages/sec              0/sec
-Page life expectancy             16434/sec


texts: SQL Query for monitoring exceptions between parent/child table

texts:
enter code hereI need help with a query that I want to create. For instance, I have two tables (Process & Article).

Article is the parent table what has a unique article number and in the Process table there are processes created under an article number.

I want to create a query to monitor instances when an Article is Active and ALL the Process status of that article number is Inactive

Article Table

ArticleTitle                   ArticleNumber   ArticleStatus
-------------------------------------------------------------
 Expense Report                       1          Active
 Inbox Monitor                        2          Active
 Daily finance report                 3          Active
 Order Audit                          4          Active
 Order Tracking                       5          Inactive

Process Table

 ProcessTitle                   ArticleNumber   ProcessStatus
-------------------------------------------------------------
 Expense Report CPA                   1          Active
 Expense Report BG                    1          Active
 Inbox Monitor db                     2          Active
 Weekly finance report CPA            3          Inactive
 Weekly finance report BG             3          Inactive
 Order Audit for xyz                  4          Active
 Order Audit for abc                  4          Inactive

 Order Tracking                       5          Inactive


texts: Writing SQL statement in sqlcmd to hide the milliseconds for column type of time


texts:
I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The system cannot find the file specified"

The database connection is: my-pc.C:\DB\DBV8.1.1.MDF.dbo


texts:
Being asked to implement an application for purchasing management, the database managing system which I have to install, is SQL Server 2008.

I am having some issues while installing the main component, Database engine.

I've searched a lot for my problem, I found out that during the installation, I have to use the account network service rather than system, and after uninstalling all the components, and then re-installing SQL Server. However it didn't do any better.

Just for the OS I am using, it's a Windows 7 Professional.
As for the message printed during the installation, i have :

  "Wait on the database recovery failed. Check the SQL Server error log for potential causes."

Any ideas where the problem can be ? or do i need another version of windows to install it ?


texts:
We have two nodes. Node1 was down for a long time. During this time Node2 raised 1 TB capacity when Node1 has 100 GB.

We tried to repair Node1 with nodetool repair but nothing changed. After that we started nodetool repair in Node2, it took 5 days to compaction but nothing changed either.

Actual status here:

Datacenter: dc1
===============
Status=Up/Down

|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens       Owns (effective)  Host ID    Rack

UN  172.x.y.z     149.46 GB  256          100.0%            xxx  rack1
UN  172.x.y.k     1.04 TB    256          100.0%            xyz  rack1

Nodes are in AWS. What should we do?


texts: Execution Plan StatementText contains incorrect syntax

texts:
I have run an execution plan with management studio 2014 on mssql 2008 R2.
To be able to better read the Selectstatement i right-clicked the plan to view it in xml.

I noticed that some statements seem to contain invalid charachters SELECT @IsHeadVersion = IsHead&#xD;&#xA;FROM myTable. At least if i copy them into a new query window i get an invalid syntax error

  <StmtSimple StatementCompId="10" StatementEstRows="1"
  StatementId="2" StatementOptmLevel="TRIVIAL"
  StatementSubTreeCost="0.0032831"
  StatementText="SELECT @IsHeadVersion = IsHead&#xD;&#xA;FROM myTable
   WHERE Id = @VersionID&#xD;&#xA;&#xD;&#xA;
  -- if head  and no delta, select platformIDs and vehicleIDs in this version&#xD;"
   StatementType="SELECT" QueryHash="0x29CDF8384A77017F" QueryPlanHash="0xF39275675F085D1B">
   <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true"
        ANSI_WARNINGS="true" ARITHABORT="true"
        CONCAT_NULL_YIELDS_NULL="true"
        NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

The Statment is dynamically generated inside a stored procedure.

My qustion is could the invalid characters be added by management studio in the xml view?

Or do i have incorrect escape sequences inside the generated sql


texts: MongoDB denying API access, while console shell works

texts:
I am a MongoDB newbie, with a MongoDB instance running on a Ubuntu machine.

If I login onto the machine's console and run mongo -u username -p password databasename I have permissions for the database databasename to create and drop collections, insert documents and remove them. I have successfully tested these operations.

However, if I try to insert a document (into the same database using the same user credentials) using the MongoDB API from a Java program (running on the same server), I get error 13:

com.mongodb.MongoCommandException: Command failed with error 13:
'not authorized on databasename to execute command { insert: "collectionName" ...

My Java code (works well when running on my local machine for another MongoDB instance without authentication):

...
ServerAddress serverAddress = new ServerAddress(serverName, portNo);
MongoCredential mongoCredential =
     MongoCredential.createCredential(username, DB_NAME, password.toCharArray());
mongoClient = new MongoClient(...
DB db = mongoClient.getDB("databasename");
mongoCollection = db.getCollection("collectionName");
this.mongoCollection.insert(...                          <-- CAUSES EXCEPTION
....

What could be the cause of this error, and how can I solve it?


texts: Tuning MySQL On Azure Windows Server Setup

texts:
I have a MySQL 5.5 running on Azure VM Windows Server 2012. Once per second the log db gets about 2K inserts through LOAD DATA INFILE... once a day I need to run stats and when the stats are crunching through the data, which involves creating tables to a 'tmp' database, the LOAD DATA INFILE times out. I'm already using disk striping to help with the write load, also the tmp database for stats is saved on a different disk but the timeouts are still occuring.

I should also mention the LOAD DATA INFILE is executed by another service which has a timeout of 60 seconds and which I have no control over other than starting and stopping it.

I have also tried including LOW_PRIORITY on all of the INSERT queries executed by the stats, but still no luck.


texts: Can I grant multiple remote LocalService accounts access to a SQL Server database?

texts:
I am designing a multi-environment Windows-based software product that will extensively employ Windows services running as LocalService to perform data access operations. All of the data access endpoints are .NET applications that use Entity Framework to access the databases.

I don't want to use SQL Server authentication if I can avoid it; I want to use Windows authentication everywhere. Currently there are no roles or users at the database level for any of the system's databases. My current plan is to grant SELECT/INSERT/UPDATE/EXECUTE privileges to the LocalService accounts associated with each of the service host environments.

First, is that approach even possible? After a dizzying amount of research, I can't make sense of how SQL Server will distinguish between the LocalService account on two remote environments.

Second, am I missing a serious security implication? I understand the principle that a role should be limited and narrow in scope. I also understand that any other applications on the servers running as LocalService -- including malware -- will have database access using the current plan. The challenge that I'm facing is that the product is licensed and will be deployed on customer site, and I'm not sure how to tell our customers that our software needs to commission its own domain accounts or, even worse, that they need to commission them.

If it makes the most sense, I will just abandon the idea of using LocalService to access databases and plan on creating purpose-specific service accounts. My expertise is stretched beyond its limit for this particular problem. Thanks in advance for sharing your thoughts.


texts: SELECT row based on range from columns in second table

texts:
I want to SELECT all rows from  first_table INNER JOIN second_table for values where the column in first_table fall within range of values from two columns in second_table.
As example, I have created these two tables
First table:
CREATE TABLE location (loc_id int, gpstime int, lat_start float, lat_end float);

Second table:
CREATE TABLE segments( segment_id int, lat float,foot float,bike float,bus float,car float,metro float);

Then insert these values:
INSERT INTO location
SELECT 1, 12345, 41.10, 41.17 UNION ALL
SELECT 2, 99999, 42.00, 42.12 UNION ALL
SELECT 3,12346, 41.35, 41.30

INSERT INTO segments
SELECT 1, 41.15, 0, 0, 0.95, 0.025, 0 UNION ALL
SELECT 2, 42.19, 0.95, 0, 0, 0, 0.025 UNION ALL
SELECT 3, 41.33,  0,0, 0, 0, 1

The result set I want should return all rows for which value of column lat in segments table is a value that exists between lat_start and lat_end columns of location table.
So I write the following query:
SELECT gpstime AS timestamp, lat_start,  lat_end,
CASE
    WHEN GREATEST(car, bike, bus, metro, foot) = car THEN 'car'
    WHEN GREATEST(car, bike, bus, metro, foot) =  bike THEN 'bike'
    WHEN GREATEST(car, bike, bus, metro, foot) =  bus THEN 'bus'
    WHEN GREATEST(car, bike, bus, metro, foot) =  metro THEN 'metro'
    WHEN GREATEST(car, bike, bus, metro, foot) =  foot THEN 'walking'
ELSE 'na' END AS mode
FROM location
INNER JOIN segments
ON  lat BETWEEN lat_start AND lat_end;

Problem: The BETWEEN key word returns TRUE only when check-value fall in range (min, max) and does  not evaluate to (max, min). Clearly, the value of lat in the "last" row of segments (41.33) is a value that fall within the range of (lat_start, lat_end) of location (41.30-41.35). However, the BETWEEN clause excludes this row, as per returned the table below:
+-----------+-----------+---------+------+
| timestamp | lat_start | lat_end | mode |
+-----------+-----------+---------+------+
|     12345 | 41.1      | 41.17   | bus  |
+-----------+-----------+---------+------+

How do I ensure all rows satisfying the criteria are returned?
Additional details
I posted this question yesterday, and following David answer, I was glad everything seems to be fixed. However, working on my database looks like there is still an error in the query as yet some rows are still missing. I reproduce minimal scenario of my work  once again:
Table 1:
CREATE TABLE location (id int, lat float, lon float, gpstime int);
Table 2:
CREATE TABLE trips( trip_id int, lat_start float, lat_end float,
lon_start float, lon_end float,
travel_mode text);

Add rows:
INSERT INTO location
SELECT 1, 41.2, 8.3, 11111 UNION ALL   --both lat and lon in the interval
SELECT 2, 43.3, 8.5, 22222 UNION ALL   --only lat meets criteria
SELECT 3, 44.0, 8.5,  33333            --only lon meets criteria

INSERT INTO trips
SELECT 1, 41.1, 41.9, 8.0, 8.8, 'foot' UNION ALL
SELECT 2, 43.1, 43.5, 9.5, 8.9, 'bus' UNION ALL
SELECT 3, 42.1, 42.8, 8.7, 8.0, 'metro'

And then the query, using BETWEEN SYMMETRIC:
SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location
INNER JOIN trips
    ON (lat BETWEEN SYMMETRIC lat_start AND lat_end)
    AND (lon BETWEEN SYMMETRIC lon_start AND lon_end)

Results:
+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       1 |     11111 | 41.2 | 8.3 | foot        |
+---------+-----------+------+-----+-------------+

Yet, row 3 of location table is not included in the result set. What I am missing here?
Note: Using BETWEEN SYMMETRIC doesn't solve the problem yet. It appears value-checking is only applied to lat column and NONE to lon.
Db<>fiddle
I share with you, this  db_fiddle  I created to test the query. Cleary, all rows of location table should be returned as rows 2 and 3 meet one criteria either.


texts: Schema Migration: SQL Server Data Tools vs Liquibase and Flyway

texts:
This might seem like a stupid question, but I've been looking into open source solutions for schema migration, namely Liquibase and Flyway.

However, my boss told me that SQL Server Data Tools (SSDT) achieves the same job.  I'm not sure if agree, but I can find very little on the Internet that directly compares it to Liquibase and/or Flyway.

My view is that SSDT is a development, data modelling and design tool for SQL Server and also supports schema comparison (and generating scripts thereof) and source control.  It tackles a different problem although there may be some overlap with Liquibase/Flyway in some aspects of schema migration.  But as an overall schema migration tool, Liquibase and Flyway are fully dedicated tools whereas SSDT is more for the design and development of a database.

Any opinions would be much appreciated even if it's just to say there's no comparison and SSDT is not a schema migration tool per se at all.


texts: Is there a way to skip errors in procedures and resuming procedure

texts:
When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again.

The procedure:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;

    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$

DELIMITER ;

I want to insert about 14000 records in a junction table, but the problem arise when there is a duplicate key for unique(iq_id,q_id)? what do do?


texts:
I usually design my databases following next rules:

Nobody else than db_owner and sysadmin have access to the database tables.
User roles are controlled at application layer. I usually use one db role to grant access to the views, stored procedures and functions, but in some cases, I add a second rule to protect some stored procedures.
I use TRIGGERS to initially validate critical information.

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,
               Field2 = <other_initial_value>
        ...
    END

DML is executed using stored procedures:

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

Do you think that, on this scenario, worth it to use DEFAULT CONSTRAINTs, or I'm adding an extra and unnecessary job to the DB server?

Update

I understand that by using DEFAULT constraint I'm giving more information to someone else that must to administer the database. But I'm mostly interested on performance.

I assume that the database is checking always default values, even if I supply the correct value, hence I'm doing the same job twice.

For example, Is there a way to avoid DEFAULT constraint within a trigger execution?


texts: How to create function(table_name) that returns a set of the table

texts:
I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as arguments and returns a set of the same table.

The query I need is a bit more complicated, but for my question the following example should be enough.

This is how I'm trying to create the function:

CREATE OR REPLACE FUNCTION "vsr_versioning_at_time"(_t regclass
                                                  , _d timestamp without time zone)
RETURNS SETOF _t AS
$$
BEGIN
    EXECUTE 'SELECT DISTINCT ON (gid) *
        FROM '|| _t ||
        'WHERE vrs_start_time <= '|| _d ||
        'ORDER BY gid, vrs_start_time DESC';
END
$$
LANGUAGE plpgsql;

The creation of this function fails since _t is not recognized by the RETURN SETOF _t.


texts: Enforcing option hint for a statement in a while loop is not possible

texts:
SQL Server 2014 Compatability level 110 uses old Cardinality Estimator.
I am trying to enforce new CE for a statement.
I am getting error "Incorrect syntax near the keyword 'option'."
I need to do deletions in batches.

while
(
    Select top (1) 1
    from [AdventureWorks].dbo.DatabaseLog
    option (querytraceon 2312)  --error
) > 0
begin
    delete top (10000 )
    from [AdventureWorks].dbo.DatabaseLog
end

-- it runs ok by itself:

Select top (1) 1
from [AdventureWorks].[Person].[Person]
option (querytraceon 2312)


texts:
Im trying to import values from a .csv file which has only one column. I keep getting all the values in just one row.

SELECT BulkColumn
FROM OPENROWSET (BULK 'C:\Temp\myfile.csv', SINGLE_CLOB) MyFile

Here is a sample of the first rows of the files content

Nummer
072XXXXXX63
072XXXXXX76
07XXXXXX66
072XXXXXX4


texts: I am trying to complete an assignment for my CIS class and I am required to include a data type called ENUM. I know that this stands for enumerated lists, but I am not sure when would be an appropriate time to use ENUM. A couple of the examples given in the book I am using were like a list of the continents. Could I use this to describe a list of departments for a workplace?


texts: Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?

texts:
We have installed dedicated SSD drives for TempDB

First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)

Does TempDB benefit from 64 Kb unit size, or it can be left with default 4 Kb ?

Second question - we have data and log files on a separate "virtual" volumes that SAN admin sliced before, those volumes are made from SAN RAID array, and show 4 Kb allocation unit size

We can not change allocation unit size for those volumes at this point.
So if we change TempDB SSD drive to 64 Kb, and data/log volumes stay as 4 Kb, will it cause any trouble for the SQL Server ?


texts: SQL Sentry Plan Explorer - Execution Mode in plan diagram?

texts:
Does Plan Explorer show the Execution Mode (estimated/actual) in the plan diagram when hovering over an operator? I'm not able to find it. I do see it in the Plan Tree and Top Operations pane.

Below is SSMS where it is shown vs Plan Explorer where it is not.


texts: Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

texts:
DBAs,
Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over?
When I hit debug (Alt-F5), gives me this error, after making some progress. Looks like once it hits the section of the code that calls another procedure, it gives this error.
Frame not in module.
The current stack frame was not found in a loaded module. Source cannot be shown for this location.

Example:
1  ..
2  ..
3  SELECT 1 FROM sys.objects
4  SELECT dbo.SomeFunction(1)
5  UPDATE TABLE SET X = 1 ....
6

It runs well, until line 3, once it hits line 4, where it is calling a different function, it throws the aboe error. In SSMS, that line just opens a new window with code for the function and show debugging progress for each F11 step-into -- but not in Visual Studio 2019 (Version 16.7.1)
Thanks


texts: Can I use RMAN to backup and restore data in a certain point in time?

texts:
I have an Oracle database over which I have full control. What I am doing is performing load testing on our application, which uses Oracle as a database. To do this, I initially set the database up with some dummy data. I'm calling this "Point A".

The load testing will consume this data, and I will have to restore the data so it looks exactly like it was in Point A before I can re-run them. The hard way to do this is using Data Pump, which takes a long time and seeing how I need to backup several schemas it's rather complex.

I was wondering if it was possible just to issue a BACKUP DATABASE on RMAN after I have the data set up. Then I would run my tests, and restore it using something like RESTORE DATABASE FROM TAG <somedate>, and that would be all I need to get my tests going again. There should be no changes to the control file or anything like that.

Is this a safe way to go about doing this?


texts: How can I tell if my SQL Server DB performance is hardware-limited?

texts: Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test data, used to be <30ms each, now it's 100-200ms, but there are several, so the delay is becoming apparent in the UI).


texts: How to check and change function volatility in Postgres?

texts:
I have a function that I think is defined as volatile, but I now know I want it stable.

How can I check the function volatility (volatile, stable, immutable) of a certain function?

When a function is defined as volatile, can I change the volatility to stable by using a create or replace function query? Or does the function needs to be dropped and created again but this time as stable?

create or replace function my_function()
returns int as $$
    select a
    from my_table
    order by 1
    limit 1
$$ language sql stable;

I checked the documentation for this, but it doesn't mention changing volatility.


texts: Moved a MySQL Database to another server, but socket keeps pointing to localhost

texts:
I know I shouldn't bother with this, maybe I should disable the old mysql, but this is a Magento virtual appliance from Bitnami thats work as a whole (application and database on the same server).

I had to follow the client's design, though, and take the mysql database to an Amazon RDS instance. (the magento/application server is an EC2 at amazon as well).

The application server has a script that starts up mysql, apache and some other stuff. In this process, it always tries to update the mysql database with any new data the server has (like DNS, hostname, IP Addresses). But since the database isn't there anymore, it fails on doing so.

I never found a way to change the configuration of mysql.sock. I would just reconfigure it and point to the new server with appropriate credentials but I can't find anywhere in the internet how to do so.

I could just forget the old mysql, but any changes I make will have to be manually updated in the remote server, and I fear this script is controlling a lot of other stuff I don't control.

In short, my issue is: mysql.sock points to wrong database. How could I change this?

Thanks for the interest!


texts:
I need to select data from 2 tables based on 1 filed UID and display some from both tables.

Data from first table "a.symbol, a.order_type, a.price_open, a.time_open, a.size", and data from second table "b.bid, b.point".

When i use INNER JOIN to get data from both tables i get many results, but there is only 5 results that i have data for, when i use it without joins as you can see on 1st image.

Ok here is an query i have problems with:

SELECT a.symbol, a.order_type, a.price_open, a.time_open, a.size, b.bid, b.point
FROM trade_log a
INNER JOIN currency_rates b ON a.uid = b.uid
WHERE a.uid = '23' AND a.status = 'Open' AND a.order_type IN ('0', '1')
ORDER BY a.time_open ASC

So this is a data i get when i use query without inner join, from trade_log table

This i a data what i'm getting now with inner join, but i need just to add "bid" and "point" to table like on this upper image, but i don't know why he multiply results.

And this is a data from "currency_rates" where UID is 23


texts:
I am running the following stored procedure to add a merge replication join filter,

sp_addmergefilter

What else does that stored procedure do behind the scenes?

I am calling that stored procedure and in one case it is timing out after 10 minutes. The join filter is this for the two tables DispatchLink and Dispatch,

Dispatch.DispatchId = DispatchLink.DispatchLink1 and Dispatch.ContractId = DispatchLink.ContractLink1
or Dispatch.DispatchId = DispatchLink.DispatchLink2 and Dispatch.ContractId = DispatchLink.ContractLink2

The structure for those two tables is explained here,

How to Optimise Query

If I remove all the stuff after the 'or' it runs fast.


texts: Enabling SQL Server authentication for restored database user

texts:
I've restored a remote database to my local, and it contains some users which are used to execute jobs.

We are trying to execute the job and it is seems to return a connection error (connection to the same database).

It is using a proxy user to run CMD EXEC commands, and we've checked the final user behind the proxy, turns out it seems that this user is not allowed to login to the SQL Server.

We've entered with SA account and taken ownership of the database, however, we are unable to change the user setting.

Please advise.


texts:
I have a DB for which DBCC CHECKDB produces the output with following lines among others:

Table error: Object ID 437576597, index ID 1, partition ID 72057594473545728, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:9689715), slot 4, text ID 11927732027392 does not match its reference from page (1:9697074), slot 2.
Table error: Object ID 437576597, index ID 1, partition ID 72057594473545728, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:9689715), slot 4, text ID 0 is pointed to by page (1:9689715), slot 5 and by page (1:9697074), slot 2.

So I go and check the entries at (1:9689715), slot 5 and (1:9697074), slot 2. First one seem to be correct, second one is not. I want to find table and row which references it. As far as I understand for that I need to find another page (and slot) that references the incorrect one and keep backtracking until the slot contains row data. Question is how do I do that backtracking?

DBCC PAGE output for page 1:9697074 contains following under slot 2:

Blob row at: Page (1:9697074) Slot 2 Length: 98 Type: 5 (LARGE_ROOT_YUKON)

Version Infomation: Transaction Timestamp  (0)
Version Pointer (Null)

Blob Id: 9242676756480 Level: 0 MaxLinks: 5 CurLinks: 1

    Child 0 at Page (1:9689715) Slot 4 Size: 1072 Offset: 1072

I don't need to recover anything at this point, just find the affected row(s).


texts: data warehouse automation tool: benefit or bane?

texts:
Today while investigating ETL and data warehouse solutions like Kettle, I came across a new concept (for me) called data warehouse automation tool.

I also looked at the white paper here, which indicates there are few vendors in this space.

One reaction I have to these tools is: whoa, anyone purchasing one of these tools is making an enormous investment. At the same time, it seems many data warehouse projects fail, even though they take a significant amount of developer hours to implement.

So, if anyone out there does have experience with a data warehouse automation tool in a real-world project, would you be able to answer these questions?

Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?
If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?
If you have done data warehousing in the past without a DWA tool, and you have also used a DWA tool recently, would you use a DWA tool again for your next data warehouse project?
When would you see a DWA tool as being overkill?
What did you like best about using a DWA tool? What did you like least?


texts: The use of multiple foreign keys on same column in SQL Server

texts: SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?