how to change the file directory of your log and data files and what are the effects I have inherited a SQL server 2012 instance where the file directories of the log and data files are on the same folder on the same drive(all are in the C:\ drive). My task that I will be doing is separating the data files from the OS disk. The instance already has few databases on it running.Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this.

how to change the file directory of your log and data files and what are the effects

  Can I just change the settings of the instance to point it to the new
  directory, detach the existing databases move the data files and
  attach it back? Please share what would be the best approach in doing
  this.

Below are two scripts I've used for this purpose quite a few times in the past; just adjust accordingly for your environment.

Basically just plug in the new path locations, the data and log file names, and the logical SQL data and log file names, and then run the first script (see comment above second script below).

First Script

Plug in your variable accordingly for your environment.

ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DBName> SET OFFLINE

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalDataFileName1>
    ,Filename = 'T:\MSSQL\Data\<DBName>.mdf'
    );

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalDataFileName2>
    ,Filename = 'T:\MSSQL\Data\<DBName1>.ndf'
    );

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalLogFileName2>
    ,Filename = 'Y:\MSSQL\Logs\<DBName>.ldf'
    );

Second Script

Before you run the below TSQL, you will need to move the applicable physical data and log files to the new locations as you plugged into the first script.

--Now move the files to the new location where you pointed them to before running the below.

ALTER DATABASE <DBName> SET ONLINE
ALTER DATABASE <DBName> SET MULTI_USER


Select arbitrary amount of rows from child table and join them into parent table as columns Good evening StackExchange DBA's. I've been asked to pull pull back every piece of information from on of our machines and export it. The problem in is, all of the values in question are spread between a few tables that have a column structure like (parent_id BIGINT, attribute VARCHAR(40), val INT).

Here's a simplified idea of what our database structure looks like.

create table parent (id BIGINT, name VARCHAR(45));
create table child (parent_id BIGINT, attribute VARCHAR(10), val INT);

INSERT INTO parent (id, name) VALUES (1, 'Adam');
INSERT INTO parent (id, name) VALUES (2, 'Brent');
INSERT INTO parent (id, name) VALUES (3, 'Cathy');

INSERT INTO child (parent_id, attribute, val) VALUES (1, 'height', 67);
INSERT INTO child (parent_id, attribute, val) VALUES (1, 'weight', 185);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'height', 63);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'weight', 162);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'arms', 2);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'height', 71);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'weight', 179);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'arms', 2);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'legs', 2);

Ideally, I'd like to return all the values and attributes of a parent in the same row but each parent will have vastly different attributes as it's children.

This is the closest I've come to what I'd like to see. Is there any improvement to be had in this query? Does anybody know what these types of queries are called so I know what to google?

select a.id, a.name, b.attr, b.val
from parent a INNER JOIN
  (select distinct parent_id,
   GROUP_CONCAT(attribute) as attr,
   GROUP_CONCAT(val) as val
   from child group by parent_id) b
ON a.id = b.parent_id;

Thanks

Try this:

SELECT
    parent.id,
    parent.name,
    GROUP_CONCAT(CONCAT(child.attribute, ":", child.val)) AS Attributes
FROM parent INNER JOIN child ON parent.id=child.parent_id
GROUP BY parent.id


Build a histogram of road speed based on road length by grouping road segments A data set look like this:

The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID
A roadID (e.i. Lake Street) belongs to a roadType (e.i. St) and consists of segments (e.i. segID is 2005 for a given road block).

I want to plot average speeds of roads grouped by road lengths further grouped by road types like shown in example output below.

How to add columns for each roadType, within each roadType group roads into bins, within each bin compute average value of maxSpeed?

I'm using PL/SQL. I can translate a query from MySQL or PostgreSQL if necessary.

Maybe the following ideas will give you a starting point for developing your queries.  We need some test data first.  The last SEGID in your question is: 4007.  In the example, we are using a table with 4500 random SEGLENGTHs, and IDs, maxspeeds etc similar to the ones you have described (in your sample table).

TEST table and data (Oracle 12c)

create table test (
  segid number generated always as identity primary key
, roadid number
, roadtype varchar2( 64 )
, seglength number ( 10, 1 )
, maxspeed number
, check ( maxspeed between 0 and 100 )
);

insert into test( roadid, roadtype, seglength, maxspeed )
select
  roadid
, case
    when roadid < 200 then 'Ave'
    when roadid between 200 and 400 then 'Hwy'
    when roadid between 401 and 700 then 'St'
    when roadid between 701 and 900 then 'Rd'
  end roadtype
, round( dbms_random.value() * 10, 2 )
, case
    when roadid < 200 then ( mod( roadid, 4 ) + 1 ) * 10
    when roadid between 200 and 400 then ( mod( roadid, 7 ) + 1 ) * 10
    when roadid between 401 and 700 then ( mod( roadid, 3 ) + 1 ) * 10
    when roadid between 701 and 900 then ( mod( roadid, 6 ) + 1 ) * 10
  end maxspeed
from
  ( select level roadid from dual connect by level <= 900 )
, ( select level segcount from dual connect by level <= 5 )
;

TEST table: first 5 rows, last 5 rows

SQL> select * from test order by segid fetch first 5 rows only;
SEGID  ROADID  ROADTYPE  SEGLENGTH  MAXSPEED
1      1       Ave       1.6        20
2      1       Ave       7.5        20
3      1       Ave       3.8        20
4      1       Ave       9.2        20
5      1       Ave       2.8        20

SQL> select * from test order by segid offset 4495 rows fetch next 5 rows only;
SEGID  ROADID  ROADTYPE  SEGLENGTH  MAXSPEED
4496   900     Rd        4.2        10
4497   900     Rd        4.2        10
4498   900     Rd        0.7        10
4499   900     Rd        7.1        10
4500   900     Rd        2          10

For dividing the SEGLENGTH into "buckets", you could use WIDTH_BUCKET() (see documentation), and use GROUP BY to find the average speed for each "bucket".

  select
    roadtype, avg( maxspeed ), max( seglength ), wb
  from
  (
      select roadtype, maxspeed, seglength
      , width_bucket(
          seglength
        , ( select min( seglength ) from test )
        , ( select max( seglength ) from test ) + 1  -- upper bucket count: exclusive!
        , 5
       ) wb
      from test
    )
  group by wb, roadtype

-- result
ROADTYPE  AVG(MAXSPEED)                              MAX(SEGLENGTH)  WB
Hwy       39.24170616113744075829383886255924170616  8.7             4
Hwy       40.70866141732283464566929133858267716535  10              5
St        18.91304347826086956521739130434782608696  8.7             4
St        19.81132075471698113207547169811320754717  10              5
Rd        34.70588235294117647058823529411764705882  6.5             3
Ave       24.49275362318840579710144927536231884058  10              5
Hwy       41.76744186046511627906976744186046511628  6.5             3
Ave       25.52995391705069124423963133640552995392  2.1             1
Ave       24.0796019900497512437810945273631840796   8.7             4
Ave       25.27272727272727272727272727272727272727  4.3             2
St        20.50632911392405063291139240506329113924  2.1             1
Rd        34.51754385964912280701754385964912280702  8.7             4
Rd        35.11811023622047244094488188976377952756  10              5
Hwy       38.68312757201646090534979423868312757202  4.3             2
Rd        35.26315789473684210526315789473684210526  2.1             1
Rd        35.52995391705069124423963133640552995392  4.3             2
Ave       25.70776255707762557077625570776255707763  6.5             3
Hwy       40.28708133971291866028708133971291866029  2.1             1
St        21.03658536585365853658536585365853658537  4.3             2
St        19.65838509316770186335403726708074534161  6.5             3

Add a PIVOT() to this query, in order to convert the ROADTYPES into columns, and use ROUND() or TRUNC() for obtaining the final values.

select msl, ave, hwy, st, rd
from (
  select
    roadtype, trunc( avg( maxspeed ) ) ams, max( seglength ) msl, wb
  from
  (
    select roadtype, maxspeed, seglength
    , width_bucket(
        seglength
      , ( select min( seglength ) from test )
      , ( select max( seglength ) from test ) + 1  -- upper bucket count: exclusive!
      , 5
     ) wb
    from test
  )
  group by wb, roadtype
) pivot  (
    avg( ams ) for roadtype in (  -- pivot() requires an aggregate function
      'Ave' as ave
    , 'Hwy' as hwy
    , 'St'  as st
    , 'Rd'  as rd
   )
) P
order by wb
;

-- result
       MSL        AVE        HWY         ST         RD
---------- ---------- ---------- ---------- ----------
       2.1         25         40         20         35
       4.3         25         38         21         35
       6.5         25         41         19         34
       8.7         24         39         18         34
        10         24         40         19         35

For getting the standard deviation values, just use STDDEV() instead of AVG().


How does MySQL evict pages from the buffer pool? I am trying to understand the internals of MySQL. I do know that MySQL's buffer pool management has an old/young page list as shows here. And I do know about the flushing of old dirty pages, such as explained here and how to customize it here. I am also aware that MySQL uses a LRU algorithm for page eviction as here.

However, is there a buffer pool daemon/thread that evicts read only pages (not dirty pages) that were brought to the buffer pool by say a select statement? Are there ways to customize it (for example, when I pass 95% of the bufferpool capacity, start evicting read pages or flush dirty pages). In other words, what triggers in the LRU algorithm evict pages from the buffer pool (e.g. time a page is on buffer pool, percentage of buffer pool filled, need to evict pages for new pages to load), are these adjustable?

Flushing and evicting are entirely different.

https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_flush

MySQL will flush dirty pages in accordance with innodb_max_dirty_pages_pct. The default database page size default in InnoDB is 16KB and the maximum setting for innodb_max_dirty_pages_pct is 99 so there isn't a whole lot of tolerance for the build up of dirty pages (although, as you know from the link you cite the actual flushing schedule calculation is a bit more complicated).

After they are flushed they will then be evicted when they are the LRU.

The documentation states, "When room is needed to add a new page to the buffer pool". So I would imagine it waits until it is pretty close to full. Regarding the characteristics of LRU in general, you can read up on that elsewhere as it is not MySQL specific. MySQL Does however use the "Midpoint insertion strategy" which makes things not a true LRU in order to deprioritize superfluous pages.

https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_midpoint_insertion_strategy


