Get the last statement execution date on an MSSQL Database

You will want to use the sys.dm_exec_query_stats dmv. If you simply want to see the last statement that was executed, this should get you started (note: you need to execute this in the context of your database e.g. by using the USE <db_name> command):

SELECT
deqs.creation_time
,   deqs.last_execution_time
,   dest.text
,   SUBSTRING(dest.text,
        (deqs.statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                WHEN -1
                    THEN DATALENGTH(dest.text)
                ELSE deqs.statement_end_offset
                END
                    - (deqs.statement_start_offset/2) + 1)))
AS statement_text
, deqp.query_plan
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY
sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY
sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE deqs.last_execution_time = (SELECT MAX(last_execution_time)
                                FROM sys.dm_exec_query_stats)


What's the best way to list groups of values that are contained in other groups

This does the trick but is not terribly fast:

select t1.x as subset_x, t2.x as superset_x
from t t1 join t t2 on(t1.y=t2.y and t1.x<>t2.x)
group by t1.x, t2.x
having count(*)=(select count(*) from t where x=t1.x);


Create Field With Default Value Of Different Field With No Data Loss

You're looking for a computed column if you want to keep the column with datetime2 data.

 ALTER TABLE Your-Table
 ADD helper1 AS
       (
          CAST(Datetime2-Column AS datetime)
       );

If you want to completely change the column, you can add a new column, transfer the data, then drop the old column and re-name the new column to the name of the old column. Steps below:

 ALTER TABLE Your-Table ADD Datetime2-Column DATETIME2

 UPDATE Your-Table
 SET Datetime-Column = Datetime2-Column
 GO

Then drop datetime2 column.

ALTER TABLE Your-Table DROP COLUMN Datetime2-Column

Then re-name your datetime column to your original datetime2 column's name. You can do this in Management Studio or with sp_rename


How to use select (query)alias on WHERE clause?

Renaming takes place after WHERE. You can either repeat the CASE statement:

SELECT res_ID,res_fName,res ...
   (case ... end) as Age_Stage
FROM resident_detail rd
LEFT JOIN ...
WHERE (case ... end) = 'Young Adult'

or nest the statement:

SELECT ...
FROM (
    SELECT res_ID,res_fName,res ...
       (case ... end) as Age_Stage
    FROM resident_detail rd
    LEFT JOIN ...
) AS T
WHERE Age_Stage = 'Young Adult'

Or you can try add it in an HAVING clause. As noted by ypercubeᵀᴹ in a comment, MySQL deviates from the standard by evaluate HAVING before renaming ( ... as Age_Stage). This is a trick that appears to be quite commonly used for MySQL in situations like this:

SELECT res_ID,res_fName,res ...
   (case ... end) as Age_Stage
FROM resident_detail rd
LEFT JOIN ...
HAVING Age_Stage = 'Young Adult'


How to copy a tablespace to another database without using Data Pump in Oracle 11G

This is the bare minimum assuming the simplest scenario.

1. Original export/import:

Source database:

export everything from the tablespace

exp userid=user/password tablespaces=example file=example.dmp

Destination:

copy example.dmp from source
create empty tablespace at the desired location, create users manually, then:

imp userid=user/password full=y file=example.dmp

or 2. Transportable tablespace with original export/import:

Source database:

put tablespace into read only mode:

alter tablespace example read only;

export metadata for transporting the tablespace

exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=example file=example_tts.dmp

Note that you get an EXP-00044 error here if you do not connect with SYSDBA.

Destination database:

copy example_tts.dmp from source
copy the datafile itself from source to the desired location (example01.dbf in below example)
create users manually, then:

imp userid=\'/ as sysdba\' transport_tablespace=y datafiles=/oradata/S112/example01.dbf file=example_tts.dmp

finally make the tablespace writable:

alter tablespace example read write;

More details can be found in the documentation:

Original Export

Original Import


you could try something like:

update h
set sal = t.sal
from @Helper h
join (
    select h.empname, h.saleid, h1.sal, row_number() over(partition by  h.empname order by h.saleId ) as n
    from @Helper h
    join @Helper1 h1 on h.empname = h1.empname
    ) t on h.empname = t.empname and h.saleid = t.saleid
where n = 1

(assuming the primary key of @Helper in on empname + saleid)


One of the first things Oracle does at restore time is pre-create the data files to the defined size. So you do need a way to store the full size of the files. A compressed filesystem could be used for that and with a little luck it works. It just does not solve the problem.

What you do want is to re organize the source database. The quickest way is to prepare a new database with correct sized tablespaces and datafiles. Next use datapump to transport the schemas to the new database. If needed, this can even be done online by using smart tools like dbvisit replicate (or goldengate if money is of no importance). Trash the old database when the two are in sync.


How do you achieve a physically sorted table file organization?

According to Chapter 7, Pages 148,149, under Storing Tables in Column-Sorted ORDER of the Book

By using ALTER TABLE ... ORDER BY statement, you have control over how MySQL stores the physical data for your tables, regardless of whether an index is in place.

You are theoretically correct, but the Storage Engine would have the final say on organization. The above statement I quoted applied to MyISAM. What about InnoDB ? Page 149 ends the chapter with

If the table had been defined with InnoDB instead of MyISAM, this would have no effect: InnoDB always stores its data by the Clustered Key.

The Clustered Index is essentially a rowid index as the MySQL Documentation says

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

This is why doing ALTER TABLE ... ORDER BY has no effect on InnoDB tables. Even if the rows get physically reordered, rows will be accessed through the Clustered Key by rowid anyway.
YOUR ORIGINAL QUESTION
Looking back at the index you made, the following can be realized

If you always enter data with increasing date values, the index simplifies doing date range scans.
If you load data from earlier dates, only the index can help you with range scans.

The only way to physically reorder the Clustered Key would be to run this
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO  mytable_new SELECT * FROM mytable ORDER BY colDate;
DROP TABLE mytable;
ALTER TABLE mytable_new RENAME mytable;

This would not be in your best interest to do this unless this is a one-time reordering. My advice would be to just stick with creating the index and let MySQL do all the necessary heavy lifting. In addition, you will need to run ANALYZE TABLE mytable; once a week to ensure the latest index statistics before running large date scans.


Everything there is probably a client round-trip, but you'll find that multistatements show individual durations for each component statement. The times are for the time spent executing it on the server, not the time including network access.

Entries with no transaction ID appear because PostgreSQL uses "virtual transaction IDs" to reduce the rate of consumption of transaction IDs, only assigning a real txid when there's a write or lock operation.


Any potential complications from ms sql backup db to disk = 'nul', disk = 'nul', ...?

So... you are getting alerted when a backup doesn't occur, and you want to suppress these alerts for certain databases, that you already know you don't need to back up?

If this is the case, it is very likely the alert system is reading the contents of msdb.dbo.backupset and other associated tables.

If you don't have the ability to exclude those in the alerts (and you can't just ignore them or make an email rule to delete them), then why don't you fake out the alert system by manually inserting those records yourself?

WARNING: THIS IS PROBABLY DEFINITELY A BAD IDEA, BUT FRANKLY NOT AS BAD AS RUNNING UNNECESSARY HOURS-LONG CPU AND IO-INTENSIVE BACKUPS THAT GO TO NUL AND ULTIMATELY DO THE EXACT SAME THING

So I'm not going to craft the INSERT statements for you (see my note above about this being a bad idea) but you'll probably need correctly-formed and properly associated records in some or all of the following tables:

msdb.dbo.backupfile
msdb.dbo.backupfilegroup
msdb.dbo.backupmediafamily
msdb.dbo.backupmediaset
msdb.dbo.backupset

I'd run a trace while running a backup to see what exactly it writes.

Good luck, but fixing the alert system is by far the best idea.


JOIN on Microsoft Access derived table not working

Perhaps rewrite the statement so Microsoft Access can more easily understand the requirements.

Something like:

SELECT
  T1.Product,
  T1.Color,
  T1.SEQMAX,
  Table1.Attribute
FROM (
    SELECT Table2.Product
        , Table2.Color
        , SEQMAX
    FROM Table2
        LEFT JOIN
        (
            SELECT
              Table1.Product,
              MAX (Table1.Seq) as SEQMAX
            FROM Table1
            GROUP BY Table1.Product
        ) AS MAXTABLE ON  Table2.Product = MAXTABLE.Product
    ) AS T1
    LEFT JOIN Table1
      ON T1.SEQMAX = Table1.Seq
        AND T1.Product = Table1.Product;


Use this script to help you create the grant script syntax :
Run a root od mysql admin user.

  select 'grant all privileges on ', table_name, 'to
someuser@somehost;' from information_schema.tables where table_schema = 'database name' and
table_name not in ('table without access', 'table without access');

This will create an output that you can run after.

Just to replicate the error :

mysql> grant select on BASE_BIB.* to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use BASE_BIB;
Database changed
mysql> show tables ;

Now revoke select from a table form inside the BASE_BIB database:

mysql> revoke select on BASE_BIB.users from test123@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test123'

on host '%' on table 'users'

No the right way to do it :

mysql> grant select on BASE_BIB.users to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on BASE_BIB.users from test123@'%';
Query OK, 0 rows affected (0.00 sec)

No error now !!

Managing access in mysql can be quite dificult !!

Once you gave him database.* you cannot revoke access for an object that is in that class.
MySQL doesn't expand the Hotels.* wildcard to the individual tables
 The permissions tables store the granted permissions. Therefore, since you didn't actually grant anything on Hotels.AllHotels , there's nothing for MySQL to revoke.
In this case you need to do it granular form the start !

Remove all privileges on database, table, column levels, etccc.

Grant privileges to EACH table, except 'you choose'.
Grant privilege to specified fields in table 'you choose'.


How to design relationships for variant data?

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2


User can't see database in SQL Server 2008 R2

