Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped? everything is in the question.

I have a table named tableA on which we have indexes and foreign keys and constraints.

I have to modify content in a column called column1. This being a production system, I want to

1) create a copy of tableA called tableA_temp
2) Perform the data change (some concatenation and regexp_replace)
3) Obtain validation that the change is correct
4) rename tableA to tableA_old
4) Rename tableA_temp to tableA.

Once renamed to tableA, would the constraints and indexes apply to the new tableA?

My guess is yes but I would need a little validation.

No.

Ok, it depends on if you create them on the copy.


Registering a SSDT database project as a data tier application fails I am having problems publishing a SSDT database project and registering it as a data tier application. Let me explain.

I have a database (A) which references two other databases (B & C) through linked servers. I have created projects based on B and C and snapshoted the projects to create dacpac's for databases B and C. I have created a database project for database A which has database references to B and C through dacpac's. I have set SQLCMD variables and modified the db project ddl scripts to use the SQLCMD variables in place of the un-resolved linked server names. The project builds!

I am trying to publish the project as a data tier application but keep receiving the following error "Databases registered as a DAC database must be hosted by an instance of SQL 2005 SP4, SQL 2008 SP2, SQL 2008 R2, SQL 2012 or SQL Azure". Incidentally I am running SQL server 2012.

I thought I would test whether I could register as a data tier application through SSMS. Within SSMS the option to "Register as data Tier Application" is grayed out. I therefore tried to "Export Data Tier Application" and received a number of error in reference to the linked server objects.

My question is; is it possible to deploy a SSDT database project and register it as a Data Tier Application where the project is using linked servers, or am I doing something wrong? If it is possible could some one provide some advice.

I have broken Google looking for the answer, so any help would be greatly appreciated. I have previously asked this question on StackOverflow but got no response so I am posting here in a more targeted forum.

I had this error recently so I'll add my solution for anyone else who comes across this,

Turns out in my publish.xml I had RegisterDataTierApplication set to True. The first time I published the database it worked fine, but then I got the same error, as the database was already registered as a Data Tier application.

By setting to false (or unchecking the checkbox in the gui) it works fine.

 <?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>foo</TargetDatabaseName>
    <DeployScriptFileName>foo.sql</DeployScriptFileName>
    <BlockWhenDriftDetected>False</BlockWhenDriftDetected>
    <RegisterDataTierApplication>False</RegisterDataTierApplication>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <TargetConnectionString>Data Source=foo;Integrated Security=True;Pooling=False</TargetConnectionString>
  </PropertyGroup>
</Project>

As a note about extracting the dacpac - you will always get an error if extracting via ssms,

If you use the SQL Server Object Explorer in VisualStudio, you can set more options, including unchecking the 'Verify Extraction' option, which will then allow you to create the dacpac.


SQL sevrer DB migration plan We are planning on migrating our existing SQL 2012 servers from current windows servers to new windows servers. Only difference is we are migrating this current instance which is standalone to Clustered SQL instance. [New windows server has been built and SQL is configured as clustered instance]

Therefore we need to migrate on the same SQL2012 and windows 2012 version.
Total database sizes on the instances are over 20 TB. And yes this is 24*7.

What all measures should i take or please suggest any action plan for this migration that can help me to kick start the migration process in above scenario?

What sort of downtime do you have available for the migration?

There are a couple of ways that I'd approach this, as you're 24\7 and need to migrate 20TB of data, I'd be wanting to stage it beforehand.  You could either do this with logshipping, setup logshipping to your new server ahead of time and then when it comes to the cutover, take a final log backup, play it into the new server and bring online.

Another option and the one that I tend to use when migrating large databases is to mirror over to the new server.  When it comes to cutover, it's a simple job of just failing the mirror over and then then stop mirroring.  There are a few advantages of that approach, it can be setup days ahead of time, cutover is quick with no risk of data loss and it also preserves the log chain.

I wrote about using mirroring in a migration situation here, https://sqlundercover.com/2017/01/21/sql-mirroring-preserving-the-log-chain-during-database-migrations/

Just make sure that any logins you script across from the old server includes the SID, otherwise you could end up with a ton of orphaned users.  Not the end of the world but a hassle you don't need on migration day.


Combine Rows with indirect relation I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone needs more information to help. This should be fairly easy and I'm just having a brain fart I think. I need to combine multiple answers into a single row as separate columns.
Here is my query as it is and it does return all the answers but every answer is generating a separate row. There will only ever be one answer for each question per visit id.
There are a few catches to working with this system. At it's heart it's SQLServer, however queries are restricted to starting with 'select' making temp tables a bit more difficult. There can be no spaces, no blank lines nothing before your select. This is their version of security I guess. All reports are written through a web interface no direct access to the db in any way.
Current Output:

clientvisit_id  |  client_id  |  members_present  |  patient_category

141001          |  2001       |                   |
141001          |  2001       |                   |
141001          |  2001       |  Patient          |
141001          |  2001       |                   |  Adult

Desired output:

clientvisit_id  |  client_id  |  members_present  |  patient_category

141001          |  2001       |  Patient          |  Adult

Select
  cv.clientvisit_id,
  cv.client_id,
  mp.answer as members_present,
  pc.answer as patient_category

From ClientVisit cv
  Inner Join SavedVisitAnswer sva On sva.clientvisit_id = cv.clientvisit_id
  Inner Join Question q On sva.question_id = q.question_id
  Inner Join Category cat On q.category_id = cat.category_id
  Inner Join FormVersion fv On cat.form_ver_id = fv.form_ver_id
  Inner Join Forms On fv.form_id = Forms.form_id
  Inner Join (Select
     a1.answer_id,
     a1.answer
     From Answer a1
     Where a1.question_id = '532096'
  ) as pc on sva.answer_id = pc.answer_id
  Inner Join  (Select
     a2.answer,
     a2.answer_id
     From Answer a2
     Where a2.question_id =  '532093'
  ) as mp on sva.answer_id = mp.answer_id

Where
  Forms.form_id = '246'

If you wrap
select
  max(x.members_present),
  max(x.patient_category)
from
(
  <your query goes here>
) as x
group by
  x.clientvisit_id,
  x.client_id

around your current query you'll collapse those rows into one, removing the blanks.


Changes access method for non-correlated subquery Oracle 11g R2

Unfortunately our application has per row security "features".
We have a query that looks about like this:

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM bigTableA_securitymapping b
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
       b.accesscode in (SELECT accesscode
                              FROM accesscodeView
                              WHERE user = :someUserID)
)

There a unique index on bigTableA_securitymapping(PrimaryKeyTableA,accesscode).

The accesscodeView could potentially return more than one accesscode for a given user, so it must be IN() and not =.

The issue is that this query ignores the unique index for bigTableA_securitymapping and chooses to do a full table scan.

If I change the IN() to an = then it does a UNIQUE SCAN on the unique index on bigTableA_securitymapping and is about 50 times faster.

Good, fast but not possible:

SELECT someRow, someOtherRow
    FROM bigTableA a
    WHERE EXISTS (
      SELECT 0 FROM bigTableA_securitymapping b
      WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
           b.accesscode =(SELECT distinct accesscode
                                  FROM accesscodeView
                                  WHERE user = :someUserID)
    )

But, I cannot do that because the accesscodeView may return more than one row.

(There's a distinct in there because the accesscodeView needs it given the =, putting the DISTINCT on the original query makes no difference.)

If I hardcode the accesscodes, it also does a UNIQUE SCAN on the unique index for bigTableA_securitymapping.

Good, fast but requires large application change:

SELECT someRow, someOtherRow
      FROM bigTableA a
      WHERE EXISTS (
        SELECT 0 FROM bigTableA_securitymapping b
        WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
             b.accesscode in (1,2,3,4)
      )

Changing to a join inside doesn't really help either. It still does a full table scan.

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM accesscode ac INNER JOIN
                bigTableA_securitymapping b ON
                  ac.accesscode = b.accesscode
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA
        AND user = :someUserID
)

So why the difference between = and IN() in.  And why does a non-correlated subquery (the accesscodeview subquery) cause such a plan difference?  Is there any way to rewrite it to do what I want?  The difference in 'good plan' costs vs 'bad plan' costs here are 87 vs 37,000 and a large amount of time in real runtime for the same results.

The database may be ignoring your index if your statistics are stale (including both the system and table statistics) or if your initialization parameters are not optimized. The default optimizer cost adjustment is likely wrong for most applications, for example.


Column Data Type for Bitwise Comparison? I have a table where I have an int32 column storing values for bitwise comparison. I've ran out int space with a int32. I can convert to int64 but that will just prolong the inevitable. Is there an efficient data type I should store these values in instead of int?

It would be OK to re-assign all my existing values if I have to.

I only use flags/bitmask fields in cases where a.) the flatness of the schema matters (data warehouse) and b.) I'm absolutely guaranteed to never pass 64 bits. If this is an OLTP system I'd refactor to use both a many to many mapping table to track the bits and a lookup table with flag names. The m2m table would have 2 fields: FK to the ID of the table currently housing the flags and an FK to a lookup table. The lookup table would be FlagID, FlagName ("Flag" should be replaced with whatever this data actually is "error" maybe). This has the added bonus of greatly simplifying queries and constraints on this data.


Suggestion for storing a lot of simple data I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date.  The way I planned on laying out the database was as follows:

2 Tables

Table: Pages

ID            URL - Max Length = 2048 chars
----------    -------------------------------
1             http://www.site1.com/page.php
2             http://www.site2.com/page-abc.php
3             http://www.site3.com/page-1.php
4             http://www.site4.com/page-cd.php
5             http://www.site5.com/page-nice.php
6             http://www.site6.com/page-some.php
7             http://www.site7.com/page-hrmm.php
8             http://www.site8.com/page-stack.php
9             http://www.site9.com/page-ex.php
10            http://www.site10.com/page-dba.php

Table: Links

Page          Links
----------    -------------------------------
2             1
3             1
4             1
5             1
6             1
7             1
8             1
8             9
9             1
10            1