How is it possible that this "sub-calculation" of the "main calculation" actually gives a HIGHER value? I do this to calculate the average food expenses per month:
    SELECT avg(amount)
    FROM
    (
        SELECT date_trunc('month', "timestamp"), SUM("amount") AS amount
        FROM bookkeping WHERE category = 'Food'
        AND "timestamp" >= now() - INTERVAL '6 months'
        GROUP BY date_trunc('month', "timestamp")
    ) a

Then I do this, to see how much of that is from food orders online:
    SELECT avg(amount)
    FROM
    (
        SELECT date_trunc('month', "timestamp"), SUM("amount") AS amount
        FROM bookkeping WHERE category = 'Food'
        AND "is online" = true
        AND "timestamp" >= now() - INTERVAL '6 months'
        GROUP BY date_trunc('month', "timestamp")
    ) a

The second query shockingly gives a HIGHER value than the first, even though it's obviously a sub-post of the first.
How can this be possible? How can something which introduces more ANDs still give a higher value? I've stared at this code and tried numerous things; it's not a hallucination. I must be missing something fundamental. The queries cannot be calculating what I think they do.
I've relied on the first query (and many similar to it) for a long time for my entire economy, so it really would be bad if this turns out to be fundamentally wrong...
(The reason I don't just do SELECT avg(amount) directly is that then this becomes the average cost per purchase instead of the average cost per month.)

In the visual table in my GUI, I have it so that it shows "Food: blablabla/month", then underneath a list of each "type" of food, so each of those "sub-groups" cannot have a number higher than the "parent".
I guess my brain fundamentally cannot understand how an average (or any number) can be higher for a sub-group of a bigger set which yields a smaller number.

Imagine you keep track of all your purchases, from chocolate bars to houses and cars, over your entire lifetime. Maybe you buy 5,000 chocolate bars at prices varying between $1 each and $2 each.  For an average price of, for the sake of this example, $1.47.  Then you buy a car, priced at $40,000.  Now, the average price of the chocolate bars hasn't changed, but if you add in the car to that set, and recalculate the average you'll be at an average of something like $2.26.  That skew of outsized members of a set is why statisticians occasionally employ standard deviation to get rid of outliers.  In this example, if you remove any purchase from the set that has a price higher than 3 standard deviations from the mean, you return the average of the set back down to $1.47.
Your question shows that have two different sets of numbers that you're combining into a single larger set, eating in a restaurant, and getting delivery.  Imagine this is the set of purchases made in a restaurant:
╔═════╗
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 189 ║
║ 134 ║
║ 190 ║
║ 144 ║
║ 157 ║
║ 102 ║
║ 186 ║
╚═════╝
The average for that set of purchases is $140.58
Now, lets take the set of purchases for delivery meals:
╔═════╗
║ 289 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 190 ║
║ 232 ║
║ 287 ║
║ 200 ║
║ 270 ║
║ 186 ║
╚═════╝
The average for that set is $239.27.
If, however, you combine the sets together, as in:
╔═════╗
║ 289 ║
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 134 ║
║ 190 ║
║ 232 ║
║ 144 ║
║ 157 ║
║ 287 ║
║ 102 ║
║ 200 ║
║ 270 ║
║ 186 ║
║ 289 ║
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 134 ║
║ 190 ║
║ 232 ║
║ 144 ║
║ 157 ║
║ 287 ║
║ 102 ║
║ 200 ║
║ 270 ║
║ 186 ║
╚═════╝
The average is $187.70, which as you can see is less than the average for eating out, and higher than the average for eating in a restaurant.


Dedicated network for MySQL replication I am setting up a mysql master-master GTID replication. I configured all prerequisites and my current replication config looks like
Node 1:
CHANGE MASTER TO MASTER_HOST = 'NODE2_IP',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = 'password',MASTER_AUTO_POSITION = 1;

Node 2:
CHANGE MASTER TO MASTER_HOST = 'NODE1_IP',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = 'password',MASTER_AUTO_POSITION = 1;

Is it possible to use a dedicated network for replication traffic. What are the prerequisites for that and what are the changes required from mysql replication perspective.
Thanks in advance

Found out the option
MASTER_BIND is for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master
Thanks


How do I get the PostgreSQL query execution time in PHP using the pg_* functions? I've spent hours on this page now, looking through everything: https://www.php.net/manual/en/ref.pgsql.php
I can't figure out how to get the "query execution time" from a pg_query (and related query execution functions).
I'm not talking about measuring the time before and after the query in the PHP code, but to get the actual "query execution time" (in milliseconds or similar) of the query, which could be slightly different from the time before and after the function was called due to various reasons.
I'm 99% sure that both PostgreSQL and all other SQL database softwares send this information back for each query somehow, but how? It has a bunch of "statuses", but not the time the query took to run from what I can tell.

I'm 99% sure that both PostgreSQL and all other SQL database softwares send this information back for each query somehow, but how?

It doesn't. PHP's pg_query uses the simple query protocol and pg_query_params the extended query protocol, and in both cases what the client gets back for successful queries is essentially a CommandComplete message, where the only metadata available is a command tag saying:

what type of query it was
how many rows where affected by the query.

If there was a server-side execution time, it would be there, and it's not.
From the documentation:

CommandComplete (B)
Byte1('C')
Identifies the message as a command-completed response.
Int32
Length of message contents in bytes, including self.
String
The command tag. This is usually a single word that identifies which SQL command was completed.
For an INSERT command, the tag is INSERT oid rows, where rows is the number of rows inserted. oid used to be the object ID of the
inserted row if rows was 1 and the target table had OIDs, but OIDs
system columns are not supported anymore; therefore oid is always 0.
For a DELETE command, the tag is DELETE rows where rows is the number of rows deleted.
For an UPDATE command, the tag is UPDATE rows where rows is the number of rows updated.
For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved.
or a MOVE command, the tag is MOVE rows where rows is the number of rows the cursor's position has been changed by.
For a FETCH command, the tag is FETCH rows where rows is the number of rows that have been retrieved from the cursor.
For a COPY command, the tag is COPY rows where rows is the number of rows copied. (Note: the row count appears only in PostgreSQL
8.2 and later.)


Query to search for a substring in xml I use below query to search for a substring in whole xml(including node name and node value)

SELECT *
FROM tablename
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )

I want an alternative query which has good performance than this. So please suggest some.
Below are details:
Table:

CREATE TABLE [dbo].[tablename](
    [Sl_no] [int] NOT NULL,
    [Date] [date] NULL,
    [Operation] [nvarchar](max) NULL,
    [Allot] [nvarchar](50) NULL,
    **[xmlcolumn]** [xml] NULL,
    [By] [nvarchar](255) NULL,
    [Dept] [nvarchar](255) NULL,
    [Db] [varchar](255) NULL,
    [tabl] [varchar](255) NULL,
    [Remark] [varchar](5000) NULL,
    [Work] [int] NULL,
    [F2] [nvarchar](max) NULL,
    [F6] [nvarchar](max) NULL,
    [F5] [nvarchar](max) NULL,
    [F8] [nvarchar](max) NULL,
    [ListC] [nvarchar](255) NULL,
    [pro] [nvarchar](max) NULL,
    [Completed] [varchar](50) NULL,
    [WorkTime] [xml] NULL,
    [RelatedData] [varchar](255) NULL,
    [User] [xml] NULL,
    [TeBy] [xml] NULL,
    [Date1] [nvarchar](50) NULL,
    [Num1] [nvarchar](50) NULL,
 CONSTRAINT [PK_DBChanges] PRIMARY KEY CLUSTERED
(
    [Sl_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Number of rows: 3000
Sample xml in a column:(number of nodes may change little bit.

<Root>
  <Row>
    <User>abs</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>xra</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>

Type of search expected:To search for a substring within xml column 'xmlcolumn' and then return the full row containing it.

So the query i used is

SELECT *
FROM tablename
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )

The IO for the query by using set statistics time on

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 93 ms.

I tried with another query(But it will search only in the node value)

select *
from tablename
where xmlcolumn.exist('//*/text()[contains(., "abc")]') = 1

and statistics output was

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 109 ms.

To know what performance you will have you have to test on your data. I obviously can't do that so I made up my own xml data to test the two queries you have in this question.

Create a table with 5000 rows containing an XML document of 9475 characters in 415 nodes:

create table T
(
  ID int identity primary key,
  XMLCol xml not null
)

declare @X xml =
(
  select top 100 *
  from master..spt_values
  for xml path('row'), root('root'), type
)

insert into T(XMLCol)
select top(5000) @X
from master..spt_values as m1, master..spt_values as m2

Execute the queries to search for a value that is present in the first node (rpc) and another value that is present in the last node (SERVER ROLE).

select count(*)
from T
where charindex('rpc',cast(xmlcol as varchar(max))) > 0

select count(*)
from T
where XMLCol.exist('//*/text()[contains(., "rpc")]') = 1

select count(*)
from T
where charindex('SERVER ROLE',cast(xmlcol as varchar(max))) > 0

select count(*)
from T
where XMLCol.exist('//*/text()[contains(., "SERVER ROLE")]') = 1

The IO for the different queries is the same so here is the output from using set statistics time on

Search for rpc with charindex:

 SQL Server Execution Times:
   CPU time = 1435 ms,  elapsed time = 1434 ms.

Search for rpc with xml exist

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 68 ms.

Search for SERVER ROLE with charindex

 SQL Server Execution Times:
   CPU time = 7316 ms,  elapsed time = 7321 ms.

Search for SERVER ROLE with xml exist

 SQL Server Execution Times:
   CPU time = 3245 ms,  elapsed time = 3244 ms.

Clear winner in both cases is the XML query. It does a better job of scanning the entire XML and it does a much better job of early termination when the search string is found.

This is true for the test data above using SQL Server 2012. It could be different for you with your data and your search strings. You have to test to know what is best for you.

NOTE: As stated in the answer to your other question, the two queries above will not return the same result bucause the XML query only search node values where the charindex query searches the entire XML document including nodenames and markup.


Why does TempDB spill happen even though statistics are correct? I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get any help from here.

Question: How much data is spilled into disk? 400 MB or 60 MB(7643KB*8)?

in the article he states:

And no matter how many times I update statistics, I’ll still get a
~400MB spill to disk.

I am kinda confused here(

Question: If everything is okay with estimates, stats are up to date, box has sufficient memory, and no queries were running at that time, then why does spill to disk happen?

look at the estimated number of rows versus the actual number of rows.
They’re identical. The stats are fine.
I’m not using a small server, either: my virtual machine has 32GB RAM, and I’ve allocated 28GB of that to SQL Server. There are no other queries running at the same time – it’s just one lonely query, spilling to disk...

As the tooltip shows, 7643 8KB pages are spilled = 59.71MB. The rest of the sort occurs within the allocated memory.
The sort spills for two reasons:

The sort is parallel. The accurate memory estimate is split evenly among the sort threads, and cannot be dynamically reallocated between threads at runtime. If the rows are not distributed evenly, one or more sort threads may spill. This is somewhat more likely to happen at low DOP e.g. 4 rather than 8.

SQL Server does not account for spill management overhead when estimating the memory requirement. This is detailed in FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct.

On SQL Server 2019, the memory granted between executions may be automatically adjusted to avoid the spill via the row mode memory grant feedback mechanism.


What exactly is INTERVAL 1 HOUR checking? MariaDB 10.1.x

Let's say I have a query like:

select FOO from db.BLAH where STAMPFOO > NOW() - INTERVAL 1 HOUR

If I run that query at 12:15 PM, will I only get records that have a date value greater than 11:15 AM?  Or greater than 11:00 AM?

Since the WHERE clause says NOW(), the expression STAMPFOO > NOW() - INTERVAL 1 HOUR makes this a dynamic query. It will return all records whose timestamp is greater than one hour ago, down to the very second.

If you run this at 2017-02-02 12:15:27, your query will return all FOO values  from 2017-02-02 11:15:27.


What are the advanvates and disadvantages of vacuum in postgresql? I'm new to PostgreSQL, I know about the concept of vacuum in database.

But I want to know the major advantages and disadvantages of vacuuming process.
Also I want to know:

What happen if I access the table while vacuuming full database?
Whether it will affect the table, or cause data loss?

Vacuum is an inherent part of Postgres due to the MVCC model - you don't have much choice, it's there.

As for vacuum full - it rewrites the table and takes an exclusive lock in the process- you won't be able to access your table.

It sounds bad but recent releases reduced the need for vacuum full dramatically.
Frequent regular vacuum (using the autovacuum for example) should be enough in most cases.
Just make sure to monitor your vacuuming and DB for bloat.


Query: All employee with sum of salary > 500 In my MYSQL Database COMPANY, I have a Table: Worker :

mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SSN    | varchar(64) | NO   |     | NULL    |       |
| name   | varchar(64) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I created using following MySQL's command:

CREATE TABLE `Worker` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table having following data:

mysql> SELECT * FROM `Worker` ORDER BY salary ASC;
+-----+---------+--------+
| SSN | name    | salary |
+-----+---------+--------+
| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |
| 2   | Rahul   |    300 |
| 4   | Harsh   |    500 |
| 7   | THREE   |   1000 |
+-----+---------+--------+
7 rows in set (0.00 sec)

its insert query:

INSERT INTO `Worker`
    (SSN, name, salary)
  VALUES
    ("1", "Grijesh", 200),
    ('2','Rahul',300),
    ('3','Sumit',250),
    ('4','Harsh',500),
    ('5','ONE','100'),
    ('6','TWO',50),
    ('7','THREE',1000);

[QUESTION]

I wants to select first n workers (in asc list)those sum of salary is just more than 500 (> 500). for example:

following four workers are first low salary employees and having SUM of salary 600. (600 > 500)

| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |

I need something like SELECT *  FROMWorkerWHERE SUM(salary) < 501 ASC;  (its incorrect query)

An assumption can be made that all having different salary.

its something like cost cutting: remove employee those are relatively unimportant

A suggestion or link to read will be helpful

After playing around with this some more, I came up with this query:

SELECT ssn, name, salary FROM (
  SELECT ssn, name, salary, (@total:=@total+salary) as total
  FROM worker, (select @total:=0) t
  ORDER BY salary ASC
) as foo
WHERE (total-salary) <=500

This seems to do what you want on this limited data, but could be very slow on large datasets.


Select the top 5 records and the specific column from each table in the same database I have 50+ tables in a database.

I don't want to use the following SQL on each table in a database.

select top 10 * from {table_name}

So, is it possible to display the first 10 records PLUS only show the 2nd and 3rd column for each table inside the same database using sql?

(If the table does not have the 2 columns - it will display the 1st and 2nd column. If the table have 1 column, it will display the 1st column. If the table have more than 2 column, it will display the 2nd and 3rd column. If the table does not have any column, then display nothing.)

This will work on SQL Server 2005 and up:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'

SELECT TOP (10) ''' + REPLACE(name, '''','') + ''','
  + STUFF(
    (SELECT TOP (3) ', ' + name FROM sys.columns
      WHERE [object_id] = t.[object_id]
      ORDER BY column_id
      FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'
  ), 1, 2, '') + '
    FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t;

PRINT @sql;
--EXEC sp_executesql @sql;

When you're satisfied with the PRINT output (note that it will truncate at 8K even though the command should be a lot larger than that for 50+ tables), uncomment the EXEC.


SQL Server Replication - Can destination db have stored procedures that source db lacks? I'm a developer with a very basic question, but I can't see anything conclusive on MSDN. I'm developing a web application that uses a SQL server 2008 R2 back end. According to this MSDN page, SQL replication can be set up to replicate only certain database objects.

If stored procedures, functions, etc. are created on the destination database (the subscriber in replication parlance) will they survive future replications from the source database? I only need the data from the source system, so the replication need not publish anything but tables.

Yes, absolutely.

You would typically use transactional replication to move data between the publisher and subscriber(s) (although you can include other objects as well). Your subscriber (destination) database is fully writeable, and you can create stored procedures there, add indexes to replicated tables, and even create entirely new tables.

Be aware, you can also delete data from the replicated tables on the subscriber, which could lead to replication breaking and you having to re-snapshot.


Database Mirroring failover with CNAME I have SERVERA and SERVERB configured for Database Mirroring starting with A as Principal and B as Mirror. I created a CNAME for SERVERA and use that in my connection string. I am not using the FailoverPartner attribute of the connection string, partly because I suspect some of my applications will not support it and partly because I'm trying to learn about CNAMEs.

My expectation upon failover from A to B was that: 1. connections would fail 2. I would need to change the CNAME to point to SERVERB. 3. I would have to ipconfig /flushdns on my clients. 4. Then all would be good.

Instead, I didn't have to do any of that. My application automagically started going to SERVERB, even though my CNAME is pointing to SERVERA. I've read that the connection string will obtain the mirror instance from SQL and that will supersede the FailoverPartner attribute of the connection string (and the CNAME, I guess), but those were all posts about when the FailoverPartner attribute was being used. Again, my connection string is not using that attribute.

Can someone explain or point me to a resource that indicates how my app knew to got to SERVERB even though the CNAME being used in the connection string pointed to SERVERA?

Setup is Windows 2008R2 / SQL 2008R2

This is by design.  In the situation where your client is able to connect to the primary server but the database has been failed over to the secondary server, the primary server will inform the client of this.  Because the server has no awareness of the CNAME you're using it will use the information it has from the mirroring configuration to direct the client to the current location of the database.

Where your CNAME would come into play would be in a situation where the primary server is no-longer contactable.  The client will then need to time out on contacting the primary before attempting to connect to the secondary server configured in the connection string.

The following page from MS describes the behavior...
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/database-mirroring-in-sql-server


What are the best practices for ETL of data from one OLTP system to another, Part 2? Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, they have to deal with ETL. This is because as a developer I am seeking knowledge from those much closer to databases than I am. Also I didn't want to muddy the waters on the question I originally asked, since I did get an answer to that question. Hence, my "part 2".

With that said, I am probably going to be using custom Java code per high level design review with the architects and other key stakeholders. Now I am on to the detailed design for the ETL process. What I am wondering is what is the best way for me to insert/update the data in the target database?

Should I just use standard code looping techniques and insert/update record-by-record as I process them? Or is it better to generate a .sql flat file of all changes and then execute that at the end? Since I need to acquire target DB record keys for a key mapping database for my ETL I'm not sure the second choice is the best idea?

Any ideas? Guidelines? Help? What are the pros and cons of each? Are there any requirements (functional or non) that help you decide which option? Any help here with me understanding what I'm up against is appreciated.

Relational database guys always like to think in "sets" but some tasks just don't work that way.  With ETL, since there is no way around the physical disk having to actually write data you just want to reduce the overhead related to each individual query as much as possible.

Speaking for MSSQL.  If you use "parameterized" queries.  Looping in code and updating in that fashion should be fine.  The reason for this is that by using a parameterized query, the SQL engine can cache a query plan to use for each iteration of your loop and avoid the overhead of starting from scratch each time.

Google parameterized query if you are unfamiliar with that term.  Speaking in Java terms you can also search for Prepared Statements.

I say "should be fine" in my previous post.... assuming that the query you are going to run repeatedly is as well written and efficient as possible.

If you are able to analyze a sample query that is going to be run repeatedly and find out what it's resulting query plan is.  You may find there are indexes that aren't necessary that can be turned off in order to improve the performance of the update.

Or, on the flip side, if your update includes logic that causes a table scan on a related table you may find the addition of an index would greatly improve the performance of your update.


Update linked server table based on local table i'm trying to update a remote table using linked server, based on id of my local table.

I'm trying this:

update [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
                set [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.cod = tabela1.codigo
                from [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
                    join tabela1
                    on tabela1.id=[HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.id

  The multi-part identifier
  "HQSQLHOM01\SQLHOM01.dba_teste.dbo.tabela2.id" could not be bound.

i tried to update the local server based on the linked server but I still receive errors like the TABLE_NAME could not be bound.

update tabela1
        set tabela1 .cod=[HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.cod
            from [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
            join tabela1
            on [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.id=tabela1.id

  Msg 4104, Level 16, State 1, Line 40 The multi-part identifier
  "HQSQLHOM01\SQLHOM01.dba_teste.dbo.tabela2.cod" could not be bound.

There's a lot of questions like this one on stackoverflow but none of them is working.

tabela1=local

You cant have 5 identifiers, this should do the trick

UPDATE T2
SET    T2.cod = T1.codigo
FROM   [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2 AS T2
JOIN   tabela1 AS T1
       ON T1.id = T2.id

https://technet.microsoft.com/en-us/library/ms190406(v=sql.105).aspx


generating scripts - Incorrect syntax near 'GO' this is a partial view of my script to generate the drop index for a particular index

   ,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) +
   + CHAR(13) + ' ON '  +
    QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name) + CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

FROM sys.indexes I
INNER JOIN (
              SELECT  Object_id
                     ,Schema_id
                     ,NAME
                     FROM sys.tables

              UNION ALL

              SELECT  Object_id
                     ,Schema_id
                     ,NAME
                     FROM sys.views

            ) T

           ON T.Object_id = I.Object_id

 INNER JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid

the question is:

when I use this:

+ CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

It works - generates this script:

DROP INDEX [IDX_ProdImages_GetProductListingPageDenormalisedData]
 ON [dbo].[ProductImages]

GO

when I don't use the + CHAR(10) + CHAR(13) +

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

is there any other way to get this done?

The problem is that you are mixing up which character is which in the variations of a newline: CRLF / LF. You have:

,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) +
   + CHAR(13) + ' ON '  +
QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name)
 + CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

Here you use either CHAR(13) or CHAR(10) + CHAR(13). The problem is that CHAR(13) is a "Carriage Return" / "CR" while CHAR(10) is a "Line Feed" / "LF". You have reversed them and hence are using both "CR" and "LFCR" in various places. If you swap the "10"s for the "13"s it would work correctly.

Better yet would be having consistency across them. Either use just CHAR(10) everywhere, or CHAR(13) + CHAR(10) everywhere.


How long does sp_blitz take to execute? I'm trying to run sp_blitz on SQL Server 2014 and it seems to be taking a long time. What is the normal length?

We only have around 30 databases. One is 300GB, but it's going on for 15 min.

Can be anywhere from 5 seconds to 15 minutes depending on your server, workloads, databases, etc. If you have thousands of databases, for example, you can check out the documentation and use switches like @CheckUserDatabaseObjects = 0 to go faster by skipping stuff.


Is there any benefit to having both an ordered and unordered index on the same column in postgres? I have inherited a postgres database where there are two identical indexes except for their order:

CREATE INDEX avail_time_ix ON table (availability_time);
CREATE INDEX avail_time_dc_ix ON table (availability_time DESC);

Is there any benefit to having both the ordered index and the unordered index?

I am combining several good comments into a comprehensive answer.

Order on a mutli-column index could have a bigger impact on efficiency than a single column. In my case it is only one column.

Postgres can scan indexes forwards and backwards just as efficiently. The index which does not have an explicit order is ordered ASC by default.

  Specifies ascending sort order (which is the default).

This means that the two indexes are redundant.


How do I guarantee data is the same after upgrading SQL Server? I have to upgrade a database from SQL Server 7 and SQL Server 2000 to SQL Server 2008. Additionally I have to be able to prove in a court of law that the data pre-migration is exactly the same post-migration.

Any ideas?

I'd buy Red Gate SQL Data Compare so 100% comparisons are possible.


Connect to mysql server with one url but have multiple masters I am looking to have one URL like master.mydb.com, but under the hood I want to be able to access databases on slave1.mydb.com or slave2.mydb.com.
The reason for this is we have a massive number of near identical databases on each server, but very different data on each database. kinda like this:
slave1.mydb.com has databases
p1
p2
p3

slave2.mydb.com has databases
p4
p5
p6

all of those databases will have the exact same tables in them, but when my server accesses the data I just want to be able to connect to the cluster, and not have to worry about figuring out where the database p4 is stored, and making a connection slave2-mydb.com. In production we will probably have something more like slave[1-20].mydb.com with

This isn't a replication problem to solve. Something like spider, max scale or vitess. Also, see the Percona toolkit for comparing tables.
Of course internally you could have some DNS based mechanism like p5.mydb.com to indicate these server has p5 and use this in the application's connection to the database.
If you have same databases in all slaves then you need a load balancer, which you can achieve with haproxy, ngnix. If you want to split read write between slave and master then you can look into proxySQL. If you have different databases then you need to manage that in the connection strings.


How to block SSMS from accessing the internet? How do I block SQL Server Management Studio from accessing internet sites?
Let me set the stage.  I'm sitting behind a Bluecoat proxy server at work.  We have a management policy forbidding us to access the internet using a privileged account.  And I need to use a privileged account to run SSMS to perform database administration.
So to perform database administration, I "runas" SSMS with my privileged account.  I suspect SSMS is opening a connection to a microsoft.com site for unknown reasons.  The Bluecoat proxy server sees this unauthorized internet connection, gets mad at me for violating company policy, and blocks internet access to my IP address for the next hour.
I'm aware that SSMS checks for updates by default at startup.  I've turned that off.  Something else is going on.

Instead of fully impersonating your privileged account, start SSMS with runas /netonly.  That will cause the SSMS Windows process to run as your logged-on account, and only use the privileged account credentials when accessing domain resources using NTLM authentication.
Http requests to internet resources will not use NTLM for resources outside of your trusted domain.
Alternatively you can configure the credentials in the Windows Credential Manager.
This is the same technique used to connect using Windows Integrated Authentication to a SQL Server from a machine not joined to a domain trusted by the SQL Server's domain.  See eg: https://www.mssqltips.com/sqlservertip/3250/connect-to-sql-servers-in-another-domain-using-windows-authentication/


You can use the UNION operator to combine your hydrats_survey and hydrants_measurement tables as one dataset, then you can use the ROW_NUMBER() window function to get the latest row per hydrant from that dataset with a query like the following:
SELECT hydrants.fid, hydrants.h_number, conditions.hydrant_condition_date as last_date, conditions.condition, conditions.st_pressure, conditions.dyn_pressure
FROM hydrants
INNER JOIN
(
    SELECT fid, hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure, ROW_NUMBER() OVER (PARTITION BY hydrants_fid ORDER BY hydrant_condition_date DESC) AS SortId
    FROM
    (
        SELECT fid, survey_date AS hydrant_condition_date, condition, hydrants_fid, CAST(NULL AS INT) AS st_pressure, CAST(NULL AS DECIMAL(10,2)) AS dyn_pressure
        FROM hydrats_survey

        UNION

        SELECT fid, measurement_date AS hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure
        FROM hydrants_measurement
    )
) conditions
    ON hydrants.fid = conditions.hydrants_id
WHERE conditions.SortId = 1 -- Filters out everything but the latest record per hydrant ordered by date

The only caveat is if you have two records with the same date that are different in other ways (such as different conditions, as nbk points out), this will choose one of those two at random because there is no distinct field combination to order on inside of the ROW_NUMBER() function.
One might try to solve this by using the fid column of the hydrats_survey and hydrants_measurement tables, but those two fields are independent of each other and could still end up creating a non-unique dataset when unioned together in the subquery. So you'd need to decide on a unique field to be the tie-breaker (e.g. if there's a bad and good condition on the same day, then take the record with the bad condition) or accept the results will be semi-random when there are two records on the same date.


How to perform ETL from RDBMS to Neo4j I have a mature 50+ tables web application based on mySQL. In order to do some advanced data mining I want to use Neo4j and the goodnes of cypher. However I'm having a hard time migrating my data from RDBMS to Neo4j. I don't wan't to do it by hand, because this is generally unmaintainable.

I use pentaho which supports the Neo4j JDBC, however I'm having a hard time wrapping my head around loading the extracted data to Neo4j since the GUI is clearly designed for RDBMS and there are no sources about how to do it. Had anyone run into a similar problem and found a solution?

JDBC Driver that Neo4J provides are for Cypher queries ( which are select only). There are no inserts , updates or delete. You will have to adopt some other means like .. exporting rdbms stuff to csv and importing csv to Neo4J.


How to provide a SQL Server database with a C# application I am working on a C# application which depends on a few SQL Server databases. Now, in my development environment, I am using SSMS (SQL Server Management Studio) to create and manage my databases. My connection string looks like this:

 SQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _
                        "Data Source=xxx.xxx.xxx.xxx,1433;" & _
                        "Initial Catalog=mySQLServerDBName;" & _
                        "User ID=myUsername;" & _
                        "Password=myPassword"

I am not sure how to properly ask the question. The point is, how do I provide the databases then? I mean:

Should I backup my databases, ask the client to install both SQL Server instance and SSMS, then restore the databases from SSMS?
Should I provide my databases and ask the client only to install SQL Server instance? In this case, how should they restore the databases?

Please, I am not very experienced, that's why I am asking.

I suppose that you have a setup kit to ship your application.

I also suppose that you want to ship an empty database with your application, with at most a handful of rows for built-in values in some tables.

The correct way to handle this is to include your database setup in your setup kit, so that it creates the database and all required objects (tables, views, procedures, users, logins, ...).

One possible way to do this is to include a .dacpac file in your setup kit and invoke sqlpackage to deploy it to a target sql server instance. The .dacpac file should be obtained by building a database project in visual studio (SSDT).

If you don't have a database project or you don't have a setup kit, you should probably rethink your deployment strategy completely.


Creating Production -> staging flow, how to skip email addresses when doing mysqldump? I want to run mysqldump or basically, anything that lets me easily export / import mysql db from one server into another.

Thing is: I want to export production data as staging / integration / demo data. That means, prior to importing the data, all user data must be gone. We had it one time, that the users got imported, and because the system ran a cron job, the server started sending out 200'000 emails. Can you imagine? yes yes, just disable the cronjob prior to import, but accidents happen. So importing user data and then deleting them is not an option.

Optimally I want to just ignore certain columns where there is an email, or replace that email with a test email string.

Is there something that helps me with that?

php is what we use for the application, so any php import export tool is a possibility as well.

Native mysqldump doesn't have a feature to ignore columns. But you can do this by exporting that specific tables as CSV then import it to your staging.

Example

DB: bhuvi
Total tables: 3
No. of tables I need to ignore some columns: 1

testtable has some secret data.

select * from testtable;
+------+----------+-----------------+
| id   | name     | email           |
+------+----------+-----------------+
|    1 | sqladmin | sql@admin.com   |
|    2 | bhuvi    | bhuvi@gmail.com |
|    3 | dba      | dba@xyz.com     |
|    4 | stack    | ex@stack.com    |
|    5 | user     | user@test.com   |
+------+----------+-----------------+

View for showing dummy emails

create view secret as ( select id, name , 'test@test.com' as email from testtable);
select * from secret;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | test@test.com |
|    2 | bhuvi    | test@test.com |
|    3 | dba      | test@test.com |
|    4 | stack    | test@test.com |
|    5 | user     | test@test.com |
+------+----------+---------------+

Export the data as CSV from shell

mysql -u root -p bhuvi -e "select * from secret" | sed 's/\t/"|"/g;s/^/"/;s/$/"/;s/\n//g' > testtable.csv

Dump all the tables with schema only

 mysqldump --no-data -u root -p bhuvi > bhuvi.sql

Dump the table which are import as it is

DBTODUMP=bhuvi
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('testtable')"
TBLIST=`mysql -uroot -ppass -AN -e"${SQL}"`
mysqldump -uroot -ppass ${DBTODUMP} ${TBLIST} > bhuvi_tables.sql

Restore the DB:

Restore schema:

mysql -u root -p stage < bhuvi.sql

Restore full tables:

mysql -u root -p stage < bhuvi_tables.sql

Restore CSV file:

 mysqlimport --ignore-lines=1 --fields-terminated-by="|"  --fields-enclosed-by='"'  --verbose --local -u root -p stage testtable.csv

Check the data:

mysql> select * from testtable;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | test@test.com |
|    2 | bhuvi    | test@test.com |
|    3 | dba      | test@test.com |
|    4 | stack    | test@test.com |
|    5 | user     | test@test.com |
+------+----------+---------------+

Or simple method: Purge the email column after restore

udpate testtable set email='test@test.com'


Concatenate multiple columns into one column (,) separated In SQL database that stores Images in a parcelImages table.Since each parcel can take part in multiple Images and each images can involve multiple parcels.

I want to get each parcel having Image value with comma separated values as shown above

I tried with this SQL Query

SELECT ParcelId,id,concat(ParcelId, ' ', id)
FROM ParcelImages
WHERE Id IN (43418,43464,43465,43466,43467,43470,43471,43482,43483)

but expected result not get. How can this be done?

I have also tried an INNER JOIN How can I achieve this, I'm kinda stuck at the early beginning.

If there is a table called STUDENTS

ID                     StudentName
----------             -------------
1                      Mary
1                      John
1                      Sam
2                      Alaina
2                      Edward

Result I expected was:

ID              StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I think you need to use GROUP CONCAT (only available in MySQL). If you are using MS SQL Server you can try this project: https://groupconcat.codeplex.com/


Publishing database changes with SSDT that include adding columns to existing table to the target database through Project Publishing database changes with SSDT that include adding columns to the existing table in the target database(which was previously created through SSDT).Instead of creating a new SSDT database. Trying to add a column through table create a script or through alter table script.i was Encountered by the same error. Even tried through Schema Compare still the table in the target database encountering with the same error. How to alter the changes to the target database objects through SSDT publishing?

  RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT;

I tried by checking out the

Block incremental deployment if data loss might occur.
Don't ALTER replicated objects.
Do not alter Change DATA Capture Objects.

This is happening because you've added the column in the middle of the CREATE TABLE script.  This causes a "table rebuild" to happen.  I talked about this on my blog a few months ago: SSDT problems: Table Rebuilds

The section on column ordinality is the specific problem you have.  Say you have a table like this:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL
);

And then add a column in the middle of the column list:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [CommentCount] INT NULL, -- NEW COLUMN IN THE MIDDLE
    [PostType] VARCHAR(10) NOT NULL
);

This will "rebuild" the whole table in order to keep the underlying table metadata in sync between the model (your SSDT project) and the target database.

The solution?  Just add the column to the end:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL
    [CommentCount] INT NULL -- NEW COLUMN AT THE END
);

This doesn't rebuild the table, and thus doesn't cause potential data loss.


High latency with SQL Server 2014 Synchronous AlwaysOn I've built a SQL 2014 cluster in our development environment that consists of 2 nodes. Non-Shared disks

Specifications:

Storage is Nimble Storage Array with dedicated LUN's presented to the hypervisors, each drive on each guest (data, logs, tempdb) has a dedicated LUN.
The LUN's are cached on SSD's and latency on the array and LUN's is under 0.25ms for both reads and writes.
Entire dataset of this test is < 350MB
vSphere 6.0U1
2 identical VM's (Windows Server 2012R2 STD)

10 vCPU
64GB Ram
Storage is on dedicated VMDK's - pVSCSCi, all VMDKs are THICK-EAGERZERO.
VMXNET3
SQL Server 2014 SP1-CU5

AG name is AG02
Primary node is SQL2K14-02
Secondary node is SQL2K14-01
Synchronous mode
Automatic Failover

A copy of the production database showed 1000ms delay for inserts, so the same test has been run using HammerDB (www.hammerdb.com) in the following scenarios:

HammerDB - Node1, no AG
HammerDB - Node2, no AG
HammerDB - AG1, Sync  (Node1+Node2)
HammerDB - AG1, Async (Node1+Node2)

Findings:

The performance of test #1 and #2 are the same, so we know that we don't have a node that is slowing us down.
Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.
Using AG1 with Async we have 0 latency, but that is something we can't use.
I was able to rule out networking latency as both VM's are on the same VLAN. Doing a constant ping + tcpping + iperf simultaneously showed latency of less then 0.3ms while both servers were pushing > 3Gbit/s over TCP.
Below are graphs of the relevant metrics for AG.
During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.
When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

Question:

What is happening and why is there a whole second delay ?
I was thinking I'm doing something wrong, but didn't find anything in the documentation.

Thanks,
Idan.

Turning my comment into an answer.

There seems to be some misunderstandings of how certain performance counters work when it comes to availability groups. Only Synchronous Commit replicas will be counted toward Transaction Delay (ms)/Sec Asynchronous Commit replicas will not change this counter as they transfer no mirrored transactions (yes, this could have been called something better).

Let me correct some of the assumptions in the thought process in the original question.

  2.Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.

Transaction Delay (ms)/Sec, in a vacuum, means only that you have at least two synchronous commit replicas. This is because ONLY synchronous commit replicas have this value as we wait for the acknowledgements from the other synchronous replica that the data is hardened.

In order to have a complete picture and get an AVERAGE of how much latency overhead PER TRANSACTION we will need to capture the Mirrored Transactions/Sec counter. This counter will give us how many synchronous commit transactions were "mirrored" to other synchronous replicas. If we take the Transaction Delay (ms)/Sec and divide it by the Mirrored Transactions/Sec counter the end result will be the average delay in ms per transaction.

  3.Using AG1 with Async we have 0 latency, but that is something we can't use.

This is not accurate at all. Ther, in fact, is GREATER latency is getting the transaction blocks hardened on the asynchronous commit replicas it's just that we don't wait for them to be hardened like we do with synchronous. Thus, there is still a delay (your network doesn't magically become a non-wait environment) it's just not counted in that specific counter as that counter ONLY contains information for synchronous commit replicas and transactions.

  6.During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.

There is not nearly enough information to make me believe this is the case. Even on some pretty terrible infrastructure I normally see 200-300 ms and nothing near 1 second PER individual transaction.

  7.When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

See my response to 3. which is also the same answer to this.

Follow Up

Capture both counters, Transaction Delay (ms)/Sec AND Mirrored Transactions/Sec and post back the results.
Test against your specific workload as the change in workload types will greatly factor in to response times.


Run CDC Cleanup job at specified time I have enabled cdc and using it , it works fine now the problem is that we have many tables in a database and also we have many databases so how to configure cdc cleanup job to run again and again after a specific time , I read this article tried what he said but its not working for me .

I also tried this SP sp_MScdc_cleanup_job , I executed this it clean all data but i need it to be automatic

Let suppose i want to run it every minute just for check that its running or not , What should i do ?

After you enable the CDC on a database 2 jobs are created for that database:

cdc.DBName_capture (which will start the change data capture collection agent)
cdc.DBName_cleanup (which will clean up the change tables periodically)

the default retention is 3 days (data older than 3 days is removed)
the default schedule for this job is daily at 02:00 AM

The default retention period (how many days worth of data is kept before removal) and the threshold value (how many rows at once to clear in one execution) can be found in the table msdb.dbo.cdc_jobs, so any configuration for the cleanup can start here. Also, changing the schedule of the cleanup job is a trivial thing to do, as it's not different than any other job in this aspect.

So what you need to do is go to all the cleanup jobs of your CDC enabled databases and tune and schedule them as needed.

References:

Cleanup Job
Change Data Capture - What is it and how do I use it?
Tuning the Performance of Change Data Capture in SQL Server 2008


Difficult migration from Postgres 8.1 (postgis 1.5) to Postgres 9.4 (postgis 2.1) I am in charge of a system developed 8 years ago. When I make a dump of the postgres 8.1 database, all the postgis 1.5 functions are included. I have successfully migrated it to a postgres 9.1, because it still supports postgis 1.5, but as soon as I try to migrate to 9.4 I encounter many problems because of all the old spatial functions that are defined during the restore process. There are also all sorts of constraints defined using the old postgis 1.5 functions.

Is there an effective way of updating all these references from postgis 1.5 to postgis 2? Do I need to manually edit the backup files? (I have about 300 tables).

Thanks in advance :-)

Follow the hard upgrade procedure which uses postgis_restore.pl to help translate the differences between the versions.


Converting multiple rows to one row I have the following data coming from Finger Print devices, Each In and Out of employees are recorded.
UserId         CheckTime              CheckType
------        -----------            -----------
  2      2020-08-03 08:15:12.053          I
  2      2020-08-03 16:00:00.053          O
  2      2020-08-04 08:00:12.053          I
  2      2020-08-04 16:10:00.053          O

I want the above data to be as following:
UserId         CheckTime           CommingTime                 LeavingTime
------        -----------          -----------                -------------
  2           2020-08-03           08:15:12.053               16:00:00.053
  2           2020-08-04           08:00:12.053               16:10:00.053

**Note: ** A user can have multiple In Out record per day
I have tried the following answers but didn't help:
How to merge multiple rows into one row with SQL?
Any idea??

Main idea:
WITH cte AS ( SELECT UserId,
                     CheckTime,
                     LEAD(CheckTime) OVER (PARTITION BY UserId ORDER BY CheckTime) NextTime,
                     CheckType,
                     LEAD(CheckType) OVER (PARTITION BY UserId ORDER BY CheckTime) NextType )
SELECT UserId, CheckTime, NextTime
FROM cte
WHERE CheckType = 'I'
  AND NextType = 'O'

Modify as you need - extract date and/or time parts, check that date parts are equal, etc.


Application specific users table or mysql.users I am using a mysql installation that handles an application with multiple users. These users have varying permissions for table access, but also need to be referenced within tables in the application database. For example, I have a manufacturer table that stores the person who manufactured a batch, but different manufacturers have different access to the tables.
If I weren't referencing them within the application tables I would use the mysql.users table to handle permissions, and if I weren't giving users variable permissions then I would use a local users table.
Is there a standard and secure way of handling this sort of situation?

You want a feature like Oracle Virtual Private Database. Unfortunately MySQL does not have this. There is a similar question with answers at https://stackoverflow.com/questions/5527129/mysql-how-to-do-row-level-security-like-oracles-virtual-private-database.


Software for working with SQL Server databases in Windows Server 2008 We have an ASP .NET project that uses SQL Server that we host on a Windows Server 2008 machine. Where does Microsoft keeps its documentation for the tools that can be used for administering SQL Server databases? I'm not sure where to start looking for the software to manage our db.

You can use Management Studio. Download the Express version which, as of SQL Server 2012 Service Pack 1, is fully functional and no longer requires a license and a different installation to gain access to the full set of features. Here is the SP2 version:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

The file you want is SQLManagementStudio_x64_ENU.exe (unless you are on 32-bit, then you want SQLManagementStudio_x86_ENU.exe).

There is some pretty good documentation on how to use Management Studio here:

http://msdn.microsoft.com/en-us/library/ms174173(SQL.110).aspx


Comparing dates in a BEFORE INSERT trigger I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use.

Something like:

CREATE TRIGGER chk_dates
   BEFORE INSERT ON `job_history`
   FOR EACH ROW
    BEGIN
      IF( NEW.end_date < OLD.start_date ) .../// error, don't insert data
      ELSE .../// allow the data to be entered

My table job_history will have a start_date, and when end_date is entered, I want it to check to ensure that it isn't prior to start_date.

How do I accomplish this?

With MySQL 5.5 or 5.6, you can use SIGNAL instead of the hacks to bail from a trigger.

Also, on a BEFORE INSERT trigger, only the NEW alias is used.  Not OLD.

CREATE TRIGGER chk_dates
BEFORE INSERT ON `job_history`
FOR EACH ROW
BEGIN
  IF (NEW.end_date < NEW.start_date) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'end_date cannot be earlier than start_date';
  END IF;
END;

No "else" is needed because taking no action in a BEFORE trigger allows the original action to proceed.


Why scanning an index backwards is slower? Empirical tests show that a query like this on an InnoDB table:

SELECT indexed_column FROM tab ORDER BY indexes_column ASC;

is faster than its counterpart with ORDER BY ... DESC. Why is this the case?

Note: I did the tests with MySQL 5.7 and 5.6. So this has nothing to do with ascending indexes in 8.0.

The author, Chaithra Gopalareddy,  of the related article, MySQL 8.0 Labs – Descending Indexes in MySQL, explains in a comment why backwards index scans are slightly less efficient than forward scans:

Thanks for showing interest in the new feature. The ~15% cost benefit in forward scans can be attributed to the optimizations done in innodb to favor forward scans over backward scans.
For ex: W.r.t a scan within the page – The records in a page form a singly linked list. To get the next record, a forward scan just follows the link where as the backward scan need to start from the beginning (first slot) till the current slot/record to identify the previous record.
Along with the above, there are some more contributing factors like, during page switch – page latching rules currently defined in innodb favor forward scans over backward scans.

So there are two factors:

the single linked list structure of records inside a page
page latching rules regarding page switches


Alter Table with var from cursor I came up with the great idea that to begin the normalization of characters and collate to utf8, I found the sentences that would help me to carry out this activity, but I have to go table by table. Then I thought about not doing a stored procedure to help me with this.

Everything was perfect, I'm even setting the name of the database and I automatically look for the tables to work, but when making an ALTER TABLE I'm generating the following error.

  Error Code: 1146. Table 'ARMADILLO_BASE.tableNameFull' does not exist

Below I share my storage procedure.

CREATE DEFINER=`root`@`%` PROCEDURE `setCollate`(
IN dbName VARCHAR(255),
OUT foundRows INTEGER
)
BEGIN
    -- ###############################################################
    -- GLOBAL VARS
    -- ###############################################################
    DECLARE tableName, tableNameFull VARCHAR(255) DEFAULT FALSE;
    DECLARE noMoreRowsOne BOOLEAN DEFAULT FALSE;
    DECLARE querydbName CURSOR FOR
        SELECT
            TABLE_NAME
        FROM
            INFORMATION_SCHEMA.TABLES
        WHERE
            TABLE_SCHEMA = dbName
                AND TABLE_NAME NOT IN (SELECT
                    TABLE_NAME
                FROM
                    INFORMATION_SCHEMA.VIEWS
                WHERE
                    TABLE_SCHEMA = dbName);
    -- set noMoreRowsOne
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRowsOne = TRUE;
    -- ###############################################################
    -- PROCESS CURSOR
    -- ###############################################################
    -- open cursor
    OPEN querydbName;
        -- set @foundRows
        SELECT FOUND_ROWS() INTO foundRows;
        -- begin LOOP
        querydbName_LOOP: LOOP
             -- fetch tableName
            FETCH querydbName INTO tableName;
            -- SET tableNameFull
            SET tableNameFull = CONCAT('`',dbName,'.',tableName,'`');
            -- SELECT tableNameFull;
            -- validate noMoreRowsOne
            IF noMoreRowsOne THEN
                CLOSE querydbName;
                LEAVE querydbName_LOOP;
            END IF;
            -- SET utf8 and utf8_general_ci
            SET foreign_key_checks = 0;
            ALTER TABLE tableNameFull CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
            SET foreign_key_checks = 1;
        END LOOP;
END

They help me? What am I failing? ... Or is this not possible?

Only values, not table names nor column names, can be filled in the easy way.

Instead, construct the SQL statement with CONCAT, then PREPARE and EXECUTE the statement.


Where is oraenv located in solaris? I had a problem recently with oraenv and oratab and finding out where oraenv is located was a bit challenging.

Since it's a question I ask myself often, I wanted to have it here. It may help others.

This is where it's located:

myuser@mysolarishost:mydbinstance:/usr/local/bin
> ls -l oraenv
-rwxr-xr-x   1 oracle   root        7012 Jan 11 11:15 oraenv


SQL Server Database File Groups on a SAN: Relevant or Not? I am about to build out a new SQL Server and I was planning to make extensive use of file groups. I expect heavy growth, and heavy read/write to 5 different databases on this server. I was planning on creating 2 additional file groups (one for user data and one for indexes) on each database for a total of 3 file groups per database. I was planning on splitting up the file groups among different drives/spindles. This server is a virtual server (VMWare) on an EMC SAN. I am new to SAN architecture and am I not the SAN administrator. I read a brief blurb on file groups and SANs in the book "Microsoft SQL Server 2012 Unleashed" that file groups were likely irrelevant when using SANs. Unfortunately, there wasn't much more detail than that, and I haven't found much else out there on the topic.

Is there a point to using file groups when using a SAN for storage?

If not, then why not? If so, then why?

What questions might I ask my SAN Admin on this topic?

I am not a SAN admin, but I have a very nice one in my team that takes the time to do his job proprely, and share his knowledge with us when we ask.

The way our SAN is configured makes it so there is a very large amount of cache between the SQL servers and the physical disks. In this situation, having multiple filegroups, or even splitting the DATA and LOG files on different drive letters is a moot point, as it all ends up in the same cache cluster and points to the same large array of 15000RPM disks. The cache is intelligent enough to keep the relevant stuff cached, and write to disk in a "Lazy Writer" method.

That said, althou we handle a very large amount of data, we are not overly heavy in throuput. This setup is lightning fast for our needs, and very safe between the usual backups and SAN automatic redundancy to another server room.

Sit down with your admin, tell him what your needs are, and balance the final setup with his knowledge and architecture. SAN architecture can vary greatly, so some of the "best practices" of SQL server installation may not even apply, like it did for us.


Compute duration of an event based on consecutive values in another column I need to compute the sum of durations of multiple events based on consecutive values in another column.
             ts             | w
----------------------------+---
 2020-07-27 15:40:04.045+00 | t
 2020-07-27 15:41:04.045+00 | t
 2020-07-27 15:41:14.045+00 | f
 2020-07-27 15:42:14.045+00 | t
 2020-07-27 15:43:14.045+00 | t

The event is considered being active as long as the column w has a consecutive value of true.
The duration of the first event would be 60 seconds. '2020-07-27 15:41:04.045+00' - 2020-07-27 15:40:04.045+00. The second event has the same duration. The sum of both would be 120 seconds.
What would be the best/most performant approach to computing these values? The longest time range we'll be looking at will probably be half a year involving about 30 million rows.
I wrote a custom aggregate function that computes the duration but it takes about 16 seconds for only 1.5 million rows.
 Aggregate  (cost=444090.03..444090.04 rows=1 width=4) (actual time=16290.826..16290.828 rows=1 loops=1)
   ->  Seq Scan on discriminator0  (cost=0.00..57289.03 rows=1547203 width=9) (actual time=0.016..1723.178 rows=1547229 loops=1)
 Planning Time: 0.196 ms
 JIT:
   Functions: 3
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.889 ms, Inlining 0.000 ms, Optimization 0.508 ms, Emission 6.472 ms, Total 7.870 ms
 Execution Time: 16291.836 ms

I'm new to SQL and basically just got this working through trial and error, so I'm sure there is room for improvement. Maybe even a whole different approach. Here's a fiddle with the aggregate function.
I'm not sure if i should include the code because it's quite long

Here is one idea that you might want to try:
select max(ts) - min(ts)
from (
    select ts, w
       ,   row_number() over (order by ts)
         - row_number() over (partition by w order by ts) as grp
    from test
) as t
where w
group by grp;

The idea is to enumerate ts in two ways, first the complete set, then per w and calculate the difference (called grp).
If the difference (grp) changes it means that w changed. We can, therefore, pick the max(ts) - min(ts) within each grp, for groups where w = true.
The problem you are trying to solve is often referred to as an "island and gap" problem. I modified your fiddle with my query: Modified fiddle
You can try and compare the performance with your function. You probably want to add an index like:
CREATE INDEX x1 on test (ts, w);

EDIT: to calculate the total duration in seconds:
select EXTRACT(EPOCH from sum(dur))
from (
    select max(ts) - min(ts) as dur
    from (
        select ts, w
          ,    row_number() over (order by ts)
            -  row_number() over (partition by w order by ts) as grp
        from test
    ) as t
    where w
    group by grp
) as tt;

EDIT: I played around a bit more, see Updated fiddle, and added a bit more data (~ 70000 rows depending on the number of duplicates ). I can see that the results differ between queries, haven't examined that more closely though. But I'm a bit surprised that you say that the queries perform about the same. Looking at the plan for your query:
Aggregate  (cost=21650.65..21650.66 rows=1 width=4) (actual time=2389.576..2389.576 rows=1 loops=1)
  ->  Seq Scan on test  (cost=0.00..1190.40 rows=81840 width=9) (actual time=0.013..36.243 rows=68709 loops=1)
Planning Time: 0.097 ms
Execution Time: 2389.663 ms

and one with window functions:
Aggregate  (cost=18951.01..18951.02 rows=1 width=8) (actual time=163.179..163.179 rows=1 loops=1)
  ->  HashAggregate  (cost=18946.01..18948.51 rows=200 width=24) (actual time=160.594..162.306 rows=8447 loops=1)
        Group Key: t.grp
        ->  Subquery Scan on t  (cost=16183.91..18639.11 rows=40920 width=16) (actual time=106.327..146.336 rows=59271 loops=1)
              Filter: t.w
              Rows Removed by Filter: 9438
              ->  WindowAgg  (cost=16183.91..17820.71 rows=81840 width=17) (actual time=106.326..140.013 rows=68709 loops=1)
                    ->  Sort  (cost=16183.91..16388.51 rows=81840 width=17) (actual time=106.316..114.565 rows=68709 loops=1)
                          Sort Key: test.ts
                          Sort Method: external merge  Disk: 2296kB
                          ->  WindowAgg  (cost=7868.76..9505.56 rows=81840 width=17) (actual time=44.285..81.613 rows=68709 loops=1)
                                ->  Sort  (cost=7868.76..8073.36 rows=81840 width=9) (actual time=44.261..52.843 rows=68709 loops=1)
                                      Sort Key: test.w, test.ts
                                      Sort Method: external merge  Disk: 1288kB
                                      ->  Seq Scan on test  (cost=0.00..1190.40 rows=81840 width=9) (actual time=0.011..5.775 rows=68709 loops=1)
Planning Time: 0.224 ms
Execution Time: 164.052 ms

indicate that the latter is significantly more efficient
I'll see if I can spin up a container with Postgres and dump the data into a table and examine the different results
The index is probably not that useful since we are scanning the whole table anyhow.
Final edit:
I could repeat the diff with only 50 rows Fiddle, so I added the rows to a Libreoffice Calc sheet. It appears as if the window version and Libreoffice agrees on the result. For some reason, I got a negative number from your function. This may be due to that the first row is a single row. This is the data used in verification, perhaps you can just add a "t" row a couple of seconds before the first one to rule out errors due to a single row:
2020-07-27 15:55:37 t
2020-07-27 16:01:56 f
2020-07-27 16:09:17 t
2020-07-27 16:23:32 t
2020-07-27 16:28:47 t   00:19:30
2020-07-27 18:14:32 f
2020-07-27 18:30:26 t
2020-07-27 18:40:52 t
2020-07-27 19:01:18 t
2020-07-27 19:05:24 t
2020-07-27 19:16:16 t
2020-07-27 19:56:00 t
2020-07-27 20:34:05 t   02:03:39
2020-07-27 21:12:18 f
2020-07-27 21:58:53 t
2020-07-27 23:51:55 t
2020-07-28 00:42:24 t
2020-07-28 00:43:56 t
2020-07-28 01:15:08 t
2020-07-28 03:34:08 t
2020-07-28 06:20:28 t
2020-07-28 06:23:14 t
2020-07-28 06:30:51 t
2020-07-28 06:34:12 t
2020-07-28 06:38:41 t
2020-07-28 06:48:48 t
2020-07-28 07:49:39 t
2020-07-28 08:28:41 t
2020-07-28 09:32:42 t
2020-07-28 09:45:36 t
2020-07-28 09:57:46 t
2020-07-28 10:29:37 t
2020-07-28 11:53:21 t
2020-07-28 11:53:29 t
2020-07-28 11:54:35 t
2020-07-28 12:23:18 t
2020-07-28 13:10:48 t
2020-07-28 13:37:21 t
2020-07-28 14:22:14 t
2020-07-28 14:51:16 t
2020-07-28 14:52:12 t
2020-07-28 15:30:04 t
2020-07-28 16:34:43 t
2020-07-28 17:02:03 t   19:03:10
2020-07-28 18:11:49 f
2020-07-28 18:21:09 t
2020-07-28 18:41:38 t
2020-07-28 19:14:45 t
2020-07-28 19:16:01 t
2020-07-28 19:22:03 t   01:00:54

             Total      22:27:13        80833 seconds

Final edit 2:-)
I had a look at your function, and it appears as if you expect the rows to be ordered according to ts? If so, that is a false assumption since they may come in any order. If you do a simple test like:
SELECT ddur(ts, w) FROM (select ts, w from test order by random()) as t;

and compare with:
SELECT ddur(ts, w) FROM test;

the results are probably different. See Modified original Fiddle


How do you find home information for Oracle RAC on Unix? Need help finding Oracle home path corresponding to a database instance in RAC environment. I am aware of few of the ways to achieve the same. Listing them below to avoid the same answers.

/etc/oratab
This file is not mandatory and hence may not have all instance information.
Parsing contents of Listener.ora
In RAC environment, the listener.ora can be located at non default location.
use TNS_ADMIN to find Listener.ora location and parse the file.
ORACLE_HOME env variable
May not be set always.
ps -ef | grep tns to get the home path from service name.
Gives path for currently running listener
select "SYSMAN"."MGMT$TARGET_COMPONENTS"."HOME_LOCATION"
from "SYSMAN"."MGMT$TARGET_COMPONENTS"
where "SYSMAN"."MGMT$TARGET_COMPONENTS"."TARGET_NAME" = <Database SID>
The schema sysman can be dropped after first time login to oracle.
SELECT NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1) , SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1)) FOLDER
FROM DBA_LIBRARIES
WHERE LIBRARY_NAME = 'DBMS_SUMADV_LIB';
So if a DBA changes Oracle Home (and hence the location of libqsmashr.so) after installation of Oracle, the path retrieved from above query would be invalid.
. oraenv
Works only for 11g

