Is it allowed to for two tables to have two types of relationships between them? I have been going back and forth working with relational and no-sql databases for some time now and my questions may be the result of mixing the way of thinking about how to approach a problem.
So consider a simple multiple-choice quiz scenario where a question has an undefined number of answers (meaning it is not a fixed number of answers that each question has. One question could have two alternatives and another could have five). This question will have one correct answer. As such, if we want to have an initial translation between what I just wrote to SQL terms, it will be something like this:
Question                Answer
===============|       |==========
Id             |       |Id
---------------|------<|----------
Title          |       |Body
---------------|-------|----------
CorrectAnswerId|       |QuestionId

So in my mind, a question can have many answers, and an answer can only be linked to one question -> One-to-Many relationship between Question and Asnwer.
Also each question has one and only one correct answer -> One-to-One relationship between Question and Asnwer.
What I am trying to ask is, Is this design acceptable? If not what approach can I take?
One thing that I could do is to add a IsCorrectAnswer flag to the Answer entity. However it makes updating the correct answer to a question more difficult and (in my opinion) error-prone.
Clarification of what I mean by "allowed"
I would like the data to be normalized as much as possible (which I think won't be with my first solution) and I would like not to have any update, insert and delete anomalies either (which I think the second solution "might" have)

This might sound a bit crazy, but as noted by ypercube in the answer linked in the comments, it is best to consider this a separate relation: for which we need a new table Question_CorrectAnswer, and it's PK is QuestionID.
This means that we only need to update a single row to change the correct answer.
Our only issue is that to prevent update anomalies, whereby someone could enter an Answer as being the correct one for a different Question, we must redefine Answer's primary key as being AnswerID, QuestionID, and FK on those two columns together.
So we define our tables like this:
(please don't take this as the best way to write CREATE TABLE, I've left out constraint names for example)
CREATE TABLE Question (
    QuestionId int IDENTITY PRIMARY KEY,
    Title varchar(100) NOT NULL
)

CREATE TABLE Answer (
    AnswerId int IDENTITY,
    QuestionId int NOT NULL REFERENCES Question (QuestionID),
    Body varchar(1000) NOT NULL,
    PRIMARY KEY (AnswerID, QuestionID)
)

CREATE TABLE Question_CorrectAnswer (
    QuestionId int NOT NULL PRIMARY KEY,
    AnswerId int NOT NULL,
    FOREIGN KEY (AnswerId, QuestionID) REFERENCES Answer (AnswerID, QuestionID)
)

It does have the downside of requiring an extra join, though.

As you seem to prefer a 1:1 relationship between questions and answers and you also don't want to reuse answers, add the CorrectAnswer to the answer, so you can have multiple correct answers per question and a re so more flexible.
CorrectAnswer is more a Attribute  for answers than for questions, so it makes more sense to put it there
Question                Answer
===============|       |==========
Id             |       |Id
---------------|------<|----------
Title          |       |Body
                       |----------
                       |QuestionId
                       |----------
                       |CorrectAnswer

As i getting downvoted i expand my answer, as i claimed the correct  answer belongs to the table answers.
You always have to put a bit of effort into it, not only on the part of the gui.
The stored procedure is only an example, you can get also the correct answers, if you collect the is that are tested. And with a bot of flow control, you can do anything in sql

CREATE TABLE Question (id int,Title  Varchar(50))

INSERT INTO Question VALUES (1,'Title1'),(2,'Title2')

✓

CREATE TABLE Answer (Id int,body Text,QuestionId int,CorrectAnswer Tinyint )

INSERT INTO Answer VALUES (1,'text1',1,0),(2,'text2',1,1),(3,'text3',1,1),(4,'text4',2,0),(5,'text5',2,1),(6,'text6',2,0)

SELECT * FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1

Id | body  | QuestionId | CorrectAnswer
-: | :---- | ---------: | ------------:
 2 | text2 |          1 |             1
 3 | text3 |          1 |             1

SELECT DISTINCT 1  AS Result FROM Answer WHERE QuestionId = 1 AND Id =  1 AND CorrectAnswer = 1

| Result |
| -----: |

SELECT DISTINCT 1 As REeslit  FROM Answer WHERE QuestionId = 1
AND  (3 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)
AND  2 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1))

| REeslit |
| ------: |
|       1 |

SELECT DISTINCT 1 As Result  FROM Answer WHERE QuestionId = 1
AND  (3 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)
OR  2 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1))

| Result |
| -----: |
|      1 |

CREATE PROCEDURE CheckValid_Answer (IN _QuestionId INT,
  IN _ChoosenNumberList varchar(4000), OUT NoCorrectAnswer INT, OUT NoFalseAnswer INT)
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
        DECLARE _int INT;
  -- declare cursor for answer numbers
  DEClARE curAnswers
      CURSOR FOR
          SELECT DISTINCT val FROM temp;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;
        -- Get all answer numvers
        SET @ChoosenNumberList := _ChoosenNumberList;
        SET @QuestionId := _QuestionId;
        CREATE TEMPORARY TABLE temp (val INT);
        SET @S1 = CONCAT("INSERT INTO temp (val) VALUES ('",REPLACE((SELECT GROUP_CONCAT( DISTINCT  @ChoosenNumberList) AS data ), ",", "'),('"),"');");
        PREPARE stmt1 FROM @s1;
        EXECUTE stmt1;
        SET @sql = CONCAT('SELECT IF(EXISTS(SELECT 1 As Result  FROM Answer WHERE QuestionId = ',
        @QuestionId,' AND (');
        SET @countcor := 0;
        SET @countfal := 0;
  OPEN curAnswers;

  getsql: LOOP
      FETCH curAnswers INTO _int;
      IF finished = 1 THEN
          LEAVE getsql;
      END IF;
      -- build query
      SET @sql2 := CONCAT(@sql,_int
      ,' IN (SELECT Id FROM Answer WHERE QuestionId = ',@QuestionId,' AND CorrectAnswer = 1)              )),@countcor := @countcor +1,@countfal := @countfal +1)' );
      PREPARE stmt1 FROM @sql2;
                EXECUTE stmt1;
  END LOOP getsql;
  CLOSE curAnswers;
SET NoCorrectAnswer := @countcor;
SET NoFalseAnswer := @countfal;
END

CALL CheckValid_Answer(1,'1,3',@OK, @false);

| IF(EXISTS(SELECT 1 As Result  FROM Answer WHERE QuestionId = 1 AND (1 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)             )),@countcor := @countcor +1,@countfal := @countfal +1) |
| -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: |
|                                                                                1 |

| IF(EXISTS(SELECT 1 As Result  FROM Answer WHERE QuestionId = 1 AND (3 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)             )),@countcor := @countcor +1,@countfal := @countfal +1) |
| -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: |
|                                                                                1 |

SELECT @OK, @false;

@OK | @false
--: | -----:
  1 |      1

db<>fiddle here


How to do a case sensitive search in WHERE clause? I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.

Any idea on how to do a case sensitive search in SQL query?

by default, MySQL does not consider the case of the strings

This is not quite true.  Whenever you create database in MySQL, the database/schema has a character set and a collation.  Each character set has a default collation; see here for more information.

The default collation for character set latin1, which is latin1_swedish_ci, happens to be case-insensitive.

You can choose a case-sensitive collation, for example latin1_general_cs (MySQL grammar):

CREATE SCHEMA IF NOT EXISTS `myschema`
DEFAULT CHARACTER SET latin1
COLLATE latin1_general_cs ;

This has an effect on things like grouping and equality.  For example,

create table casetable (
  id int primary key,
  thing varchar(50)
);

select * from casetable;
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

In a case-sensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| ABC   |        1 |
| aBc   |        1 |
| abC   |        1 |
| abc   |        1 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
+----+-------+

While in a case-insensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| abc   |        4 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Note that you can also change the collation from within a query.  For example, in the case-sensitive database, I can do

select * from casetable where thing collate latin1_swedish_ci = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Try this for insensitive search, it works fine with great performance:

"SELECT phone FROM user WHERE lower(user_name) like ".srtlower($username);


How to delete a Backup from a backup set in SQL Server 2008 R2 The title pretty much sums it up. We are running out of space on our backup disks, and need to remove old backups out of the set. I can't seem to find any information on this.

I would change your backup strategy to write to individual files for each database backup.  You could then have a maintenance plan step that examines your backup location and removes *.bak files older the X days.

Well, if you really want to get a db with those selected items you could restore the database to a new database and choose the backups you want to keep whilst restoring.


How to get multiple row data into a row with multiple columns I have a MySQL table like this:

User_Id  course_name     course_location   course_id
1        course name 1   location 1        1
1        course name 2   location 2        2
1        course name 3   location 1        3
2        course name 2   location 1        2
2        course name 4   location 4        4

How can I get data a result like this:

User_id  course 1       course2        course3        course4
1        yes-location1  yes-location2  yes-location1  NULL
2        NULL           yes-location1  NULL           yes-location4

You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.
Query:
SELECT User_id
    , MAX(
        CASE WHEN course_id = 1 THEN course_location END
    ) as Course_1
    , MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
    , MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
    , MAX(CASE WHEN course_id = 4 THEN course_location END) as Course_4
FROM data
GROUP BY User_id;

Sample query in SQL Fiddle.
You can replace course_location by CONCAT('YES-', course_location) is the leading YES is indeed needed.
Output:
User_Id | Course_1      | Course_2      | Course_3      | Course_4
1       | location 1    | location 2    | location 1    | (null)
2       | (null)        | location 1    | (null)        | location 4

