Update query in AFTER INSERT is ignored I have this triggers, that to put it simple do this things:

Before Insert:creates new record with new id,name and puts value in start time cols.

After insert: updates value in end time cols.

create or replace trigger stg_control_inserts_before
  before insert on Distributionplan
  for each row
declare
  GetError varchar2(280);
  Jobname varchar2(20) := 'DistributionPlan';
  HoraActual  timestamp (6) := systimestamp;
  FullJobName varchar2 (80) := Jobname ||' '|| HoraActual;
Begin
  insert into stg_control  (job_name,job_id,execution_start,exec_tocore_start)
   values (FullJobName,stg_control_seq.nextval,HoraActual,HoraActual);
end;
/

create or replace trigger stg_control_inserts
  after insert on Distributionplan
  for each row
declare
  HoraActual  timestamp := systimestamp;
Begin
  update stg_control set execution_end='14/05/19 00:00:00,493000000'
  where job_id in (select max(job_id) from stg_control);
end;
/

The issue is that the second trigger is ignored, the first one that creates the new record is executed but the second one, that should update the end time doesn't do anything. I tried passing a hardcoded value instead of the variable but doesn't work either.

Regards.

I'm not sure why your trigger isn't working - unless it's because other rows have been inserted in between the two triggers firing.

I suspect your issues may be mitigated by using a compound trigger along the lines of:

CREATE OR REPLACE TRIGGER stg_control_inserts
  FOR insert on Distributionplan
    COMPOUND TRIGGER

    v_job_id stg_control.job_id%TYPE;
    v_job_name stg_control.job_name%TYPE := 'DistributionPlan';

   BEFORE EACH ROW IS
   BEGIN
     INSERT INTO stg_control (job_name, job_id, execution_start, exec_tocore_start)
     VALUES (v_job_name || ' ' || SYSTIMESTAMP, v_job_id, SYSTIMESTAMP, SYSTIMESTAMP)
     RETURNING job_id INTO v_job_id;
   END BEFORE EACH ROW;

   AFTER EACH ROW IS
   BEGIN
     UPDATE stg_control
     SET    execution_end = '14/05/19 00:00:00,493000000'
     WHERE  job_id = v_job_id;
   END AFTER EACH ROW;
END stg_control_inserts;
/

N.B. untested.


Scheduling an event every day at a given time I want my event to run everyday at 00:20. I am confused in scheduling of the time. Should I be using the first option or the second one?

Option 1

CREATE EVENT my_event
  ON SCHEDULE
    EVERY 1 DAY
    STARTS '2014-04-30 00:20:00' ON COMPLETION PRESERVE ENABLE
  DO
    # My query

Option 2

CREATE EVENT my_event
  ON SCHEDULE
    AT ('2014-04-30 00:20:00'+ INTERVAL 1 DAY) ON COMPLETION PRESERVE ENABLE
  DO
    # My query

Use the first one if you want to execute your event everyday.

The second syntax will execute once.

Option 1: Execute the event every night at 00:20

Option 2: Execute the event once the 2014-05-01 at 00:20

Max.


Monitor if sqlserver is up from distant server using sqlcmd I want to monitor if my sqlserver is up from a distant machine.

I currently send a simple "select @@version" query with sqlcmd and then validate if that query returned the expected part of text :

sqlcmd -S np:\\<servername>\pipe\sql\query -i version.sql | findstr /I "expected string"

then I proceed to validate conditions based on this event.

Is there a better way to monitor db up events and db down events ?

Thanks

The best way (aside from buying a proper product) is to do it with PowerShell, with Invoke-SqlCmd, because it will give you more flexibility to check the response and do something with it.

I probably wouldn't confirm version because it's meaningless and will change when you upgrade or patch; I would confirm @@Servername matches what I expect in case the IP has somehow become mixed.

As for alternatives they're not really "better" but you can expand it as far as you want to:

You can check the Event Log for failed connection attempts. Just because you can connect doesn't mean the critical application which uses an AD account which just expired when it wasn't meant to, can connect. This is actually pretty useful. If you're not tracking failed logins then you absolutely should start, whether it's the event log, an audit, extended event, or standard trace.
Are all the databases online / sync'd? Just because you can connect to master doesn't mean one of the disks hasn't disconnected and caused the database to go into suspect mode.
You can use WMI/CIM to determine whether the service is running. Is your Agent running? It better be because otherwise your backups and other processes are probably going to stop. You're probably going to want to use WMI/CIM to check that.
Technically you can write a WMI handler which gets injected onto the server which can trigger an email notification when the SQL service stops/starts unexpectedly. But this is tricky and I wouldn't bother.
You could query performance counters to pull back logins/sec to see whether it has dropped radically from the norm. But it would be so tricky to identify outliers it's probably not worth the effort. On the other hand if it increases to > 1000 per second (4 million per hour, thanks to a broken Oracle software reading from SQL!) it can indicate a problem, so maybe you do want to catch it.


Converting SQL Server recovery model simple to full I am using SQL Server 2008 R2 with a recovery model of simple.

Monthly we have:

Total number of insert is 2196716
Total number of delete is 639542
Total number of update is 4203398

Now we are changing this server to active – active cluster environment.

Automatically recovery model change to full.

Above count’s are fine with recovery model fine? Any action required to take?

BOL says about recovery model that

  Recovery models are designed to control transaction log
  maintenance. A recovery model is a database property that controls how
  transactions are logged, whether the transaction log requires (and
  allows) backing up, and what kinds of restore operations are
  available.

Please note that recovery model controls amount of logging in transaction log file and in case of disaster how much data you can recover. It has no relation to amount of inserts or updates or number of column in you table.

You can keep simple recovery model if you do not require point in time recovery. You can take full and differential backup in simple recovery model. If you want to change recovery model to full then make sure you take regular transaction log backups after changing recovery model. Changing recovery model should depends on how much data loss you can afford or agreed between you and client basically the RPO and RTO.


How can I filter an extended event by database in SQL Server 2008 R2 If I do an extended event session like this in SQL Server 2008 R2:

