What Method / Formula does a Nested Loops Operator use for row estimation? The following, simple query in AdventureWorks:
SELECT  *
FROM    Person.Person p
        JOIN HumanResources.Employee e
            ON p.BusinessEntityID = e.BusinessEntityID

Gives the following execution plans:
New estimator plan
If I look at the above plan, I can see the index scan and index seek both (correctly) estimate 290 rows, however, the estimated loops operator that joins the two, estimates 279 rows.
Old estimator
The old estimator also correctly guesses 290 rows out of both the seek and the scan but the nested loops estimates 289 rows which in the case of this query is a better estimate.
Is it true then that in the case of the new CE the optimizer estimates that when it is joins 290 rows from the index scan and 290 from the index seek, there will be 11 rows that do not match?
What method / formula does it use to make this estimate?
Am I correct in saying whatever said method is, that it has changed from the earlier CE version as that has made a different estimate?
I realise the "bad" estimate of the new CE is not significant enough to detriment performance, I am just trying to understand the estimators processing

SQL Server estimates cardinality for joins, not physical operators. The inner join in question will have the same estimate regardless of the physical operator employed (hash, merge, nested loops, or apply). The physical operator may affect the display, but the selectivity of the logical join is the same.
With that out of the way, logical join estimation is still a complex topic. There are many valid ways to produce an estimate. Two main alternatives are well covered by Dimitry Piliugin in Join Estimation Internals in SQL Server. You can also find general differences between the original and latest CE models in the Microsoft paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.
One main difference occurs when using histograms to estimate join selectivity. The more recent CE model uses coarse alignment, as I describe in SQL Server Join Estimation using Histogram Coarse Alignment. The original CE used fine alignment with linear interpolation for each step, which can give more accurate estimates, but can also be more variable.
You may notice that using Simple Join (with trace flag 9479 given by Dima) gives a 'perfect' estimate for your test query.

There is a TF that forces the optimizer to use Simple Join algorithm even if a histogram is available. I will give you this one for the test and educational purposes.

In other (more common) cases, simple join will give a terrible result. Such is the nature of cardinality estimation.
Related:

How does SQL Server's optimizer estimate the number of rows in a joined table?
Apply cardinality estimation problem in SQL Server


How to optimize query I'm using SQLite3 and the following query, It seems like there should be a better performing way to write this query; Is there?

Select Name,
    Sum(Case
       When
          Used
             Is Null
       Then
          ItemQty
       Else
          0
       End) As QtyNotUsed
From
   ContainersReceived
Group By
   Name