Basically I'll be able to see what webpages link to where recurrently/several levels deep per website.  I want to map a large network of websites and their linking patterns.

So I need to know if there is a better way of doing so, and maybe some suggestions on how to design the database structure/system.  I was planning on PostgreSQL to start with since I've used it some, but with this amount of data I'm open to anything.

To reduce space requirements you might consider a few things:

Not bothering to store http:// or the leading www. - this is just wasted space (though in a few cases the www. is required because people don't know how to configure their web sites properly).
Making sure you use data compression. Most systems are still I/O-bound, not CPU-bound.
Only storing any domain name once, and storing the page URLs separately. Both may actually repeat and storing them multiple times is wasteful. So for example instead of:

CREATE TABLE dbo.Pages
(
  ID BIGINT IDENTITY(1,1) PRIMARY KEY, -- need BIGINT for 500 billion rows
  URL VARCHAR(2048) -- far too large to apply UNIQUE
);

CREATE TABLE dbo.PageLinks
(
  PageID BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Pages(ID),
  LinkID BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Pages(ID),
  PRIMARY KEY (PageID, LinkID)
);

You could do something like this:

CREATE TABLE dbo.Domains
(
  DomainID INT IDENTITY(1,1) PRIMARY KEY, -- probably no more than 2BN domains
  DomainName VARCHAR(255) NOT NULL
) WITH (DATA_COMPRESSION = PAGE);

CREATE UNIQUE INDEX dn ON dbo.Domains(DomainName)
  WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.URLs
(
  URLID INT IDENTITY(1,1) PRIMARY KEY, -- maybe you need BIGINT here
  URL VARCHAR(2048) NOT NULL -- still can't apply UNIQUE here
  -- but you can have the same URL (e.g. /page.php) from two different
  -- domains only listed once.
);

CREATE TABLE dbo.DomainURLs
(
  DomainURLID INT IDENTITY(1,1) PRIMARY KEY, -- may also need BIGINT here
  DomainID INT NOT NULL FOREIGN KEY REFERENCES dbo.Domains(DomainID),
  URLID INT NOT NULL FOREIGN KEY REFERENCES dbo.URLs(URLID)
) WITH (DATA_COMPRESSION = PAGE);

CREATE UNIQUE INDEX du ON dbo.DomainURLs(DomainID, URLID)
  WITH (DATA_COMPRESSION = PAGE);

Yes, table design and query semantics will be much more complex, but it will scale much better once you index many pages on the same site (or many pages with the same URL across different sites).

Just to demonstrate the potential for space savings here. Taking just the storage of the URLs and ignoring the links between them, let's look at just the Pages, Domains and URLs tables (and to be fair, I'll even test your pages table with compression).

Note that we take advantage of the fact that you will probably index each site in turn, alphabetically, rather than hash and index random URLs each iteration. This allows compression to work as well as possible in this case.

For some sample data, on my system this generates about 121,000 rows, but that will vary depending on a lot of factors, such as how many databases are on your system, how many objects in your database, the design of any non-system objects (e.g. number of columns), and even @@VERSION:

;WITH x AS
(
  SELECT
    d = t.name + CONVERT(VARCHAR(5), d.database_id) + '.com',
    p = '/' + c.name + '.php'
   FROM sys.all_objects AS t
   CROSS JOIN sys.databases AS d
   INNER JOIN sys.all_columns AS c
   ON t.[object_id] = c.[object_id]
 )
 SELECT d, p
 FROM x
 ORDER BY d, p;

Sample results:

all_columns1.com    /collation_name.php
all_columns1.com    /column_id.php
all_columns1.com    /default_object_id.php
all_columns1.com    /is_ansi_padded.php
all_columns1.com    /is_column_set.php
...

Now let's use that to populate our four tables:

;WITH x AS
(
  SELECT
    d = t.name + CONVERT(VARCHAR(5), d.database_id) + '.com',
    p = '/' + c.name + '.php'
   FROM sys.all_objects AS t
   CROSS JOIN sys.databases AS d
   INNER JOIN sys.all_columns AS c
   ON t.[object_id] = c.[object_id]
 )
 SELECT d, p
 INTO #blat
 FROM x
 ORDER BY d, p;

 CREATE CLUSTERED INDEX x ON #blat(d, p);

 INSERT dbo.Pages(URL) SELECT 'http://www.' + d + p FROM #blat ORDER BY d, p;

 INSERT dbo.Pages_compressed(URL) SELECT 'http://www.' + d + p FROM #blat ORDER BY d, p;

 INSERT dbo.Domains(DomainName) SELECT DISTINCT d FROM #blat ORDER BY d;

 INSERT dbo.URLs(URL) SELECT DISTINCT p FROM #blat;

Now of course you would have to build the junction table here by cross-referencing the Domains and the URLs. But this will still show how much space you can save by not storing the same domain name or page name twice, even if it means more complicated logic to put these things together:

EXEC sp_spaceused 'dbo.Pages';            -- 8,904 KB
EXEC sp_spaceused 'dbo.Pages_compressed'; -- 4,552 KB
EXEC sp_spaceused 'dbo.Domains';          --   656 KB
EXEC sp_spaceused 'dbo.URLs';             --   136 KB

That's with 121,000 URLs. For 500 billion? Let's extrapolate. Per complete URL, without compression, you are storing about 73 bytes. With compression, 38 bytes per URL. My method: 6.5 bytes per URL. (Discard, for a moment, how unrealistic my sample data is. Let's pretend I've come somewhere close to the average URL length, and that both (a) you will be indexing many URLs on every domain and (b) you will get duplication of page paths across many domains.)

So ignoring those realities, simple math shows more than 90% reduction in storage space between your uncompressed method and my method:

Your method, uncompressed = 33.2 TB
Your method, compressed   = 17.1 TB
My method                 =  2.9 TB

And again, it's more complicated, so more work up front, but usually this is worth it. You only design the schema and write the code around it once; you'll be maintaining this for, well, how long do you expect the service to exist?


postgresql insert from select query, plus static values Referring to How to insert values into a table from a select query in PostgreSQL?,

I would like to INSERT into a table rows from another, specified by a SELECT DISTINCT, plus some static values, something like:

INSERT INTO new_tbl (column1, column2, column3)
SELECT DISTINCT id FROM -- long where clause --,
  'a string', 0;

So that every row in the new table will get the same values for column2 and column3 Is this possible?

You can put static values into SELECT clause.

INSERT INTO new_tbl (column1, column2, column3)
    SELECT DISTINCT id, 'a string', 0 FROM -- long where clause --;


Postgres PgAdmin III Query Builder crash I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem.
I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300.

From this morning, Query Builder ("SQL Editor") option simply stopped working. When I open pgAdmin, everything is fine and workin', until I click on the Sql Query Builder button. When I do that, nothing appears and pgAdmin crashes without giving any information.

I tried to uninstall and install PgAdmin III 9.1, again, but Sql Query Builder kept not workin'.

What could I do?

We had the same problem 2 times on two different computer, in just a few days of interval.

PgAdmin started to crash each time we open query builder.

Restarting the computer, reinstalling pgAdmin with differents versions... Nothing resolved the problem. But we found the solution to this problem!

PgAdmin uses a file where it saves the last queries (kind of history) and this file was probably corrupted (or one of the queries was).

This file is in the Application Data of your Windows User, that's why reinstalling pgAdmin doesn't work: this corrupted file is common for every postgres install.

So you just got to rename or delete the file pgadmin_histoqueries.xml located in your AppData (for us, D:\Documents and Settings\XXXXX\Application Data\postgresql) and relaunch pgAdmin, this should be OK.


Clarification on Clustered index scan and Clustered Index Seek I have created one table with 80000 records with Cluster Index on CustomerID column.  When I queried the following statements the execution plan behaves in differently.  Can anyone clarify the same.

select * from customer

  Showing Cluster Index Scan,  So this is correct.

select * from customer where CustomerID=80000

  Execution plan shows ClusterIndex Seek,  So this is correct.

select * from customer where CustomerID between 1 and 70000

  Execution plan still shows ClusterIndex Seek why?

As per the statistics the row count is 90% then the optimizer have to use 'ClusterIndex Scan' but it's using 'Cluster Index Seek' Why?  All statistics are up to date and I have checked the Estimated row and actual rows are same then why the optimizer choose 'Cluster Index Seek' For getting 90% of rows among total records.

Execution plan still shows ClusterIndex Seek why?

The initial seek down the b-tree is to find the first row where CustomerID >= 1.

From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as a row is encountered that does not match the predicate CustomerID <= 70000.

The effect is that the 'seek' is an initial seek, followed by a partial ordered scan of the index.

This is usually more efficient than scanning the whole index, even where 90% of the rows are expected to qualify. The key point to get straight in your mind is that a separate b-tree seek is not performed for each row.


Checking the data latency on an Always On Availability Group in ASYNC mode We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over.

I created a latency report utilizing the below query that collects the data every min on each server.

On one of our servers the secondary routinely shows that it has an earlier last_commit_time then the primary.

I verified the server times are the same on both servers.

Why might this be?

;WITH
        AG_Stats AS
                (
                SELECT  AR.replica_server_name,
                        AG.name as AGName,
                        HARS.role_desc,
                        Db_name(DRS.database_id) [DBName],
                        DRS.last_commit_time
                FROM   sys.dm_hadr_database_replica_states DRS
                INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
                INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
                    AND AR.replica_id = HARS.replica_id
                INNER JOIN [sys].[availability_groups] AG on AG.group_id = AR.group_id
                ),
        Pri_CommitTime AS
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'PRIMARY'
                ),
        Sec_CommitTime AS
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'SECONDARY'
                )
    SELECT p.replica_server_name [primary_replica]
        , p.AGNAME
        , p.[DBName] AS [DatabaseName]
        , s.replica_server_name [secondary_replica]
        , DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Latency_Secs]
    FROM Pri_CommitTime p
    LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] and  s.AGNAME = p.AGNAME