CREATE EVENT SESSION [Query tracing] ON SERVER
ADD EVENT sqlserver.rpc_completed
    (
        ACTION(sqlserver.sql_text)
        WHERE sqlsever.database_name = 'master'

I get an error:

  Msg 25706, Level 16, State 8, Line 8
  The event attribute or predicate source, "sqlsever.database_name", could not be found.

The same occurs for WHERE sqlsever.database_id = DB_ID('master')

Is there any way to filter by database?

Two things that are the problem.

There is no database_name action in SQL Server 2008 R2 (it was introduced in SQL Server 2012):

select
    action_name =
        p.name + '.' + o.name
from sys.dm_xe_objects o
inner join sys.dm_xe_packages p
on o.package_guid = p.guid
where o.object_type = 'action'
and o.name like '%database%';

The output from that is:

action_name
-----------
sqlserver.database_id
sqlserver.database_context

But there is sqlserver.database_id.  The second problem is that your attempt with database_id didn't work because you have a typo (you have "sqlsever", missing an "r").


How do I find I.O. statistics (by index) for queries in progress By running SET STATISTICS IO ON before running a SQL query, I can get an idea of the number of logical reads performed on each index after the query completes.

By looking at the view sys.dm_exec_requests, I can get an idea of the number of logical reads (total) that have been performed so far for any query that's in progress.

How can I get an idea of how many logical reads have been performed on each index so far for queries in progress?

You can see some information about the indexes that are in use right now using the DMV sys.dm_db_index_usage_stats. An example:

declare @dbid int
select @dbid = db_id()
select db_name(db_id()) as [database]
, objectname=object_name(s.object_id)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes =  user_updates
from sys.dm_db_index_usage_stats s join sys.indexes i  on i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id  and s.database_id = @dbid  and i.index_id <> 1
order by reads

(sample code from MSDN).

Reads and writes columns will grow as soon as the specific index is used for data gathering.


SSMS and Unicode fonts in grid results I wrote this simple update query:

update foo set test ='褠ఓ�ᵂ뱗텬㰗' where id = 1

[test] is an nvarchar(16) column.  It's going to contain an encrypted value.

In SSMS (10.50) I've set the font for grid results and text results to Lucida Sans Unicode.  The glyphs (褠ఓ�ᵂ뱗텬㰗) are displayed in the query editor window. But the grid results and text results windows always show simply question marks '????????" instead of 褠ఓ�ᵂ뱗텬㰗.

I closed and opened SSMS after making the font changes in Tools ->Options.

Any suggestions would be appreciated.  I normally won't need to make any sense of the contents of this column, but when testing to make sure the client app is sending through the correct data, it would be helpful to see something other than question marks.

If you want to insert Unicode text in your update statement you need to supply a Unicode string of the form N'...', as in

update foo set test = N'褠ఓ�ᵂ뱗텬㰗' where id = 1

Otherwise the characters really are converted to question marks if SQL Server is unable to map them to corresponding non-Unicode characters, even in an nvarchar column.


Delete from GROUP BY with multiple GROUP columns I have a simple table consisting of id, channel_id, value and timestamp:
CREATE TABLE `data` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`channel_id` int(11) DEFAULT NULL,
`timestamp` bigint(20) NOT NULL,
`value` float NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_ADF3F36372F5A1AA` (`channel_id`),
) ENGINE=InnoDB AUTO_INCREMENT=2126679551

The combination of channel_id,timestamp MUST be unique but the table has gotten corrupted. My ultimate goal is to add a unique index which fails due to the duplicates.
The duplicates are:
select channel_id,timestamp,count(*) cnt
from data
group by channel_id,timestamp having cnt>1

How can I delete the duplicates? I'm stuck since obviously I can't get the row id from the duplicates sub select?
UPDATE I could probably do
delete d1.* from data d1 join data d2 on d1.channel_id=d2.channel_id and d1.timestamp=d2.timestamp where d1.id != d2.id;

but since the table has almost 1 billion records a cross-product does not seem like a good idea?

DELETE t1
FROM data t1
JOIN data t2 USING (channel_id,timestamp)
WHERE t1.id > t2.id


Overview of how MongoDB uses its various threads On one instance I have MongoDB using ~85 threads. In lieu of having time to investigate directly, I am curious:

What are the threads used for? Do they fall into a few high-level usage categories?
How can I control/influence the number of threads used? E.g. max connection params, etc.

Max connection count is usually %80 of ulimit value which is 1024 in most linux distros by default. That gives you 819 connections. It can be changed by changing ulimit value.

In MongoDB, each host can use 10 connections which is set by MongoOptions.connectionsPerHost property. These connections are controlled by an internal connection pool. It is thread-safe and you can have up to ConnectionPerHost x threadsAllowedToBlockForConnectionMultiplier threads running simultaneously. threadsAllowedToBlockForConnectionMultiplier also can be changed, its default value is 5. That is by default, you can have 50 simultaneous threads running from one host. Threads more than that value will get the "Out of semaphores to get db connection" exception.

You can change MongoOptions.connectionsPerHost and threadsAllowedToBlockForConnectionMultiplier values appropriately according to your needs.

If you are using drivers on a web application, you should use it as singleton class. No need to 'open connection-> do some work -> close connection' loop. Just declare a global connection once (and initialize in init method of your servlet) and use it in your entire web application.


In what situation the .NDF files are created? I'm not a db administrator but from a quick search I found .NDF files are secondary data files.
I'm curious to know under what conditions the .NDF files are generated ?
If it is generated, how we can know which data base is behind it ?

You have a database with a datafile(PRIMARY) in a filegroup(PRIMARY) which stores metadata as well as user data. When you reach the maximum size of the datafile(depended on underlying Operating System) then you need to add an extra file which is know as secondary data file. According to the reference, datafile can grow up to 16TB, so for normal sized database this situation may no arise. So it's optional to have any secondary datafiles.

However, these can be used to spread data across multiple disks by putting each file on a different disk drive.

  I'm curious to know under what conditions the .NDF files are generated ?

When you add extra datafiles on a database.

  If it is generated, how we can know which data base is behind it ?

It is added on A DATABASE. So you need to know database first in order to add the secondary data files. If you know that database then you can know the it's datafiles.

More details and useful links are given here


update table column if provided text is not matched I want to update a huge table, I can do the below in PHP but it will take longer time I believe.

$string = "test string";
if($string!="Amazon.com"){
//set table column value as FBA
}

if($string=="Amazon.com"){
//set table column value as AMZ
}

Can I do this via MYSQL query ?

thanks

You can update all your table with query:

UPDATE table
   SET value = IF(merchantDetails = "Amazon.com", "AMZ", "FBA");

where table and value should be changed to the actual names of table and field.


Estimated versus actual query plan with function calls I have this query on SQL server, a merge replication query:

SELECT DISTINCT
    b.tablenick,
    b.rowguid,
    c.generation,
    sys.fn_MSgeneration_downloadonly
    (
        c.generation,
        c.tablenick
    )
FROM #belong b
LEFT OUTER JOIN dbo.MSmerge_contents c ON
    c.tablenick = b.tablenick
    AND c.rowguid = b.rowguid;

The estimated query plan includes information about 3 queries:

The query above
The function call to fn_MSgeneration_downloadonly
The function call to fn_MSArticle_has_downloadonly_property

The actual query plan includes only this information:

The query above

Nothing about the functions. Why is the function information missing in the actual plan?

I tried these options:

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

Which created an actual plan, but it was missing parts 2 and 3 same as when I used the actual query plan option in Management Studio.

If for instance I was to use Profiler to capture the information about the function calls what events would I select?

Didn't find an answer specifically related to the query plans, but I profiled SP:StmtStarting and SP:StmtCompleted and it showed the function calls.

And nothing about the functions. Why is the function information
  missing in the actual plan?

This is by design, for performance reasons.

Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row.  Put another way, the function body is executed separately for each input row.  This single fact explains most performance problems associated with T-SQL scalar and multi-statement functions (note that in-line table valued functions do not use  the BEGIN...END syntax).

In the context of your question, this would result in full SHOWPLAN output for each row.  XML plan output is quite verbose and expensive to produce, so producing full output for every row would be a bad idea in general terms.

Example

Consider the T-SQL scalar function below, created in the AdventureWorks sample database, which returns the name of a product given its ID:

CREATE FUNCTION dbo.DumbNameLookup
(
    @ProductID integer
)
RETURNS dbo.Name
AS
BEGIN
    RETURN
    (
        SELECT
            p.Name
        FROM Production.Product AS p
        WHERE
            p.ProductID = @ProductID
    );
END;

Pre-execution plan

A pre-execution plan (estimated plan in SSMS) shows plan information for the parent statement and nested function calls:

-- Pre-execution plan shows main query and nested function call
SET SHOWPLAN_XML ON;
GO
SELECT dbo.DumbNameLookup(1);
GO
SET SHOWPLAN_XML OFF;

SSMS output:

The same XML viewed in SQL Sentry Plan Explorer shows the nested nature of the calls more clearly:

Post-execution output

SSMS shows details for only the main query when post-execution plan output is requested:

-- Post-execution plan shows main query only
SET STATISTICS XML ON;
SELECT dbo.DumbNameLookup(1);
SET STATISTICS XML OFF;

The performance impact of doing otherwise can be shown using the Showplan XML Statistics Profile Event Class in SQL Server Profiler, using a query that calls the function multiple times (once per input row):

SELECT TOP (5)
    p.ProductID,
    dbo.DumbNameLookup(p.ProductID)
FROM Production.Product AS p;

Profiler output:

There are five separate post-execution plans for the function executions, and one for the parent query.  The five function plans look like this in the profiler lower pane:

The parent query plan is:

Executing the query without the TOP (5) clause results in a full execution plan for each of the 504 rows in the Product table.  You can probably see how this would quickly get out of hand with larger tables.

The situation for triggers is reversed.  These do not show any pre-execution plan information, but do include a post-execution plan.  This reflects the set-based nature of triggers; each is fired once for all rows affected, rather than once per row.


Fun with DateTimes/Durations I have a table (Events) with the following columns:

ClientID, EventID, EventName, StartDate, Duration

Consider the following data:

INSERT INTO Events(ClientID, EventID, EventName, StartDate, Duration)
VALUES (1, 1, 'Login', '2016-11-27 1:30:00', 86400000),
       (2, 1, 'Login', '2016-11-27 0:30:00', 86400000),
       (3, 1, 'Login', '2016-11-27 0:00:00', 86400000)

As you can see, the Duration is in milliseconds and can span multiple days.

In the end, I would like to return the following for 2016-11-28:

EventID  EventName  Sum(Duration) Count(ClientID)
--------------------------------------------------
1        'Login'     7200000          2

The question being answered here is "How many clients were logged in on 2016-11-28 and for how many milliseconds?". 86400000 ms is 1 day. So, the first row would have 1.5 hours on 2016-11-28 (1:30 after midnight), the second would have .5 hours (0:30 after midnight), the third would have 0 hours (0:00 after midnight). Add those up, you get 2 hours or 7200000 ms and 2 clients since the 3rd did not have a duration on that day.

I have spent days toying with CASE statements to try to do this, and I really feel like there has to a better approach.

DECLARE @StartDate as DateTime, @EndDate as DateTime
SET @StartDate = '2016-11-28'
SET @EndDate = '2016-11-29'

SELECT
    EventID, EventName, COUNT(ClientID) AS CountClients,
    SUM(CAST(DATEDIFF(ms, CASE
                            WHEN StartDate < @StartDate
                               THEN @StartDate
                            ELSE StartDate
                         END,
                         CASE
                            WHEN DATEADD(ms, Duration, StartDate) >= @EndDate
                               THEN @EndDate
                               ELSE DATEADD(ms, Duration, StartDate)
                         END) AS bigint)) AS SumDuration
FROM Events
WHERE
    ((StartDate >= @StartDate AND StartDate < @EndDate)
    OR (DATEADD(ms, Duration, StartDate) >= @StartDate AND DATEADD(ms, Duration, StartDate) < @EndDate))
GROUP BY EventID, EventName

I am really struggling with how to do this efficiently, considering that I will need to get partial durations for dates that span more than a day. Any help would really be appreciated.

You could do it like this instead.

select E.EventID,
       E.EventName,
       count(*) as CountClients,
       sum(datediff(millisecond, I.StartDate, I.EndDate)) as SumDuration
from dbo.Events as E
  cross apply (
              select max(T.StartDate) as StartDate,
                     min(T.EndDate) as EndDate
              from (
                   values(@StartDate, @EndDate),
                         (E.StartDate, dateadd(millisecond, E.Duration, E.StartDate))
                   ) as T(StartDate, EndDate)
              ) as I
where E.StartDate < @EndDate and
      dateadd(millisecond, E.Duration, E.StartDate) > @StartDate
group by E.EventID,
         E.EventName;

Not sure it looks any simpler than what you have but your version is missing out on intervals that starts before @StartDate and ends after @EndDate. I changed the where clause to take care of that and at the same time it will be possible for you to use an index to somewhat limit the number of rows read from the table.

I also changed the way you calculate max StartDateand min EndDate for the sum of duration. Don't think it matter much for performance but to me the code looks neater.

With an index on StartDate having Duration, EventID, EventName as include columns you will get an index seek on E.StartDate < @EndDate and dateadd(millisecond, E.Duration, E.StartDate) > @StartDate as a residual predicate.

create nonclustered index IX on dbo.Events(StartDate)
  include(Duration, EventName, EventID);

This will scan the index from the beginning as long as StartDate is less than @EndDate and it will make wonders for performance if you are looking at early dates and not so much for later dates.

An even better option could be an index on EventID, EventName with StartDate, Duration as include columns to remove the Sort operator from the plan.

create nonclustered index IX on dbo.Events(EventID, EventName)
  include(StartDate, Duration);

Every row in the table has to be looked at but it will be way better if you need to scan almost the entire table anyway.


SQL Server select records from header and body table I have 2 tables, Header and Body, with the same columns and multiple groups of records. For each group, I want one record of header and the rest of the body records.

Header

Id Group Name Value
1  1     n1   v1
2  2     n2   v2

Body

Id Group Name Value
1  1     nb1   vb1
2  1     nb2   vb2
3  2     nb3   vb3

Expected Result

Id Group Name Value
1  1     n1    v1
2  1     nb1   vb1
3  1     nb2   vb2
4  2     n2    v2
5  2     nb3   vb3

If it was just one group I could have just done a union but the number of groups isn't fixed.

The Id column in the result is just a row number. The other three columns Group, Name, and Value are more important to me.

The header row is always the first row for that group.

I don't see why this won't work:
IF OBJECT_ID(N'dbo.Header', N'U') IS NOT NULL
DROP TABLE dbo.Header;
IF OBJECT_ID(N'dbo.Body', N'U') IS NOT NULL
DROP TABLE dbo.Body;
GO
CREATE TABLE dbo.Header
(
    iGroup int
    , sName varchar(50)
    , iValue int
);
CREATE TABLE Body
(
    iGroup int
    , sName varchar(50)
    , iValue int
);

INSERT INTO dbo.Header (iGroup, sName, iValue)
VALUES (1, 'test1', 10)
    , (2, 'test2', 20);

INSERT INTO dbo.Body (iGroup, sName, iValue)
VALUES (1, 'test3', 30)
    , (2, 'test4', 40);

;WITH src AS
(
    SELECT iGroup
        , sName
        , iValue
        , 0 as sort_by
    FROM Header
    UNION ALL
    SELECT iGroup
        , sName
        , iValue
        , 1 as sort_by
    FROM Body
)
SELECT Id = ROW_NUMBER() OVER (ORDER BY sort_by, iGroup, sName)
    , src.*
FROM src
ORDER BY sort_by, iGroup, sName;

Results:
╔════╦════════╦═══════╦════════╗
║ Id ║ iGroup ║ sName ║ iValue ║
╠════╬════════╬═══════╬════════╣
║  1 ║      1 ║ test1 ║     10 ║
║  2 ║      1 ║ test3 ║     30 ║
║  3 ║      2 ║ test2 ║     20 ║
║  4 ║      2 ║ test4 ║     40 ║
╚════╩════════╩═══════╩════════╝

Also, don't use reserved keywords as column names, please!


Restrict record to be update or deleted according to dates inside row for SQL Server 2005 I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at  the subscriber that still existed at the publisher.

I now know that if someone updates, or deletes some records at the publisher, which do not exist on my subscriber, then my replication is going to crash.

I know  that I could also filter data at the distributor, but this would mean reinitialising my subscribers, and that would mean transferring almost 40GB over a slow link.

So, whilst I am testing my subscribed database I'd love to restrict users to only updating or deleting records at the publisher side. My source table has a date field and I want to restrict updates or deletes according to date.

Is it better to use a trigger or a constraint for this?

How can I avoid as much record locking as possible?

A constraint can't control DML operations unless the update actually attempts to change the data in that column. It certainly can't control deletes unless we're talking about table-level constraints but I recommend against this.

Most people go with after triggers for this type of logic, but this means (a) attempt to perform the change (b) if it fails your logic, roll it back. I much prefer an INSTEAD OF trigger for this. The following will allow updates / deletes only to rows that fall within your date span (of course you will have to replace the 'date span ' strings with actual dates), even if a multi-row operation might affect some rows but not others.

CREATE TRIGGER dbo.PreventUpdate_table_name
ON dbo.table_name
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
      SET t.col = i.col
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO

CREATE TRIGGER dbo.PreventDelete_table_name
ON dbo.table_name
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE t
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO


How to identify columns order in a table I need to list columns from a table in the table definition order:

select * from syscolumns
where id = object_id('MyTable')
--order by colid

By examining syscolumns tables two columns look relevant: colid and colorder. The MSDN article on syscolumns says:

colid    | smallint | Column or parameter ID.
colorder | smallint | Identified for informational purposes only.
                    | Not supported. Future compatibility is not guaranteed.

I tried to run

select * from syscolumns where colorder <> colid

which yielded no rows, and that makes me think that these columns has the same values most of the time.

It does look that the safest bet is to use colid. However I would be curious to know: is there a difference between these two columns, and if there is, what is this difference?

Also the MSDN article, does not confirm, that colid reflects the order of the table definition. While this is reasonable to assume that this is the case, could you please let me know, if you are sure that it's the case, how you know that this is?

You should be using the sys.columns catalog view.  syscolumns is included only for backwards compatibility.  It's really a SQL Server 2000 system table that shouldn't be used in SQL Server 2008 R2.

select *
from sys.columns
where object_id = object_id('MyTable')
order by column_id

That should return the order of your columns.  Note, though, these column id's might not be sequential.


Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values? Can I set a (logical) PRIMARY KEY constraint, served by a (physical) NONCLUSTERED INDEX, so that it only checks certain values of the constrained and indexed column or another column in the relevant table?

And, if so, can that column be established as a FOREIGN KEY constraint referenced from other tables?

I'm trying to basically see if I can remove certain rows from a parent table without removing those rows from a child table but still enforce the relationship for, e.g:

IDs > 1000, or
CreatedDateTime > RemovalDate.

For example, you can have that kind of constraint:

ALTER TABLE MyTable WITH NOCHECK ADD
    CONSTRAINT PK_MyTable CHECK (Id > 1000)

but how would you set this column as a PRIMARY KEY fixed with a NONCLUSTERED INDEX?

And can I do something like the following definition:

ALTER TABLE MyTable WITH NOCHECK ADD
    CONSTRAINT PK_MyTable CHECK (CreatedOn > '01/01/2010')

but have an INDEX for the column named Id?

You cannot have a Primary Key that only works for some of the rows. It would really cease being a Primary key at that point. Emphasis on Primary.

You can play with filtered indexes for a Unique index and that filter can be on values you specify.

But a primary key is there to enforce uniqueness so you can't make only some of the table part of the key.

You can disable the FKs while you make fixes, you can build a foreign key relationship on an existing table with NOCHECK - but that will mean that none of the relationships are checked. You have an untrusted foreign key and some of the benefits foreign keys offer like foreign key join elimination can't work that way because SQL isn't guaranteed that your row is there or not there.

Alternatively you could just not define that relationship and deal with the ramifications. But you are basically asking SQL server to break the referential integrity at the outset by making exceptions. If you could cart those exceptions off into a separate table and join to that table in a view or something like that you could get around this and keep the relationship on the "real" table moving forward.

Remember though: A filtered index just can give you some uniqueness on the other side of filter. This doesn't address FKs or even the PK. I think a better answer is fix the issue with the data. Even a -1 primary key value for "unknown" as is common in warehouses would be better than orphans.


Pivoting Dynamic Rows into Columns in MySQL I have two tables Student_Attendance and Students.

I am using this query:

SELECT
    sa.Student_Id,
    sa.Attendance_Date,
    CASE WHEN sa.Attendance_Status as Status
FROM student_attendance sa
INNER JOIN courses c
    ON c.Course_Id = sa.Course_Id
INNER JOIN students es
    ON es.Student_Id =  sa.Student_Id
WHERE sa.Course_Id = 1
ORDER BY sa.Attendance_Date DESC;

I am getting this result from above query:

But I want something like below:

In the student_attendance table there are more than 50 rows.

How 7 attendance_date rows should be converted into 7 columns as I have given below?

Can someone help me please?

You must add a GROUP BY and an aggregate around the CASE statement(s). There is no PIVOT operator with MySQL.
Query:
SELECT sa.Student_Id
    , sa.Attendance_Date
    , MAX(CASE WHEN sa.Attendance_Date = '20160203' Then sa.Attendance_Status END) As '20160203'
    , MAX(CASE WHEN sa.Attendance_Date = '20160204' Then sa.Attendance_Status END) As '20160204'
    , MAX(CASE WHEN sa.Attendance_Date = '20160205' Then sa.Attendance_Status END) As '20160205'
    , MAX(CASE WHEN sa.Attendance_Date = '20160206' Then sa.Attendance_Status END) As '20160206'
 FROM student_attendance sa
 INNER JOIN courses c
     ON c.Course_Id = sa.Course_Id
 INNER JOIN students es
     ON es.Student_Id =  sa.Student_Id
 WHERE sa.Course_Id = 1
 ;


Azure Data Sync Between Two Azure Databases I would like to ask if I use Azure data sync with my two azure databases is there any way that I can still download data tier and install it on my local machine. I am planning to create a sync between some of my tables inside my database A and copy its record to the database B at a specific time daily. is this possible on Azure Data Sync?

Yes, you can sync up to 500 tables on a sync group (per user). So you just need to create a sync group and specify which tables you want to sync. You can have up to 5 sync groups per database.

You must have a hub database (it must be a SQL Azure Database) and you can have one or more member databases (let's say one on Azure SQL Database and another member SQL Server database on your local machine).


Recommendations on replicating an RDS PostgreSQL instance? At my company, we are looking for alternatives to replicate a PostgreSQL instance into another database. In our scenario, we have a production PostgreSQL 9.5 database server, with new entries coming in all the time, and we must sync them to a replica server.

But we are facing some challenges:

We need to be able to write to the replica database, as we want to include data from other sources as well. We've tried to use the AWS's own PostgreSQL, but apparently it doesn't allow writing to the replica.
It needs to replicate PostgreSQL special types, such as computed indices, enum types and JSON columns. Some of the tools we've researched don't do well with these types.
Also, it's necessary this tool operates without super user permissions, as the database is hosted in AWS RDS, and it doesn't give you super user permissions.

We've researched several solutions, and even wrote our own, but we are having consistency problems in the replica (i.e. some failed patches), as well as some ALTER TABLEs that failed to be replicated. We still haven't figured out a way to isolate and correct these problems in the source code.

Given all the situation, we are researching alternatives and would love to hear from people who had a similar problem.

I think that pglogical extension could be be used to solve your issue - in the future, when it is extended to allow a replication user. Currently it requires superuser privileges.

  pglogical is a logical replication system implemented entirely as a
  PostgreSQL extension.

Also, from their documentation:

  Fully integrated, it requires no triggers or
  external programs. This alternative to physical replication is a
  highly efficient method of replicating data using a publish/subscribe
  model for selective replication.

  Use cases supported are:

  Upgrades between major versions (given the above restrictions)
  Full database replication
  Selective replication of sets of tables using replication sets
  Data gather/merge from multiple upstream servers


How to refactor this subquery to be written only once? procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select T1.*
from T1
where T1.x=p1
    and (
        p2 in (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
    or
        not exists (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
        );
end;

The subquery is the exact same both times. Is there a way to refactor it to be specified only once? On Oracle at least.

One way would be to use it as a join:

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select unique T1.*
from T1
left join T2 on T2.someId=T1.SomeId and T2.y=12
where T1.x=p1
    and (
        p2 = T2.z
    or
        T2.z is null -- T2.z is a NOT NULL column
        );
end;

But this is not equivalent (I added UNIQUE to filter out rows multiplied due to join, but this also removes possible correct duplicate rows from T1).

PS: Made an SQL Fiddle with the two queries from above and from the two answers below.

I came up with this:

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
with sq as (SELECT z, someId from T2 where T2.y=12)
select T1.*
from T1
where T1.x=p1
    and (
        (p2,T1.someId) in (select * from sq)
    or
        not exists (select * from sq where sq.someId=T1.someId)
        );
end;


How to convert SQL Server .MDF database to Mac OS PostgreSQL database on Mac OS X? I need to import a SQL Server database which is in the .mdf format into a PostgreSQL database. My system is Mac OS X.

How to do it in the easiest way?

You'll need to attach the database to a Microsoft SQL Server database, the export the data into the Postgres database.  Assuming that you've got the Windows Postgres ODBC drivers handy you can use SQL Server's Import/Export wizard to copy the data into the Postgres database.


Where can I get a proper dataset for performing Normalization techniques? I'm new to databases and I just started with learning different techniques of Data Warehousing. I was given an assignment to perform Normalization techniques on a dataset of at least 1000 rows with minimum of three tables.

I'm not able to find any such data for the same.

I tried data.world, kaggle and uci but all in vain. Also, can I perform the normalization techniques using MySQL Workbench? Please help me out.

Check out Percona's tpcc-mysql. Here you can generate a dataset of any size. It should do what you ask.


PostgreSQL WAL Archival Best Practices? The postgresql documentation gives an example archive_command of
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix

but adds the disclaimer saying

This is an example, not a recommendation

As someone who's about to setup a postgresql database, I'm wondering what the best practice would be for handling WAL archival? Forgive me if this is already a question that's been beaten to death, but my stackexchange search-foo is failing me. There *is a few recommendations for using pgBarman. Is it still a good direction to go?
Coming from a setup where MSSQL backups were handled by IT with a full backup taken daily in the morning with hourly transaction logs. I'm wondering what would be an equivalent setup in postgresql to give the ability of point-in-time recovery for the past week or two?

The main problems with the example from docs is that it doesn't force the copy to be synced to disk before it reports success, meaning the database might be allowed to delete the original file, but then a poorly timed OS crash means the copy never reaches disk safely and so is not truly available.
Another problem is that if the copy fails (or succeeds, but reports to have failed, because something prevented the success message from being heard) but first created a file with the correct name, then when it is tries again the test command will cause it to keep on failing forever, until someone manually intervenes.  If you remove the test, then if you accidentally configure two servers to archive to the same spot, they can silently overwrite each others files.  So a complete solution will only allow WAL files that belong to the proper basebackup to get saved in the first place, and will allow a file to be saved more than once as long as the new version is identical to, or an extension of, the earlier one.  Of course you generally want to archive to a different machine anyway, so the example archive command doing a cp operation is often just generally wrong in that regard.
pgbarman has its flaws and limitations, but does get around the problems mentioned above.  pgbackrest is another option (one I have not played around with).  If you use log shipping with pgbarman, you would need to set archive_timeout to one hour.  If your server doesn't see much activity, which can mean a lot of  WAL files with little useful data in each hanging around.  You can get around this by using streaming mode instead.
Another option is to use pg_receivewal.  Or to use a streaming replica with a replication slot and with archive_mode set to 'always'.  Then the replica will create the archive for you. You still have the problem though that the archive_command on the streaming replica still needs to force the copy of the file to be synced before reporting success, if you want it to be bulletproof.


Zoned and hashed sharding of existing, non-empty collection I am trying to shard a existing collection. Collection is pretty big (about 100gb). Sharding should be done by 2 fields. First one is zone ['Europe', 'America'] and second one is _id.
So my sharding index looks like this {"zone": 1, "_id": "hashed"}.
I have followed these steps https://docs.mongodb.com/manual/reference/method/sh.updateZoneKeyRange/#compound-hashed-shard-key-with-non-prefix-hashed-field. This works only on empty or non existing collection. My question is is this type of sharding possible on collection that is not empty.
I am using mongo version 4.4

For me it is working. You only need to create the index { "region": 1, "_id": "hashed" } manually.
use data
db.collection.insertMany([{ region: 'EU' }, { region: 'US' }])

sh.addShardToZone("shard_01", "Europe")
sh.addShardToZone("shard_02", "America")

sh.updateZoneKeyRange(
   "data.collection",
   { "region": "EU", "_id": MinKey },
   { "region": "EU", "_id": MaxKey },
   "Europe"
)

sh.updateZoneKeyRange(
   "data.collection",
   { "region": "US", "_id": MinKey },
   { "region": "US", "_id": MaxKey },
   "America"
)

db.collection.createIndex({ "region": 1, "_id": "hashed" })
sh.shardCollection(
   "data.collection",
   { "region": 1, "_id": "hashed" }
)

Extract from sh.status():
data.collection
  shard key: { "region" : 1, "_id" : "hashed" }
  unique: false
  balancing: true
  chunks:
          shard_01  2
          shard_02  1
          shard_03  1
          shard_04  1
  { "region" : { "$minKey" : 1 }, "_id" : { "$minKey" : 1 } } -->> { "region" : "EU", "_id" : { "$minKey" : 1 } } on : shard_04 Timestamp(3, 0)
  { "region" : "EU", "_id" : { "$minKey" : 1 } } -->> { "region" : "EU", "_id" : { "$maxKey" : 1 } } on : shard_01 Timestamp(4, 1)
  { "region" : "EU", "_id" : { "$maxKey" : 1 } } -->> { "region" : "US", "_id" : { "$minKey" : 1 } } on : shard_03 Timestamp(4, 0)
  { "region" : "US", "_id" : { "$minKey" : 1 } } -->> { "region" : "US", "_id" : { "$maxKey" : 1 } } on : shard_02 Timestamp(2, 0)
  { "region" : "US", "_id" : { "$maxKey" : 1 } } -->> { "region" : { "$maxKey" : 1 }, "_id" : { "$maxKey" : 1 } } on : shard_01 Timestamp(1, 5)
   tag: Europe  { "region" : "EU", "_id" : { "$minKey" : 1 } } -->> { "region" : "EU", "_id" : { "$maxKey" : 1 } }
   tag: America  { "region" : "US", "_id" : { "$minKey" : 1 } } -->> { "region" : "US", "_id" : { "$maxKey" : 1 } }

Please be aware of Balancing failed when moving big collection, my collection is also around 100GB, so you may face the same issue.


Strange use of Database Schema (Namespace) Database schema helps grouping tables, or help identify multiple tables that have the same table name. Most of the schema usage that I have found so far are for grouping tables, isolating tables among the users (or talents), allow different permission based on the schema.

In my case, I have a predefined table definition, and I like my user to be able to duplicate this table multiple times in my application. Obviously, I can't have the user create the new tables with the same name. My original idea is to allow the user specify a table name suffix:

e.g.
[TableA]
[CustomName_TableA]
[OtherName_TableA]

However, this requires a bit of code change since the table name is hardcoded in the data access layer of the application.

What are the drawbacks, or potential issues if I create a new schema when a user duplicate the table, instead of dynamically generate a new table name?

e.g.
[dbo].[TableA]
[CustomName].[TableA]
[OtherName].[TableA]

I really disagree with your statement that "database schema helps grouping tables, or help identify multiple tables that have the same table name." Actually database schemas were originally created to address security issues and security groupings. Unfortunately, in all of the worksplaces and projects I've been involved with, schema is synonymous with namesspaces (group names). This is just bad design in my opinion.

You want to create a new schema for every user, but how many users will there be? You don't want 1000 schemas in your database. Schemas are registered in the metadata as unique objects, and there is a limit 2,147,483,640 - they are considered objects and the more objects you have the higher the overhead. The limits are set based on the SQL product you use. The number I gave was for SQL Server


Get interactive psql shell after executing queries I want to execute a set of commands before executing other queries using psql

The following is what I want to execute

psql << EOF
\setenv PAGER 'pspg -s 0'
\pset pager always
EOF

After executing this, I want to have an interactive shell with these parameters set.

I am looking for how to get interactive shell after this.

psql has a built-in feature to set defaults: the ~/.psqlrc file.

You can put any command in there that should be executed when you start psql

So just create that file and put:

\setenv PAGER 'pspg -s 0'
\pset pager always

into it.


Running Aggregation logic on multiple shards of MongoDB Suppose we have 5 shards in MongoDB having collection of data, and I have to write an Aggregation logic which should work on each of 5 shards in my cluster and collect data from these shards. Should it be taken care at the application developement side? like connecting to each Shard separatley by its shard key and get data Or once I write the aggregation logic and deploy my jar on this cluster it will be handled my MongoDB itself to read from these shards and work aggregation logic on these shards data?

Like in Cassandra MapReduce it will be handled by a Job tracker to send job to appropriate nodes.

You don't ever need to connect to specific shards in a MongoDB database. Instead, you connect to a mongos instance that handles the routing for you.

In your case, you would connect to the mongos instance normally, by typing mongo into the terminal, or through a language specific client. You send your aggregation operation to the mongos instance, and it will distribute the operations to each of the shards and combine the result at the end.

Sharding is, in many ways, transparent to the user (though certainly not transparent to the database architect): many queries that run on a non-sharded mongod instance will run in the same manner on a mongos instance.


Starting an Oracle database without a spfile & pfile? How can I start an Oracle database if both the spfile and pfile are not present, and a backup is also not available?

You can try it with a basic pfile file like this:

SID.__db_cache_size=180355072
SID.__java_pool_size=71303168
SID.__large_pool_size=4194304
SID.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SID.__pga_aggregate_target=335544320
SID.__sga_target=1001088640
SID.__shared_io_pool_size=0
SID.__shared_pool_size=419430400
SID.__streams_pool_size=50331648
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'
*.control_files='/u01/oradata/SID/control01.ctl','/u01/oradata/SID/control02.ctl','/u01/oradata/SID/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/SID/cdump'
*.db_domain='your.domain'
*.db_name='SID'
*.db_recovery_file_dest='/u01/oradata/SID/flash_recovery_area'
*.db_recovery_file_dest_size=1000G
*.db_unique_name='SID02'
*.pga_aggregate_target=335544320
*.sga_max_size=1005306368
*.sga_target=1001088640
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTS02'

Change the values so that they reflect the values (at least replace SID with your SID) that you need.


SQL Server 2016 Standard Version Features Evaluation We're looking to test the new Basic Availability Groups (BAG) feature in SQL Server 2016 Standard Edition. So far, I have not been able to find an evaluation copy of Standard.

Is there a way we can get our hands on one to test if the BAG would suffice our needs rather than having to purchase the Enterprise Edition.

If not, is there a way we can set up the Evaluation Enterprise Edition to be just like how the Standard Edition will be once bought? We don't want to be running into any surprises later on and have thorough understanding of the differences the Standard Edition has with the Enterprise Edition.

Do you have an MSDN License?  Or could you start a 30-day free trial with Azure?  If so, you can easily set up a VM with SQL Server 2016 Standard edition installed:

This will help ensure no surprises with this or other Enterprise features.


Combining Postgres jsonb query statements I have test table with votes column and I want to upsert nested jsonb object into it. My current query:

INSERT INTO test (post_id, username, votes)
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb)
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE
  SET votes = test.votes ||
('{"commentid" : {"vote": true} }'::jsonb || test.votes->'commentid')
  WHERE NOT test.votes @> EXCLUDED.votes
  RETURNING *;

test_pkey constraint is PRIMARY KEY (post_id, username)

How can I construct this statement

('{"commentid" : {"vote": true} }'::jsonb || test.votes->'commentid')

So postgresql would read it as

'{"commentid" : {"vote": true, test.votes->'commentid'} }'::jsonb

is it possible to somehow combine it like in json?

What this is supposed to do is make "commentid" key-value pair if one doesnt exist and combine them if it does.

I have tried using different variations of jsonb_set like

jsonb_set(test.votes,'{"commentid", vote}','true', true);

or

jsonb_set(test.votes,'{"commentid"}', test.votes->'commentid' || '{"vote":true}', true)

But the first one erases the whole votes object if "commentid" doesn't exist and second one replaces the whole votes object with second part of the statement.

If it's possible to make it work this way, I'd be able to return this data straight to frontend without any modification

What I ended up doing using inspiration from MatheusOl answer.

INSERT INTO test (post_id, username, votes)
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb)
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE

  SET votes = test.votes || coalesce(test.votes->'commentid', '{"commentid" : {"vote: true"}}'))
   || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb)

WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;

When using his answer, "commentid" would still be overwritten, but I don't really understand why.

This doesn't do things very efficiently, as it recreates the "commentid" key every time as a new object, but in my case it will have significantly more reads than writes, so it should be fine


Different manifestations of the same logic yield very different execution times Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested.

For every widget in the population, we want to determine the most recent test.

The two queries below show two different approaches to the solution.

However, the first query runs like lightning, whereas the second query seems to spin forever.  Why?  Is there something obviously inferior about the second query, or is this just one of those optimizer flukes?

We realize that having an inequality in the join predicate is not optimal because it can't take advantage of pre-ordering and merging, but it seems to us that the correlated subquery is just as taxing because it has to run the inner query for every row of the outer query.  So, to our minds, both queries should be equally inefficient.

SELECT   A.serial_ID,
         (SELECT MAX(B.serial_ID) FROM widgets_tested AS B
             WHERE A.serial_ID >= B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
;

SELECT   A.serial_ID,
         MAX(B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
         INNER JOIN
         widgets_tested AS B
         ON A.serial_ID >= B.serial_ID
GROUP BY A.serial_ID
;

Note:  It may not be possible to give more information about the DDL statements of the relevant tables and execution plans because the widgets tables are just sanitized stand-ins for the original tables. We just wanted to ask the community if there is something obviously wacky or inefficient about the logical structure of the second query.

Since you are not allowed to share any more information on the queries such as sample data + table definition, these tests will be on own fabricated data and with or without indexes. Test data below.

YMMV.

The inner join + group by query

The 'bad' query puts everything in an index spool, and subsequentially executes a nested loop join on the exponential 8M rows

Whole plan

Index Spool properties.

This index spool creates over 8M records as a result of the <= join.
The nested loop operator over 8M rows into a stream aggregate will also decrease performance.

The derived table

The 'better' plan

The stream aggregate is used before the nested loop operator is used. Resulting in a much smaller join.
There is also a difference in stream agg operators, this one supports the MAX() function while the other one (above) supports the GROUP BY statement + The partialagg of MAX().

TL;DR

My guess is that the huge nested loops operator used in the plan of the inner join query factors in for the excessive time.
The difference with the good plan being that the Stream aggregate happens before the nested loops operator.
Also as @ypercubeᵀᴹ mentioned, they are not logically similar.

Adding indexes

CREATE INDEX IX_widgets_population_serial_ID on widgets_population(serial_ID);
CREATE INDEX IX_widgets_tested_serial_ID on widgets_tested(serial_ID);

The 'good' query gets improves a lot after an index, using 15ms cpu time

The reason that this change is so drastic, is that the TOP operator is used now.

This top operator is getting the first value that matches the <= condition by using the index's sort order, for each value in widgets_tested.

The 'bad' query, not so much

Executing the join before executing GROUP BY & MAX(), where the MAX() is calculated before the join in the good plan.

Data used for this example

CREATE TABLE  widgets_population(serial_ID int)

CREATE TABLE  widgets_tested(serial_ID int)

SET NOCOUNT ON
DECLARE @I INT = 1;
WHILE @I  <= 1000
BEGIN
INSERT INTO  widgets_population(serial_ID)
VALUES(@I)
INSERT INTO  widgets_tested(serial_ID)
VALUES(@I)
SET @I+=1
END

INSERT INTO  widgets_population(serial_ID)
select serial_ID+1000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+1000 from widgets_tested

INSERT INTO  widgets_population(serial_ID)
select serial_ID+2000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+2000 from widgets_tested


Database Migration - Create New Filegroup and Move Objects We have built a new SQL Server Database server instance. I am migrating our databases from existing instance to the new server instance using Backup and Restore.

One existing database has a single FILEGROUP. Is it possible to create a new FILEGROUP and move the data to this?

I cannot move the existing data file to the new FILEGROUP. So is my only option to manually script all Database Objects to the new FILEGROUP? Can I then delete the now empty FILE after moving the objects?

Restore Script:

;RESTORE DATABASE [TestDB] FROM DISK = 'I:\testDB.bak'
    WITH REPLACE, FILE = 1, NORECOVERY, STATS=10,
    MOVE 'TestData' TO 'H:\Test.mdf',
    MOVE 'Test_log' TO 'E:\Test_log.ldf'
;RESTORE DATABASE [TestDB] WITH RECOVERY

This can be done if you have clustered indexes (either as the primary key constraint or on their own) on the tables that you wish to move.

So restore the database, add the new filegroup and then...

For primary keys:-

 ALTER TABLE [SCHEMA].[TABLE NAME] DROP CONSTRAINT [CONSTRAINT NAME]

 ALTER TABLE [SCHEMA].[TABLE NAME] ADD PRIMARY KEY CLUSTERED
 (
   [Column]  ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
 GO

For clustered indexes:-

CREATE CLUSTERED INDEX [CONSTRAINT NAME] ON [SCHEMA].[TABLE NAME]
(
    [Column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
GO

Please be warned that this may take a while to do on a VLDB!


MySQL DB Causing Spike-up on Windows Server CPU This is a Magento application connected to MySQL Db. When I ran show engine innodb status\GI get a list of TRANSACTIONSand it looks like some of them are pending and others are in a DEADLOCKand it doesn't matter if I kill them, they keep coming back. What can I do to prevent that? Help!
Here's the log:

---TRANSACTION 284071526856416, not started estimating records in index range
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526852056, not started estimating records in index range
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526902632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526853800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526845080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 39922654, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 1654, OS thread handle 6724, query id 46131 localhost ::1 root updating
UPDATE `log_visitor` SET `session_id` = 'imtbh6fl9rldnoi44vjnmepf70', `first_visit_at` = '2020-01-14 19:49:19', `last_visit_at` = '2020-01-14 19:49:27', `last_url_id` = '0', `store_id` = '1' WHERE (visitor_id='1763150')
---TRANSACTION 39922653, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1669, OS thread handle 5184, query id 46128 localhost ::1 root updating
UPDATE `log_visitor` SET `session_id` = 'fvobv8l301a79ece0006vot6a5', `first_visit_at` = '2020-01-14 19:49:23', `last_visit_at` = '2020-01-14 19:49:27', `last_url_id` = '0', `store_id` = '1' WHERE (visitor_id='1763153')
---TRANSACTION 39922646, ACTIVE 2 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1623, OS thread handle 5048, query id 46079 localhost ::1 root updating
UPDATE `core_session` SET `session_expires` = '1579034965', `session_data` = '_secure_cookie_check|s:32:\"919e25a2e43969e5d6bfbede7bb4e55d\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:14:\"159.138.159.72\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s:
---TRANSACTION 39922645, ACTIVE 2 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1624, OS thread handle 6820, query id 46078 localhost ::1 root updating
UPDATE `core_session` SET `session_expires` = '1579034965', `session_data` = '_secure_cookie_check|s:32:\"d1e4c0600c606a203e398f2844d33ada\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:15:\"159.138.153.198\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s

If not impacting business, have you tried to kill the connection app-side?

We had an issue with locked/active sessions from an app to a MSSQL DB, however the only way we could bypass was to break the active connection from the app to the DB via failover. That was only because it shifted resources from node 1 to node 2. We attempted to kill the connection via MSSQL queries but couldn't get it to stop that way, so failover ended up being the solution for us.

Note: I am still new to DBA, but figured it's worth a try to share what I've experienced in my short time in this role.


Azure SQL Db Linked Server - Secured Connection Methods? The developers want a linked server from our on-prem server to our Azure SQL Db (DBaaS) in order to complete a nightly data pull into our data warehouse.

I'd much rather go the route of SSIS but don't have the time to properly develop the package or teach them how to support it.

My main concern at this point is ensuring the data is encrypted in transit.

Once the linked server is in place along with a corresponding Agent job, my options appear to be:

sqlcmd (with the -N switch, "...used by the client to request an encrypted connection."
Invoke-SQLcmd Azure PowerShell cmdlet (with the -EncryptConnection switch)
OR... Azure SQL Data Sync (Hub to Member), which as of this writing is still in preview but looks promising!

Are there any gotchas or special considerations for these methods that I should be aware of?

The main difference between this answer and the previous one offered is the (what I find to be incredibly important) acknowledgement of MitM attacks rather than a blanket statement which overlooks caveats.
For posterity, from Securing your SQL Database in the documentation:

Important
All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is "in transit" to and from the database. In your application's connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal), otherwise the connection does not verify the identity of the server and is susceptible to "man-in-the-middle" attacks. For the ADO.NET driver, for instance, these connection string parameters  are Encrypt=True and TrustServerCertificate=False. For information about TLS and connectivity, see TLS considerations.

For other ways to encrypt your data, consider:

Cell-level encryption to encrypt specific columns or even cells of data with different encryption keys.

If you need a Hardware Security Module or central management of your encryption key hierarchy, consider using Azure Key Vault with SQL Server in an Azure VM.


How to get cross apply to operate row by row on a view? We have a view that is optimized for single item queries (200ms no parallelism):

select *
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

It also works on small sets of static ids (~5).

select *
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

However if the objects come from an external source, then it generates a slow plan.  The execution plan shows that the execution branch for the view part is ignoring the predicate on ObjectId whereas in the original case it uses them to perform index seeks.

select v.*
  from
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

We don't wish to invest in "dual" optimizing the view for non-singular cases.  Rather, the solution we "seek" is to repetitively call the view once per object without resorting to an SP.

Most of the time the following solution calls the view row by row. However not this time and not even for just 1 object:

select v.*
  from
     (
       select top 1 ObjectId
         from Objects
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId
       order by v_.SomeField
    ) v;

At one time I thought there was a claim  that cross apply was guaranteed for row by row execution when it calls a UDF but this also failed:

create function FunctionCallingView(@pObjectId bigint)
returns table
as
return select *
  from OptimizedForSingleObjectIdView
 where ObjectId = @pObjectId;

select v.*
  from
     (
       select top 1 ObjectId
         from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

Adding option(force order) did not help--however there are two hash hints in the view already. Temporarily removing them did not help and slows down the single case.

Here is a snippet of the estimated plan for the function based slow case.  The estimation of 1 row is correct.  Far off to the right (not shown) is where there is a seek predicate that does not include that top 1 result.  This seems similar to other cases we have where singular probed values from table seeks are not used as seek predicates elsewhere.

It is not possible to fully guarantee evaluating the view per row of the outer query, without using something that introduces a new T-SQL execution scope, for example a non-inline (multi-statement, BEGIN...END) table-valued function. This is pretty much the advice given in response to your previous question How to use merge hints to isolate complex queries in SQL Server.

  At one time I thought there was a claim that cross apply was guaranteed for row by row execution when it calls a UDF

This does not apply to inline table-valued functions, since the definition is expanded into the calling query before optimization begins.

That said, there are some things you can do to strongly encourage the desired outcome.

  The execution plan shows that the execution branch for the view part is ignoring the predicate on ObjectId whereas in the original case it uses them to perform index seeks.

You are expecting the ObjectId values to be evaluated "inside the view" using index seeks for each driving row. This is the correlated nested loops join (apply) style of execution. Note that using the APPLY T-SQL language element does not guarantee that physical execution will use the apply style.

It very much sounds as if SQL Server is choosing to execute with the ObjectId values tested at the Nested Loops Join operator instead. This is a non-correlated, or naive, nested loops join execution pattern.

Most likely, this is caused by the join hints you are using inside the view. Join hints are generally to be avoided, because they greatly restrict optimizer freedom, and not just for the physical type of join. In particular, join hints also force the order of joins for the whole query (just as if you had use a FORCE ORDER hint) and prevent several optimizations related to aggregation placement and strategy, and much else besides.

If you really must have join hints inside a view (something I would strongly suggest you avoid in general), you might find the most reliable way to get the plan shape you desire is:

Create an inline (RETURNS TABLE) function using the view definition (not referencing the view).
Provide @ObjectId as a parameter to the function.
Placing a predicate using the parameter within the function body in a way that makes an index seek more likely.
A FORCESEEK table hint inside the function may be used if truly every use should result in a seek.
Call the new inline function using APPLY.

I generally dislike using specific syntaxes and hints in an attempt to force a certain physical plan shape. You may have more general success by parameterizing queries and guaranteeing the plan shape using a plan guide.


Time taken to execute query seems longer than time measured in query...? I'm trying to optimise a stored procedure.  I have inserted some manual profiling into my SP by just using a date variable and then displaying time elapsed by using datediff.

E.g.

DECLARE @MyDate datetime = GetDate()
PRINT '..profiling text...'
PRINT DateDiff(ms, @MyDate, GetDate())
SET @MyDate = GetDate()

<execute some actions here>

PRINT '..elapsed time...'
PRINT DateDiff(ms, @MyDate, GetDate())
SET @MyDate = GetDate()

When I run this in a query window of SQL Server Management Studio the total elapsed time of my profile equates to about 5 seconds.  However, the duration of the whole query is more like 14 seconds as indicated in the query properties in the bottom right corner (and when I time it manually with a watch).

Is there any reason why this would be happening?  There is nothing wrong with where I have placed or declared my date variables. There is definitly a mismatch in time output via datediff and time taken for the query.

It should be noted also that while optimising I am not returning any large tables of data.  I'm simply inserting them into temp tables to ensure that I don't have the overhead of returning results.

The elapsed time measured by the T-SQL batch will include only execution time.

The elapsed time measured by the client application will include not only execution time but also compilation time and network latency.

Run your batch with SET STATISTICS TIME ON to see if the unaccounted time is due to compilation. With SSMS, you can also include more detailed client statistics (Query-->Include Client Statistics ).


SQL select default parameter I have a table with columns like name, age, email etc. For some columns I also have parameters to control the behaviour of queries. I want to show the column's value if the corresponding parameter has the value 0. If that parameter has value 1 I don’t want to show that column.

For example, if I have data

name  age  show_email  email
abc,  22   1           abc@gmail.com  -- parameter = 1 do not show this value
def,  33   0           def@gmail.com  -- parameter = 0 => show this value

Then output of select name, age, email from MyTable1 will be

name  age  email
abc,  22   NULL          -- NULL because of parameter value.
def,  33   def@gmail.com

Use the case statement

select name, age,
 case show_email when 0 then email else null end
from my_table;


MySQL - is there a way to implement a CHECK constraint after creating the table? I created a table and after entering all the data, I found that I had missed a CHECK constraint in the CREATE TABLE statement.

create table jims_faculty
( fac_id int primary key,
fac_name varchar(15) not null,
fac_address varchar(40),
fac_contact_no int,
fac_qualification varchar(30) default 'post graduate',
fac_designation varchar(30) default 'assistant professor',
Fac_dept varchar(15)  check(fac_dept in ('it' , 'mgmt')) ,
fac_experience int ,
fac_date_of_joining date not null,
fac_aadhar int unique);
fac_aadhar int unique);

The fac_experience field should not be less than 2 years is the condition that should be in the check constraint.

Should I recreate the table?

Or is there another possibility ?

Assuming you are using MySQL >= 8.16 when MySQL finally implmemented CHECK constraints, you can do this following:

ALTER TABLE <table_name>
ADD CHECK (check criteria)

so in your case, it would be

ALTER TABLE jims_faculty
CHECK (fac_experience >= 2)

If you want to name the constraint, you can do this:

ALTER TABLE jims_faculty
ADD CONSTRAINT chk_fac_experience_gt_2 CHECK (fac_experience >= 2);

From the manual (incredibly) Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored:

The information in this post was largely gleaned from here.


Why is PostgreSQL blocking creation of a new table on update on foreign table? I understand why an UPDATE query on tableA can block an ALTER TABLE DDL statement on the same tableA.
ex:
UPDATE tableA SET column1 = 'test' WHERE id = 1;`

But I am trying to create a new table, say tableB, that only references the id of tableA with ON DELETE CASCADE.
CREATE TABLE tableB id REFERENCES tableA(id) ON DELETE CASCADE NOT NULL`;

This query waits until the update is complete.
I was able to figure that out by getting the blocking pids through this query
SELECT pg_blocking_pids(<pid of the create table query>);

I got the pid of the update query as the blocking pid.
Why is creating a new table blocked by the UPDATE query of the referenced table?

If you are executing both statements in a single transaction, they won't lock each other.
If you execute the statements in different transactions, one will have to wait until the other is done. The reason is that foreign key relationships are implemented by system triggers in PostgreSQL and creating a trigger on tablea requires a SHARE ROW EXCLUSIVE lock on the table.
If you look at the lock compatibility table, you will see that that lock conflicts with the ROW EXCLUSIVE lock required by INSERT, UPDATE and DELETE.
Make sure that your UPDATE are running in short transactions, then that won't be a problem.


Optimising a sql correlated subquery statement I am updating a column on tableA with data from tableB like this:

update tableA
  set bKey = select bKey from tableB B where B.colB = tableA.colB

bKey has a unique index on tableB.

Which is the better optimisation for this query, indexing tableB on ColB or (ColB,bKey) and why?

I am interested in answers for any database.

Neither.

Index ColB INCLUDE (bKey)

Since you aren't filtering on bKey you don't need it at all the nodes of the index, just the leaf level.

If you only index on ColB then you will still have to pay for a key lookup to get the value of bKey from the clustered index.

Also is there a reason this isn't using a JOIN instead of a correlated subquery?

UPDATE a
SET a.bKey = b.bKey
FROM TableA A
INNER JOIN TableB B
ON A.ColB = B.ColB


SQLCMD Returning Named Pipe Error I am trying to run any command from SQL Server Express from the command line using sqlcmd. Here is an example.

sqlcmd -U myname-P mypwd -S mysys/SQLEXPRESS -d MASTER -Q "Select count(*) from sys.databases"

I keep getting the error below. It appears to be trying to connect using named pipes, but I have configured SQL Express to use TCP.  How can I get SQLCMD to not use the named pipes?

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider:
    Could not open a connection to SQL Server [67]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server :
    A network-related or instance-specific error has occurred
    while establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online..

Server name and instance name should be separated by a backslash, not a forward slash. Try:

sqlcmd -U myname -P mypwd -S mysys\SQLEXPRESS -d master -Q "Select count(*) from sys.databases"


Find field that was changed I have database with log backup every 15 minutes.

I believe some data in one table was changed 10 hours ago.

Is there any way I can find that query and located changed/updated data in log backup file?
If not is there any other way?

TNX!

SQL Server doesn't log the query text of an update. You can find the log records using 3rd party log reader tools (or fn_dblog), and you may be able to find out who did it (depends on authentication mode and how your users log in), and you can find the previous version of the data by restoring to a point in time before the update, but you're not going to be able to find the query after the fact.

It may be time to start taking away rights, adding auditing (the full-blown auditing feature or your own triggers), or forcing data manipulation through stored procedures and rolling your own logging.


How to get number of PK used as FK? How to get number of PK used as FK?

Table 1 has a column ID (PK).
Table 2 & 3 has a column ID (FK - refers Table 1 PK).

How to get Value 1 (PK) has used in table 2 & 3 or not without referring table 2 or 3 ?

SELECT id
FROM table1
WHERE EXISTS (SELECT 1
              FROM table2
              WHERE table1.id = table2.id)
  AND /* OR */
      EXISTS (SELECT 1
              FROM table3
              WHERE table1.id = table3.id)

When you need ids which are present both in table1 and table3, use AND.

When you need ids which are present at least in one of these tables, use OR.


MS Access Query to update Trying to update Packet.MSGState to P where if not R or D and it is Y in table Packet.MSGState

UPDATE Packet SET Packet.MSGState = 'P'
Where (MSGState <> 'R' and  MSGState <> 'D') and
Exists
(Select  MSGTO.Selected
From MSGTO
Where MSGTO.MSGTO = Packet.MSGTO and  MSGTO.Selected = 'Y')

Thanks Kevin

Use IN clause as following

UPDATE Packet SET Packet.MSGState = 'P'
Where (MSGState <> 'R' and  MSGState <> 'D') and
Packet.MSGTO IN
(Select  MSGTO.MSGTO
From MSGTO
Where  MSGTO.Selected = 'Y' and MSGTO.Selected IS NOT NULL )


How to decompose the relation R(a,b,c,d,e) whith following FDs into BCNF? I have  a FDs

ABC->DE
AB->D
DE->ABCE
E->C

My trial :

Step 1 :

A+ = A
B+ = B
C+ = C
D+ = D
E+ = EC
AB+ = ABD
ABC+ = ABCDE
DE+ = ABCDE

From above we get ABC,DE are our candidate keys

Step 2 :

ABC -> DE ==> no violation. bcoz abc is key.
AB -> D ==> violation.
(ABD)(CE)

in (ABD) AB is a candidate key. So it is in bcnf
in (CE) C is the candidate key. So it is in bcnf.

I have done here. But unable to process further. There is confusion after this that the step 2 is right or not.
Can anyone solve this?

In your relation schema, there are three candidate keys: ABC, ABE and DE.

Since, for instance, AB → D violates the BCNF, we can decompose the original relation in:

R1(ABD) (with dependency AB → D and candidate key AB), and
R2(ABCE) (with dependencies E → C and ABC → E, and candidate keys ABC and ABE)

this is because we decompose in two relations, AB+ and R - (AB+) + AB.

The second relation is still not in BCNF, since in E → C the attribute E is not a superkey.

So we can apply again this method to decompose R2 in:

R3(CE) (with dependency E → C and candidate key E)
R4(ABE) (with no dependency and candidate key ABE)

Both are in BCNF and the final decomposition is constituted by R1, R3, R4.

Finally, it is worth to note that the following dependencies:

DE → AB
ABC → E

are lost in the decomposition.


Retrieving page contents from SQL, Postgresql? Data is stored over pages and not necessarily one row or one column or one attribute maps to one page. Is it possible to use some query to extract data from a specific page in a database environment?

pageinspect module

Using PostgreSQL, there is an extension that does all of this. Your question is vague so I won't even try to address the particulars here, nor is it reasonable to enumerate everything this extension does, perhaps it's enough to say that it has 17 functions and as far as I know includes all of the mechanisms available to end users to introspect pages (not saying it does everything you want).

You may want to read Database Page Layout


How Wide-columns databases achieve dynamic columns on the storage layout? As known, in relational databases, when adding a new column, data must be reallocated (https://stackoverflow.com/questions/463677/alter-table-without-locking-the-table) to maintain a single row contiguous on disk.

I would like to understand how this is achieved on wide-columns storages such as Cassandra, which are sparses and can handle lots of dynamic columns insertions (http://www.datastax.com/dev/blog/thrift-to-cql3 (Dynamic Column family))

Thanks!

Although Cassandra allows the definition of "columns" within a "table", these are much less strict than a relational database schema.  As it says here

  different rows in the same column family do not have to share the same set of columns, and a column may be added to one or multiple rows at any time.

In this world, the new value is only added to the row when that row is re-written.  The software knows where it has written the row and maintains any forward pointers to be able to return the complete row on demand. It may choose to delete the old version, freeing that disk space for reuse, and write the new row elsewhere in contiguous storage. There is no implication that making a schema change in one place automatically propagates to other, similar rows.

For key-value stores the requirements are even more lax.  What constitues a "column" is entirely up to the applicaiton.  All the storage engine sees is a blob of bits, which it writes to disk and indexes.  How or where on disk it holds these bits is neither here nor there to the application.

Not all relational databases require all parts of all rows to be contiguous.  Oracle allows a single row to span multiple pages.  SQL Server has off-page pointers for long text columns, and Filestream allows for storage in the OS's filesystem outside of the DBMS.


Query customers attributes I need to create a system where the user creates dynamic filters based on our customer's attributes. There are more or less 30 possible filters and 30 millions of customers, but the number of customer increase every day and the attribute value can change every day too, so we have insert and updates in these set of data every day. Another thing is that I can create a new filter or remove.
In this case we can use a relational database like Oracle and create a index for every colunm, but with inserts and updates every day, can I have a problem with performance? Should I use a search engine for this case like Elasticsearch? Or there is a recommended database or architecture for this use case?
I need to return a count of customers that match these filters at most in 5 seconds.
EDIT
Some attributes:

Downloaded the app (boolean)
Credit card limit (number)
Last transaction (date)
Status (text)
Last access (date)
How many times used the credit card (number)
City (text)
Average transaction value (number)

The user can use >, <, =, >=, <= to filter or use IN, like city IN ('New York', 'Seattle')

Easiest solution
(If you have deep enough pockets for Oracle and can handle the audit/licensing requirements)
Create the big ugly wide table, but then use Oracle's in-memory column store to speed your analytic queries.  The high level view is each column is split, dedicated to memory (at least partially), and data compression and some other storage tricks are used to speed lookups.
This works great for the type of ad-hoc workflow you describe and doesn't require much thought about table design other than choosing the correct data types (all else being equal, smaller = faster).
As always, test thoroughly as vendor's claims are often inflated.  But the engineering is sound and others have found column stores to be very beneficial.
Also Easy Solution
(With caveats)
SQL Server has a similar solution, the Clustered Columnstore Index, but there are impacts to update performance that may make it unsuitable to your workflow.  If you have a wide window during which you can perform your updates, it may work well.  Worth a try.
Harder solution
(If you don't have deep pockets or clustered columnstore doesn't work)
Recreate, in part, the same idea as a columnstore but in a typical database table.  Some refer to this as sixth normal form (a timestamp is not required for the definition), others vertical partitioning.  I just tend to think of it as don't read more data than you have to.
You will need an RDMS that has both clustered indexes (index-organized tables) and join elimination.  Right now that's SQL Server, Oracle, Sybase, and DB2.
The idea is you have your Customer table with a very basic amount of data stored with it.  For every attribute, you will create a table Customer_<attribute> that contains the key from Customer and the column(s)* associated with that attribute.  If that attribute is NULL you will not insert a row into that table.**
This seems like a lot of work, but if you have a list of columns and their data types, you can write a script to generate the table creation as well as the required insert/update procedures.
You can also write a script to generate a view with all of your columns (for simplicity's sake you can just LEFT JOIN all the attributes back to the Customer table).  The view will look like a big ugly table, but with join eliminate it will behave much differently.
So if you search for customers on three attributes, the query engine will look at the request, realize it only needs to hit three tables in addition to Customer and off you go.
Some tables will benefit from secondary indexes and you can add them as necessary and if they improve query performance (they don't always).
This solution also has the added benefit of making updates/inserts fairly quick and without requiring the all the data to undergo a lock.
The biggest downside to this solution is you'll get some "expert" who will walk in, proclaim "joins are bad!" and you'll have to patiently explain to them why they are not.
*If one of the attributes is comprised of a foreign key reference to a composite key, you would not split them apart.  Additionally, if those columns will be used together to search for a customer you would include them.
**This pattern requires NULL/NOT NULL be enforced through your update/delete processes.
The "Please Don't Do This"
(Really, please do not do these)
Entity-attribute-value (EAV).  Looks simple.  In practice it's a nightmare, join logic is convoluted, data/relational integrity is impossible to maintain and table locks are disastrous.
Big, ugly, wide table with an index on every column.  Wastes a lot of space, performance isn't great.
Big Data/Whatever is hot this week.  No/limited indexes.  No clear access path to the data.  Data/relational integrity may vary from weak to non-existent.


T-SQL to show Azure SQL Managed Instance vs Azure SQL Database Restated: How can I use T-SQL to determine whether a instance is an Azure SQL Managed Instance or Azure SQL Database?

To programmatically determine the engine edition, use SELECT
  SERVERPROPERTY('EngineEdition'). This query will return '5' for single
  databases/elastic pools and '8' for managed instances in Azure SQL
  Database.

https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017


Collecting parameter values from Extended event session I'm using extended events to trace the statements used by our application.
I have created a session that will collect the information that I want, except that I would also like to know the actual values of the parameters that was used.

I am performing this trace on a non-production environment and any performance hit by the trace is acceptable.

All the queries originate from Hibernate and have been parameterized in the form of:

SELECT a, b, c From Customer where CustomerId = @P0

This is my session setup.

CREATE EVENT SESSION [TracingForStatements] ON SERVER
ADD EVENT sqlserver.sp_statement_completed (
    ACTION (
        sqlserver.session_id,
        package0.collect_system_time,
        sqlserver.transaction_id,
        package0.event_sequence
    )
    WHERE (
        sqlserver.database_id=555
    )
)
ADD TARGET package0.ring_buffer(SET max_memory= 128000)
WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 1 SECONDS)

How can I capture the actual values of the parameters that was used in the captured statements? Is it possible?

-- Edit (Workaround):
Changing the event to rpc_completed gives me the complete sql command(including all parameter values) called from Hibernate. For other cases it would still be good to know how to capture the parameter values, if it is possible.

Someone mentioned
"I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values."
Please review answer below:

Capture Passed Parameter Values In Sql Server Extended Sessions


SQL Server: compare names ignoring non-alpha I have a database table with a text column "title".
New rows are inserted all the time, and every time I would like to check for potential duplicates on this column.

I define duplicate by "having the same sequence of alpha characters". So, for example, if I already have the string "Lisa's Nights":

"LISASNIGHTS" is duplicate, because case doesn't matter, spaces don't
matter, apostrophe doesn't matter
"Lisa's Night" is not duplicate because the final 's' is missing.

Of course I could store an altered version of the title and search there. But is there any other solution?

You can do it on the fly without storing the stripped down name, but that would mean recalculating the strings (e.g. Lisa's Nights, Lisa's Night, LISASNIGHTS) each time a new entry should to be checked against an existing string.

On the other hand, if your table also has a column for the STRIPPEDDOWNNAME, you can leverage SQL Server's constraints.  Create a UNIQUE CONSTRAINT (or a UNIQUE INDEX) on the STRIPPEDDOWNNAME column.  Then you will calculate the stripped down name only once, when you insert it into the table.  With the UNIQUE CONSTRAINT the SQL Server will immediately let you know if an inserted string is a duplicate.

You can then capture the error and respond as appropriate to your application.

I encourage you to let the SQL Server manage the duplicate check whenever possible.


How to make this Cursor work better I have a table and I am trying to apply payments to the loan amounts. Payments must be applied to initial loan until it is zero before going to next loan.

First I must say cursors are not my strong point but I gave it a try. I cannot get the cursor to apply the pmnt_amt from the DEAL_FP_CALC_BEG_BAL_AMT until it gets to zero and apply any remaining payments to the next DEAL_FP_CALC_BEG_BAL_AMT.

For example a DEAL_FP_CALC_BEG_BAL_AMT of 5000 will be paid down like this:

5000.00- 1235.3 = 3764.7
3764.7 - 1122.00 = 2642.7
2642.7 - 1035.3 = 1607.4
1607.4 - 1005.4 = 602.00
602.4 -1005.2 = (403.2) Loan paid off
(403.2) + 7500 - 1235.3(next payment) = 5861.5 until this is paid

Table is

FP_MERCH_ID  PMNT_SEQ_ID  PMNT_AMT  PMNT_DT   DEAL_FP_BAL_AMT  FUND_DT
2359         1122         1235.3    10/1/15   5000             9/1/15
2359         1123         1122      10/12/15  5000             9/1/15
2359         1124         1035.3    10/19/15  5000             9/1/15
2359         1125         1005.4    10/24/15  5000             9/1/15
2359         1126         1105.2    10/29/15  5000             9/1/15
2359         1127         1235.3    11/3/15   7500             10/31/15
2359         1128         1122      11/8/15   7500             10/31/15
2359         1129         1035.3    11/13/15  7500             10/31/15
2359         1130         1005.4    11/18/15  7500             10/31/15
2359         1131         1105.2    11/23/15  7500             10/31/15

Here is my code below.
DEAL_FP_CALC_BEG_BAL_AMT is loan amount.

--DECLARE @COUNTER INT
DECLARE @PAYMENT INT
DECLARE @BALANCE INT
--DECLARE @MAXCOUNT INT
DECLARE @RESULTS INT
DECLARE @FP_CALC_BEG_BAL_AMT INT
SET @FP_CALC_BEG_BAL_AMT = (SELECT MAX(DEAL_FP_CALC_BEG_BAL_AMT)
                        From LOOPDATA_NEW where FP_MERCH_ID = 2359
                      )

DECLARE VINTAGE CURSOR
    FOR SELECT PMNT_AMT From LOOPDATA_NEW Where FP_MERCH_ID = 2359
                        Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT

OPEN VINTAGE

    FETCH NEXT FROM VINTAGE INTO @PAYMENT

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @BALANCE = @FP_CALC_BEG_BAL_AMT - @PAYMENT
        /* begin
        set @RESULTS = @BALANCE - @PAYMENT
        end */
    PRINT @BALANCE

FETCH NEXT FROM VINTAGE INTO @PAYMENT

END
CLOSE VINTAGE
DEALLOCATE VINTAGE

You never actually reduce your balance here. I think you mean to do this:

SET @BALANCE = @FP_CALC_BEG_BAL_AMT;
OPEN VINTAGE;
FETCH NEXT FROM VINTAGE INTO @PAYMENT;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @BALANCE = @BALANCE - @PAYMENT;
FETCH NEXT FROM VINTAGE INTO @PAYMENT;
END
CLOSE VINTAGE;
DEALLOCATE VINTAGE;

In this example, you set your starting balance to the initial loan amount. Then on each iteration of the cursor you reduce the balance by the payment amount.

I'm not sure why you chose to use a cursor, I am sure you have your reasons, but would the below not achieve the desired result without using one?

DECLARE @TOTAL_PAYMENTS INT;
    SET @TOTAL_PAYMENTS = (SELECT SUM(PMNT_AMT)
                             From LOOPDATA_NEW
                            Where FP_MERCH_ID = 2359
                         Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT);
    SET @BALANCE = @FP_CALC_BEG_BAL_AMT - @TOTAL_PAYMENTS;

Also, in your code you are casting your balance and payments to INT, this loses the decimal precision which could cause you to lose money if this were to be a real-world example. Remember to use an accurate data type such as DECIMAL so that you capture these values correctly.

EDIT BASED ON COMMENT

The reason you only every return the largest loan is because of this line in your code:

DECLARE @FP_CALC_BEG_BAL_AMT INT
    SET @FP_CALC_BEG_BAL_AMT = (SELECT MAX(DEAL_FP_CALC_BEG_BAL_AMT)
                                  From LOOPDATA_NEW
                                 where FP_MERCH_ID = 2359);

You only ever fetch the largest loan here. If you want to loop through each loan and then each payment you can do this:

DECLARE @loan DECIMAL(9,2) = 0;
DECLARE @balance DECIMAL(9,2) = 0;
DECLARE @payment DECIMAL(9,2) = 0;

DECLARE loans CURSOR
    FOR (SELECT DISTINCT DEAL_FP_CALC_BEG_BAL_AMT
           From LOOPDATA_NEW
          where FP_MERCH_ID = 2359);
 OPEN loans;
FETCH NEXT FROM loans INTO @loan;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @balance = @balance + @loan;

  DECLARE payments CURSOR
      FOR (SELECT PMNT_AMT
            From LOOPDATA_NEW
           Where FP_MERCH_ID = 2359
        Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT);

   OPEN payments;
  FETCH NEXT FROM payments INTO @payment;
  WHILE @@FETCH_STATUS = 0 AND @balance > 0
  BEGIN
    SET @balance = @balance - @payment;
  FETCH NEXT FROM payments INTO @payment;
  END
  CLOSE payments;
  DEALLOCATE payments;
FETCH NEXT FROM loans INTO @loan;
END
CLOSE loans;
DEALLOCATE loans;
PRINT @balance;

Aaron Bertrand gives a fantastic answer on the different methods you can use to calculate running totals for this question. I highly recommend checking it out as I feel it will help you in the long run.


After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour? I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=InnoDB; command, about 20MB. So, this is the first time I've done this, and I'm wondering if this is an expected behaviour?
Thanks,

This is a known issue (not a bug) all the days of Storage Engine technology
Someone asked a similar question in a Percona Forum in November 2014: The table size between InnoDB and MyISAM engines differs in 7 times.
This may have something to do with the granularity of storage between MyISAM and InnoDB.
MyISAM
According to MyISAM Dynamic Data File Layout

Variable length records are contained in "frames". A record can be put in one or more frames, also called the record "parts" or "blocks". The sense of the frames is to allow reusage of the space of deleted records. Starting with an empty data file, records are put in a single frame each, unless a record is bigger than the maximum frame size (16MB - 4). When a record is deleted, its frame is marked deleted. When a record is inserted after this, it reuses the old frame. If the new record is smaller, the frame is split. The unused part is marked deleted. If a new record is bigger than the old frame, the frame is filled with the record as much as fits. The rest is inserted in other old frames, or, if non is available, in a new frame at end of file.

InnoDB
The size of an InnoDB Page is 16K by default : See innodb_page_size
COMPARISON
Since InnoDB stores in a fixed 16K "frame" whereas MyISAM stores in frames that can vary in size up 16777212 (16M - 4), one should always expect InnoDB tables to be larger due to page splits that are required to fit data into fixed 16K "frames".


PostgreSQL 9.5: Permission to deny functions body I create new role without any grants.

New user can't select on tables, but can see functions body (pl/PgSQL)

I want permissions for new role:

allow INSERT into one table.
deny see tables, sequences, functions and function's body.

Is there any way to configure it?

As I know there is no possibility to restrict access to the DB metadata at least for reading in PostgreSQL.

As an alternative you can to create another DB for login(s) and use foreign-data wrapper to access to the desired object(s) in the your main DB.

Here is a simple scenario (not fully tested):

Login to the main DB and create test table:

create table t (x int);

Create another DB for login(s):

create database logindb with owner = postgres;
grant all on database logindb to postgres;

Create user:

create role testrole password '111' login nosuperuser noinherit;
revoke connect on database postgres from testrole;

Now login to the newely created DB with user postgres and create FDW:

create extension postgres_fdw;
create server myserver foreign data wrapper postgres_fdw options (host 'localhost', dbname 'postgres', port '5432');
create user mapping for postgres server myserver options(user 'postgres', password '111');
create user mapping for testrole server myserver options(user 'postgres', password '111');
create foreign table t(x int) server myserver options(schema_name 'public', table_name 't');
grant insert on t to testrole;

Theoretically thats all. You can use testrole to connect to the logindb and to insert data into the table t in the main DB postgres. And 'testrole' can not to connect to the main DB and see anything in it.