Access Value between ranges I'd like the following population logic built in an Access database:

I have two tables.  The first, Table1 contains a set of buckets, with a starting and ending value:

Start   End   Bucket
0.00    0.08    1
0.08    0.16    2
0.16    0.25    3
0.25    0.33    4
0.33    0.41    5
0.41    0.49    6
0.49    0.58    7
0.58    0.66    8
0.66    0.74    9
0.74    0.82    10
0.82    0.90    11
0.90    0.99    12
0.99    1.23    13
1.23    1.48    14
1.48    1.73    15
1.73    1.97    16
1.97    2.22    17
2.22    2.47    18
2.47    2.71    19
2.71    2.96    20
2.96    5.00    21
5.00    10.00   22
10.00   25.00   23
25.00   101.00  24

Table2 contains values that I need assigned to a bucket by matching the My Values column to lie between the Start and End range in Table1:

Table2 where Bucket has been populated with the corresponding Bucket value from Table1:

My values           Bucket
7.416438356         22
7.416438356         22
7.416438356         22
7.416438356         22
0.252054795         4
0.750684932         10
0.504109589         7
0.504109589         7
0.504109589         7
9.690410959         22
0.504109589         7
0.504109589         7
0.706849315         9
0.504109589         7
1.504109589         15
0.567123288         7
0.504109589         7
0.753424658         10
85.56164384         24
1.482191781         15
2.994520548         21
3.594520548         21
99                  24
0.504109589         7

I can populate Bucket for My values column in Excel easily via the VLookup function, but want to be able to do the same using Microsoft Access.

The only solution I can think of is a lot of IIF() functions, but that's messy and not classy.

Try something along the lines of:

UPDATE Table2
INNER JOIN Table1 ON Table2.[My Values] >= Table1.Start AND Table2.[My Values] < Table1.End
SET Table2.Bucket = Table1.Bucket;

Essentially, we get the value from Table1 by comparing the My Values field in Table2 to see if it is between Table1.Start and Table1.End

This will update the entire Table2 in a single statement, and shows the power of set-based approaches to data vs what is referred to as "row by agonizing row" or RBAR.  See https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/ for a great explanation of that.


How to set variables to multiple values (that are integers)? I have 5 facilities in my division, but the code below in a stored procedure only pulls documents when the "facilityID = 4" unless if I change it manually to represent other ID numbers. So I need the facilityID variable below to point to more than one value like:
[Set @FacilityID = 1,2,3,4,5] but of course it's not allowing me. If it was characters, I would be able to put them in quotes. Any advice please?

declare @facilityID tinyint,
@facilitydivisionID tinyint,

SET @facilityID = 4
SET @facilitydivisionID = 6

You can use a table-valued parameter.

CREATE TYPE dbo.Facilities AS TABLE
(
  FacilityID TINYINT PRIMARY KEY
);

Then your stored procedure can say:

CREATE PROCEDURE dbo.whatever
  @FacilityDivisionID TINYINT,
  @Facilities dbo.Facilities READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ... FROM dbo.wherever AS w
    INNER JOIN @Facilities AS f
    ON f.FacilityID = w.FacilityID
  WHERE w.FacilityDivisionID = @FacilityDivisionID;
END
GO

Then you can call it from T-SQL like this:

DECLARE @t dbo.Facilities;
INSERT @t VALUES(1),(2),(3),(4),(5);
DECLARE @d TINYINT = 6;
EXEC dbo.whatever @FacilityDivisionID = @d, @Facilities = @t;

Or from C# using a DataTable and a parameter type as Structured. You can search to find plenty of examples of using TVPs from application code.


The performance of mysql fulltext depend on order of keyword I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result:

FAST (1s)

SELECT *
FROM   table
WHERE  (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE)

SLOW (10s)

SELECT *
FROM   table
WHERE  MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE)

FAST (1s)

SELECT *
FROM   table
WHERE  MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)
AND    (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE))

SLOW (10s)

SELECT *
FROM   table
WHERE  (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE))
AND    (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE))

How can I optimize it?

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE `table`;

Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .


SSMS suddenly starts generating DEFAULT constraints inlined into the CREATE TABLE statements instead of ALTER TABLE I've been using SSMS to generate scripts of the whole database and storing them in the source control.

It's been working fine for many years, but few days ago I noticed that SSMS started generating DEFAULT constraints as a clause inlined into the CREATE TABLE statement instead of adding a separate ALTER TABLE statement.

Here is an example of one table.
This is how the script looked like before:

USE [my db name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConsignmentGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsignmentID] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Notes] [nvarchar](255) NOT NULL,
    CONSTRAINT [PK_ConsignmentGroups] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ConsignmentGroups] ADD  CONSTRAINT [DF_ConsignmentGroups_Notes]  DEFAULT ('') FOR [Notes]
GO

Here DEFAULT constraint is scripted as a separate ALTER TABLE statement.

This is how it looks like now:

USE [my db name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConsignmentGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsignmentID] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Notes] [nvarchar](255) NOT NULL CONSTRAINT [DF_ConsignmentGroups_Notes]  DEFAULT (''),
    CONSTRAINT [PK_ConsignmentGroups] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Here DEFAULT constraint is inlined into the CREATE TABLE statement.

It didn't happen to all tables with DEFAULT constraints. There are about 60 tables with DEFAULT constraints and at least one of them is still scripted as ALTER TABLE statement. I don't think anyone touched the table in the example above (ConsignmentGroups), there were no changes to the table structure. The only change is the style of DEFAULT constraint.

The problem is that this kind of change in the T-SQL script is picked up by the source control as a change. I'd like to know how to control it to avoid it in the future.

I assume that I accidentally changed some setting in SSMS without realising what it does. Does anyone know where this setting could be?

I use SQL Server 2008 Standard

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)
    Sep  3 2014 04:11:34
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

SSMS 2012

Microsoft SQL Server Management Studio     11.0.6020.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML                            3.0 4.0 5.0 6.0
Microsoft Internet Explorer                9.11.9600.18376
Microsoft .NET Framework                   4.0.30319.42000
Operating System                           6.1.7601

and SSMS Boost build 2.19.5952.29737

I don't remember updating any of these components or changing any settings for a while, but apparently something has changed.

I tried to generate these scripts using SSMS 2014 (12.0.4459.0) without SSMSBoost installed on a virtual machine that I don't usually use.

To my surprise the result was the same. The scripts generated by different versions of SSMS running from different computers are identical. This means that it is not a setting in SSMS, there must be something in the database itself.

Also, the fact that one table out of ~60 is scripted differently suggests that this setting is not database-wide, but works on a table level.

This 'might' be the problem, although, there does not appear to be a 'solution'.  I was able to reproduce your issue. See this connect item regarding this problem:

https://connect.microsoft.com/SQLServer/Feedback/Details/895113

  The SQL SMO generates for a table with defaults depends on the rowcount of the table. This makes it impossible to programmatically compare the schema.


Is there an advantage to not partition aligning an index? I have the privilege of managing a large partitioned
OLAP table.  While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme.  Since the author is not available and carefully crafted Google searches have not returned any useful results I am not sure if this was intentional or accidental.

Is there any reason not to partition align an index on SQL Server 2008?

The main advantage of not partitioning a (non-unique) index on a partitioned base object is that it works around a long-standing query optimizer limitation related to ordered data requests such as MIN, MAX, or TOP (n) queries.

On a partitioned index, the optimizer cannot generally translate MIN, MAX, or TOP (n) to the same operation per partition, followed by a final global aggregate over the per-partition partial aggregates. The optimizer instead chooses an execution plan that scans all partitions of the index. The exception to this is the single case where the aggregate or top operation is specified over the partitioning column.

I should mention that there are also very good reasons not to have any non-aligned indexes. Choosing to use a non-aligned index would have to be a very informed choice. I have done it myself (rarely) in the past, but in very specific circumstances where the benefits clearly outweighed the costs, or there was no other reasonable alternative.

Article by Itzik Ben-Gan explaining the issue.


MySQL Grouping Error I'm trying to run a query on a table to see how many unique users have a usage record in the system at a given point. I've been working with the following query, but I've yet to see a proper result.

SELECT count(distinct usageUser), divisionName
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY L.divisionName;

Currently the query returns 18, for each division in the joined table. Without the GROUP BY clause I get the same number of records.

EDIT: I ran the query again, with suggestions from a comment. By removing the group by and count clause, I get this this (too big to post). This data is very poorly formatted, unfortunately it's inherited and fairly large.

It is not possible for these users to have used every lab like it's listed.

This was originally posted to server fault, and was migrated to StackOverFlow.  I was then pointed to here saying this might actually be the most appropriate place for it.

Perhaps you could look for the number of records for each usageUser as well
This query will give the record count for all users per division
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY L.divisionName,R.usageUser;

This query will give the record count for all divisions per user (reversed GROUP BY)
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY R.usageUser,L.divisionName;

Looking back at your original query, if removing the count(distinct usageUser) gave you the same number of rows, my guess would be that 2012-07-08 has either one division or one user being retrieved. Looking back at your original post, you said you inherited this data. Chances are, there is only one division or one user throughout the data.
This may not be a full answer but just two suggested queries to get you to think. I hope this helps...
UPDATE 2013-08-06 12:48 EST
As suggested by @yercube
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00')
GROUP BY L.divisionName,R.usageUser;

This query will give the record count for all divisions per user (reversed GROUP BY)
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00')
GROUP BY R.usageUser,L.divisionName;


Is it possible to edit data inside an unencrypted SQL Server backup file (*.bak) without SSMS? I have found out that there is a difference error (Remaining Qty <> (Purchase - Sales)) in one of the items in my database. There are more than 5000 items and more than 100,000 transactions has happened up to now. Same procedure runs every time for this calculation.
But only a single item has an error and it has happened only one time. It does not seem to be happening from the application side since it has run for more than 100,000 times. The database server is not physically protected and does not connect to the Internet. Only I can log in to the server. No exceptions has happened in the procedure since I log the errors.  I do not understand how the above error happened.
I have seen that it's possible to open unencrypted SQL Server backup files using Notepad and read its data. Is the following scenario possible or not?

Boot the server using a live CD and copy the database .mdf file
Edit and change the data of it (Example: some numeric values)
Replace the original .mdf file on the server with a hacked database