It is worth noting that the value for last_commit_time is defined differently based on whether it is being queried from the secondary or primary replica.

  On the secondary database, this time is the same as on the primary
  database.

  On the primary replica, each secondary database row displays the time
  that the secondary replica that hosts that secondary database has
  reported back to the primary replica. The difference in time between
  the primary-database row and a given secondary-database row represents
  approximately the recovery point objective (RPO), assuming that the
  redo process is caught up and that the progress has been reported back
  to the primary replica by the secondary replica.

In addition to the above, a secondary replica that has a delay of a few seconds on the commit will yield a positive number, but one that hasn't finished the commit yet will yield a negative number in your query.

So if you have a latency of 10 seconds but query at the 5 second mark, since there is no commit time yet on the secondary for the primary's last commit, you're going to get the previous commit time which is probably before the primary's latest commit time. This is expected with asynchronous replication.


should we replace some non-clustered indexes with clustered indexes We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk.

I am thinking that we could replace these with one fk index since we never really access the tables by the id only the fk.

for example two tables

reportdata
(id bigint,
 itemid bigint
 ...
)

reportdatacosts
(
 id bigint,
 reportdataid bigint,
 decimal cost,
 ...
)

as I see it reportdatacosts has no need for an index on id because it is always accessed by the reportdataid so this is a perfect candidate for changing to just use clustered index. So I should change this to be a clustered index on reportdataid then id, right?

The table reportdata needs the id to be indexed for the fk on reportdatacosts to work so for this I am unsure. I believe we could perhaps get benefits by removing the fk and because this table is only populated in one place in the code it should not get bad data in. So if we remove teh fk and change to just the clustered index is there any drawback. Could we expect much benefit or might this give worse performance?

Edit: I'm trying to understand when a clustered index would not be just on the pk - this is the only example I can think of

Your problem will come in when you insert data into the ReportDataCosts table, as it will have to perform splits to insert the data, with a clustered index on the FK.

If you're so hell-bent on a clustered index on the FK, why not widen the PK to include the FK and make that clustered?


How to express this constraint in a database schema? I have the following functional dependencies which are in BCNF:

a,b -> c
a -> d
b -> d

With the additional constraint, that no a and b should be combined with a c, where a and b have different ds.

Example:

a | d   b | d   a | b | c
-----   -----   ---------
1 | 3   5 | 3   1 | 5 | 6
2 | 4           2 | 5 | 7

The first row in a,b,c is allowed (1->3,5->3), but the second row is forbidden, since (2->4,5->3) 4 != 3.

This additional constraint can have two effects on my data. For each a,b,c there are two redundant ways of determining the d. There can be data which violates the constraint.
How can my schema reflect this additional constraint?

In a nutshell, introduce d into the third table to enable vanilla foreign key constraints e.g. Transitional SQL-92 syntax:

CREATE TABLE T1
(
 a INTEGER NOT NULL,
 d INTEGER NOT NULL,
 UNIQUE (a, d)
);

CREATE TABLE T2
(
 b INTEGER NOT NULL,
 d INTEGER NOT NULL,
 UNIQUE (b, d)
);

CREATE TABLE T3
(
 a INTEGER NOT NULL,
 b INTEGER NOT NULL,
 c INTEGER NOT NULL,
 d INTEGER NOT NULL,
 UNIQUE (a, b, c),
 FOREIGN KEY (a, d) REFERENCES T1 (a, d),
 FOREIGN KEY (b, d) REFERENCES T2 (b, d)
);


T-SQL script doesn't delete files when run as an agent job - does if run manually I'm trying to write a script to copy the last 'good set' of SQL Server backup files to another drive (it's a long story - we're using a suppliers R1Soft for backup and it won't allow us to exclude folders when we're talking about ~1M files).

The idea behind the script is that it:

Grabs the last backup location from SQL Server
Deletes the file from the separate drive
Runs Ola Hallengrens script to run a backup
Grabs the last backup location again (so now it's the one just made)
Copies that file to the separate drive

The problem that i'm having is that everything runs ok if i'm running the agent job manually, but when it comes to have the job run as scheduled, it does the copy part at the end, but doesn't do the initial delete - so all the old files stay on the drive and have to be deleted manually.

There's nothing in the logs that indicate what could be happening (no failures or error messages). I'm assuming that it's a permissions issue - what user does the agent job run as when scheduled? Maybe it's the user that I'm logged in as thats used when running it manually?

Either that, or is there a better way of doing it? I don't really want to run the backup twice as there's around 1TB of data to back up.

The account that you use to run the job WILL need permissions to delete those files. To test your process, login as that specific user and test your job--specifically the deletion of files-- with that user.

That being said, I've had issues purging SQL Server backups the way I'd like so I created this CmdExec script do so. To delete files and get error information back, consider running this as one of your job steps (should/has to be a one liner for CmdExec to work right):

cmd /e:on /c "forfiles /p "c:\MYDATABASEBACKUPS" /s /m *.BAK /d -2 /c "cmd /c del @path" 2>&1 |  findstr /V /O /C:"ERROR: No files found with the specified search criteria."2>&1 | findstr ERROR&&EXIT 1||EXIT 0"&exit %errorlevel%

This script purges backups based on how old they are in number of days. The /d -2 tells the script to keep two days worth. Change 2 to whatever number you'd like to keep.

Also, change c:\MYDATABASEBACKUPS to your backup location. Change *.BAK to the backup extension of your backup files. The "fanciness" of this script is because I get rid of the false positive "No files found"--because sometimes there won't be files to find.

Good luck with your scripting!


Force chain to start immediately after completion instead of on a timer Is it possible to force an Oracle chain to restart immediately after it finishes executing, instead of on a timer?  I have only found ways to do a temporal restart, which doesn't really work for me as this is a data warehouse that has varying times of completion and cannot be set to just run every X minutes.

The easiest way to do this is to add another step prior to the end of the chain that changes the job to start execution of the chain at SYSDATE + 1/1440.

Something like this:

CREATE OR REPLACE PROCEDURE move_next_execution
AS
BEGIN
    -- change to the correct job_id
    dbms_job.next_date(666,'sysdate+1/1440');
    -- need to commit so that the scheduler (which runs in a different session) can see it
    commit;
END;
/

sys.dbms_scheduler.create_program(
    program_name => 'PHIL.MOVE_EXEC',
    program_action => 'PHIL.move_next_execution',
    program_type => 'STORED_PROCEDURE',
    number_of_arguments => 0,
    comments => NULL,
    enabled => FALSE);

sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.MOVE_EXEC');

(Untested, but will test it when I have time - unsure if the end of the chain & job will again modify the next_date.)

I have tested the following, which works OK (but involves creating a new job):

-- schedule next execution
CREATE OR REPLACE PROCEDURE schedule_next_execution
AS
BEGIN
    -- not that job_name will have to be generated so it's unique each time
    DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'chain_job',
      job_type        => 'CHAIN',
      job_action      => 'CHAIN',
      start_date => systimestamp + interval '1' minute,
      enabled         => TRUE);

END;
/

-- just an example proc
CREATE OR REPLACE PROCEDURE do_nothing_for_2minutes
AS
BEGIN
  DBMS_LOCK.SLEEP(120);
END;
/

BEGIN
    sys.dbms_scheduler.create_program(
        program_name => 'PHIL.NOTHING',
        program_action => 'PHIL.do_nothing_for_2minutes',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.NOTHING');

    sys.dbms_scheduler.create_program(
        program_name => 'PHIL.SCHEDULE',
        program_action => 'PHIL.schedule_next_execution',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.SCHEDULE');
END;
/

BEGIN
    sys.dbms_scheduler.create_chain(
        comments => 'example chain',
        chain_name => 'PHIL.CHAIN'
    );

    sys.dbms_scheduler.enable(name=>'PHIL.CHAIN');
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        STEP_NAME  => 'STEP1',
        PROGRAM_NAME => 'PHIL.NOTHING'  );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        STEP_NAME  => 'STEP2',
        PROGRAM_NAME => 'PHIL.SCHEDULE'  );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'TRUE',
        action => 'START STEP1'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'STEP1 SUCCEEDED',
        action => 'START STEP2'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'STEP2 SUCCEEDED',
        action => 'END'
        );
END;
/

-- get timestamp
select systimestamp from dual;

-- run for first time
BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
    chain_name   => 'CHAIN',
    start_steps  => null );
END;
/

-- wait 2 mins
select DBMS_LOCK.SLEEP(120) from dual;

-- check for new job
select start_date from dba_jobs where job_name = 'CHAIN_JOB';


When does SQL Server warn about an Excessive Memory Grant? What are the conditions that produce an "Excessive Grant" execution plan warning?

  The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 5128 KB, Final 5128 KB, Used 16 KB.

SSMS

Plan Explorer

Showplan xml

<Warnings>
    <MemoryGrantWarning GrantWarningKind="Excessive Grant"
        RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" />
</Warnings>

To produce this warning:

The maximum used memory must be less than 5% of the granted memory; AND
The query must use the regular (not small) resource semaphore

To use the regular resource semaphore the query must:

Have granted memory over 5MB (5120 KB, 640 x 8KB pages); OR
Have a total estimated plan cost of over 3 units and not be a trivial plan

Server version requirements:

SQL Server 2014 SP2 (12.0.5000) or later
SQL Server 2016 SP1 (13.0.4001) or later
SQL Server 2017 RTM (14.0.1000) or later


Trying to restore a corrupt Postgresql database, with corrupt data folder. Able to start postgres but the tables are empty? Postgres 9.3 on Ubuntu 12.04

We had some disk corruption on our server, and after doing a fsck the database would not start. To get us back into production, I copied the postgres data folder, blew away the DB and restored from the latest pg_dump. But there is some data I'd love to retrieve from a specific table in the corrupted data folder.

When I try to start postgres using the corrupted data folder, it wouldn't start because of missing xlog files. So I used pg_resetxlog and it threw me another error: *missing pg_clog/0000*. I suspect that the file was lost during fsck fixes, so I copied that file over from my running server, and had to do the same for the *pg_stat_tmp* folder, and the *pg_multixact/offsets/0000* file.

Now postgres will start using the corrupted data folder, but when I psql into it, all the tables seem to be empty (row count 0). Some tables seem to be missing altogther and throws me an error:  could not open file "base/16416661/16416776": No such file or directory