I am trying to find out a generic way which will work for all Oracle versions and it should not be dependent on anything which is not useful to DBA.

Do you have any way other than listed above to do the same?

Many Thanks in advance.

First of all, I should thank you for all other steps that you mentioned above.
As an addition to your points, I have this one which I've been using so far.

Solaris and Linux

$ pgrep  -lf _pmon_
12586 ora_pmon_ee10204

ORACLE_SID is ee10204

$ pwdx 12586
12586: /u01/oracle/10.2.0.4/ee1/dbs

ORACLE_HOME is /u01/oracle/10.2.0.4/ee1

HPUX

$ ps -ef | grep pmon
ora1024 25611     1  0  Mar 21  ?         0:24 ora_pmon_itanic10

ORACLE_SID is itanic10

$ pfiles 25611 | grep  bin
25611:                  /opt/ora1024/app/db_1/bin/oracle

ORACLE_HOME is /opt/ora1024/app/db_1

AIX

$ ps -ef | grep pmon
ora1024   262314        1   0   Mar 23      -  0:12 ora_pmon_mercury

ORACLE_SID is mercury

$ ls -l /proc/262314/cwd
lr-x------   2 ora1024  dba  0 Mar 23 19:31 cwd -> /data/opt/app/product/10.2.0.4/db_1/dbs/