If the above scenario is not possible, is there a chance this has happened due to an error from the SQL Server side or through communication error (not from the application code side).

In my experience, the root cause of these symptoms is most commonly a race condition in the application and/or T-SQL code rather than data corruption due to hardware, network, or malicious activity. Race conditions are insidious because the undesired outcome occurs by happenstance, difficult to reproduce, and may rarely occur.
One must use transactions, along with the appropriate isolation level (or locking hints), to ensure aggregated values like Remaining Qty in the items table reflects the related Purchase and Sales transactions.
EDIT
Testing concurrency problems is a challenge because multiple sessions are required and often many iterations too. Below is an example technique using multiple SSMS query windows I use to reproduce production issues as well as proactively identify suspect code before release. Be aware that race conditions are a matter of timing so even due diligence with testing may not catch rare occurrences.
--example setup script with race condition vulnerability
USE YourDatabase;
DROP TABLE dbo.Transactions;
DROP TABLE dbo.Item;
CREATE TABLE dbo.Item(
      ItemNumber int NOT NULL
          CONSTRAINT PK_Item PRIMARY KEY
    , RemainingQTY int NOT NULL
    );
CREATE TABLE dbo.Transactions(
      TransactionID int NOT NULL IDENTITY
        CONSTRAINT PK_Traansactions PRIMARY KEY
    , ItemNumber int NOT NULL
          CONSTRAINT FK_Traansactions_Item FOREIGN KEY REFERENCES dbo.Item(ItemNumber)
          INDEX idx_Transactions_ItemNumber
    , TransactionType varchar(10) NOT NULL
        CONSTRAINT CK_Transactions_TransactionType CHECK (TransactionType IN('Purchase', 'Sale'))
    , QTY int
);
--load 5000 items
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Item(ItemNumber, RemainingQTY)
SELECT num, 1000
FROM t10k
WHERE num <= 5000;
--load 5000 purchase transactions
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Transactions(ItemNumber, TransactionType, QTY)
SELECT ItemNumber, 'Purchase', 1000
FROM dbo.Item;
GO

CREATE OR ALTER PROC dbo.usp_InsertTransaction
      @ItemNumber int
    , @TransactionType varchar(10)
    , @QTY int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @RemainingQTY int;
BEGIN TRY

    BEGIN TRAN;

    SELECT @RemainingQTY = RemainingQTY
    FROM dbo.Item
    WHERE ItemNumber = @ItemNumber;

    IF @TransactionType = 'Purchase'
        SET @RemainingQTY = @RemainingQTY + @QTY
    ELSE
        SET @RemainingQTY = @RemainingQTY - @QTY;

    UPDATE dbo.Item
    SET RemainingQTY = @RemainingQTY
    WHERE ItemNumber = @ItemNumber;

    INSERT INTO dbo.Transactions VALUES
        (@ItemNumber, @TransactionType, @QTY);

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH
GO

Open 3 SSMS query windows and run these scripts as instructed:
--Step 1: Run script in SSMS query window 1 to acquire exclusive lock to sync start of test executions
DECLARE @return_code int;
EXEC @return_code = sp_getapplock
     @Resource = 'concurrency_test'
    ,@LockMode = 'exclusive'
    ,@LockOwner = 'session';