Basically my thought (that I can't realize) is something like:

Select
    Distinct Name
From
   ContainersReceived

combined with

Select
   Sum(ItemQty)
From
   ContainersReceived
Where
    Used Is Null

The result I want is a list of all Names and how much is in stock from the table ContainersReceived.

UPDATE:

Possible solution is to use:

Select
    Name,
    Sum(ItemQty)
From ContainersReceived
Where Used Is Null
Group By Name
UNION ALL
Select
    Name,
    0
From ContainersReceived
Where Used Is Not Null
Group By Name

It's not clear what you want but this may be it:

SELECT
    Name,
    SUM(CASE WHEN Used IS NULL
           THEN ItemQty
           ELSE 0
        END) AS QtyNotUsed
FROM
    ContainersReceived
GROUP BY
    Name ;

To be efficient, add an index on (Name, Used, ItemQty)

If you have another index, on (Used, Name, ItemQty), this query will be even more efficient - but it will not show Names that have no row with Used IS NULL:

SELECT
    Name,
    SUM(ItemQty) AS QtyNotUsed
FROM
    ContainersReceived
WHERE
    Used IS NULL
GROUP BY
    Name ;


Multicolumn statistics on large table yielding poor estimations I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the optimizer to make good estimates - in other words, is it no longer "selective" enough? With this particular customer's database/table, my query plan estimations are way off compared to others.

The particular statistic that I am concerned with comes from the table's PK/CLUSTERED INDEX. It is a multi-column statistic containing an int (ParentId) and a smalldatetime (TimeStamp).

When I issue a DBCC SHOW_STATISTICS('SomeTable', 'PK_SomeTable'), I get the following output (histogram omitted - but I can post it if it will help):

Name                    Updated              Rows       Rows Sampled    Steps   Density         Average key length  String Index    Filter Expression   Unfiltered Rows
PK_SomeTable            Jan 31 2014 10:59AM  181170887  181170887       200     2.022617E-05    8                   NO              NULL                181170887

All density     Average Length  Columns
0.0004892368    4               ParentId
5.519651E-09    8               ParentId, TimeStamp

Most of my queries are performed using the combination of both these columns (ParentId and TimeStamp). The small all density value shows the selectivity of this pair - obviously since it's the PK.

(1) The histogram appears to only show the ParentId column. Am I missing something here? Are both columns being accounted for?

(2) If I take 200,000,000 rows / 200 steps, I essentially have 1,000,000 rows defined in each histogram step. This seems large enough that it could cause estimation issues, right? What about sort spills to tempdb and things like that?

(3) Would manually created statistics/filtered statistics be an avenue to explore? How does one go about deciding what type of filter to apply?

With a table that large I would consider partitioning.  Unfortunately I can't answer your specific questions (1-3) but in general one of the benefits of using partitioned views (instead of native partitioning) is that the individual tables within the partitioned view are considered separate objects and have their own statistics each with 200 steps.  Here is a post where Kimberly Tripp of SQLSkills recommends for large tables that you consider not only partitioning or partitioned views but combining the two.

In case you don't know a partitioned view is one where you have multiple tables each holding a portion of the data and a view on top with UNION ALLs to combine the tables together.

Here is another of Kimberly's blogs on statistics in case you are interested.  It should help you answer some of your other questions.

Here's an article of Connor Cunningham regarding statistics: Statistics, Damned Lies, and Statistics – What is Statman?


Tempdb Add files require Restart Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for production I would like to be sure everything works properly. Some are stating conditions, not always necessary, sometimes necessary, I am reading resources below:

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0

Do You Need an Outage to Add tempdb files? Brent Ozar
https://www.brentozar.com/blitz/tempdb-data-files/
"Technically, not always. But practically speaking, yes. If you have to shrink files, that’s very difficult while SQL Server is in use, and we’ve seen cases where anti-virus/file control tools didn’t act up until SQL Server was restarted after the files were added."

http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/
"Once the additional files have been created, it’s not always necessary to perform a service restart.  However, in many production environments, simply modifying the filesize of the existing TempDB datafile (tempdev) is not possible without restarting services first. "

The following resources are saying no restart required:
https://www.codykonior.com/2015/08/10/modifying-tempdb-database-files-without-a-restart/
http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/

ADD - no outage required.  Although as Sean from Microsoft pointed out, SQL will prefer to use the lower filled files.  If you are going from 1 data file and adding more, then SQL will use the new ones for a while, but your performance won't be worse than only having one file.  However, if you have 2+ already and add one more, then it will hotspot on the new one and decrease performance.

SHRINK - may require an outage, depending on load.  The tail of the file needs to be empty which may not happen in a busy server.

GROW - no outage required although you may prefer to take one to avoid the performance penalty.  Instant File Initialization will mitigate this.  As Aaron pointed out, it will only mitigate for data files, but since you aren't going to add more than one log file (not useful)

REMOVE - may require an outage (has to be shrunk/emptied first)

MOVE - requires an outage since you need to restart SQL to make it start using the files in the new location.

EDIT - Added details about HotSpotting.

When SQL Server has more than one TempDB data file, it looks at them and selects the one with the lowest amount of data to use (sort of load balancing).  So, in the scenario where you have two or more existing data files and add a new one, SQL will observe that the one you just added is lowest filled and will utilize that one almost exclusively until it reaches the same level as the others.  This will negate the advantages of having multiple tempdb files as all of the activity will concentrate on just the one.  Basically, you will see tempdb contention (Pagelatch on tempdb) for the file you just added until it reaches equilibrium.

If you have 1 file and add 1 or more to it, then this will still occur (SQL will prefer to use the one that is lowest filled) but as you were going from one file with the contention, you won't see any downtick in performance, but you won't see any uptick in performance until the files all reach equilibrium.


restoring SQL Server from backup into a database with different database name? I have been provided SQL Server backups to restore but they have to be restored into new databases with different names on same server.

To explain it, say I have database "MyDB" and its backup "MyDB_full.bac" has been provided. I want to restore it into "MyDB_new".

What I have tried ?

I have tried to restore  it in SSMS but was unsuccessful, since I m getting error message :

  the backup set holds a backup of a database other than the existing

Kindly note I don't want to lose basic database "MyDB".

I found this link to be particularly helpful. I'll summarize below:
you are probably trying to restore the database on another existing active database. You can run the following query (albeit with your details filled in) to restore your database:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE


How do you achieve a physically sorted table file organization? Do I just declare an index on a column in the schema like ?

ALTER TABLE `table` ADD INDEX `index_name` (`colDate`)

I want the table records to be physically sorted not just when doing an ORDER BY query.

I believe it would be best practice to index on a date if I will be consistently query a range of dates or any query regarding a sequential range. Am I correct in believing this?

According to Chapter 7, Pages 148,149, under Storing Tables in Column-Sorted ORDER of the Book

By using ALTER TABLE ... ORDER BY statement, you have control over how MySQL stores the physical data for your tables, regardless of whether an index is in place.

You are theoretically correct, but the Storage Engine would have the final say on organization. The above statement I quoted applied to MyISAM. What about InnoDB ? Page 149 ends the chapter with

If the table had been defined with InnoDB instead of MyISAM, this would have no effect: InnoDB always stores its data by the Clustered Key.

The Clustered Index is essentially a rowid index as the MySQL Documentation says

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

This is why doing ALTER TABLE ... ORDER BY has no effect on InnoDB tables. Even if the rows get physically reordered, rows will be accessed through the Clustered Key by rowid anyway.
YOUR ORIGINAL QUESTION
Looking back at the index you made, the following can be realized

If you always enter data with increasing date values, the index simplifies doing date range scans.
If you load data from earlier dates, only the index can help you with range scans.

The only way to physically reorder the Clustered Key would be to run this
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO  mytable_new SELECT * FROM mytable ORDER BY colDate;
DROP TABLE mytable;
ALTER TABLE mytable_new RENAME mytable;

This would not be in your best interest to do this unless this is a one-time reordering. My advice would be to just stick with creating the index and let MySQL do all the necessary heavy lifting. In addition, you will need to run ANALYZE TABLE mytable; once a week to ensure the latest index statistics before running large date scans.


SQL Server displayed 'Grantor' vs system tables result When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?

My system tables join:

select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
    left join sys.server_principals prin
        on perm.grantor_principal_id =prin.principal_id
    left join sys.server_principals prin2
        on perm.grantee_principal_id =prin2.principal_id

The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.

SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.

You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.

I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:

DECLARE  @_msparam_0 nvarchar(4000);

SET @_msparam_0 = '##MS_AgentSigningCertificate##';

SELECT ascii(prmssn.state) AS [PermissionState]
    , null AS [Code]
    , grantor_principal.name AS [Grantor]
    , prmssn.type AS [SqlCodePP]
    , CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
                CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
                    WHEN 'R' THEN CASE prmssn.class
                    WHEN 4 THEN 201
                    ELSE 301
                END
            WHEN 'A' THEN 202
            ELSE
                CASE prmssn.class
                    WHEN 4 THEN 200
                    ELSE 101
                END
            END
        ELSE prmssn.class
        END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
    INNER JOIN sys.server_principals AS grantor_principal ON
        grantor_principal.principal_id = prmssn.grantor_principal_id
    INNER JOIN sys.server_principals AS grantee_principal ON
        grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
    AND (grantee_principal.name = @_msparam_0);

This contains the same joins as in your query.  I've reformatted the query for readability.

On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.


Question Regarding Proper Failover to Asynch Server in Availability Group We are in the process of moving datacenters so we want to properly failover our Primary server to an Asynchronous Server in our new datacenter so we can drive the existing gear out there. Here is my setup

Datacenter Old
SQL01 (Primary) (Current Vote 1)
SQL02 (Secondary) (Current Vote 1)

Datacenter New
SQL03 (Secondary Asynchronous) (Current Vote 1)

I also have a File Share Witness configured on a domain controller in our Office that both networks can see.  So this also has a vote.

On our cut over day we are going to failover our Primary to SQL03 so we can run our system in the New Datacenter while we drive the existing gear out.

Once I failover I need to shut down SQL01 and SQL02, drive them out to new location, change their IP's and bring them online.

Questions:

Should I remove SQL01 and SQL02 from the availability group first then re-add them once I bring them online at the new location?

Secondly How will this work with my voting seeing I currently have 4 vote and will be going down to 2?

Will this effect the majority vote?

On our cut over day we are going to failover our Primary to SQL03 so we can run our system in the New Datacenter

Before you do this, change the synchronization type to be synchronous and let the secondary catch up. Then, when the secondary is synchronized you can failover without issue.

Should you failover while the replica is still set to be asynchronous it will not guarantee that the databases in the AGs are all caught up to the same time. Additionally it'll cause you more issues as all database replicas will have their data movement suspended.

  Should I remove SQL01 and SQL02 from the availability group first then re-add them once I bring them online at the new location?

If it were me, I would. Even though driving them there doesn't "sound" like it's very far away - who knows what else will happen. When you remove the replicas the databases will go back into a restoring state so it should be fairly easy to add them back in.

  Secondly How will this work with my voting seeing I currently have 4 vote and will be going down to 2?

If you're using Windows Server 2012+ then dynamic quorum will be enabled by default. If you shut the servers down one by one in a controlled fashion then dynamic quorum will kick in and automatically readjust the votes. You won't have to do anything.

Additionally if you're using Windows Server 2012R2+ dynamic witness will also be on by default, thus giving or taking away witness votes as needed. Again, because you're doing this in a controlled fashion there shouldn't need to be any extra involvement or customization for the short period of time.


mongodb Date strings and other issues I asked a question on Stackoverflow, and was told that my schema was way off. I am using mongodb to store whether or not a log file exists. The log file contains info for an hour. I'm not actually storing the file, just the file's name and the hour it represents. I'm also determining the existence of data for a given hour if that file exists in my db. I don't deny my document structure is bad, but:

What is a better way to do the schema I have (below)? Each file represents an hour. The DB just has to know that a file exists for a specific day and hour. My webapp pulls this info to display available days and hours for the user.
Why is my schema so bad?

Current document structure:

{
    _id   : "2014-02-13",
    hours : [
              { hour: "00", file: [
                     {name:"something.csv", date_added : ISODate("2014-04-04T08:59:34.268Z")},
                     {...},
                     {...}]
              },
              { hour: "01", file: [{},{}]},
              { hour: "04", file: [{},{}]} // some hours can be missing
            ]
}

Is very bad for lots of registers... if you need accessing a file, need typing db.collection.find({"hours.hour":"05", "hours.file.name":"name"}). And you hours arrays will grow  very fast...

You can having a struct like this,

{
    _id   : {date:"2014-02-13",hour: "00"}, files: [
                     {name:"something.csv", date_added : ISODate("2014-04-04T08:59:34.268Z")},
                     {...},
                     {...}]
 }

Having a documents with a array having little data and where you find very fast...


In MySQL, how to execute two different SELECT statements depending on a condition? SET @input1=123;
SET @input2='foobar';

IF @inout1>0 THEN

SELECT
    @result := col12
FROM
    table1
WHERE
   col11=@input1;

ELSE IF @input2 != ''

SELECT
    @result := col21
FROM
    table2
WHERE
    col22=@input2;

END

Basically depending on if @input1 or @input2 is set and if the set value satisfy certain condition, one of the two SELECT statements should be run. But I just couldn't get it to work with IF THEN ELSE statement in MySQL. What should be the right syntax?

SELECT *
FROM ( full text of query 1 )
WHERE (condition 1)
UNION ALL
SELECT *
FROM ( full text of query 2 )
WHERE (condition 2)
  AND NOT (condition 1)

Neither condition must give NULL (due to NULL in field/variable values) in NOT.

Outer SELECTs for each separate subquery in UNION may be excess if separate queries are not complex.

For the code shown in the question it will be

SET @input1=123;
SET @input2='foobar';

SELECT @result := col12
FROM   table1
WHERE  col11 = @input1
  AND  @input1 > 0

UNION ALL

SELECT @result := col21
FROM   table2
WHERE  col22=@input2
  AND  @input2 != ''
  AND  COALESCE(@input1, -1) <= 0; /* prevent ejecting by NULL */


How does the transaction log work in SQL Server when i run a DML I would like to ask you something about a query execution in SQL. in general, I know that when I run a SELECT query I mean a view, this is loaded in cache memory then is parsed, compiled and then the query optimizer applies improvements based on execution plans so for that we have also the support coming from buffer pool (storage engine and relation engine). So, all that happens in memory and finally the result set is sent to SQL client.

Otherwise, we have the log file in a DB and here is my doubt because I don’t know if the log file in SQL is also used in queries ‘SELECT’ executions. Or it’s just used in transactions like an update, delete or insert so I really would like to know the role of transactional log in SQL when I perform a query (view) and also his role in the other DML transactions.

Yes, SELECT statements can in fact use space in the tempdb transaction log, as temporary working space to complete the query:

The Transaction Log (SQL Server):

  Long-running transactions might also fill up tempdb's transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.


Loopback Linked Server Is there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ?

I google it for several minutes and I find nothing about it.

Basically Loopback linked servers are mainly useful when testing an application that uses distributed queries on a single server network.

Note that they cannot be used in a distributed transactions.

In terms of performance, there will be performance impact when you do selects or joins using LOOPBACK server as all the data has to go across wire.

For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

Bottom line is linked servers are evil in terms of performance. Unless you really need them, dont / avoid using them.

For e.g -- Consider a basic example of selecting some data from a table

-- taken from : Aaron's article : "Best approaches for running totals – updated for SQL Server 2012"
-- http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals
CREATE TABLE dbo.SpeedingTickets
(
    [Date]      DATE NOT NULL,
    TicketCount INT
);
GO

ALTER TABLE dbo.SpeedingTickets ADD CONSTRAINT pk PRIMARY KEY CLUSTERED ([Date]);
GO

;WITH x(d,h) AS
(
    SELECT TOP (250)
        ROW_NUMBER() OVER (ORDER BY [object_id]),
        CONVERT(INT, RIGHT([object_id], 2))
    FROM sys.all_objects
    ORDER BY [object_id]
)
INSERT dbo.SpeedingTickets([Date], TicketCount)
SELECT TOP (10000)
    d = DATEADD(DAY, x2.d + ((x.d-1)*250), '19831231'),
    x2.h
FROM x CROSS JOIN x AS x2
ORDER BY d;
GO

SELECT [Date], TicketCount
    FROM dbo.SpeedingTickets
    ORDER BY [Date];
GO

--- select data as normally you would do

select [Date], TicketCount from test_kin.dbo.SpeedingTickets

Execution plan will have a clustered index scan

-- select data using loopback server (highly inefficient). Look at the CPU & Operator cost (though this is a simple example, but test out on a good data set with multiple joins, etc - you will clearly understand the difference - as to how expensive it is to use Linked (in this case loopback server))

Read up on :

Which one is more efficient: select from linked server or insert into linked server?
How to create an autonomous transaction in SQL Server 2008
CSS article - If you use linked server queries, you need to read this….
Bad database practices: abusing linked servers


Add constraint on number of entries of certain type in referenced table As a personal project I am working on a database to keep track of cars as they are manufactured and then sent to dealerships. One of my constraints is that certain dealerships can only have a certain number of cars of the same make. For example, DealerA can have 12 Hondas, 10 Audis, and 8 VWs. I have a table which keeps track of all the manufactured cars and then is updated with the dealership when it is shipped. I want to add a constraint in the database which stops you from changing a car's dealership field to a given dealer if the number of cars of that type is already at the dealership's limit.

In other words, if the count of cars of typeA assigned to dealerA (in the Car table) is less than typeA constraint for dealerA (in Dealership table) then allow another car of typeA to be assigned to dealer A, otherwise do not allow the change.

I am quite new to SQL and I am using this project as a way to teach myself.

What you're looking for is called an assertion. While these are in the ANSI standard few DBMS implement them.

The closest alternative is to write code in an AFTER trigger that throws an exception if the desired condition isn't met.


Oracle Query execution finishes first time but hangs up from second run We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time.

complex view - view with nested views, lots of inner and outer joins, multiple union all etc... But no locking statements, only pure read.

$ sqlplus sys/system as sysdba;
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set current_schema = DB_2017_2018;

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 33 sec

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 3 minutes

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 1m 30 sec

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 5 - 10 minutes

-- ALL THESE COMMANDS / QUERIES RUN IN SAME SESSION

I figured that running the exact same query second time is when the problem occurs. If I simply change any select column or where condition, then it runs for first time and hangs up from second time (see the above commands).

Suspect 1

One thing I suspect from this is may be it's related to oracle's cache. So when I run the following commands before each query execution, I didn't get any query hang up.

SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

Suspect 2 (root cause might be Suspect 1)

Once any query hangs up, even if I run the same query in another session, it still hangs up. So If I kill the active sessions that's running the hang up query, then I can run the query successfully only for first time but stuck with same problem from second run.

SQL> ALTER SYSTEM KILL SESSION '12,256' IMMEDIATE;

AFAIK, only thing I can be sure is that something is badly wrong with oracle's buffer_cache and shared_pool in our version.

This problem doesn't happen for tables or simple views, only for this complex view (didn't tested other complex views). Also we were facing this kind of strange issue only in last 2 - 3 weeks. It's hosted in cloud (AWS EC2), so it may be related to spectre / meltdown patches by Amazon as that's what changed in last 2 - 3 weeks (unrelated ?!). Or might be due to some db data threshold level reached in that period.