ORACLE_HOME is /data/opt/app/product/10.2.0.4/db_1

Personally, I haven't seen any environment that tries to confuses the person who will maintain the system.
Most of the time, I've always seen the normal /u01/app/oracle for the ORACLE_BASE.


What is the fastest way to duplicate inserts of an identity column to another table? I have two tables: A data table with an auto-incrementing id column, and an id table. I would like every id in the data table to show up in the ids table on insert. I have many such data tables and I wish to ensure that the ids in each cannot clash with each other. I also wish to foreign key to the global list of ids in the ids table. I would like a system to do this automatically so I don't need to script explicit inserts to the ids table. I know this is straight forward to achieve using triggers, but I want to know the most performant way to do this for data sizes beyond 100m rows. Simplified example code follows:

The tables:

create table data(
  id bigint generated always as identity(start 1 minvalue 1 maxvalue 100000000 no cycle cache 1 increment 1),
  name text
  );

create table ids(id bigint);

I tried using a rule, but had an issue with the volatile nature of the identity column:

create rule url_id_sync as on insert to data do also insert into ids(id) values (new.id);

insert into data(name) values ('some value');

The data:

select * from data;

|id|name      |
--------------
|1 |some value|

select * from ids;
|id|
----
|2 |

...which is not correct of course.

