Unique Contraint/Index Based On Values I have a table that has the following column definitions:

ID (INT, PK)
Name (VarChar)
Active (Bit)
Bunch_of (Other_columns)

Question: I want to have a Constraint on Name/Active such that we can only have 1 record with a given Name that is Active (Active = 1), but we could have many records that have the same Name that are Inactive (Active = 0). Is this possible?

I've tried the normal Unique constraints, but they limit the dataset to only allow 1 record with a given Name to be Inactive.

To enforce this declaratively in 2005 you can use an indexed view.

CREATE VIEW dbo.ActiveNames
WITH SCHEMABINDING
AS
SELECT Name
FROM  dbo.YourTable
WHERE Active = 1

GO

CREATE UNIQUE CLUSTERED INDEX UQ_ActiveNames ON dbo.ActiveNames(Name)


Restoring a backed up database fails I have backed up a database (full backup) and was attempting to restore as a new database (different name). However when attempting to restore it I get the following error:

  The transaction log for database 'L-Test' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Not really sure why that's happening. The size of the database is only 37.50MB and the logfile is set to auto grow as well.

Keep in mind that the backup does not necessarily reflect the size of the database (including the log) on disk - even if compression is not used.

You are likely trying to expand a log file that matches the size on the original database, and there is not enough space on the disk. You should check the size that the restore operation is trying to restore to looking at the size column for the files in the following query:

RESTORE FILELISTONLY FROM DISK = 'path:\to\file.bak';

This may or may not match the current size of the original source database, depending on when the backup was taken and what type of operations have been performed against that database in the meantime.

If you don't have that much space to spare on the disk you're trying to restore to, you have multiple options:

free space on the drive;
use WITH MOVE to place the data and/or log files on a different drive with sufficient space; or,
shrink the log file on the original database (this may require performing a log backup or a checkpoint to be effective, depending on recovery model) and then take a new, COPY_ONLY backup and restore that.


Table Permissions Sanity Check: GRANT not working I have been googling this for a few months now and I am confounded why I have not been able to get a solid answer on this.

Goal: Grant several roles SELECT, INSERT, UPDATE, DELETE permissions.

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1"."new_table_name" TO "role1";

I run this SQL script with no errors returned, but when I attempt to run a simple

SELECT *
FROM schema1.new_table_name

I get a permission denied for relation returned.

Do you have any rules of thumb or recommended "further reading" for how to handle and understand Postgres permission and hierarchy setup for users?

I setup a role hierarchy that doesn't seem to be respected and now GRANT scripts don't seem to be working either

Ok, so my broader question still stands (and will likely build that out in its own forum post) but for now, I'll answer the immediate problem here.

My GRANT query was syntactically incorrect and that was producing the error.

My query:

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1.new_table_name" TO "role1";

Should really be:

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1"."new_table_name" TO "role1";

(I was missing closing Quote marks after the schema name, and opening quote marks before the table name)


Explanation for second Compute Scalar I noticed a peculiar thing. This query
declare @t int = 3;

select distinct top(@t)
    Number - Number + ABS(CHECKSUM( CAST(NEWID() as binary(16)) ))
from Numbers as n;

produces this plan

dbo.Numbers is a standard Numbers table - just a contiguous list of integers with a clustered primary key - containing 100k rows.
There are two Compute Scalar operators. The right-hand one computes Number - Number and defines an internal expression. The left-hand one performs the addition and internal function calls. Strangely there is only one Compute Scalar operator if it is rewritten as
Number + ABS(...) - Number

Is there something "interesting" going on here? Or is this a simple artefact of how the parser constructs the execution plan from memos? Both forms complete compilation in search(1). Forcing search(2) using TF8677 does not change the behaviour. TF8606 shows the separation in Tree After Project Normalization.
I don't see this as a problem. I'm just interested to know if there's something to learn here.
Some background
I want to produce a fixed-length list of random numbers. This
ABS(CHECKSUM(NEWID()))

seems to be the way to go. I have a Numbers table (with 100k rows), so using that to drive output seemed reasonable. I wanted sampling without replacement so a DISTINCT would ensure there were no duplicates and a TOP() will give the required resultset size. Putting it together I got
declare @t int = 3;

select distinct top(@t)
    ABS(CHECKSUM(NEWID()))
from Numbers as n;

Sadly the plan was a clustered index scan with a Hash Match (Aggregate). Reading only the minumum number of rows required would be preferable, perhaps with a Flow Distinct to enforce uniqueness.
That quickly lead me to this question. I tried each of the suggestions there. None changed the plan except converting the base table column to a string, appending a string function's empty output and re-converting to an integer (I didn't try a CCI). This felt unsatisfactory.
Recognising that CHECKSUM returned and integer, and that the Numbers table held integers, I tried this no-op:
declare @t int = 3;

select distinct top(@t)
    Number - Number + ABS(CHECKSUM( CAST(NEWID() as binary(16)) ))
from Numbers as n;

This gave the hoped-for plan seen above.
The shape is maintained for all parameter values from 1 to 100k.
For what it's worth I'm running

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 10.0  (Build 18362: )
Intel Core i7-8550U @ 2GHz, 16GB RAM, SSD

There's nothing particularly interesting about the number of Compute Scalars. The compilation and optimization process often produces more of these than is strictly necessary. Conor Cunningham talked about this in general terms at one of the early SQLBits conferences. In an ideal world, unnecessary scalar computations would be collapsed at the end of optimization, but only a few cases are implemented.

On the background: the choice of Flow Distinct depends on the number of rows needed being less than the number of distinct values estimated.

Using a variable with TOP means a default guess of 100 rows needed, so we need the number of distinct values to be more than that.

How the cardinality estimator (CE) treats ABS(CHECKSUM(NEWID())) depends on version. The CE model introduced in SQL Server 2014 estimates this expression will produce one unique value, regardless of the number of times it is evaluated (number of Numbers table rows read in this case). This is not a very good estimate of course.

The pre-2014 CE model makes a better educated guess, giving an estimate well in excess of 100, and so a Flow Distinct. My Numbers table does have ten million rows though. You can help the equation along if necessary by hinting a low value for @t e.g. OPTIMIZE FOR (@t = 1).

You can hint the earlier CE model in any of the usual ways e.g. USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') or USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110').

For example:

DECLARE @T integer = 3;

SELECT DISTINCT TOP (@T)
    ABS(CHECKSUM(NEWID()))
FROM dbo.Numbers AS N
OPTION
(
    OPTIMIZE FOR (@T = 1),
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')
);


Make a column for each count(*) in a day of the week I have a table of people who are doing piece work. Every entry in the table has a user-ID, name, and a Date for each report they enter.

I need to make a weekly report of what these people have done per day starting with Sunday

How do you group by a week starting Sunday?
How do you make a column for each COUNT(*) per day?

t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-29
t0001, Tod, 2015-6-28
t0001, Tod, 2015-6-28
b0002, Ben, 2015-6-29
b0002, Ben, 2015-6-29
b0002, Ben, 2015-6-28

This is what I'm looking for.

NAME | S | M | T | W | R | F | S | TOTAL
----------------------------------------
TOD  | 2 | 5 | - | - | - | - | - | 7
BEN  | 1 | 2 | - | - | - | - | - | 3

Here's one way. Assuming this table and sample data:

USE tempdb;
GO
CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE);

INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150628'),('t0001','Tod','20150628'),
  ('b0002','Ben','20150629'),('b0002','Ben','20150629'),('b0002','Ben','20150628');

Then with a variable or parameter to dictate the week you care about:

DECLARE @dt DATE = '20150702';

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt(dt) AS
(
  SELECT TOP (7) dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @dt)
  FROM sys.all_objects
  ORDER BY [object_id]
),
x AS
(
  SELECT
    s.Name, dt.dt,
    dd = DATEDIFF(DAY, @dt, dt.dt),
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  LEFT OUTER JOIN dbo.splunge AS s
  ON s.Date = dt.dt
  GROUP BY Grouping SETS((s.Name),(s.Name,dt.dt))
)
SELECT
  Name,
  [S] = COALESCE(MAX(CASE dd WHEN 0 THEN c END), '-'),
  [M] = COALESCE(MAX(CASE dd WHEN 1 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 2 THEN c END), '-'),
  [W] = COALESCE(MAX(CASE dd WHEN 3 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 4 THEN c END), '-'),
  [F] = COALESCE(MAX(CASE dd WHEN 5 THEN c END), '-'),
  [S] = COALESCE(MAX(CASE dd WHEN 6 THEN c END), '-'),
  TOTAL = MAX(CASE WHEN dt IS NULL THEN c END)
FROM x
WHERE Name IS NOT NULL
GROUP BY Name;

Results:

Name    S   M   T   W   T   F   S   TOTAL
------  --- --- --- --- --- --- --- -----
Ben     1   2   -   -   -   -   -   3
Tod     2   5   -   -   -   -   -   7

Another way:

DECLARE @dt DATE = '20150702';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Name, dw = DATEPART(WEEKDAY, Date)
  FROM dbo.splunge
  WHERE Date >= @dt AND Date < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT Name, dw = COALESCE(dw, 8),
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  GROUP BY GROUPING SETS((Name),(Name,dw))
)
SELECT Name,
  [S] = COALESCE([1],'-'),
  [M] = COALESCE([2],'-'),
  [T] = COALESCE([3],'-'),
  [W] = COALESCE([4],'-'),
  [T] = COALESCE([5],'-'),
  [F] = COALESCE([6],'-'),
  [S] = COALESCE([7],'-'),
  TOTAL = [8]
FROM x
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt;

Don't forget to clean up:

DROP TABLE dbo.splunge;


Does MySQL have problems with nested insert like with subqueries in where? Query 1:

INSERT `personal`.`locations`
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON `first_id` = `s`.`id`
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
ON DUPLICATE KEY UPDATE
  `lat`  = `s`.`lat`,
  `lng`  = `s`.`lng`,
  `name` = `s`.`name`,
  `desr` = `s`.`desr`;

Query 2:

INSERT `personal`.`locations`
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON (`first_id` = `s`.`id` OR `second_id` = `s`.`id`)
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
ON DUPLICATE KEY UPDATE
  `lat`  = `s`.`lat`,
  `lng`  = `s`.`lng`,
  `name` = `s`.`name`,
  `desr` = `s`.`desr`;

The select in query 1 takes 0.008 seconds to select 4 million records.

The select in query 2 takes 1 second to select 300 thousand records.

Query 1 executes completely in 60 seconds.

Query 2 executes completely in 300 seconds.

The conclusion: MySQL seems to repeat the select for every insert just like with where clause subqueries.

Is there a solution?

Edit 1: Added new query

Query 2: faster alternative but still with the same issue

INSERT `personal`.`locations`
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON `first_id` = `s`.`id`
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
UNION ALL
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON `second_id` = `s`.`id`
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
ON DUPLICATE KEY UPDATE
  `lat`  = `s`.`lat`,
  `lng`  = `s`.`lng`,
  `name` = `s`.`name`,
  `desr` = `s`.`desr`;

Slightly faster despite the fact it actually executes more updates but since the two selects execute faster the the one they replace it gains a bit of time.

Edit 2: Added table structure

CREATE TABLE IF NOT EXISTS `references` (
  `id`        bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `first_id`  bigint(20) unsigned NOT NULL DEFAULT '0',
  `second_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name`      varchar(255) NOT NULL,
  `status`    enum('V','I','D') NOT NULL DEFAULT 'V',
  PRIMARY KEY (`id`),
  KEY `first_id`  (`first_id`),
  KEY `second_id` (`second_id`),
  KEY `status`    (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `locations` (
  `id`        bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `lat`       double NOT NULL DEFAULT '0',
  `lng`       double NOT NULL DEFAULT '0',
  `name`      varchar(40) NOT NULL DEFAULT '0',
  `desr`      varchar(254) NOT NULL DEFAULT '0',
  `status`    enum('V','I','D') NOT NULL DEFAULT 'V',
  PRIMARY KEY (`id`),
  KEY `lat`    (`lat`),
  KEY `lng`    (`lng`)
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Each reference has to have at least one location associated based on first_id. The second is optional but it does exist for 70%-80% of the records. Each location can be associated with multiple references.

Using OR in the JOIN part of your query results in the query to be performed for all 4 million records returned from the result set of the left hand side OR clause.

So using a solution with UNION is definitely faster.


How we can we know SQL Replication components installed in SQL server Can somebody please let me know how can we identify whether Replication components installed in SQL server.

Tried below:

1.we had changed the server name earlier so while checking with configure distributor and publication giving the below warning.

TITLE: Configure Distribution Wizard

  SQL Server is unable to connect to server 'xxxxxxxx'.

ADDITIONAL INFORMATION:

  SQL Server replication requires the actual server name to make a
  connection to the server. Specify the actual server name, 'zzzzzzzzz'.
  (Replication.Utilities)

i will rename the server.

while tried with configure subscription giving below warning.

TITLE: New Subscription Wizard

  Microsoft SQL Server Management Studio is unable to access replication
  components because replication is not installed on this instance of
  SQL Server. For information about installing replication, see the
  topic Installing Replication in SQL Server Books Online.

ADDITIONAL INFORMATION:

  Replication components are not installed on this server. Run SQL
  Server Setup again and select the option to install replication.
  (Microsoft SQL Server, Error: 21028)

Now, Please suggest whether the Replication components installed in SQL instance

You can us the below T-SQL command to query the registry and confirm is Replication Components are installed:

USE master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;

if @installed = 0 then replication is not installed. That stored procedure actually reads the registry using the below command, which is another way of checking:

EXEC master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
        'SOFTWARE\Microsoft\MSSQLServer\Replication',
        'IsInstalled'

sp_MS_replication_installed example

Underlying code


MYSQL - Timeclock Count Hours In/Out for each day of current week Below is my table for my database timeclock that records each record of an employee clocking in/out. I am wanting to create a function that returns hours for each day Monday-Sunday of current week.

id  user    timeinout           type    function
1   1   2015-08-05 20:20:32     in         1
2   1   2015-08-05 20:20:56     out        1
3   1   2015-08-05 20:29:25     in         1
4   1   2015-08-05 20:32:04     out        2
5   1   2015-08-05 20:36:28     in         1
6   1   2015-08-05 20:40:00     out        1
7   1   2015-08-06 09:05:39     in         1

I know this function is way wrong, but here is where I am. I have never done this before so I am at a real loss and all examples I have tried have failed.

function gettimeclockHours($user) {
  include $_SESSION['db'];
  try {
      $sql = "SELECT HOUR(timeinout) as hour, COUNT(*) as num_rows FROM timeclock WHERE user = :user GROUP BY HOUR(timeinout)";
      $s = $pdo->prepare($sql);
      $s->bindValue(':user', $user);
      $s->execute();
  }
  catch (PDOException $e)
  {
      $error = 'Error.' . $e->getMessage();
      moduleError($error);
      exit();
  }
  $result = $s->fetch();
  return $result;
}

The Output example I am looking for is below.

Day of Week                  Total Time
Monday                       1.2 Hours
Tuesday
Wednesday                    3.0 Hours
Thursday
Friday
Saturday
Sunday
Total for current results    4.2 Hours

Thanks for your help in advance.


Insert but ignore if duplicate AND set a value different if exist in another table I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array containing userIDs from friends that the user invited. I wish to loop through them and insert the invitations into a database. The looping and all is already under control but the database query isn't.

This is what my current query looks like:

SELECT `iID` FROM `invitations` WHERE `iSender` = 'SENDER' AND `iReceiver` = 'RECEIVER';

If that returns zero rows I process this query:

INSERT INTO `invitations` (`iSender`, `iReceiver`) VALUES ('SENDER', 'RECEIVER');

And then I check if they're already signed up to my website:

SELECT `uID` FROM `users` WHERE `uOAuthID` = 'RECEIVER';

If it returns more then 1 row I run the following and final query:

UPDATE `invitations` SET `iProcessed` = 1 WHERE `iReceiver` = 'RECEIVER';

So basically this is how the process is currently shaped:

If the user hasn't already has been invited by the inviter we insert the invitation into the database.
Then we check if the invited user already is signed up.
If he is signed up we update the invitation and say that it already has been processed.

I guess there's a better and faster method to do this with just maybe 1 or 2 queries. I've tried using INSERT IGNORE and ON DUPLICATE but that just gave me errors and gave up.

I hope that you understand what I'm looking for. Thank you all for your time!

You can try in two steps as

INSERT IGNORE INTO `invitations` (`iSender`, `iReceiver`)
VALUES ('SENDER', 'RECEIVER');

This will ignore if the record already exists.

Then update with by joining the two tables

UPDATE `invitations` as i
INNER JOIN users AS u on i.iReceiver = u.uOAuthID
SET `iProcessed` = 1 ;


Count up number of rows matching criteria in same table I'm trying to extract a list of results, with the greatet number of rows found. Basically, we have a table of values, linked with the key link_id_fk. What I want to basically do is look at each link_id_fk value, and then count the number of rows in the table for that record. So if I were to do it manually for each one, it would look like:

SELECT COUNT(*) FROM lsql_Flex_Values WHERE flex_type = "address" AND link_id_fk = 12345

I'm trying this query, but it always returns no results:

SELECT link_id_fk,
    (SELECT COUNT(*) FROM lsql_Flex_Values WHERE flex_type = "address" AND link_id_fk = flex.link_id_fk) AS total
    FROM lsql_Flex_Values flex WHERE flex_type = "address"

What am I doing wrong? I'm sure the veterans will spot it a mile off - but I'm just not seeing it :/

Using GROUP BY should avoid to use the subquery.

SELECT link_id_fk,
       COUNT(*) AS total
FROM   lsql_Flex_Values flex
WHERE  flex_type = 'address'
GROUP BY link_id_fk;


Using Oracle12C Client with Oracle11.g database I have been using Oracle12c client to connect to an Oracle11g database in a remote server for about one month, it wasn't giving me any problems until this week, when I started getting the error:

  The Network Adapter could not establish the connection Provider Code 17002

First I used Oracle Sql Developer to design the tables of the database, and a week after that I started to develop an app with Visual Studio 2013, where I used the native adapter and the library Oracle.DataBase.Client as part of the Visual Studio Integrated Tools that oracle provide to work with their databases.

I would like to know if the errors could be because the client is 12c and the database is 11g or if this question doesn't matter and another thing can be happening.

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required)

Oracle client version 12c is compatible with Oracle server version 11.2 (and below), so no your error isn't likely to come from that.

Check that the listener is indeed started on the database server, check that the database is up, and check that the database is registered with the listener.


Big Size Database and increasing number of users Me and my colleagues are using quite big size normalized database.

Basically it's used for warehouse management and daily minimum 100 of the user perform data manipulation operations.

80% of the users access stock information from web.
20% users are from the warehouse operation team.

Most of the time in a day, warehouse operation team performs major operations of stock taking, stock adjustment, sales, purchases, reporting etc.. But at the same time web users get quite slow response from SQL Database.

Keeping in view increasing number of web users, my question is how I could make the web access faster at the same time when major operations/transactions are running in the background?

As web users are accessing subset of the tables which are being used by Operations team, what strategy I could implement to keep the database responsive as well sync at same time?

Please bear with my language skills, I hope this is right place to ask this question, otherwise please guide.

You're asking a scaling issue which can be solved in many different ways using different technologies based on your current restraints and environments.  If the issue is coming from locking you can change the isolation level to snapshot isolation if you don't mind seeing the old value until the new ones is complete.  This is good if you have plenty of TempDB space and can afford an extra 14 or so bytes per row for the version overhead.

You can use snapshots to query the old data but as new data is added expect slower performance if your snapshot disks aren't fast enough to query the stale data.

You can mirror the data to a different server, read that from a snapshot and update the snapshot after all write operations are complete and replicated.  This will cause your queries to fail while you make a new snapshot but that's very fast, or you could just snapshot by day and delete the old one.  The problem with this is that standard edition of SQL Server requires you write to the mirrored pair first, then to the primary.  This means if the secondary is down the writes on the primary won't commit.  This isn't bad in your scenario actually since they only get updated once per day and if the network is fast enough, along with fast enough disks on the 2nd side to snapshot report from and write to, you're good to go.

Do you have 2 servers?  If so, I'd say mirror to the secondary in this scenario and always perform your reads against a snapshot which get's recreated once a day after the primary is updated and mirrored over.

Edit:
Note there is a HUGE difference between "Database Snapshots" and "Snapshot Isolation".  Snapshot Isolation works on a ROW level and is done to let you read the old value while the new one is being updated.  "Database Snapshots" work by making a copy of the old data page when it changes and referencing that, and it will stay until you drop it.  Note neither of these will help you if the issue is memory or disk IO.


Why do experienced SQL Server DBA's qualify examples with the default schema dbo? In PostgreSQL like in SQL Server we have a default schema under a normal install. In PostgreSQL it's public.

CREATE TABLE foo ( int a );

SELECT *
FROM foo;

Assuming a default SEARCH_PATH (which is public) the above can be written explicitly as,

CREATE TABLE public.foo ( int a );

SELECT *
FROM public.foo;

In SQL Server, there seems to be a default schema of dbo (where all database objects are stored by default). This means you can write,

CREATE TABLE dbo.foo ( int a );

SELECT *
FROM dbo.foo;

Like in PostgreSQL, in Microsoft SQL Server you can change it. If you do, so long as you never qualify it things just work. In PostgreSQL the convention is to never write the default schema unless required. So in examples and in ETL-code you'll never see the public schema explicit. But it almost seems like the convention in Microsoft SQL is to always write the default schema (dbo). Why is that? It seems like this just closes the door to users who don't have write access to dbo, or who otherwise don't want to pollute the main schema. I'm going to assume there is a good reason for this convention in SQL Server. Why not always omit an explicit dbo.?

You can see

Paul White doing this here
Joe Amish doing it here
Aaron Bertrand doing it here

MS SQL Server uses a fixed search order for unqualified names (the user's default schema, and the dbo schema). So if you do not want your queries to depend on that what the user's default schema happens to be, you always have to use schema-qualified names.

In PostgreSQL, the schema search order can be modified dynmically. The documentation says:

  Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in.

In other words, the Postgres schema lookup mechanism was specifically designed to allow omitting schema names in most queries.

(The default Postgres search path is "$user", public, which is essentially the same as that of MS SQL Server, and can lead to the same problems. But in practice, the default databases privileges do not allow users to actually create their own schemas.)


MYSQL improve query performance when using OR I have a very simple MYSQL table with 2 columns and I run this query:

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456')
                       OR (col1 = '456' AND col2 = '123')

Col1 and col2 are a composite primary key: PRIMARY KEY('col1','col2'). Each of both is also a foreign key for primary key in another table

When I ran the EXPLAIN command for the above query i got the following:

id  select_type  table  type    possible_keys   key key_len ref rows    Extra
1   SIMPLE       table  index   PRIMARY,col2    col2    8   NULL    1   Using where; Using index

The type in the above result is index which is very similar to All and so very likely to be slow on a large database. Is there a way to improve the above select command

What about using a union?

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456')
UNION ALL
SELECT * FROM table WHERE (col1 = '456' AND col2 = '123')


Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column? I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a MySQL schema export:

`timestamp` timestamp NULL DEFAULT NULL,
`offset` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
`date` date GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`time` time GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`hour` int(2) GENERATED ALWAYS AS (hour((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour))) VIRTUAL

As you can see I am storing timestamp (0000-00-00 00:00:00) and offset (+00:00) and then using generated columns to calculate, date (0000-00-00), time (00:00:00) and hour (0) values. And this is working well: I get to store the timestamp as UTC and then—by storing the offset value—I can dynamically get other info in non-UTC form.

But I don’t think the chained/nested REPLACE items are too hot. The goal is to be able to take an ISO 8601 timezone offset value like -0400 or +1000 as part of an interval [some value] hour calculation.

So is there a better way to approach this? I might be able to adjust the way that the timezone offset is stored initially so it is—essentially—exactly what I need for column calculations, but that seems messy and non-intuitive so I would rather use that +/-[for digit] format if possible.

After reading up on CONVERT_TZ, it seems like it would be the cleaner method of handling a case like this since I can cut down three (!!!) REPLACE statements with one like this:

CONVERT_TZ(`timestamp`,'+00:00',REPLACE(`offset`,'00',':00'));

It still seems a bit sloppy—since I am still dealing with having to convert offset values like -0400 to -04:00—but does work and is cleaner/easier to read and understand.

That said, the above method will fail for offset values such as +0530, +1000 and +0000. So instead of that, using the following method—which uses the INSERT() string function where colon (:) insertion is based on an offset value from the right calculated by the length of the string itself—is cleaner and works for a variety of different offsets:

CONVERT_TZ(`timestamp`,'+00:00',INSERT(`offset`,LENGTH(`offset`)-1,0,':'));

And these are the results using that with the following sample timestamps and related offsets:

SELECT CONVERT_TZ('2018-05-28 02:34:58','+00:00',INSERT('+0300',LENGTH('+0300')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 07:50:12','+00:00',INSERT('+0400',LENGTH('+0400')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 09:23:34','+00:00',INSERT('+0530',LENGTH('+0530')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 12:16:56','+00:00',INSERT('+1000',LENGTH('+1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 16:07:17','+00:00',INSERT('-0200',LENGTH('-0200')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 20:02:05','+00:00',INSERT('-0700',LENGTH('-0700')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-1000',LENGTH('-1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-0000',LENGTH('-0000')-1,0,':'));

And here are the results:

2018-05-28 02:34:58 with an offset of +0300 becomes: 2018-05-28 05:34:58
2018-05-28 07:50:12 with an offset of +0400 becomes: 2018-05-28 11:50:12
2018-05-28 09:23:34 with an offset of +0530 becomes: 2018-05-28 14:53:34
2018-05-28 12:16:56 with an offset of +1000 becomes: 2018-05-28 22:16:56
2018-05-28 16:07:17 with an offset of -0200 becomes: 2018-05-28 14:07:17
2018-05-28 20:02:05 with an offset of -0700 becomes: 2018-05-28 13:02:05
2018-05-28 23:33:03 with an offset of -1000 becomes: 2018-05-28 13:33:03
2018-05-28 23:33:03 with an offset of -0000 becomes: 2018-05-28 23:33:03


Check only prerequisites when running runInstaller in silent mode I want to check just prerequisites and generate a log without installing Oracle database.

When I run the following command:

./runInstaller -silentvalidate -responseFile /0/grid/response/crs_install_mk.rsp

It shows me the following error:

The command line arguments '-silentvalidate' are not valid options. Type 'oui -help' at the command line for instructions on appropriate command line usage.
Usage: runInstaller  [-options] [(<CommandLineVariable=Value>)*]

runInstaller has also an option -executePrereqs but it tries to launch installer in GUI mode.

So I want to check only prerequisites and generate log without launching a GUI.

Oracle Version: 11g R2

I found the answer:

To check the prequisites in silent mode run the following:

./runInstaller -silent -executePrereqs -responseFile "/0/grid/response/crs_install_mk.rsp"


Is there a name for the class of characters represented by [] in SQL Server? I am interested in the terminology for the characters you can put around table names, column names, etc. when referencing them in a query.  A name that in Microsoft SQL Server would be for []s and in MySQL would be for ``s or possibly ""s.

I am unable to find a generic term for them anywhere and am wondering if one exists.

"I think" this is what you are referring to:

Delimited Identifiers

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

T-SQL Naming Conventions

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql


TempDB's Size and the Buffer Pool Source:

  Next, if you can give tempdb its own disk, then configure it to almost
  fill the drive. If nothing else will ever be on the drive, then you’re
  better off setting it to be larger than you’ll ever need. There’s no
  performance penalty, and you’ll never have to worry about autogrow
  again.

Doesn't tempdb fill the buffer pool, so wouldn't this have an affect on things like the page life expectancy?  Or does this only have an effect if tempdb is actually in use?

Another way of asking this question - does the size of tempdb matter to the buffer pool or does the used size of tempdb matter to the buffer pool?

does the size of tempdb matter to the buffer pool or does the used size of tempdb matter to the buffer pool?

No tempdb physical size does not matter (you should properly (equally size) you tempdb files (not more than 8) with TF 1118 and 1117 enabled).

  Or does this only have an effect if tempdb is actually in use?

A system with heavy usage of tempdb, you will see many dirty tempdb pages in the buffer pool.

Remember that the Buffer pool is used for caching index and table pages as they are modified or read from the disk. Also, there is a concept called buffer pool disfavoring which prevent pages from a different database being flushed from the buffer pool when a large operation occurs (e.g. bulk activities).

  so wouldn't this have an affect on things like the page life expectancy?

Read up Page Life Expectancy isn’t what you think…


Estimate query speed for +1.5 billion rows mysql table I'm working on audio fingerprinting problem where I need to
query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to 100K rows in total, using multiple queries (between 20 and 50 queries).
The table has 3 columns, a hash and two int values. No constraints whatsoever. The hash column has a lot of collisions/duplicates. Here is the output for show create table
CREATE TABLE `fingerprints` (
  `hash` binary(10) NOT NULL,
  `int1` mediumint(8) unsigned NOT NULL,
  `int2` mediumint(8) unsigned NOT NULL,
  KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The query is simple, here is an example:
select int1 ,int2 from fingerprints where hash in (UNHEX("1ff99335cce004f2765d"),UNHEX("14c4b93ed575982ed2e4"),UNHEX("41044b0cf21dc8ac8f9b"),UNHEX("a791403ca116b4da53dd"),UNHEX("d9f91514b900c25fa095"),UNHEX("3349f906deae6cd32883"),UNHEX("221c0e3e2bc243fb0fe5").... more here);

I've tried different hardware specs (using AWS with only one machine/instance). Different my.cnf configurations but no significant performance boost.
the target speed threshold for this operation (total queries time) is 5 sec. But the best I've got in average is 3 sec for only one single query (if I have 20 queries, the total operation time is 1 minute).
final note: when profiling the query, SHOW profile command shows that the slowest part was (SENDING DATA) state. The query becomes slower when the result set is larger (i.e retrieving 10k rows takes about 6 sec, while retrieving 1000 rows takes 2 sec)
Questions:

What is the speed estimation for such query scenario for an SSD machine with enough RAM to hold indices. I have no experience working
at this scale.
Do you have recommendation for specific db setup? should I try mysql Memory engine? partitioning here is a necessity with distributed
machines? should I switch to innodb?

my setup:

read only myisam table compressed with myisampack and indexed on the where (hash) column.
the index table (MYI file) is fully loaded to RAM
SSD hard disk with limited iops (amazon AWS). According to AWS graphs i'm hitting 700 Iops sometimes.

Edit:
SHOW INDEX output:
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| fingerprints |          1 | hash     |            1 | hash        | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

EXPLAIN QUERY output (for the example query)

+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fingerprints | NULL       | range | hash          | hash | 10      | NULL | 4912 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

(UNHEXing is not a significant part of the problem.)

The real problem is the randomness of hashes.  The leads to jumping around lots of places on disk.  Let's dissect the query.

The IN list is a list of values scattered throughout the INDEX(hash).
Each value is looked up by drilling down a BTree (found in .MYI file) that is cached in MyISAM's key_buffer.  What is the value of key_buffer_size?  What is the result of `SHOW TABLE STATUS LIKE 'fingerprints' \G ?
If the index_size is greater than key_buffer_size, then many of the lookups will hit the disk.
At the leaf node of each BTree lookup will be a 5-byte (I think) "record number".
Now to lookup the row -- This will be a random disk access (a seek, no BTree) into the fingerprints.MYD at offset = 17 * record_number.  (The records appear to be FIXED length of 17 bytes.)
Again, we are looking at a probable disk hit -- now assuming that the remaining free space on disk is less than the Data_length (see the TABLE STATUS).

What to do?

Case 1:  Data_length + Index_length < RAM size:  Have key_buffer_size a little bigger than Index_length.  Gradually both caches will fill with index and data, and the I/O will go away.

Case 2:  That sum is slightly bigger than RAM:  Pick one of the caches to make big enough.

Case 3:  The sum is a lot bigger than RAM:  You are stuck with lots of I/O until you get more RAM.

I suspect the Data_length and Index_length are about equal.  I would split available RAM in half -- half for key_buffer_size, the rest for data caching.

Here are 2 more ideas:

Rather than fetching the ints in a second step, have KEY(hash, int1, int2)  This means that only the BTree lookup is needed; the data will sitting in the leaf node.  With this approach, you could set key_buffer_size to 'most' of available RAM.  That SELECT won't touch the data, only the index.
Switch to InnoDB.  It's blocks are 16KB, not 1KB.  This may make things faster.  But the disk footprint will be 2-3 times as much.  Again, use the 3-column index, but shrink key_buffer_size to 20M and raise innodb_buffer_pool_size to 70% of RAM.

Other Notes:

"Sending data" does not tell you anything.  Profiling, in general, is useless.
SSDs run a lot faster than HDDs.
You appear to be I/O bound.
Whether you are I/O-bound or not, the total query time is roughly proportional to the number of hashes being looked up.  (This can be deduced from my dissection.)
MEMORY is not likely to be significantly faster or slower than MyISAM.  And if your data needs to persist, then there is a hassle because MEMORY is volatile.
I predict that compression is useless since you have only 6 bytes to compress.  (And the hash, itself, is probably not compressible.)
If your provider is limiting the IOPs, that is a problem.  If your index is fully cached (and not so big that it is unnecessarily eating RAM), then the IOPs are fetches of data blocks.  The 3-byte key would be about 70% larger; will a large enough key_buffer fit in RAM?  If so, that approach might be optimal.


Apportioning usage of PostgreSQL WAL to database/table/query I have a PostgreSQL 11 database which hosts several databases, some of which are contain tables that are frequently inserted and updated. Recently the amount of data being written to WAL files has increased greatly (about 400%), though I believe that the number of inserts to the database has increased by only about 20%.
As a consequence not only are we using more disk, but our database performance now seems constrained by the writer performance of the disks the WALs are on.
The application is updated very frequently, and it may be that something in the application (which we control, and are able to change) has changed, which is causing less efficient inserts/updates to be made, but I don't know how we would identify that. There have probably been a many changes since this change In behaviour started, and several since it was first noticed.
Is there a way of determining which databases/tables/queries are writing to the WAL, and in what (approximate) quantity?

If you are using PostgreSQL v13, you can install pg_stat_statements, which logs the amount of WAL per statement in the wal_bytes column. So you could run
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT wal_bytes, calls, query
FROM pg_stat_statements
ORDER BY wal_bytes DESC
LIMIT 10;

The WAL itself has no connection to a certain SQL statement, but if you examine the entries with pg_waldump, you can connect them to certain files, which correspond to database objects. That route is cumbersome.
Upgrade to v13.


Error purging PostgreSQL database. Reason: ERROR: syntax error at or near "exists"Position: 15 I am using PostgreSQL database with Tomcat Server. When I checked webpage today, it shows about mentioned alarm with following logs:

2018-11-05 01:06:05,628 [1AM Thread] DEBUG com.swhh.csta.d.c.k - Purging Devices
2018-11-05 01:06:05,628 [1AM Thread] WARN  com.swhh.csta.a.n - REFRESH: TSAPI service down.
2018-11-05 01:06:05,628 [1AM Thread] WARN  com.swhh.csta.a.n - REFRESH: TSAPI service down.
2018-11-05 01:06:05,628 [1AM Thread] WARN  com.swhh.csta.a.n - REFRESH: TSAPI service down.
2018-11-05 01:06:05,628 [1AM Thread] DEBUG com.swhh.cs.e.u - No lock folder to check yet
2018-11-05 01:06:05,628 [1AM Thread] INFO  com.swhh.cs.uarchive.h - Purging and checking for historic archive runs
2018-11-05 01:06:05,628 [1AM Thread] INFO  com.swhh.cs.uarchive.h - Purging archive \\ip-address\archive3\
2018-11-05 01:06:05,643 [1AM Thread] INFO  com.swhh.cs.e.au - Purging recordings (except those locked) prior to Nov 5, 2013 1:06:05 AM
2018-11-05 01:06:05,659 [1AM Thread] INFO  com.swhh.cs.e.au - Retaining 0 contacts due to locked records from before purge point.
2018-11-05 01:06:05,659 [1AM Thread] INFO  com.swhh.cs.e.au - Retaining 0 contacts due to locked records from before purge point and/or spanning the purge point.
2018-11-05 01:06:05,659 [1AM Thread] ERROR com.swhh.cs.a.p - Alarm raised err.database.purge [ERROR: syntax error at or near "exists"
  Position: 15] [null]
2018-11-05 01:06:05,659 [1AM Thread] INFO  com.swhh.cs.e.au - Daily House-keeping completed

After checking postgres logs, I update postgres version from 8 to 9.2 and everything works now.


Multiple values for a single column I've got a small database with 5 tables: 3 of them are resources (material, skill and tool) and the other 2 are tasks (corrective maintenance and preventive maintenance).
Every table has a primary key with a code, and the task tables has a column for every resource code. My problem is that in some tasks, there is the possibility of having many resources of one type (2 materials, 3 skills or 6 tools). What is a good way of implementing this into an SQLite database? Creating many columns is not a good idea because there is not fixed number of resources that are needed.
Thank you in advance!


How to views all documents in a particular collection of database in MongoDB through mongo Shell? In the MongoDB shell, how to views all documents in a particular collection for the current database that I'm using?

when i am trying through the query

> db.getCollection().find()

To getting the error as mention below

2017-10-14T00:57:34.363+0530 E QUERY    [thread1] Error: collection constructor called with undefined argument :
DB.prototype.getCollection@src/mongo/shell/db.js:34:16
@(shell):1:1

I am also uploading the screen shot of mongo shell command prompt here

OK, let's start from basics!

After you have connected to mongod with command mongo.

List databases with command show dbs

iot:PRIMARY> show dbs
    admin  0.000GB
    iot    0.020GB
    local  0.042GB
    test   0.000GB
    testi  0.000GB

Select one of the DB's with use iot command

iot:PRIMARY> use iot

switched to db iot

List collections on that DB with show collections command

iot:PRIMARY> show collections
data
header
key

Make query to one of those collections

iot:PRIMARY> db.header.find()
{ "_id" : "1b5caa", "temp1" : "Temperature", "pressure1" : "Pressure", "humidity1" : "Humidity", "uv1" : "UV", "BusV1" : "Solar Panel (V)", "Current1" : "Solar Panel Current (mA)", "BusV2" : "Battery (V)", "Current2" : "Battery Current (mA)" }
{ "_id" : "30444", "temp1" : "Temperature", "pressure1" : "Pressure", "humidity1" : "Humidity" }
{ "_id" : "239684", "temp1" : "Temperature", "pressure1" : "Pressure", "humidity1" : "Humidity" }

So, you need to be connected WANTED database with use command and you need to show the one collection what you want to query with db.<collection_name>.find()

How to see to what database I'm connected currently? Just give command db and you get the answer what is your current DB.


Saving a SQL Server database to a file and opening it on another server under any database name I am trying to find an efficient way of being able to physically save and open a SQL Server database like how it's done with Access. I would like to be able to save my entire database into a file and open it on some other server. I have already tried backup/restore but they're very tricky and it does not work all the time. Is there a way to do it other than the backup/restore procedure?

The following is what I get when I try to do the restore. I tried every possible approach that I could think of, but it simply doesn't restore.

Backup and restore are the simplest/safest ways to do this. There are a couple of things that you need to watch out for, or you'll get errors like the one you're seeing now.

SQL Server will default to restoring the files to their original physical locations. If you're restoring to a different server, or a different database name, these paths probably aren't correct. You will have to specify the correct new database name, and file locations (WITH MOVE...).
If you're overwriting an existing database, you'll need to tell SQL Server that you specifically intend to do this (WITH REPLACE). This is a safety feature; don't get into the habit of blindly using this option unless you know you need it.

The restore GUI tool in Management Studio supports both of these options.

To specify the name of the database you want to restore as, go to the "General" page, and enter the correct name in the "Database" field (under the "Destination" subhead).

To specify where you want the database files to be restored to, go to the "Files" page, and specify the new paths in the "Restore As" column.

If you're overwriting an existing database, you'll need to go to the "Options" tab and check the "Overwrite the existing database" option.

If this is a procedure you plan to repeat regularly, you can generate a script for the RESTORE command by clicking the "Script" button on the Restore Database window toolbar.


How to set indexes on a table with a FILESTREAM ROWGUID? I'm confused with the requirement for a ROWGUID column on a table with FILESTREAM. How is this column going to be used?

Would it be okay if I used a different primary key (clustered) on the same table while letting that ROWGUID column "just be there" without a clustered index?

Like this:

CREATE TABLE Sample
{
    -- This is going to be clustered
    [Id] int NOT NULL,

    -- What shall I do with it index-wise?
    [RowGuid] uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE
}

UPDATE: I found something of potential relevance:
http://msdn.microsoft.com/en-us/library/cc949109%28d=printer%29.aspx

They've got a similar example:

CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID () ON Data_FG1)
ON DocPartScheme (DocumentID)
FILESTREAM_ON DocFSPartScheme;
GO

With a remark:

  Notice that to use the DocumentID column as the partitioning column, the underlying nonclustered index that enforces the UNIQUE constraint on the DocGUID must be explicitly placed on a filegroup so that the DocumentID column can be the partitioning column. This means that partition switching is only possible if the UNIQUE constraints are disabled before performing the partition switch, as they are unaligned indexes, and then re-enabled afterwards.

I suppose I'll then leave ROWGUID be as it is, a UNIQUE nonclustered index.


Connecting two different 'user' tables into one single table How to connect three tables so when the admin inserts one row in a table, that row will be copied and pasted in another table? Or how to define a relationship between them?

I have three tables in a database, they are tb_student, tb_teacher, tb_user. Their DDL design is shown below:

-- Teacher Table: Primary Key is `id_teacher`

CREATE TABLE `tb_teacher` (
  `id_teacher` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Sample Data

INSERT INTO `tb_teacher` (`id_teacher`, `name`, `status`) VALUES
('14320', 'Lenny Belardo', 'active'),
('14323', 'Shoffin Nahwa Utama', 'nonactive');

-- Student Table: Primary Key is `id_student`

CREATE TABLE `tb_student` (
  `id_student` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entry_year` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Sample Data

INSERT INTO `tb_student` (`id_student`, `name`, `entry_year`, `status`) VALUES
('352014110577', 'Buddha Gautama', '2014', 'active'),
('352014121153', 'Harie Tanoesoedibjo', '2015', 'active');

tb_user is kind of like the combination of in both tb_student and tb_teacher

-- tb_user: Primary key is ID

CREATE TABLE `tb_user` (
  `id_user` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entry_year` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `level` enum('admin','teacher','student') COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I intend to create accounts for both students and teachers; therefore, a user table is necessary to create. The data type from tb_student.id_student and tb_teacher.id_teacher is matching with tb_user.id_user which is VARCHAR(13). The sample data is as the following:

INSERT INTO `tb_user` (`id_user`, `username`, `name`, `password`, `entry_year`, `level`, `status`) VALUES
('14231', 'spectrum@unida.gontor.ac.id', 'Nunik Siti Nurzanah', '123456', '2014', 'teacher', 'active'),
('352014121188', 'tukiyem@ngebel.udik.gov.id', 'Tukiyem', '123456', '2012', 'student', 'active');

Where the id 14231 is a teacher and the id 352014121188 is a student.

How to define a relationship between these three tables so that when the admin inserts a new row of student/teacher, the data is copied automatically to the user table?

Right now I still need to insert the data for these three tables manually or one-by-one.

Option 1: Single table

If you don't really have a lot of unique columns that belong only in tb_student or in tb_teacher, then it might make sense to combine everything in the tb_user table, and there would be no need for the tb_teacher and tb_student tables. They would be superfluous. But there could be reasons for keeping them separate, like maybe relationships to the other tables in your schema.

Option 2: Normalisation

So if you try to follow the principles of normalisation, and keep three separate tables, then all columns that are unique to the tb_student tables should be found only there, and likewise with the tb_teacher table. (You don't have anything unique to the tb_teacher table at the moment, but maybe you'll think of something later?) You will need to add a column e.g. id_tb_user in each of the tb_student and tb_teacher tables, and declare foreign key constraints for these columns referring to the tb_user table. E.g.:

CREATE TABLE tb_teacher (
...
  id_tb_user int unsigned,
...
  CONSTRAINT tb_teacher_fk1 FOREIGN KEY (id_tb_user)
    REFERENCES tb_user(id_user)
...
)

The tb_user table will contain all the shared columns: name, status and some of the other columns you already have, but not entry_year.

To retrieve information about students, including info about them in the tb_user table, you will then use a query with a simple INNER JOIN:

SELECT s.id_student,
  s.entry_year,
  u.username,
  u.name -- + any other columns you want
FROM tb_student s
  INNER JOIN tb_user u ON s.id_tb_user = u.id_user
WHERE
  u.username = 'spectrum@unida.gontor.ac.id';
  -- or any other condition you want

(To retrieve info about students and teachers in a single query, use instead two LEFT OUTER JOINs.)

By the way, you should also explicitly declare your primary keys as such. It's also a good idea to use int unsigned as the data type for these columns (or even bigint unsigned, although that is rarely needed), and it's also often a good idea to use auto_increment (see also the FAQ) so that MariaDB/MySQL does the job of generating a new id when you insert a new row:

CREATE TABLE tb_user (
    `id_user` int unsigned auto_increment PRIMARY KEY,


Changing master for channel gives syntax error I'm trying to set up a multi-source replication

When trying to set a master for a specific channel using this query

CHANGE MASTER TO MASTER_HOST='192.168.0.203', MASTER_USER='rep', MASTER_PORT=3306, MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107 FOR CHANNEL 'master-203';

I get the following error

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'FOR CHANNEL 'master-203'' at line 1

I can't figure out what's the problem. The query is pretty much a copy-paste from the official documentation

My server is running MariaDB 10.1.21

What could be wrong?

You're looking at the documentation for MySQL 5.7, but you're running MariaDB 10.1, and replication channels happen to be a feature that is implemented differently in MariaDB.

From the MariaDB documentation on multi-source replication:

  You specify which master connection you want to work with by either specifying the connection name in the command or setting default_master_connection to the connection you want to work with.

So in your case you could try:

CHANGE MASTER 'master-203' TO MASTER_HOST='192.168.0.203',
MASTER_USER='rep',
MASTER_PORT=3306,
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=107;

(Disclaimer: I've not tried this in practice.)


table design for display content In my previous work station, I saw them have a table design across all website they have worked on displaying editor content like this:

column|page_id| title |content_1|... |content_n| lang  |update_date|...
-----------------------------------------------------------------------
row   |   1   |varchar|   text  |text|  text   |tinyint|  datetime |
......

  where content_n column are determined by the max editor it overall
  has on that website.

  For an example: If the website generally has 1 editable content per
  page but in addition, there is one page that has 4 editable content,
  they will have 4 content_n column.

The problem(imo):

For pages that have only 1 editable content area, only content_1 will be filled, and other column content_n will be empty. In addition, with multiple languages involve, those empty field add up.

My question:

Is this a good design of choice?
Wouldn't those empty column create unnecessary overhead?
What would be an improved table design when have a table such as this?

My thought :

I will probably merge all the content and title into one single column(mediumtext/longtext) and use json_encode/array to differentiate the content.

they usemysql(MYISAM) for all database engineer, and I prefer Innodb..if that is matter

Do not build arrays as columns.  Instead, use extra tables.  If the different content_* are differeng languages, have another table with page_id (for JOINing), content, and lang.  Probably the PRIMARY KEY would be (page_id, lang).


sp_OACreate vbscript.regexp Class not registered Imagine a Windows Server 2003 Enterprise x64 Edition.

I can create a 'vbscript.regexp' object using a vbscript (confirming that the DLL is registered).

I cannot create a 'vbscript.regexp' object using sp_OACreate in a SQL Server 2005 SP3 (.4053)(cluster) environment.

OLE Automation is checked ON in surface area configuration.

The sp_OACreate was working until very recently.

Can you help me?

*CODE*

            declare
                @source varchar(5000),
                @regexp varchar(1000),
                @replace varchar(1000),
                @globalReplace bit,
                @ignoreCase bit

            set @source = 'NBG76TF43'
            set @regexp = '[^0-9]'
            set @replace = ''
            set @globalReplace = 1
            set @ignoreCase = 1

                DECLARE @hr integer
                DECLARE @objRegExp integer
                DECLARE @result varchar(5000)

                EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'A'
                        PRINT @hr
                    END
                EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'B'
                        PRINT @hr
                    END
                EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'C'
                        PRINT @hr
                    END
                EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'D'
                        PRINT @hr
                    END

                EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
                IF @hr <> 0
                    BEGIN
                        EXEC @hr = sp_OADestroy @objRegExp
                        PRINT  'E'
                        PRINT @hr
                    END

                EXECUTE @hr = sp_OADestroy @objRegExp
                    IF @hr <> 0
                        BEGIN
                            PRINT  'F'
                        PRINT @hr
                        END

                PRINT  @result

            /*
            A
            -2147211480
            B
            -2147211480
            C
            -2147211480
            D
            -2147211480
            E
            -2147211480
            F
            -2147211480
            */

Ten months later, the issue occurred again on the same system.

Specific error: 0x8007045A (A dynamic link library (DLL) initialization routine failed)

I cannot find the cause of this issue. However, I found enough related scenarios  (ten months ago) that suggested that rebooting the server would solve the problem.

I rebooted the server cluster (both nodes, following controlled failovers), and the issue was resolved (can create a 'vbscript.regexp' object using sp_OACreate).


How can I tell if a SQL Server backup is compressed? We have recently upgraded from SQL Server 2005 to SQL Server 2012.  Under SQL Server 2005 there is no option to create compressed backups as there is in 2012.

If you attempt BACKUP DATABASE ... WITH (COMPRESSION); to a file that has already been initialized without compression, the BACKUP DATABASE command will fail with the following error message:

ERROR MESSAGE : BACKUP DATABASE is terminating abnormally.
ERROR CODE : 3013

How can I tell if an existing backup file is initialized for compressed backups?

Maybe instead of backing up over and over again to the same file, you should consider using WITH INIT and a new file always. I think it is simpler to manage multiple backup files, all with their own timestamp embedded into the filename, and be able to archive/purge each file individually. When you keep dumping backups to the same file, it just gets bigger and bigger, and harder to manage IMHO. Never mind that you no longer have to care that

Also I am not sure why you would ever be turning backup compression on and off. Have you found a case where disabling it is better? Do you have real use cases where on an edition that supports compression you are taking one-off backups without compression, and using the same file? Why?

Anyway you can always do something very simple like:

BEGIN TRY
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak' WITH COMPRESSION, ...;
END TRY
BEGIN CATCH
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak', ...;
END CATCH

The error bubbles up immediately before any work is done.

But I still think it is much better to just not use the same file over and over again in the first place. IMHO.


Convert row into concatenated string with column names included I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in the query?

I'm doing this because the columns represents fields in an event report. I'm putting them back together so a person can read the report in a logical manner.

I've done some of this with a query, but it is laborious to do for each column and seems error-prone.

Here is a brief snippet showing three columns concatenated in the format I need, done in the column-by-column approach:

SELECT
 Concat(
   IIf(Id IS NULL, Null, Concat('Id: ' , [Id] , '\n') )  ,
   IIf(StandardClientId IS NULL, Null,
     Concat('StandardClientId: ' , [StandardClientId] , '\n') )  ,
   IIf(ClientName IS NULL, Null,
     Concat('ClientName: ' , [ClientName] , '\n') )
 )  AS ReportLine
FROM dbo.DataDecoded;

Thanks

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server Management Studio.  Once you get the hang of how this works, it can become something you use almost every day.

"Drag-and-drop" the list of columns from the Object Explorer window onto a Query Window, as shown:

I'm using the dbo.spt_fallback_usg table as an example.  After I drag-and-drop them into the query window, the columns are:

xserver_name, xdttm_ins, xdttm_last_ins_upd, xfallback_vstart, dbid, segmap, lstart, sizepg, vstart

Hit [CTRL + H] to open the "Search and Replace" dialog box:

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box.  Pressing the "Replace All" button will put each column name on a new line.  Next, modify the "Search and Replace" items like this:

This will turn each individual column name into a CASE WHEN... statement.

Now replace the "newlines" with +, as in:

This results in the following code in the query window:

CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END +
CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END +
CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END +
CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END +
CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END +
CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END +
CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END +
CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END +
CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END +

This can then be manually wrapped into a SELECT statement like:

SELECT
    CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END +
    CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END +
    CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END +
    CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END +
    CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END +
    CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END +
    CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END +
    CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END +
    CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END
FROM dbo.spt_fallback_usg


How to display queries of separate tables in adjacent columns? I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.

Here are the schema of the two tables (edited for readiability):

                         Table "public.planning_constraints"
   Column    |           Type           |          Modifiers
-------------+--------------------------+-------------------------------
 start_time  | timestamp with time zone |
 end_time    | timestamp with time zone |
 sot_allowed | interval                 |

                         Table "public.planning"
      Column      |           Type           |         Modifiers
------------------+--------------------------+----------------------------
 start_time       | timestamp with time zone |
 end_time         | timestamp with time zone |
 sot_contribution | interval                 |

I can query them separately and produce the totals that I want.  The query of the "planning_constraints" table is:

SELECT
  date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(sot_allowed) AS minutes_allowed
FROM planning_constraints
WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:00+00' AND comment like '6%'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_allowed
--------------+-----------------
 2016-11-26   | 01:24:00
 2016-11-27   | 01:38:00
 2016-11-28   | 01:29:00
 2016-11-29   | 01:43:00
 2016-11-30   | 01:32:00
 2016-12-01   | 01:41:00
 2016-12-02   | 01:43:00
(7 rows)

The query of the "planning" table is:

SELECT
  date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(sot_contribution) AS minutes_planned
FROM planning
WHERE start_time>='2016-11-26 18:00:00' AND start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_planned
--------------+-----------------
 2016-11-26   | 01:19:12
 2016-11-27   | 01:38:23
 2016-11-28   | 01:27:36
 2016-11-29   | 01:40:39
 2016-11-30   | 01:27:16
 2016-12-01   | 01:38:28
 2016-12-02   | 01:42:50
(7 rows)

I would like to present the results of these queries in adjacent columns, along with the difference between them.  Here is my attempt to do so:

SELECT
  date_trunc('day', c.start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(c.sot_allowed) AS minutes_allowed,
  sum(p.sot_contribution) AS minutes_planned,
  (sum(c.sot_allowed) - sum(p.sot_contribution)) AS diff
FROM planning_constraints c, planning p
WHERE c.start_time>='2016-11-26 18:00:00+00' AND c.start_time<'2016-12-03 18:00:00+00' AND c.comment like '6%'
AND p.start_time>='2016-11-26 18:00:00' AND p.start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_allowed | minutes_planned |   diff
--------------+-----------------+-----------------+-----------
 2016-11-26   | 576:48:00       | 43:37:36        | 533:10:24
 2016-11-27   | 672:56:00       | 43:37:36        | 629:18:24
 2016-11-28   | 611:08:00       | 43:37:36        | 567:30:24
 2016-11-29   | 707:16:00       | 43:37:36        | 663:38:24
 2016-11-30   | 631:44:00       | 43:37:36        | 588:06:24
 2016-12-01   | 693:32:00       | 43:37:36        | 649:54:24
 2016-12-02   | 707:16:00       | 43:37:36        | 663:38:24
(7 rows)

The 'minutes_allowed' and 'minutes_planned' columns do not present the correct values.  I feel like I am missing something small here, however, I have been unable to find a solution.

You are basically building two tables, which then has to be joined on the date column.  In your query you miss the join condition.

One clear way of solving this is building the two result sets as CTEs (WITH queries):

WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned
            FROM planning_constraints
            ...),
      p AS (SELECT planning_day, sum(sot_allowed) AS minutes_allowed
            FROM planning
            ...)
SELECT planning_day,
       COALESCE(minutes_allowed, interval '0 minute') AS allowed,
       COALESCE(minutes_planned, interval '0 minute') AS planned,
       COALESCE(minutes_allowed, interval '0 minute') -
           COALESCE(minutes_planned, interval '0 minute') AS diff
  FROM pc FULL OUTER JOIN p USING (planning_day)
 ORDER BY planning_day;

FULL OUTER JOIN means if there is a day specified in either one of the result sets, there will be a line for that day.  If this is not what you want, figure out which type of join you need.

Notes:

planning_day does not need to be COALESCEd, as the USING clause takes care of this
also, there is no need to use a GROUP BY in the outer query, as planning_day is already the key of both source tables
the ORDER BY can be now removed from both subqueries


Ansi SQL: Auto Numbered Column Is there an ANSI standard for auto-numbered columns.

Currently, we have a choice of SERIAL, AUTOINCREMENT, AUTO_INCREMENT, IDENTITY() and good old NEXTVAL() among others.

I read somewhere that there is a new standard using IDENTITY which I know that Oracle has recently implemented. I know that Oracle is hardly the last word on standards.

If there is a standard, then it’s a long time coming.

Yes there is, and it's definitely not "a new standard". It's been in the SQL standard since SQL:2003

The definition is:

<identity column specification> ::=
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    [ <left paren> <common sequence generator options> <right paren> ]

The optional part with the sequence generator options lets you define the characteristics of the underlying sequence (=generator), you could do something like:

id integer GENERATED ALWAYS AS IDENTITY (start with 42 increment by 10 cycle)

This syntax is supported (at least) by PostgreSQL 10, Oracle 12.1, DB2, Apache Derby, HSQLDB, Firebird and NuoDB.


How can I get more information about which lookup failed in an SSIS lookup? I'm frequently getting errors along the lines of

Error: 0xC020901E at Build Test Data, Lookup ID String[120]: Row yielded no match during lookup.

To resolve these, I'm going back to the source and making intelligent guesses, but it would be far easier if I could see the row that fails.

What's the best way to do this in SSIS (using MSSQL2012)?

Thanks.

There's nothing I've encountered that provides out of the box capability for this and I'll be delighted if someone proves me wrong.

Therefore, what I do is a controlled failure.

My lookup against DimEmployee should always yield a match. Data was loaded to that table before this dependent package executed. There's no opportunity for source data to change betwixt that load and but I was still running into situations where the lookup yields no match. And no, it's not a late arriving dimension, just they had a poor understanding of what the requirements were.

At any rate, what I do is set no match to "Redirect Rows to No Match Output". For those of you on 2005, you'll have to make do with the "Redirect Rows to Error Output"

I then count how many rows flow out of the failed lookup because if there's one failure, there might be more. That's the downside to even if the lookup could capture this row failed - it's only going to show you the first row and I generally want to know all the failures.

Controlled failure

I use a script task for this and as I type this up, I can see how this could be made into a re-usable component... My Script Task acts as a Transformation but I could have just as easily specified a Destination. I map in the columns that I used in the lookup and yielded no match. For this example, I have an EmployeeID and their EffectiveDT

What I'm going to do in this component is fire a Warning event for every row that comes through. The default for 2012 projects run in the SSISDB will capture warning events. Once all the rows have gone through, in my PostExecute method, I'll raise the Error event which will cause the DataFlow as a whole to fail.

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    /// <summary>
    /// Variable identifying whether we had any rows
    /// </summary>
    bool Found;

    /// <summary>
    /// Initialize our variable
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        this.Found = false;
    }

    /// <summary>
    /// Error out the entire task if we found any rows
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();

        if (this.Found)
        {
            bool cancel = true;
            ComponentMetaData.FireError(0, "SCR Lookup Employee", "Unmatched Employees found. See warning for more context", string.Empty, 0, out cancel);
        }
    }

    /// <summary>
    /// Emit warnings for all the bad rows and then flag the entire operation as having bad rows
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        this.Found = true;
        string msg = string.Format("Research->{0}:{1}", Row.EmployeeID, string.Format("{0:yyyy-MM-dd}", Row.EffectiveDT));

        ComponentMetaData.FireWarning(0, "Unmatched Employees", msg, string.Empty, 0);
    }
}

I then run my package on the server and observe the execution/operation id. If you go into the All Executions report in SSMS, this is the value in the first column, ID. In this case, I see 938 so I then run the following query in the SSISDB

USE SSISDB;
SET NOCOUNT ON;

DECLARE
    @operation_id bigint = 938;

WITH SRC AS
(
    SELECT
        OM.message
    ,   CHARINDEX('->', OM.message) AS arrow
    ,   CHARINDEX(':', OM.message, CHARINDEX('->', OM.message)) AS colon
    ,   LEN(OM.message) AS length
    ,   RIGHT(OM.message, LEN(OM.message) - CHARINDEX('->', OM.message) -1) AS elements
    FROM
        catalog.operation_messages AS OM
    WHERE
        OM.message_type= 110
        AND OM.message_source_type = 60
        AND OM.message LIKE '%research%'
        AND OM.operation_id = @operation_id
)
, PARSED AS
(
    SELECT
        SRC.message
    ,   CHARINDEX(':', SRC.elements) AS colon
    ,   LEN(SRC.elements) AS length
    ,   SRC.elements AS RelevantText
    ,   LEFT(SRC.elements, CHARINDEX(':', SRC.elements) -1) AS EmployeeID
    ,   RIGHT(SRC.elements, LEN(SRC.elements) - CHARINDEX(':', SRC.elements)) AS EventDate
    FROM
        SRC
)
SELECT
    P.message
,   P.RelevantText
,   P.EmployeeID
,   P.EventDate
FROM
    PARSED AS P;

That's going to give me all the relevant bits I'd see in the "All Messages" report but with the advantages of I can click and select things and I've already parsed out what I need.

message RelevantText    EmployeeID  EventDate
DFT Load FactPayroll:Warning: Research->90132693:2011-05-25 90132693:2011-05-25 90132693    2011-05-25
DFT Load FactPayroll:Warning: Research->900432371:2011-05-25    900432371:2011-05-25    100432371   2011-05-25
DFT Load FactPayroll:Warning: Research->900443209:2011-05-25    900443209:2011-05-25    100443209   2011-05-25
DFT Load FactPayroll:Warning: Research->900443418:2011-05-25    900443418:2011-05-25    100443418   2011-05-25

Running in interactive mode

If you're running this from the context of Visual Studio/SSDT/BIDS, I wouldn't even worry about the script's output, just add a Data Viewer and then you don't have to worry about crawling through logs.

Alternative to modifying your package (2012+)

The 2012 release of SSIS gave us a new tool for troubleshooting already deployed packages: Data Taps. These are a per-execution mechanism that allows you to generate a CSV (only) extract at a particular point in a Data Flow. This is amazingly helpful in highly regulated environments (SOX/SAS 70/HIPPA/PCI) where you can't just deploy an updated package without signoff from everyone on planet Earth. Just run the package with a special bit set and it'll generate an CSV in a well known location.

For the referenced screenshot above, if I wanted to see all the source data that was going to hit "LKP DimEmployee", I'd identify my PackagePath: \Package\DFT Load FactJobAction and my IdentificationString: Paths[ALL DateSK.Union All Output 1] and then use the script in Josh's article

USE SSISDB;

DECLARE @ExecutionID  BIGINT

/*****************************************************************************
--  First create an execution instance.  Data taps are valid for the specified
--  execution instance only
*****************************************************************************/
EXEC catalog.create_execution
        N'DW Folder',  --Folder name in SSISDB
        N'DW Project', --Project name in SSISDB
        N'FactJobAction', --Package name in SSISDB
        NULL, --optional parameter to hold reference ID for later use
        0, --optional  parameter set to 1 if 32-bit runtime required
        @ExecutionID OUTPUT;

DECLARE @DataTapID BIGINT;

/******************************************************************************
--  Next create the actual data tap.  The parameters specified below deterimine
--  at which point in a specific package the data tap will be added.
******************************************************************************/
EXEC catalog.add_data_tap
        @ExecutionID, --output from catalog.create_execution
        N'\Package\DFT Load FactJobAction', --PackagePath property value from data flow task in SSDT
        N'Paths[ALL DateSK.Union All Output 1]', --IdentificationString property value from data flow task in SSDT
        N'File.csv', --Desired Output file name
        NULL, --optional paramter to specify number of rows to log. NULL for all rows
        @DataTapID OUTPUT; --output ID

/******************************************************************************
--  This final block of code executes the package.  The data tap file output
--  will be found in the %SSISRoot%\DataTaps directory upon completion
******************************************************************************/
EXEC catalog.start_execution
        @ExecutionID;  --output from catalog.create_execution


Predicate re-write in where clause How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing index scan instead of seek. Please help me out with this.

The easiest way would be to add a computed column to the table with your mod calculation, and then create an index or add it to an existing index.

For example:

-- Test table and values
CREATE TABLE #Test (col integer PRIMARY KEY);

INSERT #Test (col)
VALUES (1), (2), (3), (4), (5);

-- Original query (scan)
SELECT T.col
FROM #Test AS T
WHERE col % 5 = 4;

-- Add computed column (no need for persisted)
ALTER TABLE #Test
ADD col_mod5 AS col % 5;

-- Index computed column
CREATE INDEX i
ON #Test (col_mod5)
INCLUDE (col);

-- Index seek
SELECT T.col
FROM #Test AS T
WHERE col % 5 = 4;

-- Tidy up
DROP TABLE #Test;

Another alternative is to create an indexed view selecting what you need from the table, plus a column with the mod calculation, and refer to that in your queries. You do this by declaring the view as schemabinding and then creating a clustered index on it as if it were a table.

If you are using Enterprise Edition, you may not need to change your query to use the view. Otherwise, you will need to reference the view explicitly and use the NOEXPAND hint. The indexed computed column is the lighter weight solution.


a user, that is a organizer can create conferences, but can also participante in conferences In a conference management system, a user can create one ore many conferences. The user that creates a conference is the conference organizer. But a user can also register in a conference.
So there should be 3 entities: Conference, User, Organizer

Conference: id, name, description
Organizer: id, name, description
User: id, name, username, password, email, etc

Now, do you know how the relationships between this entities should be?
This model seems difficult to understand, because a organizer can organize multiple conferences and a conference is organized by one organizer.  So should be a 1 to many relationship between organizer and conference? But then, the organizer is also a user that besides create a conference can also register in conferences. So how should be the relation between the user and the event/organizer?

First, lets consider the User - Organizer relationship:

An Organizer is a User but a User may or may not be an Organizer

This is a one-to-one (1 - 0..1) relationship. One way to implement it is by having the PRIMARY KEY of Organizer being a FOREIGN KEY that references User:

User:
    user_id         PK
    name
    username
    password
    email

Organizer:
    organizer_id    PK  FK -> User
    description

Now, for the Conference, it seems there are 2 relationships:

An Organizer can organize many Conferences; a Conference has exactly one Organizer.

This is one-to-many relationship as you correctly noted. The common way to implement it is to have organizer_id in Conference:

Conference
    conference_id   PK
    organizer_id       FK -> Organizer
    name
    description

The relationship between User and Conference is probably many-to-many. A User can participate in many conferences and a conference can have many participating user.

We need a junction table:

Participation
    conference_id   PK FK1 -> Conference
    user_id         PK FK2 -> User


Copying the database from SQL Server to PostgreSQL I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of data in SQL Server. Is there any tool that can simplify this copying process? Also, I assume compression techniques have become much more advance in recent years so how much storage I should expect in PostgreSQL?

For the person who's done this before I've looked at many tools and there are many pitfalls in doing that.  The cheapest way I found to do this was to do a text dump, convert, and load.

My setup was MSSQL 2005 to PostgreSQL 8.x on Linux.  The reason I say convert is because in the bulk bcp dump there is no way to distinguish between '' and NULL so the COPY command I used to load was complaining about NULL value for a table that had '' as a part of the primary key.

Dumping the schema also required conversion since the types are not exactly compatible, so it's been a bit of work but no buying of data migration tools which can be expensive for what may be just one time use.

There are other tools like this one but from what I have tested(2 years ago) has limitations.


how to show a specific value for a non existing value in second table in join? I have 2 tables, City(city_id, city_name) and count_staff_in_city(city_id, count_staff). the first table keeps the name of the city and its id, and the second table keeps the number of staffs working in each city. I want to join this 2 table in this way: if City.city_id exists in count_staff_in_city.city_id then show its value in select. if City.city_id does not exists in count_staff_in_city, then 0 should appear in output for this city.

how can I do this??

Use an outer join and replce NULLs using COALESCE:

SELECT C.city_name, COALESCE(CS.count_staff,0) AS count_staff
FROM City AS C
LEFT OUTER JOIN count_staff_in_city AS CS ON C.city_id = CS.city_id;


Oracle dbms_xplan.Display() equivalent to SQL Server 2017 I am converting some sql from Oracle to SQL Server 2017.

One of SQL code is having dbms_xplan.Display() inside the script.

I want to know what is the equivalent of dbms_xplan.Display() in SQL Server 2017.

SET STATISTICS XML ON;
GO
SELECT ... FROM ...;

This will produce a rowset with a clickable XML showplan:

Clicking on the XML will render a new tab with the graphical showplan. If you just want text output, instead of STATISTICS XML, use:

SET SHOWPLAN_TEXT ON;
GO
SELECT ... FROM ...;


Mirroring on-premise SQL Server to AWS EC2 SQL Server Trying to mirror an on-premise SQL Server instance to EC2 instance, but not much information is available on this topic. The closest one I managed to find is: Implement SQL Server High Availability and Disaster Recovery on Amazon EC2 - Part 4

Has any one done this? If so is there any documentation available?

My intention is to make the EC2 as a backup for the HA in case my on-premise DB crashes.

Please note that this is not for AWS RDS, nor EC2 SQL Server to EC2 SQL Server; but on-premise SQL Server to AWS EC2 SQL Server.

The DB version is not a constraint, it can be SQL Server 2017 too.

I know this is very old but yes I have this working. Ended up using mirroring due to company policy between SQL 2016 AWS and on premise SQL 2016 running on Vmware.

Found this link very helpful.

https://tecadmin.net/setup-database-mirroring-sql-server/

I know not exactly what you were looking for but thought might help.


PostgreSQL; Update different column on condition using CASE statement I have a table which has different types of balance and I need to deduct the balance from different columns based on availability.
Here is the query (Sample) I tried to use but it didn't work
update table_A
set case
    when column_A>table_B.balance then column_A
    when column_B>table_B.balance then column_B
    when column_C>table_B.balance then column_C
    end =value
from table_B
on table_A.Id=table_B.id

Thanks!

It is to be something like (logic only)

update table_A
set column_A = case when     (column_A>table_B.balance) then value else column_A end,
    column_B = case when not (column_A>table_B.balance)
                     and     (column_B>table_B.balance) then value else column_B end,
    column_C = case when not (column_A>table_B.balance)
                     and not (column_B>table_B.balance)
                     and     (column_C>table_B.balance) then value else column_C end
from table_B
on table_A.Id=table_B.id


Search for null in id column over all tables programmatically I want to automatically check for null ID's over all id columns in my database, I have to check all tables of which there is about 50,000 so manually doing this is infeasible

This is a function that isn't very elegant, nor optimized, but perhaps it can be a start to get you going. It loops over all tables in a database and in case a table contains a column with name id and if any row value in that column is NULL it will return the table name. The function is quite stupid as it will not check if the column is a primary key, just that its name is id. And I'm certain there is quite a big room for improvement.

CREATE OR REPLACE FUNCTION check_tables_id()
  RETURNS table(tablename TEXT) AS
$BODY$
DECLARE row RECORD;
DECLARE has_id_col BIGINT;
DECLARE has_null BIGINT;

BEGIN
    FOR row IN
    -- Restrict to specific database and schema if necessary by specifying
    -- table_catalog='_dbname_' and/or table_schema='_schema_' in WHERE-clause
        SELECT * FROM information_schema.tables
    LOOP
    -- Check if id col exists
    EXECUTE 'SELECT COUNT(*) FROM information_schema.columns
        WHERE column_name =''id''
        AND table_catalog=''' || quote_ident(row.table_catalog) || '''
        AND table_schema=''' || quote_ident(row.table_schema) || '''
        AND table_name=''' || quote_ident(row.table_name) || ''';' INTO has_id_col;
    IF has_id_col > 0 THEN
        -- Check if col ID is null
        EXECUTE 'SELECT count(*)
        FROM ' ||  quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || '
        WHERE id IS NULL' INTO has_null;
        -- Return table name
        IF has_null > 0 THEN
            RETURN QUERY EXECUTE 'SELECT ''' ||  quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || '''::TEXT AS tablename';
        END IF;
    END IF;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';

Just run the function using:

SELECT * FROM check_tables_id();

The result you get is a list like this:

dbase=# select * from check_tables_id();
   tablename
----------------
 dbase.table3
 dbase.table4
(2 rows)


remove postgres server from local machine I know this is a real beginner question. But nevertheless I'm really confused:/
I wanted to have a clean installation of postgres on my ubuntu laptop. So I tried to delete everything related to postgres.
However, when I start running the PgAdmin, I see this under the servers:

I don't even remember when I created this robin_test-server. But now I fail to remove, connect or find any information about it. Anybody might have an idea on what to do in this situation? Any help is super appreciated:)

PgAdmin stores preferences and a list of servers in the .pgadmin directory in your HOME directory.
To reset it to a pristine state, you may delete this directory, when the application is not running.


Insert Into table ommitting columns that have default values I have a PostgreSQL database with lots of tables of the same structure, 36 columns in total:

CREATE TABLE some_schema.some_table    (
    id integer NOT NULL DEFAULT nextval('some_schema.id_seq'::regclass),
    col2,
    col3,
    col4,
    [...],
    col35,
    mi_prinx integer NOT NULL DEFAULT nextval('some_schema.mi_prinx_seq'::regclass),
    CONSTRAINT some_table_pkey PRIMARY KEY (mi_prinx)
)

In many instances I will have to insert records from another table with the same structure:

INSERT INTO some_schema.some_table (col2,col3...col35)
    SELECT col2,col3...col35
    FROM some_schema.another_table_with_same_structure;

Is there a way of doing this without having to list all columns that don't have a default value? I think I can use DEFAULT VALUES somehow but I can't get the syntax right based on the documentation.

Is there a way of doing this without having to list all columns that don't have a default value?

No you either insert all the columns using the * expansion,

INSERT INTO foo
  SELECT * FROM bar;

Or, you insert just the listed columns

INSERT INTO foo (col2,col3)
  SELECT col2, col3
  FROM bar;

You can also use DEFAULT to be explicit, but no one does that. It's in the spec however, Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none. Being explicit looks like this.

INSERT INTO foo (col1,col2,col3)
  SELECT DEFAULT, col2, col3
  FROM bar;

DEFAULT COLUMNS sets all columns to their respective default. From the docs on DEFAULT COLUMNS,

  DEFAULT VALUES, All columns will be filled with their default values.

CREATE TABLE foo (id serial, foo serial, bar serial);
INSERT INTO foo (id,foo,bar) VALUES (DEFAULT, DEFAULT, DEFAULT);
INSERT INTO foo DEFAULT VALUES;
INSERT INTO foo(id,foo,bar) VALUES (42,42,DEFAULT);

TABLE foo;
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
 42 |  42 |   3
(3 rows)