I went through the sames steps as you described.  Restoring a database from one server to another and then fixing the user.  I didn't give any additonal permissions though, as you did with GRANT VIEW DEFINITION.

If however, I execute DENY VIEW ANY DATABASE to PUBLIC on Instance B, myuser can no longer see mydatabase in SSMS, but he can connect to mydatabase and run queries either way.

Without DENY VIEW ANY DATABASE , myuser on Instance B, was able to see the restored database.  I then used the opposite statement GRANT VIEW ANY DATABASE TO public to give that permission back.

Is there anything here in this test that you did that I didn't?

Is the user on the server with the restored database still able to connect to that database and run queries?

On Instance A

-- Create the mydatabase database

CREATE DATABASE mydatabase
CREATE TABLE mytable(t INT)
INSERT INTO mytable (t) VALUES (1),(2)

  create the login myuser, with default schema dbo and is member of
  db_datareader.

USE [master]
GO
CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [mydatabase]
GO
CREATE USER [myuser] FOR LOGIN [myuser]
GO
USE [mydatabase]
GO
ALTER USER [myuser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [mydatabase]
GO
EXEC sp_addrolemember N'db_datareader', N'myuser'
GO

-- Backup the database

BACKUP DATABASE mydatabase to disk = 'C:\MSSQL\Backup\mydatabase.bak'
GO

On Instance B

-- restore the database to instance B

RESTORE DATABASE mydatabase FROM DISK = 'C:\MSSQL\BACKUP\mydatabase.bak'
WITH FILE=1,
MOVE 'mydatabase' TO 'C:\MSSQL\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'C:\MSSQL\mydatabase_log.mdf',
RECOVERY, NOUNLOAD, STATS=10

-- recreate the login,

CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF

-- fix the user

USE [mydatabase]
sp_change_users_login 'UPDATE_ONE', 'myuser', 'myuser'

-- connect to instance B in SSMS as myuser

use mydatabase
select * from mytable

t
1
2

Connect as myuser to InstanceB using mydatabase

Show all databases connected as myuser to InstanceB

I found this on answer on stackoverflow which provides a query that returns users and their permissions for a given database:

https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database


Think about something like

UPDATE PCL, ( SELECT PCL.SrNo, MIN(HST.Date) minDate
              FROM PCL, HST
              WHERE HST.SYM=PCL.INST
                AND HST.DATE >=  PCL.Date
                AND HST.HP >= PCL.T2
                AND PCL.BS = 'B'
                AND PCL.T1A IS NOT NULL
              GROUP BY PCL.SrNo ) subquery
SET PCL.T2A = subquery.minDate
WHERE PCL.SrNo = subquery.SrNo;

Of course, proper indices on both tables is safe.


can you run sql server 2005 and sql server 2008 r2 in mirror mode?

The partners must be the same edition and the same version.

Please see BOL's reference on Database Mirroring Overview

  For a new mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.

  Since SQL Server 2005 Service Pack 1 (SP1), database mirroring partners and witnesses have been supported by Standard and Enterprise Edition. But the partners must use the same edition, and asynchronous database mirroring (high-performance mode) is supported only by Enterprise Edition. Witnesses are also supported by Workgroup Edition and Express Edition.


To directly answer your questions:

"Do relational databases ensure consistency in reads?"

A: In short, when the ACID principals, and specifically the Consistency part, were defined, this was long before distributed data was a concept for relational databases. So the Consistency from that standpoint was automatic because there's only a single server at play. Once a transaction was committed, the server was immediately Consistent with itself, regarding that transaction and all constraints associated with it, there were no other servers to synchronize and commit to. (Note this only focusses on one part to the meaning of Consistency in the ACID principals for the relevancy of OP's question regarding distributed data.)
Nowadays there's a multitude of ACID compliant relational database systems out there that are designed to handle distributed data across multiple servers. But let's take even Microsoft SQL Server and their AlwaysOn Availability Groups feature as an example for a moment. This feature is meant to be a High Availability / Disaster Recovery feature by synchronizing data from your primary server to other secondary servers. It's also ACID compliant because it can be configured to ensure Consistency across servers (when set to Synchronous mode). In a very rudimentary explanation on how it works is  it only fully commits a transaction once that transaction has been synchronized to all secondary servers from the primary server. This guarantees Consistency across all servers, at all times, and allows it to remain ACID compliant in a distributed server environment.

"Do relational databases ensure referential integrity constraint well?"

A: There's not really a way to quantifiably answer that question, but generally speaking, yes they do. Referential Integrity is one of the main points of using a relational database. When there's a well relationally defined schema, with proper constraints defined, such as foreign keys for example, a relational database is guaranteed to always enforce the rules of those constraints to ensure it always enforces the appropriate referential integrity. This ties into the definition of Consistency and how a relational database is ACID compliant.


T-SQL insert value based on identity column

You never set the SequenceId to anything.  It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation.  If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY value at the time that the insert begins.

If what you want is to create a column that concatenates a prefix and an IDENTITY value then you should use a computed column, for example:

CREATE TABLE dbo.Customers
( Sequence   int  IDENTITY(1,1) NOT NULL
, Prefix     nchar(1) NOT NULL
, CustomerID AS Prefix + CAST(Sequence + 1000000 as nvarchar(8))
, GivenName  nvarchar(50)
... etc ...
, CONSTRAINT UN_CustomersSequence UNIQUE (Sequence)
, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
)

You should be able to make CustomerID in the above table your primary key if you want.  Since the Sequence value is already unique (by virtue of being IDENTITY) I would question why you want to make a compound primary key.  Are you sure that this is the design you want?


Upgrade MySQL Master Slave Replication Cluster from 5.7.22 to 5.7.24

Upgrade the slave  -- This will have the Slave offline for minute(s).  This step may include mysql_upgrade.
Change the configuration to be Master-Master -- binlog, log_slave_updates, etc, on both servers.
Failover  -- This will have the Master offline for minute(s).  This also involves getting writing clients to point at the new Master.  (Presumably, you already have failover procedures in place and tested.)
Upgrade the former Master (now the Slave)

Optionally failback.

With only 2 nodes there will be short downtimes.  With some more advanced clustering techniques, plus a proxy, the downtime is less.  But never absolute zero.  See Group Replication or Galera.

You may as well leave it Master-Master instead of Master-Slave.  This avoids needing to do step 2 again.  However, you should set the Master that is not being written to to read_only.


number of seconds to years-months-weeks-days-HH:MM-SS

Assuming you know @Seconds and @StartDateTime, something like the following could work:

 DECLARE @Seconds INT = 50000000;
 DECLARE @StartDateTime DATETIME2(7) = GETDATE();

 -- Get the EndDateTime
 DECLARE @EndDateTime DATETIME2(7) = DATEADD([SECOND], @Seconds, @StartDateTime);

 -- Make a one-row CTE of useful info for logic and math later
 WITH Calendar_CTE
      AS (SELECT @StartDateTime AS [StartDateTime]
               , CONVERT(DATE, @StartDateTime) AS [StartDate]
               , DATEPART(YY, @StartDateTime) AS [StartYear]
               , DATEPART(MM, @StartDateTime) AS [StartMonth]
               , DATEPART(DD, @StartDateTime) AS [StartDay]
               , @EndDateTime AS [EndDateTime]
               , CONVERT(DATE, @EndDateTime) AS [EndDate]
               , DATEPART(YY, @EndDateTime) AS [EndYear]
               , DATEPART(MM, @EndDateTime) AS [EndMonth]
               , DATEPART(DD, @EndDateTime) AS [EndDay]
               , DATEADD(DD, -1, DATEFROMPARTS(DATEPART(YY, @EndDateTime), DATEPART(MM, @EndDateTime), 1)) AS [EndLastOfPreviousMonth]),
      -- Another one-row CTE, this time with each DATEPART's differences
      Differences
      AS (SELECT [EndYear] - [StartYear] - CASE
                                             WHEN [EndMonth] < [StartMonth] THEN 1
                                             ELSE 0
                                           END AS [YearDiff] -- If EndMonth is less than StartMonth, subtract 1 Year from the naive differnce
               , CASE
                   WHEN [EndMonth] >= [StartMonth] THEN [EndMonth] - [StartMonth] - CASE
                                                                                WHEN [EndDay] < [StartDay] THEN 1
                                                                                ELSE 0
                                                                                END -- If EndMonth >= StartMonth -> If EndDay is greater than StartDay, subtract 1 Month from the naive differnce
                   ELSE 12 - ( [StartMonth] - [EndMonth] ) -- If EndMonth < StartMonth, subtract the difference from 12
                 END AS [MonthDiff]                    -- e.g. this May to next March: 12 - (5 - 3) = 12 - 2 = 10 months
               , CASE
                   WHEN [EndDay] >= [StartDay] THEN [EndDay] - [StartDay] -- If EndDay >= StartMonth, then take a simple differnce
                   ELSE DATEDIFF(DD, DATEFROMPARTS([EndYear], [EndMonth] - 1, [StartDay]), [EndLastOfPreviousMonth]) + [EndDay] -- If EndDay < StartDay, find the remaining difference to the previous end-of-month, then add the rest of the days to the EndDate (this accounts for differnt length months)
                 END AS [DayDiff]                                                                                -- e.g. Jan 30th to Feb 2nd: (Jan 31st - Jan 30th) + 2 = 1 + 2 = 3 days
               , @Seconds / 60 / 60 % 24 AS [HourDiff]                                                              -- e.g. Jun 29th to Jul 2nd: (Jun 29th - Jun 30th) + 2 = 1 + 2 = 3 days
               , @Seconds / 60 % 60 AS [MinuteDiff]
               , @Seconds % 60 AS [SecondDiff]
          FROM [Calendar_CTE])

     -- Put it all together
      SELECT CONCAT([YearDiff], ' Year(s), ', [MonthDiff], ' Month(s), ', [DayDiff] / 7, ' Week(s), ', [DayDiff] % 7, ' Day(s), ', [HourDiff], ' Hour(s), ', [MinuteDiff], ' Minute(s), ', [SecondDiff], ' Second(s)') AS [TimeElapsed]
      FROM [Differences];