Ready to provide any other analysis or metrics

This is quite typical on 11.2 because of a feature called "cardinality feedback". Check the execution plan of the slow runs, and if you see "cardinality feedback" in the Notes section of the execution plan, try setting the below parameter: alter session set "_optimizer_use_feedback"=false;, and run your tests again.

Some bugs related to this:

Bug 12557401 - Suboptimal plan on 2nd execution caused by cardinality feedback (Doc ID 12557401.8)

Bug 16837274 - Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)

Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback (Doc ID 8608703.8)

Bug 8521689 - SubOptimal execution plan on second execution of GROUP BY query (Doc ID 8521689.8)


It seems our sql database is corrupted Is there a way to completely check a SQL-Anywhere database (5.5.05) integrity?

"Validate" won't help: we had databases that were broken but "validate" always told us that everything is o.k.

I had the idea to simple read every record in every table (first "select count(*) from. ", second "select * from. ") Number of readed records must be same as the number of records reported by the "count(*)".

This works ok (I can report errors) - but I have found a database that will "hang" on the "Count(*)" (seems to loop forever, CPU-Usage 100% for >20Min (than I stopped it)

Any other ideas?

Try unloading and then reloading the database - if that runs OK then the re-built database will be OK. Look at the docs for details of the dbunload.exe utility.

If you need more help then you can try posting on the dedicated SQL Anywhere forum http://sqlanywhere-forum.sap.com/, but don't forget that v5.5 hasn't been officially supported since 2002 - current version is v16!


How can I disable xb_doublewrite? Getting error while service restart.

  [ERROR] InnoDB: A parallel doublewrite file /test/mysql/xb_doublewrite found on startup.

You have to look in my.cnf for the variable  innodb_parallel_doublewrite_path
You should also set this
[mysqld]
innodb_doublewrite=0

Then, restart mysql
After restarting mysql, you can then delete /test/mysql/xb_doublewrite
CAVEAT
What is the purpose of this feature ?
According to Percona Server 5.7 performance improvements

Parallel doublewrite buffer. For ages, MySQL has had only one
doublewrite buffer for flushing data pages. So even if you had several
threads for flushing you couldn’t efficiently use them – doublewrite
quickly became a bottleneck. We’ve changed that by attaching two
doublewrite buffers to each buffer pool instance: one for each type of
page flushing (LRU and flush list). This completely avoids any
doublewrite contention, regardless of the flusher thread count. We’ve
also moved the doublewrite buffer out of the system tablespace so you
can now configure its location.

So, if you do want it gone, remove innodb_parallel_doublewrite_path if it is there. Although not recommnded, you must add innodb_doublewrite=0 to disable the InnoDB Double Write Buffer altogether.


What is "Index Fragmentation Percent" a percentage of? I am familiar with the concept of index fragmentation and how to fix it, but I'm not really sure of one specific item.

When you look at index fragmentation, it's always reported in terms of Percentage.

What is that Percentage a percent of?

It's going to be logical fragmentation for indexes, and extent fragmentation for heaps.  The BOL reference on sys.dm_db_index_physical_stats actually gives pretty good information on the topic:

  Logical Fragmentation

  This is the percentage of out-of-order pages in the leaf pages of an
  index. An out-of-order page is a page for which the next physical page
  allocated to the index is not the page pointed to by the next-page
  pointer in the current leaf page.

  Extent Fragmentation

  This is the percentage of out-of-order extents in the leaf pages of a
  heap. An out-of-order extent is one for which the extent that contains
  the current page for a heap is not physically the next extent after
  the extent that contains the previous page.

  The value for avg_fragmentation_in_percent should be as close to zero
  as possible for maximum performance. However, values from 0 percent
  through 10 percent may be acceptable. All methods of reducing
  fragmentation, such as rebuilding, reorganizing, or re-creating, can
  be used to reduce these values. For more information about how to
  analyze the degree of fragmentation in an index, see Reorganize and
  Rebuild Indexes.

Think about an index, as highlighted above it's the "percentage of out-of-order pages in the leaf pages".  An index page is going to be a doubly-linked list.  In other words, each page points to the next page and the previous page in it's respective index level.  Here's a picture on what a doubly linked list looks like (for illustrative purposes):

Note: the above image was just randomly pulled from the internet, it's a simple representation.  But it does lack A's next pointer, and F's previous pointer.  I didn't want any confusion that it's exactly how a leaf page in a SQL Server index would look like.

This structure is going to represent the logical flow of the data.  In other words, what page comes next regardless of what the physical reality is.  That logical fragmentation comes into play when the actual physical next page isn't what the next page is pointed to by the current leaf page.

Given the above definition, say you have four index pages.  1 out of 4 of those index pages next page pointer is not the physical page next to it, but the other 3 do have that physical nature.  In that case, your percent of fragmentation would be 25%.  Of course, that was a simple example and in reality you wouldn't care about an index less than thousands of pages.  But hopefully that makes it a little clearer exactly what that number represents.

Here's a few good reads on Clustered Index Structures, Nonclustered Index Structures, and Heap Structures.


How do dirty reads at both the transaction and table level provide increased performance? I ran the following code past our architect as I thought there may have been a tautology between the isolation level and table hint:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT  p.Forename ,
        g.Descriptor AS PersonGender
FROM    dbo.Person p WITH ( NOLOCK ) ,
        dbo.Gender g WITH ( NOLOCK )
WHERE   p.GenderId = g.Id;

I am assured by the architect (and I have no reason to doubt him) that this is for high volume environments and helps the optimiser better decide about how to improve the performance of the query.

So, some questions:

How does it do this?
Which versions of SQL Server support this?
Is this a documented or undocumented feature?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at the transaction level or at the session level while NOLOCK is a query hint.

You mentioned that you fully understand dirty reads, so using at the transaction level is what I would recommend.

if you want dirty reads on some tables only, then NOLOCK hint will help you.

SQL server 2005 and up allows you to use SNAPSHOT isolation where readers don't block writers and it uses row versioning with some penalty on tempdb.

essentially SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and NOLOCK are same thing but the use depends on the context of what you are using.


How do I figure out what is consuming space in my Zenoss DB (mysql) I have been running Zenoss 4.2 and the filesystem has never exceeded 20gb. In the past 3 days it has shot up to 90gb and I am almost out of disk space. It appears the used space is all in /var/lib/mysql/ibdata1. First, how can I tell what is causing the system to grow so quickly, and how can I cleanup the space?

1.) Identify the largest tables

Find out the largest tables on your DB MySQL Server - run this query as DB "root":

SELECT CONCAT(table_schema, '_', table_name)                                          DB_DBtable,
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    data_size,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   index_size,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

2.) Find the reason/solution of the largest tables

It depends on the largest table. Probably the largest table will be zep_events*. In this case you should to tune your Zenoss events settings.

Note 1: You have a size problem with the InnoDB tables, so it wont be easy to release InnoDB space. You can delete rows from the table, but space won't be released. See Deleting & Reclaiming space from InnoDB table

Note 2: I recommend you to use http://www.zenoss.org/forum or IRC #zenoss for Zenoss specific questions


Using MySQL 8.0 "Upgrade Checker" on RDS The MySQL 8.0 Shell came with an "Upgrade Checker".

Useful to run before upgrading, as you may detect incompatible issues with your table structure / etc.

Running its python version from the shell be like:

util.check_for_server_upgrade()

Unfortunately, when using AWS's RDS, we get the following error:

  SystemError: LogicError: Util.check_for_server_upgrade: The upgrade
  check needs to be performed by user with ALL privileges.

What can be done?

You can run util.check_for_server_upgrade() with a Mysql user who has the ALL PRIVILEGES. As you are using AWS RDS your master MySQL user don't have SUPER user permission, that's why your are getting this error, you can check your user permission with SHOW GRANTS FOR 'user'@'host'.
Only rdsadmin user have the ALL PRIVILEGES in AWS RDS MySQL instance. rdsadmin is amazon's own user that is used by Amazon to perform release or version update, taking backups and other maintenance tasks. Amazon's support should be able to advise you better in this topics.


