Query doesn't respond when adding two columns When I add two columns to my select the query doesn't respond. The type of column is nvarchar(2000). It's a little unusual.

The SQL Server version is 2014.
There is only one primary index.
The whole records is only 1000 rows.

Here is execution plan before (XML showplan):

Execution plan after (XML showplan):

Here is the query:

select top(100)
  Batch_Tasks_Queue.id,
  btq.id,
  Batch_Tasks_Queue.[Parameters], -- this field
  btq.[Parameters]  -- and this field
from
        Batch_Tasks_Queue with(nolock)
    inner join  Batch_Tasks_Queue btq with(nolock)  on  Batch_Tasks_Queue.Start_Time < btq.Start_Time
                            and btq.Start_Time < Batch_Tasks_Queue.Finish_Time
                            and Batch_Tasks_Queue.id <> btq.id
                            and btq.Start_Time is not null
                            and btq.State in (3, 4)
where
    Batch_Tasks_Queue.Start_Time is not null
    and Batch_Tasks_Queue.State in (3, 4)
    and Batch_Tasks_Queue.Operation_Type = btq.Operation_Type
    and Batch_Tasks_Queue.Operation_Type not in (23, 24, 25, 26, 27, 28, 30)

order by
    Batch_Tasks_Queue.Start_Time desc

The whole result count is 17 rows. The dirty data (nolock hint) is not important.

Here is the table structure:

CREATE TABLE [dbo].[Batch_Tasks_Queue](
    [Id] [int] NOT NULL,
    [OBJ_VERSION] [numeric](8, 0) NOT NULL,
    [Operation_Type] [numeric](2, 0) NULL,
    [Request_Time] [datetime] NOT NULL,
    [Description] [varchar](1000) NULL,
    [State] [numeric](1, 0) NOT NULL,
    [Start_Time] [datetime] NULL,
    [Finish_Time] [datetime] NULL,
    [Parameters] [nvarchar](2000) NULL,
    [Response] [nvarchar](max) NULL,
    [Billing_UserId] [int] NOT NULL,
    [Planned_Start_Time] [datetime] NULL,
    [Input_FileId] [uniqueidentifier] NULL,
    [Output_FileId] [uniqueidentifier] NULL,
    [PRIORITY] [numeric](2, 0) NULL,
    [EXECUTE_SEQ] [numeric](2, 0) NULL,
    [View_Access] [numeric](1, 0) NULL,
    [Seeing] [numeric](1, 0) NULL,
 CONSTRAINT [PKBachTskQ] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Batch_Tasks_QueueData]
) ON [Batch_Tasks_QueueData] TEXTIMAGE_ON [Batch_Tasks_QueueData]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Batch_Tasks_Queue]  WITH NOCHECK ADD  CONSTRAINT [FK0_BtchTskQ_BlngUsr] FOREIGN KEY([Billing_UserId])
REFERENCES [dbo].[BILLING_USER] ([ID])
GO
ALTER TABLE [dbo].[Batch_Tasks_Queue] CHECK CONSTRAINT [FK0_BtchTskQ_BlngUsr]
GO

Summary

The main problems are:

The optimizer's plan selection assumes a uniform distribution of values.
A lack of suitable indexes means:

Scanning the table is the only option.
The join is a naive nested loops join, rather than an index nested loops join. In a naive join, the join predicates are evaluated at the join rather than being pushed down the inner side of the join.

Details

The two plans are fundamentally pretty similar, though performance may be very different:

Plan with the extra columns

Taking the one with the extra columns that doesn't complete in a reasonable time first:

The interesting features are:

The Top at node 0 limits the rows returned to 100. It also sets a row goal for the optimizer, so everything below it in the plan is chosen to return the first 100 rows quickly.
The Scan at node 4 finds rows from the table where the Start_Time is not null, State is 3 or 4, and Operation_Type is one of the listed values. The table is fully scanned once, with each row being tested against the predicates mentioned. Only rows that pass all the tests flow on to the Sort. The optimizer estimates that 38,283 rows will qualify.
The Sort at node 3 consumes all the rows from the Scan at node 4, and sorts them in order of Start_Time DESC. This is the final presentation order requested by the query.
The optimizer estimates that 93 rows (actually 93.2791) will have to be read from the Sort in order for the whole plan to return 100 rows (accounting for the expected effect of the join).
The Nested Loops join at node 2 is expected to execute its inner input (the lower branch) 94 times (actually 94.2791). The extra row is required by the stop parallelism exchange at node 1 for technical reasons.
The Scan at node 5 fully scans the table on each iteration. It finds rows where Start_Time is not null and State is 3 or 4. This is estimated to produce 400,875 rows on each iteration. Over 94.2791 iterations, the total number of rows is almost 38 million.
The Nested Loops join at node 2 also applies the join predicates. It checks that Operation_Type matches, that the Start_Time from node 4 is less than the Start_Time from node 5, that the Start_Time from node 5 is less than the Finish_Time from node 4, and that the two Id values do not match.
The Gather Streams (stop parallelism exchange) at node 1 merges the ordered streams from each thread until 100 rows have been produced. The order-preserving nature of the merge across multiple streams is what requires the extra row mentioned in step 5.

The great inefficiency is obviously at steps 6 and 7 above. Fully scanning the table at node 5 for each iteration is only even slightly reasonable if it only happens 94 times as the optimizer predicts. The ~38 million per-row set of comparisons at node 2 is also a large cost.

Crucially, the 93/94 row row goal estimation is also quite likely to be wrong, since it depends on the distribution of values. The optimizer assumes uniform distribution in the absence of more detailed information. In simple terms, this means that if 1% of the rows in the table are expected to qualify, the optimizer reasons that to find 1 matching row, it needs to read 100 rows.

If you ran this query to completion (which might take a very long time), you would most likely find that many more than 93/94 rows had to be read from the Sort in order to finally produce 100 rows. In the worst case, the 100th row would be found using the last row from the Sort. Assuming the optimizer's estimate at node 4 is correct, this means running the Scan at node 5 38,284 times, for a total of something like 15 billion rows. It could be more if the Scan estimates are also off.

This execution plan also includes a missing index warning:

/*
The Query Processor estimates that implementing the following index
could improve the query cost by 72.7096%.

WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([Operation_Type],[State],[Start_Time])
INCLUDE ([Id],[Parameters])

The optimizer is alerting you to the fact that adding an index to the table would improve performance.

Plan without the extra columns

This is essentially the exact same plan as the previous one, with the addition of the Index Spool at node 6 and the Filter at node 5. The important differences are:

The Index Spool at node 6 is an Eager Spool. It eagerly consumes the result of the scan below it, and builds a temporary index keyed on Operation_Type and Start_Time, with Id as a non-key column.
The Nested Loops Join at node 2 is now an index join. No join predicates are evaluated here, instead the per-iteration current values of Operation_Type, Start_Time, Finish_Time, and Id from the scan at node 4 are passed to the inner-side branch as outer references.
The Scan at node 7 is performed only once.
The Index Spool at node 6 seeks rows from the temporary index where Operation_Type matches the current outer reference value, and the Start_Time is in the range defined by the Start_Time and Finish_Time outer references.
The Filter at node 5 tests Id values from the Index Spool for inequality against the current outer reference value of Id.

The key improvements are:

The inner side scan is performed only once
A temporary index on (Operation_Type, Start_Time) with Id as an included column allows an index nested loops join. The index is used to seek matching rows on each iteration rather than scanning the whole table each time.

As before, the optimizer includes a warning about a missing index:

/*
The Query Processor estimates that implementing the following index
could improve the query cost by 24.1475%.

WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([State],[Start_Time])
INCLUDE ([Id],[Operation_Type])
GO

Conclusion

The plan without the extra columns is faster because the optimizer chose to create a temporary index for you.

The plan with the extra columns would make the temporary index more expensive to build. The [Parameters] column is nvarchar(2000), which would add up to 4000 bytes to each row of the index. The additional cost is enough to convince the optimizer that building the temporary index on each execution would not pay for itself.

The optimizer warns in both cases that a permanent index would be a better solution. The ideal composition of the index depends on your wider workload. For this particular query, the suggested indexes are a reasonable starting point, but you should understand the benefits and costs involved.

Recommendation

A wide range of possible indexes would be beneficial for this query. The important takeaway is that some sort of nonclustered index is needed. From the information provided, a reasonable index in my opinion would be:

CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time);

I would also be tempted to organize the query a little better, and delay looking up the wide [Parameters] columns in the clustered index until after the top 100 rows have been found (using Id as the key):

SELECT TOP (100)
    BTQ1.id,
    BTQ2.id,
    BTQ3.[Parameters],
    BTQ4.[Parameters]
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
    ON BTQ2.Operation_Type = BTQ1.Operation_Type
    AND BTQ2.Start_Time > BTQ1.Start_Time
    AND BTQ2.Start_Time < BTQ1.Finish_Time
    AND BTQ2.id != BTQ1.id
    -- Look up the [Parameters] values
JOIN dbo.Batch_Tasks_Queue AS BTQ3
    ON BTQ3.Id = BTQ1.Id
JOIN dbo.Batch_Tasks_Queue AS BTQ4
    ON BTQ4.Id = BTQ2.Id
WHERE
    BTQ1.[State] IN (3, 4)
    AND BTQ2.[State] IN (3, 4)
    AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    -- These predicates are not strictly needed
    AND BTQ1.Start_Time IS NOT NULL
    AND BTQ2.Start_Time IS NOT NULL
ORDER BY
    BTQ1.Start_Time DESC;

Where the [Parameters] columns are not needed, the query can be simplified to:

SELECT TOP (100)
    BTQ1.id,
    BTQ2.id
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
    ON BTQ2.Operation_Type = BTQ1.Operation_Type
    AND BTQ2.Start_Time > BTQ1.Start_Time
    AND BTQ2.Start_Time < BTQ1.Finish_Time
    AND BTQ2.id != BTQ1.id
WHERE
    BTQ1.[State] IN (3, 4)
    AND BTQ2.[State] IN (3, 4)
    AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ1.Start_Time IS NOT NULL
    AND BTQ2.Start_Time IS NOT NULL
ORDER BY
    BTQ1.Start_Time DESC;

The FORCESEEK hint is there to help ensure the optimizer chooses an indexed nested loops plan (there is a cost-based temptation for the optimizer to select a hash or (many-many) merge join otherwise, which tends not to work well with this type of query in practice. Both end up with large residuals; many items per bucket in the case of the hash, and many rewinds for the merge).

Alternative

If the query (including its specific values) were particularly critical for read performance, I would consider two filtered indexes instead:

CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time)
WHERE
    Start_Time IS NOT NULL
    AND [State] IN (3, 4)
    AND Operation_Type <> 23
    AND Operation_Type <> 24
    AND Operation_Type <> 25
    AND Operation_Type <> 26
    AND Operation_Type <> 27
    AND Operation_Type <> 28
    AND Operation_Type <> 30;

CREATE NONCLUSTERED INDEX i2
ON dbo.Batch_Tasks_Queue (Operation_Type, [State], Start_Time)
WHERE
    Start_Time IS NOT NULL
    AND [State] IN (3, 4)
    AND Operation_Type <> 23
    AND Operation_Type <> 24
    AND Operation_Type <> 25
    AND Operation_Type <> 26
    AND Operation_Type <> 27
    AND Operation_Type <> 28
    AND Operation_Type <> 30;

For the query that does not need the [Parameters] column, the estimated plan using the filtered indexes is:

The index scan automatically returns all qualifying rows without evaluating any additional predicates. For each iteration of the index nested loops join, the index seek performs two seeking operations:

A seek prefix match on Operation_Type and State = 3, then seeking the range of Start_Time values, residual predicate on the Id inequality.
A seek prefix match on Operation_Type and State = 4, then seeking the range of Start_Time values, residual predicate on the Id inequality.

Where the [Parameters] column is needed, the query plan simply adds a maximum of 100 singleton lookups for each table:

As a final note, you should consider using the built-in standard integer types instead of numeric where applicable.

Please create the following index:

create index Batch_Tasks_Queue_ix_Start_Time on Batch_Tasks_Queue(Start_Time);


How can I implement data encryption in SQL Express for a VB6 application? I am a developer tasked with maintaining a legacy application that is written in VB6. With the upcoming introduction of GDPR we are working on an encryption solution for our users. Our users use a variety of different versions of SQL Server and a variety of OS's. They host their own databases on their own servers.

I have come up with an idea of using column level encryption on the tables using certificates installed on the database server, then using a view to retrieve unencrypted data. Then using an INSTEAD OF trigger to update the encrypted data.

This means no changes to the application for data access. Unfortunately, because we connect via an ADODB.Recordset in many places the system will not allow the data to be updated (it throws an OLE DB generated error).

My next idea was to implement object model classes within the VB6 code and save via a stored procedure. This will involve allot of laborious  code changes so we would ideally like to avoid this.

Does anybody have any experience solving this issue or any advice regarding implementing the correct form of encryption to enable our clients to be compliant?

Thanks in advance

I hope it is permitted to mention our third party product, NetLib Encryptionizer. It provides Transparent Data Encryption for all versions and editions of SQL Server including Express.


Deadlock on inserts to a single table with a single key. (Mysql) I have a simple game server.
One of the requirement i received was to handle a single request for a single player at a time. so in order to achieve that i decided to create a lock table:

CREATE TABLE `GameRoundLock` (`theKey` varchar(255) NOT NULL,
PRIMARY KEY (`theKey`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Before I handle any request from any player I am inserting a row into that table like so:

// save and flush == acquiring the lock
GameRoundLock lock = this.gameRoundLockRepository.saveAndFlush(somePlayerKey);

   /** Play busniess logic here...  **/

// deleting == releasing the lock
this.gameRoundLockRepository.delete(lock);

This actually works.

In the following scenario the whole system works as expected:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request... (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) thread of 2) completes

5) thread of 3) now continuing

6) DONE, playerA total played rounds == 2.

****Pay attention that the id (playerKey) is identical.****

The problem arises when the playerA tries another play request while the handling of the first thread still occurs. here is the scenario that resulting in a weird deadlock:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request... (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) While 2) is still executing and 3) is still waiting, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2)/3) completes.

5) thread of 2) completes

6) ERROR - a deadlock is thrown!!!!!

Here is the deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-11-23 17:05:26 0x1d98
*** (1) TRANSACTION:
TRANSACTION 5493356, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5191, OS thread handle 5152, query id 15582091 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table      `game_server`.`gameroundlock` trx id 5493356 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) TRANSACTION:
TRANSACTION 5493355, ACTIVE 12 sec inserting, thread declared inside InnoDB    1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5193, OS thread handle 7576, query id 15582083 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table     `game_server`.`gameroundlock` trx id 5493355 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info     bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table    `game_server`.`gameroundlock` trx id 5493355 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info    bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** WE ROLL BACK TRANSACTION (2)

It seems like two "close\near" inserts for the locks of the 2nd and 3rd play requests causes this. why a deadlock and how to mitigate it?

I guess you have following issue.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

It looks like your lock function or key needs to take into account who set and who can release the lock (i.e. an owner). Perhaps by using a randomly generated ID. With a specific lock owner each thread has to verify that they are the owner when they set a lock and when they release a lock.


Does "allow remote connections to this server" in sql server impact SSIS packages? I'm wondering if anyone has experience with this setting "allow remote connections to this server" in sql server. I am partly responsible for a few hundred sql servers which run SSIS in various ways. Some run it with ssis packages deployed through integration services catalog, and others run the SSIS package from a batch server or even manually from their laptop running visual studio, using a package that connects to our sql server.
A security consultant is saying we need to disable this on every server, no questions asked, but I'm hearing conflicting information. Some would imply any SSIS packages deployed on another server or laptop and connecting to this one, would be denied now. Others say it actually means connections FROM this server, so it would impact ssis packages called from batch servers. Others say it is FROM still, but for some reason wouldn't impact SSIS packages.
Any recommendations on what I should do? Rather than test every possible scenario to appease the security consultant, I would like to be able to rule out a few "always true" situations where we would keep this setting checked. Otherwise I will have to test hundreds of environments just to disprove him. Any help appreciated.

I basically asked the same question a few years ago, take a look at my thread here: What does the setting allow remote connections actually do.

If you disable "allow remote connections to this server" then you wouldn't be able to connect to SQL server from local VM or any other server. You also wouldn't be able to run SSIS packages remotely. No remote connections would be allowed and the only option would be to login to the server and perform the activities.
Regards,
Shivangi


Find tables with modified data after a specified datetime I am performing few inserts and updates on my database using tool provided by app supplier. I want to compare the updated database and backup after running the tool.

But my database size is around 250GB, that takes long time to compare. Rather than full database compare I want to compare the tables which are modified after running the tool.

How can I get all the table names those are modified after specified date like 2015-04-14?

Can someone help me with this please?

You can use something like

    SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables

you can add a WHERE clause to limit the results to only those changes that have occurred in the past week, month, year, whatever. Something like

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

Although this is no substitute for true database auditing that captures who modified the table, what was changed, and what the table looked like prior to the change.

To get the former, you'll need to invest some time in DDL triggers or some money into a third party tool.

you can also run to this query to get accurate details of when was  the table last updated. If WHERE condition is entirely removed it will provide details of the entire database.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
 FROM sys.dm_db_index_usage_stats
 WHERE database_id = DB_ID( 'YourDatabaseName')
 AND OBJECT_ID=OBJECT_ID('TableName')


Replication between two different SQL Server versions We have mirroring configured (transactional replication) in our primary and secondary SQL Servers, which are running on Windows 2008 R2 SP2 OS with 2008 R2 SP3 as the SQL Server version.

If we first upgrade the secondary server, we have to figure out how the synchronization between the two servers has to be configured, as for some time they will be running with two different versions of SQL Server.

We want to upgrade them to SQL Server 2014.

It sort of depends whether you're using Database Mirroring, or Transaction Replication (although I suppose you could be using both)

Assuming Transaction Replication:

This is supported, so long as you stick to certain rules, as described in the MSDN article Upgrade Replicated Databases

As long as your Distributor is the same version or greater than your Publisher, and a Subscriber is within two versions of the Publisher, which fits in your case so you're OK. That MSDN article provides instructions.