Join against two tables to get single combined result set

In this case, and due Coupon is an specific entry of another table I'd use a UNION.

As a suggestion, I'd try to convert 'Coupon' entry of orders_total, into another product added as another row to the orders table.

SELECT orders_id, products_name as name, cost
FROM orders o
JOIN  orders_products op on o.orders_id = op.fk_orders_id
WHERE o.orders_id = 1
UNION
SELECT fk_orders_id, name, total as cost
FROM   orders_totals
WHERE fk_orders_id = 1
AND   name = 'coupon'

Results:

| orders_id |   name | cost |
|-----------|--------|------|
|         1 |   Nuts |  100 |
|         1 | coupon |  -10 |

sqlfiddle here


how to take postgres server backup skipping some of databases?

Instead of using pg_dumpall, just use pg_dump and specify which databases you want to dump.  One possible way to do it is:

for db in <space-separated list of database names>
do
  pg_dump -U <user> -h <host> ${db} > ${db}.dmp
done


Is maintaining a single database an issue when the underlying project becomes larger?

I think you are correct in your answers and JNK is correct about locks. I see some reasons to have multiple databases.

Replication. If parts of your database should be replicated to local read servers they benefit from being on different database. I don't know about sql-server specifically but in mysql (and generally) a master can only maintain one binary log per database and if table A and B should replicate to some server and table C and D should replicate to another server than those needs to either be on different databases or the binary log needs to be filtered on the slaves.
Uptime and accessibility. If different parts of your data have different requirements on uptime and accessibility it would make sense to put them on different databases. One could have hot standby with all data replicated and the other could have a nightly dump.

But again, I think you're right :)


SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only

Of course they can block each other. The values you're inserting for PersonName can end up anywhere in the index, unless your customers only place orders alphabetically.

Whenever you perform an insert, the value for PersonName gets inserted to the

Leaf of the clustered index
Key of ix_PersonName
Leaf of ix_CustomerGuid

These inserts will lock intermediate (for keys) and leaf pages (for includes/the clustered index), and may cause page splits on full pages.

Even though your Id and GUID columns are sequential, which should direct most of the INSERT work to the end of those indexes where you're theoretically less likely to be selecting data from, PersonName can be anywhere in the indexes.

That can lead to blocking for any query that needs to touch PersonName.

Though singleton inserts should be brief, they can certainly pile up a bit, and may be hampered by any data synchronization (Mirroring, AGs), or just writing out to the transaction log (WRITELOG waits).

In an INSERT/SELECT only workload, RCSI or SI would behave like the READPAST hint, where you'd skip past reading locked rows being inserted.

Read Committed waits for write locks to commit or rollback, which can cause blocking in the same way that they could cause blocking waiting for an update or delete to finish.


MySQL 3.23.58 was the last release of MySQL 3.

Your version (3.23.56) still had an issue with improper locking and binlogging during a DELETE FROM tbl with no WHERE clause : Bug #169 : DELETE FROM t; does not lock 't' enough, causes bad order in binlog. It required a patch to get around it.

You should upgrade from 3.23.56 ASAP

I did find one repo with MySQL 3.23.58.

Still, you need to abandon MySQL 3.x. Go to MariaDB for the latest MySQL repos.


Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder

You can't work with multi-user Access databases on a non-SMB network storage (and in fact, until this bug is fixed, you can't reliably work with Access databases on SMB network storage either unless you avoid leasing).

Access databases require that many small writes to files and partial locks on files are allowed. Most network storage solutions, however, only support locking the entire file and don't support small writes or have terrible performance while supporting it, or cache data locally and cause terrible concurrency issues when multiple users are trying to use the same database.

This is very different from other types of files, where it's very usual to lock the entire file, not write to it at all until you save it, and overwrite the entire file when you save it (e.g. Excel). These kind of files do fine on a filetable.

Unfortunately, you will have to rethink your approach, as I don't think it will ever work. You might want to have a dedicated file share for Access that uses SMB with leasing disabled (or just migrate your Access data to SQL server and use linked tables and local copies of the database for every user)


One option would be to handle some of this logic inside your code running in the job... this works if you are doing things in batches, or if you are using a cursor or something similar.

Procedure to be Executed by Job

--create some_stored_procedure as

declare @procStartDate datetime = getdate()
declare @threshold int = 2

--doing something in batches, like batch delete, etc.
--could also uses a cursor here for reindexing, or what ever...

waitfor delay '00:00:05'

--after each batch, check the time that's expired
--if your threshold is met, put an informational message in the error log--or a fatal error if you want it to stop
if datediff(second,@procStartDate,getdate()) > @threshold
begin
    exec xp_logevent 50001,'Stored procedure: some_stored_procedure has been running past threshold', informational
end

select
    ProcStart = @procStartDate,
    CurrentDT = getdate()

Set Up Alert

General

Name it and select Type = SQL Server event alert
Set Severity = 10
Check "Raise Alert when message contains" and enter "some_stored_procedure" or something unique to the message in xp_logevent

Response

Check Notify operator and set the operator

That is, test it out! You can just create the alert and run the code above without creating a proc and it should email you.

Another Option

If this ins't feasible, and your threshold is high enough where you wouldn't mind running a job every X minutes, you could create a job to query the sys.jobactivity for jobs running that don't have a stop time and then send an email with sp_send_dbmail. Or, if your job has a lot of small steps, add this to the end of each step. What ever works for your environment.

declare @minuteThreshold int = 20

if exists(
SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
WHERE
    start_execution_date is not null
    AND stop_execution_date is null
    AND j.name = 'yourJobName'
    AND datediff(minute,ja.start_execution_date,getdate()) > @minuteThreshold)

exec sp_send_dbmail
    <your parameters here>


Null Or Multiple Parameters When Querying

You can do it this way (notice this avoids the unnecessary #temp table):

SELECT SomeColumns
FROM dbo.TABLE AS t
LEFT OUTER JOIN dbo.FunctionName(',',@Values) AS p
ON t.COLUMN = p.Value
WHERE t.COLUMN = CASE WHEN @Values IS NULL THEN t.COLUMN ELSE p.Value END;

But much, much, much better to pass @Values as a TVP instead of a string you have to split.

No idea what your split function actually looks like, but have a look at these posts:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql


Add timestamp to field automatically in PostgreSQL

You could use a trigger like this:

CREATE FUNCTION last_upd_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.lastupdate := current_timestamp;
   RETURN NEW;
END;$$;

CREATE TRIGGER last_upd_trigger
   BEFORE INSERT OR UPDATE ON finds
   FOR EACH ROW
   EXECUTE PROCEDURE last_upd_trig();


Is it safe to run antivirus software on my database servers?

For me, personally, I have not found it practical. But for 15+ years I have worked in environments that were dedicated to isolating and protecting the database servers. All of the activity that can lead to virus and malware infestation on a SQL Server machine can be prevented IMHO. Extremely limited access, no browsing from the server, no hosting of file shares, rigorous firewall protection, principle of least privilege, etc. Some of these lines get blurred a bit depending on the functionality of your server, for example filestream/filetable.

In my experience anti-virus is often a false security blanket. It is either responding as a post-mortem correction or, in some cases, doesn't have the signature yet for the new threat - either because it is not kept up to date by the system administrator or by the vendor (or both). Since rules and exceptions can change over time, if sysadmins can't keep anti-virus signatures up to date, how will they keep the rules and exceptions up to date?

Your goal should be to secure SQL Server so that you aren't concerned about anti-virus. If you're not having sex, you don't need to buy condoms. :-)


How to Create Aggregate function in SQL Server 2008

You must compile your CLR code as a library (.dll) using the .NET build tools or Visual Studio to create the assembly. See the MSDN article on Deploying CLR Database Objects for details. Assuming your source file is Concatenate.vb, you can compile from a command prompt (set your PATH to include the appropriate .NET Framework directory or cd to it):

vbc /target:library /out:Test.dll C:\Concatenate.vb

After compiling the library, you must notify SQL Server of the assembly's existence using CREATE ASSEMBLY. This step will resolve the error you are seeing by adding the assembly to the database catalog. Copy the DLL to the database server and execute T-SQL similar to the following:

CREATE ASSEMBLY Test from 'C:\Test.dll' WITH PERMISSION_SET = SAFE;

After these steps are complete, you can access the functions defined in an assembly using the CREATE AGGREGATE syntax.


Performance/Health checking queries for a user with Read permissions

Have your DBAs create

Adam Machanic's - sp_whoisactive
Brent's - sp_AskBrent

and grant execute permission to you.

That should be a good starting point to find out why your sql server is slow.

Note: Brent has already mentioned on How to Grant Permissions to Non-DBAs. You can follow that method to ask your DBA to grant permissions to you.


Why isn't my !include directive working in MySQL?

When I created foo.cnf, I set the permissions to 777.  Apparently world-writable config files are ignored:
~/Workspace/project_time/mysql ()  $ mysql --print-defaults -uroot -p<secret> new_database;
mysql: [Warning] World-writable config file '/Users/myself/Workspace/project_time/mysql/foo.cnf' is ignored.
mysql would have been started with the following arguments:
--i_am_a_dummy -uroot -p<secret> new_database

When I reset the permissions for foo.cnf to 755 and re-run the command, I get the following:
~/Workspace/project_time/mysql ()  $ chmod 755 foo.cnf
~/Workspace/project_time/mysql ()  $ mysql --print-defaults -uroot -p<secret> new_database;
mysql would have been started with the following arguments:
--skip_column_names --i_am_a_dummy -uroot -p<secret> new_database

And when I invoke the aforementioned SELECT statement in the CLI, it appears as expected:
mysql> select * from t1;
+------+------+------+
| 2001 | 1    | 1    |
| 2001 | 1    | 20   |
| 2001 | 1    | 30   |
| 2001 | 2    | 2    |
| 2001 | 2    | 23   |
| 2001 | 2    | 23   |
+------+------+------+
6 rows in set (0.00 sec)

EDIT: The warning is actually printed out even when not passing the --print-defaults flag, I just didn't notice it among the other output in the CLI.  Since --print-defaults results in much less output on start-up, the warning was easier to notice.


Get all CONVERT(datetime) styles (formats)

There is no such built-in function – or system table, or view – that would give you a list of all styles (formats). Otherwise people would not have been creating custom-made style lists (like this one, for instance) – as a way of a reminder, I guess, because the same information can be found in the online documentation, even if slightly differently arranged.

Go ahead and create one for yourself. New styles are sometimes introduced with new major releases, but those do not happen too often, so maintaining such a list should not be much of a burden.

Another thing is whether you really need to have such a table. Unless you are still using a pre-2012 version, you can format your datetime values arbitrarily with the FORMAT() function.


Can PostGIS use spatial indexes on foreign tables?

Assuming current Postgres 9.6, it can work.

Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run ANALYZE for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual.

Related answer with more details:

Postgres 9.5 foreign table inheritance not using indexes

Since PostGIS is an extension, this new feature of Postgres 9.6 is particularly relevant:

  Allow extension-provided operators and functions to be sent for remote
  execution, if the extension is whitelisted in the foreign server's
  options (Paul Ramsey)

  Users can enable this feature when the extension is known to exist in
  a compatible version in the remote database. It allows more efficient
  execution of queries involving extension operators.

Bold emphasis mine.

The use of indexes is bound to operators. Various PostGIS spatial functions are transformed to use operators internally when the query is planned. This related answer on SO has more explanation:

Can PostgreSQL index array columns?

Assuming Postgres 9.6 for local and remote server, and the same version of PostGIS installed, running this on the local server should do the trick:

ALTER SERVER your_foreign_server_name OPTIONS (ADD extensions 'postgis');

Here is a related blog post by Michael Paquier.


Can I find errors without using testing data for all the branches of each function?

Well, there is the Postgres extension plpgsql_check, successor of plpgsql_lint:

  plpgsql_check is next generation of plpgsql_lint. It allows to check
  source code by explicit call plpgsql_check_function.

I'm not currently using either, but the author Pavel Stehule is one of the core developers of PL/pgSQL (and also active around here).

Pavel announced it Dec. 2013 in this blog post.


An aggregate query without a GROUP BY is a scalar aggregate and (in the absence of any HAVING clause) returns exactly one row.

So if you do

SELECT COUNT(*)
FROM EmptyTable

Or

SELECT COUNT(*)
FROM SomeOtherTable
WHERE 1 = 0

Both would return the value 0 rather than an empty result set.

If you add a GROUP BY it then becomes a vector aggregate and returns one row per group (So would return nothing in the above examples) - See Vector and scalar aggregates

Your first query just consists of a scalar aggregate with @i added to the SELECT list so you get the single row. The second query has no aggregates of any kind and nothing matches the WHERE so you get no row.

Further Reading

Fun with Aggregates - Paul White


"Manage" option vanished on portal for Azure SQL database

The manage button is no longer available for V12 SQL databases on Azure. Official response from support engineer at Azure:

  The Manage button is not available on Azure SQL Database V12. It is
  only compatible with the previous version of azure SQL Database, V2
  (or V11). With the new database that you created, you may use either
  Visual Studio, or SQL Server Management Studio to manage them.


deleting small number of rows hangs in tsql database

Might be that cascading foreign key that table [Shift Offer] has pointing to table [Availability], slows down your delete operation

Try following:

1) create nonclustered index on column [Shift Offer][availability_id_fk]
and try running your query again; see if nonclustered index speeds things up