When to make changes to the cost threshold for parallelism While examining a performance issue , I have seen an influx on CXPACKETS suggesting I might need to look at the cost threshold for parallelism and perhaps the MAXDOP.

Before making any drastic changes to the MAXDOP I have following the advice of many others including that of @mrdenny in the answer to CXPACKET Waits performance tune for SQL Server 2008 and @aron-Bertrand 's answer from Dealing with CXPACKET waits - setting cost threshold for parallelism. I have added to the maintenance to update the statistics fully on a nightly basis. This feels like a sensible move.

However, making modifications to the cost threshold is still something which niggles me.

At what point should the cost threshold for parallelism be altered? Does any one have an example of where (after examining the cost of their queries and workload) they made change to this cost?

Apologizes if this is something which which has been answered in a previous question.

Thanks!

Using MAXDOP = 1 can be a help, but it is a big gun.  It may be that the actual problem is the usefulness of the indexes.  Perhaps a new or a different index would resolve the problem.

Following Mr Denny and Aaron Bertrand's comments, did you discover what other waits in that connection were likely the cause of the CXPACKET waits?

Jonathan Kehayias suggested a query that might help you assess you parallelism experience and make a more thoughtful decision.  But you should also read the conversation between Jonathan and Paul White.

https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/


MongoDB switch to smallfiles I've reached a point where mongo fails to start because there's no enough free disk space. The log suggests using the --smallfiles option to start the mongod service.