The table that I'm after though, seems to still exist, but it gives me a row count of 0, even though the file itself in the /base directory is 1Gb large.

I've also tried doing a pg_dump of the database, which gives me an empty file, and a dump of that specific table, which pg_dump says it can't find (it says the table doesn't exist, even though psql can see it)

Does anyone understand the internals of the /base files, and is there any way I can recover data from the database, or that single table?

Thanks!

First action

When facing corruption, before you do ANYTHING ELSE, take a complete file-system-level copy of the damaged database. See: http://wiki.postgresql.org/wiki/Corruption . Failure to do so destroys evidence about what caused the corruption, and means that if your repair efforts go badly and make things worse you can't undo them. Do not attempt any repair first.

You appear to have made the right choice an done that, assuming you really copied the whole datadir. However, you appear to have then messed with the copy. Before you do anything else, make a copy of that damaged datadir somewhere safe and do not touch it again. This is your hope of recovery. Never work on this copy - duplicate it, and test recovery attempts on the duplicate.

Don't trust that server!

BTW, I strongly recommend that you stop using this server:

  We had some disk corruption on our server

is not ok. Until you know why that happened you should not be using that server. Retire it or put it aside and get some trustworthy hardware.

If you cannot do that, make absolutely certain you're doing at least daily logical backups and streaming replication with WAL archiving to a secondary server. Treat the faulty server as if it might vanish or eat your data again at any time.

If the disk corruption corresponded with a power failure, it's probably due to unsafe write-back caching or a system that's ignoring disk flush requests. This is why I do plug-pull testing on server deployments, and don't buy cheap SSDs.

Backups?

This is the point where I tell you that you need to restore from those backups you've been making and testing regularly, preferably the point-in-time recovery or streaming replication setup.

If that was an option you wouldn't be posting here, though.

Ask for help in the right place

Once you have a safe copy of the datadir set aside, post for help on the pgsql-general mailing list.

If the data is important and hard to recover, be prepared to pay for data recovery / repair expertise. See http://www.postgresql.org/support/professional_support/ . (I work for one of the listed companies, just by way of fair disclosure).

Corruption cases tend to be somewhat unique and require lots of back-and-forth, so they're not usually a good fit for Stack Overflow.

Internal structure

As for the internals of the files in base/ ... you really need the system catalogs to interpret them usefully. The table structure is documented in PostgreSQL internals.

The structure of individual relation extents is basically a header, followed by a bunch of columns that're interpreted based on the system catalogs. If you've lost the system catalogs you've got no reliable way to tell what each column's type and name is, etc.

The other problem you have is that you've lost the transaction commit logs (pg_clog) that keep a record of open, commited, and rolled back transactions. With that data lost, you will need to do a dirty read of the tables in order to recover any data, because you no longer know which tuples were added by transactions that later rolled back, which are deleted, which are old versions of updated tuples, etc.

Recovery?

... will be very hard.

In theory you might be able to read the tuples out of the heap table extents. I am not aware of any tools to do this. You would need to be able to construct new system catalogs that matched the on-disk structure of the tables, probably with a stand-alone PostgreSQL backend (postgres --single).

I'd like it if PostgreSQL had better recovery options, but frankly, we prefer to have good backups and use streaming replication etc to avoid the need in the first place. Repairing DB corruption is always iffy and results in untrustworthy, mangled data. So in general - don't do that.

It might help if you still have a copy of your data directory from before you ran pg_resetxlog.

Prevention

I wrote a bit on corruption prevention a while ago. See this post on my old blog.


Why do I need to backup the Database Master Key? When I make a Database Master Key for a database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Long Password'

This page from Microsoft recommends I back it up offsite:

http://msdn.microsoft.com/en-us/library/ms174382.aspx

I've confirmed through testing that the Master Key is stored when you backup the database. I'm already backing up the database, and I'm already storing the password safely. So why do I need to backup the Master Key too?

(As an aside, I am already backing up the Service Master Key and storing offsite).

I primarily use them when I'm moving or copying a database to a new instance of SQL Server. In that case, the DMK in the database will be re-encrypted with the new instance's service master key, but the objects encrypted by the DMK (in your case, the symmetric keys) will not be updated accordingly.  You'll need to restore the DMK from a separate backup using the FORCE option to allow the objects to be automatically decrypted.

And yes, for the record: corruption of the keys is rare, and they will be verified by the same mechanisms as any other data in the database (i.e., CHECKDB).  However, I would still keep backups of all encryption keys you use - master keys, certificates, symmetric keys, etc.  In all of those cases, if you lose the key, you will never be able to decrypt the associated data.  It's not necessary to backup the keys with every database backup, but it's good insurance to back them up when they're created or modified.


Restore SQL database to newer version I'm using Drupal 7 on Windows and its using SQL Server 2008 RS2 (SP2).
Now, I have to move my site and new server using SQL Server 2016.

How can I restore my database to new server? Is it possible?

Thanks.

Simply backup the database on 2008R2, then restore it on the new server. You can change the compatibly level to the new version. The last thing to do is to add the logins from the old server, you can check out this link: Restore database rights on restored database SQL 2012


Implement a two sided zero to many relationship? How does one implement the following relationship?

One car may belong to zero or many persons
One person may own zero or many cars

Ideally I could have a junction table that has a zero to many relationship to both Car and Person tables. Would that be the correct table organization?

For practical purposes, if you're implementing a many-to-many relationship with an intersection table - which is by far the most common way to do it, then you will in effect have a "zero or more to zero or more" relationship.

Relational databases don't have declarative referential constraints that enforce the child end of a one to many relationship.  In other words, your CAR table can't have a constraint (imposed by DRI) that enforces 0, 1 or many owners.  DRI works on the table with the foreign key, not the parent (referenced) table.

This means that unless you implement some additional procedural logic in your application, you wouldn't be able to enforce 1 or more, so you in effect get 0, 1 or more to each of CAR and PERSON from the intersection (OWNERSHIP).

Implementing a many-to-many relationship in this way is consistent with correct normalization.


How to use variables in a mongodump query I want to backup data of last day (Current Day-1) every day using cron tasks.
In the mongo shell, I can query the data using variables as below:

var start = new Date();
start.setHours(0,0,0,0);
start.setHours(start.getHours() - 24);

var end = new Date();
end.setHours(23,59,59,999);
end.setHours(end.getHours() - 24);

db.collection.find({created_at: {$gte: start, $lte: end}});

How to use the above process as a single query to run with mongodump from outside of the shell. What will be the query in the command below?

mongodump --db dbname --collection cname --query '{}' --out collection_01NOV2019

You can do it like this:

start="new Date(1000*$(date -d `date -d yesterday +%F` +%s))"
end="new Date(1000*$(( $(date -d `date +%F` +%s) - 1 )))"
query="db.collection.find({created_at: {\$gte: $start, \$lte: $end}});"

echo $query
db.collection.find({created_at: {$gte: new Date(1000*1575586800), $lte: new Date(1000*1575673199)}});

Putting all in one line is a bit ugly but possible:

mongodump --db dbname --collection cname --query 'db.collection.find({created_at: {$gte: '"new Date(1000*$(date -d `date -d yesterday +%F` +%s))"', $lte: '"new Date(1000*$(( $(date -d `date +%F` +%s) - 1 )))"'}})' --out collection_01NOV2019

Note, in crontab you have to write +\% rather than +%. Perhaps it will be more clear when you write a 3-line shell script and execute this by cron.

Simplified alternative:

start="new Date('`date -d yesterday +%FT00:00:00`')"
end="new Date('`date -d yesterday +%FT23:59:59`')"
query="db.collection.find({created_at: {\$gte: $start, \$lte: $end}});"

echo $query
db.collection.find({created_at: {$gte: new Date('2019-12-05T00:00:00'), $lte: new Date('2019-12-05T23:59:59')}});


Difference in size of table between InnoDB and MEMORY engines Question

The same table stored on hard drive (InnoDB engine) takes 930.72 MB when in Ram (MEMORY engine) it takes 1538.54 MB.

For what reason?
How to calculate space needed to save data in memory? Are there any general rules?

Example

Text below is not question, but allow reconstruct my database state.

We create database:

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;

Create table innoDB

CREATE TABLE main(
  id INTEGER UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value INTEGER
);

Define procedure to load data

delimiter #
create procedure load_data(IN _max INTEGER)
begin

declare counter int unsigned default 0;

  truncate table main;
  start transaction;
  while counter < _max do
    set counter=counter+1;
    insert into main (value) values (counter);
  end while;
  commit;
end #

delimiter ;

Call procedure

call load_data(25000);

Create table MEMORY

-- SET max_heap_table_size = 16*1024*1024;

CREATE TABLE memory_main (
  id INTEGER UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value INTEGER
) ENGINE=MEMORY
AS SELECT * FROM main;

We measure size of both tables and this size is not the same.

SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "test"
    AND table_name LIKE "%main%";

InnoDB stores the data in a BTree, with the PRIMARY KEY clustered.  Your 8 bytes of data will expand to an average of about 30 bytes due to record overhead, block overhead, BTree characteristics, etc, etc.  The BTree blocks are 16KB each.  After some point in the growth of the table, chunks of 8MB are allocated, so you will find the table growing is big increments.

MEMORY stores data in a Hash.  So the PK is hashed to build the data structure.  I am not familiar with the details, but they are significantly different than InnoDB's BTree structure.  A common technique is to double the hash size when needed, thereby also leading to significant increments at seemingly random times.

What is the goal of this exercise?


Why doesn't SQL Server use my index in this SELECT ... WHERE? I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]):
CREATE TABLE [dbo].[MP_Notification_Audit] (
    [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,
    [type]                  INT                NOT NULL,
    [source_user_id]        BIGINT             NOT NULL,
    [target_user_id]        BIGINT             NOT NULL,
    [discussion_id]         BIGINT             NULL,
    [discussion_comment_id] BIGINT             NULL,
    [discussion_media_id]   BIGINT             NULL,
    [patient_id]            BIGINT             NULL,
    [task_id]               BIGINT             NULL,
    [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [clicked]               BIT                NULL,
    [date_clicked]          DATETIMEOFFSET (7) NULL,
    [title]                 NVARCHAR (MAX)     NULL,
    [body]                  NVARCHAR (MAX)     NULL,
    CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC)
);

[...]

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

This table has about 11,700 rows of data in, so it should be enough to trigger the use of indexes with WHERE clauses.  If I SELECT just the column I'm filtering on, only the index is used and 133 matching rows are read - an index-only scan:
SELECT [target_user_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

However, as soon as I add an extra column to the SELECT, the index is ignored and a table scan with a predicate is done to attain the result, reading over 11,700 rows:
SELECT [target_user_id], [patient_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

Why is it ignoring my index in this second query?  I'd have thought it would still be more efficient to use the index to get down to 133 RIDs, then query the extra row data required, than to go through every row of the table with a predicate?  I know I can add columns to the index with INCLUDE with the extra fields needed in the SELECT clause to make it use the index again, but I'm interested as to why it doesn't still use the index in this case.

Given the size of your table (~11k rows), I think it would be safe to assume that SQL Server estimated that the cost of performing a seek on the non-clustered index and then potentially multiple RID lookups was more expensive than performing a table scan.
There is some evidence to support this theory within the second query plan that you pasted. I would normally expect the Query Optimiser to suggest adding a covering index for your query as you have mentioned in your post. However, it did not. This to me suggests that SQL thinks that doing so would provide little or no improvement over a full table scan.
With all that being said, I am sure that if you added more rows to the table SQL Server may change its mind and ask you to add a covering index or start performing a seek + RID lookup as you expect. If you have Query Store enabled you can always keep an eye out for queries on this table that are causing problems - if it isn't causing you a problem, I wouldn't worry about it right now.


Execute Dynamic SQL Generated From Select A business process in SQL can generate a variable to each operation 1-N  number of calls to a stored procedure whose purpose is to insert data related to the main process.

How does one execute multiple rows of generated sql from a select or is there a way of calling a stored procedure from a select?

The process before this step is an involved operation of business logic and data driven operations and has done subsequent inserts to related FK joined tables.

At that point where it needs to call a stored procedure it has data ready to be processed in a table variable. The step (logic inside the proc) calling the stored proc is an involved process that makes sense to be in a stored proc and cannot be copied to the existing proc.

Faux example of generated code generated by Select.

SELECT 'EXEC [sim].[ROI_Save] ' + CONVERT(varchar(6), VSL.PathId)           + ', '
                                + Convert(varchar(6), @ROIResultId)         + ', '
                                + CONVERT(varchar(6), RT.routeTypeResultId) + ', '
                                + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + '; '
FROM  @routeResultIds RT
JOIN sim.ROI_Result_Vehicle VSL ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId

Results

Does one concatenate the results and call Exec?

While I would prefer to generate true dynamic SQL statements with strongly-typed parameters, concatenation is probably ok here because the proper way will be a lot more cumbersome. I do, however, prefer sp_executesql over EXEC(), always.

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

SELECT @sql = @sql + N'EXEC [sim].[ROI_Save] '
        + CONVERT(varchar(6), VSL.PathId)           + N', '
        + CONVERT(varchar(6), @ROIResultId)         + N', '
        + CONVERT(varchar(6), RT.routeTypeResultId) + N', '
        + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + N'; '
FROM  @routeResultIds AS RT
JOIN sim.ROI_Result_Vehicle AS VSL
ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId;

EXEC sys.sp_executesql @sql;

I still don't like that the parameters to the procedure aren't named, and if any of your parameter values were strings or dates, the build-up of single quotes would quickly get very annoying.


SSMS IntelliSense not working with sa user In SSMS (versions 17 and 18), IntelliSense is not working with the sa user, but it is working with another user.
If I connect to a SQL database with the sa user and create a query window, IntelliSense does not work in this window.
However, if I make a second connection to the same database with another user and create a query window, IntelliSense works.
If change back to the query window with sa user, IntelliSense again does not work.

When you create a user in sql, it is generally assigned the default bd you want to work with, that's why the IntelliSense works for you, although I think you shouldn't use the SA user for that, you can make the IntelliSense work by calling the bd with which you want to make inquiries ...
use [yourdb]
select * from yourtable


Creating a flexible table containing prices and periodes I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price.

Room Type |  01  |  02  |  03  |  04  |  05  | ... |  31  |
-----------------------------------------------------------
Single    | $100 | $100 | $200 | $200 | $200 | ... | $300 |
-----------------------------------------------------------
Double    | $150 | $150 | $200 | $250 | $350 | ... | $320 |

The best thing that I come accross is :

RoomsAvailable
--------------
 # ID
 room_type
 from_date
 to_date
 price

But, despite this table is flexible with the periodes, it is not with every single day's price.

Thanks in advance.

Here's one possibility.

The idea is that every time the price for a room changes, a PricingChange row would be added. From that date on, until there is another PricingChange row for the same RoomType, the price for that RoomType would be set to that row's price.

Sample data for this model:

With this sample data, Single rooms ware set at the price of $100 for June 1 2015, $50 for June 2 2015 to June 4 2015, and then back to $100 from June 5 2015 onwards. Double rooms are set to the price of $200 from June 1 2015 onwards.


Can I make sure two columns dont have the same value If I have a table that looks like this

CREATE TABLE foo (
   id INT NOT NULL AUTO_INCREMENT,
   aa INT NOT NULL,
   bb INT NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (aa, bb),
   CONSTRAINT aa_ref FOREIGN KEY (aa) REFERENCES bar (id),
   CONSTRAINT bb_ref FOREIGN KEY (bb) REFERENCES bar (id)
)

Is there a way to make sure that aa != bb besides using application level logic or forcing a trigger to fail on BEFORE INSERT?

No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK constraint:

ALTER TABLE foo
  ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK
    CHECK (aa <> bb) ;

I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal values and simply ignore the rows by accessing the table always through a view:

CREATE VIEW foo_correct AS
SELECT id, aa, bb
FROM foo
WHERE aa <> bb ;

Alternatively, you can restrict Insert and Update operations through (stored) procedures that take care of the constraint and not allow data to be inserted (or changed) that do not satisfy it.


Multiple self joins where joining condition not always exists I am trying to perform a multiple self join query where one of the conditions it is joining on does not always exist. I have a table that stores different sale events, where a single transaction (sale_id) can have multiple sale events associated with it - for example, transaction with sale_id 123 has four rows with sale_id 123 and sale_types SALE_PROPOSED, SALE_ACCEPTED, SALE_DONE, SALE_EXCEPTION.
I'm trying to write a query that joins all sale types based on sale_id and selects relevant information from them into a single result row. So far I got:
WITH SALES_TODAY AS(
SELECT *
FROM SALES
WHERE ..//date range logic for getting sales for the day)
SELECT SALE_PROPOSED.sale_id, SALE_PROPOSED.customer_id, SALE_ACCEPTED.price,
       SALE_ACCEPTED.accepted_date, SALE_DONE.done_date, SALE_EXCEPTION.exception_description
FROM SALES_TODAY SALE_PROPOSED, SALES_TODAY SALE_ACCEPTED,
     SALES_TODAY SALE_DONE, SALES_TODAY SALE_EXCEPTION
WHERE (SALE_PROPOSED.sale_type='SALE_PROPOSED')
AND (SALE_PROPOSED.sale_id=SALE_ACCEPTED.sale_id AND SALE_ACCEPTED.sale_type='SALE_ACCEPTED')
AND (SALE_PROPOSED.sale_id=SALE_DONE.sale_id AND SALE_DONE.sale_type='SALE_DONE')
AND (SALE_EXCEPTION.SALE_TYPE='SALE_EXCEPTION');

Example table excerpt
sale_id |    sale_type    | ....// other columns
------------------------------------------
123     | SALE_PROPOSED
123     | SALE_ACCEPTED
123     | SALE_DONE
123     | SALE_EXCEPTION
456     | SALE_PROPOSED
456     | SALE_ACCEPTED
456     | SALE_DONE

While it works fine for sales that have all 4 events, it returns 0 rows for sales that don't have a sale_type 'SALE_EXCEPTION' type associated with them.
I also have tried refactoring this SQL with left join for joining with 'SALE_EXCEPTION' but it returned duplicates for sales that had sale_type 'SALE_EXCEPTION' associated with them while working fine for sales without such event.
Could anyone advice what my query is missing so that it would return correct results despite whether a sales_id has sale_type 'SALE_EXCEPTION' or not. Thank you.

You use INNER JOIN (in comma-delimited tablenames form) whereas you must use LEFT JOIN.
WITH SALES_TODAY AS( /* daily selection logic */ ),

     sale_id_list AS ( SELECT DISTINCT sale_id FROM SALES_TODAY )

SELECT sale_id_list.sale_id, ...
FROM sale_id_list
LEFT JOIN SALES_TODAY AS SALE_PROPOSED
    ON SALE_PROPOSED.sale_id=sale_id_list.sale_id
    AND SALE_PROPOSED.sale_type='SALE_PROPOSED'
LEFT JOIN SALES_TODAY AS SALE_ACCEPTED
    ON SALE_ACCEPTED.sale_id=sale_id_list.sale_id
    AND SALE_ACCEPTED.sale_type='SALE_ACCEPTED'
LEFT JOIN ...


snychronize multiple databases using database replication As I show in the picture , I have three database servers (different town location) and client computers each one is running software.
Database schema for those three servers are same.

And now I have to synchronize those three servers.
So I upload the database to smarterasp.net.
I want to access this database from different local server and synchronize every Transaction ( Insert,Update,Delete ).

I found SQL Server Replication and there are three types of replication

Transactional replication
Merge replication
Snapshot replication

What approach should I use for this ?
Or any other way which is more suitable ?

Note: Sometime , the local server may be offline due to the condition of internet connection.

The best match for occasionally disconnected clients and bidirectional replication is merge replication.

Other flavours of replication support bidirectional replication, but transactional replication with updateable subscriptions is deprecated and peer-to-peer transactional replication is more suitable for high-speed low-latency links rather than remote sites.

Merge replication also offers the ability to synchronize over an HTTP connection, using an IIS ISAPI filter.

Merge replication allows you to define partitions of data and let each subscriber access a subset of the data. You could have a central database containing all the data and each subsidiary containing only the data that pertains to that subsidiary. This is accomplished by using join filers and/or static filters.

The only downside to merge replication is its performance toll: each table that you decide to publish has to be monitored for changes by a system-generated trigger and every published table (article) needs to contain a uniqueidentifier column (it gets added automatically in case it's missing).


xml parse in postgresSQL I have a problem with parse simple xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <data-set>
        <department>
            <ID>1</ID>
            <Name>IT</Name>
            <ID_CUSTOMER>1</ID_CUSTOMER>
            <ID_CUSTOMER>2</ID_CUSTOMER>
            <ID_CUSTOMER>3</ID_CUSTOMER>
            <ID_CUSTOMER>4</ID_CUSTOMER>
            <ID_CUSTOMER>5</ID_CUSTOMER>
            <Adress>Pražská</Adress>
            <Desc>platba kartou</Desc>
        </department>

    </data-set>

This XML file I parsed with use code:

DO $$
   DECLARE myxml xml;
BEGIN

myxml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));

DROP TABLE IF EXISTS my;
CREATE TABLE my AS

SELECT
     (xpath('//ID', x))[1]::text AS ID,
     (xpath('data-set/@Name', x))[1]::text AS Name,

     (xpath('//ID_CUSTOMER', x))[1]::text AS id_customer,
     (xpath('//Adress', x))[1]::text AS Adress,

     (xpath('//Desc', x))[1]::text AS tgen

FROM unnest(xpath('//data-set', myxml)) x
;

END$$;

select * from my

Unfortunately, this parse gives me only first single row in result.

I need create table where are all record in to relevant rows:

Rows1 - ID 1, ID_CUSTOMER 1, Adress Pražská, Desc Platba kartou
Rows2 - ID 1, ID_CUSTOMER 2, Adress Pražská, Desc Platba kartou
Rows3 - ID 1, ID_CUSTOMER 3, Adress Pražský, Desc Platba kartou
Rows4 - ........
Rows5 - ........

Thank you for your tips.

if I correct I understand You - Your problem is single row in result
You can use unnest function for return all values

EDIT Text of query after questions from author:

DO $$

DECLARE xml_string xml;

BEGIN
xml_string := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));

DROP TABLE IF EXISTS my;
CREATE TABLE my AS
SELECT
    unnest(xpath('//ID/text()', xml_string)) as ID,
    unnest(xpath('//Name/text()', xml_string)) as Name,
    unnest(xpath('//ID_CUSTOMER/text()', xml_string)) as ID_CUSTOMER;

END$$

SELECT * FROM my;

1   IT  1
1   IT  2
1   IT  3
1   IT  4
1   IT  5


Help with this query, joining three tables in mysql Can you help me with this query:
I have three tables

first, main table

page_id | title
1 aaa
2 bbb
3 ccc
4 ddd

revision tbl:

id | rev_page | rev_text_id
1 1 1
2 2 2
3 3 3
4 4 4
5 3 5
6 3 6
7 4 7

text table:

old_id | old_text
1 some_text_blob
2 some_text_blob
3 some_text_blob
4 some_text_blob
5 some_text_blob
6 some_text_blob
7 some_text_blob

I used this query

SELECT c.old_id,c.old_text
FROM text c
INNER JOIN revision b ON c.old_id=b.rev_text_id
INNER JOIN page a ON a.page_id=b.rev_page
WHERE a.page_id=3

and I get

3 some_text_blob
5 some_text_blob
6 some_text_blob

I need only the last one,
6 some_text_blob.

Order resulting table in the reverse chronological order and limit result to single row:

SELECT c.old_id,c.old_text
  FROM text     AS c
  JOIN revision AS b ON c.old_id=b.rev_text_id
  JOIN page     AS a ON a.page_id=b.rev_page
 WHERE a.page_id=3
 ORDER BY b.rev_text_id DESC
 LIMIT 1
;


When defining a foreign key in this Oracle example, what is the significance of the CONSTRAINT keyword? In the code below, The first column I create for the table courses is a column called department_code. I then define fk_courses_department_code and specify that department_code in this table will be a foreign key that will reference department_code from the departments table. What is the relevance, or significance of the CONSTRAINT fk_courses_department_code line? As far as I'm concerned, it is understood that department_code is a foreign key in this table. What exactly is the role of fk_courses_department_code then? Does it even exist as a real column? Is it just an alias for department_code? What is it, exactly?

CREATE TABLE courses
(
  department_code    VARCHAR2(2)   NOT NULL,
  course_number      NUMBER(3,0)   NOT NULL,
  course_title       VARCHAR2(64)  NOT NULL,
  course_description VARCHAR2(512) NOT NULL,
  credits            NUMBER(3,1)   NOT NULL,
  CONSTRAINT pk_courses
    PRIMARY KEY (department_code, course_number),
  CONSTRAINT fk_courses_department_code
      FOREIGN KEY (department_code)
      REFERENCES departments (department_code),
);

What exactly is the role of fk_courses_department_code then?

It is the name of the constraint.

This is useful to e.g. drop the constraint later, as that can only be done by supplying the name.


MongoDB - Can I create TTL index with rolling method Im getting stuck with archiving a huge amount of data in MongoDB 3.6
I want to delete 506 million records in a collection. I tried to remove using bulk.remove(), but that is also slow. 50 records are removing per second.
But somewhere I read, TTL index and do scan interval every 1 hour. So it'll remove in faster way.
But if I create this index in foreground, it'll lock the collection. So i'm thinking to do with the rolling index creation method.
If do like that, lets say on a 3 node replica set, detach the node3 and then create index. Once its created it'll start automatically remove the data. Then once I add the node back to the Replica set, maybe the primary will do the delete after I create the index, that time it'll try to replicate, in the worst case, the data is already removed on that node, then it'll break the replication?

Can I create TTL index with rolling method

Yes, this is a supported approach for building indexes on replica sets. However, if your goal is to efficiently remove a large quantity of existing documents there are some caveats to be aware of as noted below.

  I tried to remove using bulk.remove(), but that is also slow.(50 records are removing per second).

A TTL index will not speed up removal of documents if you already have an index that supports finding expired documents: the TTL thread still needs to find & remove matching documents so will be doing similar work to a bulk remove.

I would investigate why your current bulk remove operations are slow. For example, make sure you have an optimal index in place to find documents to remove and monitor your system resources (memory, I/O, network, ...) to ensure there aren't any obvious bottlenecks.

If you have a large number of documents that are ready to be removed when the TTL index is created, this could have a significant performance impact. Bulk remove queries with a supporting index would allow more control over the impact since you can add query criteria to restrict the range of documents matching each bulk deletion.

  But somewhere I read, TTL index and do scan interval every 1hour. So it'll remove in faster way.

That timing is incorrect: the TTL deletion task runs every 60 seconds. Based on an indexed date field the TTL monitor can either expire documents after a specified number of seconds has passed or expire documents at a specific clock time.

Assuming your documents have a range of expiry dates, once the initial removal of expired documents is complete a TTL index will be able to delete documents in smaller batches which will be less impactful than an infrequent bulk delete.

  But if I create this index in foreground, it'll lock the collection. So im thinking to do with the rolling index creation method.

Prior to MongoDB 4.2, a foreground index build on a populated collection will block all other operations on the database that holds that collection. For a populated collection in a production environment you will definitely want to use either a rolling index build or a background index build. The rolling index build ensures that only one of your replica set members is building an index and allows a foreground index build to complete faster, however this approach does include some risk of that member becoming stale while running in standalone mode.

MongoDB 4.2+ uses an optimised index build process that limits the lock scope to the affected collection and only holds an exclusive lock at the beginning and end of the index build. You can still use the rolling index build approach but there is no longer a foreground vs background index build distinction.

  If do like that, lets say on a 3 node replica set, detach the node3 and then create index. Once its created it'll start automatically remove the data.

The TTL index thread on replica set members only deletes documents when a member is in the primary state. Document deletes are replicated via the oplog so secondaries always have a consistent point of time with the current primary.

If you restart a replica set member in standalone mode, the TTL collection monitor will not be started (again, to keep the secondary state consistent).


Can you Identify the sql server name from sql backup file(.bak)? I have a backup file from an SQL 2016 server and I need to identify which SQL server the file was created on. Is there a way to identify the original SQL server where the file originated at?  Thank you.

You could run a RESTORE HEADERONLY on the backup file to read the contents and get the ServerName column.

For example:

CREATE DATABASE BLABLA
GO

backup database BLABLA
to disk = 'c:\temp\BLABLA.BAK'

RESTORE HEADERONLY FROM DISK = 'c:\temp\BLABLA.BAK'

Result:

With the servername column being the one you want for the backup(s) residing in the backup file.


Drop tables but space not claimed in postgres 12 I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link  (-k) option.
So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4).
Yesterday I have dropped two tables of size 700GB and 300GB.
After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few ~50GBs have been freed from storage partition.
I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none.
Note : Still I have not deleted old data directory (v9.5). I am not sure if deleting will impact my running postgres 12 as I have used 'link' option in pg_upgrade.
Looking for the solution.

You can use TRUNCATE first before dropping and that'll automatically reclaim the space for you. See more information in this StackOverflow: PostgreSQL drop table command is not reducing DB size
Though this shouldn't have to be your process normally with dropping tables, it might help clue you in on why the space isn't being reclaimed.


Compatibility level when upgrading a SQL Server 2005 database to SQL Server 2012 I would like to move my databases which are currently running on SQL Server 2005 to SQL Server 2012, using the Copy Database Wizard in SQL Server Management Studio.

Should I first set the database compatibility level to SQL Server 2012 (while the database still exists on SQL Server 2005) or should I set the compatibility level to SQL Server 2005 after I've moved the database?

You can not set the compatibility level of a SQL Server 2005 instance to anything higher.
Hence the upgrade path:

Create SQL Server 2012 Server / Instance.

Option 1: Backup / Restore; change compatibility level.
Option 2: Detach, copy, Attach; change compatibility level.

Option 1 is most used because you have overlap (old is available, new can be tested).
Rollback to previous is certain.

Misread your question. You want to remain at SQL 2005 compatibility: Don't change compatibility level during option 1 or 2. Compatibility is configured at the database level.


Optimize command issuing "'View is not base table" and "Corrupt" error messages I have no experience with MySQL. My boss ran an optimize against a MySQL database. Against a bunch of views we got the message

  "tablexyz" is not BASE TABLE

and the next has message is

  Corrupt

It seems to be only against views, we didn't get any of these error messages against the base tables.

Does this look like an incorrect error message, or do we have issues with our tables?

You cannot run OPTIMIZE TABLE against a View. I can see why the error message would be Corrupt

In mysql, a view is represented as a table in INFORMATION_SCHEMA.TABLES.

I have discussed views in this light before

However, a View in INFORMATION_SCHEMA.TABLES has NULL as a Storage Engine.

Mar 21, 2012 : How do I get the execution plan for a view?
Nov 23, 2011 : How can I define the datatype of a view in MySQL?

Consequently, there must be a storage engine to optimize on. Since the Storage Engine is NULL, Corrupt error message is understandable. Therefore, don't be concerned with the error message. Just run OPTIMIZE TABLE on the underlying base tables.


SQL Server 2012 - Dropping Snapshots takes hours We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL 2005 the Job that drops the snapshots used to finish in few seconds. But once the server has been upgraded to 2012, the same job takes close to an hour to complete.

The log files for the job contains a message

  "Nonqualified transactions are being rolled back. Estimated rollback
  completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified
  transactions are being rolled back. Estimated rollback completion:
  100%."

This message is repeated several times. While monitoring the underlying database, we have found some blocking on the database during that time. But if I am correct, a blocking on the underlying database should not delay a statement to drop its snapshot.

May I know if I am correct? If so, what could be the reasons for this delay after upgrading to 2012? Any missing settings at the server or database level? Thanks.

Can you please confirm the Compatibility level of the database you upgraded? (Right-click the database -> Properties -> Options -> Compatibility level). If this is set to 2005, please don't change it without thoroughly testing your applications, etc. as it could definitely break your environment! Just curious as to the value here.

Also, I did some work with a test system and didn't see the behavior you describe. Even if I had an open transaction using the DB snapshot I could drop it from another session and the query running on the snapshot would immediately fail (rather than block me from dropping the DB snapshot).

I would recommend taking a closer look at the disk where the DB snapshot is created (since dropping the DB snapshot physically deletes the snapshot file created). There possibly could be speed issues there causing the delete of the snapshot file to simply take close to an hour...or there could be contention at the file level (something trying to back up the DB snapshot file while you're trying to drop the DB snapshot, virus tools scanning the DB snapshot file, etc.).


For alignment optimized table is bigger than original table - why? In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and performance did not change. Of course I did a VACUUM ANALYZE. How comes?

(I see that the index sizes won't change if I only index single columns.)

This is the table I was coming from (I've added sizes + padding):

                               Table "public.treenode"
    Column     |           Type           | Size |          Modifiers
---------------+--------------------------+------+-------------------------------
 id            | bigint                   | 8    | not null default nextval( ...
 user_id       | integer                  | 4+4  | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 project_id    | integer                  | 4    | not null
 location      | real3d                   | 36   | not null
 editor_id     | integer                  | 4+4  |
 parent_id     | bigint                   | 8    |
 radius        | real                     | 4    | not null default 0
 confidence    | smallint                 | 2    | not null default 5
 skeleton_id   | integer                  | 4    | not null

With real3d being defined as

CREATE TYPE real3d AS (
  x real,
  y real,
  z real);

I changed this layout to the following:

                                Table "public.treenode_new"
    Column     |           Type           | Size |            Modifiers
---------------+--------------------------+------+--------------------------------
 id            | bigint                   | 8    | not null default nextval(' ...
 project_id    | integer                  | 4    | not null
 location_x    | real                     | 4    | not null
 location_y    | real                     | 4    | not null
 location_z    | real                     | 4    | not null
 editor_id     | integer                  | 4    | not null
 user_id       | integer                  | 4    | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 skeleton_id   | integer                  | 4    | not null
 radius        | real                     | 4    | not null default 0
 confidence    | real                     | 4+4  | not null default 5
 parent_id     | bigint                   | 8    |

If I am not mistaken, I should save 66 bytes per row (138 is one original row, 72 is a new row). This however is not happening: With 7604913 in these tables, the original table had a size of 1020 MB. The new  table has a size of 1159 MB. I used pg_size_pretty(pg_relation_size('<tablename>')) to measure the sizes. So what am I missing?

One note: all but the last four columns are inherited from another table (of which I of course had to change the layout, too).

Update: After running VACUUM FULL as suggested by Erwin Brandstetter, the new table needs only 734 MB.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.


Unable to restore dump file in mysql5.5 I'm trying to restore a mysql5.5 dump file using mysql command prompt using the command given below:

mysql> -u <username> -p <password> <dbname> < <dumpfile>

But it is giving the below error:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use near '-u ro
  ot -p password mydb< "D:\mysql_dump\backup_2013_01_mydb.sql"' at line 1

Please help. I've tried it on both WinXP and Win7.
Also please guide how to do the same using Mysql Workbench.

Please check the link below for the answer.

https://dba.stackexchange.com/a/36043/20734

Thank You


checking availability of rooms I have 3 table in my sql database:

Table Room

roomnumber   roomtype
  1           DeluxeRoom
  2           DeluxeRoom
  3           DeluxeRoom
  100         StandardRoom
  101         StandardRoom
  102         StandardRoom

Room is a table which store the room and their type.

Table Reservation

reservationid  checkin       checkout
  1           2017-01-01     2017-01-03
  2           2017-01-10     2017-01-20
  3           2017-02-05     2017-02-08

Reservation is a table where users make their booking.

Table RoomReserve

reservationid    roomnumber
 1                 1
 2                 100
 2                 101
 3                 1
 3                 2
 3                 3

In RoomReserve, for each reservationid, I can input multiple values for roomnumber. I can also input same roomnumber for different reservationid if the checkin and checkout date are not the same.

I am trying to make a query  to count the available rooms when user want to book rooms but the results i am getting is wrong.

Here are the codes i am trying:

SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
        SELECT  *
            FROM  roomreserve r,reservation e, room m
            WHERE  r.reservation_id=e.reservation_id
              AND  m.roomnumber=r.roomnumber
              AND  ('$checkin' BETWEEN start_date AND end_date)
              OR  ('$checkout' BETWEEN start_date AND end_date)
                          );
2)
SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
SELECT  COUNT(roomnumber)
    FROM  roomreserve r,reservation e, room m
    WHERE  r.reservation_id=e.reservation_id
      AND  m.roomnumber=r.roomnumber
      AND  ('$checkin' BETWEEN start_date AND end_date)
      OR  ('$checkout' BETWEEN start_date AND end_date)
                          );

This is a somewhat common logical error.

('$checkin' BETWEEN start_date AND end_date) OR
('$checkout' BETWEEN start_date AND end_date));

The problem is not the way the query is written, it's that you're using the wrong criteria. A reservation can conflict while neither of these is true, simply by starting before and ending after the desired values.

What you actually need to find is the overlap.

A room is not available if it has any existing reservation with the following conditions true:

a check-in before your desired check-out and
a check-out after your desired check-in

Any reservation that starts before your desired end and ends after your desired start is a conflict.

Stated another way, a check-in after your desired check-out cannot possibly be a conflict, and a check-out before your desired check-in cannot possibly be a conflict.  All others are conflicting.

So you want to identify these reservations:

(end_date > '$checkin') AND
(start_date < '$checkout');

Note also that you must never build queries with variable interpolation, as you have done here.  It is an unsafe practice for which there are no genuine exceptions, only excuses.  Use prepared statements and placeholders.


Create database level constants (enumerations) without using CLR? I have a several SQL objects that need to take alternate actions based on a desired state of the request.  Is there a way to create database level constants (enumerations) that can be passed to stored procedures, table-valued functions, and used in queries (without using CLR)?

CREATE PROCEDURE dbo.DoSomeWork(@param1 INTEGER, ..., @EnumValue myEnumType)  AS ...;

and then use it:

EXEC doSomeWork 85, ..., (myEnumType.EnumValue1 + myEnumType.EnumValue2);

Where myEnumType would hold a few enumeration values.

In the procedure I would be able to use @EnumValue and test it against values in myEnumType to do the required work. I would make the values of myEnumType a bitmask for the case I am considering.

For a simple example, consider an expensive process that takes a huge dataset and reduces it to a smaller but still very large dataset. In this process you need to make some adjustment in the middle of that process that will affect the result. Say this is filter for (or against) some types of records based on some status of an intermediate calculation within the reduction. The @EnumValue of type myEnumType could be used to test for this

SELECT   ...
FROM     ...
WHERE       (@EnumValue & myEnumType.EnumValue1 = myEnumType.EnumValue1 AND ...)
        OR  (@EnumValue & myEnumType.EnumValue2 = myEnumType.EnumValue2 AND ...)
        OR  ...

Are these sort of database level constants possible in SQL Server without the use of CLR?

I am seeking a database level enumeration or set of constants that can be passed as parameters to stored procedures, functions, and so on.

You can create an enumeration type in SQL Server using a XML Schema.

For example Colors.

create xml schema collection ColorsEnum as '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Color">
        <xs:simpleType>
            <xs:restriction base="xs:string">
                <xs:enumeration value="Red"/>
                <xs:enumeration value="Green"/>
                <xs:enumeration value="Blue"/>
                <xs:enumeration value="Yellow"/>
            </xs:restriction>
        </xs:simpleType>
    </xs:element>
</xs:schema>';

That allows you to use a variable or parameter of the type xml(dbo.ColorsEnum).

declare @Colors xml(dbo.ColorsEnum);
set @Colors = '<Color>Red</Color><Color>Green</Color>'

If you try to add something that is not a color

set @Colors = '<Color>Red</Color><Color>Ferrari</Color>';

you get an error.

Msg 6926, Level 16, State 1, Line 43
XML Validation: Invalid simple type value: 'Ferrari'. Location: /*:Color[2]

Constructing the XML like that can be a bit tedious so you can for example create a helper view that also holds the allowed values.

create view dbo.ColorsConst as
select cast('<Color>Red</Color>' as varchar(100)) as Red,
       cast('<Color>Green</Color>' as varchar(100)) as Green,
       cast('<Color>Blue</Color>' as varchar(100)) as Blue,
       cast('<Color>Yellow</Color>' as varchar(100)) as Yellow;

And use it like this to create the enumration.

set @Colors = (select Red+Blue+Green from dbo.ColorsConst);

If you would like to create the view dynamically from the XML Schema you can extract the colors with this query.

select C.Name
from (select xml_schema_namespace('dbo','ColorsEnum')) as T(X)
  cross apply T.X.nodes('//*:enumeration') as E(X)
  cross apply (select E.X.value('@value', 'varchar(100)')) as C(Name);

The enumeration can of course also be used as parameters to functions and procedures.

create function dbo.ColorsToString(@Colors xml(ColorsEnum))
returns varchar(100)
as
begin
  declare @T table(Color varchar(100));

  insert into @T(Color)
  select C.X.value('.', 'varchar(100)')
  from @Colors.nodes('Color') as C(X);

  return stuff((select ','+T.Color
                from @T as T
                for xml path('')), 1, 1, '');
end

create procedure dbo.GetColors
  @Colors xml(ColorsEnum)
as
select C.X.value('.', 'varchar(100)') as Color
from @Colors.nodes('Color') as C(X);

declare @Colors xml(ColorsEnum) = '
<Color>Red</Color>
<Color>Blue</Color>
';

select dbo.ColorsToString(@Colors);

set @Colors = (select Red+Blue+Green from dbo.ColorsConst);
exec dbo.GetColors @Colors;


What is the way to paginate last updated rows from multiple tables? I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results.

Row1 = Post's - text, username
Row2 = Photo's - text, picture_url, username
Row3 = Post's - text, username
Row4 = Photo's - text, picture_url, username

I was thinking having a table inheritance called Activity that will hold all types of objects, referencing them with the columns item_type and item_id.

This way I query directly from the Activity table.

This should work. But how to maintain the activity to "stay tuned" of it's items "updated_at"?

When I comment in a post, should the comment update the post "updated_at" column and the post update the "updated_at" column on the activity?

Thanks!

EDIT
Activities table:

  id   event      item_type   item_id   parent_type    parent_id    updated_at
  1   "create"      Post         1         Group           100
  2   "create"      Post         2         Group           100
  3   "create"     Comment       1         Post            1
  4   "create"      Photo        1         Group           100

Query: The latest root events (parent_type is "Group") from activities. Should bring Post 1, Photo 1 and Post 2.

SELECT * FROM activities WHERE
parent_type = "**Group**" and parent_id = 100
ORDER BY created_at

Still not retrieving the latest. Because the comments didn't affected the Post activity.

You may add a column timestamp and a trigger that update the column when the row is updated.

Then, sorting by that column will get your result.


Do I need a locking read? I am inserting rows in a table, based on a value and on the existence of a row, which  I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The basic steps are (pseudocode)

function doStuff(int someID){
    found = `SELECT someIDFROM targettable`
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

The problem is ofcourse that if process1 reads uniqueID, figures it should insert the value, and process2 does the same before the write is finished, I get 2 lines in my targettable. This is possible (someID is not unique for that table), but in this case unwanted because it was done on wrong assumptions because of the concurrency.

Now my assumption/feeling is that a transaction alone is not enough for this. The second process will have consistent reads I think, but that does not help prevent the second INSERT, does it? so this will not do (I think, please advice me on this. This could be where I'm going wrong). (I'm quite sure at the moment this is not enough to ensure correct behaviour, because of testing)

function doStuff(int someID){
    'START TRANSACTION HERE'
    found = `SELECT someIDFROM targettable where someid = someID `
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
    COMMIT TRANSACTION
}

From what I  read about InnoDB locking reads it could be that I should use those, but I'm not sure as I have never worked with them. It could also be the transaction is enough, and I'm just begin foolish.

My current idea is to use SELECT someID FROM targettable FOR UPDATE, which I figure will give me a lock. But I'm not sure what lock, since it depends on isolation level. This would look like so:

function doStuff(int someID){
    found = `SELECT someIDFROM targettable where someid = someID FOR UPDATE`
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

I'm not sure how to cope with the value I get from another table, but that might be another thing.

Summarizing: Can I get away with just using a transaction, do I need to use a locking-read (if so, how?) or is there another option I missed?

Update: If I do something like

START TRANSACTION;
SELECT * FROM targettable WHERE someid = 3 FOR UPDATE

And query that select in another thread, I get no result (locked?) until this transaction is finished. But if I put an INDEX on that someid, I DO! I'm probably missing something, but I wasn't expecting that.
How can I be sure I don't insert this row twice?

I have a suggestion that could simply your code and your SQL

You are doing round trips at this point by checking each individual someID, coming back to the client, seeing if the check failed. If the check failed, it is OK to perform the INSERT.  That's another round trip. You will require some kind of locking for reads.

If you have to INSERT 10000 rows:

In the best case, you run 10000 SELECTs are find that someID exists
In the worst case, you run 10000 SELECTs, each someID does not exist, and do 10000 INSERTs.

Why not just perform all this with a single SQL statement ?

function doStuff(int someID) {
    getvalue = `SELECT value FROM statetable`
    INSERT IGNORE INTO targettable VALUES (someID,getvalue);
}

This works only if someID is unique in targettable

In your code, try submitting a list of someID values, iterate that list, and submit INSERT IGNORE commands for all someID values. That you can do in a START TRANSACTION/COMMIT block.

function doStuff(int someIDList) {
    int idcount = 0;
    getvalue = `SELECT value FROM statetable`
    for someID in someIDList
    do
        if idcount = 0 then
            START TRANSACTION
        endif;
        idcount++;
        INSERT IGNORE INTO targettable VALUES (someID,getvalue);
    end while;
    if idcount > 0 then
        COMMIT
    endif;
}


Query shows sort cost,even when required index is available This question is from SO,iflagged it move to DBA.SE..but i may not see that happening,due to upvotes..so posting here

below is test data  :

--Main Table

 CREATE TABLE [dbo].[LogTable]
    (
      [LogID] [int] NOT NULL
                    IDENTITY(1, 1) ,
      [DateSent] [datetime] NULL,
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent_LogID] ON [dbo].[LogTable] ([DateSent] DESC) INCLUDE ([LogID]) ON [PRIMARY]
GO

--Cross table

 CREATE TABLE [dbo].[LogTable_Cross]
    (
      [LogID] [int] NOT NULL ,
      [UserID] [int] NOT NULL
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable_Cross] WITH NOCHECK ADD CONSTRAINT [FK_LogTable_Cross_LogTable] FOREIGN KEY ([LogID]) REFERENCES [dbo].[LogTable] ([LogID])
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_Cross_UserID_LogID]
 ON [dbo].[LogTable_Cross] ([UserID])
 INCLUDE ([LogID])
GO

-- Script to populate them
 INSERT INTO [LogTable]
        SELECT TOP 100000
                DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0)
        FROM    sys.sysobjects
                CROSS JOIN sys.all_columns

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                1
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                2
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                3
        FROM    [LogTable]
        ORDER BY NEWID()

GO

When we use below simple query  ,

SELECT  DI.LogID
FROM    LogTable DI
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID
        WHERE  DP.UserID = 1
ORDER BY DateSent DESC

query shows sort

As per my understanding,sort cost should be avoided,since we have the required index

 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]

and also digging into plan shows the same index is used..

My questions are  :
1.Why sort cost is still present
2.What exactly does ordered property is true means .

Few observations/work i have done :
1.Ordered property is set to false

so i have rewrote query like below

   SELECT  DI.LogID
    FROM    LogTable DI
            INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID
            WHERE  DP.UserID = 1  and di.datesent is not null
    ORDER BY DateSent DESC

rewriting like above,makes ordered property to true,but still sort is present

closest,i can find is this article by Paul White:A Tale of Two Index Hints ,in this article,below Point clarifies why this sort occurs

  For very large tables, the optimizer may calculate that an IAM-driven scan might save more time than would be consumed by the extra sort, and a plan featuring the unordered scan + sort would be chosen.  This is a heuristic optimization: the optimizer knows nothing about the actual fragmentation level of an inde

But this query requires sort and it shouldn't satisfy the conditions for an IAM scan

Let me know ,if you need any more details

In query plans, ordered property set to true means that an IAM-driven scan was not done. The data was read in the logical order according to the index definition.

Hash join does not preserve order. Your first query has a hash join so the explicit SORT is needed at the end. One way to avoid the sort in the query is to do a nested loop join with LogTable as the outer table. On my machine I was able to accomplish this with a variety of hints:

SELECT  DI.LogID
FROM LogTable DI
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID
        WHERE  DP.UserID = 1
ORDER BY DateSent DESC
OPTION (MAXDOP 1, LOOP JOIN, FORCE ORDER, NO_PERFORMANCE_SPOOL);

This is a very inefficient query plan because the right index is not defined on the LogTable_Cross table. A seek is done against that table, but only one the UserId column:

Filtering on LogID is done in the join itself. I can create an index that better supports the query plan that I'm looking for:

CREATE INDEX IX_LogTable_Cross ON LogTable_Cross (LogID, UserID);

The creation of that index does not guarantee a query plan without a sort. SQL Server may estimate that a plan with a sort has a lower cost. However, if I eliminate all hints except for the LOOP JOIN then I get a reasonably efficient query plan without a sort: