View with a concatenated text field with line breaks

I found this example here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138165

CREATE TABLE #MessageLog
    (
      MessageText VARCHAR(MAX),
      MessageDateTime VARCHAR(MAX),
      MessageAuthor VARCHAR(MAX),
      IncidentNumber INT
    )

CREATE TABLE #Incident
(
id INT,
[description] VARCHAR(30),
IncidentNumber INT
)

INSERT INTO #MessageLog (MessageText, MessageDateTime, MessageAuthor, IncidentNumber  )
SELECT 'masg1 'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 4 IncidentNumber
UNION ALL
SELECT 'msg 2'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 4 IncidentNumber
UNION ALL
SELECT 'msg3'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 3 IncidentNumber

INSERT INTO #Incident
SELECT 1, 'desc', 4
UNION ALL
SELECT 1, 'desc', 4
UNION ALL
SELECT 1, 'desc', 3

-- the solution
SELECT        STUFF(( SELECT  '------------' + CHAR(13) + CHAR(10)
                        + MessageLog.MessageText + CHAR(13) + CHAR(10)
                        + '- on ' + MessageLog.MessageDateTime + ' by '
                        + MessageLog.MessageAuthor + CHAR(13) + CHAR(10)
                FROM    #MessageLog MessageLog
                WHERE   Incident.IncidentNumber = MessageLog.IncidentNumber
                ORDER BY MessageLog.MessageDateTime DESC
              FOR XML PATH (''),TYPE ).value('.[1]','varchar(max)')
              , 1, 2, '') AS allMessages
FROM    #Incident Incident


Set names to attributes when creating JSON with row_to_json