So, I tried a trigger approach:

create or replace function ufnt_internal_identifier_insert() returns trigger as
$$
begin
  insert into ids(id)
  values (NEW.id);
  return new;
end;
$$ language plpgsql
;

create trigger utr_ids_insert after insert on data for each row execute function ufnt_internal_identifier_insert();

This works fine of course:

insert into data(name) values ('some value');

select * from data;
select * from ids;
|id|name      |
--------------
|1 |some value|

select * from ids;
|id|
----
|1 |

Some perf metrics: Doing the insert of 10m rows to the data table without the trigger takes ~24s on my system. With the trigger enabled, it takes ~52s. I guess this is because its doing 2x the number of inserts shrug.

I tried creating a 'sync' statement:

insert into ids(id)
select d.id from data d
left join ids i on d.id = i.id
where i.id is null;

Total time ~22s (~13s for the initial insert, ~9s for the 'sync').

My issues are as follows:

Over time both tables are expected to become large (>1bn rows) and doing a left join in the sync function is going to spill to disk. Its not restricting the records in the data table to ones that have been inserted, its checking all of them every time. However, the operation is batch based.

The trigger approach is firing on ever row insert, I don't know how I can write the trigger to function on a statement level.

Are there any other techniques I can follow up? Would partitions help? Indexes?