So, I have two options, either make the hard drive bigger (it's an Amazon free instance) or doing what the mongodb log suggested.

Would data get corrupted if I enable --smallfiles for an existing mongo instance? I wouldn't want to lose any data.

Straight to the point? The smallfiles option doesn't help. It doesn't automagically shrink existing files. All this option does is changing the behavior of how data files are preallocated.

Usually, the first datafile allocated has a size of 64MB. The next has 128MB. This doubling goes on until 2GB are reached. After that, every new datafile is allocated with 2GB.

The smallfiles option simply sets the initial size to 16MB, and the doubling starts from there.

You need either to increase disk space or start to shard.


Designing User & its Sub User Database Diagram Problem There are some business category e.g. Travel Agency, Hotel, Car Rental etc.
Roles:

Management rolesSuper Admin, Staff
Customer side roles are Owner, admin and sales counter
May be etc. customer side roles will be added

Status i.e. On-Hold, Active, In-Active, Blocked, Deleted etc.
Scenario:

Each user in the database has its role and status with other credentials
Super Admin or Staff creates a customer (USER) with category e.g. "Travel Agency" and provide owner details
Now this Travel Agency (USER) has its own SUB_USERS that may be one or many

Now I am confused between user and sub users.
The work that I done so far is as following in shape of ERD:

Please put me on the right path as to how can I create a USER with its SUB USERS?
Thanks in anticipation

Why not just get rid of the main_user and sub_user tables and have a nullable field on the users table called parent_user_id. Then you can use a self-join on users.parent_user_id = users.user_id to get all the sub-users for a given Travel Agency. (When the parent_user_id field is null, that's a Travel Agency.)


Is matriculation number a good primary key? I am trying to design a grade assignment database and i am facing the following dilemma.

I have a student table which has the following characteristics:

matriculation number which i know for a fact is unique.
first name
last name
course of studies(Bsc Mechanical engineering, Msc      mechanical
engineering, Bsc computer science etc...)
email
remark Maybe some special notes about a particular student.

However there is also the following condition. Every student can at the same time belong to two different course of studies, that is a student can be Bsc and Msc mechanical engineering at the same same time. So after some thought i decided to create a separate table called course of studies, which has two columns an auto increment id(PK) and a course of study which will include all possible course of studies. Then connect the student and course of studies tables using a many to many relationship.

At the same time i thought that another implementation would be to use a surrogate key (auto increment id) on the student table and instead of creating a separate course of studies table i could simply incorporate it in the student table as a column and not have any problem with my primary key. Since if i had a student that belongs on two different course of studies i would have two different rows with a unique id

So my question to you is what do you think is the best implementation given the situation(Please explain why). I know that natural primary keys vs surrogate primary keys is a highly debated subject which doesn't seem to have a universal answer however given the situation what do you think is the best implementation?

Any additional details can be provided .

EDIT
There are two good answers that are saying more or less the same thing. I chose to accept the one with the more votes.

I would go with the surrogate key approach. I'm not familiar with matriculation number, but i expect it to be large enough so that having a surrogate integer might be the preferred alternative. Having a larger PK, means larger indexes, which means less performance for reads and writes, plus more space usage, and more space for referring tables too.
Go with the surrogate key.


Oracle Missing Comma error on check constraint I'm attempting to create a table in Oracle, but keep getting the "ORA-00917":missing comma error at the end of my first constraint (P_IMAGEFILE_CHECK). I've tried adding and taking away commas but nothing has worked, and I do not see how the statement is incorrect. Any suggestions?

CREATE TABLE P_SPECIES (
GENUS                         VARCHAR2(40 BYTE)         NOT NULL,
SPECIES                       VARCHAR2(40 BYTE)         NOT NULL,
COMMONNAME                    VARCHAR2(80 BYTE)         NULL,
SPECIES_DESC                  VARCHAR2(1000 BYTE)       NULL,
SPECIES_IMAGEFILE             VARCHAR2(20 BYTE)         DEFAULT 'NOIMAGE.JPG' NOT NULL,
CONSTRAINT                    P_IMAGEFILE_CHECK         CHECK
              (REGEXP_LIKE(SPECIES_IMAGEFILE(UPPER('[a-zA-Z0-9._%-]+.JPG')))),
CONSTRAINT          P_SPECIES_COMMONNAME_UK1            UNIQUE(COMMONNAME),
CONSTRAINT          P_SPECIES_PK                        PRIMARY KEY(GENUS, SPECIES),
CONSTRAINT          P_SPECIES_FK1                       FOREIGN KEY(GENUS)
              REFERENCES P_GENUS(GENUS)

);

It's your regexp_like expression that is invalid. You're using your column as if it was a function name. The syntax is:

regexp_like(thing_to_check, pattern [, options])

You should write:

regexp_like(species_imagefile, '[A-Z0-9._%-]+.JPG', 'i')

'i' is for case-insensitive matching.


Slow query performance on large table with no unique keys I have a table that has approximately 20 million records and I'm having a very hard time improving the performance of the following query. It's taking well over a minute to run.

SELECT  a.batch_date, a.batch_number, sum(a.gross_amt) AS gross_amount,
        (sum(a.trans_fee)*-1) as trans_fee, sum(a.net_amt) AS net_amount
    FROM  cc_detail a
    WHERE  a.company_id='1'
      AND  a.account_number='999999'
      AND  (a.batch_date BETWEEN '2015-10-01' AND '2016-01-31')
    GROUP BY  a.batch_date, a.batch_number
    ORDER BY  a.batch_date DESC, a.batch_number
    LIMIT  0, 10

Here is the table structure:

CREATE TABLE `cc_detail` (
    `company_id` int(11) NOT NULL,
    `account_number` int(12) NOT NULL,
    `location_number` int(12) NOT NULL,
    `batch_date` date NOT NULL,
    `batch_number` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    `gross_amt` decimal(15,6) NOT NULL,
    `trans_fee` decimal(15,6) NOT NULL,
    `net_amt` decimal(15,6) NOT NULL,
    KEY `company_id` (`company_id`),
    KEY `account_number` (`account_number`),
    KEY `location_number` (`location_number`),
    KEY `batch_date` (`batch_date`),
    KEY `batch_number` (`batch_number`),
    KEY `ca` (`company_id`,`account_number`),
    KEY `cab` (`company_id`,`account_number`,`batch_date`),
    KEY `cb` (`company_id`,`batch_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The server has 3.5 GB RAM and is running MariaDB 10.1. One issue with this table is that it does not have a primary or unique key. Due to the source of this data not having a unique key, I can not create one (unless someone has suggestions on ho to do this safely). This table is updated multiple times a day by deleting records within a date range and inserting data within that same date range.

Here is the result of the Explain statement using the above query:

id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: company_id,account_number,batch_date,ca,cab,cb
key: ca
key_len: 8
ref: const, const
rows: 1
Extra: Using where; Using temporary; Using filesort

INDEX(a) is unnecessary when you also have INDEX(a,b). So get rid of these:

KEY `company_id` (`company_id`),
KEY `ca` (`company_id`,`account_number`),

ca is a good index, but cab is better; removing ca will encourage the optimizer to use cab.  How many rows are in the result set?

Was the SELECT slow during one of those big DELETE + INSERT times?  If so, please provide those queries for further discussion.


Preventative measures to avoid wrong cardinality estimate statistics This is a follow-up to Queries randomly getting slow, good preventive practices?, attempting to ask a more specific question about one of the ideas stated there.
A common cause of queries suddenly becoming slow after a long history of being fast is outdated cardinality estimates. SQL Server takes advantage of cardinality estimates to figure out a query plan that will be the fastest to answer a given query. If a query that usually takes few minutes suddenly bursts to tens of hours because of outdated cardinality estimates, it might negatively affect business uses before support staff can respond.
Are there any measures that could be applied preemptively to find and fix outdated cardinality estimates before a query is executed? Are there any specific tools that could be periodically executed to go over estimates and fix them, or maybe some metrics to be observed that would indicate how good are the estimates in a database?

The first line of defense is a nightly statistics gathering process.  Ola Hallengren's rather ubiquitous set of maintenance scripts, for example, have a number of options for index and statistics maintenance.  That should keep your statistics from getting too out of date.
The next line of defense are the database settings to enable the automatic updating of statistics, to enable statistics to be gathered asynchronously, and to enable the automatic creation of statistics
alter database [your database]
  set auto_create_statistics on;
alter database [your database]
  set auto_update_statistics on;
alter database [your database]
  set auto_update_statistics_async on;

If auto_update_statistics is enabled, SQL Server will re-gather statistics that it deems stale (the threshold differs by version) when it encounters them.  If you allow that to be done asynchronously, the query that first encounters the stale statistics will use the old stats while a background thread updates the stats for a future execution.  In any sort of OLTP system, I'd much prefer that to the synchronous approach where the query that encounters stale stats waits to execute until fresh stats are available.  In a data warehouse system, I'd be a lot happier for the query to wait for fresh stats.
There are downsides to allowing auto stats updates, though.  When you refresh stats, you're going to force SQL Server to purge all the plans that use those stats from the cache and to recompile them which can be expensive.  You're also introducing additional background stats gathering processes that will consume CPU during the day.
A third line of defense involves a DBA or developer being thoughtful about updating statistics as part of a load or based on information they know about the system in question.  If you're writing a warehouse load, for example, it probably makes sense to update statistics as a final step when you know that you've made a large number of changes to a table (maybe you do this once a week rather than every day depending on the windows that you have).  If you're a DBA running an issue-tracking system and you know a bunch of queries are highly dependent on a particular statistic on a filtered index for all the open issues and you know that there are generally very few open issues overnight when statistics run but the number grows steadily to a peak around 2pm, and then slowly declines, you might schedule a job to update that particular filtered index statistic hourly.


Running total from another field I have a table just like that:

date      sales    rest
1/1/2017   52        0
2/1/2017   30        0
3/1/2017   10        200.

I only have the rest in the last date and i want to make it like this:

1/1/2017   52        240
2/1/2017   30        210
3/1/2017   10        200.

What I want to achieve to to calculate in a new column the rest for every date from the sum of the day's sales and the last days rest.

Can someone suggest a query ?

For SQL-Server & Postgres:

WITH RN AS
(
    SELECT date, sales, rest,
           COALESCE(LAG(sales) OVER (ORDER BY date DESC), 0) LG

    FROM sales
)
SELECT   date, sales,
         SUM(LG + rest) OVER (ORDER BY date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rest
FROM     RN
ORDER BY date

date       | sales | rest
:--------- | ----: | ---:
2017-01-01 |    52 |  240
2017-01-02 |    30 |  210
2017-01-03 |    10 |  200

For MySql:

SELECT date, sales, rest
FROM (
      SELECT t.date,
             t.sales,
             @acm := @acm + t.rest + @last_sales AS rest,
             @last_sales := t.sales
      FROM   sales t,
             (SELECT @last_sales := 0, @acm := 0) r
      ORDER BY t.date desc
      ) X
ORDER BY date;

date       | sales | rest
:--------- | ----: | ---:
2017-01-01 |    52 |  240
2017-01-02 |    30 |  210
2017-01-03 |    10 |  200

SQL-Server dbfiddle here

MySQL dbfiddle here

Postgres dbfiddle here


Seeting up a PostgreSQL database on another hard drive than my system one on OS X? I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.

Is there a way to create new database on another drive/partition (not the system one, where postgres is installed)?

How can I do that?

I very strongly recommend that you do not put a tablespace on an external removable drive. If the tablespaces disappears (ie: drive unplugged) you risk severe database corruption that will be difficult to recover from. You'll likely find permissions management a challenge, too.

You should generally initdb a new database cluster on the drive, then start it with pg_ctl. If you like you can change your launchd properties to auto-start the database (this will require messing with file permissions) or you can just manually start it with pg_ctl when you want to use it.

It may be easier to move the existing data directory to the new location and either create a symlink from the old location to the new one or edit your launchd configuration to tell the PostgreSQL launcher to look in the new location. If your postgresql.conf is outside the data directory you can just change the data_directory parameter in postgresql.conf instead.

See the PostgreSQL user manual for details on initdb and pg_ctl if you need to use them.

For how to set PostgreSQL up to start your new database on boot-up you'll want to do some learning about file permisisons and launchd properties. I don't use OS X much so I can't be of much help with this part; if you're stumped and you've done some reading to try to figure it out, consider asking for help on the OSX stack exchange site. Just remember to explain what you've tried and what you've done already. You won't need to do this if you just symlink it from the old location.


Display a field using regex I have a table with a field in with data such as:

id | path
1  | /2/9/1
2  | /2/4/11
3  | /1/3/11
4  | /5/3/2
5  | /2/1/4

I'm wanting to do a select query to show only the first number between the slashes.  Can you use a Regex in the select part of a query?  If so, what is the syntax to find what I need?

You can use SUBSTRING_INDEX() function for that, no need for regexes:

   SUBSTRING_INDEX( SUBSTRING_INDEX(path, '/', 2), '/', -1)


Reuse previous value in window function Is it possible to use LAG() to get the previous value of the current column?

The computation I need to do is the following:

value(n) = (value(n-1) + 1) * column(n)

meaning I already have a column, and I want to create a new one by multiplying the column by the previous value of the new column I am creating + 1...

First value would be 1.

Something like:

SELECT (LAG(col1) OVER (ORDER BY date) + 1) * col2 AS col1

The current cell value is defined by the previous value times another column.

If I try this I get undefined column col1 (of course since I am creating it in my query...)

In Excel it is extremely easily done with the following formula:

B2 = (B1 + 1) * A2

So, if the table has:

  date     | col2
------------------
2018-12-01 |  2
2018-12-02 |  3
2018-12-03 |  1
2018-12-04 |  4
2018-12-05 |  1.5

the result should be:

  date     | col2  | col1
-----------------------------
2018-12-01 |  2    |    1
2018-12-02 |  3    |    6           (1+1) * 3
2018-12-03 |  1    |    7           (6+1) * 1
2018-12-04 |  4    |   32           (7+1) * 4
2018-12-05 |  1.5  |   49.5        (32+1) * 1.5

This likely needs a recursive CTE, since you want the value of the column to depend on the value of the previous (calculated) value of the same column.
(Recursive CTEs are available in MariaDB since version 10.2.2)

I assume that (date) is unique, otherwise the result will be non-deterministic:

WITH RECURSIVE
  t AS
  (
    SELECT
        date, col2,
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM
        tableX
  ),
  cte AS
  (
    SELECT
        date, col2, rn,
        CAST(1 AS DECIMAL(10,3)) AS col1     -- starting value
    FROM
        t
    WHERE
        rn = 1

    UNION ALL

    SELECT
        t.date, t.col2, t.rn,
        (c.col1 + 1) * t.col2                -- calculate col1
    FROM
        t JOIN cte AS c
        ON t.rn = c.rn + 1
   )
SELECT date, col2, col1
FROM cte ;

Tested at: dbfiddle.uk


stored procedure update retain values In SQL Server, when using optional parameters, the only way to have default values is using @param = NULL upon creating.

Is there a way for the stored procedure to know that the application will only update specific columns and the remaining columns will retain the original values?

Here is my example:

Data: col1 = "AA" col2 = "BB" col3 = "CC"

Application update inside stored procedure: UPDATE table SET col3 = "C", col ="B" WHERE id = 1

After update, value of col1 will remain.

I hope my explanation is clear enough :)

If I understood correctly, if stored procedure is called with @vCol1 = 'AA', @vCol2 = 'BB' and @vCol3 = NULL then you want to update only Col1 and Col2 keeping the Col3 value intact.
Then you can go with the following solution. I agree its not the best one but it will get work done in simple manner.

UPDATE table
SET col1 = ISNULL(@vCol1, col1)
  , col2 = ISNULL(@vCol2, col2)
  , col3 = ISNULL(@vCol3, col3)
WHERE id = 1


How to backup PostgreSQL database (a schema) where a user has access to only one schema I have created a sales schema in Postgres.
(so in total there are sales and the default public schemas)

I added the user salesuser to Postgres, and granted it the access to the sales schema.

This is how I did that:

CREATE ROLE salesuser WITH LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE database_name TO salesuser;
GRANT USAGE ON SCHEMA sales TO salesuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales TO salesuser;
ALTER ROLE salesuser SET search_path to sales;

Now, I'm trying to do a backup of the schema sales using the pg_dump and the user salesuser.
The expected result is to backup only the schema sales, as it's the only schema the user salesuser is allowed access to.

The command I use is:
pg_dump -h localhost -p 5432 -U salesuser -d database_name > /tmp/2223.dmp (note there is no --schema= or -n parameter)

Unfortunately, somehow the pg_dump tries to dump also the public schema, to which the user salesuser doesn't have access rights to, as the error below is displayed:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation customer
pg_dump: [archiver (db)] query was: LOCK TABLE public.customer IN ACCESS SHARE MODE

I assumed that the pg_dump command with a user which has only access to a single specific schema, would backup only this schema, and other schemas (such as public) would be omitted.

I need to avoid defining the -n or --schema= parameter for the pg_dump.

I need to focus on access rights of the user with the use case to backup all the schemas the user salesuser is allowed to access to.

How is it possible?
What am I missing?

Looks like usage privilege on public schema has been revoked from salesuser. So, you are getting the permission denied error while dumping the data. RESTRICTING PUBLIC SCHEMA

pg_dump, in general will backup all the schemas(sales and public) of your database.
Hence, you need to explicitly mention -n sales or --schema=schema_name while taking the pg_dump and it will work fine.

You can issue command \dn+ in psql to view the schemas and access permissions.


How to debug a growing Redo Queue Size in the Availability Group Dashboard? The Redo Queue Size has been growing pretty big over the last couple of hours on my secondary replica's server (SQL16). There's nothing long running or heavy running that I've found over the last hour or so, so I'm not sure where this is coming from.

My index maintenance jobs ran last night which are pretty beefy and normally cause this "lag" in sync between the replicas but that usually clears up by the morning.

I log the Redo Queue Size and can see where it spiked through the night and then started coming down as per usual. The jobs finished hours ago, and the Redo Queue Size spiked to around 10 GB during the job and after the job finished came back down to around 1.5 GB, but now it's growing again even though nothing heavy has been running since.

Note: My secondary replica is setup as a synchronous replica.

There is no way to "debug" this other than to open an incident with Microsoft for investigation.  They may be able to get a series of process dumps or enable some more verbose logging to see if there is a hung thread, but you may have to leave it in the current condition for quite a while, so that is the downside.

Based on my experiences with the same symptoms, stopping and starting the SQL Server service on the secondary will resolve the issue (unless you have an underlying I/O issue that is causing it).  It may take a while for the database to recover as it will have to play all of the transactions in the redo queue, and during this time, replication will be paused.  You can track progress of database recovery in the error log, and once the database is recover, replication will resume.


PRIMARY index not used if too few entries this query :

EXPLAIN SELECT cmd.cmd_date, liv.liv_nom
FROM cmd
    LEFT JOIN liv USE INDEX(PRIMARY) ON cmd.liv_id = liv.liv_id
WHERE 1
ORDER BY cmd_date DESC
LIMIT 0, 20;

gives me a different result when the table liv contains 5 or 6 rows.

6 rows :

1   SIMPLE  cmd     ALL                 134331  100 Using filesort
1   SIMPLE  l       eq_ref  PRIMARY PRIMARY 4   cmd.liv_id  1   100

5 rows :

1   SIMPLE  cmd     ALL                 134331  100 Using temporary; Using filesort
1   SIMPLE  l       ALL PRIMARY             5   100 Using where; Using join buffer (Block Nested Loop)

I've read somewhere (can't find where anymore :/) that MySQL can choose to not use a primary if there is too few rows in a table (here the limit seems to be 5). Is there a way to disable this ? Because with the index the query takes 80ms, vs 4s without index :/

I'm using MySQL 5.7.9

Thanks

Here the 2 tables, simplified.

EDIT :

CREATE TABLE `liv` (
  `liv_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `liv_nom` varchar(100) NOT NULL,
  `liv_denomination` varchar(100) NOT NULL,
  `liv_public` tinyint(1) unsigned NOT NULL,
  `liv_slug` varchar(20) NOT NULL,
  `liv_ordre` tinyint(2) unsigned NOT NULL DEFAULT '99',
  `liv_id_crm` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`liv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

create table cmd
(
    cmd_id int(10) unsigned auto_increment
        primary key,
    str_id int(10) unsigned null,
    con_id int(10) unsigned null,
    pri_id int(10) unsigned not null,
    liv_id int(10) unsigned null,
    ... 20+ more columns with data only ...
    cmd_transaction_id int(10) unsigned null,
    constraint fk_bout_commande_bout_structure1
        foreign key (str_id) references structure (str_id)
            on update cascade,
    constraint fk_crm_commande_crm_contact1
        foreign key (con_id) references contact (con_id)
            on update cascade,
    constraint fk_crm_commande_livre_campagne1
        foreign key (pri_id) references prix (pri_id)
            on update cascade,
    constraint fk_crm_commande_famille_livreur1
        foreign key (liv_id) references liv (liv_id)
            on update cascade,
);

create index fk_bout_commande_bout_structure1_idx
    on crm_commande (str_id);
create index fk_crm_commande_crm_contact1_idx
    on crm_commande (con_id);
create index fk_crm_commande_famille_livreur1_idx
    on crm_commande (liv_id);

It seems you can force MySQL to use an index with force index, documented here.

First, make sure your statistics are up to date.

Then try something like this (untested)

SELECT cmd.cmd_date, liv.liv_nom
FROM cmd
    LEFT JOIN liv force index (primary) ON cmd.liv_id = liv.liv_id
WHERE 1
ORDER BY cmd_date DESC

Different index hints might work better. Expect different behavior when liv contains only 5 or 6 rows total rather than 5 or 6 matching rows out of thousands or millions of rows.


Postgresql: Insert trigger function fails on partial insert statement We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test db and explain everything to the rest later. I'm no dba, I'm a perl developer.

Focused on just one master table, I've produced a function to create a new children table, to create children tables for the existing information and to move things from master to children by batches. I don't think it's relevant, but our checks are on the year and month of the created field.

Now I'm building the insert trigger in the master database that would detect the period for the new record, look for the corresponding children table, creates it if missing, and inserts. Everything is ready but the insert is not working!

Our inserts statements are usually partial: will add the basic fields, do some things and fill in the rest (the fill in part will come later). So the insert statement is something like

INSERT INTO public.transactions ( created, tr_status )
VALUES (now(), 'created');

and leave Postgres to fill in the id. But in between created and tr_status, the schema has a field called modified. And, when the data gets to the trigger function, this field is empty. Trying to insert the row will throw an error:

EXECUTE (
    'INSERT INTO ' || child || ' VALUES ' || NEW.*
);

LINE 1: ...15_07 VALUES (123456,"2015-07-03 16:48:17.890627",,completed...
                                                             ^

How can I use in the trigger the same fields that were in the insert statement?

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;


What is the damage to the database if it uses high memory storage? Are there consequences for this use? Does it affect performance? What is the damage to the database if it uses high memory storage? Are there consequences for this use? Does it affect performance?
I need to understand a lot about this problem to be able to create solutions to it

To add on to danblack's point, it's generally good for your server's memory to be mostly consumed by the database system because accessing data from memory is faster than disk, but if you don't have enough memory to support your database system's workload then you can run into performance issues when it needs to pull from disk or compensate in other ways.
If you updated your question with the specific issue you're trying to solve then we can provide more specific information, but if you're just generally asking then you don't have to worry too much about this until you run into performance issues. There's a lot of other things that can equally or more likely affect performance before memory provision does.


Postgres Order By but only the most recent of a particular type Assume I have a PET table with three columns: Name, Type, and Date. I want to order the three columns by Date but only display the ONE most recent of a specific particular type ("Dog" for this example), all for the rest. For example, my raw list of data:

Name        Type        Date
Nemo        Fish        June 1, 2016
Fido        Dog         January 1, 2016
Felix       Dog         February 1, 2016
Whiskers    Cat         April 1, 2016
Marlin      Fish        August 1, 2016
Shifu       Cat         March 3, 2016

Would look like this when the query was applied.

Felix       Dog         February 1, 2016
Shifu       Cat         March 3, 2016
Whiskers    Cat         April 1, 2016
Nemo        Fish        June 1, 2016
Marlin      Fish        August 1, 2016

I'm sure there are multiple ways to do this, but only a few that are the most optimal from an efficiency perspective.

Solution 1

CREATE TABLE pet ( name text, type text, date date);

INSERT INTO pet VALUES
 ('Nemo', 'Fish', 'June 1, 2016'),
 ('Fido', 'Dog', 'January 1, 2016'),
 ('Felix', 'Dog', 'February 1, 2016'),
 ('Whiskers', 'Cat', 'April 1, 2016'),
 ('Marlin', 'Fish', 'August 1, 2016'),
 ('Shifu', 'Cat', 'March 3, 2016');

Using PostgreSQL-specific DISTINCT ON clause we can do this in a single table scan:

SELECT name, type, date
FROM (
  SELECT DISTINCT ON (
    type,
    CASE WHEN type<>'Dog' THEN date END
  )
  name, type, date
  FROM pet
  ORDER BY
    type,
    CASE WHEN type<>'Dog' THEN date END,
    date DESC
) AS subq1
ORDER BY date;

Solution 2

Using more ANSI-compatible SQL, like UNION and LIMIT. It will work on MySQL, DB2 and some others. Similar solution can be done on Oracle, just replace LIMIT with ROWNUM.

(
  SELECT * FROM pet
  WHERE type = 'Dog'
  ORDER BY date DESC
  LIMIT 1
) UNION ALL (
  SELECT * FROM pet
  WHERE type <> 'Dog'
)
ORDER BY date;

Notes on performance

With small tables (less than 1000000 rows), any solution will work. DISTINCT ON is slightly faster:

Iterations: 100000
Query:   /*q1*/ SELECT name, type, date FROM ( SELECT DISTINCT ON ( type, CASE WHEN type<>'Dog' THEN date END ) name, type, date FROM pet ORDER BY type, CASE WHEN type<>'Dog' THEN date END, date DESC ) AS subq1 ORDER BY date;
Time:    24.834 s (31%)
Average: 0.248 ms
Rows:    500000
Winner:  80914 times (80%)
Query:   /*q2*/ ( SELECT * FROM pet WHERE type = 'Dog' ORDER BY date DESC LIMIT 1 ) UNION ALL ( SELECT * FROM pet WHERE type <> 'Dog' ) ORDER BY date;
Time:    26.778 s (33%)
Average: 0.268 ms
Rows:    500000
Winner:  12881 times (12%)
Query:   /*q3*/ select name, type, date from ( select pet.*, case when type = 'Dog' then rank() over (partition by type order by date desc) else 1 end as rnk from pet ) as x where rnk = 1 order by date;
Time:    27.490 s (34%)
Average: 0.275 ms
Rows:    500000
Winner:  6205 times (6%)

With "big data", make sure your query does only one, indexed, table scan.


DB2 Defining remote database alias I'm using DB2 9.7.0.3 for AIX.

I want to select data from a remote database, also running  DB2 9.7.0.3 for AIX, and insert it into the local database I'm connected to.

I believe the query for this is

INSERT INTO SCHEMA.TABLE
    SELECT * FROM REMOTEDB.SCHEMA.TABLE

How do I define REMOTEDB as an alias to a remote database connection?

First you need to catalog the remote instance.

db2 catalog tcpip node <my alias node name> remote <host or host alias name> server <port>

Issue db2 terminate to make sure the catalog entry remains.

Now you can actually catalog the remote database.

db2 catalog db <remote database name> as <local alias> at node <node alias supplied above>

And once again issue db2 terminate

Here is an example of what this could look like:

db2 catalog tcpip node devinst1 remote devservera server 50100;
db2 terminate;
db2 catalog db crmdb as remotedb at node devinst1;
db2 terminate;

Now you can connect to that local database using either the db name (crmdb) or the alias name (remotedb) depending on your needs.

Note: Sometimes you can run into issues where it will say things about a null package id. This would mean that particular packages need to be bound for the connection to work. Easiest way to solve this is to have your instance owner id (of the remote database) make the first connection and then disconnect or to have your id you are using have BINDADD authority. Then the correct packages will get bound. You may not run into this, especially if you are on the same version and fix pack level of DB. If you ever differ between "client" and "server" is when you for sure have to do this.


What postgres sync solution should I use to duplicate production, but read it from another application? I'm a little out of my league with my current problem, but I'm looking for a solution where the following conditions are met:

My production database reads and writes as it currently does
My production application ALSO writes everything to a 2nd remote database
The writes to the 2nd database are frequent and near real time
I have a separate application that can read from the 2nd database

Both applications are rails, and the main application/database is the product. The 2nd application is a custom built analytics service that only reads from the 2nd database.

Which postgres solution(s) can support these requirements?  Thanks.

It sounds like streaming replication with a hot standby is the solution you're looking for.

Streaming Replication details the steps of how you go about setting it up. The hot standby option that they go over (they have it put to "on" which is what you will want) will allow you to run SELECTS against the 2nd database. Under this setup, however, your production application would only write everything to the master database ... postgres itself would then stream those changes to the 2nd database.


Structuring data in MongoDB for a to-do list type application This is a fairly high level question as I am in the initial stages of planning this application, and am also very new to MongoDB and document-based databases in general. Disclaimer aside, I am building a to-do list type application whose two main types of data are users and items (aka tasks or todo's), and my question is, what would be a good way to structure this data in mongodb, if I want it optimized more for read speeds rather than updates?

My initial thought, based on my limited knowledge of mongodb, is that since it allows embedded type data structures I could basically store it in a single collection like this:

{
    username: "joe",
    password: "...",
    items: [
        {
            text: "This is a task",
            completed: 1
        },
        ...
    ]
},
...

However, will this make it more difficult to update/remove existing items since they are not themselves stored as separate documents, and therefore lose things like auto-generated unique object ID's, ect? Also each user could potentially have a relatively large number of items associated with them (possibly 1000's). Is embedding large lists in parent documents like that a bad idea?

creating a sub collection should offer better read performance
see http://docs.mongodb.org/manual/core/data-modeling/

I think it would be a good idea to add an id to each subdocument so it is easier to access it (because from your schema you may have identical items)

like:

{
    username: "joe",
    password: "...",
    items: [
        subid:{
            text: "This is a task",
            completed: 1
        },
        ...
    ]
},
...

you can use subid = ObjectId() to autogenerate ids, or create this in your application.

I don't think there is a problem with having thousands of items in a document just remember that the maximum document size is 16 megabytes.


How to limit PostgreSQL INSERTs to assure that the caller has an attribute conistent with the parent table? In the parent table, there is a column that defines the 'owner' of the row. When inserting into the child, the caller provides an owner id or '%', to indicate that the the caller is the administrator. I was expecting the insert with this check to be slower that a straight insert, but I didn't expect a 70x penalty. Can you give me some ideas for how to optimize the performance to achieve the same result as this statement?
INSERT INTO child (parent_key, value1, value2)
  SELECT $1, $2, $3 FROM parent
    WHERE parent_key = $1
      AND owner LIKE $4
    LIMIT 1;

Table definitions:
CREATE TABLE parent (
  parent_key VARCHAR(255) PRIMARY KEY,
  owner VARCHAR(255)
);

CREATE TABLE child (
  child_key SERIAL PRIMARY KEY,
  parent_key VARCHAR(255) REFERENCES parent,
  value1 VARCHAR(255),
  value2 VARCHAR(255)
);

I ran an explain on my statement, and this is what I see.
 Insert on child  (cost=0.42..8.46 rows=1 width=1670)
   ->  Subquery Scan on "*SELECT*"  (cost=0.42..8.46 rows=1 width=1670)
         ->  Limit  (cost=0.42..8.44 rows=1 width=296)
               ->  Index Scan using parent_pkey on parent  (cost=0.42..8.44 rows=1 width=296)
                     Index Cond: ((parent_key)::text = '111'::text)
                     Filter: ((owner)::text ~~ '%'::text)

Since parent_pkey is a unique index, I would expect the LIKE filter to contribute an insignificant amount to the execution time. This conditional INSERT takes >70 times as long as an INSERT of VALUES. What would be a more efficient way of enforcing this constraint?

Nevermind. The long response time seems to be some aberration in the DB server. Running the same code a little later is doing the INSERTs is around 10msec pretty consistently. Sorry to bother you!


Database development/admin - What exactly should I be trying to learn? (I tried posting to programmers.se but was told this might be a better place) My apologies if this isn't true

I've been a bit weary about approaching learning databases. I've dabbled into them before, and "DATA" in itself appeals to me a lot. Maintaining/searching/moving, everything about it in the abstract sense I love. (This isn't a career question, this is a learning question.)

But as RDBMS's begin to be easier to maintain, and with tools that "anyone" can use to manage data I feared for the database admin jobs. (Yet I see jobs for SQL everywhere!). I know "No-SQL" was a big deal but it kinda has its niche.

But that leaves me here... unsure of really "what" to study. What tools should I have in my tool belt?

I'm sure RDBMS's will be around in both use and maintaining legacy code. But what else? Obviously data will always be around, but is this a secure field or a dying one? And what should I be concentrating on?

For development you should learn:

SQL including optimizations
DDL (table creations etc.)
plpgsql, PL/SQL, T-SQL (native database language to write procedures and functions)
good to know C, java, scripting languages
jdbc how it works
storage - how db store data and which solutions are faster then another
...

For administration
all above, scripting languages is a must
7. everything about operation system that you will work with
8. storage maintain
9. a lot of measure possibilities how server works (monitoring)
10. installations, upgrades - OS and db system
11. archive possibilities
12 high availability possiblities
13.....

and for both, a lot more things

About tools you have to know how to work with console very good in case that tools cannot connect at current time to db but you can connect to server itself.
Other tools are usually database system specific.


Oracle 11.2.0.3 Service Name Mismatch issue My Environment

During Installation

SID  lab3

Service_Name  lab3.domain.com

Changes

Some time ago I changed my service_name to just lab3 to match my SID

Select instance_name from v$instance;

lab3

Select value from v$parameter where name like '%service_name%';

lab3

Issue

when i try to connect from my application
jdbc:mercury:oracle://192.168.1.203:1521;servicename=lab3 doesn't work

jdbc:mercury:oracle://192.168.1.203:1521;servicename=lab3.domain.com works while this is not my service name anymore

My application doesn't require any oracle client installed so there is no tons file or anything else on the client side. it's all on server side

Error in Application Logs

java.sql.SQLNonTransientConnectionException: [Mercury][Oracle JDBC Driver][Oracle]ORA-12514 Connection refused, the specified service name (lab3) was not recognized by the Oracle server. at com.mercury.jdbc.oraclebase.ddcr.b(Unknown Source) at com.mercury.jdbc.oraclebase.ddcr.a(Unknown Source) at

lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 06-MAR-2016 20:34:40
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                06-MAR-2016 20:02:45
Uptime                    0 days 0 hr. 32 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\Administrator\product\11.2.0\network\admin\listener.ora
Listener Log File         C:\oracle\Administrator\diag\tnslsnr\Lab3\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Lab3.domain.com)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "lab3.domain.com" has 1 instance(s).
  Instance "lab3", status READY, has 1 handler(s) for this service...

Service "lab3XDB.domain.com" has 1 instance(s).
  Instance "lab3", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE

db_domain                            string      domain.com

SERVICE_NAME is made up of the values db_unique_name and db_domain parameters. Here is the reference to Oracle Documentation about SERVICE_NAME. Even if you set lab3 as service name oracle automatically adds value of db_domain on it. Your service_name is 'lab3' and db_domain is 'domain.com' so you end up with the orgianl service name.
Instead you can create tns service name to point that service. Example is given below

lab3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lab3.domain.com)
    )
  )

Then you can use this tnsname(lab3).


How to specify localhost for SSIS package source server? I have a job running on SQL Server 2012, where one of the steps is of type "SQL Server Integration Services Package". For the "Package source" I choose SSIS Catalog. For the "Server", if I type in the name of the server, everything works fine, but if I type in localhost I get an error.

How do I specify that the SSIS Catalog that I want to use is under the same server where the job is running?

You can reference the local server with a period .

If you have an instance name, too, then use .\instancename


How can I force SQL Server Management Studio to update the IntelliSense cache? IntelliSense in SSMS isn't updating unless I shut it down and restart. I've tried disconnecting from the server and reconnecting while the SSMS session is active, but that isn't working.

Steps to recreate:

Create a table
Select something from that table
SSMS displays
squiggly red lines under it with the tip "invalid object name"
Execute the query

How can I force IntelliSense to update without restarting SSMS?

You need to use this key combination:

  CTRL + SHIFT + R

Alternatively, use the menu item:

  Edit > IntelliSense > Refresh Local Cache

This article might also be useful (for future readers who have more perplexing IntelliSense issues):

Troubleshooting IntelliSense in SQL Server Management Studio


What's an "inline query"? According to the currently accepted answer to this question a partial solution to an efficiency problem was to "inline" the query. I can't tell what that is, I can't ask in comments (too little rep) and Googling 'postgresql inline query' isn't very decisive (some seem to think it's synonymous w/"subquery" but others don't).

In the FROM clause below, the subquery acts as a table:

SELECT * FROM (SELECT id, name FROM users)

That subquery is called an inline view. So in this case you are not directly selecting rows from a table, but from an "inline view".
 From your first link it seems that the user created an actual view, which you can do from a query. More information over here: https://www.postgresql.org/docs/9.2/static/sql-createview.html

But it seems that Erwin thinks it might be a better idea, performance wise in that specific case, to "inline the query" from the view. Or in other words, not by first creating a view but use inline views like I did.

Hope this helps.


Determine ANSI_DEFAULTS setting ON or OFF? How can I determine if ANSI_DEFAULTS is enabled or not on my server. My server is using IMPLICIT transactions and one way from which it can affect it based on setting of ANSI_DEFAULTS.

How can I determine state of ANSI_DEFAULTS?

ANSI_DEFAULTS isn't really a distinct option, but a logical grouping of several other session-based options:

IMPLICIT_TRANSACTIONS
CURSOR_CLOSE_ON_COMMIT
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON

You can use the @@OPTIONS configuration function to get the bit-masked value of the current Session settings, the values of which are noted on the Configure the user options Server Configuration Option MSDN page.

Putting those elements together gets you the following query which determines the current session setting of the "ansi_defaults" group of options:

SELECT @@OPTIONS AS [user_options],
       CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END AS [implicit_transactions],
       CASE WHEN @@OPTIONS & 4 = 4 THEN 'ON' ELSE 'OFF' END AS [cursor_close_on_commit],
       CASE WHEN @@OPTIONS & 8 = 8 THEN 'ON' ELSE 'OFF' END AS [ansi_warnings],
       CASE WHEN @@OPTIONS & 16 = 16 THEN 'ON' ELSE 'OFF' END AS [ansi_padding],
       CASE WHEN @@OPTIONS & 32 = 32 THEN 'ON' ELSE 'OFF' END AS [ansi_nulls],
       CASE WHEN @@OPTIONS & 256 = 256 THEN 'ON' ELSE 'OFF' END AS [quoted_identifier],
       CASE WHEN @@OPTIONS & 1024 = 1024 THEN 'ON' ELSE 'OFF' END AS [ansi_null_dflt_on],
       -- all above options combined
       CASE WHEN @@OPTIONS & 1342 = 1342 THEN 'ON' ELSE 'OFF' END AS [ansi_defaults]

When all of the base options are set to ON, then ANSI_DEFAULTS will be shown as being ON. If any of those options is OFF, then ANSI_DEFAULTS will also be OFF. You can see this behavior via the following test:

SET ANSI_DEFAULTS ON;

DBCC USEROPTIONS; -- ansi_defaults is SET

SET ANSI_NULLS OFF;

DBCC USEROPTIONS; -- ansi_nulls AND ansi_defaults are missing

SET ANSI_NULLS ON;

DBCC USEROPTIONS; -- ansi_nulls AND ansi_defaults are back

Setting ANSI_DEFAULTS to ON will set all of those options to ON. Likewise, setting ANSI_DEFAULTS to OFF will set all of those options to OFF.


How to report stats on restore via sqlcmd? I've inherited a batch file script that calls sqlcmd with parameters. One of the parameters is the path to a SQL file. That file includes a database restore command. The command in the SQL file includes the parameter stats = 1 but nothing is reported in the commandline window during a restore.

Is it possible to use sqlcmd this way to initiate a database restore and have the stats report back and displayed on the commandline as the restore executes? If not, then can you point me to alternative approaches?

UPDATE: 2018-12-20 - I completely re-wrote my answer to use Powershell.  Perhaps this solution will work for you.

I believe SQLCMD was rewritten for 2012 in such a way that print statements and even raiserror messages issued during a run via SQLCMD never displayed until the entire job finished.

If using Powershell is an option, you can use the Invoke-SqlCmd cmdlet to run your scripts.  To see the output while the script is running, include the -Verbose parameter.  (you'll also want to include something like -QueryTimeout 0 to keep the script from timing out at the default 30 seconds)

Here is a quick way to test this functionality.

Create the following stored procedure which issues raiserror statements every 10 seconds (for 50 iterations) and displays the current timestamp.  Under normal runs of SQLCMD, you wouldn't see any output until the procedure completely finishes.  However, using INVOKE-SQLCMD and the -Verbose parameter, you will see the output as the script runs

DROP PROCEDURE IF EXISTS [scutility].[SqlCmdTestOutput]
GO
CREATE PROCEDURE [scutility].[SqlCmdTestOutput]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count INT = 50
    DECLARE @msg VARCHAR(8000)

    WHILE @count > 0
    BEGIN
        SET @msg = convert(VARCHAR(50), sysdatetime())

        RAISERROR (@msg,0,1) WITH NOWAIT

        WAITFOR DELAY '00:00:10'

        SET @count -= 1
    END
END

--The powershell command
powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"

--output
C:\>powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"
VERBOSE: 2018-12-20 08:12:24.3689374
VERBOSE: 2018-12-20 08:12:34.3696751
VERBOSE: 2018-12-20 08:12:44.3704152
VERBOSE: 2018-12-20 08:12:54.3710561
VERBOSE: 2018-12-20 08:13:04.3717697

You can also eliminate the string 'VERBOSE:' from the output by redirecting the -Verbose output and using Write-Host.

C:\>powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose 4>&1 -QueryTimeout 0 | % {Write-Host ($_.Message)}"
2018-12-20 08:42:59.9874559
2018-12-20 08:43:09.9891544
2018-12-20 08:43:19.9898284
2018-12-20 08:43:29.9905699
2018-12-20 08:43:39.9912415
2018-12-20 08:43:49.9919723
2018-12-20 08:43:59.9936545
2018-12-20 08:44:09.9943466
2018-12-20 08:44:19.9960623
2018-12-20 08:44:29.9967689


"Linked server does not contain table" error when using VFPOLEDB provider linked server I am receiving the below error when attempting to run any queries via a linked server in SQL Server 2008 R2 using the Visual Fox Prox OLE DB Provider v9.0.0.3504.

I am attempting this as I need to import data from VFP .dbf files into a SQL database.

Error:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "vfp" does not contain the table "pat". The table either does not exist or the current user does not have permissions on that table.

Example Query:

select * from vfp...pat

I have ensured that there are 'Full Control' AD permissions to the free table directory for the service account that SQL Server is running and the user account that is logged into SSMS on the server.
The server OS is Windows Server 2003 SP2 x86.
TSQL for linked server creation is below:

EXEC master.dbo.sp_addlinkedserver @server = N'VFP', @srvproduct=N'Visual FoxPro', @provider=N'VFPOLEDB', @datasrc=N'"C:\VFP"', @provstr=N'VFPOLEDB.1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VFP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

I've also used an alternate @provstr value of

Provider =vfpoledb; Data Source =C:\VFP\; Collating Sequence =general;

So my question is have I missed anything that I need to configure? Can anyone provide a few pointers as to what I should check to resolve this problem?

Thanks in advance

EDIT:

I should mention that "allow inprocess" is configured for the VFPOLEDB provider and queries fail when using either 4 part names or OPENQUERY().

Eventually found the answer when attempting to work around the problem using SSIS and the VFP ODBC driver. VFPODBC provided a more verbose error message, stating that the .fpt file was missing for the dbf file I was attempting to query.

The staff that did the original data copy (the VFP data is hosted off server) failed to include all the data available (.cdxs, .fpts, .etc). After recopying the data, the linked server described above functions correctly.


Three tables joined by a many-to-many relationship? I'm working on creating a db model for the following requirements:

I have many Types that have many different Statuses.

Depending on the Type and Status, the Status can have certain SubStatuses.

So let's say I have

Type 1 with Status A, that has SubStatuses X and Y.

I can also have

Type 2 with Status A, that has Substatus Z

I created a Many-to-Many relationship between Type and Status and a Many-to-Many relationship between Status and SubStatus. Am I going about this the right way? Is there a better design?

I was just going to make this a comment but it is too long to just be a comment, and I think it is one viable solution though I would say that there are many solutions and it will depend on how large the tables are going to be, how frequently they are used, how they are used, etc...as to what is going to be the best for your situation.

In this case, I think having your Type, Status and SubStatus tables all be separate and having a table that links them together, seems to make sense.

Here is the code that I put together to make that happen for your data.

create table tblType
(
    TypeID int identity(1,1) primary key clustered
    ,TypeName varchar(20)
);
GO
Create table tblStatus
(
    StatusID int identity(1,1) primary key clustered
    ,StatusName varchar(20)
);
GO
Create table tblSubStatus
(
    SubStatusID int identity(1,1) primary key clustered
    ,SubStatusName varchar(20)
);
GO
create table StatusSubStatus
(
    StatusSubStatusID int identity(1,1) primary key clustered
    ,TypeID int
    ,StatusID int
    ,SubStatusID int
);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Type
FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Status
FOREIGN KEY (StatusID) REFERENCES tblStatus(StatusID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_SubStatus
FOREIGN KEY (SubStatusID) REFERENCES StatusSubStatus(StatusSubStatusID);
GO
insert into tblType
Values ('Type 1'), ('Type2'); --id 1, 2
GO
insert into tblStatus
Values ('A'); --id 1
GO
insert into tblSubStatus
Values ('X'), ('Y'), ('Z');  --id's 1, 2, 3 respectively
GO
insert into StatusSubStatus
Values (1, 1, 1), (1, 1, 2), (2, 1, 3);
GO

The advantage to this is that you get to keep your Status/Type tables from being large.  The disadvantage is that you have a linking table that can get quite large.   But it is a clean way to get all of this stuff together cleanly.

If you wanted to see all of the data for TypeID of 1 you would just do the query

Select TypeName, StatusName, SubStatusName
from StatusSubStatus sss
join tblType tt on sss.TypeID = tt.TypeID
join tblStatus ts on sss.StatusID = ts.StatusID
join tblSubStatus tss on tss.SubStatusID = sss.SubStatusID
Where tt.TypeID = 1;

There are many solutions to making this work, this is one that I like but it will certainly depend on your situation as to whether this is the best one for you or not.


Repeating pattern X amount of times in LIKE I took a look at the documentation for patterns from Microsoft (can be found here), and from what I understood, it doesn't say a way for a pattern to repeat a limited number of times. It's either the wildcard (which goes on indefinitely) or looking for a specific character in a location, with each location typed out independently. Is there a way to type out "Pattern X repeated Y times"?

For example, if I wanted to look up account numbers that have exactly 5 digits, what I have now is:

SELECT * FROM table WHERE account LIKE '[0-9][0-9][0-9][0-9][0-9]'

Is there a shorthand way to do that?

Yes. Function REPLICATE():

SELECT * FROM table WHERE account LIKE REPLICATE('[0-9]', 5) ;