select
   c.id,
   (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last,
   c.age
from
   customers as c

will do what you want without any performance impact (and is not too verbose):

  id  |   first_last                                |   age
------+---------------------------------------------+---------
  1   | {"fisrt_name": "John", "last_name": "Smit"} |   34


Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?

IDENTITY_INSERT is only used for columns with the IDENTITY property.

UNIQUEIDENTIFIER is merely a data type.

Your insert statement will work.


Dropping a table/column automatically drops views using it - how to warn instead?

For i-series, at least for 7.1, you can try the RESTRICT clause of DROP COLUMN.

There is also a RESTRICT clause for DROP TABLE.

Per documentation

  RESTRICT

  Specifies that the column cannot be dropped if any views, indexes, triggers, or constraints are dependent on the column. 1

  If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.


Personal and Global messaging database design

You could combine recipient and message_status.  That would halve the number of inserts.


Normalize End Date - Nulls or separate table

When the supplier 'comes back' then do you want to set the EndDate to null again or do you want to keep track of the periods in which the supplier supplied to you? In the last case you must put the StartDate and EndDate in a separate table. If you put it in a separate table then you always must do a join to see if it is an active supplier unless you put an active Y/N switch in the supplier row.


How to get query output in addition to EXPLAIN ANALYZE output?

Client can be configured to receive the auto_explain message by setting client_min_messages=log.

Example configuration:

-- Load the extension.
LOAD 'auto_explain';
-- or (if you are using AWS RDS):
LOAD '$libdir/plugins/auto_explain';

-- Enable logging of all queries.
SET auto_explain.log_analyze=true;
SET auto_explain.log_format=json;
SET auto_explain.log_min_duration=0;
SET auto_explain.log_timing=true;

-- Enables Slonik to capture auto_explain logs.
SET client_min_messages=log;

with this configuration, client will receive auto_explain log message after every query.

node.js client Slonik uses this configuration to associate auto_explain messages with the specific queries and parse the log messages.


Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

In the ETL process, you can replace the codes with names while loading a target table. I'll focus on Informatica PowerCenter but I'm sure other ETL tools offer a similar feature.

There is a Lookup transformation that is used to look up values (DNAME) from a a relational table (it may also be a view or a flat file), based on a defined lookup match criteria (source.DEPTNO = lookup.DEPTNO). These values may then be appended to the source rows and stored in a target table (that is used for reporting).

When a session is executed, a SELECT statement is generated for each lookup in the mapping. These statements are run against the data source once and the results are stored in the lookup cache. Later, when a value needs to be looked up, the transformation uses the cache.


Backup to Azure Storage Container: Shared Access Signature the only way?

RESTORE FILELISTONLY FROM URL = '' WITH CREDENTIAL = '' WORKS in SQL Server 2016
IF your blob resides in a container in Storage Account (CLASSIC)...
Then Ola Hallengren's scripts work, and the world makes sense... even encryption works.


Should I put multiple related items in one field in one record or store them separately?

So the question LowlyDBA is likely to provide you some insight on how to implement a many-to-many relationship.  But I think there's a little more to your specific situation that should be expanded upon.
First how to model this.  Whenever you see " has zero/one or more ", this indicates a one-to-zero-to-one relationship.  The  doesn't necessarily need to be another entity as in the other question.
So you would have one table with your terms:
CREATE TABLE Term
(
  Term  VARCHAR(50)  NOT NULL  /* 50 is probably reasonable, but whatever works best for you */
 ,CONSTRAINT PK_Term PRIMARY KEY (Term)
 ,CONSTRAINT CK_Term_Length_IsValid CHECK (LENGTH(Term) >= 1) /* Or some other reasonable length */
 /* You can/should define additional constraints to keep invalid characters out of this column */
)
;

Next we need a table to store the definitions for each Term:
CREATE TABLE TermDefinition
(
  Term        VARCHAR(50)   NOT NULL
 ,Definition  VARCHAR(250)  NOT NULL /* Or some other reasonable maximum length */
 ,CONSTRAINT FK_TermDefinition_Definition_Of_Term FOREIGN KEY (Term) REFERENCES Term (Term)
 ,CONSTRAINT PK_TermDefinition PRIMARY KEY (Term, Definition)
 ,CONSTRAINT CK_TermDefinition_Definition_Length_IsValid CHECK (LENGTH(Definition) >= 1)
)
;

So you might ask why not replace the primary key with an integer row pointer like TermId and save some space?

In order for TermId to be a valid key, it has to replace an existing key which becomes an alternate key UNIQUE (Term).  This creates another copy of your data via an index.  Depending on the average number of definitions per term you might not really save a significant amount of space to begin with.
Any search for a definition now has to hit the index for Term (again, a copy of Term) to select the TermId before querying TermDefinition, versus just querying TermDefinition directly.  So extra code,  more index tuning/maintenance.
We should avoid creating meaningless objects when possible.

You might also ask why you would need a Term table in the first place, if all lookups would be performed against TermDefinition?

We may need a unqiue/distinct list of Terms in the future for other reasons, such as relating Terms to each other.
You may want to make it hard for people to add new Terms but not new TermDefinitions.

*Some may take umbrage with a VARCHAR primary key column, but the data has to stored somehow, the data has to be read somehow, and if that's a list of variable length characters, it is what it is.  We either create a primary key with varying characters forming the B-Tree, or an alternate key with varying characters forming another B-Tree.


How to partition an existing non-partitioned table

You do not specify if your table has a clustered index or not, so let's walk through all options.

I am going to use this example partition function, partition scheme and table:

CREATE PARTITION FUNCTION pf1(INT) AS RANGE LEFT FOR VALUES(10,20,30,40);
GO
CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([PRIMARY])
GO
CREATE TABLE dbo.pt(pc INT NOT NULL, id INT NOT NULL) ON [PRIMARY];
GO

1. Your table has a clustered index that was not created by a constraint.

This is the easiest case. You can just use the CREATE INDEX statement with the DROP_EXISTING clause to move the table to the partition scheme.

Assume for the example that this clustered index had been created:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(Id) ON [PRIMARY];

To partition this table, the clustered index has include the partition column (pt in our case) as part of the key. This statement changes the clustered index to include the partition column and partitions it at the same time:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(pc, Id) WITH(DROP_EXISTING = ON)ON ps1(pc) ;

The DROP_Existing clause automatically removes the existing index before creating the new one. This is preferred over a separate DROP INDEX as it causes the nonclustered indexes to be rebuild only once.

2. Your table has a clustered index that is part of a PRIMARY KEY or UNIQUE constraint and it contains the partition column as part of the key

This one is still easy and very similar to the previous one.

Assume this PRIMARY KEY constraint had been created on the table:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY CLUSTERED (pc, Id) ON [PRIMARY];

Now you can just run the same re-creation script that we used in 1:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(pc, Id) WITH(DROP_EXISTING = ON)ON ps1(pc) ;

3. The table has a clustered index that does not include the partition column but was created as part of a PRIMARY KEY or UNIQUE constraint

Tough luck. You cannot change the definition of a PRIMARY KEY or UNIQUE constraint after the fact. Your only option is to drop the constraint and then either recreate it including the partition column or create a clustered index independent of the constraint that includes the partition column. In the second case you can re-create the constraint NONCLUSTERED without including the partition column. Because now this constraint is not aligned (meaning its supporting index is not partitioned) you have to specify where to place it on disk.

Assume the table had a primary key like this:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY CLUSTERED (Id) ON [PRIMARY];

To partition this table you have to drop the constraint first:

ALTER TABLE dbo.pt DROP CONSTRAINT ptc;

Then you need to create the partitioned clustered index:

CREATE UNIQUE CLUSTERED INDEX ptci ON dbo.pt(pc, Id) ON ps1(pc) ;

If you choose to re-create the PRIMARY KEY constraint non-aligned you can do so like this:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

4. Your table does not have a clustered index

In this case it is recommended in most cases to just create a clustered index to establish the partitioning. You can use the previously seen create index statement for that:

CREATE UNIQUE CLUSTERED INDEX ptci ON dbo.pt(pc, Id) ON ps1(pc) ;

However, if you have a good reason to not create a clustered index you can get away with the following two-step approach. Sadly there is no direct way to make this change.

Assume your table does not have a clustered index. To partition the table you need to first create a CLUSTERED UNIQUE constraint. (You could also use a CLUSTERED PRIMARY KEY constraint). If you have a column combination that is unique that is a simple step:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc UNIQUE CLUSTERED(pc,id);

After the constraint is created you can drop it again and "move" the table to the new partition scheme at the same time:

ALTER TABLE dbo.pt DROP CONSTRAINT ptc WITH(MOVE TO ps1(pc));

If you do not have a column combination that is unique, you are out of luck. In this case your only option is to add a new column and fill it with unique values. If the table is reasonably small you could do something like this:

ALTER TABLE dbo.pt ADD tmp_id INT IDENTITY(1,1);

However, that will take an exclusive table lock until all rows are valued. Depending on the table size this can be for quite a while. After that column is created follow the above two steps to first create the UNIQUE constraint and then drop it again right away. Afterwards you can also drop the column again. All these steps are fairly intrusive, so you are probably better of to just create a clustered index on the table. That does not even have to be unique.

If you have Enterprise Edition, you can use the WITH(ONLINE=ON) clause on most of the statements above. That will keep your table available to other connections. However, there will be a performance impact during that time.


How to use pg_restore in database with different postgis installation (postgis schema location)?

You cannot do that. Install PostGIS in the same schema in both databases. That shouldn't be a problem in the new database.

The alternative would be to manually edit the dump file, which is tedious and error prone.


azure sql - create index, does DB have to be offline?

You should absolutely be able to access the table while the index builds. Now, depending on your service tier in Azure, you might be using a lot of your resources to build this index, and that might impact user access, but otherwise, no. The index will build, but access to the table will continue.


Using PostgreSQL's Window Functions, specifically LAG and LEAD, should be able to show you the previous and next entries in your table.

select *
from (
    select  id, thread_id, is_notice, title, content,
            lag(id) over (order by is_notice desc, thread_id desc, id asc) as prev,
            lead(id) over (order by is_notice desc, thread_id desc, id asc) as next
    from post
    ) x
where 3 IN (id, prev, next);

A demo can be found here:
http://sqlfiddle.com/#!15/9fd7a/8


How to select status variables into temp variables in mysql

You can use information_schema's table for that purpose:

mysql> select VARIABLE_VALUE into @qcache_hits FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE  VARIABLE_NAME="qcache_hits";
Query OK, 1 row affected (0.00 sec)

mysql> select @qcache_hits;
+--------------+
| @qcache_hits |
+--------------+
| 0            |
+--------------+
1 row in set (0.00 sec)


InnoDB Error - InnoDB: Error: trying to remove tablespace 264 from the cache but InnoDB: it is not there

That seems to be a AWS problem bug.
Please, see https://forums.aws.amazon.com/thread.jspa?threadID=226520


Change sa password for SQL Server on a GNU/Linux machine

What OS are you using?

To get a fresh installation you have to remove the data directory, after removing the mssql-server package:

  sudo yum remove mssql-server
  OR
  sudo apt-get remove mssql-server

And then:

  sudo rm -rf /var/opt/mssql

This will delete all your user databases, but will also remove the system databases, which will allow you to reinstall and, hence, re-specify the SA password upon reinstall.

Let me know if this works.


Migrate schema updates onto existing schema

I suggest that you use a tool such as Liquibase or Flyway which is designed to manage such schema changes in a database and keep the configurations consistent and known.

http://www.liquibase.org/

https://flywaydb.org/

There are other tools available and they usually have plugins to maven, Puppet & Chef etc...it'll come down to a google search and personal preference.


Oracle Enterprise Manager 11g Database Control - Agent Unreachable

Do you have separate Oracle Homes for your Database and Grid Infrastructure (which was new to 11gR2, IIRC)?
If so, then the Agent probably runs from the Grid Infrastructure's Oracle Home.
Annoyingly, the bin directory in both Oracle Homes contains a program called emctl but only one of them will work as you expect!
If in doubt use the Linux find command to locate anything called emctl ...
find ???  -type f  -name emctl  -ls

I'd suggest, once you've found it, you add the Agent's "Oracle Home" into /etc/oratab under its own name, to make it easier to find it next time.


This should be handled by adding a reference to the master database.

Previously, we've had issues related to the relative path that's used to reference the master.dacpac (as described here). If I remember correctly, we pulled a copy of the master.dacpac file into the local project and referenced it using the last option shown in the dialog above. This is not ideal however as you'll need to remember to update your copy of the reference periodically.

Will update if I can find the details of what was causing a problem with the referencing method.


Using activity over last few days to label user as "active"

That may not be the most elegant solution, but it works:
with activity_cte (day, user_id, act1, act2) as (
    values
        ('2020-01-01'::date, 1, 0, 1),
        ('2020-01-01'::date, 3, 1, 0),
        ('2020-01-02'::date, 1, 3, 2),
        ('2020-01-02'::date, 2, 0, 2),
        ('2020-01-02'::date, 5, 0, 1),
        ('2020-01-03'::date, 1, 1, 2),
        ('2020-01-03'::date, 5, 1, 1),
        ('2020-01-04'::date, 2, 1, 1),
        ('2020-01-04'::date, 5, 4, 0)
),
day_users AS (
    SELECT DISTINCT
           day,
           array_agg(user_id)
               OVER (ORDER BY day
                     RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW) AS users
    FROM activity_cte
)
SELECT day_users.day,
       count(DISTINCT user_id)
FROM day_users
    CROSS JOIN LATERAL unnest(users) AS u(user_id)
GROUP BY day_users.day;

    day     | count
------------+-------
 2020-01-01 |     2
 2020-01-02 |     4
 2020-01-03 |     3
 2020-01-04 |     3
(4 rows)


Can't install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2

I found the answer here:
Warning Setup Limited to Reporting Services for SharePoint by Alberto Morillo
Which, in short, says:

The only SQL Server 2016 features allowed to install on Windows Server 2008 R2 are Reporting Services (SharePoint) and Reporting Services Add-in for SharePoint products as mentioned on the "Hardware and Software Requirements for Installing SQL Server 2016" article created for SQL Server 2016 Preview (CTP versions).
You won't be able to install the Database Engine or any other feature because they are not supported on Windows Server 2008 R2.


Collation conflict ... Could not use dbo.sysdac_instances

The sysdac_instances view is defined as follows:

CREATE VIEW [dbo].[sysdac_instances]
AS
    SELECT
        -- this must be locked down because we use instance_id visability as a security gate
        case
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id
            when sd.owner_sid = SUSER_SID() then dac_instances.instance_id
            else NULL
        end as instance_id,
        dac_instances.instance_name,
        dac_instances.type_name,
        dac_instances.type_version,
        dac_instances.description,
        case
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.type_stream
            when sd.owner_sid = SUSER_SID() then dac_instances.type_stream
            else NULL
        end as type_stream,
        dac_instances.date_created,
        dac_instances.created_by,
        dac_instances.instance_name as database_name
    FROM sysdac_instances_internal dac_instances
    LEFT JOIN sys.databases sd
        ON dac_instances.instance_name = sd.name

The view performs a join on instance_name against the sys.databases.name column.  Since the column definition for the sysdac_instances_internal.instance_name does not specify a collation, it will assume the collation of the msdb database.

Perhaps you restored msdb from an instance where the collation was Latin1_General_BIN, which would cause the collation conflict.  If that is the case, you'd need to recreate the MSDB database, since its collation cannot be altered.


Most probably because you are using the generate scripts method to do so.

I would suggest to use open source DBA tools to view the permission or export.

Aliases : Export-SqlUser

Synopsis

Exports users creation and its permissions to a T-SQL file or host.

Description

Exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions.

Example:

  PS C:> Export-DbaUser -SqlInstance sqlserver2014a -User User1, User2
  -Path C:\temp\users.sql

Exports ONLY users User1 and User2 from sqlserver2014a to the file C:\temp\users.sql


normalize this! allow user-defined ordering but fall back to default order

Can this schema be improved/normalized? Yes, it would be better if all the ordering data was located in the same place.

Should we just use vendor_order table for the company order too? That's a good idea. That would place all ordering information in the same table where it is much easier to work with and maintain. Of course, you would have to create an office entry that would stand for this default "company-wide" office. But that shouldn't be a problem.

You would have to rewrite the query, of course.

As order data would no longer be in the Vendor table, you would also have to join it to the Vendor_order table on that "company-wide" office then left join again on the actual office number.

I've provided a simple sqlfiddle example. Comment out the WHERE clause to see that all vendors are listed for all offices in the correct order.

Should we make a separate table for office vendors? Not necessary. There should be a table for offices, a table for vendors, and a table which defines the default ordering and any special ordering for specific offices. See the example.


In order to get the count across multiple columns, I'd suggest looking at unpivoting the data.  Since you are using SQL Server 2008+, you can use CROSS APPLY to convert your multiple columns into multiple rows. Once the data is in the rows, you can easily count the occurrences.

select
  sum(case when value = 1 then 1 else 0 end) Count1,
  sum(case when value = 2 then 1 else 0 end) Count2,
  sum(case when value = 3 then 1 else 0 end) Count3,
  sum(case when value = 4 then 1 else 0 end) Count4,
  sum(case when value = 5 then 1 else 0 end) Count5
from yourtable
cross apply
(
  values
    (1, Ques1),
    (2, Ques2),
    (3, Ques3),
    (4, Ques4),
    (5, Ques5)
) c (col, value);

See SQL Fiddle with Demo.

Note, this could also be written using the UNPIVOT function:

select
  sum(case when value = 1 then 1 else 0 end) Count1,
  sum(case when value = 2 then 1 else 0 end) Count2,
  sum(case when value = 3 then 1 else 0 end) Count3,
  sum(case when value = 4 then 1 else 0 end) Count4,
  sum(case when value = 5 then 1 else 0 end) Count5
from yourtable
unpivot
(
  value
  for col in (Ques1, Ques2, Ques3, Ques4, Ques5)
) un;

See SQL Fiddle with Demo

If you don't want to UNPIVOT the data, you could also CROSS JOIN to a virtual table similar to:

select
  sum(case when DATA = 1 then 1 else 0 end) Count1,
  sum(case when DATA = 2 then 1 else 0 end) Count2,
  sum(case when DATA = 3 then 1 else 0 end) Count3,
  sum(case when DATA = 4 then 1 else 0 end) Count4,
  sum(case when DATA = 5 then 1 else 0 end) Count5
from
(
  select
    CASE s.col
      WHEN 'Ques1' THEN Ques1
      WHEN 'Ques2' THEN Ques2
      WHEN 'Ques3' THEN Ques3
      WHEN 'Ques4' THEN Ques4
      WHEN 'Ques5' THEN Ques5
    END AS DATA
  from yourtable
  cross join
  (
    select 'Ques1' AS col UNION ALL
    select 'Ques2' UNION ALL
    select 'Ques3' UNION ALL
    select 'Ques4' UNION ALL
    select 'Ques5'
  ) s
) s;

See SQL Fiddle with Demo

All versions give the result:

| COUNT1 | COUNT2 | COUNT3 | COUNT4 | COUNT5 |
|--------|--------|--------|--------|--------|
|     11 |      6 |      5 |      3 |      0 |


Sql Server Installation can't find files

I think you are trying to start instance from a failed installation.
Un installing and reinstalling SQL Server may clear this error.

Please look into below link which deals with similar issue.

https://connect.microsoft.com/SQLServer/feedback/details/684859/during-install-set-the-default-location-of-databases-problem


Same query but different plan on different server

Solved the problem by altering the index on parent_id

CREATE NONCLUSTERED INDEX [IX_Parent_Id] ON [Items] ([Parent_Id]) INCLUDE (Name) WITH DROP_EXISTING


Registering a SSDT database project as a data tier application fails

I had this error recently so I'll add my solution for anyone else who comes across this,

Turns out in my publish.xml I had RegisterDataTierApplication set to True. The first time I published the database it worked fine, but then I got the same error, as the database was already registered as a Data Tier application.

By setting to false (or unchecking the checkbox in the gui) it works fine.

 <?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>foo</TargetDatabaseName>
    <DeployScriptFileName>foo.sql</DeployScriptFileName>
    <BlockWhenDriftDetected>False</BlockWhenDriftDetected>
    <RegisterDataTierApplication>False</RegisterDataTierApplication>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <TargetConnectionString>Data Source=foo;Integrated Security=True;Pooling=False</TargetConnectionString>
  </PropertyGroup>
</Project>

As a note about extracting the dacpac - you will always get an error if extracting via ssms,

If you use the SQL Server Object Explorer in VisualStudio, you can set more options, including unchecking the 'Verify Extraction' option, which will then allow you to create the dacpac.


A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757

Ridicule and disdain aside, the following query would return all possible permutations of your integers.

SELECT n1.i, n2.i, n3.i, n4.i, n5.i
FROM numbers AS n1
INNER JOIN numbers AS n2 ON n1.i<n2.i
INNER JOIN numbers AS n3 ON n2.i<n3.i
INNER JOIN numbers AS n4 ON n3.i<n4.i
INNER JOIN numbers AS n5 ON n4.i<n5.i

.. given that the table or common table expression numbers contains 39 unique records with 1<=i<=39.

You could add different filters for your purposes, like how many odd numbers:

WHERE (CASE WHEN n1.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n2.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n3.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n4.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n5.i%2=1 THEN 1 ELSE 0 END)>howmanyoddnums

.. or even numbers (WHEN nx.i%2=0 THEN ...). And because the numbers are "ordered" with the < in the join, n1.i will always be the lowest and n5.i the highest.

Any more advanced features (or tuning for that matter) will come down to what platform and dialect of SQL you're using.


Updating linked server with temp table join

current query

Update linked
   set linked.col1 = local.col1
  From LinkedServer.LinkedDatabase.dbo.remoteTable linked
  JOIN #localTempTable local
         on local.column = linked.primaryKeyColumn

It is typically better to perform the write operation locally
Here you are both reading from and writing to the linked server

My recommendation is if you are going to create #localTempTable then do so on LinkedServer.  Have a session on the LinkedServer and link to local to populate #localTempTable but #localTempTable is now on LinkedServer.  Flop the link.

insert into #linkedLocalTempTable
select ...
from Local.LinkedDatabase...

Please post the query to populate #localTempTable
If you use that table for other stuff then flopping stuff around may not be and option


at the moment mysqldump (22 minutes exec time, 5 GB Dump), when to use xtrabackup?

Can't you test this on a test machine? I don't mean to be rude, but only you can really tell if XtraBackup is better  for you. What I do know is that  Percona is a quality outfit and that their offerings are always worth a look.

I'm guessing about your scenario, but any backup will depend more on your disk config than on a particular programme (RAID config &c.). There are four things to be considered in any IT setup - hardware, networking, OS and software (and maybe 5 - people :-) ).

Have you also looked at MyLVMBackup? Zmanda? If you're considering XtraBackup, you must be on Linux - these also work on that platform.


Change MySQL default installation directory in OS X

1- Open mysqld_safe in NewMysqlDir/bin with a text editor & replase all /usr/local/mysql to NewMysqlDir & save.

2- Open mysqld_safe in NewMysqlDir/bin with Terminal


What happens exactly when a chunk gets migrated?

Yes and no! First config server copy all documents of moving chunk to destination server, then reading and writing is paused during time when chunk document is updated (one document, one value in that document), update is very fast process, so you don't actually notice anything. After that start cleanup at server where chunk was before.

So, you are very lucky if you managed to run collection.find() command just that moment when config server chunk collection is updated. And if you managed to do that, you probably cannot notice it or even measure that "slowness".


How do I move SQL Server database files?

You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline.  This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.

The process to move them is fairly simple.  Detach/Attach was already described, but it is not nearly this complex.

Change the file locations with an ALTER DATABASE command:

USE master; --do this all from the master
ALTER DATABASE foo
MODIFY FILE (name='DB_Data1'
             ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

Note, you do not need to declare the old location in this command.  Changing this path does not take effect immediately, but will be used the next time the database starts up.

Set the database offline

(I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

Move/Copy the files to the new location

Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)

Bring the database online

ALTER DATABASE foo SET ONLINE;

You can see this described in more detail here.


Ok, finally I got the mid-way to solve this problem.

I created views for contact number, sales order and purchase order along with the crm_entry and then I used these views in my stored procedure with inner joins.
Here the query is :

SELECT
  view_crm_entry_phone.*,
  view_crm_entry_sale.sales,
  view_crm_entry_purchase.purchase
FROM
  view_crm_entry_phone
INNER JOIN
  view_crm_entry_sale ON view_crm_entry_sale.id = view_crm_entry_phone.id
INNER JOIN
  view_crm_entry_purchase ON view_crm_entry_purchase.id = view_crm_entry_phone.id


How can I map a login to a database using T-SQL (not SSMS)

USE msdb;
GO
CREATE USER shims FROM LOGIN shims;
GO
ALTER ROLE SqlAgentUserRole ADD MEMBER shims;
GO

Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


Can power failure rollback mysql innodb database of many days?

I'm quite sure that the rollback was made by automatic Windows 7 System Restore after the power failure. This restored the mysql data folder. I cannot understand how a OS restore can move files from other applications even becuse I was using a custom path for mysql-data: c:\mysqldata and not the standard c:\programData\Mysql\....
My solution is to disabled the windows restore, but I think can be a better solution.

EDIT

The problem is the IBD data files of Mysql/MariaDB, these are a "monitored extension" and Windows rollbacks them anywhere in the volume.

EDIT2

Seems that switching innodb_file_per_table option to OFF solved the problem because new tables (only new ones) will not use IBD files.


ORA-12154: TNS: Could not resolve the connect identifier specified

In line 21:

SET DEFINE OFF

But you never re-enable it. When you run the script the 2nd time, substitution variables are not replaced with values provided by the user, because DEFINE is still OFF. Just add the below to the start of your script, so variables will be used in consecutive runs:

SET DEFINE ON


MariaDB threads goes around 10K suddenly

Many things may be involved.

First, which of these are you talking about?  (And what values do you have?)

"Threads_cached": "5",      -- 1000 might be good for you
"Threads_connected": "10",
"Threads_created": "36404", -- divide by Uptime
"Threads_running": "4",     -- 20 is a 'large' value

"Max_used_connections": "238",
"max_connections": "400",   -- we may need to tune this to avoid the problem

How do you "load balance" the Slaves?  Assuming that the connections are normally quick, "round robin" is better than any sophisticated method.

You should consider adding another Slave -- Keep in mind that if you lose a Slave (crash, maintenance, hiccup, etc), you will be back down to 3, which seems has the problem.  Having 4 up-to-date slaves may make the problem much rarer.

The reason for throttling max_connections is to throw the problem back to the client, forcing it to pick another Slave.  (I assume you recover from connection error and try again?)

Please describe how the data comes in.  It sounds like sensor data that is pushed from remote sensors?  Is there anything that would cause it to "sync" -- that is everyone sends at the top of the minute?  If so, is there a way to stagger the pushes?

Do the remote sensors do the following?

connect to mysql
insert one row (please provide details and SHOW CREATE TABLE)
disconnect

?  If not, please describe it.

Is there any "connection pooling"?  You mentioned "maxscale"; are you using that?

What is the average number of connections over an hour (or day or whatever)?  As a first cut, I would set max_connections to twice that.

Please provide (1) RAM size, (2) SHOW VARIABLES, (3) SHOW GLOBAL STATUS after mysql has been running for a day.  This may give more insight into the problems.


Poor man's change:

mysql> CREATE TABLE `foo` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `content` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> INSERT INTO foo VALUES (NULL, 'bar'), (NULL, 'another_bar');

mysql> SELECT * FROM foo;
+----+-------------+
| id | content     |
+----+-------------+
|  1 | bar         |
|  2 | another_bar |
+----+-------------+
2 rows in set

mysql> SELECT content INTO @newContent FROM foo WHERE id=2;

mysql> UPDATE foo SET content=@newContent WHERE id=1;

mysql> SELECT * FROM foo;
+----+-------------+
| id | content     |
+----+-------------+
|  1 | another_bar |
|  2 | another_bar |
+----+-------------+
2 rows in set

And now I've done it without using session variables:

 UPDATE foo, (SELECT content AS selectContent FROM foo WHERE id=2)selectFoo
  SET content=selectContent
  WHERE id=1;


Adding slave to existing master-master config throws foreign key constraint failed error

Given the Following

+<------+
|       ^
V       |
M1      M2
|       ^
V       |
+-------+

and You Want the Following

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

Given Master IP is 10.1.2.30

Logon to the Slave and run the mysqldump like this

MAS_IP=10.1.2.30
SLV_IP=localhost
MYSQL_MAS_CONN="${MAS_IP} -uroot -ppassword"
MYSQL_SLV_CONN="${SLV_IP} -uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=1 --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database --all-databases"
mysql ${MYSQL_MAS_CONN} -e"FLUSH LOGS;"
echo "STOP SLAVE;" > MySQLData.sql
mysqldump ${MYSQL_MAS_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysql ${MYSQL_MAS_CONN} < MySQLData.sql

If you have already performed this, let me address your comments.

  But upon adding the third DB server and starting slave on it, it throws Cannot add or update a child row. foreign key constraint fails error.

Why would foreign key problems happen if you copied the data from a Master to a Slave? The auto_increment ids on the Slave simply did not match that of the Master. This is true even if you the Slave's Master is where you got the data from. Why?

  auto_increment_increment is set to 2 in both masters. auto_increment_offset is set to 1 in Master 1 and 2 in Master 2. We have mostly innoDB tables and some MyISAM tables.

You have to make sure the Slave has the exact same auto_increment_increment and auto_increment_offset as the Master you are replicating from.

Goto the Master and run

SHOW VARIABLES LIKE 'auto_increment%';

Put those values into the Slave's my.cnf and restart mysql.

REMEMBER : A Slave's auto_increment behavior needs to be identical to that of its master.

Give it a Try !!!

UPDATE 2013-04-06 17:26

Here is the problem: Let's say you have the setup

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

If you did the following:

mysqldump data from M1
load dump it S1
enabled Replication from M1 to S1
M1 and S1 have identical auto_increment_offset
M1 and S1 have identical auto_increment_increment

there should never be foreign key violations.

What you may need to do is cleanup every server's view of the auto increment values

Here is something to try

Set these values on M1's /etc/my.cnf

auto_increment_increment = 10
auto_increment_offset = 1

Set these values on M2's /etc/my.cnf

auto_increment_increment = 10
auto_increment_offset = 1

Set these values on S1's /etc/my.cnf

auto_increment_increment = 10
auto_increment_offset = 1

Restart mysql on all three servers

Load Data like this:

mysqldump from M1 to
Run SET GLOBAL sql_log_bin = 0; on M1
Load the mysqldump into M1
Run SET GLOBAL sql_log_bin = 1; on M1
Run SET GLOBAL sql_log_bin = 0; on M2
Load the mysqldump into M2
Run SET GLOBAL sql_log_bin = 1; on M2
Load the mysqldump into S1
Reset Replication amongst the threee server

If nothing breaks, you can then do this

Set these values on M2's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 2

Restart mysql on M2

Everything should be clean now. From here, INSERTs should be properly handled.

Give it a Try !!1


Are CNAME aliases for connection strings a good idea?

One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections.  This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application.

If you are using a separate alias for each application, you would need to generate and install a new certificate each time you add a new application to the server.  The same might apply if you want to make sure there are no unused/invalid SANs in the certificate, in that you would need a new certificate each time you removed an application.


How do I count and order by unique entries?

Try this

SELECT
    tblClient.[Printed Name],
    tblClient.[Address 1],
    tblClient.[Address 2],
    tblClient.City,
    tblClient.State,
    tblClient.Zip,
    tblClient.Phone,
    tblClient.[Email Address],
   (select count(*)
    from tblCurrent
    where tblCurrent.[Date Ordered]>#6/27/2013#
      and tblClient.[Client Code] = tblCurrent.Client
   ) AS Qty
FROM tblClient
ORDER BY Qty desc;


There is not really much to talk about here.  At least, the conventional wisdom is very clear.  Only option 2 should be considered unless you have a very, very good reason, borne out of practical experience with your database, to do otherwise.

Neither option 1 nor option 3 have a parent table in first normal form (1NF) because they include repeating columns, i.e. the foreign keys to the sub-tables.

In a transactional system, you should start from the assumption that your tables should be in 3NF at least.  Back away from that only for a good, well considered reason.

One such reason might be that there are lots of sub-tables and you don't know which one(s) might need to be joined to the parent for a particular parent record.  It could happen that a performance issue results, depending on many factors.  One compromise that you could use if this problem did occur, which you might consider Option 4 is to have bit flags on the parent table indicating the presence of each type of child.  This is still begging for an eventual data inconsistency problem, but it at least will be less inconsistent than option 3, because it risks only pointing you to an empty join rather than to an incorrect record.


Restore mysql database with different name

You can let mysqldump create the dump in such a way that it does not create or select  the database.

EXAMPLE : You are dumping the database db1 and loading it into database db2

This will put in the CREATE DATABASE and the USE commands in the dump

mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql

This will not put in the CREATE DATABASE and the USE commands in the dump (this is what you want)

mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql

You can load it into another database (such as db2) in one of four(4) ways:

OPTION 1

$ mysqldump -u... -p... --routines --triggers db1 | mysql -u... -p... -A -Ddb2

OPTION 2

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2 < /root/db1.sql

OPTION 3

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2
mysql> source /root/db1.sql

OPTION 4

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A
mysql> use db2
mysql> source /root/db1.sql

Give it a Try !!!


Create an "int" column forced between 0 and 3

thank you for your answers. I am using SQLite3 local on my laptop for testing purpose, I am not sure what the "real" database will be.
So for that reason and because it does not seem easy to restrict values in a certain range, I have solved my problem in the software itself.

Using the "AND" condition to check if values are in the interval I want: "UPDATE xxx SET xxx WHERE xx AND xx"

Thank you all.


Ok I found the solution by myself, thanks to Rick James for giving me an enlightenment.

The Right MySql call for this, is

SELECT * WHERE labels LIKE 'N'

Just to mention:
labels is a SET('Y','N') column and I want to check, if a row has value 'Y' or 'N'
then get the row.

The SET column doesn't work with labels='N' because by default is labels='Y','N'


What happens when I try to store more 8019 bytes in a SQL Server table row?

From Row-Overflow Data Exceeding 8 KB :

  A table can contain a maximum of 8,060 bytes per row. In SQL Server
  2008, this restriction is relaxed for tables that contain varchar,
  nvarchar, varbinary, sql_variant, or CLR user-defined type columns.
  The length of each one of these columns must still fall within the
  limit of 8,000 bytes; however, their combined widths can exceed the
  8,060-byte limit

That means it's really hard to hit the 8kb limit.  You could script it:

if object_id('YourTable') is not null
    drop table YourTable
go
create table YourTable (id int identity);
declare @i int = 1
declare @sql varchar(max)
while @i < 800
    begin
    set @sql =  'alter table YourTable add col' + cast(@i as varchar(24)) +
        ' decimal(38);'
    exec (@sql)
    set @i = @i + 1
    end

This will show you the error message:

  Msg 1701, Level 16, State 1, Line 1 Creating or altering table
  'YourTable' failed because the minimum row size would be 8076,
  including 65 bytes of internal overhead. This exceeds the maximum
  allowable table row size of 8060 bytes.


Merge two rows letting the second one override the first in PostgreSQL

It depends if you need something generic or if it can work with a "small" number of columns.

In your specific example, this query:

WITH new_data (id, name, password) AS (values ('743ccdf0-e9d8-4268-b6d7-0645eb70feb9'::uuid, NULL, 'snthsnth'))
SELECT
id,
COALESCE(new_data.name, current_data.name) AS name,
COALESCE(new_data.password, current_data.password) AS password,
current_data.version
FROM user_revisions current_data
INNER JOIN new_data USING (id)
WHERE id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9'
ORDER BY current_data.version DESC LIMIT 1;

would produce the expected:

id                                      name    password    version
743ccdf0-e9d8-4268-b6d7-0645eb70feb9    Bengan  snthsnth    2

You can test things in this SQLFiddle: http://sqlfiddle.com/#!17/c4700/5/0

I am quite confident it could be made generic if needed, but it may be enough already for your needs?

However it will not work correctly if you need to store NULL values, and if you need to handle transition such as "some data value => NULL"

In such cases there would be a specific need to realy test the existence of each field and just take its value if present, including if it is NULL

In passing I would also say like @Lennart that it is better to separate archive data from live data for many reasons, one would be performance, to compute the "next" version you will only have to read one row (the live one) and then doing something as above, instead of having to do things like order by version desc limit 1 each time, which will be more costly, even with an index on version.


How to know which instance is default in Configuration Manager if default port number is changed?

I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses property which shows IP addresses and used ports from Network Configuration settings

That is not the correct way to identify default instance in SQL Server. A default instance can be identified by going to SQL Server configuration manager (SCCM). On SSCM page click on SQL Server Services on the left pane. Now on the right you can see all the instances. Default instance is one which is like SQL Server(MSSQLSERVER. For named instances it would be like SQL Server (Instance_Name) where instance_name is name of the instance. Something like below. Source is this SO Thread.

If you cannot open SCCM go to run and type services.msc. Look for SQl Server service. Again on services pages as well the SQL Server(MSSQLSERVER) will be service of default instance.

If you want to get the information using TSQL you have Serverproperty T-SQL command to help you

select serverporperty(InstanceName)

  InstanceName: Name of the instance to which the user is connected.

  Returns NULL if the instance name is the default instance, if the
  input is not valid, or error.

  NULL = Input is not valid, an error, or not applicable.

  Base data type: nvarchar(128)

So you can see this is not absolute way, but if the TSQL syntax is correct and you get NULL means you are connected to default instance. Other disadvantage of this method is you have to run this query on each instance to get the information.

Port number hardly has anything to do with default or named instance of SQL Server, it has more to do with securing SQL Server connection. A default instance can listen on port apart from 1433. When you install SQL Server by default it will listen on port 1433, as a good practice it is advised to change it to listen on other port.


How to add a column with a foreign key constraint to a table that already exists?

You just have to add another step - in fact PostgreSQL is already telling you that: column "sender" referenced in foreign key constraint does not exist.
The FOREIGN KEY (aka parent) column has to already exist in order to make it an FK.
I did the following (from here and the documentation). Note that the parent column has to have a UNIQUE constraint (or be the PRIMARY KEY) but it doesn't have to be NOT NULL. This is because NULLs are not equal to each other, nor are they equal to anything else - each NULL is considered UNIQUE in its own right!
CREATE TABLE x(a INT UNIQUE NOT NULL);

CREATE TABLE y(b INT);

ALTER TABLE y ADD COLUMN c INT NOT NULL
CONSTRAINT y_x_fk_c REFERENCES x (a)   -- if x (a) doens't exist, this will fail!
ON UPDATE CASCADE ON DELETE CASCADE;  -- or other Referential Integrity Action

A couple of points to note (see the fiddle here) - an attempt to insert a value into y (c) which is not in x (a) fails and the constraint name is given in the error message.
The fiddle has NOT NULL constraints on x (a) and on y (c). Unless I have a really compelling reason, I always declare my columns as NOT NULL - it helps the optimiser and reduces the potential for confusion/error. You can experiment yourself with the fiddle to see what happens when you leave out the NOT NULL on either (and both) field(s) - the behaviour isn't always intuitively obvious!
ALWAYS give your foreign keys meaningful names. Being told that key "SYS_C00308108" is being violated is not very helpful. See the fiddle here for Oracle's behaviour under these circumstances the key name will vary from fiddle to fiddle, but is some arbitrary string beginning with SYS_... (comes after the long dbfiddle generated tablename).
Evan Carroll in his answer here believes that auto-generated names are OK - I've shown why that is not a good idea for Oracle (at least up to 18c), but I also feel that it's not a good idea for PostgreSQL either - potential problems for portability if nothing else.
I would like to credit Evan Carroll for pointing out that the addition of the new field and the FOREIGN KEY creation and the CONSTRAINT (with specified name) can be added in one step and not two steps as I originally said) - so please give him credit for that if you feel like upvoting me - I do go into more detail however.
Considering the statement in your question:
ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;

It would be a "nice-to-have" if the RDBMS could automatically create the field you want with the data type matching the referenced field.
All I would say is that changing DDL is (or at least should be) a rarely used operation and not something that you'd want to be doing regularly. It also risks adding to an already fairly substantial documentation.
At least PostgreSQL tries to do something reasonable - it concatenates the table name, the FOREIGN KEY field name and fkey. Furthermore, when you do name the constraint yourself,  the error message will add DETAIL: Key (c)=(7) is not present in table "x". to give something that might make sense to a human being (unlike Oracle - see the end of the PostgreSQL fiddle).


Can't create table, but table doesn't exist

InnoDB Architecture

ANALYSIS

Somehow, you lost the my_user.frm and my_user.ibd files. The data dictionary still has an entry for that table.
You cannot run DROP TABLE my_user; because mysqld looks for the my_user.frm first. Since this is no my_user.frm, the table cannot be dropped.
Although my_user.frm does not exist, you cannot run CREATE TABLE my_user ... because mysqld thinks it is OK to create the table but then defers to the storage engine. InnoDB says "I already have the tablespace_id of my_user registered".

This sequence of events can be proved if you create the table using MyISAM. mysqld will allow it. Once you switch to InnoDB, it goes right back to the data dictionary, which is faulty on that one entry.
I have two suggestions
SUGGESTION #1
Don't create the table with that name anymore. Use a different table name
CREATE TABLE my_usertable (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

This will result in you changing the table name in your application code
SUGGESTION #2

mysqldump all the data, triggers and stored procedures
backup ibdata1
delete ibdata1
reload the mysqldump
Detailed steps from my StackOverflow post

I have dealt with this problem before in my post InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)


Error in loading data using DB2 load command in DB2 10.5

Have you run runstats recently for at least the relevant tables and indexes?
Are you sure you have index(es) to support your monthly batch?
Have you checked the bufferpool hit ratio when the batch is running?
db2top and db2advis may help you answering these questions.

The secret optimizer was surely changed and it may see tiny details differently than the previous version, that can cause dramatic differences. It's always a good idea to pamper it with runstats when unexplainable slowdown is seen and check the rest when there is no benefit.


Create database level constants (enumerations) without using CLR?

You can create an enumeration type in SQL Server using a XML Schema.

For example Colors.

create xml schema collection ColorsEnum as '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Color">
        <xs:simpleType>
            <xs:restriction base="xs:string">
                <xs:enumeration value="Red"/>
                <xs:enumeration value="Green"/>
                <xs:enumeration value="Blue"/>
                <xs:enumeration value="Yellow"/>
            </xs:restriction>
        </xs:simpleType>
    </xs:element>
</xs:schema>';

That allows you to use a variable or parameter of the type xml(dbo.ColorsEnum).

declare @Colors xml(dbo.ColorsEnum);
set @Colors = '<Color>Red</Color><Color>Green</Color>'

If you try to add something that is not a color

set @Colors = '<Color>Red</Color><Color>Ferrari</Color>';

you get an error.

Msg 6926, Level 16, State 1, Line 43
XML Validation: Invalid simple type value: 'Ferrari'. Location: /*:Color[2]

Constructing the XML like that can be a bit tedious so you can for example create a helper view that also holds the allowed values.

create view dbo.ColorsConst as
select cast('<Color>Red</Color>' as varchar(100)) as Red,
       cast('<Color>Green</Color>' as varchar(100)) as Green,
       cast('<Color>Blue</Color>' as varchar(100)) as Blue,
       cast('<Color>Yellow</Color>' as varchar(100)) as Yellow;

And use it like this to create the enumration.

set @Colors = (select Red+Blue+Green from dbo.ColorsConst);

If you would like to create the view dynamically from the XML Schema you can extract the colors with this query.

select C.Name
from (select xml_schema_namespace('dbo','ColorsEnum')) as T(X)
  cross apply T.X.nodes('//*:enumeration') as E(X)
  cross apply (select E.X.value('@value', 'varchar(100)')) as C(Name);

The enumeration can of course also be used as parameters to functions and procedures.

create function dbo.ColorsToString(@Colors xml(ColorsEnum))
returns varchar(100)
as
begin
  declare @T table(Color varchar(100));

  insert into @T(Color)
  select C.X.value('.', 'varchar(100)')
  from @Colors.nodes('Color') as C(X);

  return stuff((select ','+T.Color
                from @T as T
                for xml path('')), 1, 1, '');
end

create procedure dbo.GetColors
  @Colors xml(ColorsEnum)
as
select C.X.value('.', 'varchar(100)') as Color
from @Colors.nodes('Color') as C(X);

declare @Colors xml(ColorsEnum) = '
<Color>Red</Color>
<Color>Blue</Color>
';

select dbo.ColorsToString(@Colors);

set @Colors = (select Red+Blue+Green from dbo.ColorsConst);
exec dbo.GetColors @Colors;


MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement?

cat 1.sql | grep -v "DROP TABLE" | mysql --force

Assuming no other string contains drop commands this should work..

Updated: use --force to ignore errors by CREATE TABLE.


Vacuum (even FULL) isn't the most efficient when it comes to reclaiming space from bloated indexes. And in PostgreSQL 8.x it was even more the issue.
I do remember reading about tests where dump, drop and restore might be indeed faster and gets better results than vacuum full.
And this is what I would suggest if possible.

There is another option you might try first though, quoting PostgreSQL 8.4 documentation:

  The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would. The FULL option does not shrink indexes; a periodic REINDEX is still recommended. In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.


Specified key was too long creating primary key

767 Limit in InnoDB Indexes lists 5 work arounds for the 767 problem.
But, these days, the preferred solution is to upgrade:
This problem exists before the limit was raised in 5.7.7 (MariaDB 10.2.2?).


Using count in triggers to update another table

This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.


MDX Drillthrough allows you to specify a limit for the row set returned.  As far as I am aware it doesn't have a 'show how many rows this would produce' facility.  You could fake it by having a count measure for each of your fact tables and selecting this for the slice in question before you execute the drillthrough.

I'm not sure whether Excel 2010 has much in the way of facilities to manage this out of the box.  Back when I were a lad Excel 2000-2003 didn't do drillthrough out of the box so you had to roll your own.  This is an example of a VBA script to do a drillthrough action.  You can hook the menu on the pivot table fairly easily to provide access to this sort of script.  This would at least allow you to provide some sort of validation.

Introspection into the pivot table through the API is possible if a little fiddly.  You can get the dimension and slicer state out.  Back in the SQL Server 2000 days I did a 'drill across' this way that allowed you to open another cube on the same slice (think insurance policies going across to claims).  Now SSAS supports cubes with multiple fact tables (measure groups) this is less useful.

However, if you could intercept the (IIRC) double click event that would kick off a drillthrough and knock that on the head then you could use the introspection to formulate a count query that could guard against over-large drillthroughs.  This would then pop up a dialog box along the lines of 'You're going to drill through into 1,347,961 rows.'


Was it due to the fact that there was no changes made ?

Yes, it's your case.

  When can i find out that the log sequence has been broken ?

You can do smth like this:

select database_name,
       first_lsn,
       last_lsn,
       lag(last_lsn) over (order by first_lsn) as prev_last_lsn
from msdb..backupset
where type = 'L' and database_name = 'yourDB'
order by first_lsn;

In unbroken log backup chain first_lsn = prev_last_lsn as every next lob backup starts where the previous backup stopped.

In your case the chain is unbroken.

  Having said that, even the action of taking a backup will normally
  cause an increment to the LSN

This is not true.

Log backup does not write anything to the log.

Full and differential do write to the log as they always do checkpoint at the beginning, so they write checkpoint operation to the log.

And in fact, after the Full backup in OP we see that the next log backup has changed its LastLSN.


The feature you are describing is a clustered index, which MongoDB (as at 3.2) does not support.

The most relevant feature request to upvote/watch is currently SERVER-3294.

  I am looking for something like creating chunk in NOT sharded environment.

Chunks in a MongoDB sharded cluster are metadata representing contiguous ranges of shard keys. All documents for a given chunk range will exist on the same shard, but the chunk does not currently determine the locality of data on disk.


Your join has to describe a condition where rows in actual are matched to rows in test. In your example, you're just filtering rows using variables; the join is effectively a CROSS JOIN (a cartesian product), which is why you get SUM(actual)=122 on every row.

The following query should solve your problem, with the assumption that rows in actual cannot span multiple weeks in test.

SELECT SUM(a.Actual), w.StartDate, w.EndDate
FROM actual AS a
INNER JOIN test AS w ON
    a.StartDate<=w.EndDate AND
    a.EndDate>=w.StartDate
GROUP BY w.StartDate, w.EndDate;

If rows in actual can span multiple week, you may need to calculate some type of distribution of those. Here's a simple idea:

WITH cte AS (
    SELECT a.Actual,
           1+DATEDIFF(dd, a.StartDate, a.EndDate) AS actualDays,
           (CASE WHEN a.StartDate<w.StartDate THEN w.StartDate ELSE a.StartDate END) AS weekStartDate,
           (CASE WHEN a.EndDate>w.EndDate THEN w.EndDate ELSE a.EndDate END) AS weekEndDate,
           w.StartDate, w.EndDate
    FROM actual AS a
    INNER JOIN test AS w ON
        a.StartDate<=w.EndDate AND
        a.EndDate>=w.StartDate)

SELECT SUM(a.Actual*(1+DATEDIFF(dd, weekStartDate, weekEndDate))/actualDays) AS Actual,
       StartDate, EndDate
FROM cte
GROUP BY StartDate, EndDate;

Explanation, using an example: assume Actual=1000, StartDate='2014-05-02', EndDate='2014-05-13':

The common table expression (cte) will match this with three rows in test.

a.Actual  w.StartDate  w.EndDate
1000      2014-05-01   2014-05-03
1000      2014-05-04   2014-05-10
1000      2014-05-11   2015-05-17

Note the CASE expressions: If a.StartDate is within the week, this is the weekStartDate, otherwise, use w.StartDate. Same thing for the end dates, but the other way around.

a.Actual  w.StartDate  w.EndDate   weekStartDate  weekEndDate
1000      2014-05-01   2014-05-03  2014-05-02     2014-05-03   -- start date the 2nd
1000      2014-05-04   2014-05-10  2014-05-04     2014-05-10
1000      2014-05-11   2015-05-17  2014-05-11     2014-05-13   -- end date the 13th

Now all we have to do is calculate the number of days between weekStartDate and weekEndDate for each row. Divide this number of days by the total number of days for the Actual (actualDays=12), and you get a distribution key:

a.Actual  w.StartDate  w.EndDate   (distribution key)
1000      2014-05-01   2014-05-03  2/12 =0.167
1000      2014-05-04   2014-05-10  7/12 =0.583
1000      2014-05-11   2015-05-17  3/12 =0.250

... and multiply that distribution key by the original Actual:

w.StartDate  w.EndDate   (Actual)
2014-05-01   2014-05-03  167
2014-05-04   2014-05-10  583
2014-05-11   2015-05-17  250
                       =1000


When you drop or truncate tables in SQL Server do old data pages get overwritten with new data?

When in doubt, read the documentation first:

  TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

So it deallocates the pages and make them available for reuse.


Can a replication subscription also be used as a publication?

Could we could we publish from the subscriber/cloud database to a subscribing database in one of our own data centers?

Sure.  See Republish Data.  You would need to be the admins of the instance hosting the subscription database to make this all work.


What you have is a physical dump of your database, not a logical dump.  So you can't reload it using psql.

What you would do is copy the files over to a new server (don't move them, keep a clean copy for future efforts) which has the same OS, architecture, and version of PostgreSQL, and try to start the database.  If the database were down while you did the rsync backup (and if you rsynced all the necessary files, and whatever crashed the server didn't corrupt things in the process) then the database should come back up and automatically go through its internal recovery process, get to a consistent state, and reopen.

If the database were running during the rsync, then it is very likely that starting it up will fail, or that it will report success but the data will be corrupt, perhaps in hard to detect ways.

Rebuilding it from scratch might be the best option, as then you have a known-good state.


SQLCMDMODE from SSMS. Can I set -b mode when using :r to include files?

No, you can't.

Not in the supported subset of options: http://msdn.microsoft.com/en-us/library/ms174187.aspx


Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE

We've ended up using the JOINs over CTEs, which implicitly applies the expected order, and queries looking like:
WITH
      acl AS (SELECT set_config('scope.acl', 'VALUE', TRUE) "__acl"),
      result AS (${query})
    SELECT * FROM bypass FULL OUTER JOIN result ON 1 = 1

In which case, acl CTE runs before the result and ensures that RLS could pick up a correct configuration.
Whilst it might very well be sub-optimal for a larger scale table, it worked perfectly for our scale (10k-100k rows) with no noticeable performance degradation compared to using CTEs without the resulting FULL OUTER JOIN. More importantly, our correctness tests haven't highlighted any deviations from the expected order, despite this behavior (of result running after acl in CTEs block) is implicit.


Recover registered servers in SQL Server Management Studio 2012

If your RegSrvr.xml contains a  "password=....;" in <RegisteredServers:ConnectionStringWithEncryptedPassword> section, replace ";uid=...;password=...;" with ";trusted_connection=true;"

In the same file, replace:

<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">PersistLoginNameAndPassword</RegisteredServers:CredentialPersistenceType>

with:

<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">PersistLoginName</RegisteredServers:CredentialPersistenceType>

Now the server registration will try to use Windows Authentication.  Once the Registered Servers are loaded, you can go back and edit the registrations to use your SQL Authentication information.


To join all rows that "equal" each other:

WHERE "DA-A"."DeliveryHour"
    = '0:0'::time + "RT-A"."DeliveryHour" * interval '1h'

Or

WHERE EXTRACT(hour FROM "DA-A"."DeliveryHour")::numeric
    = "RT-A"."DeliveryHour"

One or the other may be favorable to keep one side sargable when you want to use an index in a query on one or the other side.

For the case where you are only interested in  2 / 2:00:00, your explicit expressions may be the best choice, since both expressions are sargable.


Multi-row insert vs transactional single row inserts

Yes, it can make a huge difference.

For example, I did a single-statement insert with one million value lists, and it took 7 seconds.  I did one million single-row inserts all in one transaction, and it took 110 seconds.  That is a 15-fold difference.

This is for a case where the table had no constraints or indexes.  Having a single index narrowed the gap down to only be 10 times faster, by slowing both approaches down by few seconds.  Once the index is large enough that it can't be cached in RAM, then both methods will get so much slower that the difference between them may become relatively insignificant.

Note that the accepted answer to the related question you linked is not correct.  Index maintenance is done the same way whether it is a multi-valued insert or a bunch of single-valued inserts--at least for BTree indexes.  There has been talk of optimizing the index maintenance, but if the optimizations are implemented they will probably be done in such a way that it applies to both types of INSERT.


This is all IMHO:

•table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend?

Use a timestamp - better for multiple mods in a given day.

•Entry title - VARCHAR or text?

You're not going to lose much with a  text over VARCHAR, but I think that VARCHAR is the reasonable choice.

•Entry body - I assume text.

BLOB.

•5 Star rating - INT or Decimal? Why since I have read about both?

How flexible do you want to be - I'd tend to Decimal.

•Phone & Zip - INT or VARCHAR?

VARCHAR - intl codes, extensions... more flexible.

•Event Date - Again, Date or Timestamp

Again you're not losing much adding the time - never throw away information that you may use later.


Check progress of database restore running asynchronously

I don't see anything obvious about why this command won't be showing up like any other restore command, unless it is busy doing something other than RESTORE DATABASE.

I suggest identifying the session_id PowerShell is using (you should be able to see in sys.dm_exec_sessions or sys.dm_exec_connections), or you could change the script to obtain it and print it to the console. Then change your filter to:

SELECT command, status, percent_complete,
  wait_type, last_wait_type, wait_resource,
  blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = <x>;

This will help you verify:

if the command is actually a RESTORE or if Powershell is actually doing something else first
current status
percent complete, if that is available (it isn't always)
what it's waiting on (and if you run it repeatedly, if this is changing)
if your restore is actually being blocked by anything


Get-ClusterParameter Not Returning Expected Results

The command you are running is returning all of the parameters for an availability group resource.  There are not many, as most of the config is stored in SQL Server and not as cluster resource parameters.

The RegisterAllProvidersIP parameter is not a parameter of the availability group resource.  RegisterAllProvidersIP is a parameter of network name resources.  You can see all of the RegisterAllProvidersIP settings on all network name resources on the cluster with the following command:

Get-ClusterResource | where-object {$_.ResourceType.name -eq "Network Name"} |
    Get-ClusterParameter RegisterAllProvidersIP

I use the following command on my new cluster builds to disable RegisterAllProvidersIP on all cluster network name resources except for the Cluster Name resource:

Get-ClusterResource | where-object {$_.ResourceType.name -eq "Network Name"} |
    where-object {$_.name -ne "Cluster Name"} |
    set-ClusterParameter RegisterAllProvidersIP 0

Please keep in mind that this command changes that setting on all cluster name resources except for the Cluster Name resource, so if you have other applications set up on the cluster you may not want to use this.


I can share the conceptual answer to this problem, you will have to fill in correct syntax for query as per your database engine.

select record, count(record)
from table
where datetime is interval of 12 hours from now
group by record

This query can give you record occurrences in one single interval of time.

select record,
   SUM(case when datetime is in interval 12h then 1 else 0 end) 12_hour_occurrence,
   SUM(case when datetime is in interval 24h then 1 else 0 end) 24_hour_occurrence,
   SUM(case when datetime is in interval 72h then 1 else 0 end) 72_hour_occurrence
from table
where datetime > now() - 72 hours # don't pass thorugh extra records
group by record

For running on single query on multiple instances, you can use this concept to find occurrences in multiple intervals.


recover a single mysql database on a busy master-slave replicated system

If all your database use InnoDB only, I have some good news.

You should be to dump all the database in parallel from a slave.

In fact, you can force all the databases into the same point-in-time.

First thing to remember about a the Slave is that it is not not required to have binary logging enabled if it is not a Master for other Slaves.

You cannot use --master-data option for parallel dumps because each dump will have a different position written at line 22 of each dump file. It is better to record the Master's last log file and position the Slave executed using SHOW SLAVE STATUS\G. That way, all the databases have the same point-in-time position.

You can collect all databases and script the parallel dump of all the database.

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

for DB in `cat ${DBLIST}`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

mysql -h... -u... -p... -e"START SLAVE;"

If there are simply too many databases, dump them 10 or 20 at a time as follows:

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0
for DB in `cat ${DBLIST}`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
wait
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

If you need to recover a single table, you can parallel dump tables 20 at a time in size order.

Try this:

TBLIST=/tmp/ListOfTablesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY data_length" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0
for DBTB in `cat ${TBLIST}`
do
    DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
    DUMPFILE=$DB-{DB}-TBL-${TB}.sql.gz
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} ${TB} | gzip >  ${DUMPFILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
wait
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

Now that you have scripts to dump databases or individual tables, you can load that data at your discretion. If you need to get SQL executed from the binary logs on the master, you can use mysqlbinlog and give it the position ot datetime and output the SQL to other text files. You just have to perform due diligence to find the amount of data you need from whatever timestamps the bnary logs have. Just remember that every binary log's timestamp in the OS represents that last time it was written.


You can not place DDL commands directly in PL/SQL code. You can run them as dynamic SQL, for example:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    END;
    /


If the full backup just grows by a few MB but your DIFF is 200MB then you're probably doing a lot of UPDATES which change the page, thus forcing the page to get copied to disk.  However, full backup size won't change much as it copies all the pages anyways.  This is ignoring the transaction log which in the comments you've already specified has very little fluctuation.  Are you perhaps compressing your full backup but not your diff?  Filestream?

At 8K a page you're looking at 25,000+ pages changed.  Is this a high percentage of your database or table size?  Is it very UPDATE heavy?  That's most likely what's causing it based on the available info.

Can you record a extended event session or trace for UPDATES and also check to see what tables are the most updated?  If for some reason you simply are not able to review the data live perhaps you can keep recording 'last updated' on the table and see which one get's a lot.

This is assuming the cause is from DB activity and we simply can't collect the changes live to troubleshoot.  That's one way I'd deal with it, or open up the backup file and see what pages it has


@Molenpad, you shall get the WSFC details through below mention TSQL query.

select * from master.sys.dm_hadr_cluster;
Go

Here cluster_name is the Name of the WSFC cluster that hosts the instances of SQL Server that are enabled for Always On availability groups.

I am also attaching the screen shot.

Note: I have highlighted the WSFC name due to my Prod server.

for your ref Here


Differential Backup Issue - Why? Is this Possible?

You don't need to mess with RECOVERY and NORECOVERY here, all you need is the STANDBY option. Here's a quick demo on how to use it.

Create a database, set it to simple recovery, and create a table.

Insert data, take some diffs.

Fun, right?

USE master;

/*Create a dummy database*/
CREATE DATABASE DiffRestoreTest

/*We simple now*/
ALTER DATABASE DiffRestoreTest SET RECOVERY SIMPLE

/*Context is everything*/
USE DiffRestoreTest

/*If nothing changes, do we even need a diff backup?*/
CREATE TABLE dbo.t1 (Id INT)

/*Take a full backup, dummy*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_FULL.bak'
WITH INIT, FORMAT, COMPRESSION

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a differential backup*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_DIFF_1.bak'
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_DIFF_2.bak'
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_DIFF_3.bak'
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

Yeah, I lied. That's the boring part.

You can restore your Full backup in STANDBY:

/*Exit stage left*/
USE master

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak'
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

You can restore Diffs in order with STANDBY:

/*Square one*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_1.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 2*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_2.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 3*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

And unlike those pesky Log files, you can skip ahead when restoring Diffs, too:

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak'
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*What happens if I try to jump the restores?*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

If you want to test readability, just run this in between restore commands. You should see IDs increment with each. If you don't, you did something horribly wrong.

Keep in mind that when you restore files, it will kick any end users out of the database, and it won't wait for their queries to finish.

This also leaves the database in a read only state, no changes can be made here.

SELECT *
FROM DiffRestoreTest.dbo.t1 AS t

And finally, clean up after yourself.

/*Bring'er online, lad*/
RESTORE DATABASE DiffRestoreTest WITH RECOVERY

DROP DATABASE DiffRestoreTest

Hope this helps!!


SQL Developer - trying to import an excel file into a table

This link shows how to deal with importing excel file and invalid date format: how-to-import-excel-to-oracle. Hope this helps.


Wrap identifiers automatically with brackets in SSMS text editor

There is no native function for this yet in SSMS. You can view the IntelliSense properties document here.

APEX makes a free formatting tool and there is another tool called Poor Man's T-SQL. RedGate also makes a tool that is paid, all three work nicely. I am sure there are other tools out there too.


How can we sort the words inside a string in PostgreSQL?

Considering your table has some ID column, you can get it this way:

with ct as
(
select id,
  unnest(regexp_split_to_array(t, '\+')) part
from
  a
),
ct2 as
(
select
  id, string_agg(part, '|' order by part) parts
from
  ct
group by
  id
)
update a
set t = ct2.parts
from ct2
where ct2.id = a.id;

id | t
-: | :----------------------------------------------------------------------------------------------------------------------------------
 1 | 250 TABLETS | ALU | Film-coated tablet - BLISTER - [/Tablet] - QUETIAPINE FUMARATE 28.78 mg is present| HOSPITAL PACK | PVC | PVDC
 2 | 20 ML | MATERIAL UNKNOWN | Oral suspension - BOTTLE - [/mL] - ALUMINIUM HYDROXIDE 37 mg; MAGNESIUM HYDROXIDE 40 mg

db<>fiddle here


Do SQL Servers in a cluster need to have the same memory?

There's no functional requirement for servers in a cluster to have the exact same specs (memory, CPU, etc).  So you can definitely set it up this way.

To some extent, you've answered your own question.  To put it differently - if one of the servers in the cluster will never be running the same workload as the other, you should tune that server appropriately for its workload.  It would be a waste of resources to do otherwise.


Convert Postgres TIMESTAMP to TIMESTAMPTZ

Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC.
ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';

Check a possible column default for sanity, too. Any expression working with data type timestamp (like LOCALTIMESTAMP or now()::timestamp) is subject to the same problem. To change:
ALTER TABLE tbl ALTER ts_column SET DEFAULT now();  -- or current_timestamp

Obviously, statements writing to the table also need to use timestamptz now - or you have another instance of the same problem with automatic conversion from the type timestamp [without time zone].
Since this is a production DB, best do it all in a single transaction to avoid race conditions - or even a single statement:
ALTER TABLE tbl
  ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC'
, ALTER ts_column SET DEFAULT now();

Basics:

Ignoring timezones altogether in Rails and PostgreSQL


Allow db_owner to add/view existing logins to database

Isn't GRANT VIEW DEFINITION ON LOGIN::Whoever TO [domain\dbogroup] in master enough?
This is implied by MSDN (I think, can't test) in GRANT Server Principal Permissions

Failing that, one quirk that may be useful for SQL Server 2005+

This may give no rows

SELECT * FROM sys.server_principals WHERE name = 'domain\somegroup';

But this will give a number if it exists

SELECT SUSER_ID('domain\somegroup');

Note: SQL Server 2012 has contained databases where logins are at the DB level


How can I keep two tables in two different databases with different schemas the same in real-time?

As there are two servers there are no real good options. Pretty much your only choice will be to use triggers in both databases that write to the remote database. You'll need logic in the trigger so that the trigger does nothing if the call is coming from the other SQL instance. Otherwise you'll end up with a deadlock every time.

If one instance is down than nothing will work until that server is back up and running.


How to script server level objects in sql server?

One way to script server-level objects is with SMO and PowerShell. Below is a PS example that uses the SMO assembly included with SSMS to script server endpoints and triggers. SMO is also available on NuGet to avoid the dependency on an SSMS install.
# generate scripts for server-scoped object collection
Function Create-ServerObjectScripts($serverCollection, $rootFolder, $typeName) {

    # exclude system objects
    $serverCollection = $serverCollection | Where-Object {
        $_.IsSystemObject -eq $false -or $_.IsSystemObject -eq $null
    }

    if($serverCollection.Count -eq 0) {
        return
    }

    Write-Host "Scripting $typeName for server $($server.Name)..."

    $scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $scriptingOptions.IncludeIfNotExists = $true
    # specify desired SQL version below
    $scriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version130
    $scriptingOptions.AppendToFile = $true
    $scriptingOptions.ToFileOnly = $true

    foreach ($serverObject in $serverCollection) {
        $scriptingOptions.FileName = "$rootFolder/$typeName.sql"
        [void](New-Item -Path "$($scriptingOptions.FileName)" -ItemType "file" -Force)
        $serverObject.Script($scriptingOptions)
    }

}

# ############
# ### MAIN ###
# ############

try {

    $serverName = "YourServer"
    $scriptRootFolder = "C:\Scripts\YourServer"

    $smoAssemblyDirectory = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE"
    Add-Type -Path "$smoAssemblyDirectory/Microsoft.SqlServer.SMO.dll"

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)
    if ($server.ServerType -eq $null) {
        throw "Unable to connect to server '$ServerName' "
    }

    Create-ServerObjectScripts -serverCollection $server.Endpoints -rootFolder "$scriptRootFolder" -typeName "Endpoints"
    Create-ServerObjectScripts -serverCollection $server.Triggers -rootFolder "$scriptRootFolder" -typeName "Triggers"

    Write-Host "Scripting completed"

}
catch {
    throw
}


UPDATE a column in one table according to a column in another

Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after:

UPDATE dpoint d
SET    native_name = nna.native_name
FROM   native_name_aliases nna
WHERE  d.building_id = 42
AND    d.dpoint_id = nna.dpoint_id
AND    d.native_name IS DISTINCT FROM nna.native_name;

You did not provide table definitions and Postgres version, so I can't tell for sure.

The last line is to prevent expensive "empty" updates. Details:

How do I (or can I) SELECT DISTINCT on multiple columns?


PostgreSQL 9.5, getting "cached plan must not change result type" error

FireDAC FDQuery.

Cursor kind in fetch options was set to ckAutomatic. I changed it to ckDefault and it works now.


Select only rows with minimum values from union table

You can treat the union as a subquery.

SELECT TOP 1 DBID, UPC, Price1, Price2
FROM (SELECT DBID, UPC, Price1, Price2
    FROM Table1
    UNION
    SELECT DBID, UPC, Price1, Price2
    FROM Table2) sub
ORDER BY Price1 DESC


Is there a way to compare very short periods of activity in Oracle?

Directly query the DBA_HIST_ACTIVE_SESS_HISTORY view if you want a narrower view than an ASH or AWR can provide. Use something like this query:

select distinct user_id, session_id,session_serial#, sql_id, program, machine
from dba_hist_active_sess_history
where sample_time between
    to_date('20160819 1830','yyyymmdd hh24mi') and to_date('20160819 1835','yyyymmdd hh24mi')
and event = 'log file sync';


Restarting the server is probably one of the most damaging things for performance. It means you force a cold cache for data, a cold cache for query plans, and all SQL Server internal caches are also nuked in the process. Not to mention that by throwing away all the statistics collected in the operational stats DMVs, you diminish your chances of ever investigating something successfully.

There is no official guidance backing this practice, I have never seen it mentioned in any good practice reputable work, I never hear of a reputable expert ever mentioning this as a practice. Don't do it.


Is there much difference between having a key (intA, intB), or having key (longAB) where longAB is the concatenation of bytes of intA and intB?

Worry about performance only after you've worried about the business.  If intA and intB are meaningful to the business, then NEVER EVER hide those attributes in columns that are "obfuscated" in the way you suggest.  Doing that WILL ALWAYS come back to haunt you.

You'll pay the price when someone wants to do a WHERE INTB = somevalue, and that moment WILL come.

With your design hack, the only way you could answer that question is by filtering the value out of your obfuscated column using some variant of MOD(yourcol,32BIT_MAX).  That means table scan by definition, unless you have been smart enough to also add an index on MOD(yourcol,32BIT_MAX).  But adding that index in addition to an index on yourcol is likely to make the overall solution SLOWER, precisely the opposite of what you were hoping to achieve.


SQL server taking too much time in opening properties in SSMS

Now I'm not stating that this is the solution, but I have encountered various issues with various versions of SQL Server Management Studio if SSMS cannot retrieve Microsoft's Certificate Revocation List from the internet. When SSMS starts or runs various commands, it can display slow performance.

Solution

Downloading the MicrosoftRootAuthority.crl file from the following location and installing it into your trusted certificates, can alleviate some of the issues surrounding SSMS and slow performance.

https://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl


Generate backup script of SQL Agent Jobs using c#

You can use the Script method of the Scripter object in SMO to achieve that.

An example can be found here.


How to write a SQL query to find the range from column values?

As follows (replace yourtable and each of the two instances of rangefield):

SELECT *
FROM `yourtable`
WHERE ? BETWEEN CAST(`rangefield` AS int) AND CAST(MID(`rangefield`, LOCATE('-', `rangefield`)+1) AS int);


SQL Server Transactional Replication and Security Setup

Firstly, it doesn’t matter how you configure your replication, the snapshot agent always runs on your distributor. It takes a snapshot of your publication and puts it in snapshot folder, then depends on your replication type, it will be send to subscriber by either distribution agent or merge agent. So publisher doesn’t need access to snapshot folder, actually no agent will be running on publisher.

Secondly, as the snapshot agent takes snapshot from publication and puts it in snapshot folder, it will need access to publisher and the folder. The log reader agent runs on distributor and reads transaction log on publisher then moves it to distribution database, so it will need access to both publication database and distributor database.

Thirdly, “Agent process account” here is the account setting for distribution agent. In transactional replication, distribution agent will apply the initial snapshot to subscriber and also takes transactions from distribution database to subscriber, so it will need access to both distribution and subscriber database, as well as snapshot folder.

If you need more information about replication, please refer to Book Online.

The only reason the snapshot directory need to be a share is when the subscribers are pull subscribers.Whatever account you are pulling with (the account the distirbution agent runs under on the subscriber, or the publisher login account) needs to be able to read the snapshot share and read files and list files and folders permissions on c:\snapshot.


Querying special character from SQL Server

Unfortunately no, if you have written special characters on a VARCHAR and not NVARCHAR (by omitting the N), their binary storage representation changed and there is no way to retrieve the original. Please note that this only applies when writting explicit strings as they need the N before the literal, if it's assigned throughout an update (for example) with a NVARCHAR column, the values will still retain their correct values.

You can see differences on the following examples. Here we write unicode chinese characters to an NCHAR and display both NCHAR and CHAR versions (their behaviour is similar to NVARCHAR and VARCHAR respectively, for this matter):

DECLARE @char NCHAR(1) = N'人'

SELECT
    NChar = @char,
    Char = CONVERT(CHAR(1), @char)

Result:

NChar   Char
人       ?

Here you can see the data loss, we show the binary representation of both the NCHAR and the CHAR with 2 different unicode characters. Please note that both are different characters but they are stored as the same on VARCHAR / CHAR data types.

DECLARE @char NCHAR(1) = N'人'
DECLARE @char2 NCHAR(1) = N'物'

SELECT
    NChar = @char,
    Char = CONVERT(CHAR(1), @char),
    NChar2 = @char2,
    Char2 = CONVERT(CHAR(1), @char2)

SELECT
    BinaryNChar = CONVERT(VARBINARY, @char),
    BinaryChar = CONVERT(VARBINARY, CONVERT(CHAR(1), @char)),
    BinaryNChar2 = CONVERT(VARBINARY, @char2),
    BinaryChar2 = CONVERT(VARBINARY, CONVERT(CHAR(1), @char2))

Results:

NChar   Char    NChar2  Char2
人       ?       物       ?

BinaryNChar BinaryChar  BinaryNChar2    BinaryChar2
0xBA4E      0x3F        0x6972          0x3F

NVARCHAR holds 2 bytes for each character while VARCHAR just 1. Considering this, you can't revert the conversion back from VARCHAR to NVARCHAR for special characters.


Determine size of mysqldump prior to dumping

Actually, the numbers you have indicate something special: Your table data are fragmented.

Why such fragmentation ? InnoDB data and index pages are 16K. Row data will never completely fill up 16K.

I am sure if you reload the dump into another mysql instance, the data will take up less space, no fragmentation.

What you could also do is shrink all the InnoDB tables. How ???

To shrink mydb.mytable run

OPTIMIZE TABLE mydb.mytable;

or

ANALYZE TABLE mydb.mytable;

You could probably script i as follows:

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema','mysql','performance_schema','sys')"
INNODB_CONV_SCRIPT=Shrunk_InnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${INNODB_CONV_SCRIPT}
mysql -h... -u... -p... -ANe"${SQL}" >> ${INNODB_CONV_SCRIPT}
mysql -h... -u... -p... < ${INNODB_CONV_SCRIPT}

After running this all your tables will be smaller.

To answer your actual question, no, you are not doing anything wrong.

Views take up no actual diskspace except for the View Definition.


First answer uses a CTE to select max(group_value) and then join with the table.

with maxgv as
(
    select element, max(group_value) maxg
    from x
    group by element
)
select x.element, x.group_value, x.value
from   maxgv
inner join x
on         x.element = maxgv.element
and        x.group_value = maxgv.maxg
;

This one uses RANK() function:

with grp as
 (
     select element, group_value, value,
            rank() over (partition by element order by element, group_value desc) rn
     from x
)
select element, group_value, value
from   grp
where  rn = 1;

Both returns the same result:

| element | group_value | value |
|---------|-------------|-------|
| a       | 2           | 1500  |
| a       | 2           | 2500  |
| b       | 1           | 1000  |

Rextester here

But, just to check and compare performance, we need the table schema. I don't know which index is using your current query.


Good idea to include summary of frequently accessed data from another foreign-keyed table?

Is there some particular reason you're worried about how much work your computer has to do to join three tables?

The purpose of normalizing your schema is to make it much harder to corrupt your data when you insert, update and delete it.  Normalizing your schema means that reading your data will be more work (generally), but your code for maintaining your data will be much simpler and therefore much less prone to data corruption errors, as well as being cheaper to build and maintain, of course.

If your data is pretty static, i.e. you write it once and then read it a lot, then the benefits of normalization are less important.

If your performance or resource utilization is very critical, and you can't get the performance you need from normalized data, then you can consider denormalizing your schema.  However, whenever you use denormalization you should do it with full knowledge of all of the potential traps you've set for yourself with respect to data corruption.

Normalize by default and denormalize as necessary, and with caution.


Why would SQL cast have to be expensive if used on a join column?

CAST or any other function is expensive when used in a JOIN condition because it makes the transformed column(s) be non-SARGable.

The database has no way to know what the output of the function will be until it runs the function.  This is as true for CAST as it is for a UDF that you write which does some fancy string manipulation logic.

The condition being non-SARGable means that the database can't use a search argument to find the value - it has to run it on every single row that matches the other criteria in the query.

For this reason, functions should be avoided in any filtering logic - JOINs, WHERE clauses, etc. wherever possible, as it essentially forces a table/index scan.