This is similar to the question/answer asked a couple of years back, but the MSDN article has been updated since.

If you mean Database Mirroring:

I'm assuming you don't mean this, as this question has been asked before in a number of ways, but I'll add the answer in for completeness.

This can be done, but it is supported strictly for minimising downtime during upgrades (which it seems is what you are doing, so that's OK).

Have a look at MSDN - Minimize Downtime for Mirrored Databases When Upgrading Server Instances for information about this (and a walk-through).

Are you using a distributor? The order is DPS: Distributor > Publisher > Subscriber

I think as long as everyone is SQL Server 2008 and later you should be OK. Last I read, SQL Server 2014 won't replicate with SQL 2005.


How to check the growth of database I would like to know how to check the growth of entire database and individual data files.

Also, what to suggest to the client if the database is growing rapidly?

You can query the default trace to get information about recent growth events.

DECLARE @path nvarchar(260) = (
    SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) +'log.trc'
    FROM    sys.traces
    WHERE   is_default = 1)

SELECT gt.DatabaseID,
       gt.FileName,
       COUNT(*) AS NumberOfEvents,
       CASE WHEN te.name LIKE'%Grow' THEN 1 ELSE 0 END AS is_growth_event
FROM  sys.fn_trace_gettable(@path, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE   te.name in ('Data File Auto Grow','Log File Auto Grow','Data File Auto Shrink','Log File Auto Shrink')
GROUP BY gt.DatabaseID,
       gt.FileName,
       te.name

You can also use Event Notifications to be notified on DATA_FILE_AUTO_GROW,LOG_FILE_AUTO_GROW without having to poll the trace.

---use sp_hlepdb, schedule it and dump it in a table.

--here you go

---step1
USE [msdb]
GO

/****** Object:  Table [dbo].[database_growth]    Script Date: 2/4/2015 11:47:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[database_growth](
    [dbname] [sysname] NOT NULL,
    [owner] [sysname] NULL,
    [created] [nvarchar](11) NULL,
    [dbid] [smallint] NULL,
    [dbdesc] [nvarchar](600) NULL,
    [dbsize] [nvarchar](13) NULL,
    [cmptlevel] [tinyint] NULL,
    [run_date] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

GO

---setp2
USE [msdb]
GO

/****** Object:  Job [Database Growth Tracking]    Script Date: 2/4/2015 11:51:21 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/4/2015 11:51:21 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Database Growth Tracking',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Track Database Growth daily',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [FATCH DB SIZE INFO PUT INTO DATABASE GROWTH TABLE IN MSDB]    Script Date: 2/4/2015 11:51:21 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'FATCH DB SIZE INFO PUT INTO DATABASE GROWTH TABLE IN MSDB',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'USE
MSDB
INSERT INTO  database_growth
EXEC sp_helpdb2
--- EXEC [dbo].[SP_HELPDB2]',
        @database_name=N'msdb',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'fatch all db size info',
        @enabled=1,
        @freq_type=16,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20140731,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959,
        @schedule_uid=N'49a0ae34-40c7-4d9b-912a-8ea5df6d6e62'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

NADIR


Can SQL server deny connections until the AG failover has completed ? We have been doing rigorous testing of the Always On environment by doing manual failovers multiple times at the same time doing transaction in the midst of failover and we observed the following and would like to know your opinion on this.

The application that uses the AG databases does inserts/updates frequently (like in seconds interval). During failover… the application is connected to the failed over AG Group but throwing an error saying the database is in read only state and not able to write to the database and it couldn’t do any inserts and updates until the failover completes. The Nodes are set up for synchronous commit mode (automatic failover).

When we are doing manual failover it takes about 10 seconds to fully failover. The issue we have is on this 10 second interval.

I know we can add error handling codes from the application side to handle the error or make the application wait until the failover fully takes place. The problem is to change the application code is not going to be an easy process for us since it is a third party application.

Is there any way we can set/tell SQL server to not allow connections to the server until the failover has completed; hence we do not see the “database is in read only state” error. In other words, can SQL server deny connections until the failover has completed?

There is not a built in option to do this directly from AlwaysOn.  You would have to go with something a bit custom.  Depending on your setup, you could do something as simple as a job or a trigger scanning for changes in alwayson state.  Which then disables/enables the login for the application during a failover. But then you may see other errors in your log for the failed login attempt.

One thing you can look into is ScaleArc

http://www.scalearc.com/

it will queue the connections until the replica has failed over from SideA to SideB. the application will not know that a fail over has occurred. The connections and transactions will be released to the new primary replica once its available... Of course the solution costs $$


Query very slow on innodb I'm using mysql 5.7 on ubuntu server 14.04, and engine innodb.
I have a table that are very huge (about 66 Go), I insert and delete from it without problem, but some query of select are taking lot of time till they are abondoned from java.

When I do select count(*) from TABLE, it take about 10 minutes.

I export all my database and data to another server ubuntu server 16.04 mysql 5.7, and do the same query and it take about 2 minutes.

Why this difference happends on the same table and same data?

Here is my config files :

First server (10 minutes)

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /home/mysql_data/mysqld.sock
datadir = /home/mysql_data
log-error   = /var/log/mysql/error.log
bind-address    = 127.0.0.1
symbolic-links=0
open_files_limit = 10000
query_cache_type=0
query_cache_size=0
innodb_buffer_pool_size = 36G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 8
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
innodb_buffer_pool_load_at_startup = 1 # MySQL 5.6+
innodb_buffer_pool_dump_pct = 75 # MySQL 5.7 only
innodb_checksum_algorithm = crc32 # MySQL 5.6 or newer
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_file_per_table

Second server (2 minutes)

[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice    = 0
[mysqld]
user    = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /home/mysql/mysqld.sock
port    = 3306
basedir = /usr
datadir = /home/mysql
tmpdir  = /tmp
lc-messages-dir = /usr/share/mysql
bind-address    = 127.0.0.1
skip-external-locking
key_buffer_size = 16M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size = 8
query_cache_limit   = 1M
query_cache_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size = 100M

Let check the the table size after you migration to new db(in new db server).
a reason is

First server (10 minutes)

your table might not up to day of statistic use ANALYZE TABLE for Innodb.
you mention you table is 66GB but it might not the real table size it may include the dead-tuple(caused by your update,delete operation).

when you migration it to other place this table or whole database will be ANALYZE to the real time statistic.


MySQL replication - some values are not being replicated MySQL Replication issues

I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion.

My structure looks like:

1) Master
   2) slave (without SSL)
   3) Slave (with SSL)

On some occasions, what goes wrong is that data are not replicated to slave.

  First my question, was if i someone is updating slaves. And i took off all
      privilege from all user other than read.
      which itself contradicts because both slave are not replicated and user's do not have permision on both slave.

Ignored DB:

binlog-ignore-db                                        = mysql
binlog-ignore-db                                        = test
replicate-ignore-db                                     = mysql
replicate-ignore-db                                     = test

Manual Test:
            I created new database, created table, new filed all was replicated to both slave. Updated works, altered works and delete works.

On some occasions when it misses out, randomly I am unable to troubleshoot or narrow down problem area.

Any suggestions?

FYI, I do not have skip-error in place.

Are you aware of http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/ ?

Perhaps some of the missing updates are being run with the test database as the current database?

It is less error-prone if you use the replicate-wild-* options. In your case

    replicate-wild-ignore-table = mysql.*
    replicate-wild-ignore-table = test.*

When you setup a filtered replication, you should prefix all tables by database name. All transactions with table not prefixed will not replicate... It's not a bug but a choice of MySQL team... Strange not? :)


Copy whole database from SQL Server to SQL Server using linked server I am using SQL Server 2014 Management Studio installed in my system. And using linked server I have connected to a linked server.

Now here I need to copy whole database from linked server to my local SQL Server using linked server itself.

I am using a linked server instead of backup/restore because using linked server I can copy remote database.

Tool used: Microsoft SQL Server 2014 Management Studio.

Backup/restore, the Copy Database Wizard, and Log Shipping are all potentially better choices than linked server copies.

See the following documentation:

Back Up and Restore of SQL Server Databases
Use the Copy Database Wizard
About Log Shipping (SQL Server)

You can use 'select into' construct to load data from tables on a linked server to your local server.
I have just tested it and i works perfect ecpecially if you have few tables to get. Otherwise you will have to create some sort of a cursor to get the list of tables and data from them.


Execute a script after transactional replication setup completes We successfully set up transactional replication with 15 articles.
After successful of article changes Done in subscriber, we need to execute ".SQL File"  at subscriber database. Please help on this.

Create the below table in Publisher database:

Create Table DwDimTable (
Ino int Primary Key
,Item Varchar(10)
,Usage numeric(16,2)
,StartDate date
,EndDate date)

Create the below table and run the query in the Subscriber database:

Create table DwFactTable(
Ino int,
Item    varchar(15),
PerDayUsage numeric(16,5),
[Day]   int,
[Month] int,
[Year]  int,
[Date]  date)
Go
SELECT TOP (1000) IDENTITY(INT,0,1) AS N
INTO dbo.Multi
FROM sys.columns c1, sys.columns c2

Insert the data in the Publisher with below query:

insert into DwDimTable
values
    (1,'Gas Bill','9500.00',cast(GETDATE() as date),
    cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as date))

Set up transactional replication for the article ‘DwDimTable’. After successful of transaction replication.

Here, I need assistance to execute/run automatically below .SQL file/Query
automatically at subscriber once publication is done:

insert into DwFactTable
SELECT DISTINCT
Ino
,Item
,sum(Usage)/(DATEDIFF(DD,StartDate,EndDate)+1) [PerDayUsage]
,DD as [Day]
,MM as [Month]
,YY as [Year]
,CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATE) AS [Date]
FROM   DwDimTable
CROSS APPLY (SELECT DAY(DAYDT) AS DD, MONTH(DAYDT) AS MM, YEAR(DAYDT) AS YY
             FROM (SELECT TOP (DATEDIFF(DAY,StartDate,EndDate) + 1)
                          DATEADD(DAY, N, StartDate) DAYDT
                   FROM DBO.Multi) AS R
            ) F
GROUP BY Ino,Item,Usage ,DD,MM,YY,StartDate,EndDate,
CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATETIME)

I need assistance to execute/run automatically below .SQL file/Query
  automatically at subscriber once publication is done

You should use sp_addscriptexec tsql to run sql script on all subscribers of a publication once the snapshot is applied to subscribers. Make sure you use @skiperror = 0 to stop the distribution agent when an error is encountered.

Brandon Williams has a good blog post on - Executing scripts with sp_addscriptexec

You can create After Insert/Update/Delete trigger if you want to run script when any data Insert/Updates/Deletes at publication side. Here i have created script for After Insert.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[DwDimTable_INS]
   ON  [dbo].[DwDimTable]
   AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM INSERTED) BEGIN
    RETURN;
END

insert into DwFactTable
SELECT DISTINCT
Ino
,Item
,sum(Usage)/(DATEDIFF(DD,StartDate,EndDate)+1) [PerDayUsage]
,DD as [Day]
,MM as [Month]
,YY as [Year]
,CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATE) AS [Date]
FROM   DwDimTable
CROSS APPLY (SELECT DAY(DAYDT) AS DD, MONTH(DAYDT) AS MM, YEAR(DAYDT) AS YY
             FROM (SELECT TOP (DATEDIFF(DAY,StartDate,EndDate) + 1)
                          DATEADD(DAY, N, StartDate) DAYDT
                   FROM DBO.Multi) AS R
            ) F
GROUP BY Ino,Item,Usage ,DD,MM,YY,StartDate,EndDate,
CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATETIME)

END
GO


remove a paritition table I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2.

The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you can see the partition column is not the primary key.

I have three partitions. Left boundary <=boundary 2, 5 and the last one is empty. All of the partitions are on primary file group. There are no other filegroups.

@Kin yes he wants just to remove the partition so it becomes non- partitioned table.
 as Aaron said I would   select * into new_table from old_paritioned and rename the new table and drop the partitioned table.I am sure they are better ways of doing that though.
If it is large table(billion of rows) copying all rows takes time and resources.


How to retrieve the query I used to create a view? for my company I created a view which is used for exporting.
Maybe there is a problem with the query that is used to create this table.

So here is my question:

Is it possible to get the query I used to create said view table ?

View definitions are stored in the information schema:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_NAME = 'yourviewname'

For the purposes of recreating the query used to create your view and for the purposes of backing up all views, I found the following:

Dump all views:

select CONCAT('CREATE or REPLACE VIEW ',table_name,' AS ',view_definition,';') from information_schema.views;

Dump views from only a specific DB:

select CONCAT('CREATE or REPLACE VIEW ',table_name,' AS ',view_definition,';') from information_schema.views WHERE table_schema = '$DB_NAME';


MongoDB cluster deployment I am new to MongoDB. I am trying to deploy a MongoDB cluster. I am following:

https://docs.mongodb.org/manual/tutorial/deploy-shard-cluster/

I initiated Config Server Replica Set and after that I tried to addShard but I receive always error:

mongos> sh.addShard("rs1/10.1.10.12:27017,10.1.10.13:27017,10.1.10.14:27017")
{
        "ok" : 0,
        "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rs1",
        "code" : 133
}

I have tried to add a shard but I receive the following error:

mongos> sh.addShard("firstRS/centos-m-1.ovm:27017")
{
        "ok" : 0,
        "errmsg" : "Cannot add firstRS/centos-m-1.ovm:27017 as a shard since     it is part of a config server replica set",
    "code" : 96
}

In replicaSet I see:

rs.conf()
{
        "_id" : "firstRS",
        "version" : 3,
        "configsvr" : true,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "centos-m-1.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "centos-m-2.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "centos-m-3.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("572e55c00408eaa72066cef3")
        }
}

I cannot understand. Could you help me?

MongoDB shell version: 3.2.6
connecting to: 10.1.10.12:28017/test

mongos> rs.status()
{
        "info" : "mongos",
        "ok" : 0,
        "errmsg" : "replSetGetStatus is not supported through mongos"
}

If I connect to config server and I give: rs.status() I found all nodes with one node highlighted as PRIMARY. After than I try to connect to a mongos instances and I try to add a shard:

sh.addShard( "rs1/10.1.10.14:28017" )

and I get error I reported previously and in log I found:

  2016-05-06T12:37:33.787Z W NETWORK  [conn1] No primary detected for set rs1
  2016-05-06T12:37:33.787Z I NETWORK  [conn1] All nodes for set rs1 are down. This has happened for 1 checks in a row. Polling will stop after 29 more failed checks

creat three replica set with different port number and then try


I dropped MySQL's `performance_schema` database, how can I create it? While fixing a ibdata/log problem, I accidentally dropped my performance_schema database, I'd like to create a new one.

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name                                     | Value   |
+---------------------------------------------------+---------+
| performance_schema                                | ON      |
| performance_schema_events_waits_history_long_size | 10000   |
| performance_schema_events_waits_history_size      | 10      |
| performance_schema_max_cond_classes               | 80      |
| performance_schema_max_cond_instances             | 1000    |
| performance_schema_max_file_classes               | 50      |
| performance_schema_max_file_handles               | 32768   |
| performance_schema_max_file_instances             | 10000   |
| performance_schema_max_mutex_classes              | 200     |
| performance_schema_max_mutex_instances            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| performance_schema_max_table_handles              | 100000  |
| performance_schema_max_table_instances            | 50000   |
| performance_schema_max_thread_classes             | 50      |
| performance_schema_max_thread_instances           | 1000    |
+---------------------------------------------------+---------+
16 rows in set (0.06 sec)

Those variables appear to be fine to me.

The following question asks the same thing, however the user concludes that they were able to create it by following documentation that I could not find such instructions in.

mysql: deleted performance_schema, is it a problem?

Any thoughts?

Tables in the performance_schema database are a collection of views and temporary tables that do not store data permanently. The mysql_upgrade command will restore the performance_schema database

From the shell

mysql_upgrade --user=root --password=password

It alludes to DROP DATABASE being recoverable, but only in odd conditions i'm not familiar with http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

According to Docs, binlogs are just a sequence of commands executed based on a given reference point. So that when you did "DROP DATABASE", instead of going "Oh, hes droppping the database, we should back up now just in case" it merely wrote a "DROP DATABASE" to the last binlog. Recovery is not as simple as playing the tape backwards.

What you need to do is recover the database from a last-known-good, and apply the binlogs that happened between that recover point and the DROP command.

http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html

How one determines which binlogs to use tho, unclear.

There is nothing better than having full file system backups. And you should at least have these to fall back to.


Microsoft SQL server 2008 and linked servers I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any of these databases to a dedicated server for itself if required and just create a linked server to the other server(s). however doing so I also need to change all the codes which are accessing the other databases.

does anyone know of any way to avoid this later modification in case of using a linked server ?

or in other words :

Is that possible to have stored procedures which would work regardless of the fact that it needs to access to a linked server or same local server ?

example :

DatabaseA and DatabaseB are both located on SQLInstannceA
Sp on DatabaseB :

SELECT * FROM DataBaseA.dbo.tbx

and now if I move DatabaseB to SQLInstanceB, and create a linked server to SQLInstanceA, I have to change the SP code to the following :

SELECT * from linkedserverName.DatabaseA.dbo.tbx

is there anyway to avoid this change in case of moving databases ?

Using loop back linked servers isn't the greatest idea.  This can lead to some interesting performance problems once you start doing joins between local tables and "remote" tables as you are going through a linked server so suddenly instead of doing a normal join you may end up transferring the entire table across the linked server (which means possibly reading it off of the disk), stuffing it into tempdb and joining to a hash table which doesn't have any indexes on it.

Don't setup linked servers unless you actually need them.  You can get some very powerful servers these days for not much money (I'm currently working on a server which has 32 cores and 256 Gigs of RAM) so scaling a server up isn't all that hard.  If the application actually needs to be scaled out across multiple servers you'll need someone who is used to doing very complex tuning to help out to make sure that you aren't shooting yourself in the foot in the process (which isn't hard to do).

Once you do get to needing linked servers, there's going to be so much stuff that needs to be done to move the database and test everything going through and fixing the code isn't going to seem all that bad.

I think the easiest solution is to have as many rows in your hosts file as you need, all of them pointing to the same IP address of your current development server and start the whole stuff by making the linked servers.

So let's suppose that you need two additional (future linked) servers. Then your hosts file (c:\windows\system32\drivers\etc) would look something like this:

123.123.123.101 mylinkedserver1.mydomain.com
123.123.123.102 mylinkedserver2.mydomain.com

Doing this, you can create and use the linked servers right at the beginning.


Select COUNT of days between two dates except weekends I am trying to get the number of days between two different dates, except the weekends.

I don't know how to achieve this result.

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10'
                     , interval  '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.
Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. The upper bound is only excluded if the last interval would be truncated, which is not the case with full days.
With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.
Rather call generate_series() with timestamp input. Here is why:

Generating time series between two dates in PostgreSQL

count(*) is slightly shorter and faster than count(the_day), doing the same in this case.

To exclude lower and / or upper bound, add / subtract 1 day accordingly. Typically, you might include the lower and exclude the upper bound:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10' - interval '1 day'
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

-- Returns number of weekdays between two dates
SELECT count(*)  as "numbers of days”
FROM generate_series(0, (‘from_date’::date - 'todate'::date)) i
WHERE date_part('dow', 'todate'::date + i) NOT IN (0,6)

-- Returns number of weekdays between two dates
SELECT count(*)  as days
FROM generate_series(0, ('2014/04/30'::date - '2014/04/01'::date)) i
WHERE date_part('dow', '2014/04/01'::date + i) NOT IN (0,6)


Default value for UUID column in Postgres In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?

tl;dr

Call DEFAULT when defining a column to invoke one of the OSSP uuid functions. The Postgres server will automatically invoke the function every time a row is inserted.

CREATE TABLE tbl
(
  pkey UUID NOT NULL DEFAULT uuid_generate_v1() ,
  CONSTRAINT pkey_tbl PRIMARY KEY ( pkey )
)

If you already use the pgcrypto extension, consider the Answer by bpieck.

Plugin Required To Generate UUID

While Postgres out-of-the-box supports storing UUID (Universally Unique Identifier) values in their native 128-bit form, generating UUID values requires a plug-in. In Postgres, a plug-in is known as an extension.

To install an extension, call CREATE EXTENSION. To avoid re-installing, add IF NOT EXISTS. See my blog post for more details, or see this page in StackOverflow.

The extension we want is an open-source library built in C for working with UUIDs, OSSP uuid. A build of this library for Postgres is often bundled with an installation of Postgres such as the graphical installers provided by Enterprise DB or included by cloud providers such as Amazon RDS for PostgreSQL.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Generating Various Kinds Of UUID

See the extension’s doc to see a list of multiple commands offered for generating various kinds of UUID values. To get the original version of UUID built from the computer’s MAC address plus current date-time plus a small random value, call uuid_generate_v1().

SELECT uuid_generate_v1();

  672124b6-9894-11e5-be38-001d42e813fe

Later variations on this theme were developed for alternate kinds of UUIDs. Some people may not want to record the server’s actual MAC address, for example, for security or privacy concerns. The Postgres extension generates five kinds of UUIDs, plus the “nil” UUID 00000000-0000-0000-0000-000000000000.

UUID As Default Value

That method call can be made automatically to generate a default value for any newly inserted row. When defining the column, specify:

  DEFAULT uuid_generate_v1()

See that command used in the following example table definition.

CREATE TABLE public.pet_
(
  species_ text NOT NULL,
  name_ text NOT NULL,
  date_of_birth_ text NOT NULL,
  uuid_ uuid NOT NULL DEFAULT uuid_generate_v1(),  -- <====
  CONSTRAINT pet_pkey_ PRIMARY KEY (uuid_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.pet_
  OWNER TO postgres;

UUID versions

The uuid-ossp plugin can generate various versions of UUID.

uuid_generate_v1()Contains MAC address of current computer + current moment. Commonly used, but avoid if you are sensitive about disclosing the MAC of your database server or the time when this value was generated. Defined by specification as a Version 1 UUID.
uuid_generate_v1mc()Like Version 1, but with a random multicast MAC address instead of the real MAC address. Apparently a way to use Version 1 but substituting another MAC rather than the actual MAC of your database server if you are sensitive about disclosing that fact.What is a ‘random multicast MAC’? I do not know exactly. After reading section 4.1.6 of RFC 4122, I suspect this is a random number used in place of the MAC but with bits set to indicate a multicast MAC address rather than the usual unicast so as to distinguish this variation of Version 1 from a usual real-MAC Version 1 UUID.
uuid_generate_v3( namespace uuid, name text )Contains an MD5 hash of text you provide. Defined by specification as a Version 3 UUID, namespace-based UUID.
uuid_generate_v4()Based on randomly-generated data for 121-122 of the 128 bits. Six or seven bits used to indicate Version & Variant. This kind of UUID is practical only if implemented with a cryptographically-strong random generator. Defined by specification as a Version 4 UUID.
uuid_generate_v5( namespace uuid, name text )Same as Version 3 but using SHA1 hashing. Defined by specification as Version 5 UUID.
uuid_nil()A special case, all bits set to zero 00000000-0000-0000-0000-000000000000. Used as a flag for an unknown UUID value. Known as a nil UUID.

To compare types, see Question, Which UUID version to use?

If you are interested only in Version 4 (randomly generated), and are already using pgcrypto, see the Answer by bpieck.

If you are curious about Versions 3 & 5, see this Question, Generating v5 UUID. What is name and namespace?.

For more discussion, see my Answer to a similar Question and my blog post UUID values from JDBC to Postgres.

pgcrypto extension

Just a small addition to Basil’s very detailed answer.

Since currently most are using pgcrypto, instead of uuid_generate_v4() you can use gen_random_uuid() for a Version 4 UUID value.

First, enable pgcrypto in your Postgres.

CREATE EXTENSION "pgcrypto";

Just set DEFAULT of a column to

DEFAULT gen_random_uuid()


Override/kill when Code 3/SQLSTATE=57016 Occurs I have encountered issues a few times when a process of mine performs a bulk load to a temp table we use for pre-processing purposes. The Bulk load fails for one reason or another (this we are looking into and addressing separately), and then the table that we were bulk loading to gets locked up. Quiesce/Unquiesce and even restarting DB2 are not releasing the lock on this table.

Here is a screenshot of the error in question that we receive every time:

This error code means The table is in the "Load Pending" state. A previous LOAD attempt on this table resulted in failure. No access to the table is allowed until the LOAD operation is restarted or terminated.

Is there any command or way to circumvent this or regain access to this specific table? We have many other processes running when this bulk load runs so restarting DB2 is a very undesirable option and, in our case right now, has not freed up the table.

Apologies if this is not enough information to go off of, please let me know what else I should provide if needed.

Your LOAD was interrupted so the table was placed into a LOAD PENDING state. To take it out of the LOAD PENDING state you will need to issue one of the following:

LOAD TERMINATE to roll back the operation
LOAD REPLACE to reload the entire table
LOAD RESTART

Also, do not take a backup before you take the table out of LOAD PENDING state.

See Restarting an interrupted load operation in Information Center for details.

Try below command in db2 command line

db2 REORG TABLE [SCHEMA_NAME.TABLE_NAME]


PostgreSQL not running on Mac The error in its entirety reads:

  psql: could not connect to server: No such file or directory. Is the
  server running locally and accepting connections on Unix domain socket
  "/tmp/.s.PGSQL.5432"?

This is my second time setting up Postgresql via Homebrew on my Mac, and I have no clue what is going on. Previously, it had been working. At some point, I must've entered a command that messed things up. I'm not sure. Now, whenever I enter a SQL command from the command line, I receive the above message. I've run a command to check whether the server is running, and it apparently is not. If I attempt to start the server using

  $ postgres -D /usr/local/pgsql/data

I receive the following error:

  postgres cannot access the server configuration file
  "/usr/local/pgsql/data/postgresql.conf": No such file or directory

I've uninstalled and reinstalled Postgresql via Homebrew, but the problem persists. I'm completely at a loss as to how to get this working. Any help would be appreciated.

The problem can also be attributed to a crashed process that left postmaster.pid file behind.
$ brew services stop postgresql
# adjust path accordingly to your install, e.g. `/opt/homebrew/var/postgres/postmaster.pid`
$ rm /usr/local/var/postgres/postmaster.pid
$ brew services start postgresql

update it by using command

  brew postgresql-upgrade-database

if you have following error
Command 'brew' not found, but can be installed with:
sudo apt install linuxbrew-wrapper

then install it by using command

 sudo apt install linuxbrew-wrapper


How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time?

The query at issue is the following one:

  SELECT id_empresa,
         referencia,
         archivo,
         status
    FROM Outbound_call_files
   WHERE movido=0
     AND status NOT IN('Peticion')
ORDER BY id_empresa, status;

The execution plan is:

| id | select_type | table               | type | possible_keys | key           | key_len | ref   | rows    | Extra       |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | Outbound_call_files | ref  | motivo_status | motivo_status | 1       | const | 2561294 | Using where |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+-

SHOW CREATE INDEX returns the following:

| Outbound_call_files |          1 | motivo_status      |            1 | movido      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            2 | id_empresa  | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            3 | status      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |

I can see the cardinality is too low.

The structure and constraints of this table are shown below:

CREATE TABLE `Outbound_call_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_empresa` int(11) NOT NULL,
  `id_campana` int(11) NOT NULL,
  `referencia` text NOT NULL,
  `movido` tinyint(4) NOT NULL,
  `archivo` varchar(50) NOT NULL,
  `status` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_empresa_campana` (`id_empresa`,`id_campana`),
  KEY `motivo_status` (`movido`,`id_empresa`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10144111 DEFAULT CHARSET=latin1

Executing profiling, this was the result:

SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000070 |
| checking permissions |  0.000008 |
| Opening tables       |  0.000019 |
| System lock          |  0.000010 |
| init                 |  0.000030 |
| optimizing           |  0.000018 |
| statistics           |  0.000090 |
| preparing            |  0.000022 |
| executing            |  0.000004 |
| Sorting result       |  0.000006 |
| Sending data         | 12.512944 |
| end                  |  0.000017 |
| query end            |  0.000006 |
| closing tables       |  0.000011 |
| freeing items        |  0.000019 |
| logging slow query   |  0.000004 |
| logging slow query   |  0.000067 |
| cleaning up          |  0.000006 |
+----------------------+-----------+

Note: The database server has 150G RAM and the buffer pool is configured in 100G.

Create a new index that is based on movido,status, id_empresa, referencia, archivo

To convert the query to a covering index


Column does not exist in UNION ALL query I am doing some code for my badminton club.

Tables

CREATE TABLE School (
    Name text PRIMARY KEY
);

CREATE TABLE Tournament (
    Number integer PRIMARY KEY
);

CREATE TABLE Player (
    Name text NOT NULL,
    SchoolName text NOT NULL,
    Gender text CHECK (Gender IN ('Masculin', 'Féminin'))
);

ALTER TABLE ONLY Player
    ADD CONSTRAINT Player_pkey PRIMARY KEY (name, SchoolName);

ALTER TABLE ONLY Player
    ADD CONSTRAINT SchoolName_fkey FOREIGN KEY (SchoolName) REFERENCES School(Name);

CREATE TABLE Result (
    -- PlayerId_fkey text
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text CHECK (Category IN ('Benjamin', 'Cadet', 'Juvénile')),
    TournamentNumber integer,
    Score integer,
    TypeOfPlay text CHECK (TypeOfPlay IN ('Simple', 'Double'))
);

ALTER TABLE ONLY Result
    ADD CONSTRAINT PlayerName_fkey FOREIGN KEY (PlayerName_fkey, SchoolName_fkey) REFERENCES Player(Name, SchoolName);

ALTER TABLE ONLY Result
    ADD CONSTRAINT TournamentNumber_fkey FOREIGN KEY (TournamentNumber) REFERENCES Tournament(Number);

Query

-- Select each player with their single and double total score
SELECT PlayerName_fkey, ScoreSimple, ScoreDouble

FROM (
    -- Select the sum of the best results for each player in single play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple
    FROM (
        -- Select the best four results in the season for each player in single play
        SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
        FROM (
            -- Select, for each player, the score for each tournament they played in single play
            SELECT PlayerName_fkey, score, TournamentNumber
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank

    UNION ALL

    -- Select the sum of the best results for each player in double play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
    FROM (
        -- Select the best four results in the season for each player in double play
        SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
        FROM (
            -- Select, for each player, the score for each tournament they played in double play
            SELECT PlayerName_fkey, score, TournamentNumber
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank) t1

Question

The problem I have is I get the error: "column "scoredouble" does not exist". How do I get the scoredouble column display on my first query? I cannot use a JOIN because some players have scores in one type of play (singles or double) and not the other.

Link here: http://rextester.com/XIG14273

With the help of many people whom gave me a lot of help, I finally got a working solution:

SELECT PlayerName_fkey, SumScoresSimpleTotal, SumScoresDoubleTotal, SumScoresTotal, rank() over (order by SumScoresTotal desc) AS OverallRank

FROM (

    SELECT COALESCE(t1.PlayerName_fkey, t2.PlayerName_fkey) as PlayerName_fkey, coalesce(SumScoresSimple, 0) as SumScoresSimpleTotal, coalesce(SumScoresDouble,0) as SumScoresDoubleTotal,
    (coalesce(SumScoresSimple, 0) + coalesce(SumScoresDouble, 0)) as SumScoresTotal
    FROM (

    SELECT PlayerName_fkey, ScoreSimple, sum(ScoreSimple) over (partition by PlayerName_fkey) as SumScoresSimple

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank)tinside1) t1

        FULL OUTER JOIN

    (SELECT PlayerName_fkey, ScoreDouble, sum(ScoreDouble) over (partition by PlayerName_fkey) as SumScoresDouble

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank) tinside2) t2

    ON t1.PlayerName_fkey = t2.PlayerName_fkey

    GROUP BY t1.PlayerName_fkey, t2.PlayerName_fkey, SumScoresSimple, SumScoresDouble

    ORDER BY SumScoresTotal DESC

The colasesce functions are particularly important here to avoid empty entries for both the player names and the scores.

The columns returned from each leg of the Union must have the same names. Therefore when the second part of the UNION is reached, the column

SUM(score) as ScoreDouble

is trying to be matched with the fourth column from the first leg of the UNION, which is

SUM(score) as ScoreSimple


PostgreSQL GRANT ALL on mutliple tables using LIKE I have more than 100 tables named public.test_*

How can I easily GRANT ALL access to the user test to all that tables at once?

I tried with:

GRANT ALL ON TABLE public.test_* TO test;

But it is not working ...

GRANT doesn't take wildcards in table identifiers.

You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name.

If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack Overflow for dynamic DDL in PL/PgSQL.

Untested rough example to give you the idea:

DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name LIKE 'test\_%'
    LOOP
        EXECUTE format('GRANT ALL ON TABLE %I.%I TO test;', table_schema, table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

For what %I means see the reference for the format function. If you don't have a format function your PostgreSQL is obsolete and you should probably plan an upgrade; you can use quote_ident and string concatenation in the mean time.

First revoke your database
REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

then try with your GRANT query.


Postgres: How to insert row with autoincrement id There is a Table "context".
There is an autoincrement id "context_id".
I am using sequence to retrieve the next value.

SELECT nextval('context_context_id_seq')

The result is: 1, 2, 3,...20....

But there are 24780 rows in the "context" table

How can I get the next value (24781)?

I need to use it in the INSERT statement

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.

You need to set the correct value for the sequence using setval()

select setval('context_context_id_seq', (select max(context_id) from context));

Then the next call to nextval() should return the correct value.

If the column is indeed defined as serial (there is no "auto increment" in Postgres) then you should let Postgres do it's job and never mention it during insers:

insert into context (some_column, some_other_column)
values (42, 'foobar');

will make sure the default value for the context_id column is applied. Alternatively you could use:

insert into context (context_id, some_column, some_other_column)
values (default, 42, 'foobar');

I have solution for shis:
Alter library sequelize file: squelize/lib/sql-string.js
incluid on line 56 this:

change

if (dialect === 'postgres' || dialect === 'sqlite' || dialect === 'mssql'){
    // http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
    // http://stackoverflow.com/q/603572/130598
    val = val.replace(/'/g, "''");
  } else {
    val = val.replace(/[\0\n\r\b\t\'"\x1a]/g, s => {
    switch (s) {
      case '\0': return '\0';
      case '\n': return '\n';
      case '\r': return '\r';
      case '\b': return '\b';
      case '\t': return '\t';
      case '\x1a': return '\Z';
      default: return '\' + s;
    }
  });
}

for

if (dialect === 'postgres' || dialect === 'sqlite' || dialect === 'mssql') {
  // http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
  // http://stackoverflow.com/q/603572/130598
  if(val.indexOf('nextval(')>-1) {
    return val
  }
  val = val.replace(/'/g, "''");
  } else {
    val = val.replace(/[\0\n\r\b\t\'"\x1a]/g, s => {
    switch (s) {
      case '\0': return '\0';
      case '\n': return '\n';
      case '\r': return '\r';
      case '\b': return '\b';
      case '\t': return '\t';
      case '\x1a': return '\Z';

      default: return '\' + s;
    }
  });
}

On your model declare field with defaultValue = "nextval('xxxxxxx')", ex:

...
return sequelize.define('cgm', {
  z01_numcgm: {
  type: DataTypes.INTEGER,
  defaultValue: "nextval('cgm_z01_numcgm_seq')",
  allowNull: false,
  primaryKey: true,
},

....


How to safely eject a tablespace oracle and move it to another machine I've a tablespace created on a external hardisk with one big datafile. If there are no connections to it, is it safe to eject the disk and plug it into a new machine? And how to map the tablespace to the oracle in the new machine?

If there are no connections to it, is it safe to eject the disk and plug it into a new machine?

No.  Absolutely not.

Database != File

Unless you shutdown the entire database, there is no guarantee that changes have been written from the Buffer Cache into the data files that make up that Tablespace.

Making a Tablespace transportable does forces that write activity to happen (and then prevents any more) but without the downtime.  But then, as you say, you have to muck about "mapping" that Tablespace into the new database.  Don't make work for yourself.

Your best option, though (i.e. easiest, most reliable, best supported but, perhaps, not fastest to execute), is to dump (datapump) the data out of one database and into the new one.  Depending on your network connectivity, you might be able to do this over a database link, or might have to resort to moving the dump file between machines.


How to find the nth highest salary in SQL? I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?

EmpID         Salary
1             90000
2             80000
3             54000
4             37000
5             12000
6             69000
7             50000

SELECT * FROM Employee E1
WHERE (N-1) = (
                SELECT COUNT(DISTINCT(E2.Salary))
                FROM Employee E2
                WHERE E2.Salary > E1.Salary
              )

If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!

What's happening here is the subquery is looking at each individual salary and essentially ranking them, then comparing those salaries to the outer salary (a separate query against the same table). So in this case if you said N = 4 it is saying:

WHERE 3 = (number of salaries > outer salary)

So looking at the data you have, let's rank them in order, and compare.

EmpID   Salary   How many *distinct* salaries are greater than this one?
-----   ------   -------------------------------------------------------
5       12000    6
4       37000    5
7       50000    4
3       54000    3
6       69000    2
2       80000    1
1       90000    0

So when n = 4, the row that will be returned is EmpID 3 (54000).

A much more intuitive way to write this query, in my opinion, is to use windowing functions like RANK(), ROW_NUMBER() or DENSE_RANK() (depending on whether or not you want ties). Let's take a look at how these different functions work against your data (and I've added an 8th row to represent a tie for 4th place):

DECLARE @salary TABLE(EmpID INT, Salary INT);

INSERT @salary VALUES
(1,90000),(2,80000),(3,54000),(4,37000),
(5,12000),(6,69000),(7,50000),(8,54000);

;WITH x AS
(
  SELECT EmpID, Salary,
    r  = RANK()       OVER (ORDER BY Salary),
    dr = DENSE_RANK() OVER (ORDER BY Salary),
    rn = ROW_NUMBER() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary, r, dr, rn FROM x;

Results:

EmpID  Salary   r   dr  rn
-----  ------   --  --  --
5      12000    1   1   1
4      37000    2   2   2
7      50000    3   3   3
8      54000    4   4   4
3      54000    4   4   5
6      69000    6   5   6
2      80000    7   6   7
1      90000    8   7   8

I don't think you'd want to use RANK() for this specific problem, because of the way it works there is no 5th place, for example. So now it comes down to whether you want to include multiple rows in the case of a tie, and if not, if you want an arbitrary row or a specific row based on some criteria. So adjusting the statement slightly:

-- if you want ties:
;WITH x AS
(
  SELECT EmpID, Salary,
    dr = DENSE_RANK() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE dr = 4;

-- results:
-- 3   54000
-- 8   54000

-- to take the *lowest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary,
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 3   54000

-- to take the *highest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary,
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID DESC)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 8   54000

-- Another way with TSQL

declare @n

set @n = 9  -- example for nth position

set @n=@n -1

select top 1 * from Employees
where Salary not in (select top (@n) salary from Employees order by Salary desc)
order by Salary desc


How can I determine which tables are causing the database to grow over time? I have a database in SQL Server 2008 that is growing all the time, and I'd like to know which tables have caused the most growth over the past 6 months.

SQL Server doesn't track this information. You'd need some kind of monitoring (whether it be a tool or home-grown) that keeps snapshots of table space over time, going forward. This can be simple or complex:

Viewing the report @Zane highlights in his answer, and saving the file every so often;
Taking your own periodic snapshots of views like sys.dm_db_partition_stats;
Looking at restored copies of older backups, as suggested by @Ali - however who knows how far your backups go, and if disk space is an issue, you might not have the luxury of restoring a bunch of them for side-by-side comparisons anyway.
Using an external tool like PowerShell to automate it, like the article @Kin points out;
Using a full-blown monitoring tool which tracks space, fragmentation, etc. over time (Disclaimer: I work for SQL Sentry).

Short of having already implemented one or more of these methods, or someone inventing a time machine, there's not really any good way to figure out how big a table was 6 months ago, or two weeks ago, or 20 minutes ago.

How i do it in MySQL without extra tools:

Run a query like "SHOW TABLE STATUS"  and save the result in a table with an extra timestamp field.

With this data you have a history of each table and can pick your tables for data length, rows or biggest diff. over time.

As the status data isn't much bytes you can run it with a daily cronjob. Runs perfect, easy and for free with onboard commands.

.

Same procedure like the link Marian posted before. The MS T-SQL command should be something like "SELECT * FROM INFORMATION_SCHEMA.TABLES" - just google. I found 2 possible solutions in seconds:

Listing all tables in a database and their row counts and sizes:
http://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx

SQL SERVER – Query to find number Rows, Columns, ByteSize for each table:
http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/


What configs influence whether user credentials/grants are written to the binlog in Mysql I have a fairly trivial master-master setup.
When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific database only:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      694 | my_db        |                  |
+------------------+----------+--------------+------------------+

Why are DDL queries like (CREATE USER) and inserts into mysql.user logged as well? I can't see any mechanisms/configs in the manual which explain this, seemingly, exception.

If it matters, the binlog format is:

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

* = Let's forget about the bad-practice of using binlog-do-db for now

You are replicating only my_db.

There are valid security reasons for having the option to not replicate mysql.

The quick solution is to add binlog_do_db=mysql and restart.  However, any GRANTs and CREATE USERs have already been lost.  Either re-issue them and hope they replicate, or replay them on the Slave(s).


How can I add back Identity to a column? I have a table:

CREATE TABLE [dbo].[Question] (
    [QuestionId]       INT              IDENTITY (1, 1) NOT NULL,
    [QuestionUId]      UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL
);

QuestionId was a primary key and when I dropped the primary key then the identity seemed to drop also. Is there a way that I can get back (add) the identity as a default for this column and have it so the new value starts at a value greater than the existing values ?

Dropping the primary key constraint from a table does not alter the IDENTITY property of the primary key column.

USE tempdb;
DROP TABLE dbo.Question;
GO
CREATE TABLE dbo.Question
(
    QuestionID INT NOT NULL CONSTRAINT PK_Question PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData VARCHAR(255) NULL
);

INSERT INTO dbo.Question (SomeData) VALUES ('test');

SELECT *
FROM dbo.Question;

ALTER TABLE dbo.Question DROP CONSTRAINT PK_Question;

INSERT INTO dbo.Question (SomeData) VALUES ('test2');

SELECT *
FROM dbo.Question;

The code above creates a table with a primary key, then drops the primary key.  It then adds a row to the table; and as you can see in the results below, the new row contains an auto-generated IDENTITY value for the QuestionID column.

Results:

Be aware that you could use the SQL Server Management GUI to modify the IDENTITY property of the column in question, however behind the scenes, SSMS will actually drop and recreate the table, which might be problematic to say the least.

The parameters of the IDENTITY allow you to seed the start value:

[QuestionId]       INT              IDENTITY (SEED_VALUE, INCREMENT) NOT NULL,

So get the MAX(QuestionId) from your table and use that + 1


2 Linked Servers using MSDASQL with conflicting Provider settings I have a linked server problem on a SQL Server 2012 database.

Linked Server 1 accesses a sql server 2000 database using sql server native client 10.0 and MSDASQL provider.

Linked Server 2 accesses a mysql database using mysql odbc 5.1 driver and MSDASQL provider.

If Level Zero Only is unchecked on the MSDASQL provider Linked Server 1 works but Linked Server 2 does not ("An invalid schema or catalog was specified for the provider “MSDASQL” for linked server").

If Level Zero Only is checked on MSDAQL provider Linked Server 1 does not work (error as above) but Linked Server 2 does.

Is there a way to provide the value of Level Zero Only on the provider string in the linked server settings? Or can anyone explain why this scenario is happening and how to fix it. Thank you!!

couldn't figure it out so I just solved it using openquery instead of selecting from linked server! not great but time constraints needed it solved.


Query without having to specify table's schema I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.

In the table properties it lists 'erpadmin' as the db schema. When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing.

Current result:

select *
from erpadmin.tablename

Desired result:

select *
from  tablename

If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema:

ALTER SCHEMA dbo TRANSFER erpadmin.tablename;

An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default.)

ALTER USER erpadmin WITH DEFAULT_SCHEMA = erpadmin;

The two part name you have (schema.table) is a good habit to get into though, so you can be explicit with which table you are referring to.  Some features require you to use a two-part name, Indexed Views are one example.

Start your command with the USE [tablename]
Your query does not have an associated database to reference and the database you are looking at is not the default for the logged in user
At the top of the query window it probably says "master"


Is there a way to see if database has been accessed through SQL Injection attack? I recently discovered that my website had a page with a SQL Injection vulnerability. Upon testing, it was easily exploitable with a pentesting tool like sqlmap. How do I figure out if the site database has been accessed with a SQL Injection attack, possibly exploiting this loophole? The server is running Apache/2.4.18 and MySQL Ver 14.14 Distrib 5.7.16.

Since you log all queries against the database, look for queries that don't belong.

Start with queries that failed. Assuming your app isn't riddle with bugs, there should be very few queries submitted to the DB that fail. Attackers typically have a set of queries they execute once they find a vulnerable app. If you apply the principle of least privileges on accounts used to access the DB, you should find a bunch of failed queries. There will also be queries that fail because a less sophisticated attacker might have a bunch of queries that are not compatible with your database edition, version or vendor.

Next, look for queries/commands that could not have been generated by your application. For instance, if the application should never accesses system tables, system functions, information schema, etc... but you see them in your log, that's a good indicator something might be amiss and warrants deeper investigation. Looking at your OS logs might be helpful here in case they tried or succeeded in planting a backdoor/rootkit.

Once you're done with that, look for queries with unusual/unexpected behavior. For instance, queries that return significantly more data than normal. If you see queries that access all tables around the same time or ran show tables then accessed each table in order, there's a good chance you have an attacker that tried (or succeeded) to exfiltrate data. Even something seemingly innocuous like SELECT * without a WHERE clause is suspicious (assuming your app doesn't do this). Note that this applies to any possible query, not just SELECT. The attacker might be some disgruntled employee and decided to randomly DELETE or UPDATE data to get you in trouble with your auditors or worse.

Looking at IP addresses is often not helpful even if you're looking at the web server logs unless you have a narrow and/or well known band of IP addresses your users connect from. Few attackers do it from their home PC anymore. Bots and proxies are found all over the world plus IPs are easily spoofed. Unless you get your ISP or Telco provider involved, it's unlikely you'll get very far looking at your own logs. One better indicator is your corporate firewall or network logs to find unusual outbound IP targets. An attacker that exfiltrates data has to send it to some real endpoint. That is usually to a place that isn't cooperative and/or moves slowly when foreign companies or law enforcement comes knocking.

See Apache's access.log.  Interesting things show up there.


Having troubles creating a specific TRIGGER in MySQL So I am trying to implement a trigger in MySQL that is triggered once a new user is registered.
The relation of my tables are:
USER_ACCOUNTS has primary key USER_ID.
RACE_TRACKS has primary key RACETRACK_ID.
HIGHSCORES has as foreign_keys USER_ID and RACETRACK_ID.

The trigger I am trying make, must create records in HIGHSCORES table in a way that each NEW.USER_ID has a record for each of the RACE TRACKS (RACETRACK_ID).

Using phpMyAdmin database management tool I came to this point.

BEGIN
DECLARE trackID INT;
DECLARE done INT DEFAULT FALSE;
DECLARE trackCURSOR CURSOR FOR SELECT USER_ID FROM INSERTED WHERE USER_ID = NEW.USER_ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

INSERT INTO HIGHSCORES SELECT trackID FROM RACE_TRACK;

OPEN trackCURSOR;
    REPEAT
    FETCH trackCURSOR INTO trackID;
    IF NOT done THEN
            BEGIN
            INSERT INTO HIGHSCORES (RACETRACK_ID, USER_ID)
            VALUES (trackID, NEW.USER_ID);
            END;
    END IF;
    UNTIL done END REPEAT;
CLOSE trackCURSOR;
END;

I am not getting any errors but when I am testing it if it works nothing happens. I am a bit confused to say the least because I do not have experience with triggers. If there is any way to enlighten me I would appreciate it.

Thank you in advance.

EDIT:
After some feedback more searching this is how my trigger looks now.

BEGIN
DECLARE v_RACETRACKID INT;
DECLARE FINISHED INTEGER DEFAULT FALSE;
DECLARE racetrackCURSOR CURSOR FOR SELECT RACETRACK_ID FROM RACETRACK;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = TRUE;

OPEN racetrackCURSOR;

get_racetrackID : LOOP
    FETCH racetrackCURSOR INTO v_RACETRACKID;
    IF FINISHED = TRUE THEN
          LEAVE get_racetrackID;
    END IF;

    INSERT INTO HIGHSCORES (RACETRACK_ID, BEST_LAP, BEST_RACETIME_1,
        BEST_RACETIME_2,USER_ID) VALUES (v_RACETRACKID,null,null,null
        ,NEW.USER_ID);
    END LOOP get_racetrackID;
    CLOSE

 END;

You need to put your code in a CREATE TRIGGER statement.

CREATE TRIGGER AddNewUserToHighscoresTable
    AFTER INSERT ON USER_ACCOUNTS
    BEGIN
         <insert code to run after inserts on the USER_ACCOUNTS table here...>
    END


SQL Server Order SELECT List with condition I want order the select list with condition. For example; I have t1 table and it has 3 columns and 6 rows

Table t1:
+----+---+---+
| id | x | y |
+----+---+---+
| 1  | a | 1 |
| 2  | b | 3 |
| 3  | c | 2 |
| 4  | d | 2 |
| 5  | e | 1 |
| 6  | f | 3 |
+----+---+---+

Ordering the select list without condition, that select query is easy:
SELECT y FROM t1 ORDER BY y result: 1,1,2,2,3,3
SELECT y FROM t1 ORDER BY y DESC result: 3,3,2,2,1,1

If I want that result 2,2,1,1,3,3 or 2,2,3,3,1,1, how can i do it with select query or c# entity framework?

For a simple solution based on just the one 'y=2' value (as per comments), the suggested order by case ... construct should suffice.

However, if the requirement is to be able to sort by a varying number of 'y' values, or perhaps order by a combination of columns ...

One idea that comes to mind ... use a table value constructor (TVC) to build an 'inline' table of your desired sort order; we'll then join this TVC back to your original data set to provide the desired ordering sequence.

We'll start by creating our table and populating with some sample data:

drop table if exists t1;

create table t1
(id     int
,x      varchar(30)
,y      int);

insert into t1 (id,x,y) values
(1,'a',1),
(2,'b',3),
(3,'c',2),
(4,'d',2),
(5,'e',1),
(6,'f',3);

For our TVC we'll generate a single-column derived table called dt(y) that includes the y values listed in their desired sort order.  We'll add the row_number() function to provide the column that we'll actually order by.  Finally, we'll wrap all of this in a common table expression (CTE) for ease of reference.

-- order by 'y' values 2, 1, 3:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(1),(3) ) as dt(y)  -- desired sort order is 2, 1, 3
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  1
 3       |  3

-- order by 'y' values 2, 3, 1:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)    -- desired sort order is 2, 3, 1
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  3
 3       |  1

We can now join our TVC with the original data set (table t1 in this case) to generate the desired sort order:

-- sort solely on the TVC's 'y' column:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y

from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid;

 id | x  |  y
 -- | -- | --
  3 | c  |  2
  4 | d  |  2
  2 | b  |  3
  6 | f  |  3
  1 | a  |  1
  5 | e  |  1

-- sort by TVC's 'y' column (ascending order), then by t1's 'x' column (descending order):

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y

from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid,
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2
  3 | c  |  2
  6 | f  |  3
  2 | b  |  3
  5 | e  |  1
  1 | a  |  1

The key item to remember is to make sure the TVC's records are listed in an order that matches the desired sort order.

One potential issue is that if you don't know how many 'y' values you have, or perhaps you only need to sort by a handful of known 'y' values (and don't care about ordering of other 'y' values), you could:

limit the TVC to just those 'y' values you're interested in and ...
join the main data set via an outer join to the TVC and ...
then default the the missing TVC's 'orderid' columns to some sufficiently large number to ensure their location in the sort order does not affect the 'y' values of interest

For example, assume we want 'y=2' values to be listed first, and we don't care about the rest of the 'y' values:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2) ) as dt(y)        -- only interested in 'y=2' records
)

select t1.id,
       t1.x,
       t1.y

from   t1                            -- left join w/ TVC/CTE
left
join   myorder mo
on     mo.y = t1.y

order by isnull(mo.orderid, 100),    -- default missing TVC.orderid's to 100
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2   -- 'y=2' records show up
  3 | c  |  2   -- first in our output

  6 | f  |  3   -- while we let the database engine
  5 | e  |  1   -- determine the order of the rest
  2 | b  |  3   -- of the result set; granted, for this
  1 | a  |  1   -- example we also had 't1.x desc'

Obviously you could still add other columns to the 'order by' clause to affect the final ordering.

Here's a dbfiddle for all of the above.

While the example queries (above) are based on hard coding the sort order for the 'y' column, this method could be expanded to allow for hard coding the sort order for any combination of columns.

Let's say for example you want a hard coded sort order based on 'y=2' and 'x in ('a','b','c')', the CTE/TVC would look like:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        x
        y
 from   (values (2,'a'),(2,'b'),(2,'c') ) as dt(x,y)
)

The main query could then be (outer) joined to myorder on the x and y columns:

with myorder as(...)

select ...

from   t1
left
join   myorder mo
on     mo.x = t1.x
and    mo.y = t1.y

order by isnull(mo.orderid, 100);

Again, if you don't need the flexibility then the more simple order by case ... construct should be sufficient.

You could add sort columns to the table for each use case. You need to have something logical in your table to sort on and I don't see how you could get 2,2,1,1,3,3 or 2,2,3,3,1,1 with your current columns.