Table Creation:

CREATE TABLE VMRRTEST_DATA (
        ID INTEGER,
        CATG VARCHAR(30 ),
        PRICE INTEGER,
        DATE TIMESTAMP
    )

Insertion:

INSERT INTO VMRRTEST_DATA VALUES(1,'CAR',1000,CURRENT TIMESTAMP);

INSERT INTO VMRRTEST_DATA VALUES(2,'CAR',2000,(CURRENT TIMESTAMP)- 10 MINUTE);

INSERT INTO VMRRTEST_DATA VALUES(3,'CAR',1000,CURRENT TIMESTAMP - 20 minute);

INSERT INTO VMRRTEST_DATA VALUES(4,'CAR',30000,CURRENT TIMESTAMP);

INSERT INTO VMRRTEST_DATA VALUES(5,'CAR',5000,(CURRENT TIMESTAMP)-20 minute);

Result set:

ID CATG PRICE DATE
 -- ---- ----- --------------------------
  1 CAR   1000 2018-10-09 22:17:59.907636

  4 CAR  30000 2018-10-09 22:18:32.58254

  2 CAR   2000 2018-10-09 22:30:30.875961

  3 CAR   1000 2018-10-09 22:20:43.80537

  5 CAR   5000 2018-10-09 22:21:14.787224

Expected Result set:

 RANK ID CATG PRICE DATE
 ---- -- ---- ----- --------------------------
    1  3 CAR   1000 2018-10-09 22:20:43.80537
    1  2 CAR   2000 2018-10-09 22:30:30.875961
    1  5 CAR   5000 2018-10-09 22:21:14.787224
    1  4 CAR  30000 2018-10-09 22:18:32.58254

Solution:

SELECT Rank,ID,CATG,PRICE,DATE FROM
(

SELECT
--ROW_NUMBER() OVER(ORDER BY PRICE ASC) AS RW_NM,
RANK() OVER (PARTITION BY PRICE ORDER BY DATE DESC) AS Rank,

ID,CATG,
PRICE
,DATE
FROM
VMRRTEST_DATA
--WHERE Rank
GROUP BY ID,DATE,CATG,PRICE

HAVING COUNT(*)>0
ORDER BY PRICE,DATE DESC
)
WHERE Rank = 1


update table with data from other table in remote oracle db I have table X from remote Oracle DB A, and table Y in local Oracle DB B.

The data in Y is a partial copy of the data in X (X has some columns that aren't needed in Y; the columns I need in Y have the same names in both tables).

I periodically need to synchronize the data Y with what's in X (this is unidirectional - there's nothing in Y that needs to be copied back to X). Y has constraints that prevent simply dropping all the data in Y and repopulating it; I need to update what's already in 'Y', and insert any new rows from X that don't already exist in Y.

I've looked at a couple of options that won't work:

The copy feature in SQL Developer would copy X into a brand new table, not into Y
The export option would insert everything as new records, not update existing rows

What's the best way to do this?

I did it by:
creating dblink from db A to DB B
and using : merge into Y y using select X.columns from X@dblink x on (x.idX=y.idY)
when matched then update set
y.columns=x.columns --here ids are not included
when not matched then
insert (y.columns) values(x.columns) --ids are included
x.colums and y.colums mean shared columns

Have you checked

table_exists_action=replace

Parameter in data pump import (impdb)

table_exists_action tips


Why do I get "Truncated incorrect date value" when using str_to_date()? I have data in one of my tables as "Sun Dec 14 22:49:13 2014 -0800" and need to load it into another table but skipping the time filed i.e. HH:MM:SS.

I used:

insert into final_data
select Book_Name, Author_Name, Author_email,
str_to_date(substring_index(date_string,' ',4),'%a %b %d %Y'),
Total_Sold
from data;

  ERROR 1292 (22007): Truncated incorrect date value: 'Sun Dec 14 22:49:13'

I can use str_to_date(substring_index(date_string,' ',5),'%a %b %d %T %Y') but that will add HH:MM:SS in the column as well.
Is there a way to load the data skipping HH:MM:SS ?
i.e instead of 2014-12-14 22:49:13 it diplays 2014-12-14

you have to pass your datetime column into date() function like this :
create table test (
  id int AUTO_INCREMENT,
  datetime_string datetime,
  date_date date,
  PRIMARY KEY (id)
);

insert into test(datetime_string) values('2014-12-14 22:49:13');
update test set date_date = date(datetime_string);

http://sqlfiddle.com/#!9/cc200b/1


Why is add timestamp column with default not null so slow in Oracle 11g? In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows).
I cannot have more than 30 - 60 seconds of downtime when I run it in production.

This statement completes instantly:

alter table
my_table
add my_timestamp_col timestamp(6) with time zone;

However I would like to default the column to current_timestamp at time zone 'UTC' and force it be non-null.

With other data types, Oracle 11g will do this quickly if you use "add column  default  not null"
For example both of these complete instantly:

alter table
my_table
add my_varchar_col varchar2(10) default 'hello' not null;

alter table
my_table
add my_date_col date default sysdate not null;

But with a timestamp it is slow (at least tens of minutes on my test DB). I assume this means it is updating every row.

So my questions are:

Why is it different with a timestamp than other data types?
Is there a fast way to accomplish the outcome where the column is
forced to be non-null and new rows default to the current UTC
timestamp?

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

This is actually improved in Oracle 12c.

From Oracle Magazine's Ask Tom column:
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

  In Oracle Database 11g you were able to perform a fast add of a column
  to a table if it had a default value and was defined as NOT NULL.
  (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you
  attempted to add a column with a default value and that column
  permitted null values, the ADD COLUMN operation could take a
  significant amount of time, generate a large amount of undo and redo,
  and lock the entire table for the duration of the operation. In Oracle
  Database 12c, that time, volume, and locking are no longer part of the
  process.

I know this doesn't help if you're stuck on 11gR2, since you probably want to get that column added before taking an outage to upgrade the DB.

As to why it takes so long only when the data type is TIMESTAMP, I'm at a loss.  Maybe since the TIMESTAMP data type is a few more bytes to store than DATE, or than VARCHAR2 for empty strings, and those extra bytes are causing a cascade of row migrations.  To test that theory, try adding a fixed length CHAR column (in a non-Prod environment): alter table my_table add my_char_col CHAR(200) default 'hello' not null; I expect this would not be instantaneous due to row migrations.

Your best bet on 11gR2 might be either to

Take an outage at an appropriate time that is ok with your stakeholders to add this column

or

Use DBMS_REDEFINITION to add the column as part of an online redefinition.  This should be non-blocking, and even though it may take hours, the users will not even notice it happening (no locking).  If you have a lot of indexes, this becomes a little more difficult.  If you have foreign keys, this becomes even more difficult.  If you have stored procs or views that reference this table, be sure to recompile them after you FINISH the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).

or

Maybe add the column without the NOT NULL constraint.  When that finishes, then alter the column to add the NOT NULL constraint. My intuition is that you'll still get locking somewhere in the process, so no promises. Maybe just a crazy idea.

If you want to try the Online Redefinition, then you might find these links helpful:

http://www.oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1.php
http://docs.oracle.com/cd/E18283_01/server.112/e17120/tables007.htm

Fast add timestamp column (two steps) tested on table with ~2e9 rows Oracle 12.1:
But this works with null only.

alter table T1 add C1 timestamp(6) null;

alter table T1 modify (C1 default systimestamp);


Can PostgreSQL use nulls in its indexes? I've been reading this book which says that

  The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this condition.

I recognize that the book is more than 10 years old, but it has already proven quite useful--Using instructions gleaned from its pages, I've sped a query up by a factor of ten.

Further, in running EXPLAIN ANALYZE on a SELECT query, I've found that none of my indexes are being used, even when by all rights, they ought to be.

Thus, my question is:

Supposing there is a table which has a column, whose column definition includes "NOT NULL", and that an index exists which covers this column, would this index be used in a query of that table where the columns is part of the query?

Like:

CREATE TABLE my_table(
a varchar NOT NULL
);

CREATE INDEX ix_my_table ON my_table(a);

SELECT a from my_table;

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either.

If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index.

I can't figure out what you're trying to say with:

  If this is correct, is my understanding that an index on a column defined as "NOT NULL" not be used in a query which uses that column?

Certainly an index won't get used for an IS NOT NULL condition on a NOT NULL column. It'd always match 100% of rows, so a seqscan will almost always be much faster.

PostgreSQL won't use an index if the index doesn't filter out a large proportion of rows for a query. The only likely exception is when you're asking for a set of columns covered by a single index, in an order matching that of the index. PostgreSQL might do an index-only scan then. E.g. if there's an index on t(a, b, c) and you:

select a, b FROM t ORDER BY a, b, c;

PostgreSQL might use your index, even though no rows are filtered out, because it only has to read the index and can skip reading the heap, avoid doing a sort, etc.

You haven't posted your query or example data.  But the most common reason indexes are not used has to do with volume.

Indexes are like a phonebook that translates a column to a row location.  If you're only looking for a few rows, it makes sense to look up each row in the phonebook, and then look up the row in the main table.

But for more than a few rows, it's cheaper to skip the phonebook, and iterate over all rows in the main table.  In my experience the tipping point is around 100 rows.


Change the behavior of temporal tables to log actual value changes rather than dummy updates Currently the articles I read on temporal tables push the responsibility of the clean-up to the application code. Is it possible to configure the temporal tables to record particular column value changes instead of when the update statement is called on the table?

Example for the expected behavior:

USE Master;
GO

-- Create test database
CREATE DATABASE [TemporalTables];
GO

USE TemporalTables;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Hidden period columns
CREATE TABLE [dbo].[PersonHistory](
    [PersonID] [int] NOT NULL,
    [EmailAddress] [varchar](75) NULL,
    [IsSubscribed] [bit] NULL,
    [SysStartTime] datetime2  NOT NULL,
    [SysEndTime] datetime2 NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_PersonHistory
   ON PersonHistory;
CREATE NONCLUSTERED INDEX IX_PersonHistory_ID_PERIOD_COLUMNS
   ON PersonHistory (SysEndTime, SysStartTime, PersonID);
GO

CREATE TABLE [dbo].[Person](
    [PersonID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [EmailAddress] [varchar](75) NULL,
    [IsSubscribed] [bit] NULL,
    [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)
);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:11:56.6294004

--Insert Rows
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed]) VALUES ('SD.Burman@gmail.com', 1);
GO

INSERT [dbo].[Person] VALUES ('RD.Burman@gmail.com', 1);
GO

INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed], [SysStartTime], [SysEndTime])
VALUES ('AR.Rehman@gmail.com', 1, default, default);
GO

-- Query the main table
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
-- Shows all changes for specific PK (for inserts to show up)
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
FOR SYSTEM_TIME ALL
where Personid IN (1,2,3);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:13:27.0374068

--Update Rows (Actual Updates)
UPDATE [dbo].[Person] SET [EmailAddress] = 'SD.Burman@hotmail.com' WHERE [EmailAddress] = 'SD.Burman@gmail.com';
GO
UPDATE [dbo].[Person] SET [EmailAddress] = 'RD.Burman@hotmail.com' WHERE [EmailAddress] = 'RD.Burman@gmail.com';
GO
--Update Rows (Dummy Update)
UPDATE [dbo].[Person] SET [EmailAddress] = 'AR.Rehman@gmail.com' WHERE [EmailAddress] = 'AR.Rehman@gmail.com';
GO

-- Query the history table
SELECT *
FROM dbo.PersonHistory

-- !!!
-- What I'd want is that the Id = 3 update to not get saved since the data did not change.
-- Also a way I can limit the number of columns I want this behavior on.
-- PersonID    EmailAddress                                                                IsSubscribed SysStartTime                SysEndTime
-- ----------- --------------------------------------------------------------------------- ------------ --------------------------- ---------------------------
-- 1           SD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5018193 2017-03-14 16:13:27.0374068
-- 2           RD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5118172 2017-03-14 16:13:27.1484051
-- !!!

Similar question highlighting this behavior:

Do temporal tables log changes when there are none?

I'm looking for a workaround for the mentioned behavior.

Change Data Capture would be able to handle this (or even custom triggers for that matter), but I'm exploring if I could get the benefits of temporal tables with selective history recording.

The challenge is, we have an ETL job that touches the syncdatatime columns often, and we want to exclude or include certain columns, as well only record when the actual data value changes, preferably with the temporal tables.

On a further dive it seems that this currently falls under : Manage Retention of Historical Data in System-Versioned Temporal Tables as a reactionary solution to the problem, instead of a prior configuration.

And they propose three approaches:

Stretch Database
Table Partitioning
Custom Cleanup Script

There are some other nice articles I found, which partially answer my question, however, I'd love if I can manage this behavior at the point of data creation rather than the cleanup, so I'd request more information in case someone has it, or if this changes in the future.

Other articles:
Managing Temporal Table History in SQL Server 2016
Temporal Table Considerations and Limitations

We use an old technique where each record has a 'Checksum'.  This checksum is calculated in a trigger whenever a record is created or updated.We compare the checksums on the records and if they are different then we update the record.
Something like this...
UPDATE PRODUCTIONTABLE
SET
EMAILADDRESS = STAGINGTABLE.EMAILADDRESS
WHERE
PRODUCTIONTABLE.UNIQUEKEY = STAGINGTABLE.UNIQUEKEY
AND PRODUCTIONTABLE.CHECKSUM <> STAGINGTABLE.CHECKSUM


SQL Server: reset all database tables I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to recreate database.

Is there any way to keep the database and just reset it?

Recreate is the better option, because:

You end up with a script to create the db
It's dirt simple.
You don't have to take the complexity of your schema in to account.
SQL Server will be more than happy to create the script for you.

The other option is to write something that looks through the schema, identifies tables with foreign key constraints so they can be dropped, drops them, then finds the identities and reseeds them, then puts the FKs back

Or if you are a masochist, you could write (well try to) something to use the dependency order

Both of options 2 and 3 are high maintenance.

Execute both queries to reset auto incremented id

string Query1 = @"ALTER TABLE [Your Table] DROP COLUMN id";
string Query2 = @"ALTER TABLE [Your Table] ADD id int IDENTITY";


Does Reporting Services lock tables? Possible Duplicate:
  Does SSRS lock the table when querying?

I'm using SQL Server 2008 R2 and Reporting Services. Do the reports block other queries or are they executed in highly isolated transactions, such as a snapshot?

Next obvious question: Can reporting services produce deadlocks or timeouts in other queries as a result of locking tables for a long time?

FYI, unless specifically directed, all TSQL statements are presumed Snap-Shots.

1st Question:

So Think of it as your SSRS is looking at your data as if it were a View/Select statement.

2nd Question:

It could, pending on the volume of calculation (SSRS) and volume of users.  Think you would have to have well over 10% CPU/RAM utilization from SSRS and over 50% CPU/RAM utilization from general queries in order for you to notice an effect, albeit hardly noticeable (like 1-10 nSecs).


How to not allow to insert a new record if count of current records exceeds a specified limit I have two tables as the following
team
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`member_limit` INT NOT NULL

team_member
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`team_id` BIGINT NOT NULL,
`member_id` BIGINT NOT NULL,

CONSTRAINT `fk_team_member_team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`),
CONSTRAINT `fk_team_member_member_id` FOREIGN KEY (`member_id`) REFERENCES `user` (`id`)

My question is on MySQL
How to not allow to insert a new member into team_member when the inserted member's team_id reference to team that count of its current members on team_member exceeds its member_limit

CREATE TRIGGER tr_bi_check_limit
BEFORE INSERT
ON team_member
FOR EACH ROW
BEGIN
    IF ( SELECT COUNT(*)
         FROM team_member
         WHERE team_id = NEW.team_id ) > ( SELECT member_limit - 1
                                           FROM team
                                           WHERE id = NEW.team_id ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Team limit reached!';
    END IF;
END

fiddle with some explanations.

Cross row constraints are tricky and the implementation depends on the API you present.  You may be able to adapt this technique https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:4233459000346171405 from AskTOM.
As recommended in the comments, here is a solution that works but has its limitations.  When dealing with cross row constraints, my recommendation is to carefully evaluate any solution with a complete set of requirements.  The question, as posted, only mentioned the INSERT limitation; however, not acting on UPDATEs and DELETEs would cause problems.
I opted for an AFTER INSERT/UPDATE/DELETE because I generally prefer to modify related tables in an AFTER trigger.  That may not be the right solution for your specific use case, so caveat emptor.
The lack of deferrable constraint checking in MySQL makes this type of cross row constraint difficult to make robust.  Assuming MySQL is a hard requirement, the assignment of users to teams should probably be managed by stored procedures.

CREATE TABLE team (
  `id`            BIGINT PRIMARY KEY AUTO_INCREMENT,
  `name`          VARCHAR(255) NOT NULL,
  `member_count`  INT,
  `member_limit`  INT NOT NULL
);

CREATE TABLE user (
  `id`            BIGINT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE team_member (
  `id` BIGINT   PRIMARY KEY AUTO_INCREMENT,
  `team_id`     BIGINT NOT NULL,
  `member_id`   BIGINT NOT NULL,
  CONSTRAINT `fk_team_member_team_id` FOREIGN KEY (`team_id`)
    REFERENCES `team` (`id`),
  CONSTRAINT `fk_team_member_member_id` FOREIGN KEY (`member_id`)
    REFERENCES `user` (`id`)
);

ALTER TABLE team
  ADD CONSTRAINT enforce_team_member_limit
    CHECK ((member_count >= 0) AND (member_count <= member_limit))
;

DELIMITER //
CREATE OR REPLACE TRIGGER tr_ai_update_team_size
AFTER INSERT
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = NVL(member_count,0) + 1
      WHERE id=NEW.team_id;
END;
//
CREATE OR REPLACE TRIGGER tr_au_update_time_size
AFTER UPDATE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = NVL(member_count,0) + 1
      WHERE id=NEW.team_id;
    UPDATE team SET member_count = NVL(member_count,0) - 1
      WHERE id=OLD.team_id;
END;
//
CREATE OR REPLACE TRIGGER tr_ad_update_team_size
AFTER DELETE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = NVL(member_count,0) - 1
      WHERE id=OLD.team_id;
END;
//
DELIMITER ;

Some test statements

INSERT INTO team VALUES (1, 'Team 1', null, 3);
INSERT INTO team VALUES (2, 'Team 2', null, 2);

INSERT INTO user VALUES (1);
INSERT INTO user VALUES (2);
INSERT INTO user VALUES (3);
INSERT INTO user VALUES (4);

COMMIT;

INSERT INTO team_member VALUES (1, 1, 1);
INSERT INTO team_member VALUES (2, 1, 2);
INSERT INTO team_member VALUES (3, 1, 3);

INSERT INTO team_member VALUES (4, 1, 4);

DELETE FROM team_member where team_id=1 and member_id=3;
INSERT INTO team_member VALUES (4, 1, 4);

Here is a mockup on dbfiddle.
Pros:

Avoids having a SELECT clause in the trigger (avoiding a full index
scan or shudder full table scan plus the attendant concerns about
transaction isolation)

The check constraint enforces the limitation in the team table, thus
preventing buffoonery

The member_count column could make some routine queries more efficient
(e.g. how many people are on a team).

Cons:

An update statement that swaps users between teams that are full will
fail because MySQL does not support deferrable constraints.

The introduction of the member_count column.

I am not a big fan of having columns that are essentially derived from something else stored in the database, but I will make the trade-off for performance or constraint checking reasons.  I tend to advocate for constraints because, in my experience, making the database enforce data quality prevents buffoonery by applications or users.


How to JOIN more than two tables? Following is the schema of my Tables:

Marks_Table
(SubjectCode,TypeID,MarksObtd)
Subjects_Table
(SubjectCode, Subject)
Types_Table
(TypeID, Type)

I want to Select the Marks_Table table with SubjectCode and TypeID replaced with appropriate Subject and Type.

I wrote the following query:

SELECT [Subject] , [Type] , MarksObtd
FROM [Marks_Table]
INNER JOIN [Subjects_Table]
   ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode
INNER JOIN [Types_Table]
  ON *XXXXXXXXXXXXXXX*.TypeID = [Types_Table].TypeID

What should I write in place of XXXXXXXXXXXXXXX ? What should be the name of the table that has not been created yet?

It can be done in two queries, but is it possible to do this like above? and how?

try this

SELECT [Subject] , [Type], MarksObtd
  FROM [Marks_Table]
  INNER JOIN [Subjects_Table]
  ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode
  INNER JOIN [Types_Table]
  ON [Marks_Table].TypeID = [Types_Table].TypeID

As james added comment, I think it is a simple join and you need to change [TypeID] in select clause to [Type]

Check this out;

SELECT [subject_id],[subject_name]
FROM [relationship_table] ON subject_id = subject_id
    JOIN [subject] ON [subject_id] = [relationship_table_id]
WHERE subject_id = [Any_key]


ceil() function behavior in postgresql While using the ceil() in postgresql, I found some strange behavior of it, Please see SQL :

Select
     *,
     ceil( (550*0.5)/100 ),
     ceil( (58*5)/100 ),
FROM
   XYZ

If I focus on the output of ceil functions it is strange :

ceil( (550*0.5)/100 ) Returning 3 ( Which converts 2.75 to 3 )
ceil((58*5)/100 ) Returning 2 ( Which converts 2.9 to 2 )

Why 2nd statement returning 2 if it is closed to the greater integer 3 ?

Description
  The PostgreSQL ceil function returns the smallest integer value that is greater than or equal to a number.

And according Postgres Docs:

  Except where noted, any given form of a function returns the same data type as its argument.

As you can see it works properly, in fact, if you use integers the returned value is one integer (58 * 5) / 100) returns 2 but if you forces it to decimal (58.0 * 5.0) / 100.0 then it returns 2.9.

select
     (550 * 0.5) / 100 as C1,
     ceil((550 * 0.5) / 100) as CEIL_C1,
     (58 * 5) / 100 as C2,
     ceil((58 * 5) / 100) as CEIL_C2,
     (58.0 * 5.0) / 100.0 as C3,
     ceil((58.0 * 5.0) / 100.0) as CEIL_C3
;

                c1 | ceil_c1 | c2 | ceil_c2 |                 c3 | ceil_c3
-----------------: | ------: | -: | :------ | -----------------: | ------:
2.7500000000000000 |       3 |  2 | 2       | 2.9000000000000000 |       3

dbfiddle here

integer division truncates the result

Documentation


SSIS ETLs deadlocking We have an SSIS package in our SQL Server 2008 server to load data onto our system every night. The SSIS package consists of several ETLs (around 20) that are currently run sequentially.

On some nights it spits out below deadlock messages for certain ETLs.
Some nights pass with no errors at all. Every night what's run on the server is the same.

The specific ETLs deadlocking are not the same. For instance,

Monday it'll be ETL1, ETL3, ETL9.
Tuesday & Wednesday – all OK.
Thursday – ETL2, ETL5, ETL6, ETL7.
Friday – all OK.

Below is the 19th night's error message in SQL Server Agent:

  Executed as user: localhost. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  04:26:33  Error: 2016-11-19 04:31:00.39     Code: 0xC0202009     Source: DFT_Extract_ETL1 OLE_DST_ETL_Destination [68]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".  End Error  Error: 2016-11-19 04:31:00.61     Code: 0xC0209029     Source: DFT_Extract_ETL1 OLE_DST_ETL_Destination [68]     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81)" failed because error code 0xC020907B occurred, and the error row disposition on "input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2016-11-19 04:31:00.84     Code: 0xC0047022     Source: DFT_Extract_ETL1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE_DST_ETL_Destination" (68) failed with error code 0xC0209029 while processing input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2016-11-19 04:31:01.11     Code: 0xC02020C4     Source: DFT_Extract_ETL1 OLE_SRC_ETL1[1]     Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.  End Error  Error: 2016-11-19 04:31:01.31     Code: 0xC0047038     Source: DFT_Extract_ETL1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "OLE_SRC_ETL1" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  04:26:33  Finished: 04:41:09  Elapsed:  875.54 seconds.  The package execution failed.  The step failed.

Check out this link. It has clear explanation of digging down the issue.

https://stackoverflow.com/questions/8390322/cause-of-a-process-being-a-deadlock-victim


How can I search the full text of a stored procedure for a value? I use the following script to search the text of all stored procedures when I want to find specific values.

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @searchText
ORDER BY ROUTINE_NAME

I recently discovered that ROUTINE_DEFINITION stops after 4000 characters, so some procedures were not getting returned when they should have been.

How can I query the full text of a stored procedure for a value?

Use one of

sys.sql_modules
Red Gate SQL Search (free!)
OBJECT_DEFINITION

Never use

INFORMATION_SCHEMA
sys.syscomments

SELECT name
FROM sys.all_objects
WHERE  Object_definition(object_id) LIKE '%text%'
ORDER BY name


SQL Server Replication - FTP Snapshot: Policy requires SSL SQL Server 2014 on both Publisher & Subscriber.
Publisher & Subscriber are across the internet.
Replication is Transactional Replication

Error messages:

  The process could not connect to FTP site '' using port 21. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL20032)

  Get help:
  http://help/MSSQL_REPL20032

  220 Microsoft FTP Service

  534 Policy requires SSL.

  (Source: MSSQL_REPL, Error number: MSSQL_REPL12003) Get
  help: http://help/MSSQL_REPL12003

Is there a way around this without relaxing SSL policy for the FTP Server ?

Yes you can. You open the FTP site in IIS and change the SSL Settings from Require to Allow.

You have to check with the owners of the FTP site if this is allowed and I would recommend to use https if possible as this way you are transmitting the username and password using cleartext


Storing bus routes in a database I have done some research and found that I should store a route as a sequence of stops. Something like:

Start -> Stop A -> Stop B -> Stop C -> End

I have created three tables:

Routes
Stops
RouteStops

...where RouteStops is a junction table.

I have something like:

Routes

+---------+
| routeId |
+---------+
|    1    |
+---------+
|    2    |
+---------+

Stations

+-----------+------+
| stationId | Name |
+-----------+------+
|     1     |   A  |
+-----------+------+
|     2     |   B  |
+-----------+------+
|     3     |   C  |
+-----------+------+
|     4     |   D  |
+-----------+------+

RouteStations

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     2       |       A       |
+-------------+---------------+
|     2       |       D       |
+-------------+---------------+

Route 1 goes through

Station A -> Station C -> Station D

Route 2 goes through

Station A -> Station D

Is this a good way to store routes?

According to Wikipedia:

  [...] the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified [...]

Can I rely on such a database schema or maybe this should be done differently?

This is actually my university project, so I'm just wondering if such schema can be considered as a correct one. For this case, I would probably store only several routes (approx 3-5) and stations (approx 10-15), each route will consist of about 5 stations. I would be also glad to hear how this should look like in case of real and big bus company.

For all business analysis leading to database architecture, I recommend writing rules:

A route has 2 or more stations
A Station can be used by many routes
Stations on a route come in a specific order

The 1st and 2nd rules as you noticed implies a many to many relationship so you concluded rightfully to create routeStations.

The 3rd rule is the interesting one. It implies that an extra column is needed to fit the requirement. Where should it go? We can see that this property depends on Route AND Station. Therefore it should be located in routeStations.

I would add a column to table routeStations called "stationOrder".

+-------------+---------------+---------------
| routeId(fk) | stationId(fk) | StationOrder |
+-------------+---------------+---------------
|     1       |       1       |       3      |
+-------------+---------------+---------------
|     1       |       3       |       1      |
+-------------+---------------+---------------
|     1       |       4       |       2      |
+-------------+---------------+---------------
|     2       |       1       |       1      |
+-------------+---------------+---------------
|     2       |       4       |       2      |
+-------------+---------------+---------------

Then querying becomes easy:

select rs.routeID,s.Name
from routeStations rs
join
Stations s
on rs.stationId=s.StationId
where rs.routeId=1
order by rs.StationOrder;

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+

Notes:

I fixed the StationId in RouteStations in my example. You are using the StationName as the Id.
If you don't use a route name, then there's not even a need for routeId since you can get that from routeStations
Even if you would link to the route table, your database optimizer would notice it doesn't need that extra link and simply remove the extra steps.

To develop on note 3, I've built the use case:

This is Oracle 12c Enterprise.

Note that in the execution plan below that table routes isn't used at all. the Cost Base Optimizer (CBO) knows it can get the routeId directly from routeStations's primary key (step 5, INDEX RANGE SCAN on ROUTESTATIONS_PK, Predicate Information 5 - access("RS"."ROUTEID"=1))

--Table ROUTES
create sequence routeId_Seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

CREATE TABLE routes
(
  routeId  INTEGER NOT NULL
);

ALTER TABLE routes ADD (
  CONSTRAINT routes_PK
  PRIMARY KEY
  (routeId)
  ENABLE VALIDATE);

insert into routes values (routeId_Seq.nextval);
insert into routes values (routeId_Seq.nextval);
commit;

--TABLE STATIONS
create sequence stationId_seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

create table stations(
   stationID INTEGER NOT NULL,
   name varchar(50) NOT NULL
);

ALTER TABLE stations ADD (
  CONSTRAINT stations_PK
  PRIMARY KEY
  (stationId)
  ENABLE VALIDATE);

insert into stations values (stationId_seq.nextval,'A');
insert into stations values (stationId_seq.nextval,'B');
insert into stations values (stationId_seq.nextval,'C');
insert into stations values (stationId_seq.nextval,'D');
commit;
--

--Table ROUTESTATIONS
CREATE TABLE routeStations
(
  routeId       INTEGER NOT NULL,
  stationId     INTEGER NOT NULL,
  stationOrder  INTEGER NOT NULL
);

ALTER TABLE routeStations ADD (
  CONSTRAINT routeStations_PK
  PRIMARY KEY
  (routeId, stationId)
  ENABLE VALIDATE);

ALTER TABLE routeStations ADD (
  FOREIGN KEY (routeId)
  REFERENCES ROUTES (ROUTEID)
  ENABLE VALIDATE,
  FOREIGN KEY (stationId)
  REFERENCES STATIONS (stationId)
  ENABLE VALIDATE);

insert into routeStations values (1,1,3);
insert into routeStations values (1,3,1);
insert into routeStations values (1,4,2);
insert into routeStations values (2,1,1);
insert into routeStations values (2,4,2);
commit;

explain plan for select rs.routeID,s.Name
from ndefontenay.routeStations rs
join
ndefontenay.routes r
on r.routeId=rs.routeId
join ndefontenay.stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 1000
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2617709240

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |    79 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                  |     1 |    79 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                  |       |       |            |          |
|   3 |    NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RS"."ROUTEID"=1)
   6 - access("RS"."STATIONID"="S"."STATIONID")

Now the fun part, let's add a column name to the route table.
Now there's a column we actually need in "routes".
The CBO uses the index to find the rowID for route 1, then accesses the table (table access by index rowid) and grabs the column "routes.name".

ALTER TABLE ROUTES
 ADD (name  VARCHAR2(50));

update routes set name='Old Town' where routeId=1;
update routes set name='North County' where routeId=2;
commit;

explain plan for select r.name as routeName,s.Name as stationName
from routeStations rs
join
routes r
on r.routeId=rs.routeId
join stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 500
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3368128430

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |   119 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                  |                  |     1 |   119 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                  |       |       |            |          |
|   3 |    NESTED LOOPS                 |                  |     1 |   119 |     0   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| ROUTES           |     1 |    40 |     0   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | ROUTES_PK        |     1 |       |     0   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("R"."ROUTEID"=1)
   8 - access("RS"."ROUTEID"=1)
   9 - access("RS"."STATIONID"="S"."STATIONID")

I am speaking as an application programmer:

Don’t even think of doing routing or timetabling with queries against the database (or in a stored proc) it will never be fast enough.  (Unless this is just a “homework” problem.)

Even for an application that processes the data in memory loading the data from the database will never be fast unless all the data is loaded at start up, or the data is stored in a demoralised form.  Once the data is demoralised there is little point in using a relational database.

Therefore I would think of the database as being the “master” copy of the data and accept that I will also have to store it pre-processed in the application memory, or in a cashing server like membase.

ndefontenay’s answer gives a good table design as a starting point, but you are have to consider that routes have different timing depending on the time of day and often have different stops depending on time, day of week, or even school holidays.


Checking whether two tables have identical content in PostgreSQL This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table.

A PostgreSQL solution would be fine, but a generic one would be better. I found http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data, but I don't understand the logic used.

Background: I re-wrote some database generating code, so I need to check whether the old and new code produce identical results.

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data):

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

Or with EXISTS to return just a boolean value or a string with one of the 2 possible results:

SELECT CASE WHEN EXISTS (TABLE a EXCEPT TABLE b)
              OR EXISTS (TABLE b EXCEPT TABLE a)
            THEN 'different'
            ELSE 'same'
       END AS result ;

Tested  at SQLfiddle

Also not the that EXCEPT removes duplicates (that should not be a worry if your tables have some PRIMARY KEY or UNIQUE constraint but it may be if you are comparing results of arbitrary queries that can potentially produce duplicate rows).

Another thing that EXCEPT keyword does is that it treats NULL values as identical, so if table A has a row with (1,2,NULL) and table B has a row with (1,2,NULL), the first query will not show these rows and the second query will return 'same' if the two tables have no other row.

If you want to count such rows as different, you could use a variation on gsiems' FULL JOIN answer, to get all the (different) rows:

SELECT *
FROM a NATURAL FULL JOIN b
WHERE a.some_not_null_column IS NULL
   OR b.some_not_null_column IS NULL ;

and to get a yes/no answer:

SELECT CASE WHEN EXISTS
            ( SELECT *
              FROM a NATURAL FULL JOIN b
              WHERE a.some_not_null_column IS NULL
                 OR b.some_not_null_column IS NULL
            )
            THEN 'different'
            ELSE 'same'
       END AS result ;

If all the columns of the two tables are not nullable, the two approaches will give identical answers.

Looking at the linked code you don't understand:

select count(*) from
(
select * From EmpDtl1
union
select * From EmpDtl2
)

The secret sauce is using union as opposed to union all.  The former retains only distinct rows whereas the latter keeps duplicates (reference).  In other words the nested querys says "give me all rows and columns from EmpDtl1 and in addition those from EmpDtl2 which are not already in EmpDtl1".  The count of this subquery will be equal to the count of EmpDtl1 if and only if EmpDtl2 does not contribute any rows to the result i.e. the two tables are identical.

Alternatively, dump the tables in key sequence to two text files and use your comparison tool of choice.


Netezza Error Record size 120000 exceeds internal limit of 65535 bytes I have a column PARAM that has JSON data that looks like this:

JSON:{"Name":"RES_InformationalIssues","StepName":"Page_1","StepData":"f601e9fe-d68a-4ed7-bbaa-f40c9cbc4d12=General Product Information","SessionId":"ab04f2c4-ddge-4555-c792-01d9518e7148"}

I would like to have multiple columns (Name, StepName, StepData, SessionID) with their data in them.

SELECT substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21) as Name, substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) as StepName FROM Table

That query won't work exactly right since I have changed the names but I think you get the picture. When I run the query for either of these columns I get the data I am looking for. But when they are run together as I have above, I get the error:

ERROR [HY000] ERROR:  Record size 120000 exceeds internal limit of 65535 bytes

It feels like this means that I shouldn't assign the text of PARAM as the column name but that isn't what I am trying to do. Any suggestions or clarifications needed?

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name,
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17)
  as varchar (100)) as StepName
FROM Table

I am guesisng you are creating a table and you are maxing out the column/row size. break apart into more tables. if that is true.


Listener pointing to incorrect AlwaysOn Availability Group All, Thanks in advance
I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener.  Instance 1's listener points to Instance 1, no issue.
Instance 2 listener also points to instance 1.  I'm at a loss why listener #2 is showing databases for Instance 1.  Ive recreated instance 2's HAdr groups from scratch but still no change.  Here is some additional information -

Both instances Database mirroring endpoints are configured on different ports.
The SQL Server instances are named instances also using different ports.
Listeners have different names and different IP addresses and ports.
Opening up each SQL instance on each of the three nodes show the correct HADR information and databases.

The Listener seems to be getting confused and is looking at the wrong instance.

Any help would be greatly appreciated
thanks
James

An Availability Group Listener simply connects the user to a database instance. Once they are connected to the instance they can access all the databases on the instance. It doesn't matter of the databases are part of the availability group or not. This is by design.

In other words everything is working exactly as it should be.

connect your Named SQL Instance 2 listener like below

"listener name\static port"  or "listener name\Namedinstance"

then Instance 2 will point your named instance database.


How can I get individual rowcounts like SSMS? I have a client c# program that is executing stored procedures via ExectueNonQuery, including catching the PRINT and Error output with InfoMessage events.  It works fine, but I have noticed something odd.

When I execute a stored procedure from SSMS, it displays rowcounts for each individual SQL statement that is executed in the Messages tab (as though it were coming from the InfoMessages).  However my program never sees these messages, though it does catch all of the same other output.  Instead, it just returns the rows affected in the ExecuteNonQuery function result that is the sum of all of the individual rowcounts (which is kind of useless).

For example, this procedure:

use [tempdb]
go

SELECT  *
INTO    MyCols
FROM    sys.columns
go

CREATE PROC foo As

    UPDATE  MyCols
    SET     name = name + N''
-- SSMS shows (662 row(s) affected)

    UPDATE  MyCols
    SET     name = name + N''
    WHERE   name like '%x%'
-- SSMS shows (59 row(s) affected)

PRINT 'bar'
-- both SSMS and ExecuteNonQuery get this

-- ExecuteNonQuery returns 721 rows affected
GO

When the foo proc is run, SSMS displays rowcounts of 662 and 59, but ExecuteNonQuery only returns the total of 721.

So, how can I get the same information that SSMS is getting?

Just to be clear here: I am not interested in how to change the stored procedures to add PRINT @@ROWCOUNTs after every SQL statement.  I know how to do that and it's not an option most of the time for a variety of reasons.

I am asking how to do what SSMS is doing here.  I can change the client code all I want at this point (for right now, anyway) and I would like to do it right.

The SqlCommand.StatementCompleted event will fire after each statement in a batch, and one of the properties of the event (well, pretty much the only property) is the number of rows affected by the statement that fired the event.

Some notes:

A requirement of getting this info is that you did not specify SET NOCOUNT ON;, or conversely, you did specify SET NOCOUNT OFF;.
All events fire at the completion of each Execute___(), not during the execution.
The StatementCompletedEventArgs.RecordCount includes row counts from SELECT statements, whereas the SqlDataReader.RecordsAffected property only reports row counts from DML statements (INSERT, UPDATE, DELETE, etc).
The StatementCompleted event does not include the individual SQL statement from the batch that fired the event. However, the event handler is sent the sender as an input parameter and this is the SqlCommand of the query batch, and you can see that batch by casting sender to SqlCommand and then looking at the CommandText property (this is shown in the example below).

The documentation is very sparse on this so I have worked up an example that shows this event firing for both ExecuteNonQuery and ExecuteScalar, as well as for both ad hoc queries and stored procedures (i.e. SqlCommand.CommandType of Text vs StoredProcedure):

using System;
using System.Data;
using System.Data.SqlClient;

namespace StatementCompletedFiring
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection _Connection =
                          new SqlConnection("Integrated Security = True;"))
            {
                using (SqlCommand _Command = new SqlCommand(@"
SET NOCOUNT OFF; --  ensures that the 'StatementCompleted' event fires

EXEC('
CREATE PROCEDURE #TestProc
AS
SELECT * FROM sys.objects;

SELECT * FROM sys.tables;
');

SELECT * FROM sys.objects;
", _Connection))
                {

                    _Command.StatementCompleted += _Command_StatementCompleted;

                    try
                    {
                        _Connection.Open();

                        _Command.ExecuteNonQuery();

                        _Command.CommandText = @"
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause to shows when the events fire

SELECT 2 AS [Sally]
UNION ALL
SELECT 5;
";
                        Console.WriteLine("\n\t");
                        Console.WriteLine(_Command.ExecuteScalar().ToString());
                        Console.WriteLine("\n");

                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "#TestProc";
                        _Command.ExecuteNonQuery();
                    }
                    catch (Exception _Exception)
                    {
                        throw new Exception(_Exception.Message);
                    }
                }
            }
        }

        static void _Command_StatementCompleted(object sender,
                                                StatementCompletedEventArgs e)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("\nQuery Batch: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(((SqlCommand)sender).CommandText);

            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("Row(s) affected: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(e.RecordCount.ToString() + "\n");

            Console.ResetColor();
        }
    }
}

OUTPUT:

  Query Batch:
  SET NOCOUNT OFF; --  ensures that the
  'StatementCompleted' event fires

  EXEC(' CREATE PROCEDURE #TestProc AS SELECT * FROM sys.objects;

  SELECT * FROM sys.tables; ');

  SELECT * FROM sys.objects;

  Row(s) affected: 453

  Query Batch:
  SELECT 123 AS [Bob];

  WAITFOR DELAY '00:00:05.000'; --5 second pause

  SELECT 2 AS [Sally] UNION ALL SELECT 5;

  Row(s) affected: 1

  Query Batch:
  SELECT 123 AS [Bob];

  WAITFOR DELAY '00:00:05.000'; --5 second pause

  SELECT 2 AS [Sally] UNION ALL SELECT 5;

  Row(s) affected: 2

  123

  Query Batch: #TestProc
  Row(s) affected: 453

  Query Batch: #TestProc
  Row(s) affected: 17

The executenonquery result just isn't going to do what you want here.  But you can still get there, it just depends on what you want to use the information for.

You can add this line after each insert  "PRINT @@ROWCOUNT" and you should get the number of rows affected by the prior operation as part of the output (where you get "bar".

Alternatively, you can add an "OUTPUT" parameter to your stored procedure to hold the results and then just capture that when you run the executenonquery.

EDIT:

I managed to modify the sample that Jonathan Kehasias put together to include the statementcompleted event handling.  Just add these two lines.

#Add handler for StatementCompleted
$statementhandler = {param($sender, [System.Data.StatementCompletedEventArgs]$event) Write-Host $event.RecordCount };

#Attach handler...
$cmd.add_StatementCompleted($statementhandler)


How to insert values in junction table for many to many relationships? I have three tables in database:

 trips(trip_id(pk), trip_name(unique), user_id(fk))

 places(place_id(pk), place_name(unique))

 trips_places_asc(trip_id(fk), place_id(fk))

Since many trips can have many places, I have one junction table as above.

If a user inserts places to the trip, the places will be added to places table and the trip will be associated with the places in trips_places_asc table.

So, if I write  a query like:

INSERT INTO places (place_name)
VALUES ('XYZ')

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

then how to store trip_id and place_id in Junction or Association table trips_places_asc? Will I have to fire two queries?

You want to maintain atomicity of the action of storing a new place and trip, along with the intersection between the two.  In MySQL, you do this with a START TRANSACTION / COMMIT transactional wrapper.

You use the MySQL function LAST_INSERT_ID() (See documentation; similar to SQL Server variable @@IDENTITY) to get the value of the most recent auto increment value assigned.

To add a place and a trip and associate the two all at once, you would write code like this:

START TRANSACTION

DECLARE placeKey int

INSERT INTO places (place_name)
VALUES ('XYZ')

SET placeKey = LAST_INSERT_ID()

DECLARE tripKey int

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

SET tripKey = LAST_INSERT_ID()

INSERT INTO trips_places_asc(trip_id, place_id)
VALUES (tripKey, placeKey)

COMMIT

You should add error handling to the above and if an error occurs part way through, use the ROLLBACK command to undo the partially completed work.

You should also read up on LAST_INSERT_ID() to make sure you know how it works.  It can give you undesirable results in some cases, such as when you insert multiple rows with a single insert statement.

---Method 1 using Ident_Current

  BEGIN TRAN
        INSERT INTO places (place_name)
        VALUES ('XYZ')

        INSERT INTO trips (trip_name)
        VALUES ('MyTrip')

        Insert INTO trips_places_asc (trip_id,place_id) values
        (Ident_Current('trips'), Ident_Current('places'))

   COMMIT TRAN
      ---Method 2 Using Scope_Identity

        Declare @PlaceId int ,   @tripid int
        INSERT INTO places (place_name)
        VALUES ('XYZ')
        set @PlaceId = SCOPE_IDENTITY()
        INSERT INTO trips (trip_name)
        VALUES ('MyTrip')
        set @tripid = SCOPE_IDENTITY()
        Insert INTO trips_places_asc (trip_id,place_id) values
        (@tripid, @PlaceId)


mysql stored procedure create date plus one day but time set to 00:00:00 In a mysql stored procedure, how can i put into a date variable:-

curdate()+interval 1 day but at midnight - e.g. today is 2014-09-14 13:15:23 so the variable will hold:-
2014-09-15 00:00:00

thanks

I figured it out:-

SELECT DATE_FORMAT(CURDATE()+INTERVAL 1 DAY,'%Y-%m-%d 00:00:00') AS mdate;


Using IDs from multiple tables in a single column One of my co-workers created a schema similar to the following.  This is a simplified schema including only the parts necessary to address this question.

The system rules are as follows:

Departments can have 0 to many divisions.
A division must belong to only one department.
An article can be assigned to either a department, or a division of that department.

The schema is:

Department
----------
DepartmentID (PK) int NOT NULL
DepartmentName varchar(50) NOT NULL

Division
--------
DivisionID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
DivisonName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
UniqueID int NOT NULL
ArticleName varchar(50) NOT NULL

He defined the schema using an imaginary rule (for lack of a better term), that all DepartmentIDs would be between 1 and 100, and all DivisionIDs would be between 101 and 200.  He states that when querying the Article table, you will know whether the UniqueID is from the Department table or the Division table based on what range it falls into.

I think this is a poor design and proposed the following alternative schema:

Department
----------
DepartmentID (PK) int NOT NULL
ParentDepartmentID (FK) int NULL /* Self-referencing foreign key.  Divisions have parent departments. */
DepartmentName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
ArticleName varchar(50) NOT NULL

I believe this is a properly normalized schema and properly enforces relationships and data integrity, while honoring the business rules outlined above.

My specific question is this:

I know using one column to contain values from two domains is poor design, and I can argue the benefits of the foreign key in the Article table.  However, can someone provide a reference to a specific database design article / paper that I can use to backup my position.  If I can point to something concrete, it will make it that much easier.

Your self-reference could introduce a hierarchy of divisions within divisions. You need code (usually a trigger) to ensure that no divisions allow this.

The original schema using encoding is broken too. You have no enforceable FK

Personally, I'd consider this with a check constraint to ensure than only one of DepartmentID and DivisionID per row is populated

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NULL
DivisionID (FK) int NULL
ArticleName varchar(50) NOT NULL

Why?

no code needed
no nested divisions
full DRI via FKs to parent table
enforced "multiple parent, only one at a time"

You could also use computed columns to add a column that says "department" or "division"

Another way: mandate that each department has at least one division...

Might I propose a new idea? The lookup table here is an auto-increment, in this way there is only ONE ID used between both "Division" and "Departments". This way also allows for "Departments" to be owned by multiple divisions (I think this is what you are after).

Whenever I have a set of unique tables that need to be shared with only one item, I do this "Lookup" table design. If there are any contradictions to this design, I would really like to hear them because this scenario comes up fairly often for me!

I use access to quickly layout my DBs, then usually convert them to MySQL...

EDIT: Since there is some misunderstanding with my layout, let me explain it in detail.

With this design an Article can be owned by either A (single) Division, OR a single department. Not both department and a devision, but if you assign it to a department you can then know which division the department is assigned to by querying both division and department tables.

This design also keeps "Division" and "Department" normalized in case additional information is tied to either one in the future.

I reviewed the requirements and still stand by this design.


What is the most efficient way to get the minimum of multiple columns on SQL Server 2005? I'm in a situation where I want to get the minimum value from of 6 columns.

I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance.

The first method is to use a big case statement. Here's an example with 3 columns, based on the example in the link above. My case statement would be much longer since I will be looking at 6 columns.

Select Id,
       Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
            When Col2 <= Col3 Then Col2
            Else Col3
            End As TheMin
From   MyTable

The second option is to use the UNION operator with multiple select statements. I would put this in an UDF that accepts an Id parameter.

select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable

and

select min(col)
from
(
    select col1 [col] from MyTable where Id = @id
    union all
    select col2 from MyTable where Id = @id
    union all
    select col3 from MyTable where Id = @id
) as t

And the 3rd option I found was to use the UNPIVOT operator, which I didn't even know existed until just now

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from
        cte
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

Because of the table size, and frequency in which this table is queried and updated, I am concerned about the performance impact these queries would have on the database.

This query will actually be used in a join to a table with a few million records, however the records returned will be reduced to around a hundred records at a time. It will get run many times throughout the day, and the 6 columns I am querying are frequently updated (they contain daily stats). I do not think there are any indexes on the 6 columns I am querying.

Which of these methods is better for performance when trying to get the minimum of multiple columns? Or is there another better method that I don't know of?

I am using SQL Server 2005

Sample Data & Results

If my data contained records like this:

Id    Col1    Col2    Col3    Col4    Col5    Col6
1        3       4       0       2       1       5
2        2       6      10       5       7       9
3        1       1       2       3       4       5
4        9       5       4       6       8       9

The end result should be

Id    Value
1        0
2        2
3        1
4        4

I tested the performance of all 3 methods, and here's what I found:

1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.
Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.
The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

I also ran some tests using Mikael's answer; however, it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

I'm not a dba, so I may not have optimized my tests and missed something. I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results.

I guess that the first option is fastest (although it does not look very slick from programming perspective!). This is because It deals with exactly N rows (where N is the table size) and has to do no search or sort like method 2 or 3.

A test with large sample should prove the point.

As yet another option to consider (as if you need more!), is to create a materialized view over your table. if your table size is in 100s of thousands or more. This way, the min value is calculated while the row is changed and the entire table would not have to be processed with every query. In SQL Server, materialized views are called Indexed Views


SQL Server 2012 database backup successfully report but no backup file I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be stored. But SQL Server logs history about this maintenance plan are successful.

What is the problem?

I am using SQL Server 2012. The operating system is Windows Server 2008 R2.

Thank you for paying attention.

The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.

This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+.  Just tested in 2012.

/*
Find Where DB Backups Went Physical Location
For last two days.
backupset.type
D --> FULL
I --> DIff or incrimental
L --> Log backups
*/

DECLARE @dbname     sysname
SET @dbname = ''

SELECT
     @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Date]
    ,msdb.dbo.backupset.user_name
    ,datediff(second, msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]
    ,msdb.dbo.backupset.type
FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE
    msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY
    master.sys.sysdatabases.name
    ,msdb.dbo.backupset.backup_start_date
    ,msdb.dbo.backupset.backup_finish_date
    ,msdb.dbo.backupmediafamily.physical_device_name
    ,msdb.dbo.backupset.type

Just a guess that you are looking at the folder on a different computer.
Your backup location represents folder where SQL Server instance is running.


Number of tables in join and query performance Recently I came across a standards in my project which strangely asserts that we should refrain from joining more five tables in SQL query. More than five tables in a query leads to performance degradation. Even though I strongly disagree with such assertion (regardless it's SQL Server or Oracle) I was wondering is there any suggestion as to how many tables we should use in join in SQL Server or Oracle. Does it depends on the database type (SQL Server Vs Oracle Vs MySQL) or number of rows in the tables, indexing, INNER Vs OUTER Join, cardinality of the tables joined, etc.? My understanding is since SQL is not a procedural language we should try to add more tables in JOIN provided that we are using the right columns for joining.

More than five tables in a query leads to performance degradation.

A join will increases the cost of running a query to some degree no matter what.  But what's the right thing to do ultimately depends on what's ideal or acceptable for any given project.

For example.  I have a 1-column table that contains 1 million row, whose only column is an ordered bigint clustered index. Make 10 queries ranging from no join to 10 joins, and compare execution plan and execution time.  You'll see the increased cost and time pretty much in a linear manner.  In my personal environment, SELECT * with no join might take 5 seconds to complete while 6 joins might take 6 seconds.  What if the table had only 10,000 records?  It will run significantly faster even if the number of joins is doubled (in my environment <200ms).

Now, if the only way to get the right data set is by joining 10 tables and it takes 5 seconds, is that good or bad?  If the speed is critical to your application, like high-speed stock trading, then you might want to reconsider.  What if it was a report for sales department?

  Does it depends on the database type (SQL Server Vs Oracle Vs MySQL)
  or number of rows in the tables, indexing, INNER Vs OUTER Join,
  cardinality of the tables joined, etc.?

Database product, yes and no.  Each products have different optimization logic.  So one might not execute a query the same as others.  One might be faster than others in some cases and vice versa.  Everything else you mentioned affects performance, including selectivity.  OUTER can be slower than INNER.  There are occasions where OUTER joins can be faster or costs less.

Of course, my very simple example is far from any real-life scenario but the underlying logic is the same.  How many you should join depends on your environment and your requirements.  Things you mentioned will affect the performance, and if the query is running below acceptable level or started having a performance issue, you should examine them.  But none of them are relevant to how many joins you SHOULD have.

I only now SQL Server

With 5 or more joins I have observed the query optimize will tend to go with loop joins more often.  And a loop join can perform poorly.

But you just need to deal with it.  You can get good performance with 5 or joins - some times it takes a bit more work.


Reducing the size of mysql files I have been working on MySQL database whose size is growing quite huge and I want to save on space on my cloud. I have tables whose engine is Innodb which holds the largest amount of data. I have already run OPTIMIZE TABLE  on them which saved some space. Index data length is only a few MBs. I am looking for any other way to reduce the size of my growing tables and if possible the whole database. I am only keeping binary logs for 3 days for recovery. I will appreciate a solution that will cause no downtime or minimal downtime and risk. I also have a local replicated data of the same.

u can do something like this:
add this to /etc/my.cnf

[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you

if you talking about reduce the size of the table , i would advise this:

1st u need to sort from workbench or phpmyadmin , which tables consumes the most.
2nd maybe u can start plan on how to reduce that 'biggest table'
3rd u think to think or consult with your superior , how long u need to keep the record , some company might wanna keep 3months record only ,then dump and keep somewhere(cloud or Harddisk) , or 1 year or 3 years .
4th before dump , maybe u can create archive table.
eg: your original table 'test' , archive table rename as 'test_archive' then move older records or records that u want to dump there.
5th dump and move to somewhere safer.


Spanish characters saved incorrect in Mysql I have googled this already, but I couldn't find what's wrong in my situation.

I have a wordpress site that has a lot of non-english users which type in words such as "español" it is printed correctly with php or sent via email, but when it's saved to the database, it appears as "espau00f1ol".

It's a MyISAM, mysql database with utf8mb4 unicode encoding. What do I need to change in order to be able to save non-English characters?

Check and log/dump the insert/update queries - this looks to me as if the php code somehow recodes your data. The 00f1 is the hexadecimal representation of the character ñ and in the form \u00f1 it might be used as an special/escape sequence (for example in JSON).

You have to change fields/tables encoding to utf8_spanish_ci or utf8_spanish2_ci so you can save spanish characters.


Query a database for empty tables In my system there are two tables called ‘faculty’ and ‘faculty_assets’.

I need to delete faculty by faculty from the ‘faculty’ table. But system should allow deleting the faculty when only faculty_assets table empty. Otherwise It should not allows to delete the faculties from ‘faculty’ table.

Can anyone modify below sql query?  (‘fac_id’ is the primary key of ‘faculty’ table )

function removeFaculty($fac_id){
    $conn=new connection();
    $sql="delete from faculty where fac_id='$fac_id'";
    $result=$conn->query($sql);
    return $result;

}

Assuming faculty_assets and faculty are joined by the fac_id field, you could use something like

$sql="DELETE FROM faculty WHERE fac_id='" . (int)$fac_id . "' AND NOT EXISTS
        (SELECT * FROM faculty_assets WHERE fac_id = '".(int)$fac_id . "')";


SQL Server error 3417 The message is

  The log scan number (1166:312:1) passed to log scan in database
  'master' is not valid. This error may indicate data corruption or that
  the log file (.ldf) does not match the data file (.mdf). If this error
  occurred during replication, re-create the publication. Otherwise,
  restore from backup if the problem results in a failure during
  startup.

Can you help in resolving the issue.

Restore your master database from a backup.  This is a good guide:

http://thomaslarock.com/2014/01/restore-the-master-database-in-sql-server-2012/

First, run the DBCC CHECKDB command and find the exact reason behind this problem.Now restore from the updated backup. If you not have updated backup then try to fix the problem by using automatic repairing options of DBCC CHEKCDB:

Repair Rebuild
Repair allow data loss

Good Luck!


How to visualize joins between inner queries to their respective outer queries? I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.

Here's an example:

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT TOP 1
    FROM Ninjas AS N
    INNER JOIN NinjaWeapons AS NW
        ON N.WeaponId = NW.WeaponId
    WHERE N.NinjaId = NM.NinjaId
)

Is there a way to represent this (or a similar kind of inner query joined to outer query) in a table or diagram of sorts?

Just break it down into stages and consider what the output of each stage represents

SELECT *
FROM Ninjas AS N
INNER JOIN NinjaWeapons AS NW
    ON N.WeaponId = NW.WeaponId

This can be thought of as "all ninjas with at least one weapon along with their weapons". The data model does not appear to tie weapons to particular movies.

So you are now left with the simpler query to analyse (there is no value in including TOP 1 in an EXISTS so that has also been removed.)

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT *
    FROM WeaponOwningNinjasWithTheirWeapons AS N
    WHERE N.NinjaId = NM.NinjaId
)

There seems to be a normalisation issue here as apparently NinjaMovies will contain multiple rows for the same movie starring more than one Ninja and repeats the title in each one - but perhaps NinjaMovies is itself a view resulting from a join of normalised tables.

In any event SELECT NM.MovieId, NM.MovieTitle clearly selects the Movie Ids and Title from it so the only thing left to analyse is the EXISTS.

This simply says to only return rows where the corresponding Ninja exists in the WeaponOwningNinjasWithTheirWeapons result set.

So to sum up. The query returns all rows in NinjaMovies where the corresponding Ninja has at least one weapon.

The semantics of the query seem a bit odd as NM.NinjaId is not projected so in the event that the movie stars multiple ninjas - some with weapons and some without - there is no indication as to which led to the inclusion of the row. If you don't care about this then you should probably add a DISTINCT to remove duplicates in the event that the movie stars multiple weapon bearing ninjas.

In the past I have utilised Venn Diagrams. I will try and post a sample later.


How to best encapsulate monetary values in a database I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the equation). In a number of places I need to store monetary values (e.g. 1.23 USD). I understand that I need to store both the amount (1.23), and the currency (CurrencyID with FK to Currency table having ISOAlpha3Code = USD). My question is, how should I best encapsulate and isolate these two data from the other columns in the table (TableX) to which the monetary value relates?

As I see it, I have three options:

I store two columns in TableX, one being the Amount (numeric(p,s)) and one being the CurrencyID (FK). I add an XOR check constraint on the two columns to TableX.
(DBMS dependent) I create a CLR UDT (or equivalent) representing Currency, storing both Amount and CurrencyID. I store one Currency type column on TableX containing the monetary value.
I create a MonetaryAmount table with three columns: MonetaryAmountID (surrogate PK), CurrencyID (FK), and Amount. I add MonetaryAmountID (FK) to TableX.

I dislike #1 because I have used this pattern before and nobody has understood that the two data are part of the one complex datum; also, it's not normalized. I dislike #2 because it breaks 1NF, it is DBMS specific, and it will likely be difficult/expensive to enforce referential integrity on Currency. I'm rather ambivalent towards #3 - it feels over-engineered and will probably be computationally expensive, but it is correctly normalized.

#3 also has the added complexity of whether to place a unique constraint on the (CurrencyID, Amount) combination, thereby making it an ad-hoc reference table and better normalizing it. I believe it would be best not to do this, as whilst MonetaryAmounts may correlate, they are distinct in nature.

Any experience you can share (or alternatives you can suggest) would be appreciated. I would especially like to hear from genuine database architectural gurus.

From a database (and client) point of view, you need to do a few things.

The first thing you need to consider is that you have to use integers for everything and have a number of implied decimals for each currency (eg: $2.00 is actually 200 with 2 implied decimals. The $2.00 part is a client formatting issue and a database maths issue.). Floating point never comes into it - you don't want to go near the mathematical implications involved in using floating point.

My advice: Have a currency table with the currency_id (PK), currency_name and decimals fields. Any other table then just needs a currency_id and amount column to relate to the original table. Formatting is then just a join between the two. Maths between any tables with the same currency is then just straight integers.

Maybe add a few more columns to the currency table for leading and trailing symbols (eg: '$' for prefix, '.' for decimal spacer) for formatting.

Regarding your options:

This is anti-relational and it's easy to see why people do not understand it. The Amount column should not contain values of different types.
This is an object relational solution. It is valid so long as you can define the type's equality and comparison functions.
This is like implementing solution 2 in classic SQL, which inherits the issue with solution 1, but is probably more readable.

I don't think there is a purely relational solution to this.


SQL Server audit query that fires a trigger Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it?

I need to know the SQL query that fires a trigger over a database without a profiler.

Thanks

I have a number of triggers that do this, and I find that DBCC INPUTBUFFER is generally the best way to do it. Caution: the output is limited to 4000 characters. Very long queries will be truncated.

DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
CREATE TABLE #SQL (
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql

SELECT @sql = EventInfo FROM #SQL
DROP TABLE #SQL

At the end of this, @sql contains the query for the current request. Also, you could just as easily use a table variable instead of a temp table.

Searching over the web I found the solution to my answer. The key is use DBCC INPUTBUFFER:

ALTER TRIGGER Audit_Test
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
   DECLARE @TEMP TABLE
   (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))
   INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')
   SELECT EventInfo FROM @TEMP
END
GO

It works for me!


Turn off ANSI_WARNINGS for heterogenous queries We have a Stored Procedure with many linked-server queries. When executing it displays a Warning:

  Warning: Null value is eliminated by an aggregate or other SET
  operation.

To suppress this, I  tried :

  SET ANSI_WARNINGS OFF

But that threw this error :

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
  to be set for the connection. This ensures consistent query semantics.
  Enable these options and then reissue your query.

All articles I looked at mention setting ANSI_WARNINGS to ON to get it to work.
But I Absolutely do not want to see those Warnings, because I'm sending query results as attachment in sp_send_dbmail which has that Warning message right at the top of attachment.

Is it possible to suppress the warnings for heterogenous queries ?

This should remove your warning.

select name, count(employeeId) from employees group by name
select * from RemoteServer..departments

Nevermind, found a workaround. The code was as such:

select name, count(*) from employees group by name

select * from RemoteServer..departments

I modified it to :

SET ANSI_WARNINGS OFF
select name, count(*) from employees group by name
SET ANSI_WARNINGS ON

select * from RemoteServer..departments

and it worked !

SQL wants Warnings to be On ONLY when executing remote queries. Thankfully in my case, the Group By (which generates the Null value Warnings) is done locally, so I 'fooled' SQL by Turning Warnings ON just before remote query.

If however, your Group By is part of the RemoteServer query, you're out of luck, and need to pull data in locally before grouping.


what is difference between database developer and administrator? i am a doing graduate degree, and i want to go in database field as i find this subject very interesting. For becoming the DBA ,is there a need of becoming developer first for graduate passout and career beginners.

At a masters level you should be pretty good at both

Administrator - DBA

install and configure hardware
maybe you say that is outside of DBA but most DBA can do hardware
install and configure database software
optimize the database configuration
users, groups, and security
backups
index maintenance
monitor
need basic SQL just to query systems tables and validate data
build tables and indexes

Developer

database design
write, queries, procedures ....
index planning and implementation
build tables
performance monitoring

Mix

if you get into partitions and other exotics the database design gets into hardware
index maintenance - if the DBA is going to do this need to understand indexes
data load - is this a DBA or developer?
if it is the DBA then need a lot of developer skills

At a master level should know at least one language / platform.  If you load data there going to be times you need to scrub it row by row.

In a large software shop you may have a developer on a programming team that really only does pure development.   Lets say you are running a packaged application like SAP you would have a DBA that only does DBA and not even have a developer.

Also have application developers that are also database developers.  For big high volume applications the back end developer(s) need to be part database developers.  For small apps that cannot afford a database developer the application developer has no choice but to do it all.

If you are a DBA expect to do some mail, web, group policy, and other system tasks at some point in you career.

To become a DBA you don't need developing skills. But if you are fast enough to learn that, it'll be an added advantage.