2) if still running too long, try disable the foreign key at all (run sql command)

alter table [Shift Offer] nocheck constraint [paste your constraint name
here I guess its availability_id_fk]

run delete command, and if succeeds and works fast, you will have to manually delete records from [Shift Offer] table, if it had any rows with availability_id_fk values that were deleted from [Availability] table, and  then enable foreign key back


How to force oracle accept insufficient swap size?

You possibly can't, really, unless there is a documented option for that. As the error is marked as "Severity:IGNORABLE" it is not unlikely that there will be an option to ignore "ignorable" warnings and proceed. Check the installation documentation for this information.

If not, and the problem is that you have a fixed swap partition/volume that you cannot increase because there is no space available to grow it into, you could temporarily add more swap by adding a swap file with something like:

dd if=/dev/zero of=/tmp/additionalswap bs=1048576 count=2100
chmod 600 /tmp/additionalswap
mkswap /tmp/additionalswap
swapon /tmp/additionalswap

before the installation process. This assumes you have sufficient space in the filesystem /tmp is found in, choose another location if not.

Once done, turn the extra swap space off with swapoff /tmp/additionalswap and remove the file.


Oracle, changed NLS_LANGUAGE and is now invalid and can't fix

You are not supposed to edit the spfile manually as it is in binary format and also stores a checksum. Start sqlplus, create a text version of the parameterfile:

sqlplus / as sysdba
create pfile from spfile;

Edit the initORCL.ora, then recreate the spfile from it:

create spfile from pfile;


How to setup replication(Master/slave) in MySQL 5.5.20?

Please create this script

Fron the Linux prompt, open up an editor like vi

vi /root/CreateMySQLSlave.sh

and add these lines to it

MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"

MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"

CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"

RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}

mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}

Next, make the file executable and run it

chmod +x /root/CreateMySQLSlave.sh
/root/CreateMySQLSlave.sh

That's it !!!

Make sure you set the IP Address for MYSQL_MASTER_HOST and MYSQL_SLAVE_HOST

By the way, don't worry about the dummy-file in the master_log_file parameter for CHANGE MASTER TO. Line 29 of the dump file should have the correct setting there due to the --master-data=1. You can see it by doing this to the dump file

head -29 /root/MySQLData.sql | tail -1

Make sure binary logging is already enabled on the master. Here is how you can tell: run SHOW MASTER STATUS;. If you get nothing back, you need to enable it like this:

Add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

then restart mysql

OK that takes care of binary logging for the Master.

You also need to make sure the Master and the Slave have different server IDs. Here is how you can tell:

Run SHOW VARIABLES LIKE 'server_id'; If the result comes up as 1, you must set it explicitly.

Goto the Master and add this to /etc/my.cnf

[mysqld]
server-id=1

Goto the Slave and add this to /etc/my.cnf

[mysqld]
server-id=2

The main thing here is to make sure server_id is explicitly set in my.cnf on both Master and Slave. You must also make sure the server_id values are different from all other servers involved in the replication topology.

If you are new to this, have a sysadmin join you to do this.


Restructuring table - start with PK or not?

The fastest mode in your case is to use

INSERT INTO..WITH(TABLOCK)
SELECT..
ORDER BY PK_KEYS

With the new table having the same PK defined on it.

This way you don't sort data even if insert in clustered table, and it will be minimally logged in simple and bulk logged recovery models.

Note this will work only if your PK is not identity where you try to preserve the old values by using set identity_insert on, when the rows will be sorted anyway.

In any case you can create your new table and ask server for estimated query plan when trying to insert in it. If you see there is no sort this will be the fastest way.

When your PK is on identity you'll get the sort in the plan and you may use select..cast(), cast(),...into without creating your new table and you still will have minimal logging, but then you create your clustered PK and have sort. Note in this case or you want to use sort_in_tempdb option otherwise your db may increase it size and if autogrouth is not set properly you'll be wait for it autogrow.


How to break out of error loop in transactional replication?

The error is caused by the code in the custom delete stored procedure for each article:

create procedure [dbo].[sp_MSdel_dboChild]
        @pkc1 int
as
begin
    delete [dbo].[Child]