RAISERROR('sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO

--Step 2: Run this script in SSMS query windows 2 and 3 to acquire a shared lock on same resource as session 1.
--These will block until session 1 lock is released to start test.
DECLARE @return_code int;
EXEC @return_code = sp_getapplock
     @Resource = 'concurrency_test'
    ,@LockMode = 'shared'
    ,@LockOwner = 'session';
RAISERROR('sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO
--execute queries to insert transactions and update RemainingQTY 100 times
DECLARE @IterationCount int = 0;
WHILE @IterationCount < 100
BEGIN
    EXEC dbo.usp_InsertTransaction
          @ItemNumber = 1000
        , @TransactionType = 'Sale'
        , @QTY = 1;
    SET @IterationCount +=  1;
END;
GO
--release lock after test completes
DECLARE @return_code int;
EXEC @return_code = sp_releaseapplock
     @Resource = 'concurrency_test'
    ,@LockOwner = 'session';
RAISERROR('sp_releaseapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO

--Step 3: Run script in SSMS query window 1 to release lock to start test
DECLARE @return_code int;
EXEC @return_code = sp_releaseapplock
     @Resource = 'concurrency_test'
    ,@LockOwner = 'session';
RAISERROR('sp_releaseapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO

--Step 4: Run this script to validate RemainingQTY after test completes
WITH transaction_summary AS (
    SELECT
          t.ItemNumber
        , SUM(CASE WHEN t.TransactionType = 'Purchase' THEN t.QTY END) AS Purchase
        , SUM(CASE WHEN t.TransactionType = 'Sale' THEN t.QTY END) AS Sale
    FROM dbo.Transactions AS t
    WHERE t.ItemNumber = 1000
    GROUP BY t.ItemNumber
)
SELECT
      i.ItemNumber
    , i.RemainingQTY AS ItemRemainingQTY
    , t.Purchase
    , t.Sale
    , t.Purchase - t.Sale AS ActualTramsactionsRemainingQTY
FROM dbo.Item AS i
JOIN transaction_summary AS t
    ON t.ItemNumber = i.ItemNumber
WHERE i.RemainingQTY <> t.Purchase - t.Sale;
GO

Below is the output of the step 4 validation query after a test on my box that shows item RemainingQTY is invalid. This happened 31 times of 200  total proc calls on my test machine. The cause is different sessions both executed the SELECT query at about the same time and read the same RemainingQTY value. One session reduced the RemainingQTY as desired but the value was subsequently overwritten by the other session based on stale data. This test ran in a tight loop so it's more likely to occur than in a common prod workload.

ItemNumber
ItemRemainingQTY
Purchase
Sale
ActualTramsactionsRemainingQTY

1000
831
1000
200
800

One way to fix this code bug is to refactor the proc to avoid the local variable. This will serialize updates to the item row and perform slightly better too by eliminating the SELECT query:
CREATE OR ALTER PROC dbo.usp_InsertTransaction
      @ItemNumber int
    , @TransactionType varchar(10)
    , @QTY int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @RemainingQTY int;
BEGIN TRY

    BEGIN TRAN;

    UPDATE dbo.Item
    SET RemainingQTY = RemainingQTY +
        CASE @TransactionType WHEN 'Purchase' THEN @RemainingQTY ELSE @RemainingQTY * -1 END
    WHERE ItemNumber = @ItemNumber;

    INSERT INTO dbo.Transactions VALUES
        (@ItemNumber, @TransactionType, @QTY);

    COMMIT;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH
GO


Finding Columns causing errors in SQL Developer I'm relatively new to Oracle SQL and trying to import CSVs using SQL Developer. I've been getting errors such as

Insert failed for rows 1351 through 1400
ORA-01438: value larger than specified precision allowed for this column

Is there a way to determine the Columns that this error message concerns (?), so I can examine the values in the CSVs to see what is causing the error and how I should change the DDL.
Thanks!

Set the preview window higher, default is 100 rows.

Then we can validate more during the preview.

You can scroll the preview window's worth of data, and each column value for each row will get validated via the column definition in the data.

Or, set the import type to insert script, and run the script in a worksheet.


Grant access on a database to all users I have a global database, as I all users should have access to it. Is it possible to grant permission on a database to all users? something like

GRANT ALL ON my_database.* TO *@localhost;

Even (for security reason), it can be read permission only.

Yes, this is possible, though not recommended. You can create a user with an empty string as username, also known as the anonymous user. It matches any username. This is the only possibility, wildcards like in the hostname (i.e. 'user'@'%.any.domain.de') are not allowed.

GRANT ALL ON my_database.* TO ''@'localhost';


How does unnest() treat NULL values and empty arrays? I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted to pass fake array with single null element if the source array is empty, but it does not work as expected:

Query 1

select element
from (
  select array['a']::text[] as arr --< single non-null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
    else sub.arr
    end
  )
) element
-- returns 1 row with element = "a"

Query 2

select element
from (
  select array[]::text[] as arr --< empty array
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
    else sub.arr
    end
  )
) element
-- returns 0 rows (should return 1 row with element = NULL?)

Query 3

select element
from (
  select array[null]::text[] as arr --< single null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
    else sub.arr
    end
  )
) element
-- return single row with element = NULL

Just figured out that select array_length(array[]::text[], 1) returns NULL - my question is why?

To quote Tom Lane in the thread "Should array_length() Return NULL" on pgsql-hackers:

The thing is that that syntax creates an array of zero dimensions, not
one that has 1 dimension and zero elements.  So "0" would be incorrect.

There is a related TODO item in the Postgres Wiki.
Hard to say whether array_length() should return NULL or 0 for an empty array dimension ...
Solution
You could invert the logic and process the original source array only if the length is >= 1:
WITH tbl(id, arr) AS (
   VALUES
     (1, '{a}'::text[])
   , (2, '{NULL}'::text[])
   , (3, '{}'::text[])
   , (4, '{x,y,z}'::text[])
   )
SELECT id, elem
FROM   tbl t
     , unnest (
          CASE WHEN array_length(t.arr, 1) >= 1
               THEN t.arr
               ELSE '{null}'::text[] END
       ) elem;

Returns at least one row for each source row.
Or, much simpler, use an actual LEFT JOIN (the comma in your FROM clause is short notation for CROSS JOIN):
SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;

This fills in NULL values automatically for columns of the right table if no match is found. Exactly what you are trying to achieve.
Related:

What is the difference between LATERAL and a subquery in PostgreSQL?

db<>fiddle here


Can SQL Server Change Data capture (CDC) log rows to an external DB? I have not used SQL Server CDC before, but to my understanding it works like this:

business data changes in source DB, e.g. SQL Server
CDC has been configured to track certain table/column changes, which it reads asynchronously from business data server transactional logs

I have two question:
1. Can CDC be triggered to store e.g. some DB rows to an external DB, or do any eventual database rows that are being created from the transaction logs, need to be stored back to the source DB?
2. Can CDC read from the business DB transactional logs and create new rows to a NoSQL DB?

Thanks!

CDC is just the component that exposes the changes. It is strictly tied to the host database where the changes occur. CDC is useless without an application you provide which consumes the changes. This application of yours can do anything with the changes, including storing them anywhere you fancy. You just have to implement this application.


MariaDB order by before Group by I have looked around at other solutions, but none seems to work quite right.
What I am trying to do is to get the latest result (sorted by date) from a selection, grouped together by sample.

Here is my partial ERD (excuse the ascii):

                  +--------+
                  | sample |
                  +--------+        +--------+
+--------+      ->| ID     |        | batch  |
| result |     /  | text   |        +--------+
+--------+    /   | batch  |------->| ID     |
| ID     |   /    +--------+        | text   |
| text   |  /                       +--------+
| sample |--      +--------+
| test   |---     | test   |
| date   |   \    +--------+
+--------+    --->| ID     |
                  | text   |
                  +--------+

And the DDL:

create table result (resultid int(11), resulttext varchar(20), resultsample int(11), resulttest int(11), resultdate datetime);
create table sample (sampleid int(11), sampletext varchar(20), samplebatch int(11));
create table batch (batchid int(11), batchtext varchar(20));
create table test (testid int(11), testtext varchar(20));

insert into batch(batchid, batchtext) values(12759, 'batch 12759');
insert into sample(sampleid, sampletext, samplebatch) values(99787, 'sample 99787', 12759);
insert into sample(sampleid, sampletext, samplebatch) values(99786, 'sample 99786', 12759);
insert into test(testid, testtext) values(2144, 'test 2144');
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(1, "Pass", 99787, 2144, "2018-02-01 11:02:18");
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(2, "Fail", 99787, 2144, "2018-02-01 09:01:34");
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(3, "Pass", 99786, 2144, "2018-02-01 08:06:12");

Here is an SQL query (just to see whats there):

select
    resulttext,
    resultdate,
    sampleid
from
    batch Right Join(
        sample Right Join(
            test Right Join
            result On testid = resulttest
        ) On sampleid = resultsample
    ) On batchid = samplebatch
where
    batchid = 12759 and
    testid  = 2144
order by
    resultdate desc

Here is an example of my result set (from the above query):

resulttext resultdate          sampleid
---------- ----------          ---------
Pass       2018-02-01 11:02:18 99787
Fail       2018-02-01 09:01:34 99787
Pass       2018-02-01 08:06:12 99786

So I wish to sort on resultdate (get the latest one) before grouping by sampleid. My query is currently:

select
    *
from (
    select
        resulttext,
        resultdate,
        sampleid,
        batchid,
        testid
    from
        batch Right Join(
            sample Right Join(
                test Right Join
                result On testid = resulttest
            ) On sampleid = resultsample
        ) On batchid = samplebatch
    order by
        resultdate desc
) x
where
    batchid = 12759 and
    testid  = 2144
group by
    sampleid

But the result set I get is incorrect:

resulttext resultdate          sampleid
---------- ----------          ---------
Pass       2018-02-01 08:06:12 99786
Fail       2018-02-01 09:01:34 99787

It should have got the 11:02 result (where sampleid=99787), but it gets the wrong one. Can someone help with my query?

I am using MariaDB 15.1

Use something like

SELECT result.text, result.date, sample.ID
FROM       ( SELECT sample, MAX(date) date
             FROM result
             GROUP BY sample) maxtime
INNER JOIN result  ON result.sample=maxtime.sample
                  AND result.date=maxtime.date
LEFT JOIN  test ON test.ID = result.test
LEFT JOIN  sample ON sample.ID = result.sample
LEFT JOIN  batch ON batch.ID = sample.batch
WHERE batch.ID = 12759
  AND test.ID  = 2144


How do you normalize a table that is indirectly dependent on a has-many relationship? I currently have the following database design:

Product: contains various description attributes

Belongs to many ProductSets

ProductSet:

Has many Products
Belongs to many Stores

Store:

Has one ProductSet
Must, through some set of relationships, specify the price for each product in its product set

I'm specifically struggling with the best way to incorporate the final bullet in a normalized way. So far, the only solution I've come up with is to have a join table between Store and Product, but this doesn't guarantee that the store has prices for all of (and only) the products in its ProductSet. What (if any) is the best way to accomplish this?

You can create a table ProductPrice:

CREATE TABLE ProductPrice (
  ProductID int NOT NULL,
  ProductSetID int NOT NULL,
  StoreID int NOT NULL,
  Price numeric(10,2) NOT NULL,
  PRIMARY KEY (ProductID, ProductSetID, StoreID),
  FOREIGN KEY (ProductID, ProductSetID) REFERENCES ProductProductSet,
  FOREIGN KEY (StoreID, ProductSetID) REFERENCES Store
)

ProductProductSet beging the many-to-many relationship between Product and ProductSet.

These two foreign keys would ensure that the price refers to a store that is intended to have such product. Then you can create a query to find out which prices are missing:

SELECT s.*, p.*
FROM Product p
JOIN ProductProductSet pps ON (pps.ProductID = p.ProductID)
JOIN Store s ON (s.ProductSetID = pps.ProductSetID)
LEFT JOIN ProductPrice pp ON (pp.ProductID = p.ProductID AND pp.StoreID = s.StoreID)
WHERE pp.ProductID IS NULL


Scheduling duplication of two tables across instances In my SQL Server 2012 database I have two tables:

[tbl_Customers] - about 30 rows
[tbl_Users] - about 10,000 rows

I need to schedule the two tables above to be copied to a new instance, say every 12 hours. I am considering setting up a SQL Azure account to act as the second instance.

[tbl_Users] is a popularly-accessed table, with CRUD operations being performed quite a lot, therefore the data changes often.  It doesn't matter too much about those changes being replicated on the second instance immediately (but it would be nice if possible if the option allows).

Can anyone recommend which approach they might take to achieve this?  I'm new to this type of thing so please don't hold back with the simplicity of your answers...!

I have SQL2012 Standard on my dev machine (but no SSIS at the moment although I could install it), but don't know if my existing SQL host and/or Microsoft have SSIS on their offerings.

As long as all the changes happen only on your first instance, transactional replication is what you are looking for. It was designed for this purpose and usually operates close to realtime.

You can find an introduction to replication here: http://www.sqlservercentral.com/stairway/72401/

If you need to be able to apply changes on both machines, merge replication might be the better option, but is was not designed for realtime operations. The above link has an introduction to merge replication too.


Create/copy new superuser from an existing one I have a superuser for a PostgreSQL database, let's call him userX.

How can I make a new superuser (let's call him userY) with all the same permissions, access, etc...?

Basically, I want a clone of everything about userX to userY and keep both users.

Well, if you have an existing superuser role called "super1", you could do:

CREATE ROLE super2 WITH SUPERUSER LOGIN;
GRANT super1 TO super2;

And, in theory at least, any of super1's privileges would be transferred over to super2. However, I'm not sure your question makes much sense, since superusers generally are allowed to override any privilege checks. As the documentation explains:

  It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary.

I'd be interested to hear what privileges your existing superuser role had which were not automatically granted to whatever new superuser role you created by default.

Edit: and if you're interested in copying over the per-role configuration parameters (i.e. those documented under configuration parameters), then you could use a function like this (demo only, you may need extra error handling, security considerations, etc. for production use):

CREATE OR REPLACE FUNCTION copy_role_configs(source_role text, target_role text)
RETURNS VOID AS
$$
DECLARE
  setconfig_val text;
  eq_pos int;
  i int = 1;
BEGIN

  LOOP

    SELECT setconfig[i] INTO setconfig_val
    FROM pg_db_role_setting WHERE
      setdatabase = 0 AND setrole =
      (SELECT oid FROM pg_authid WHERE rolname = source_role);

    EXIT WHEN setconfig_val IS NULL;

    RAISE NOTICE 'Copying config % from % to %', setconfig_val, source_role,
                  target_role;
    SELECT strpos(setconfig_val, '=') INTO eq_pos;

    EXECUTE 'ALTER ROLE ' || target_role || ' SET ' ||
               substr(setconfig_val, 0, eq_pos + 1) || '''' ||
               substr(setconfig_val, eq_pos + 1) || '''' ;

    i := i + 1;

  END LOOP;

  RETURN;
END;
$$ LANGUAGE plpgsql strict;

-- Now call SELECT copy_role_configs('role1', 'role2') to copy configuration
-- parameters from role1 to role2.


Faster alternative to count value from 2 queries and select with WITH I have a query which I have to make it run faster.
This is my best shot.
Any recommendations that I can improve this?

WITH cm
     AS (SELECT Count(*) AS count_messages
         FROM   messages AS m
         WHERE  EXISTS (SELECT 1
                        FROM   order_items AS o
                               inner join orders ox
                                       ON ox.order_id = o.order_id
                        WHERE  m.product_id = o.product_id
                               AND m.message_type = 0
                               AND m.seller_id IN( 3, 2, 6, 1, 9 )
                               AND current_date >= ox.order_creation_date)),
     co
     AS (SELECT Count(*) AS count_order_items
         FROM   order_items AS o
         WHERE  EXISTS (SELECT 1
                        FROM   messages AS m
                               inner join orders oy
                                       ON oy.order_id = o.order_id
                        WHERE  m.product_id = o.product_id
                               AND m.message_type = 0
                               AND m.seller_id IN( 3, 2, 6, 1, 9 )
                               AND current_date >= oy.order_creation_date))
SELECT cm.count_messages :: DECIMAL / co.count_order_items :: DECIMAL * 100 AS
       result
FROM   cm,
       co

It's a bit tricky to do query rewriting without knowing any keys etc, but if I'm not mistaken cm can be rewritten as:

SELECT Count(distinct m.message_id) AS count_messages
FROM   messages AS m
JOIN   order_items AS o
    ON m.product_id = o.product_id
JOIN   orders ox
    ON ox.order_id = o.order_id
WHERE m.message_type = 0
  AND m.seller_id IN( 3, 2, 6, 1, 9 )
  AND current_date >= ox.order_creation_date

using the same technique and the fact that A |x| B <=> B |x| A give us:

SELECT Count(distinct o.order_item_id) AS count_order_items
FROM   messages AS m
JOIN   order_items AS o
    ON m.product_id = o.product_id
JOIN   orders ox
    ON ox.order_id = o.order_id
WHERE m.message_type = 0
  AND m.seller_id IN( 3, 2, 6, 1, 9 )
  AND current_date >= ox.order_creation_date

for co. This can be combined to:

SELECT 100*Count(distinct m.message_id) :: Decimal
         / Count(distinct o.order_item_id) :: Decimal
FROM   messages AS m
JOIN   order_items AS o
    ON m.product_id = o.product_id
JOIN   orders ox
    ON ox.order_id = o.order_id
WHERE m.message_type = 0
  AND m.seller_id IN( 3, 2, 6, 1, 9 )
  AND current_date >= ox.order_creation_date


Best practice - Update Primary Keys What's you opinion about Update a primary Key in SQL?

I have a "link" table with 3 key from 3 other tables. Should the administration let people to edit one of the keys?

I usely said no, dont change PK. Add a new line and if you want remove the other line.

sample:

Table A : idA Int
Table B : idB Int
Table C : idC Int

Table Link : PK { idA, idB, idC }

UPDATE [Link] SET idA=@NewIdA

Once a record is created with a primary key, you really shouldn't be updating the primary key.  The point of a PK is that it uniquely identifies that row.  That's why you should be careful when creating a PK with a composite key (multiple fields that comprise the PK), because if those fields need to change, then you're changing your PK and any depending records need to change as well.

Now in a table that has a Foreign Key to another table, it's ok to update the FK if/when necessary.

So really, when the data needs to change, create the PK on an auto-increment field and let people modify the rest of the data, or delete the entire record when necessary.


Is there a performance difference in the 2 temp table initializations? I've recently come across a interesting practice where I work. I've noticed that some developers use the following way to initialize a sql server temp table:

if object_id('tempdb..#TempTbl','u') is not null
drop table #TempTbl

Other teammates will use:

if object_id('tempdb..#TempTbl') is not null
drop table #TempTbl

My questions:

Is there a performance difference between the 2 initializations?
What purpose does the ,'u' piece of code serve in the first statement?

I've tried researching msdn to get more information and it doesn't seem like either statement has a real difference. They produce the same results, but I'm curious about performance or other related factors.

Is there a performance difference between the 2 initializations?

No. There is no performance difference. If you use set statistics time, io ON and check there is no CPU or IO involved

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

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

  What purpose does the ,'u' piece of code serve in the first statement?

The syntax for object_id is

  OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
        object_name' [ ,'object_type' ] )

The u specifies its a user object. It is called user defined table in sys.objects

I think it is good coding practice to define what you are doing. So in the 1st example the developer is saying that if #TempTbl table exists then drop it. I have seen poor naming where people name views like tables, do not use schema prefix and other bad habits !

In SQL server 2016, you will see a new syntax ..

DROP TABLE IF EXISTS


Foreign key constraints failures Given the following tables:

CREATE TABLE `longdescs` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` mediumint(9) NOT NULL,
  `who` mediumint(9) NOT NULL,
  `bug_when` datetime NOT NULL,
  `work_time` decimal(7,2) NOT NULL DEFAULT '0.00',
  `thetext` mediumtext NOT NULL,
  `isprivate` tinyint(4) NOT NULL DEFAULT '0',
  `already_wrapped` tinyint(4) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `extra_data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `longdescs_bug_id_idx` (`bug_id`),
  KEY `longdescs_who_idx` (`who`,`bug_id`),
  KEY `longdescs_bug_when_idx` (`bug_when`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `fk_longdescs_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_longdescs_who_profiles_userid` FOREIGN KEY (`who`) REFERENCES `profiles` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82727 DEFAULT CHARSET=utf8

CREATE TABLE `trace` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `comment_id` mediumint(9) NOT NULL,
  `type` varchar(255) NOT NULL,
  `short_hash` char(22) DEFAULT NULL,
  `stack_hash` char(22) DEFAULT NULL,
  `trace_text` mediumtext NOT NULL,
  `quality` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trace_comment_id_idx` (`comment_id`),
  KEY `trace_short_hash_idx` (`short_hash`),
  KEY `trace_stack_hash_idx` (`stack_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

ALTER TABLE trace ADD CONSTRAINT `fk_trace_comment_id_longdescs_comment_id` FOREIGN KEY (`comment_id`) REFERENCES `longdescs`(`comment_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)

MariaDB [bugs4]> show warnings;

| Level  | Code | Message |
| Warning |  150 | Create table 'bugs4/#sql-4bb_6f1d' with foreign key     constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)                                                                                |

Any idea why I get that error? FYI I looked it up and it appears that it was a bug but not anymore.

http://bugs.mysql.com/bug.php?id=16290

It looks like your data types don't match. In order to create a foriegn key between two tables the data types must be the same. You have comment_id in the longdesc table as INT(11) and comment_id in the trace table as MEDIUMINT(9).

I hope this helps you.


I cannot do anything on replSet when auth is enabled I have a mongodb cluster with one replSet, 3 config servers, and 2 mongos instances. I logged into mongos using (admin, pwd). admin is given ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]. It works well. but when I logged into the replSet directly. I cannot pass authentication using (admin, pwd) again. I found that mongodb culster, replset, config servers, and mogos all have admin databases. They are different. What are the difference? what are the differences when doing authentication?

Can I create a (admin, pwd) in the admin database of the replSet? Will I have trouble when I add new replSet?

Thanks, Best,

When I created admin users against mongos, I also created admin users for replication Set. Now I can administrate shards, config server, and mongos now.

Different shards can have different users in their admin databases. If keyfile is uique, new shard (running with auth and keyfile enabled) can be added to the running cluster.


Problems with sqlcmd -q and quotations marks I have a SQL Server 2008 R2 Express edition. Because it is only an Express edition I don't have a SQL Server Agent for automation of tasks.

So I tried to do something with command line program sqlcmd.

I try to execute this command:

use DB; EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";

If I do this in SQL Server Management Studio everything works fine. But if I try to execute this via sqlcmd it doesn't work.

sqlcmd -S .\INSTANCE -Q "use DB; EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";" -o C:\FOLDER\LOG_DB_REINDEX.txt

I think this is because of the quotation marks. I also tried to use ` and ' but I didn't get it to work...

How can I resolve this issue?

Try to use this. ;-)

use DB; EXEC sp_MSforeachtable @command1=N'print ''?'' DBCC DBREINDEX (''?'', '' '', 80) ';

Double quotation marks will escape the following quotation mark.

Here an example of sqlcmd:

sqlcmd -S YOURSERVER -Q "use net_temp_test; EXEC sp_MSforeachtable @command1=N'print ''?''';"


Trouble backing up remote database from local server I have 2 servers LOCAL and REMOTE. REMOTE has a database 'remotedb'.

From REMOTE the following statement backs up the database just fine.

BACKUP DATABASE [remotedb] TO  DISK = N'C:\Temp\remotedb.bak'

Now I want to issue the backup from LOCAL. From LOCAL I create a linked server to REMOTE.

exec sp_addlinkedserver 'REMOTE'

From LOCAL I issue the same statement...

BACKUP DATABASE [remotedb] TO  DISK = N'C:\Temp\remotedb.bak'

I get the following error message...

Msg 911, Level 16, State 11, Line 3
Database 'remotedb' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

I am SYSADMIN on both SQL Servers. Can someone tell me why I am not able to issue a backup command from LOCAL for remotedb and what can I do to make this work?

Much thanks!

As is the command is trying to execute on your LOCAL instance and not REMOTE. You need to have it execute through the linked server itself. You can build the query as a dynamic statement and then simply execute the query against sp_executesql on the linked server. So something like this should:

DECLARE @qry varchar(2000)
SET @qry = 'BACKUP DATABASE [remotedb] TO  DISK = N''C:\Temp\remotedb.bak'''
EXEC REMOTE.master.dbo.sp_executesql @qry


Is it secure to set default value for password field? I'm looking at an application where admins can "invite" users by email and invited users. Invited users then are sent a unique URL and are inserted into a MySQL database with status pending and an activation token.

However, the application has a bug currently, where the DB was set up with the password field NOT NULL, and without default value, which makes INSERTs fail.

If I modify my table structure and set a default value for the password (using some arbitrary encrypted value), admins can add new users and these users are prompted to set a password prior to first login.

My question is - is there a security risk here that I should be aware of?

I see only 2 options to get it to work :

Application inserts value for password field
Table has NULL or default value for password field

If the column allows NULL, that should be enough, right?
If you want it to have a value, having the application insert it, across the network, is probably riskier, than setting a default value in the table.

Setting the same default value for all users may also pose a slight risk (someone gets a hold of the hash and tries to reverse-engineer). If you want to avoid that too, you can use a BEFORE trigger to set a computed default value based on username+timestamp as salt for the encrypted password field.


mysql selecting a distinct summed column while having I have a one-to-many relationship between two tables, where I need to get distinct values out of a summed column b while limited by another summed field a

SELECT
    t1.a - sum(t2.a) as a,
    t1.b - sum(t2.b) as b
FROM
    t1 inner join t2 using(id)
GROUP BY
    t1.id
HAVING
    a > 0
ORDER BY
    b ASC
LIMIT
    15

Is there any other way then bagging the whole query in another SELECT disctint b (...) as t ?

If I understand correctly and you only want DISTINCT b values, you just need to remove the a column from the SELECT list and use SELECT DISTINCT:

SELECT DISTINCT
    t1.b - sum(t2.b) AS b
FROM
    t1 INNER JOIN t2 USING (id)
GROUP BY
    t1.id, t1.a, t1.b
HAVING
    t1.a - sum(t2.a) > 0
ORDER BY
    b ASC
LIMIT
    15 ;


Calculation Not Displaying Proper Answer I am sure this results from an incorrect parenthesis on my part or something silly like such, but for the life of me I can not get this syntax accurate.  Here is sample DDL

Declare @Hold Table
(
val1 float
,val2 float
,val3 float
,val4 float
,val5 float
,val6 float
,val7 float
)

 Insert Into @Hold values
 (145, 10, 20, 30, 40, 50, 60)

 Select
 (val1-(val2+val3+val4+val5+val6+(val7*.22)))/val1
 FROM @Hold

So for a breakdown of what I am after with the equation:

val1 = 145
val2+val3+val4+val5+val6 = 150
val7 = 60
val7 * .22 = 13

Which takes us to this equation:

145-(150)+13/145
8/145

My desired output is
.055172 or 6%

However my code above produces this as the answer:

-0.12551724137931

What do I need to alter in order to get my above %?

In addition to correcting the parentheses, if you want your output to be .055172 then you should cast (val7*.22) as an integer in your equation.

http://rextester.com/SQP94408

Declare @Hold Table (val1 float,val2 float,val3 float,val4 float,val5 float,val6 float,val7 float)
Insert Into @Hold values (145, 10, 20, 30, 40, 50, 60)
Select
 (
   (val1)
  -(val2+val3+val4+val5+val6)
  + (cast((val7*.22) as int)) -- this results in 13
  )  /val1
FROM @Hold
/* results in 0.0551724137931034 */

Select
 (
   (val1)
  -(val2+val3+val4+val5+val6)
  +(val7*.22)  -- this results in 13.20 not 13
  )  /val1
FROM @Hold

/* results in 0.056551724137931 */


Merge 2 tables and fetch data one by one I have two tables: product & offers.

products has (prod_id, category_id, prod_name, brand, price, status)
offers has (offer_id, prod_id, offer_type, discount_amt, validity)

I want to display products on a page, 20 products at a time.

The problem that I want to display all the products from the offers table arranged by descending offer_id and then display products from the products table except those in the offers table within that 20 limit.

How can I achieve that?

Seems pretty straightforward.

SELECT * from products left join offers on prod_id=prod_id
ORDER BY offer_id DESC LIMIT 20;

See it here:  http://sqlfiddle.com/#!2/dff49/3/0


Ideal database to store/query data submitted through dynamic user created forms I am designing a web application that allows administrators to create public facing forms. The fields that can be included in the forms are anything from text fields to radio buttons.

I need a solution that allows the users to filter through form submissions.

I have 2 solutions in mind:

A document database such as MongoDB to store submitted form data
Store submitted data in a MySQL JSON data type

Also, what if the form is edited and fields are changed?

Storing the submissions is easy. Any persistence software with sufficient bandwidth will do. Heck, even the OS's native file system will do. From this perspective JSON in MySQL is excessive as you have the DBMS overhead.

Similarly with an update. The system need only overwrite the current submission with the new one.

The real constraint on your design will be on retrieval. You use the word "filter." I presume you want to see all submissions with a given value in a particular form field. To do that efficiently will require indexes. Capability in this area varies widely across the various NoSQL and relational products. You must learn what each can provide, gain a good understanding of your requirements, and choose.

If you adopt a relational store you will likely gain a lot of advantage by shredding the JSON into table(s). This is how an RDBMS is meant to work. It may be difficult to settle on a schema, however, if there is great variabilty in your forms.

There may be some advantage to holding, in addition to the submissions, a separate list (document / table) for each answer and pointing to all submissions which contain that value. This may become unworkable at scale because there will be a document keyed by answer holding an array of submission IDs. When any of those submissions is updated this document will also have to be updated. This will be a bottleneck when there are a large number of updates per second. It will require careful programming to keep the submission document and answer cross-reference document in sync during updates since Mongo does not have transactions that span documents.


Where is the SQLS database backup virtual device string stored in the system tables? In my SQLS Server 2005 log, when backups occur, I see something like:

Database backed up. Database: blahblah, creation date(time): 2011/08/11(15:49:19), pages     dumped: 1, first LSN: 142:489:1, last LSN: 142:491:1,
number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9A325509-4285-4C8A-E1B2-2F4815C4C4DF}15'}). This is an informational message only. No user action is required.

Regarding that virtual device specification, {9A325509-4285-4C8A-E1B2-2F4815C4C4DF}15'}:

In what system table is that virtual device string?  If it matters, the reason I ask is I have a query regarding latest backups and need to filter based on the virtual device.

I have looked in all of the obvious places, like:

msdb.dbo.backupmediaset
msdb.dbo.backupmediafamily
msdb.dbo.backupfile
msdb.dbo.backupfilegroup
sys.backup_devices

but no luck.  If it is indeed stored somewhere, does the location change between SQL versions?

Any help appreciated.

TIA

dbaman

This is old, but I had the same issue.  My SQL logs and backup tables were indicating that backups were happening, but even when I used the vssadmin command above, I could not locate the backup files.
I listed the writers, but none matched the long string in the SQL logs, so I tried some of the other "vssadmin list" commands:

---- Commands Supported ----

List Providers        - List registered volume shadow copy providers

List Shadows          - List existing volume shadow copies

List ShadowStorage    - List volume shadow copy storage associations

List Volumes          - List volumes eligible for shadow copies

List Writers          - List subscribed volume shadow copy writers

"List Providers" turned out to be the golden nugget.  Although nothing was returned for existing copies or storage associations, the Providers listed the info I needed - it named my SAN provider.  I looked in my storage tool and saw it was using Microsoft VSS, and the snapshot times correlated with what was in my SQL log.

Three things of note:

nowhere could I find anything to correlate to the  TYPE=VIRTUAL_DEVICE: number listed in my SQL Logs (even though there were plenty of guids all over)
"List Shadows" did not return anything, which was scary until I looked at my storage tool and saw the snapshots there.
My DBs are all currently in "SIMPLE" recovery model, so I never have to do anything but attach database mdf/ldf files from the snap.  This would be more complicated with databases that need to live in full recovery mode.

HTH future searchers - this has been bugging me forever.  Where were my backups?


Import CSV file - dynamic path I am trying to import a csv file dynamically.

This works:

BULK
INSERT #CSV
FROM 'C:\Users\MyName\Desktop\Players.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

I would like to change it to be with a @path parameter like that:

DECLARE @path NVARCHAR(4000) = 'C:\Users\MyName\Desktop\Players.csv';

BULK
INSERT #CSV
FROM @path
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

But this does not compiles.

How could I do that?

Thanks in advance.

You would need to use Dynamic SQL to pass a dynamic file path to the Bulk Insert.

DECLARE @FileName NVARCHAR(4000);

SET @FileName = '/path/to.csv';

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #CSV
        FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';

EXEC(@sql);


Does a TRIGGER improve the performance? I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too?

Replacing two or three queries as

INSERT INTO table1 ...
UPDATE table2 ...
UPDATE table3 ...

with a trigger-based query as

INSERT INTO table 1

with trigger

CREATE TRIGGER test
AFTER INSERT ON table1
FOR EACH ROW BEGIN
UPDATE table2 ... WHERE id=NEW.id;
UPDATE table3 ... WHERE id=NEW.id;
END

Do we still have the same three queries with identical performance? I mean does it matter if we perform the subsequent queries or the TRIGGER does?
What if we use an API (like conducting queries through PHP). The subsequent queries are internal with no need to connector/driver. Does the TRIGGER improve the performance?

Additional Information: The database is mysql 5.5 with innoDB.

Using a trigger will not affect the execution speed significantly -- in the end, the database system is executing the same operations.

But if you are sure that every insert mandates the updates then triggers are a great way to ensure database integrity, so by all means do it.


SQLite - Counting referenced rows in one table using a different, related self-referencing table media_subscriptions
+----+--------------------------+
| id | target_metadata_item_id  |
+----+--------------------------+
| 30 | 19039                    |
| 31 | NULL                     |
| 32 | NULL                     |
| 33 | NULL                     |
| 34 | NULL                     |
| 35 | NULL                     |
| 36 | NULL                     |
| 37 | 18976                    |
+----+--------------------------+

metadata_items
+-------+-----------------+-----------+
| id    | guid            | parent_id |
+-------+-----------------+-----------+
| 18976 | id://265        | NULL      |
| 18977 | id://265/1      | 18976     |
| 19039 | id://841        | NULL      |
| 19040 | id://841/1      | 19039     |
| 19041 | id://841/1/1    | 19040     |
| 19224 | id://841/1/2    | 19040     |
| 19966 | id://265/1/1    | 18977     |
| 19967 | id://290        | NULL      |
| 19968 | id://376        | NULL      |
+-------+-----------------+-----------+

I have the above two tables with data. The parent_id column defines how each row in metadata_items relates to any other row, which is also evidential by each row's guid. The parent_id can be nested by a root ID having a child, and that child having a child (if that makes sense). The target_metadata_item_id column from media_subscriptions relates to the id column of the metadata_items table. I want to get a COUNT(*) of any records from the metadata_items table that have an associated record in media_subscriptions based on a search of guid. Ultimately, if there is a matching record, the count should only ever be 1, and 0 if not, but I'm using COUNT(*) just in case there's ever a duplicate record in the media_subscriptions table, and then I'm just checking elsewhere if it returns > 0.

Some examples of what I'm trying to accomplish:

guid='id://841' would return 1 because its root parent id (19039) is referenced by id 30 in media_subscriptions.
guid='id://841/1/1' would return 1 because its root parent id (19039) is referenced by id 30 in media_subscriptions.
guid='id://290' would return 0 because its root parent id (19967) is NOT referenced by ANY column in media_subscriptions.

Here's what I've got right now, which obviously isn't working as I'd intend (take it easy on me, I'm a noob with database administration, and am just starting to learn).

SELECT COUNT(*) FROM `metadata_items` AS `t1`
 LEFT JOIN `metadata_items` AS `t2` ON t1.id = t2.parent_id
 INNER JOIN `media_subscriptions` AS `subscriptions` ON t1.id = subscriptions.target_metadata_item_id
 WHERE t1.guid='id://841'

This correctly returns 1, but if I switch out the last line with WHERE t1.guid='id://265/1/1' it returns 0 where I'd like it to return 1. Obviously I'm doing this very incorrectly, but I'm really not sure how to go about getting where I need to be.

Test

WITH RECURSIVE cte AS
(
SELECT id, guid, parent_id, id _id_, guid _guid_, parent_id _parent_id_
FROM metadata_items
UNION ALL
SELECT cte.id, cte.guid, cte.parent_id, src.id, src.guid, src.parent_id
FROM cte
JOIN metadata_items src ON cte._parent_id_ = src.id
)
SELECT cte.id, cte.guid, cte.parent_id, MAX(target_metadata_item_id) IS NOT NULL
FROM cte
LEFT JOIN media_subscriptions src ON cte._id_ = src.target_metadata_item_id
GROUP BY cte.id, cte.guid, cte.parent_id
ORDER BY cte.id;

fiddle

If you need the data for one separate record only then add proper WHERE.


How to specify trigger execution order under PostgreSQL? I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table.

CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE FUNCTION insert_twitter_interactions ...;
CREATE TRIGGER insert_twitter_interactions_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

CREATE OR REPLACE FUNCTION maintain_data_pointers ...;
CREATE TRIGGER maintain_data_pointers_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

I haven't fully verified, but I suspect the partitioning logic runs before the maintain trigger, and since the row doesn't end up in the parent table, then the 2nd trigger never fires.

What happens if I want to run an AFTER INSERT OR UPDATE as well? Since the row doesn't make it into the original table, then I'm at a loss to implement the after logic.

PostgreSQL executes the triggers in alphabetical order by name. So make sure to use names that get them in the order you want. From the docs

  SQL specifies that multiple triggers should be fired in time-of-creation order. PostgreSQL uses name order, which was judged to be more convenient.

BTW your SQL creates the same trigger twice.


Do database servers need load balancing? I am trying to build a 1TB database on MySQL which will need strong read performance.  I intend to replicate my database to prevent downtime.

Do my database servers need load balancing to improve performance, or would a memcached setup  be better?

What is the best failover solution for databases?

If you want to have a solution for automated failover you could take a look at Galera replication with Galera load balancer up front of your servers.
Galera will allow you to have a multi master cluster that uses the InnoDB storage engine. You can write and read to all servers.
If you don’t want to use a load balancer you can make your database logic aware of multiple database servers and distribute load in the database abstraction logic.

Continuent also provide a really nice MySQL replication/clustering product (Tungsten), the basic software is free however the full clustering system costs money.

Memcached: You should always implement caching whenever possible and try to cache as much static or infrequently changing data as you can. This is another thing you can handle in your database abstraction logic.

Implementing a true multi master system using a software like Galera or Tungsten with Memcached can provide HA and speed if done correctly.


Database solution for a large matrix/very wide table Suppose I have these two data frames (simplified for my question):

users

+---------+
| user_id |
+---------+
| 1       |
| 2       |
| ...     |
+---------+

articles

+------------+------------+
| article_id |    date    |
+------------+------------+
| a          | 2019-01-01 |
| b          | 2018-03-03 |
| ...        |            |
+------------+------------+

And a dense matrix of user-article pairs where each value is how much I predict each user would want to read each article (from 0 to 1):

+-----+------+------+-----+
|     |  1   |  2   | ... |
+-----+------+------+-----+
| a   | 0.54 | 0.99 | ... |
| b   | 0    | 0.7  | ... |
| ... | ...  | ...  | ... |
+-----+------+------+-----+

I have a web app that needs to do thing like return the top 10 most recommended articles for a single user, or the 11th-20th most recommended, for a given date range etc.:

query: (user_id=123) AND (news_date IN ('2019-04-01', '2019-05-01')) LIMIT 10 OFFSET 10

+---------+-------+------+
| news_id | score | rank |
+---------+-------+------+
| g       | 0.98  | 11   |
| d       | 0.97  | 12   |
| ...     | ...   | ...  |
| q       | 0.8   | 20   |
+---------+-------+------+

The challenge is that I have users and articles numbering in the tens of thousands, so I can't just store my matrix as a Postgres table due to its column limit.

I could store the recommendation scores in Postgres in a table as (user_id, article_id, score), which would be fast for querying, but this table would have 100M+ rows and be expensive to update, which I do daily.

My current solution is to store a single data frame (news_id, news_date, user_1_score, user_2_score, ..., user_n_score) as a gzipped Parquet file on disk, load the news_date and user_x_score columns, then filter, sort and slice. The only downside is that my web host has an ephemeral file system, so this file needs to be downloaded when the app boots. It's fast enough to get data during a web request, at least.

I don't know much about columnar data stores, but I have a feeling that one of these products might be good for my problem. Does anyone have an idea?

"but this table would have 100M+ rows and be expensive to update, which I do daily."

In order to refute this, I did the following;

CREATE TABLE test_article (
    the_series integer,
    user_id integer,
    article_id integer,
    rating numeric
);

Put on the timing, so we have proper metrics.

\timing

Then, I inserted 10 million records into test_article:

INSERT INTO test_article
SELECT generate_series(1, 10000000), CAST(RANDOM() * 10 + 1 AS INTEGER), CAST(RANDOM() * 100 + 1 AS INTEGER), ROUND(CAST(RANDOM() AS NUMERIC), 2);

TIME:

INSERT 0 10000000
Time: 33520.809 ms (00:33.521)

Table content (sample):

test=# SELECT * FROM test_article;

 the_series | user_id | article_id | rating
------------+---------+------------+--------
          1 |       5 |         85 |   0.95
          2 |       6 |         41 |   0.14
          3 |       5 |         90 |   0.34
          4 |       3 |         18 |   0.32
          5 |       7 |          6 |   0.30
          6 |      10 |         32 |   0.31
          7 |       8 |         70 |   0.84

I realise that this is not a perfect benchmark. In order for it to be so, there would have to be a UNIQUE index on (user_id, article_id) - however in order to make it as realistic as possible, I'm going to put it on those fields. I believe that it's not a huge distortion. EDIT - see below - this problem has been resolved!

So, I created the index:

CREATE INDEX user_article_ix ON test_article (user_id, article_id);

TIME:

CREATE INDEX
Time: 20556.118 ms (00:20.556)

Then, I inserted 100K records:

INSERT INTO test_article
SELECT generate_series(1, 100000), CAST(RANDOM() * 10 + 1 AS INTEGER), CAST(RANDOM() * 100 + 1 AS INTEGER), ROUND(CAST(RANDOM() AS NUMERIC), 2);

TIME;

INSERT 0 100000
Time: 996.115 ms

Less than 1 second!

So, it would appear that there is no problem with inserting a large amount of records into your linking table (also called an Associative Entity - aka joining table, association table...)

So, I very much suggest that you should go with this as a solution!

Unique combination of user_id and article_id.

After much wailing and gnashing of teeth, I finally  figured out how to make the combination of user_id and article_id be unique (because any given user can only have one current rating of an article) using generate_series.

I won't show every step, just the ones which helped with uniqueness - based on what's above:

The "secret sauce" was this bit:

INSERT INTO test_article (user_id, article_id)
SELECT * FROM
(
  WITH x AS
  (
    SELECT generate_series(1, 500) AS bill
  ),
  y AS
  (
    SELECT generate_series(1, 20000) AS fred
  )
  SELECT * FROM x
  CROSS JOIN y
) AS z
ORDER BY bill, fred;

It involves CROSS JOINing a table of 500 (i.e. users) with a table of 20,000 (i.e. articles) - the astute among you will realise that the product of these is 10,000,000 (seen above).

Now, the combination of user_id and article_id are guaranteed to be unique, because with (sample), bill = 2 and fred = 3, you get

bill | fred
------+------
    1 |    1
    1 |    2
    1 |    3
    2 |    1
    2 |    2
    2 |    3

Every record is unique - et voilà!

In any case, I used this construct to test for dupes:

SELECT (user_id, article_id)::text, count(*)
FROM test_article
WHERE 1 = (SELECT 1)
GROUP BY user_id, article_id
HAVING count(*) > 1

TIME: 4s.

You can then make (user_id, article_id) the PRIMARY KEY (not shown - only took around 30s).

Then, to add 100,000 records, you leave the users alone (still 1 - 500), but you modify the generate_series() for the articles to 20,001 to 20200 (i.e. 200 x 50 = 100,000) and do the same INSERT as above. Blisteringly fast - even with the PRIMARY KEY (< 1s).

To obtain all the articles of a particular user is v. fast (~ 25 ms)

test=# EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM test_article WHERE user_id = 77;
                                                                  QUERY PLAN
 Index Scan using test_article_pkey on test_article  (cost=0.44..65174.74 rows=44503 width=44) (actual time=0.074..21.837 rows=20200 lo
ops=1)
   Index Cond: (user_id = 77)
   Buffers: shared hit=40371 read=361 dirtied=271
 Planning Time: 0.131 ms
 Execution Time: 23.475 ms
(5 rows)

Time: 24.187 ms

And the pièce de résistance, a point search on the PK (< 1 ms):

test=# EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM test_article WHERE user_id = 77 AND article_id = 4567;
                                                            QUERY PLAN

 Index Scan using test_article_pkey on test_article  (cost=0.44..10.22 rows=2 width=44) (actual time=0.038..0.040 rows=1 loops=1)
   Index Cond: ((user_id = 77) AND (article_id = 4567))
   Buffers: shared hit=4
 Planning Time: 0.219 ms
 Execution Time: 0.078 ms
(5 rows)

Time: 0.947 ms


Is there a way to recover this database (MySQL) I did a "cold" (db offline, all filesystem files) backup of a MySQL db.
I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default.
I didn't realize that until attempting to restore the backup and notice that the
entire set of files are in DOS 8.3 format. Many of the MySQL filesystem files are not 8.3.

Anyone have tips or information on whether this is a lost cause?

If it's not a lost cause for restoring, are there some tips directions that can be offered?

Much appreciated.
========= 4/22/13
I have been able to recover some of the tables. Others respond with 1146 "Table Doesn't exist". Not sure why, as they are tables containing data, but renaming the frm does not appear to work.

As well, most of the time, the table name isn't in the frm file, just the column name data.

It might not be a lost cause, if you can figure out which files are which, but you'll most likely have to do that manually.  Presumably your 8.3 format preserved the extensions of the files, which should make things somewhat easier.

I would start with a new installation of the same version of MySQL, verify that it works, and then stop it.

References to the "data" directory below would be whatever SHOW VARIABLES LIKE 'datadir' returns.  Often, this is either /usr/local/mysql/data or /var/lib/mysql.

Identify which files in the backup were ibdata1, ib_logfile0, and ib_logfile1 in the "data" directory, and rename them to the proper names and copy them into place over the files from your new install.

Each schema should be a directory inside "data" in your backup.  Copy all of those directories into "data" from your new install (including, especially the "mysql" directory inside "data"), then rename them to the original names of the schemas.

Then, inside each of those directories, you'll find the table files, views, and triggers, which hopefully have their original extensions... rename those to their original names, also, where .frm is a table or a view, .myi, .myd, .ibd would be tables, .trn would be triggers.  Each of these needs its original filename, which would also be the names of the original table/view/trigger.

You can use the strings(1) unix command line utility to get some idea of what's in the files, to help you identify them.  The frm files, for example, will have table or view definitions, which you could use to identify which table you're dealing with, if the filenames are ambiguous.  Example:

sqlbot@dev:/usr/local/mysql/data/sakila# strings actor.frm
PRIMARY
idx_actor_last_name
InnoDB
)
        actor_id
first_name
last_name
last_update
actor_id
first_name
last_name
last_update

The same thing on the .ibd file will show the row data (assuming the tables weren't stored compressed on disk).  It isn't pretty or formatted, but it's readable.

You don't have to go through all of this work, though, to test whether this is going to be a viable route for you.  After you've fixed the ibdata1 and ib_log* files and the data/mysql files (compare with the names of the files in the reinstalled data/mysql directory) and fixed up the names of a couple of tables to test, you should be able to start the server and try to access only those tables that you've fixed the names on so far, and see if you can access the data.

If you don't try to access the tables you haven't yet fixed, the server should come up.   if not, the hostname.err log should contain hints as to what you might have overlooked.

Then shut it down again to do more renaming.

If this process proves successful, I would then suggest doing a mysqldump of all the databases and restore it elsewhere, since this resurrected instance wouldn't be something I'd want to put in production.


Purpose of Nonclustered indexes in SQL Server 2012 (New to SQL Server)
I am struggling to understand purpose of Nonclustered indexes in SQL Server. Read though MS documentation (both for v2012 and v2008 that has pretty pictures) and some online resources, but still have a lot of unanswered questions.

Does noncl. index maintains sorted B-tree ? As far as I understood, it does - http://technet.microsoft.com/en-us/library/aa933130%28v=sql.80%29.aspx, otherwise analogy of book index would not make any sense.
Why on earth you want noncl. index to be defined on table or view with a clustered index or on a heap ? Heap has no order, so defining noncl. index on heap makes some sense - you can quickly find exact matches. However, table with clustered index is already organized data, and defining noncl. index will only speed up exact searches.

You've got two questions in here:

1. Does the nonclustered index have a sorted B-tree? Yes, because you have to get to the right page of the index to find the data you're looking for.

2. Why do you want a nonclustered index when you also have a clustered index? Think of the white pages of the phone book as your clustered index. If you wanted to find all of the people in your city with a first name of "Brent", then the clustered index isn't going to do you much good - the clustered index is based on last name, first name, middle initial. Wouldn't it be helpful to have a separate phone book sorted by first name?

Just like in real life, the answer to that might be yes or no - but it depends on how often you search for people by first name, and how often people move in/out of your city, or how often people change names.


How to update where subquery value is null in SQL Server I am attempting to update a column if another value from a subquery is less than a certain value or null:

UPDATE Customer
SET PriceClassID = 'A'
FROM (SELECT custid, SUM(curybaldue) as last_sum
FROM SOShipHeader
WHERE OrdDate > @year_ago
AND Status = 'C'
GROUP BY CustID) a
JOIN Customer ON Customer.CustId = a.CustId
WHERE (last_sum < 3000 OR last_sum IS NULL)
AND PriceClassID IN ('CLUB', 'CLUB-E')
AND Customer.User7 <= @year_ago
AND Customer.User7 > @year_and_month_ago

However, when I then check if the PriceClassID was changed, I see it was not changed for any row where last_sum was null. What am I doing wrong?

Many of your NULLs may be excluded by the fact that you're using an INNER JOIN between the tables.  Try flipping it around:

UPDATE C
SET PriceClassID = 'A'
FROM    Customer AS C
    LEFT JOIN
        (
        SELECT CustId, SUM(curybaldue) AS last_sum
        FROM SOShipHeader
        WHERE OrdDate > @year_ago
            AND Status = 'C'
        GROUP BY CustID
        ) AS A ON C.CustId = A.CustId
WHERE
        (A.last_sum < 3000 OR A.last_sum IS NULL)
    AND C.PriceClassID IN ('CLUB', 'CLUB-E')
    AND C.Customer.User7 <= @year_ago
    AND C.Customer.User7 > @year_and_month_ago

Incidentally, relatively simple queries like this are usually better on StackOverflow; DBA.SE is meant more for server administration, database design, performance tuning, and such.


Mongdb start up warning logging Mongdb start up warning logging, I see lot questions around these warning in this forum however I wanted to know where does these warnings are logged. I could find them in mongod.log.
Can someone share path for these warning logs ??
MongoDB server version: 3.4.1
Server has startup warnings:
2020-11-19T19:36:51.030-0800 I STORAGE  [initandlisten]
2020-11-19T19:36:51.030-0800 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2020-11-19T19:36:51.030-0800 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: The server is started with the web server interface and access control.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] **          The web interfaces (rest, httpinterface and/or jsonp) are insecure
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] **          and should be disabled unless required for backward compatibility.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 128538 processes, 500000 files. Number of processes should be at least 250000 : 0.5 times number of files.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
mongo7052:SECONDARY>

MongoDB server version: 3.4.1
Server has startup warnings:
2021-02-19T00:04:24.384-0800 I CONTROL  [main] ** WARNING: --rest is specified without --httpinterface,
2021-02-19T00:04:24.384-0800 I CONTROL  [main] **          enabling http interface
2021-02-19T00:04:24.469-0800 I STORAGE  [initandlisten]
2021-02-19T00:04:24.469-0800 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2021-02-19T00:04:24.469-0800 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten]
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] ** WARNING: The server is started with the web server interface and access control.
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] **          The web interfaces (rest, httpinterface and/or jsonp) are insecure
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] **          and should be disabled unless required for backward compatibility.
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten]
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten]
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten]