Of course in the full production system the ids columns will be primary keys. I left this out of the example code, but I've seen performance improvement by scripting out the constraints before the inserts, and then applying them again.

Try

WITH cte AS ( INSERT INTO data(name)
              VALUES ('some value'), ('one more value')
              RETURNING id )
INSERT INTO ids
SELECT id
FROM cte;

fiddle


SQL Server 2016: cannot restore database from .bak file because of huge transaction log? From our production server, I have downloaded a .bak file of SQL Server 2014 which I want to restore in my SQL Server 2016 Developer in my pc to test newer versions of the application with the most recent data.

I restore the DB from SSMS and while the progress bar gets at 100% in few seconds (the .bak file is about 145 MB), then the process remains stuck like that and nothing more happens, even if the restoring process seems to continue.

I have noticed that a very huge transaction log is created at once as soon as the restore process starts and it has a size of more than 300 GB.

How can I solve this? I have read maybe I should shrink the transaction log before create the .bak file but I am afraid of causing damages to the production DB. Which am I supposed to do in the correct way?

When you backup a database, it grabs all the data from your database and the part of transaction log that it needs to put the database in the consistent state after restore.

When you restore database from backup it creates exactly that database that was backuped, so if you now have a log file of 300Gb it means the original database on your production server has a log file of 300Gb.