where [ChildID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end

If no row is deleted, a special procedure is called and the transaction is rolled back.

In order to avoid this, I've created a post-Snapshot script that alters the all delete / update replication stored procedures and logs the error to a table instead.

CREATE procedure [dbo].[sp_MSdel_dboChild]
        @pkc1 int
as
begin
    delete [dbo].[Child]
where [ChildID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        insert into repl_errors (database_name, table_name, pkey_col, pkey_val)
        select dbo.fn_GetDB(APP_NAME()), 'Child', 'ChildID', @pkc1
end


why user with db_owner & db_BackupOperator database role is unable to restore database?

"Their database" for the dbcreator means owner in the sys.databases.owner_sid sense
Not members of db_owner.
The actual rights needed are described under RESTORE (and my added bold)

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.


Execute a script with SQLPlus containing html entity mappings

set define off

More details can be found in the manual:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2698854


DISCONNECTION (bad value of SQLNET.EXPIRE_TIME)

It seems that the settings is inside the dispatcher.
So we have shutdown the dispatchers (and of course start others) and it solve our problem


secure specific tables in Data Warehouse

You might be asking too broad of a question but if you wanted to have a user only have access to a table in SQL Server you could simply do a:

--Create Server Level User
USE [master]
GO
CREATE LOGIN [(UserName)] WITH PASSWORD=N'PutPasswordHere' DEFAULT_DATABASE=[DatabaseName]
GO

--Create DB Level Uesr
use [DatabaseName]
GO
CREATE USER (username) FOR LOGIN (username)

--Grant Select
GRANT SELECT ON DatabaseName.EDWTableName TO username

As your app matures you'll probably move to securing access through stored procedure and granting execute at some time but this answers your immediate question.  As far as how to secure SQL Server I'd recommend Microsoft's docs:
Securing SQL Server


Improving remake of primary key performance

You should use pt-online-schema-change.
This will allow for no downtime and you can pause it in the middle of a production period
Provided you have twice as much space free (at least 248GB)
STEP 01 : Select Folder
For this example, let's choose /home/whoeveriam/online_change
mkdir -p /home/whoeveriam/online_change
cd /home/whoeveriam/online_change

STEP 02 : Create local my.cnf
vi mysql_creds.cnf

The contents of mysql_creds.cnf should be
[client]
host     = (RDS Endpoint)
user     = blahblahblah
password = blahblahblahblahblahblah

STEP 03 : Create Dry Run Script
Create a file called Dry_Run.sh with this
BASE_FOLDER=(select absolute path)
MYCNF=$BASE_FOLDER}/mysql_creds.cnf
MYSQL_AUTH="--defaults-file=${MYCNF}"
cd ${BASE_FOLDER}

DB=mydb (put your DBName here)
TB=mytable (put your tablename here)
DBTB="${DB}.${TB}"

OPER=Change ; PHASE=Before ; STEP=DryRun ; EXEC_TYPE=--dry-run
RUNLOG=${BASE_FOLDER}/${TB}-${OPER}-${STEP}.log
ERRLOG=${BASE_FOLDER}/${TB}-${OPER}-${STEP}.err
SCTLOG=${BASE_FOLDER}/${TB}-${OPER}-${PHASE}.log
[ -f ${RUNLOG} ] && echo -e "\nAlready Executed" && cat ${RUNLOG} && exit

PAUSE_FILE=${BASE_FOLDER}/pause.txt
PTOSC_OPTS1="--recursion-method=none"
PTOSC_OPTS1="${PTOSC_OPTS1} --pause-file=${PAUSE_FILE}"
PTOSC_OPTS2="${PTOSC_OPTS2} --max-load      Threads_running=100,Threads_connected=500"
PTOSC_OPTS2="${PTOSC_OPTS2} --critical-load Threads_running=200,Threads_connected=1000"

ALTER_TABLE_STMT="DROP PRIMARY KEY,ADD PRIMARY KEY(column1, column2)"

T1=`date +%s`
pt-online-schema-change ${PTOSC_AUTH} D=${DB},t=${TB} ${PTOSC_OPTS1} --alter "${ALTER_TABLE_STMT}" ${PTOSC_OPTS2} ${EXEC_TYPE} >${RUNLOG} 2>${ERRLOG}
T2=`date +%s`
mysql ${MYSQL_AUTH} -ANe"SHOW CREATE TABLE ${DBTB}\G" > ${SCTLOG}

SQL="SET @t1=${T1}; SET @t2=${T2}; SELECT"
SQL="${SQL} FROM_UNIXTIME(@t1) ${STEP}_Initiated"
SQL="${SQL},FROM_UNIXTIME(@t2) ${STEP}_Completed"
SQL="${SQL},SEC_TO_TIME(@t2 - @t1) ${STEP}_RunTime"
mysql ${MYSQL_AUTH} --table -Ae"${SQL}" >> ${RUNLOG}
cat ${RUNLOG}

STEP 04 : Create Live Run Script
cp Dry_Run.sh Live_Run.sh

Change the line that has
OPER=Change ; PHASE=Before ; STEP=DryRun ; EXEC_TYPE=--dry-run

to
OPER=Change ; PHASE=After ; STEP=LiveRun ; EXEC_TYPE=--execute

STEP 05 : Start up Linux Screen Session
screen -S BigChange

STEP 06 : Execute Dry Run
chmod +x Dry_Run.sh
./Dry_Run.sh

STEP 07 : Execute Live Run
chmod +x Live_Run.sh
./Live_Run.sh

FINAL NOTES
If you wish pause the change in the middle of Production Time, run this
cd /home/whoeveriam/online_change
touch pause.txt

It will pause whether it is. Incoming changes will be handled by the triggers
When you want to continue, remove the file
cd /home/whoeveriam/online_change
rm pause.txt

You can monitor progress in the Log and Error Files


What to do when I get “mysqld dead but subsys locked” in my amazon ec2 micro instance?

Fatal error: cannot allocate memory for the buffer pool

You have only 1GB of RAM?  What is the value of innodb_buffer_pool_size; it should not be more than, say, 50M.

What changes did you make to my.cnf?  There may be other things to decrease.  (Or you could get more RAM.)

More

If you have more than just MySQL in that 1GB, it may really be impractical to have such a tiny VM.  Either split into to VMs, one for MySQL and one for app, or get get a bigger VM.


Use the ONLY key word:

TRUNCATE ONLY public.history_uint;

The manual:

  If ONLY is specified before the table name, only that table is
  truncated. If ONLY is not specified, the table and all its descendant
  tables (if any) are truncated. Optionally, * can be specified after
  the table name to explicitly indicate that descendant tables are included.

The schemas where parent and children live are only relevant as far as user privileges on the schema are concerned. Otherwise it's all the same to Postgres.


There is no way to do what you want. ENUMs are inherently a unique textual representation, stored in a special way. You can't add a dynamic to them that makes them useful in any sense to an INTERVAL.

MySQL doesn't really have ENUM types, it has more of a macro over intergers (a hack). MDEV-4912, MDEV-4913
MySQL also lacks an interval type

For these reasons, the best you can do is probably a lookup table.

See also,

https://dba.stackexchange.com/a/177075/2639


You could try a "script the script" approach by using the SQLCMD :out command to redirect output, with :connect and optionally :r to read the output, something like this:

SET NOCOUNT ON

DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0))

INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', N'[Lps_FinishPack_CZ]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_DK]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_NY]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_Supplier]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_372', N'[Lps_FinishPack_MO]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_678', N'[Lps_FinishPack_678]')

:out d:\temp\temp.sql

SELECT
    ':connect ' + serverName + '
USE ' + dbName + '
SELECT * FROM [config].[LpsPlant]
GO
'
FROM @tbl
GO

:out STDOUT
GO

-- Optionally read/run the output
--:r d:\temp\temp.sql

Run the script then go and have a look in the temp.sql file. Mine looks like this:

If you are happy with the output, uncomment the :r to run it, or just run/edit the script you have created.

Hope that makes sense.


MySQL Subquery returns more than one row

You need to add the LIMIT clause
SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0 LIMIT 1;

This is an oversimplified answer.
If there is a row with PK=1, you find it.
If not, a full table scan may ensue because of gameid not being indexed.
You are better off splitting the query with a UNION. Perhaps, like this:
SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

Since I do not know the data, I cannot predict any performance results.
Give it a Try !!!
UPDATE 2014-01-23 11:01 EST
The reason it works because of the goal I set for the query : Retrieve one value.
First look at the UNION
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)

The first SELECT can return at most one row.
The second SELECT can return at most one row because of the LIMIT 1.
In the worst case scenario, the UNION will have two rows.
Now look at the whole query:
SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

The outer part of the query will receive either 0, 1, or 2 rows.
Then, the part ORDER BY id LIMIT 1 forces the outer query to choose the first value.


Why do you need to store this value in the Number column? Much better to derive this information at query time than to bother storing redundant information and constantly have to update it as rows are added, changed or deleted.

SELECT DocumentID, SalesCompanyFinancialPeriodID,
  Number = ROW_NUMBER() OVER (PARTITION BY SalesCompanyFinancialPeriodID
    ORDER BY DocumentID)
FROM ACC.Document;


Conversion failed when converting date or time from character string error?

set dob = TRY_CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))


SQL Server 2012 Restore backup to new DB Name

Based loosely on Example E in the documentation, open a new query window and run:

RESTORE DATABASE MyDB_Copy FROM DISK = 'C:\blahblah\MyDB.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'MyDB' TO 'C:\blahblah\Data\MyDB_Copy.mdf',
---------------------------------------^^^^^
  MOVE 'MyDB_log' TO 'C:\blahblah\Data\MyDB_Copy.ldf';
-------------------------------------------^^^^^

The logical names are not important; the physical file names are. This makes assumptions about your logical file names and that there are only two; run EXEC MyDB..sp_helpfile; to be sure.

If you need to restore logs, then change RECOVERY to NORECOVERY:

  WITH REPLACE, NORECOVERY,
----------------^^

Then you can issue a series of:

RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\file1.trn' WITH NORECOVERY;

And on the very last one:

RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\fileN.trn' WITH RECOVERY;