Startup warnings shown in the mongo shell are excerpts from your MongoDB server logs. These warnings do not change until the process is restarted, so if you can't find corresponding entries in your default MongoDB log file I expect that the log file has been rotated since the process was started.
Depending on your environment configuration, old log files are typically renamed and compressed so you may need to search for different file names or use a utility like zgrep to search compressed files.
You can redisplay the warnings via show log startupWarnings or the getLog administrative command.


Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my Production (2008R2). Just joined this company and this is one of my first tasks...

You cannot do an upgrade from enterprise to Standard edition. Instead you can follow below steps :

Install and configure fresh SQL Server standard 2008R2 instance (same configuration as like your 2008R2 ).
Run below script replacing the @destserver with the NEW Server Name on the Old Enterprise Edition instance.

Note: Test below script upfront in your environment. I have assumed that xp_cmdshell is enabled. If not, it needs to be enabled before you run this script.

Edit: As per Aaron's suggestion, you can think of doing backup restore as a good and safe option (the script can be changed to do that).

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins, Databases from one instance to another
-- Version :: 1.0 for dba.stackexchange.com
--  Date    :: 04/29/2013

  declare @datafile varchar(255),
            @logfile varchar(255),
            @dbid tinyint,
            @SQLText varchar(8000),
            @dbname varchar(255),
            @destserver varchar(255),
            @SQLText2 varchar(8000)
set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
--2. Detach All Local Databases and prepare for Attach on dest.
if exists(select 1 from tempdb..sysobjects where name like '%#filetable%')
      begin
      drop table #filetable
      end
      create table #filetable (mdf varchar(255),ldf varchar(255),dbid tinyint,dbname varchar(100),fileid tinyint)
      --

      insert #filetable (mdf,dbid,fileid)
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=1

      insert #filetable (ldf,dbid,fileid)
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=0

      update u
      set u.dbname = s.name
      from #filetable u
      inner join master..sysdatabases s
      on u.dbid = s.dbid
select @dbid = min(dbid) from #filetable where dbid > 4
while @dbid is not null
begin

      select @SQLText = 'alter database '+ dbname from #filetable where dbid = convert(varchar,@dbid)
      select @SQLText = @SQLText+' set single_user with rollback immediate'
      select @SQLText = @SQLText+' exec master..sp_detach_db ' + dbname from #filetable where dbid = convert(varchar,@dbid)
      print @SQLText
      Exec(@SQLText)

      select @SQLText2 = 'exec opendatasource(''SQLNCLI'',''Datasource='+@destserver+';Persist Security Info=False;Integrated Security=SSPI'').master.dbo.sp_attach_db '''+dbname+'''' from #filetable where dbid = @dbid
      select @SQLText2= @SQLText2+','''+ mdf+'''' from #filetable where dbid = @dbid and mdf is not null
      select @SQLText2=@SQLText2+','''+ ldf+''''  from #filetable where dbid = @dbid and ldf is not null
      print @SQLText2
      Exec(@SQLText)
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid
end
select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
--shutdown with nowait


Rebuild transaction logs a colleague deleted accidentally all transaction logs (SQLServer/Logs/*.ldf files) of our SQL Server 2016 instance. Now we cannot start SQL Server service anymore.
We tried to use a recovery data tool (https://www.cgsecurity.org/wiki/Undelete_files_from_NTFS_with_TestDisk) but not all files were there.

Here is the error that is shown in the Windows Event Logs:

Is there any way to recover/rebuild the logs or any other procedure to make it work again?

Thanks

Is there any way to recover/rebuild the logs or any other procedure to make it work again? We do not have backups.

That depends on how you define "work again" as the log holds information to make the database consistent. This means even if you did get the database back up and running there is no guarantee that the data in it is correct. So maybe that "works" for you but if it's something like accounts payable, that might not be a solution.

You can give this a shot for user databases, but again YMMV and the data shouldn't be trusted. If there is a way to re-create the database and import the data, that would be best.

With system databases the easiest method is to rebuild them from the media. Since master is an issue in this instance, you'll lose information such as logins (if they are SQL logins their associated passwords), linked servers, any instance wide certificates, etc.

If you get this back up and running, I'd implement backups and lock down servers and permissions.


How to SELECT all records where there is not another record with the same key within a given time offset? What is the best way to perform an SQL query to return all records where there is not another record with a matching id and a timestamp within X time?

So, for example, given the database table sample below and a timeframe of 5 hours, I would want the query to return the sample result.

Sample Data:

id | timestamp
01 | 2015-08-03 03:00:00
01 | 2015-08-03 14:00:00
01 | 2015-08-03 15:00:00
01 | 2015-08-03 16:00:00
02 | 2015-08-03 4:00:00
02 | 2015-08-03 5:00:00
03 | 2015-08-03 2:00:00
03 | 2015-08-03 5:00:00
03 | 2015-08-03 10:01:00

Sample Result:
01 | 2015-08-03 03:00:00
01 | 2015-08-03 16:00:00
02 | 2015-08-03 5:00:00
03 | 2015-08-03 5:00:00
03 | 2015-08-03 10:01:00

...since these are the only records for their respective ids where no other records with matching ids occur in the following 5 hours.

My current solution is to essentially query the entire table and then loop through each record querying on a local datatable. It works but is highly inefficient. Is there an SQL way of doing this? I have looked around for awhile, and I am coming up short. But, I might just not be searching correctly. There is probably some simple solution that I am missing, but I am still pretty new to SQL.

Thanks!

Update:
I should have included the current query I am using for reference. Note that "id" in the sample above really refers to "deviceid" in my actual query. It is:

SELECT id                                   AS 'ID',
       eventsink.elevatortripevent.deviceid AS 'DeviceID',
       completed                            AS 'Created',
       digitalposition                      AS 'DigitalPosition',
       buildingstate                        AS 'State'
FROM   eventsink.elevatortripevent
       JOIN md.elevator
         ON md.elevator.deviceid = eventsink.elevatortripevent.deviceid
       JOIN ea.elevatoreventevent
         ON ea.elevatoreventevent.deviceid =
            eventsink.elevatortripevent.deviceid
WHERE  completed >= '8/2/2016 8:13:34 PM'
       AND completed <= '8/3/2016 8:13:34 PM'
ORDER  BY eventsink.elevatortripevent.deviceid

Attempt at Implementing Answer by @irimias

SELECT id              AS 'ID',
       trip1.deviceid  AS 'Device ID',
       trip1.completed AS 'Created',
       digitalposition AS 'Digital Position',
       buildingstate   AS 'State'
FROM   eventsink.elevatortripevent trip1
       LEFT JOIN eventsink.elevatortripevent trip2
              ON trip1.deviceid = trip2.deviceid
                 AND trip1.completed < trip2.completed
                 AND trip1.completed > Dateadd(hh, -5, trip2.completed)
       JOIN md.elevator
         ON md.elevator.deviceid = trip1.deviceid
       JOIN ea.elevatoreventevent
         ON ea.elevatoreventevent.deviceid = trip1.deviceid
WHERE  trip2.deviceid IS NULL
       AND trip1.completed >= '8/2/2016 8:13:34 PM'
       AND trip1.completed <= '8/3/2016 8:13:34 PM'
ORDER  BY trip1.deviceid

Attempt at Implementing Answer by @dnoeth

WITH cte
     AS (SELECT eventsink.elevatortripevent.deviceid,
                completed,
                Lead(completed)
                  OVER (
                    partition BY eventsink.elevatortripevent.deviceid
                    ORDER BY completed) AS nextTS
         FROM   eventsink.elevatortripevent)
SELECT id                                   AS 'ID',
       cte.deviceid AS 'DeviceID',
       completed                            AS 'Created',
       digitalposition                      AS 'DigitalPosition',
       buildingstate                        AS 'State'
FROM   cte
       JOIN md.elevator
         ON md.elevator.deviceid = cte.deviceid
       JOIN ea.elevatoreventevent
         ON ea.elevatoreventevent.deviceid =
            cte.deviceid
WHERE  ( nextts IS NULL
          OR completed < Dateadd(hh, -5, nextts) )
       AND completed >= '8/2/2016 8:13:34 PM'
       AND completed <= '8/3/2016 8:13:34 PM'
ORDER  BY cte.deviceid

maybe you can try something like :

select ds1.*
from SampleData ds1
left join SampleData ds2 on ds1.id = ds2.id
    and ds1.timestamp < ds2.timestamp
    and ds1.timestamp > dateadd(hh, -5, ds2.timestamp)
where ds2.id is null


Create a form solely for inputting data? I am looking to create a form for a database in MS Access, that will be used solely to allow users to input data into the database. However, all the options I have found do not allow for this, they all provide navigation buttons that allow users to navigate to data that has already been entered into the forms that are related to them. How can I create a form to just allow users to input data without allowing them to look over more of the information held in the form's related table?

If you enter design mode on your entry form, open up your properties window and select the form object. You should find a property called "Data Entry" which you should set to "Yes." This removes the link to all data from the related table and will only display blank controls. You can then have your user enter data directly into the form. I believe the default adds the data to the table when they exit the last control in the tab order.

There are also options in the same properties pane for the form that allow you to switch off the record navigation buttons.


What is the meaning of "minimal key"? In the definition, it says minimal key is a minimal set of attributes needed to identify the table. Does the "minimal" part mean "minimal number of attributes" or "cannot be eliminated"?

For example:

For a relation R(A, B, C, D, E, F), the closure of A, BC, and DEF are all {A, B, C, D, E, F}.

Are they all minimal keys? Or is only A the minimal key?

They are all minimal keys.

Minimal means there is no proper subset with the same property.


Set column to randomly chosen value from another table Consider this:

UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
                WHERE s.name = 'abc'
                ORDER BY random() LIMIT 1 OFFSET 0);

My goal: Set dest.dest_col in every row to a randomly picked value out of all the values matched by the subquery.

To accomplish that, I added ORDER BY random() LIMIT 1. But this works by picking a random value once, and then setting it to every row in the dest.dest_col.

I tried adding OFFSET 0 (suggested in other answers as a way to prevent Postgres from optimizing the subquery), but this doesn't seem to have any effect.

Also note that I need this to work on PostgreSQL running on GCP.

The subquery is evaluated only once because it has no dependency on the row to update.

You may add one, just to force the engine to evaluate the subquery for each row. For instance if dest_col is a non-null number:

UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
                WHERE s.name = 'abc'
                ORDER BY random()+dest_col LIMIT 1)

Or if it's not a number, any other column that is a number in dest will do, or any other expression involving column(s) of the table to update that will not affect the result but create a dependency.


Postgres automatic insert time stamp with time zone (india) Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we use zone).pg version 9.1. and how to use to desired time format ?

Actually, I want to give a comment about your question. I can't to do that because I have low reputation here. So, I decided to answer whether it fits with your question or not.

Let's I start. Would you like determine "query is inserted" first?

I have an assumption about your "query is inserted". "Query is inserted" is every query is inserted or affected on database (it could be insert, update, delete, etc.) that will be noted in log. Log here means log table where the trigger handles every query. OK. I give you clear explanation by an example below.

First, make a definition of log table like this,

CREATE TABLE query_log (
    log_id SERIAL PRIMARY KEY,
    log_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    action VARCHAR(20) NOT NULL
);

Action column above determines whether query performed by someone (for example when someone do insert, so the action column will be "insert" and so on). Then, we have to make a trigger to handle what we write on log table. It depends on table what affected by query. For example, we do query on table query. So,

CREATE TRIGGER add_insert_query_log
AFTER INSERT ON name_of_table
FOR EACH ROW
EXECUTE PROCEDURE insert_query_log();

and

CREATE OR REPLACE FUNCTION insert_query_log() RETURNS TRIGGER AS
$$
BEGIN
   INSERT INTO query_log VALUES (currval(log_id)), DEFAULT, 'insert');
   RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

You can continue with another function for update, delete, and etc or you can combine all of them in one function.


How can I limit the number of emails sent by SQL Server agent? I use SQL Server agent notifications a lot for notifying of failed jobs. Usually, jobs run every hour so I don't mind getting a few emails if the job fails.

However, I want to run a job every minute now and leave it running. I really don't want to get 100s of emails if it fails for a few hours.

Can I set a limit on the number of emails SQL Server agent will send for a job?

This feature is already built into SQL Server. I had a similar situation and what I did was to change my code to use a custom error number, above 50000, and set up a new Alert, under SQL Server Agent|Alerts in Management Studio, with that custom error number. Under the Options section, there is a Delay between responses section that can be used to mitigate the volume of notifications per hour.  I had my process monitoring every 10 seconds, but notifying every 5 minutes because I wanted to know when the critical situation started, but not be overwhelmed with alerts.


SQL Server 2012 Developer edition questions I'd like to use SQL Server 2012 Developer Edition on our development/test server, which is a dedicated server rented from an online company.

When the product is ready, I would like to have it deployed on production server(s) using SQL Server 2012 Web Edition, by hosting with companies that offer SQL Server Web Edition through their SPLA.

Is this possible and the correct use of the Developer and Web editions?
Does Web Edition allow one to create multiple databases on the same physical server, and if so, how many?
How can I buy a license online for the developer edition? All online sales seem to be for physical media, and I don't have physical access to the development server, so the only option seems to be, to rip the CD/DVD and then upload it to the development server which would be a tedious process. I would like to download SQL Server directly on the development server, buy the development edition license online, and then put that license into SQL so it is properly licensed.

Thanks.

1: yes

2: yes. Limits generally are documented in the documentation. In this particular case you shall look to http://technet.microsoft.com/en-us/library/ms143432.aspx for all capacity specifcations for all editions of sql server.

3: Why you have to? Normally developers have MSDN (Microsoft Developers NEtwok) and get the licenses there.

Then - ever heard of google? Finsind a shop is trivial.

For example google and "sql server developer edition price" led me to
http://www.amazon.com/SQL-Server-Developer-Edition-2012/dp/B007RFXQAM

  so the only option seems to be, to rip the CD/DVD and then upload it to the
  development server which would be a tedious process.

No need to "rip" - a file copy is good enough. SQL Server does nothing fancy with the install media. But generally I keep ISO files around for all the stuff I need to develop - easier than looking for the DVD anyway every time I need to install it.

Generally if you want to do anything professional - or even semi professional - these days better stop assuming copying an ISO file up to the cloud is tendious. You will move a lot more around than you ever think of. This is 2013.

If you would have MSDN you could just download the ISO from the MS official repositories.