Yes you can shrink your log file now, on your test server, but first you should put your database in SIMPLE RECOVERY MODEL. This will not affect your production database.

But if your backup size was 145Mb only, this means that your production database has a problem: someone put it into FULL RECOVERY MODEL and does not take log backups. It's clear from your database size: when log file is >300 times greater than data file, it's a problem with wrong recovery model

Here is the code to check your database size:

use yourDB;
select name, type_desc, cast(size * 8. / 1024 as decimal(20,2)) as Mb
from sys.database_files;

UPDATE:

  ...to set up a maitenance plan every night so that it does a full
  backup of the DB, re-organizes/updates indexes and, once a week,
  updates statistics.Up to now, a night backup has always been enough.

  ... Anyway, should I want to keep thos transactional logs and the full
  recovery model, what should I know to improve things?
         I mean, if I wanted to take log backups in order to reduce transactional logs, what shall I do? Am I supposed to change any
  settings in the maintenance plan

You don't need FULL recovery model.
As for now, your backup plan suppose that it's acceptable to you to lose 1 day of work, and it's normal for a small noncritical database.

When you are in simple recovery model, minimally logged operations like index rebuild or select into will be minimally logged, that means they will be faster.

In case you need to restore your database it will be one simple restore from a full backup.

Your log management will be minimal: it will be automatically truncated on checkpoint.

When you are in full recovery model it means that every change to your data will be fully logged, and all that changes will be retained in your log file until you do a log backup. So what happened to you now is that your log file contains all the changes made to your database from your first full backup (since no log backup was taken) till now.

If you back up your log now, you'll be waiting much more time than you was waiting today while 300Gb of log space was zeroing out. To back up this log you'll need enough disk space, about 300Gb. After this backup completes, your log size will not change even if you'll try to shrink it because the active portion of the log will reside in the last VLF.

So if you decide to stick with full recovery model even after taking this enormous log backup you will still have a problem with your log size. You'll have to wait until the active portion of the log will move to the 1st VLF and it can require some time because your database is small enough, changes are small too, but your last VLFs are enormous. After shrinkig you'll have to decide how often will you take log backups and what is the appropriate log file size for your database.

So who and why chose FULL recovery model? Some HA technologies require it: log shipping, mirroring, AOAG. Or someone who cannot allow data lost. For example, if today your data file got lost, you could take your tail of the log backup (300Gb) and restore your database to the last minute of its life by restoring first your last night full backup and then restoring your log backup.
Without it all that you could do was to restore your database from yesterday's backup, i.e. restored database will contain the data at the state of yesterday with all today's work lost.

What they are talking about in the comments? Point-in-time restore. Full recovery model permits you to restore your database to any point in time in the past (only in case you have unbroken log backup chain, of course). Do you really need it? Can it be a situation when you are told to bring your db to 2017-01-04 03:45:00? Or maybe it's enough to you to bring it back to 2017-01-04 00:00:00, the time of your full backup? If only latter is the case, you are good enough to stay in simple recovery model and continue to back up your db every night at 00:00:00

I just do not know how to fit in 1 page the materials that merit a book :)
Try to start from BOL: Recovery Models and Transaction Log Management


Including the substring "_id" in column names I have a table of Teams. Each Team has an ID.
I have another table that holds Players. Each Player is assigned to a team.

So my tables might look something like

CREATE TABLE players (
   id        INTEGER   PRIMARY KEY
   name      STRING
   team_id   INTEGER   REFERENCES teams(id)
)

CREATE TABLE teams (
   id        INTEGER PRIMARY KEY
   name      STRING
)

Does writing _id make it any more clearer that the column is meant to hold an ID of a team (as opposed to maybe a team name)?

If I saw that team was linked to a key on the teams table I would assume the column is meant to hold an ID.

I find that if I started following a convetion where I tack on _id I'll end up with a lot of columns with _id in them, but am not sure whether it adds any value.

This is a matter of style, and likely to get religious.  Personally, I do find it valuable to explicitly label ID fields TeamID and PlayerID; as you say, it makes it perfectly clear that the field stores an ID and not some other type of data, such as a label.  Being explicit is usually more valuable than being brief, in the long term.

I prefer using PlayerID, TeamID, etc. over just ID, but offhand I can't give you a compelling reason.  It means that your joins are consistently X to X (P.TeamID = T.TeamID), whereas P.ID and T.ID are very different fields which should not be joined.  It's easier to confuse P.ID and T.ID when glancing through a query than P.PlayerID and T.TeamID.  On the other hand, there's value in consistently using the same field name for your PK, too.


Domain User lockout after SQL Cluster Failover We have this one user who uses SSMS to connect to a 3 node 2 instance fail over cluster. He also uses SSIS, Excel, SharePoint, SSAS etc.. to connect to this database instance I'll call TEST. There are a couple hundred other users of this instance.

His domain account gets locked out about 1-2 days after we have an instance failover. This has been happening for about a year. We generally failover about once a month but sometimes not for a couple months. He gets his password reset and everything is fine until the next failover.

I'm assuming he has entered his password in somewhere and it has since expired.  He changed it but never updated the spot he saved it. I can't find that spot for the life of me and the user is getting upset because I keep asking the same question.

We have verified using our syslog server that the lockout is coming from that SQL node that runs the TEST instance. It changes as the instance floats around the cluster so we are sure it is something in the TEST instance. The database engine and SSAS run as part of the TEST cluster service. SSISDB is fairly heavily used on that instance.

I checked all the agent job owners, he doesn't own any of those. I checked the SSISDB parameters list but didn't find is account in there. I'm not really sure if the constrained delegation we have setup for reporting services could trigger the lockout to come from the database server but maybe...

Any other spots I could check for saved credentials in SQL?

The problem with finding saved creds in SQL Server is that it's usually stored client side.  You will have to capture people trying to connect to the server, but it's interesting that it only occurs You could run a extended event or a trace and capture failed logins by that user, that will tell you if it's MS SQL Server that is locking him out.  Then you could view what time it occurred and capture other data.

A lot of things could be trying to log in with his AD account.  Even a Excel reporting server elsewhere.  It's interesting the log in attempts stop because after you change the password, it works and it only happens after fail over.

Are you sure it's in the SQL Server instance itself and maybe not something with the cluster group?  Perhaps it's something like a script on a failover disk or something related with the cluster.  I've had scripts do this before, or applications that store login creds.  Services are often a culprit as noted, and other potential configs.


Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately? The PostgreSQL 9.4 documentation states that adding the NOWAIT option to a SELECT FOR UPDATE means an error is generated when a row cannot be locked:

  To prevent the operation from waiting for other transactions to commit, use the NOWAIT option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately.

Exactly what error would that be?

As this is an acceptable condition, I want my Java code to check for such an expected error and then work around it.

SQLState: 55P03

In Postgres 9.4.x I tested this by performing an unresolved SELECT FOR UPDATE in pgAdmin and then doing a SELECT FOR UPDATE NOWAIT in my Java app. I used the JDBC driver JDBC41 Postgresql Driver, Version 9.4-1201.

The result was the following PostgreSQL Error Code.

Message:ERROR: could not obtain lock on row in relation "my_table_"
Class:Class 55 — Object Not In Prerequisite State
SQLState:55P03
Condition Name:lock_not_available


What ways are there to limit the processor / ram requirements of a specific query? Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that.

What ways are there to limit the processor / ram requirements of a specific query, basically trading speed and freshness for reliability?

You can set the session to allow uncommitted reads like so:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;

which will reduce the locking abnd concurrency overhead of your queries (but may make the results inconsistent as you will sometimes see "dirty reads" and similar). For more detail see http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

This will not limit the CPU/RAM/IO footprint of the query itself significantly though.

A common solution to this sort of thing (which may be overkill depending on your circumstances) is to maintain a slave replica on another server (or the same server but on different physical disks if I/O contention is your only/main concern) and run hefty reporting queries from there. Similarly if your reports are not real-time you could restore your backups to another place instead of the hassle of maintaining replication, so if you have daily backups you restore those elsewhere after they are made so your reporting queries don't impact the live DB at all but your reports are only correct as of the last backup. Neither of these methods works if your heavy SQL needs to perform updates or inserts though.

Reusing your backups in this way can be a useful way to verify that your backup arrangement is working: if your automatic restore to the reporting copy fails then that may be an early warning that your backup regime needs work. It is surprising how many people religiously schedule backups but never test them until they need to restore one in an emergency (hence there are quite a few panicked "how do I restore from a corrupt backup?" questions out there in various forums!).