Or if you only need part of a log up until a point in time (I assume you've checked where the LSNs and times are so you know exactly which files you need):

RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\fileN.trn' WITH
  STOPAT = '<some point in time Friday>', RECOVERY;

The way you said worked in previous versions would never have worked, unless the backup came from a different server. By default it will try to put the new mdf and ldf files in the exact same place, and this isn't possible.


Cannot remove idle connections to a Postgres database

It seems there is an always-running program that reconnects to this database as soon as it's disconnected.

Before terminating the connections, you might prevent this from happening again with:

REVOKE CONNECT ON DATABASE mydb FROM public, myname;


You can create your keys using a SEQUENCE. Every time you need a new ID from the sequence, you just have to issue SELECT NEXT VALUE FOR SomeSequence;. Unfortunately not all versions of SQL Server support sequences (it's 2012 and up), so if you're stuck with SQL Server 2008 R2 you will have to code it by yourself.

See Sequence Tables by Paul White for an example.
As far as I know, returning NEW from a BEFORE trigger means the INSERT or UPDATE operation that originally fired the trigger will take place. The trigger function may change the column values of it.  For a description and examples check the documentation.

You can achieve the same thing in SQL Server with regular DML statements such as UPDATEs and INSERTs, joining the destination table with the logical tables INSERTED and DELETED. You could also decide to implement the operation on behalf of the original statement, using an INSTEAD OF trigger.
Triggers in Postgres are either row-based or statement-based. Translating FOR EACH ROW into joins to INSERTED/DELETED is just a matter of using the correct keys to correlate the tables.


Definitely more ram. Regardless how you configure it - and your cache limits are way too low configured - on a 4gb machine as dedeicated db server you never will have more than maybe 3.5gb cache. With a 35gb database - unless it is mostly data you dont like - that is a pretty crappy relationship (10:1) which means the cache wont really help a lot. Again - unless your data is mostly stale and rarely used.

With a databbase that small - and 35gb is quite tiny - I would try to keep 100% in cache.

But in your current case.... you only have 512mb cache permission, if I read that right. That is REALLY tiny given the database size.

Disclaimer: I am a SqlServer guy - not MySql, but physics and math is the same regardless of program. I regularly deal with non-small databases (100-1000gb) and up to very large databases - my record size is 21tb.


How often does Postgres or Mysql make the fsync call?

I can only answer for PostgreSQL:
About sync on the WAL:
The transaction log (WAL) is synced to disk on every commit (unless you set synchronous_commit = off.
In addition, the WAL writer regularly syncs the WAL; this is governed by wal_writer_delay (this will only take effect if WAL hasn't been synced for a while). Also, if more than wal_writer_flush_after bytes have been written without a sync, data will be synced to disk.
About sync on the data files:
Data files are normally synced during checkpoints.
Since PostgreSQL uses buffered I/O, there is the danger that too many dirty blocks in the file system cache cause write spikes. Since version 9.6, PostgreSQL has various *_flush_after parameters that avoid that by regulary syncing data when a lot is written:

bgwriter_flush_after (default 512K) makes the background writer flush data to disk after it has written that amount of data
backend_flush_after (default 0 = disabled) makes the client session flush data to disk after it has written that amount of data
checkpoint_flush_after (default 256KB) makes the checkpointer flush out data after writing this much, rather than flushing all at the end


SQLCLR User-Defined Type (UDT) SCH-M blocking

When you use SQL CLR objects across databases, SQL Server will automatically convert them if certain conditions are met (https://technet.microsoft.com/en-us/library/ms178069(v=sql.105).aspx).

In my case, this automatic conversion was taking out the SCH-M locks. I fixed the problem by manually converting the contents of my SQL CLR type to binary, and passing it to the second db as varbinary(max).  I then deserialized back to my CLR type in the second db.


SQL Server 2008 Triggers and Merge Replication

The problem was an error in my trigger insert code. It was using INNER JOIN's and one was not correct so was not returning any data.


Counting using window function with partition over an empty table

Your query outputs one row for each row in the table, so an empty table must result in an empty output.
(COALESCE handles NULL values, but what you have is not a NULL value, but no value at all.)

You could add another query:

select count(*) over (partition by field_name)
from my_empty_table

union all

select 0
from my_empty_table
where not exists (select * from my_empty_table);


In what way are Access tables linked with SQL server?

If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery.
If I edit the linked Access table will it be reflected in the SQL server table?
Yes; as before, these updates will be visible on the other end on next refresh or requery.
Trusted Connection only controls how the connection is made to SQL Server, not behaviour after a connection is made.

The key concept here is that linked SQL Server tables are truly linked to ACCESS, not copied. If you desire a private copy of the data, copy the tables down instead of linking them.


Bottom line:  Can't be optimized.

But, you can speed things up some by minimizing the size of the rows.  Such will be especially beneficial if the table is too big to be cached.

Here's one approach.

Put the flags in a single MEDIUMINT UNSIGNED column.  That will give you up to 24 yes/no values in a mere 3 bytes.  DATE is 3 bytes.  Your id is perhaps 4 bytes.  Put those three things into an index -- total of 10 bytes (plus a lot of overhead).

I suggest this ordering:  INDEX(date, flags, id).  Then have code like:

SELECT id FROM tbl
    WHERE date >= ... AND date < ...
      AND (flags & $mask) = $value;

The steps will be

Range scan using the first column of the index (date) to limit the number of index 'rows' to search.
Perform the boolean operation to filter out rows based on the flags
Get the id so you can go off to the table to get whatever other info you need.

Something like:

SELECT b.*
    FROM (
        SELECT ... -- as above
         ) AS filter
    JOIN tbl AS b  USING(id)
    ORDER BY ...

The ORDER BY on the end won't be able to use any index, but it will benefit from having all the filtering already done, so only the desired number of rows will be involved.


What is the difference between COUNT(*) AND COUNT([attribute]) in the following scenario?

In terms of the speed:

As I know that both the queries (select count(*) or Select Count(id)) are using same execution plan, so should use same amount of CPU as long as blocks are fetched from either buffer pool or from disk not mix.

  In terms of the result, and in your case:

if you said count(*), then it will count the row even if you don't have employees since you are making left join.
if you said count(employee_id), then this will only count the rows "which are exists", means not the null rows.. I think this is what you need to have 0 for your case
If you want to mention the field and you want to count it even if there's no row you have to say count(NVL(employee_id, 123)) while 123 is any value in case null


Client times out, while MySQL query remains running?

You need to look at what default values are in place for timeouts:

mysql> show variables like '%timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 60    |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 60    |
+----------------------------+-------+
10 rows in set (0.00 sec)

Usually, I watch for several timeout variables. This is very imperative if you use MySQL remotely from MySQL Workbench, mysql client, or PHP app on an app server contacting MySQL on a DB Server.

Here is what the MySQL Documentation says one these settings:

wait_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
interactive_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.
net_read_timeout (Default 30) : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
net_write_timeout (Default 60) : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

Please make sure these timeouts are set high enough to accommodate queries that may run for a very long time, which may include:

Mass UPDATEs
Mass DELETEs
ENABLE KEYS on a Large MyISAM

To deal with queries that keep running after you lose touch with it, you have to run KILL against the process ID of the long running query. Even with the KILL command, you will have to wait for any query that is in the middle of disk-intensive steps or have internal mutexes in progress.


Is there any way to prevent the memo structure from being pruned?

We know that the memo structure is pruned and some expensive
  alternative plans are discarded during optimization. I was wondering
  if there is any way to prevent this and let the optimizer just
  consider every possible plan and select the best from all
  alternatives?

There is, but I don't publicise it because it would be misunderstood and misapplied. In any case, it would not result in an exhaustive search of the plan space because only a limited set of transformations (ones that commonly produce good results) are implemented.

Preventing pruning and discarding would generally simply result in (much) longer compilation times without much of an improvement in final plan quality, if any.

Ultimately, the question is a natural and reasonable one, but it is predicated on a misunderstanding of the goals of the SQL Server query optimizer: it is designed to find good plans for common queries quickly. It is not built on a framework designed for exhaustive search.

If you have a real-world situation that would benefit from a different approach to optimization, you could make the case for it at the Connect website (though I do think it unlikely Microsoft would invest the necessary engineering resources).


What's the best practice of inserting a collection of data into a SQL Server database

You don't want either of those two options, though #2 is quite close. Option #1 is slow due to being row-by-row and each call being its own separate transaction. Even if you wrap all of the Stored Procedure calls into a single explicit Transaction, it will still be slower than a set-based approach.

Option #2 is flawed mainly for the additional work being done in both layers: joining the collection into a single string in the app layer, and splitting the string back out into elements in the data layer. However, the general structure of this idea — passing in just two params: ID and the collection of strings — is the way to go. You just need to use a Table-Valued Parameter (TVP) so that you can iterate through the collection as is, and receive it as a pre-populated Table Variable in SQL Server.

You do this by creating a User-Defined Table Type (which here could be a single NVARCHAR column) and using that as the parameter for the collection (hence the "Table"-Valued "Parameter").

The other piece to this puzzle is, on the app code side, creating a method that accepts int ID, List<String> _listobj and returns IEnumerable<SqlDataRecord>, and then use that method as the "Value" for the SqlParameter that is mapped to the TVP. Meaning, something like:

private static IEnumerable<SqlDataRecord> SendRows(List<String> RowData)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("SomeSomething", SqlDbType.NVarChar, 4000)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   foreach (string _CurrentRow in RowData)
   {
      _DataRecord.SetString(0, _CurrentRow);

      yield return _DataRecord;
   }
}

Then, you would just pass in SendRows(_listobj) as the value for the TVP parameter. When you execute the SqlCommand, it will call that method and stream that collection into SQL Server.

The technique shown above requires no additional memory or processing, it just sends the collection as a collection. No need for DataTables or String.Joining or splitting :-).

To see a complete example code setup for this, please see my answer to the following Stack Overflow question: Pass Dictionary to Stored Procedure T-SQL.


Mongodb - Error after turning fork mode on

Use full path with path and dbPath. So, both "should" start with /. When you are in fork, there is no "base" directory.


Most SQL Servers are configured for the non-system databases to exist apart from the system databases.  That is what is normally meant by the default path.

You can lookup some code for finding this at:

http://www.codeproject.com/Articles/684020/Checking-MS-SQL-Server-database-files-default-path

Also, Alex Aza has provided a popular post over at StackOverflow. The key is to get the data from the registry keys where they are stored.  Here is a snippet from Alex's longer script:

declare @DefaultData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultData', @DefaultData output

declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultLog', @DefaultLog output


can I add a custom SSRS e-mail subscription that forces the "TO" address

What version of SQL Server/SSRS are you using? If you have SQL Server Enterprise then you can use Data Driven Subscriptions which allows you to use a query to define the various subscription delivery parameters such as To, CC, Subject etc.


create table script with constraints in sql server 2008

Here's an example of creating a constraint at the same time as a table:

create table Table1
(
multiplier1 int,
multiplier2 int,
answer int,
constraint PK_Table1 primary key(multiplier1, multiplier2),
constraint CHK_Table1_Answer check (answer = multiplier1 * multiplier2),
check (answer / multiplier1 = multiplier2)
)

Per @Martin Smith's comment, the constraint name is optional.  The database will generate a name for the third constraint, like CK__Table1__22AA2996.

Constraints that are introduced on their own line can can refer to multiple columns.


PL/SQL Script Line with Only a Dot...What is the purpose/function of the dot?

A line with only a dot, ".", appears to be a SQL*Plus command to end the input and save it in the buffer without executing it.

SQL>
SQL>
SQL> DECLARE
  2     qty1 number;
  3  BEGIN
  4     select count(*) into qty1 from dba_roles;
  5     dbms_output.put_line('Number of roles: '||to_char(qty1));
  6  END;
  7  .
SQL>
SQL>
SQL> LIST
  1  DECLARE
  2     qty1 number;
  3  BEGIN
  4     select count(*) into qty1 from dba_roles;
  5     dbms_output.put_line('Number of roles: '||to_char(qty1));
  6* END;
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2     qty2 number;
  3  BEGIN
  4     select count(*) into qty2 from dba_procedures;
  5     dbms_output.put_line('Number of procedures: '||to_char(qty2));
  6  END;
  7  .
SQL>
SQL>
SQL>
SQL> LIST
  1  DECLARE
  2     qty2 number;
  3  BEGIN
  4     select count(*) into qty2 from dba_procedures;
  5     dbms_output.put_line('Number of procedures: '||to_char(qty2));
  6* END;
SQL>
SQL>
SQL>
SQL> /
Number of procedures: 26744

PL/SQL procedure successfully completed.

SQL>
SQL>


Active Directory not working through SQL server Management studio

Normally when you receive an error message with the Error: 18456, ... it will contain additional information somewhere.

Detailed SSMS Login Error

In the case of the SSMS Login, the error message will be displayed together with a couple of icons. One of them is page with a white cross on red background. If you click on that icon you will be presented with the detailed error message, which could look a bit like this:

The detailed error message will contain additional information:

Server Name: .
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Detailed Login Error in ERRORLOG

Additionally you can find the full error message in the ERRORLOG of your SQL Server instance. The entry will look similar to the following:

2018-08-10 15:37:56.10 Logon       Error: 18456, Severity: 14, State: 5.
2018-08-10 15:37:56.10 Logon       Login failed for user 'bigger'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Decoding The State Number

With the state number you can then go and check Aaron Bertrand's Blog's article:

Troubleshooting Error 18456 (SQLBlog / Aaron)

Further Analysis

If you can edit additional information into your question, then we might be able to find the root cause together.


Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM.

The host name "DB server 1" along with the "Microsoft SQL Server" program name indicates a linked server query, not a query executed directly from the application.

From the DBA perspective what else I can do to get the real issue?

As @Learning_DBAdmin suggested in comments, examine the active queries running on DB server 1 during the blocking episode. One or more should show the query with the linked server along with the originating application host and program name.
The real issue is likely the need for query/index tuning to optimize the remote query. You may need indexes on the join/where clause columns. The explict conversion on Tbl1029.MyColumn3 will preclude using an index on the column efficiently (if one exists). The resultant full scans can lead to blocking.


You have a few possibilities.

To change work_mem for a user is easy:

ALTER ROLE alice SET work_mem TO '5GB';

If you can arrange things like alice is the user running those heavy queries, this might be enough.  If this number is huge (a significant portion of the RAM you have on your server), it makes sense to limit the user to have only a small number of connections (even 1 might make sense) at any time:

ALTER ROLE alice WITH CONNECTION LIMIT 2;

If this is not feasible, you can store your queries as functions.  The advantage of this is that you can set chosen parameters when defining the function, or after it:

ALTER FUNCTION heavy(integer) SET work_mem TO '2GB';
ALTER FUNCTION very_heavy(integer) SET work_mem TO '13GB';


How to add an extended property to a table type?

The MSDN documentation:

http://msdn.microsoft.com/en-us/library/ms180047.aspx

appears to be incorrect.  TABLE_TYPE is not a valid level1type value.

Documentation is also missing here:

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

but a comment at the bottom provided the answer.

Extended properties may be specified for a table type by using a leve1type of 'TYPE'

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'An INT value.',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TYPE',  @level1name = AllTypesRecord,
@level2type = N'COLUMN', @level2name = 'Id'
Go


So you're going to pass in two optional parameters:

@host
@env

Example sql (to be put in a stored proc):

Select top 1 * from dbo.FilterTable ft
Where
    ((@host is not null and ft.host = @host) or ft.host is null)
    And ((@env is not null and ft.environment = @env) or ft.environment is null)
Order by
    Case
        When ft.host is not null and ft.environment is not null then 1
        When ft.host is not null then 2
        When ft.environment is not null then 3
        Else 4
    End asc


Scrubbing sensitive data is a vast topic. You basically need to define as per your company's data protection policy - what is considered PII (Personally Identifiable Information) or what is sensitive that you don't want other people to see ?

SQL Server 2012* does not have any native tools to mask or scrub sensitive data.

In my company, we have developed in-house tools that will mask client sensitive data like email, phone, names, and many more.

If you have to do it on a repeating basis, I would suggest you to take the schema from PROD and then use 3rd party tools like data generator from RedGate to generate test data.

There are several methods that will be useful to mask sensitive data (once you have identified - what is considered sensitive) like :

Masking Personal Identifiable SQL Server Data
Obfuscating your SQL Server Data

Brent has written a good blog post on How Do You Mask Data for Secure Testing?

* In SQL Server 2016, Microsoft has introduced Dynamic data Masking

  It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results.


How can I establish whether it would be a good idea to upgrade from Express to Standard?

I am trying to work out whether it would be better to delete records or upgrade.

This is a business decision, not a technical one. Do not just delete data "to make space" - if the data is important and must be retained, then the organization will have to find a way to pay to keep it around.

You can't downgrade SQL Server in the way you're suggesting.

If you want to explore the possibility of upgrading to a paid edition of SQL Server, I suggest setting up a new server with Developer Edition installed (Developer Edition is free). Then take a backup of your current database, restore it to this test server, and run a sample of your workload against that.

If you decide you need to upgrade to Standard Edition, this method (backup & restore) is also how you'll move your data over. I do not recommend performing an in-place upgrade (which is what your proposed method suggests).

If you're currently satisfied with the overall performance of your setup, and only looking into upgrading due to the database size limitations of Express Edition, you have a few options:

Create a second database which mirrors the first, splitting the data between the two. This will require application changes
Move "old" data to a second (archival) database based on age, deleting it from the original database
Just delete the old data altogether
Implement page- or row-level compression on your tables & indexes to buy some more time. But eventually, you'll hit the 10GB limit anyway.
Migrate to a cloud-hosted database platform like Azure SQL DB where you'll pay for what you use

Options 2 & 3 should only be considered if the business requirements for the data allow for it. Option 5 should only be considered if the business's data governance policies allow for it.


How to query transfers for single source to single destination?

I don't know all of your source data (or why there isn't any type of unique constraint that would prevent full-on duplicates or a source with multiple destinations), but given only the sample data supplied:

;WITH s AS
(
  -- first let's eliminate duplicates
  SELECT DISTINCT Source, Destination
    FROM dbo.MyTable
)
SELECT Source, Destination
FROM s
WHERE NOT EXISTS
(
  SELECT 1 FROM s AS d WHERE

  -- eliminate chains in either direction:
    d.Destination = s.Source OR d.Source = s.Destination

  -- eliminate any source with multiple destinations:
    OR (d.Source = s.Source AND d.Destination <> s.Destination)

  -- eliminate any destination with more than one source
    OR (d.Destination = s.Destination AND d.Source <> s.Source)
);

SQL fiddle demo


First of all you should not be mounting any file system on the device that Aerospike is configured to use. Aerospike manages its storage directly. The file system metadata will corrupt Aerospike storage.

In storage engine device configuration Aerospike will use the entire device irrespective of how much data you actually inserted. If you do not want to give entire device you can create raw partition on the device (without any file system) and configure the raw partition as device in Aerospike.

To see how much of the device is being used, you should be looking Aerospike stats. To wipe the device you can either use the dd command or the more time efficient blkdiscard.


How to get exception context for a manually raised exception in PL/pgSQL?

This behaviour appears to be by design.

In src/pl/plpgsql/src/pl_exec.c the error context callback explicitly checks to see if it's being called in the context of a PL/PgSQL RAISE statement and, if so, skips emitting the error context:

/*
 * error context callback to let us supply a call-stack traceback
 */
static void
plpgsql_exec_error_callback(void *arg)
{
        PLpgSQL_execstate *estate = (PLpgSQL_execstate *) arg;

        /* if we are doing RAISE, don't report its location */
        if (estate->err_text == raise_skip_msg)
                return;

I can't find any specific reference as to why that is the case.

Internally in the server, the context stack is generated by processing the error_context_stack, which is a chained callback that appends information to a list when called.

When PL/PgSQL enters a function it adds an item to the error context callback stack. When it leaves a function it removes an item from that stack.

If the PostgreSQL server's error reporting functions, like ereport or elog are called, it calls the error context callback. But in PL/PgSQL if it notices that it's being called from a RAISE its callbacks intentionally do nothing.

Given that, I don't see any way to achieve what you want without patching PostgreSQL. I suggest posting mail to pgsql-general asking why RAISE doesn't provide the error context now that PL/PgSQL has GET STACKED DIAGNOSTICS to make use of it.

(BTW, the exception context is not a stack trace as such. It looks a bit like one because PL/PgSQL adds each function call to the stack, but it's also used for other details in the server.)


Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small.

I have discussed TOAST before in the DBA StackExchange

May 01, 2012 : what is bigger than a longblob?
Mar 21, 2012 : Are many NULL columns harmful in mysql InnoDB?
Jul 19, 2011 : Different MySQL Datafile Sizes After Restoration

YOUR QUESTION

To answer your question, neither InnoDB nor MyISAM feature an external mechanism like TOAST. The closest thing for InnoDB is overflow pages (See MySQL Documentation).

SUGGESTION #1

You will have to tune for BLOBs in one or more ways:

Create a table with a BLOB field and a foreign key to the parent table for the remainder of the data.
In the event of multiple BLOBs in a single row, find a way to combine all the BLOB data into a single BLOB field and compress it and/or change the ROW_FORMAT (See MySQLPerformanceBlog in this one).
Look for the biggest BLOB in your dataset and scale up the innodb_log_buffer_size and innodb_log_file_size to accommodate 10 such BLOBs (InnoDB only)

Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

SUGGESTION #2

Switch to PostgreSQL :-(

GIVE IT A TRY !!!


AddWithValue performance and plan cache implications

I strongly recommend always using the method that allows you to explicitly set the data type and precision. Yes, this is more work, both up front and if the data type in the database ever changes (which is common when, say, an IDENTITY column exceeds the range of int and it is upsized to bigint).

cmd.parameters.Add("@VehicleId", SqlDbType.Int).Value = Vehicles.VehicleId;

This is simply better practice, since other forms of AddWithValue() can cause issues. For example, the wrong types will be inferred here:

cmd.parameters.AddWithValue("@intParam", 5);        -- tinyint
cmd.parameters.AddWithValue("@intParam", 257);      -- smallint
cmd.parameters.AddWithValue("@varchar20", "bob");   -- nvarchar(3)
cmd.parameters.AddWithValue("@varchar20", "frank"); -- nvarchar(5)

In the int range you're unlikely to come across much in the way of problems, but if you extend this to other types, and are using ad hoc queries as opposed to stored procedure calls with strongly typed parameters, things can get very bad very quickly. As David pointed out, you can get bad plans, for example implicit conversions that can make seeks switch to scans. Allowing the default nvarchar to be passed through to a varchar column can cause massive performance issues, as Jonathan Kehayias demonstrates here. And allowing .NET to determine the length of the parameter based on the length of the string can - depending on version of SQL Server and parameterization settings - lead to a different plan for every unique length of string passed in. This won't make those specific queries slower, but they can waste a lot of memory, since there will be a redundant plan stored and used for each length.

This question has been asked in other places:

MSDN Forums
Stack Overflow

And Joel Coehoorn has blogged about the type conversion issues too, recommending you stay away from AddWithValue() (even in cases where you "know" the problem doesn't exist):

Can we stop using AddWithValue() already?


Connect to SQL Server using windows authentication from another PC without Active Directory

If none of the machines involved have Active Directory authentication enabled via Kerberos, which precludes using an online-based Microsoft Account (Windows 8 and higher), you should be able to connect to SQL Server using Windows Authentication assuming you follow these points:

Ensure SQL Server is configured to use TCP connections.
Ensure the Windows firewall is either disabled or has SQL Server connections allowed.
The machine where SQL Server is installed needs Windows user accounts for all the people who will be connecting via the network.  For instance, if the SQL Server computer is named MyPC, and you have User1, User2, and User3 who will be using the SQL Server, MyPC must have accounts setup for User1, User2, and User3 with the same password those user accounts use on their own PC.
You create logins in SQL Server for those user accounts, i.e.:

CREATE LOGIN [MyPC\User1] FROM WINDOWS;
CREATE LOGIN [MyPC\User2] FROM WINDOWS;
CREATE LOGIN [MyPC\User3] FROM WINDOWS;

The client machines may need to connect to the server using the server's IP address instead of it's name.   If the server's IP address is 192.168.0.100, and it is listening on port 33333 then they would use this as the server name to connect to:

tcp:192.168.0.100,33333

If you follow all the above advice, then receive the following error when connecting, then the client PC is using Kerberos authentication, and nothing will help you connect.

  The target principal name is incorrect.  Cannot generate SSPI context.


MySql - Changing expire_logs_days without restarting the server

thanks @dezso

here is how I changed it: SET GLOBAL expire_logs_days = 4;


Few reasons could be:

one of them - 'eximstats' has other host than @'localhost' in user table
more than 1 user with same name but different host could also give
error

try to manual connect to the database with this user credentials and check errors if it will be.


Restore .mdf data file for a File Group in SQL Server

The only way to restore a single file group is to run in all the logs so it's up to date with the rest of the files.  This would of course run in the bad data modification you're trying to prevent but it's necessary to ensure consistency throughout the database.  You'll need to restore it to another location and move the data over.

http://msdn.microsoft.com/en-us/library/ms178099%28v=SQL.105%29.aspx


DB2 - how to safely blow away database on linux?

You cannot drop a database if someone is connected to it. You would need to force everyone off the database first (and to do that you need SYSCTRL authority for the instance, and I'm gonna guess DBADM at the database level if you don't have instance level authority).

db2 connect to xydb
--the next command forces off the users asynchronously
db2 quiesce database immediate force connections
--but you need to unquiesce to drop
db2 unquiesce database
db2 connect reset
-- hope you don't have a connection in between
-- also make sure the database isn't activated....
db2 deactivate db xydb
-- do this as a safeguard cause there is no going back once you drop it.....
db2 backup db xydb to '<path you provide>' compress
db2 drop db xydb

or if you want to go instance level and lock down the instance.....

db2 quiesce instance <instance name> restricted access immediate force connections
-- make sure the db isn't activated
db2 deactivate db xydb
-- do this as a safeguard cause there is no going back once you drop it......
db2 backup db xydb to '<path you provide>' compress
db2 drop db xydb
-- wait a few seconds
db2 unquiesce instance <instance name>

Then don't forget to backup logs and archived logs and then you can clean those out as well.

Now you can attempt to create a new database. However, I'm guessing you need to worry about migrating your data? Are you attempting to migrate to a new fix pack or version of DB2? Are you attempting to upgrade your schema? You can always test out the "upgrade" on another server first. I guess I'd like more information on your "upgrade" and I can tweak this answer to hopefully respond to that part rather than just giving you hints on how to drop a database.


Is the update in this MERGE Statement redundant?

Something needs to be in the WHEN MATCHED THEN clause, otherwise you don't get the Id back when the row exists.

However, you don't need to update the target table.

DECLARE @dummy int;

;WITH    _ContactPhoneNumbers ( [PhoneNumber] )
              AS ( SELECT    DISTINCT
                            ( [#ChannelData].[CallerAni] )

                   FROM     [#ChannelData]
                 )
        MERGE [PhoneNumber]  WITH ( HOLDLOCK ) _target
        USING [_ContactPhoneNumbers] _source
        ON [_target].[PhoneNumber] = [_source].[PhoneNumber]
        WHEN NOT MATCHED THEN
            INSERT ( [PhoneNumber] )
            VALUES ([PhoneNumber] )
        WHEN MATCHED THEN
            UPDATE SET @dummy = 0
        OUTPUT
            INSERTED.[Id] ,
            [_source].[PhoneNumber]
        INTO #Contact_PhoneNumber_Output ( [Id], [PhoneNumber] );

Given the information in Use Caution with SQL Server's MERGE Statement, I'm going to ditch the MERGE completely. There's loads of LCK_M_RS_U contention too so I think I'll be better off with simpler SQL which I understand better.


I had a similar issue, not sure it will sort yours.

Try this:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails. You can see the table here:

select * from master.sys.messages
where text like '%deadlock%'

You can have more details here


I have a slightly different approach:

I tend to caution against sp_msforeachdb because it is undocumented, unsupported, and has a known bug where it can silently skip databases, and Microsoft has no interest in fixing it (see here, here, here, here, and here).
I like to build a dynamic SQL statement that can be inspected before execution, or printed out to be massaged first, instead of just throwing it into a black box. I also like to make sure the statement only attempts to run against databases that are online and that the user can access.

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @sql = N'', @src = N' UNION ALL
SELECT ''$d'',
    s.name COLLATE SQL_Latin1_General_CP1_CI_AI,
    t.name COLLATE SQL_Latin1_General_CP1_CI_AI
  FROM [$d].sys.schemas AS s
  INNER JOIN [$d].sys.tables AS t
  ON s.[schema_id] = t.[schema_id]';

SELECT @sql = @sql + REPLACE(@src, '$d', name)
  FROM sys.databases
  WHERE database_id > 4
    AND [state] = 0
    AND HAS_DBACCESS(name) = 1;

SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));

PRINT @sql;
--EXEC sys.sp_executesql @sql;

Note: this will bomb on databases with a single quote/apostrophe (') in the name. Which seems fair, because who does that?

If you need to put the results into a temp table for later use:

CREATE TABLE #t(d SYSNAME, s SYSNAME, t SYSNAME);
INSERT #t EXEC sys.sp_executesql @sql;


First, your estimates are bad.  I would expect estimates to be harder to calculate on multi-column joins because, as in here, the values may be co-existing in ways they may not.  Additionally the rows on joining may be wider and so you might have work_mem issues too.

Another important area to check is caching.  Try:

explain (analyse true, buffers true) [query]

The two issues I could see would have to do with planning costs and multiple columns, particularly the question of coexistence of various values across the join, and the memory usage.  Multi-column joins complicate both these areas.


Eliminate duplicates in different order from all combinations, can I order returned columns alphatically?

I'm not 100% sure what you're trying to achieve, but would something like this solve if for you?
(If so, I'm sure there's a more effective method as well).

SELECT
BV.Title,
BV2.Title
FROM dbo.BaseValues AS BV
CROSS APPLY dbo.BaseValues AS BV2
WHERE bv.Title <> BV2.Title
AND BV.ID <= BV2.ID

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV2.Title <> BV3.Title
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID

    SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3,
    BV4.Title AS T4
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  CROSS JOIN BaseValues BV4
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV1.Title <> BV4.Title
    AND BV2.Title <> BV3.Title
    AND BV2.Title <> BV4.Title
    AND BV3.Title <> BV4.Title
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID
    AND BV3.ID <= BV4.ID


Join results and sum of likes in a different table

You can use a subquery to sum all likes of each article.

SELECT author,
       name,
       text,
       id,
       (SELECT SUM(value)
        FROM Articles-Likes
        WHERE articles_id = Articles.ID) as Likes
from   Articles