Your question is too vague and general to get a good response here. What you need is a text (El Masri or Garcia-Molina or Connolly) to explain the basics of RDBMSs and of data warehousing.

Forums like this one are more about smaller particular problems than broad stroke questions like this. Read a text, google. The only tip I would give you is that you should have a good grasp of the basics before going on to DW systems.

There's a time and a place to depart from the rules of normalisation - and don't forget to test with realistic data.


Confusion about connection_id in SQL Server DMV

If I connect to an instance of SQL Server using SSMS, should it be one physical connection only?

Well, I think it would help to first be clear about what you actually mean by "connect to an instance". I will assume that you are referring to starting up SSMS and it asking you to connect to an instance. When this happens, it is, at the very least, Object Explorer, that is making the connection. And when it first connects, it appears to use between 2 and 4 connections / sessions. After a while, it appears that all but one of those connections drops off. These connections are different threads of Object Explorer, gathering info to display. And the behavior of the connections sticking around for a while suggests that connection pooling is being used.

If you have the "Object Explorer Details" tab, then that can use another connection or two, especially if you are clicking on various objects to get per object details at the bottom.

Each connected query window will be yet another connection.

Open up a "properties" window (such as right-clicking on a database within Object Explorer and selecting "Properties" to open the "Database Properties" dialog) and that will be another 1 or 2 connections.

And if you have IntelliSense enabled (it is by default), then sometimes it will connect to gather info and then disconnect.

You can see these behaviors by doing the following test:

Close SSMS completely.
Open SSMS, but when prompted to "Connect to Server", hit the "Cancel" button.
Click the "New Query" button and now connect to the server.
Immediately paste in the following query and hit F5:

SELECT conn.[connection_id], sess.[program_name], txt.[text] AS [most_recent_batch],
       '---' AS '---',
       sess.*,
       '---' AS '---',
       conn.*
FROM   sys.dm_exec_sessions sess
LEFT JOIN sys.dm_exec_connections conn
       ON conn.[session_id] = sess.[session_id]
OUTER APPLY sys.dm_exec_sql_text(conn.[most_recent_sql_handle]) txt;

This query shows all sessions and their respective connections (if they have one). It also shows the most recent query batch to help see how that connection is being used.
Scroll to the bottom of the result list and you should see one row with the following value for "program_name":
Microsoft SQL Server Management Studio
(I believe it should actually be "Microsoft SQL Server Management Studio - Query", but this is how it is working in SSMS 17.0)
Now hit F5 again, scroll to the bottom of the result list, and you should see an additional row with the following value for "program_name":
Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
Don't wait too long between executions or else you might miss it.
Click on the "New Query" button, go back to the first query tab, hit F5 again, scroll to the bottom of the result list, and you should see an additional row with the following value for "program_name":
Microsoft SQL Server Management Studio - Query
Go back to the recently opened query tab, paste in the query shown above (in step # 4), close the original query tab, hit F5, scroll to the bottom of the result list, and you should see one row with the following value for "program_name":
Microsoft SQL Server Management Studio - Query
Go to the Object Explorer panel and connect to the instance.
Go back to the query tab, hit F5, scroll to the bottom of the result list, and you should see at least two rows with the following value for "program_name":
Microsoft SQL Server Management Studio
If you have the "Object Explorer Details" tab, then there should be another connection for that (i.e. at least three non-"- Query" connections).


A NOT EXISTS can help in this case:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Users(Id INT PRIMARY KEY);
CREATE TABLE Tags(Id INT PRIMARY KEY,User INT REFERENCES User(Id));

INSERT INTO Users(Id) VALUES(1);
INSERT INTO Users(Id) VALUES(2);

INSERT INTO Tags(Id,User)VALUES(1,1);

Query 1:

SELECT * FROM Users AS U WHERE NOT EXISTS(SELECT 1 FROM Tags AS T WHERE T.user = U.id);

Results:

| ID |
|----|
|  2 |

(Thanks, @dotancohen for putting this into a SQLFiddle)


What is a good way to query data stored in multiple identical tables split by date/time

Personally, I'd consider using triggers to load a proper single table and query that.
Then you have control of partitioning (if needed), defining good indexes etc

Depending on volumes, you could use service broker to decouple the app and your tables.

Basically, anything to avoid massive UNIONs and dynamic SQL...


Known Good SQL Logins Failing (Error 18456) Accompanied by “Unable to Reuse a Session” (Error 18056)

This is a guess.

It may be network errors. A client process will open a connection to the server, query and then typically close the connection. A few seconds/minutes later it does it again. Opening a connection is an expensive operation, so Connection Pooling was invented. With Connection Pooling, when the client process closes the connection, the connection is not actually closed: the object in the application representing the connection is invalidated and cannot be re-used, but the actual connection to the DB is preserved. When the process tries to open a new connection to the DB, the pooled connections associated with the process are searched for one with the same connection string; if found that connection is re-used avoiding the overhead of creating a new one. If one isn't found then a new one is created from scratch.

If during a query the network burps the actual underlying connection in the pool can be invalidated by the server, but the client end remains in the Pool. Re-using it will come up with an error tat mentions something about 'transport-layer level exceptions'.

The invalidation by the server would be logged by the server. I've never looked at what it logs having always come across these from the client end.

One way to recreate the error is to use SSMS to open a query against a server (could be localhost). Run a simple query and it should work. Stop and restart the SQLServer service and re-run the query. SSMS should give an error.

One way to deal with this is by clearing the Connection Pool. Another way is by stopping and restarting the client application (which effectively clears the pool because the app is now running in a different process).

Here is some code I used in an EF Azure-based app (where transient network errors should be catered for):

internal static class TransientError
{
    // Try a very light-weight SQL command to test the connection
    // Basically costs a round trip to the DB.
    // If an SqlException is returned, clear the connection pool, wait and retry.
    // If another SqlException double the wait until a total of 12 secs has been used.
    // After 12 secs (wait = 6400) return; it's not that transient so let standard error handlers
    // cope with it.
    // If any other exception occurs - don't try to do anything clever, just return and let nature
    // take its course.
    internal static void Try(DbContext context)
    {
        int wait = 800;
        bool retry = true;
        while (retry)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(context.Database.Connection.ConnectionString))
                {
                    con.Open();
                    using (SqlCommand com = new SqlCommand("declare @i int;", con))
                    {
                        com.ExecuteNonQuery();
                    }
                }
                retry = false;
            }
            catch (SqlException)
            {
                Clear(context);
                Thread.Sleep(wait);
            }
            catch (Exception)
            {
                retry = false;
            }
            // break after we reach 6.4 secs (for a total as 12 secs)
            if (wait == 6400)
            {
                retry = false;
            }
            wait = wait * 2;
        }
    }
    private static void Clear(DbContext context)
    {
        // This is a clint side operation so won't cause an exception
        // with a server side error.
        // If it does fail, probably should throw!
        using (SqlConnection con = new SqlConnection(context.Database.Connection.ConnectionString))
        {
            SqlConnection.ClearPool(con);
        }
    }
}

This Try method was called before every EF access. For example:

public Service GetService(string serviceName)
{
    TransientError.Try(this.context);
    Service s = (from svc in this.context.Services
                 where svc.System == serviceName
                 select svc).FirstOrDefault<Service>();
    return s;
}

As i said, a guess. If you try the SSMS experiment above and the error matches exceptions thrown by the client then this is likely what is happening.


Will different tables for different Users help in performence

You only need 2 tables:

Users (user_id (PK), username, password, email, created_at)
Posts (post_id (PK), user_id (IX), subject, body, created_at)

Updates to either table would be done using the PK so they will be very fast. A simple JOIN can be used to retrieve posts of a particular user based on username, or a simple SELECT if the user_id is already cached.

Later on, any new features would be another table, referencing data in the aboves. Let's say you wanted to add a feature that tracked 'Post Likes'. Create a table 'post_likes' (post_id (PK), user_id (PK), created_at). Each time a user likes someone else's post, insert to this table.

Or allowed people to 'subscribe' to a particular user. 'user_subscribe' (subscribee_id (PK), subscriber_id (PK)). When user #14 makes a new post,

SELECT
  u.email
FROM user u
LEFT JOIN user_subscribe us ON u.user_id = us.subscribee_id
WHERE
  u.user_id = 14

then email all those people of the new post.

Until your "posts" table reaches the ~500-700GB range, you should be fine with this. After that, we can discuss proper sharding.


Getting DPM Alerts for Failed Recovery Point for Database that has Been Deleted

DPM is checking the consistency of the SharePoint meta-base, which is not consistent anymore due to you deleting the database in SQL Server. That is, you have removed the logical/physical database in SQL Server but you have not removed references to it from the SharePoint metadata. My advice to you is to restore the database that you deleted and then remove it correctly via the SharePoint PowerShell cmdlets.

There is a really good article on removing orphaned databases here:

http://social.technet.microsoft.com/wiki/contents/articles/30558.sharepoint-2013-deleting-orphaned-sharepoint-databases.aspx

And a list of all database-related cmdlets can be found here:

https://technet.microsoft.com/en-us/library/ee906544.aspx


There could be many reasons.
But generally, it may be due to:

Free space in data files or temporary files
Undo data
Row chaining and migrating
Data blocks fragmentation


Testing multiple queries from input file

It looks like you need to write dynamic SQL. Dynamic SQL is SQL the creates SQL that can get run. Here is a simple example of dynamic SQL.

SELECT 'ALTER ' ||
       CASE object_type
       WHEN 'PACKAGE BODY' THEN 'PACKAGE '
       WHEN 'TYPE BODY'    THEN 'TYPE '
       ELSE object_type ||' '
       END || owner ||'.' || object_name ||' '||
       CASE object_type
       WHEN 'PACKAGE BODY' THEN 'COMPILE BODY;'
       WHEN 'TYPE BODY'    THEN 'COMPILE BODY;'
       ELSE 'COMPILE;'
       END text
  FROM dba_objects
 WHERE status = 'INVALID'
 ORDER BY 1;

You should also have formatting in the SQL to keep the output of the SQL as clean as possible.

SET TERM      OFF
COLUMN text FORMAT A150
SET ECHO      OFF
SET TRIMSPOOL ON
SET TAB       OFF
SET FEEDBACK  OFF
SET PAGESIZE  0
SET LINESIZE  150
SET TIMING    OFF
SET TERM      ON

Then you just need to write the SQL that generates SQL, if you need to connect as a different user you can include a line like:

SELECT 'CONNECT user1/cdjkfghljsdg@sidname' text from dual;

But you need to figure out how to write the SQL, since you have not given many details.
You will spool to a file, then turn on feedback, set pagesize 50 or so, probably set timing on etc, then run the output of the spool file. You may want to spool to a log file during the run phase. There are alot of examples, you just need to query for an example that matches what you want to do.


Any issue in referencing a unique index from a foreign key table?

No, a FOREIGN KEY constraint can reference a column combination that is either the PRIMARY KEY or has a UNIQUE constraint or has a UNIQUE INDEX.

Copied from MSDN page, CREATE TABLE:

  FOREIGN KEY REFERENCES
  Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table. Foreign keys on computed columns must also be marked PERSISTED.

The only difference between the first (primary key) option and the other two is that unique* constraints and indexes can be defined on nullable columns. Primary key columns have to be NOT NULL.

* Note that there are some slight differences between these in SQL-Server (unique indexes offer some options that are not available with unique constraints): When should I use a unique constraint instead of a unique index?

For example, (as @gbn points), a unique index can be partial. But then, it can't be referenced by a FK.


SQL Server 2012 AlwaysOn performance best practice

AlwaysON in a roundabout way can help with overall performance but it does that by helping you scale out the reads to other nodes which then also help you with blocking/locking.

From how you described it you are spreading out your databases across 2 different instances, but then making replica's of each instance on the other database server.  Outside of locking/blocking benefits you won't get too much out of this, so no, I wouldn't consider this a 'best practice' for performance.  Your active production databases are still sharing server resources with the read only copies.  Thus, this isn't a 'best practice' for performance and AGs.  Also AG's represent a issue where you have to decide if you're willing to get data that's a little bit stale and near real time, or if you want to force performance loss on the master writer and keep the data on the secondary up to date.

If you're interested, here is how StackExchange uses AGs to give you a bit more idea of how enterprises use it.


Postgres. Generate continuous series for multiple categories

You can use generate_series() function:

select
    d.d::date as date,
    t.update_date,
    t.category_1,
    t.category_2,
    t.value
from
    t,
    generate_series(start_date, end_date, interval '1 day') as d(d) ;

It works basically by taking each row from t and producing a set of rows, one for each day from start_date up to (and including) end_date.

Test at dbfiddle.uk


Check out this Microsoft KB article:

You receive an "Error 18482" error message when you try to make a remote procedure call (RPC) from one server to another in SQL Server

Especially the very last part:

  You may receive this error message when installing replication because the installation process makes remote procedure calls between the servers involved in replication.

Since you mentioned that SQL Server was reinstalled, it's possible server names have gotten out of sync (depending on how the install was done).

I'd suggest following the troubleshooting steps in that KB.  In particular, make sure that srvid for the local server is "0" in sys.sysservers on both machines.


Query to test and return timed lockout in MySQL

I think a more elegant solution is

SELECT
TIMESTAMPDIFF(MINUTE, date_last_updated, now()) as mins_since_last_update
FROM levels
WHERE id = 123

This gives me the time value as the OP requested, as an integer.


SQL: Can we make query to compare data for 2 different years in same month, but looking at the same day as priority

If I correctly understand your question, you can use two (ore more) ways but both are related with DATEPART function.

DECLARE @Date1 datetime = '20181101';
DECLARE @Date2 datetime = '20170101';

SELECT DATEPART(DAYOFYEAR, @Date1) DayOfYear,
       DATEPART(WEEK, @Date1) WeekNumber,
       DATEPART(WEEKDAY, @Date1) WeekDay,
       @@DATEFIRST FirstDayOfWeek;

DayOfYear | WeekNumber | WeekDay | FirstDayOfWeek
--------: | ---------: | ------: | :-------------
      305 |         44 |       5 | 7

Using DayOfYear

SELECT DATEADD(DAY, DATEPART(DAYOFYEAR, @Date1), @Date2) AS UsingDayOfYear;

| UsingDayOfYear      |
| :------------------ |
| 02/11/2017 00:00:00 |

Using same week & same day of week

This depends on first day of week:

SET DATEFIRST 1;

SELECT DATEADD(DAY, ((DATEPART(WEEK, @Date1) - 1) * 7) + DATEPART(WEEKDAY, @Date1), @Date2) UsingSameMonthSameDayOfWeek;

UsingSameMonthSameDayOfWeek |
| :-------------------------- |
| 02/11/2017 00:00:00         |

You can use that function to add another combination, for example: First Thursday of November.

db<>fiddle here


Why is this filtered index not only not used but errors when I try to force it

We found that this error occurred because forced parameterization was on at the database level. For other readers  who want to reproduce it, use Kenneth's sample code in a database with this setting on:

create database filterme;
GO

alter database filterme set parameterization forced;
GO

For parameterized queries, SQL Server doesn't want to cache an execution plan with a filtered index that might not work for other parameter values.

In later versions of SQL Server, the query plan for the "non-hinted" plan would (hopefully) show an "unmatched indexes" warning to alert you better to this. That just didn't exist in SQL Server 2008R2. (Note: I just tested on SQL 2016 and am not seeing the unmatched indexes warning, need to look into more why it isn't showing up with this sample code.)

To get the query to work without changing the database setting, you can use an "Option recompile" hint in queries. That works because it instructs SQL Server not to cache the plan for re-use.


Mysql improve Self join performance large table?

Here's a stab at this: see fiddle at http://sqlfiddle.com/#!9/1fc60/1.  My algorithm in general to take all the start/end values for all clients, sort those numbers and make each adjacent pair of values into an interval.  For n clients, you have 2n-1 intervals (in worst case). Then, evaluate each of those intervals to see how many clients' IP ranges they fall into.  If more than 1, then you found a conflicting IP range between two clients.  It does works, but as you get bigger data sets, it gets considerably slower.  The algorithm is Polynomial in time complexity.  On modest hardware, I was able to process 16k clients for conflicts in about 6 minutes.

I think there's a much better algorithm that might take logarithmic time (good), or even linear time (way better), but this algo would be pretty tough to do in MySQL or any RDBMS.  It would be much easier in an OO 3GL like Java or .NET.


Why does DBCC TRACEON (1204, 1222) not work?

Running just DBCC TRACEON (1204, 1222) is per session level.

You should run DBCC TRACEON (1204, -1) globally.

See BOL- DBCC TRACEON for more details.


Is there a way to hide mysql innodb cluster queries from general_log?

There is obviously a whole lot of administrative MySQL noise in the general_log.
You cannot really screen them out. So, what can do you ???
SUGGESTION: USE THE SLOW LOG !!!
The Slow Log ??? Yes, that's exactly what I said : USE THE SLOW LOG !!!
You are probably saying : "Why would I use the slow log when I need to general log ???"
Here is the problem you seem to be facing: you want to hide administrative SQL commands like:
select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance

and
select I.mysql_server_uuid, I.endpoint, I.xendpoint from
mysql_innodb_cluster_metadata.v2_instances I join
mysql_innodb_cluster_metadata.v2_gr_clusters C
on I.cluster_id = C.cluster_id
where C.cluster_name = 'prodcluster'
AND C.group_name = '8a70e310-a997-4d59-8e81-92dab9daa5be'

You can make the slow log behave like the general log by setting this:
SET GLOBAL long_query_time = 0;

Then, everything that executes will land in the slow log.
So, how can you screen out the administrative SQL commands ??? Think about how long it takes for administrative SQL to execute. Try setting this:
SET GLOBAL long_query_time = 0.5;

Once you set this, everything that takes less than 0.5 seconds will not be recorded in the slow log. I am sure administrative SQL commands will not take 0.5 seconds or more to run.
Once you are satisfied that administrative SQL commands are not being recorded in the slow log, go add that setting in the my.cnf file:
[mysqld]
long_query_time = 0.5

or if you are using MySQL 8.0
mysql> SET PERSIST long_query_time = 0.5;

SUMMARY

Disable the general log
Enable the slow log
Run SET GLOBAL long_query_time = 0.5; in mysql
Add long_query_time = 0.5 under [mysqld] in /etc/my.cnf


Defining password complexity and its action on "grace" state USERS

The password complexity is managed by a function. You can edit the function to fit your requirements or disable it at all.

Check this link : https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/configuring-authentication.html#GUID-A39E191B-4A06-442D-94C7-5882B73DDCFA
On the section "Managing the Complexity of Passwords"

For the password "grace" time, you should check the Profiles and modify them accordingly.
On the same link check for "PASSWORD_GRACE_TIME"

If you want, you can disable the password verify function, assuming the all your users are in the default profile, alter profile default limit PASSWORD_VERIFY_FUNCTION NULL;


Clustered index update, how changing the index only at different column value?

how many record will updated at one go and how frequently ?

  Why index is updating when update a equal value?

Because optmizer is not smart enough to detect if you are updating same value.

It is good that index is rebuild of its own,though it is time consuming.

If you disable index then you have to rebuild it later.

your situation is very serious ,1.one row, 30 000 per minute
Hope you have handle the requirement correctly.

As by definition of index or disadvantage of index is that
it slow down update statement if that table is so frequently updated.

Now you have to answer how frequently Name is use in predicate and how.is index helping you there.Depending upon all this it is better to drop index on Name.

Don't keep any non clustered index in these column which are use in this proc.

since @name 10% is null you need dynamic sql whether you drop index or not.
it will improve your query on 90% occasion.

sp_executeSql example (just an example)

declare @Name varchar(250)
declare @othercol varchar(250)
declare @Id int=1

DECLARE @ParmDefinition nvarchar(500);
set @Sql='update table1 set'

if (@Name is not null)
set @Sql=@Sql+' Name=@Name1 ,'

set @Sql=@Sql+' othercol=@othercol1 '

set @Sql=@Sql+' where Id=@LocalID '

SET @ParmDefinition = N'@LocalID int , @Name1 varchar(250) , @othercol1 varchar(250)';

print @Sql

EXECUTE sp_executesql @Sql, @ParmDefinition,
                      @LocalID = @Id,@Name1=@Name,@othercol1=@othercol ;


Will the indexing affect insert/update/delete operation in Mysql

Of course.

The choice of primary key can greatly influence all select/update and insert operations. It's because your data will be PHYSICALLY placed on the disk by the order of the primary key.

Modern hard drives provide great transfer but only if you read sequentially. When it comes to reading random spots on the drive, your HDD  can only access 75-150 different areas per second. So when it has to "jump" around when reading/writing data that can't be written/read sequentially ( due to bad primary key chosen ) - it will significantly affect performance.

Additionally, when inserting, mysql server has to recalculate indexes and when you overindex your table, this can kill your performance.

Choosing your indexes - and particularly your primary key - is one of the most important choices when designing your tables. Most performance problems can be fixed by addressing indexes.


It is quite common to copy the data as it stands at the point in time that a transaction occurred - in your case the Settings when a FoodMaker made something.  This happens with financial systems a lot for audit and reporting purposes.  Yes, it is duplication, but in a known and controlled way for a purpose.

The alternative is to add time range columns to Settings.  These delimit the interval in which those settings values are applicable.  The third table must hold the datetime when a row is inserted to it, as you propose.  When you read you add the predicate

.. and Third.date_time >= Settings.FromDateTime and Third.date_time < Settings.ToDateTime

With this approach you have to be very careful to never update values in Settings, only ever the ToDate, or insert whole new rows.  Ensuring there are no gaps or overlaps in these time ranges can be tricky, too.  Frequent changes to Settings can cause this table to bloat, slowing down the transactional part of your application.  Eventually you will want to purge old Settings which will fragment the table.

With a separate audit table containing snapshots of values most of these concerns go away.  You can deny all UPDATE activity on it, allowing only INSERTS so there is no risk of history changing.  The table can be partitioned by date allowing for fast purging.  Indeed, the whole table can be removed to a separate data mart for long-term storage and reporting.


SQL Server Availability Group Design Question/RFC

Yes, other than the 2012R2 bit what you are describing can be done, but to correct some of the terminology I think you are describing the following:

A 4 server Window Geo cluster with 3 servers in Data Centre 1 (DC1) and one server in Data Centre 2 (DC2).
Instance of SQL 2012 lets call it INST01 on server SQL1 in DC1.  This instance will be used for your infrastructure dbs.
Instance of SQL 2012 lets call it INST02 on server SQL2 in DC1.  This instance will be used for your user dbs.
Instance of SQL 2012 lets call it INST03 on server SQL3 in DC1.  This instance will be used to run your local AOG replicas in normal running.
Instance of SQL 2012 lets call it INST04 on server SQL4 in DC2.  This instance will be used to run your DR AOG replicas in normal running.

From an always on perspective you will have:

AOG group called ITDBS.  This will exist across INST01, INST03 AND INST04.  Always On Group ITDBS on INST01 would be the primary holding your infrastructure dbs and INST03 would be synchronous read only replicas (handy for reporting/scaling out) and INST04 would be asynch replicas for your DR scenario.
AOG group called USERDBS.  This will exist across INST02, INST03 AND INST04.  Always On Group USERDBS on INST02 would be the primary holding your infrastructure dbs and INST03 would be synchronous read only replicas (handy for reporting/scaling out) and INST04 would be asynch replicas for your DR scenario.

The above Always On Group configuration assumes 1 AOG for each db type on each server, you will have to consider if you need to subdivide dbs if so just create more Always on groups.  An AOG is basically just a way of grouping databases so they fail over as a logical consistent unit (unlike mirroring where there is no consistency across dbs in terms of failing over).

The above scenario gives you high availability with automatic failover if you wish due to synchronous replicas in DC1.  It also gives you DR capability albeit with a manual failover to your SQL4 server in DC2.

If SQL1 and SQL2 are big enough you could use each as the others synchronous replicas thus avoiding the need for SQL3 (ie SQL1 sync reps would live on SQL2 and vice versa) but SQL1 and SQL2 would need to be big enough hardware to cater for the loads in the event of failure.  This may save you some money.  You would need to work out the costs of having SQL3 (hardware and SQL licenses) against the extra CPUs and memory you would need in SQL1 and SQL2 to run the extra load during failure.

From a licensing perspective and storage perspective quite expensive and all 4 servers need full Enterprise SQL licenses and all non shared storage.

One other possible way to save a bit on storage and everything is have a 3 server windows cluster with 2 in DC1 and 1 in DC2.  Have SQL1 and SQL2 as a SQL failover active/passive cluster.  This would mean in SQL2014 as long as you have Software Assurance for the license you get 28 days free failover to the passive node.  This would save a lot on licensing but also storage as the 2 server SQL cluster would be using shared storage.  The down side of this option is that failover is not instant as it is in your solution, there is a 20 second delay or so for the cluster service to failover (depends on disks etc).  Your Always On Group would then include the FCI and the SQL3 in DC2. Also another downside of having an FCI in an AOG is you lose automatic failover, but the dbs in DC2 are  asynch replicas so you wouldnt have feature anyway.

Also think about your quorum design, 3 would be a bit trickier you would need a vote from a witness disk and to use the DR server you would need to force quorum if both DC1 servers were down but once documented it is easy for your ops/dbas to follow.

Lots of info to consider, hope this helps.


Apply job aborting with <1 granule size unable to allocate, ASMM on

This answer is based on the discussion had in the comments of the original question.

The primary cause of any ORA-4031 is that the shared pool is exhausted and is unable to allocate new connections to the database. This will cause the database to crash. I am not going to comment if the package that is running the import is just the last straw or if it is the entire problem, architecturally, I am just offering a workaround.

Due to the small nature of the server memory, space is extremely restricted. I would recommend assigning specific amounts of memory to the shared_pool_size parameter in the spfile and bumping up memory usage incrementally each time the issue occurs. This, of course, implies an increase to SGA_max_size and SGA_target_size. Initial sizing and increment size will be left for the author to discuss, but I'd imagine 50m jumps would be small enough to keep the server happy and also have reasonable chances of success to workaround the ORA-4031.

If memory of the server becomes exhausted before issues begin to resolve, then I would begin digging around the import package load to see if there are inefficiencies with how that procedure is using the shared_pool.


changing Virtual IP interface names on RAC nodes - how to reconfigure

If the NIC has changed for the Public / private NIC:

Modify the Public NIC information like so:

$CRS_HOME/bin/oifcfg/oifcfg delif -global <if_name>[/<subnet>]
$CRS_HOME/bin/oifcfg/oifcfg setif -global <if_name>/<subnet>:public
For example:
% $CRS_HOME/bin/oifcfg delif -global eth0/10.2.156.0
% $CRS_HOME/bin/oifcfg setif -global eth0/10.2.166.0:public

Then make the change at OS layer.

It can be done one node at a time so there's no down time unless the OS requires it.

Once the public network is changed, the VIP needs to be modified like so:

View the VIP information:

$ srvctl config nodeapps -n racnode1 -a
VIP exists.: /racnode1-vip/101.17.80.184/255.255.254.0/eth1

Stop the nodeapps resources

$ srvctl stop instance -d <db_name> -i <inst_name>   (optional for 10.2.0.3+)
$ srvctl stop asm -n <node_name>                     (optional for 10.2.0.3+)
$ srvctl stop nodeapps -n <node_name>

eg,
$ srvctl stop instance -d RACDB -i RACDB1
$ srvctl stop asm -n racnode1
$ srvctl stop nodeapps -n racnode1

Verify that the VIP is offline:

$ crs_stat -t

Modify VIP like so:

# srvctl modify nodeapps -n <node> -A <new_vip_address or new_vip_hostname>/<netmask>/<[if1[if2...]]>

eg:
# srvctl modify nodeapps -n racnode1 -A racnode1-nvip/255.255.255.0/eth2

Verify the change:

$ srvctl config nodeapps -n <node> -a (10g and 11gR1)
$ srvctl config nodeapps -a (11gR2)

eg:
$ srvctl config nodeapps -n racnode1 -a
VIP exists.: /racnode1-nvip/110.11.70.11/255.255.255.0/eth2

Restart nodeapps:

$ srvctl start vip -n <node_name>
$ srvctl start listener -n <node_name>
$ srvctl start instance -d <db_name> -n <node_name>      (optional)

eg,
$ srvctl start vip -n racnode1
$ srvctl start listener -n racnode1
$ srvctl start instance -d RACDB -n racnode1

Verify the VIP is online

$ crs_stat -t (or $ crsctl stat res -t for 11gR2)


How can I output a new line with `FORMATMESSAGE` in transact sql?

The problem is the interpretation of \r\n as carrriage return and line feed. SQL Server does interpret \r\n like this in some places, but the FORMATMESSAGE function is not one of them, so instead you need to use CHAR(13) and CHAR(10) which represents the the ASCII codes for Carriage Return (13) and Line Feed (10):

DECLARE @RowCount INT
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + CHAR(13) + CHAR(10) + ' %d users', DB_NAME(), @RowCount+1)

Or use a variable:

DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + @NewLine + ' %d users', DB_NAME(), @RowCount+1)
-- or
DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has %s %d users', DB_NAME(), @NewLine, @RowCount+1)


When referring to ib* files, are you also referring to the ib_log files? They also need to be removed when you remove the ibdata* files. Have a look at https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql that might be of some help.


Yes, you can put different node types on the same machine.  But...  You need to change the configuration of each.  Running out of RAM would be really bad for performance.

For a Web application, I see no need for the complexity of NDB Cluster.  Virtually all Web usages of MySQL simply use InnoDB.

Before digging into more details, please state your goals.  Redundancy?  Resiliency?  Scale?  Key-value store?  Other?  I don't hear any of those in your Question.


How to copy the postgresql's xlog files safely

The best (and recommended) way to do it is to configure the database server to copy them via the archive_command configuration variable. See also the chapter Setting up WAL Archiving.

If you are hell bent on copying them manually then another way might be to look at the contents of the archive_status subdirectory. When you see a file in this directory with the suffix .ready it means that the corresponding WAL file is completed and should be safe to copy. However I don't think this would happen unless WAL archiving was turned on in the first place - if it is not turned on then postgres will just delete the WAL files soon after they are written (which will not give you much chance to copy them!)


The problem is that values of the UDVs is restricted to the statement an do not passed across different SELECTs and JOINs. If you want to refer to the values calculated in the one SELECT from another SELECT you have to use values stored in the resulting table from the previous statement. Try the next form of the second operand of the CROSS JOIN:

SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines', X.id as id
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM ( SELECT @r := X.id , @l := 0) vars
        CROSS JOIN ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) AS Y;

Here I've replaced all references to the @z by X.id that contains the values of @z calculated in the first SELECT statement.

Just to make it clear: only the last calculated value of the @z UDV is passed across the statements and then it stay unchanged. Therefore you have the same value in the last column of the resulting table. And even now I'm not sure the result would be correct because of complexity of the whole query.


Oracle Single table with json vs set of tables using joins

Part of that depends on whether or not you need to ever parse that JSON, say in a WHERE clause, or GROUP BY, etc...
If it stays a faceless blob, it's probably faster to leave it as option #1; but keep in mind this is rarely the case - you're likely to unexpectedly be asked to report/query on the JSON at some point!!!
I don't know about Oracle, but in Microsoft SQL Server, UNIONs tend to perform poorly when ALSO joining; they seem fine as a standalone query but once it becomes a subquery things seem to slow down, unless you do something like a materialized view.
So while I personally prefer option 2, the performance may bog down with a large data set that is over 1 million rows. Can you refrain from using a UNION, and instead place the records in the same table but distinguish them with a type column?

How many rows (and how wide is a single row) of your "large data set"?
Can you temporarily obtain a test environment and try both options under a load test?


SQL Agent Job Logs - Can't get the Error Log

I have written a post about retrieving job status from msdb, which you can see here.  The code in that post does not pull in data from dbo.sysjobstepslogs, but it's pretty straightforward to extend that code to pull in the last error message from dbo.sysjobstepslogs if you are logging to that table.

By default, job steps do not log to msdb. To configure your job steps to log to dbo.sysjobstepslogs, you must go into the job step properties for every step in the job, visit the "Advanced" tab, and select the option for "Log to table":

You can then create this Table-Valued Function to get the most recent job status. I've modified the code from the post above to also pull the most recent error from the job, if it is available:

 CREATE FUNCTION dbo.SqlAgentJob_GetStatus (@JobName sysname)
     RETURNS TABLE
 AS
 RETURN
 SELECT TOP 1
     JobName        = j.name,
     IsRunning      = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL
                          THEN 1 ELSE 0
                        END,
     RequestSource  = ja.run_requested_source,
     LastRunTime    = ja.start_execution_date,
     NextRunTime    = ja.next_scheduled_run_date,
     LastJobStep    = js.step_name,
     RetryAttempt   = jh.retries_attempted,
     JobLastOutcome = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL THEN 'Running'
                        WHEN jh.run_status = 0 THEN 'Failed'
                        WHEN jh.run_status = 1 THEN 'Succeeded'
                        WHEN jh.run_status = 2 THEN 'Retry'
                        WHEN jh.run_status = 3 THEN 'Cancelled'
                      END,
     LastError = (SELECT TOP 1 jsl.log
                 FROM msdb.dbo.sysjobstepslogs jsl
                 WHERE jsl.step_uid = js.step_uid
                 AND jsl.date_modified >= ja.start_execution_date)
 FROM msdb.dbo.sysjobs j
 LEFT JOIN msdb.dbo.sysjobactivity ja
     ON ja.job_id = j.job_id
        AND ja.run_requested_date IS NOT NULL
        AND ja.start_execution_date IS NOT NULL
 LEFT JOIN msdb.dbo.sysjobsteps js
     ON js.job_id = ja.job_id
        AND js.step_id = ja.last_executed_step_id
 LEFT JOIN msdb.dbo.sysjobhistory jh
     ON jh.job_id = j.job_id
        AND jh.instance_id = ja.job_history_id
 WHERE j.name = @JobName
 ORDER BY ja.start_execution_date DESC;
 GO

Finally, you can leverage the TVF to query for your job status, last step to run, last logged error, etc:

 --Specific jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE
        j.[name] like '%JOB_1%'
     OR j.[name] like '%JOB_2%'
     OR j.[name] like '%JOB_3%'
     OR j.[name] like '%JOB_4%'
     OR j.[name] like '%JOB_5%';
 --Failed Jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE sts.JobLastOutcome <> 'Succeeded';


Every subfolder under datadir is considered fair game to be registered as a database. You could just mysqldump that entire database and then drop the database.
SUGGESTION
If you have to leave the database present but inaccessible, here is something radical you can try:
EXAMPLE
Suppose you have a database called mydb and you want to disable access to it. Go into the Linux OS and do the following:
chown -R root:root /var/lib/mysql/mydb

That's it. Since /var/lib/mysql/mydb would be no longer owned by the Linux mysql user, mysqld cannot access anything in that folder.
If you want the database accessible again, just do the reverse:
chown -R mysql:mysql /var/lib/mysql/mydb

Now, if you are dealing with Windows, try shutting down mysql with
net stop mysql

You could then

Rename the folder from mydb to #mydb something mysqld would not like
Run attrib +a against that folder
Just about anything that can deny read/write access to the Windows folder

Finally, start mysql back up
net start mysql

Give it a Try !!!


SQL Server AlwaysOn Failover Transparency

It depends on how long it takes the clustering to fail over and then for crash recovery to run.
No, not unless you program your app to resubmit the query.
Yes, provided that you setup an AlwaysOn listener (that might not be the correct name) and the IP 10.10.10.10 is the IP address which the cluster service and AlwaysOn are using for the floating listener.  If that IP is an IP which is assigned to one of the specific servers which is hosting a copy of the database then no.


Can I remove binary log files not listed in mysql-bin.index?

You can also try purge binary logs before '2016-04-29 08:00:00'. I sometimes find one works when the other doesn't for some reason.

Failing that just delete them with rm like you said. It's not technically correct, but it shouldn't be a problem (I did this frequently in the past before I learned about the PURGE commands, and never had any problems).

Of course you also want to make sure you have expire_logs_days set as well to keep them down in future.

I also just saw this note:

  Prior to MySQL 5.1.24, PURGE BINARY LOGS TO and PURGE BINARY LOGS
  BEFORE did not behave in the same way (and neither one behaved
  correctly) when binary log files listed in the .index file had been
  removed

for some reason I don't have access to the 5.0 page to check what that says.


MariaDB FULLTEXT search with short / mandatory words

FULLTEXT is imperfect.  You are doing the 'right thing' to analyze the data it this way.  Now switch from "abc" and "def" to strings that are realistic in your dataset.
Here are a couple of tricks that I have used reasonably successfully.

Put a + in front of long words, don't put a + in front of short words.

Use this combo to check for things that need LIKE (or RLIKE) but desire the speed of FULLTEXT:
  WHERE MATCH(text) AGAINST ('abc def IN BOOLEAN MODE')
    AND text LIKE '%abc+def%'

That is, use FT to find rows with the "words", then use LIKE to make sure they are adjacent and/or contain certain punctuation.


Restore Oracle tablespace to its state from 2 days ago

You probably tried to restore and then recover the database. When you recover the database all the files should be in sync before you can open the database for work. The error shown tells you that some of your data files are not in sync with the rest of the database.

What your really need to use to recover a tablespace or a set of tablespaces is Tablespace Point-in-Time Recovery (TSPITR). You can do it manually or with RMAN. In the latter case the command might look like:

recover tablespace app_data
until SCN 1234567
auxiliary destination '/tmp/auxdb';

And you wouldn't need to configure the auxiliary database manually. With user-managed TSPITR, on the other hand, you have to configure the auxiliary database first, and then use Transportable Tablespaces feature (see the section "Introduction to Transportable Tablespaces" in Database Administrator's Guide) to move the data files along with metadata to the target database.

For more details read "RMAN Tablespace Point-in-Time Recovery (TSPITR)" and "Performing User-Managed TSPITR" sections in Database Backup and Recovery Advanced User's Guide, and also "Tablespace Point-in-Time Recovery" section in Concepts.


Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key)

Select FileNumber,
    DateOpen as DayOpen,
    Month(#DateOpen#) as MonthOpen,
    Year(#DateOpen#) as YearOpen
From [file]
Order by FileNumber

I assume by day you just want to show the date you have from the day open field. Month(DateOpen) will give you the month number.  You weren't specific about what you were looking for in this field, so I assume this is ok. Order By FileNumber ensures you maintain the order of the primary key field.


Why does pt-table-checksum gives exit status 0 if slave is unreachable?

pt-table-checksum runs on the master primarily, but as binary logging on MySQL is statement based, the actual calculation statements are being executed on the slave as well. One should then run pt-table-checksum on the slave to check and compare the checksums it can't access the slave from the master. Add the --replicate-check-only option to have it only check for differences based on the checksums already present in the checksums table.


NLS_LANGUAGE determines the sort order. If you've went from, for example,  FRENCH to the defaut, AMERICAN, you would get the difference in sort order you describe. To set the default NLS_LANGUAGE execute

alter system set nls_language ='FRENCH' scope=spfile;

and bounce the instance. You can always override this in the session using

alter session set nls_language = 'FRENCH';

To bounce the instance is to restart it. You need to do that when changing some system parameters like NLS_LANGUAGE. If you only change the parameter in the current session you don't need to restart the instance.

To check the parameter values you can, like Phil said earlier, query NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS, and NLS_SESSION_PARAMETERS. To check the setting in the current session you can also run show parameter nls_language.


Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs.

INSERT INTO groupmember (person_id, group_id)
SELECT p.person_id, g.group_id
FROM  (
   VALUES
     ('alice'::varchar, 'girls'::varchar)
   , ('bob','boys',
   , ('alice','coolkids')
   , ('bob','coolkids')
  ) x (username, group_name)
JOIN   person p  USING (username)
JOIN   "group" g USING (group_name);

Aside: don't use the reserved word group as table name.


From here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.


How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

Kimberly Tripp & Paul Randal has a couple of excellent posts on this very subject at sqlmag.com:

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/moving-lob-data-142636
http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/moving-lob-data-keeping-table-online-142703


Is there any way to trace optimizer's work in MySQL?

The recent 5.6 version has added this feature.

See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer

Typical Usage:

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";

SELECT ...; # your query here

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

# possibly more queries...

# When done with tracing, disable it:
SET optimizer_trace="enabled=off";

You can see it in action in SQL-Fiddle. Not sure how the output can be beautified.


Delete shared memory segments left over by an Oracle instance

I hope that you know what you are doing....

First find which segment would be a candidate to drop because it was not cleaned up by an Oracle crash:

ipcs -bom

Take note of the owner and group, they should be the owner of the Oracle rdbms processes. Next check the NATTCH column, should be 0 processes attached to the segment. If you still want to remove that segment (which is pretty safe when there are no processes attached), use the ID column as input for

ipcrm -M {your_ID}

Normally this does not solve a problem. There was a problem that made the instance crash .... check the alertlog, it could have some clues in it.


Attempting to avoid SORT in query planner

You may get an ordered reverse index scan if you had an index on big_table( attr, lower(groupname) ) for your example query.  The explain you posted estimates 14 rows output from the query, in which case the final sort is trivial and not worrying about. Note the sort estimation cost "Sort  (cost=24511.42..24511.45 rows=14 width=150)" shows there's a big cost generating input to the sort (24511.42) and very little incremental cost for the sort (24511.45-24511.42 => 0.03).

You should generally run EXPLAIN with the ANALYZE option so you can see real times, not just estimates.  Divergences between the actual time/rows from the estimated costs/rows is a signal that the planner has bad statistics or lacks adequate rules for proper estimation. DEPESZ's explain explainer tool is often handy for hunting the real problems with your queries.


Mysql performance related question for very large database

Q1:  A "database" is a collection of "tables".  The performance of a query on one table (or a few tables JOINed together) is entirely on the table(s) in the query.  Not other tables; not other databases.

Q2:  Do not make a bunch of tables.  If you have an index starting with ProductCode, then queries with WHERE ProductCode = 123 AND ... will be efficient.  (This question has been asked and answered many times - always the same way.)

innodb_buffer_pool_size should be set to about 20G.

If you usually fetch by ProductCode, then change

PRIMARY KEY (`id`),
KEY `ProductCode` (`ProductCode`),
KEY `AddedDate` (`AddedDate`)

to

PRIMARY KEY(ProductCode, AddedDate, id),
INDEX(id),
KEY `AddedDate` (`AddedDate`)   -- (no change here)

That will 'cluster' all 12K (avg) rows for a given product together, making many SELECTs run faster.

Does Table B have 8K rows?  Or 100K rows?  If there is one row per product, then get rid of id and change

PRIMARY KEY (`ID`),
KEY `ProductCode` (`ProductCode`)

to

PRIMARY KEY(ProductCode)

BUG! You have inconsistent CHARACTER SETs between the tables.  This can cause comparisons on ProductCode to not use an index!  (And hence be sloooow.)  (See ALTER TABLE .. CONVERT TO ..)

UPPER(Model) -- Don't use UPPER.  You have a case-folding COLLATION, so you will get the desired test without need for UPPER().

When I suggested PRIMARY KEY(ProductCode, AddedDate, id) above, including AddedDate was not important.  But looking at the SELECT, it is important, both in the WHERE and in the ORDER BY.


Designing Database for Longitudinal Survey Data

According to Dr. E. F. Codd, the originator of the Relational Model of Data:

Duplicate values are permitted within columns of a relation, but duplicate rows are prohibited.1

Therefore, at the logical level —and taking into account the stated intention to create a relational database—, the column values duplicates presented in your sample table are not harmful.
If the relevant database has to contain large volumes of information, then it consequently must be managed as such; however, there are some aspects that can be optimized conceptually, logically, physically and pragmatically speaking.
Reorganization suggestions
Expository IDEF1X diagram
Having the referred sample table and data as a reference, it appears that the combination of the columns (year, company_name) is sufficient in ensuring row uniqueness (so it should be declared as a KEY, be it PRIMARY or ALTERNATE); therefore, the column labeled as id would be superfluous (assuming that it is a special addendum meant to contain surrogate key2 values, which lack business meaning, and, quite probably, would require handling an extra INDEX at the physical level).
Nevertheless, company_name and (company_?) type seem to be columns that hold data that is physically “heavy” (in terms of bytes, perhaps of type CHAR(n) or VARCHAR(n)), so I consider convenient to make some structural reorganizations, as depicted in the IDEF1X3 diagram contained in Figure 1:

Sample SQL-DDL logical-level design
Then, in accordance with said IDEF1X diagram, we can derive the following logical-level DDL layout:
-- As these are only illustrative examples,
-- you should determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient INDEX strategies.

CREATE TABLE company (
    company_id       INT      NOT NULL,
    name             CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT company_PK PRIMARY KEY (company_id),
    CONSTRAINT company_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE year ( -- “Look-up” table.
    year             SMALLINT NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT year_PK PRIMARY KEY (year)
);

CREATE TABLE company_type ( -- “Look-up” table.
    company_type_code CHAR(2)  NOT NULL,
    name              CHAR(30) NOT NULL,
    --
    CONSTRAINT company_type_PK PRIMARY KEY (company_type_code),
    CONSTRAINT company_type_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE organizational_register (
    company_id          INT           NOT NULL,
    year                SMALLINT      NOT NULL,
    company_type_code   CHAR(2)       NOT NULL,
    number_of_employees INT           NOT NULL,
    total_sales         PERTINENTTYPE NOT NULL
    created_datetime    DATETIME      NOT NULL,
    --
    CONSTRAINT organizational_register_PK                 PRIMARY KEY (company_id, year), -- Composite PRIMARY KEY.
    CONSTRAINT organizational_register_TO_company_FK      FOREIGN KEY (company_id)
        REFERENCES company (company_id),
    CONSTRAINT organizational_register_TO_year_FK         FOREIGN KEY (year)
        REFERENCES year    (year),
    CONSTRAINT organizational_register_TO_company_type_FK FOREIGN KEY (company_type_code)
        REFERENCES company (company_type_code)
);

Company
The company_id column, of type INT (or something similar, depending on the platform of choice), would decidedly be much “lighter” than company.name (helping to, e.g., speed up data retrieval) that is why I added and fixed it as the PRIMARY KEY (PK) of this table.
The name of each company would only be retained once in the company.name column, optimizing disk space usage if that is one of the (physical) concerns. Said column should be established as an ALTERNATE KEY (AK) by means of UNIQUE and NOT NULL constraints, which prevents the duplication of the same name value (thus, protecting data integrity and consistency).
Company Type
The company_type table would carry out a “look-up” role.
Its PK, called company_type_code, being of type CHAR(2) can retain values with business meaning and is far smaller than the column that encloses the full company_type.name, points that enhance data retrieval quickness and maintain readability (quite useful when, e.g., the end-users are interpreting result sets, the data is analyzed with a reporting tool, at the application program code debugging phase, etc.).
This table may hold the data shown below:
 +-—————————————————-+-————————————-+
 | company_type_code | name         |
 +-—————————————————-+-————————————-+
 | FF                | Fast food    |
 +-------------------+--------------+
 | PI                | Pizza        |
 +-------------------+--------------+
 | BS                | Barber shops |
 +-------------------+--------------+
Since the company_type.name values would be kept exclusively in this table, this rearrangement would as well permit exploiting disk space in a more advantageous manner. This column, being an AK, also demands UNIQUE and NOT NULL constraints.
There might well be some other columns that serve “look-up” usage in your database, so a comparable approach can be followed.
Year
I integrated a year table because I esteem it can be helpful to enforce the exclusive retention of data that is associated with years that have already been INSERTed in this table.
Organizational Register
All the the modifications discussed above will have the more substantial impacts on the processes that pertain to the organizational_register table.
Since, comparatively, it is very likely that this table is the one that will keep the larger quantity of rows, having two FK constraints of reduced size, i.e., company_id and company_type_code, the disk space consumption would be smaller.
Moreover, as the “reduced” FK values would very likely be involved in SELECT operations as search conditions or filters, they would accelerate data retrieval speed at the physical tier.
Optional columns
As you mentioned that the real table has more than 60 columns —which, in my experience, is considerably wide— and also that you will usually be querying subsets of them, it would be as well favourable to identify which columns are optional, and then move them to separate tables, grouping the ones that bear a conceptual-level association. This procedure would naturally yield several tables that are narrower (in the order of columns) and, being of course “lighter” compared with a wide one, behave generally faster with respect to retrieval and/or computing processes (e.g., the calculation of an average) at the physical level.
In this way, let us suppose that company_type_code and number_of_employees are optional columns (generally handled via NULL marks). Hence, the expository IDEF1X Diagram may evolve as shown in Figure 2:

And a the tentative logical arrangement would be modified as follows:
CREATE TABLE company (
    company_id       INT      NOT NULL,
    name             CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT company_PK PRIMARY KEY (company_id),
    CONSTRAINT company_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE year (
    year             SMALLINT NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT year_PK PRIMARY KEY (year)
);

CREATE TABLE sales (
    company_id       INT           NOT NULL,
    year             SMALLINT      NOT NULL,
    total_sales      PERTINENTTYPE NOT NULL
    column_x         FOO           NOT NULL,
    column_y         BAR           NOT NULL,
    created_datetime DATETIME      NOT NULL,
    --
    CONSTRAINT yearly_sales_PK     PRIMARY KEY (company_id, year), -- Composite PRIMARY KEY.
    CONSTRAINT sales_TO_company_FK FOREIGN KEY (company_id)
        REFERENCES company (company_id),
    CONSTRAINT sales_TO_year_FK    FOREIGN KEY (year)
        REFERENCES year    (year),
);

CREATE TABLE company_type (
    company_type_code CHAR(2)  NOT NULL,
    name              CHAR(30) NOT NULL,
    --
    CONSTRAINT company_type_PK PRIMARY KEY (company_type_code),
    CONSTRAINT company_type_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE organizational_register (
    company_id          INT      NOT NULL,
    year                SMALLINT NOT NULL,
    company_type_code   CHAR(2)  NOT NULL,
    number_of_employees INT      NOT NULL,
    created_datetime    DATETIME NOT NULL,
    --
    CONSTRAINT organizational_register_PK                 PRIMARY KEY (company_id, year), -- Composite PRIMARY KEY.
    CONSTRAINT organizational_register_TO_company_FK      FOREIGN KEY (company_id)
        REFERENCES company (company_id),
    CONSTRAINT organizational_register_TO_year_FK         FOREIGN KEY (year)
        REFERENCES year    (year),
    CONSTRAINT organizational_register_TO_company_type_FK FOREIGN KEY (company_type_code)
        REFERENCES company (company_type_code)
);

So, organizational_register would be split into two tables:

sales, that would hold the mandatory columns, and
organizational_register, that will incorporate the optional ones.

Using ACID Transactions
The discussed reorganizations would require multiple INSERTs in order to manipulate and retain all the relevant data; in this respect, all the pertinent operations might require to be executed within the same ACID TRANSACTION, so that they either succeed or fail as a single Unit of Work. Perhaps, some kind of batch process would be worth investigating and (or) testing.
JOINs and views
Then in order to derive all the columns of the table as presented in your question, you would have to create a SELECT statement that JOINs the necessary tables.
Certainly, defining one or more views based on the queries that include the JOINs discussed above would be very practical in specific cases, as you can SELECT information directly from the pertinent view with ease.
Summary
In general, my recommendations are:

setting up PK constraints on columns with data types and sizes that “behave” (physically) faster;
retaining the business meaning of (sometimes multicolumn) PKs and FKs when possible;
identifying what are the columns that can be moved to new tables, and place them accordingly based on conceptual-level associations; and
defining (physical) indexing strategies based on the retrieval tendencies that apply.

Endnotes
1 Codd, E. F. (Jan. 1990). Preface. In The Relational Model for Database Management: Version 2 (p. viii). Boston, MA, USA: Addison-Wesley.
2 E.g., a column with the IDENTITY property in Microsoft SQL Server, or with the AUTO_INCREMENT attribute in MySQL, etc.
3 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


How to know the user doing a delete action on an audit table, when using a shared login?

While using EXECUTE AS User = @UserId may be your best option (depending on other issues), here is an alternative approach:

In you stored procedures, or at anytime in your SQL session before you do the DELETE execute the following command:

SET CONTEXT_INFO @UserId

Then in your Trigger you can retrieve this value with

SELECT @var = CAST(CAST(CONTEXT_INFO() As Varbinary(4)) As Int)

This has some disadvantages, the most important of which is that you cannot readily use CONTEXT_INFO for more than one thing at a time.


Grouping by sequential relationships with Postgres

Ugly, but it works!

First, define array_agg_mult from this question

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

and then run the query

WITH summary AS (
  SELECT name, array_agg(related) AS touches
  FROM relationships
  GROUP BY name
),

grouped AS (
  SELECT name, (
    SELECT array_agg(uniques) FROM (
      select distinct unnest(array_agg_mult(sub.touches)) AS uniques
      ORDER BY uniques
    ) x
  ) my_group

  FROM summary LEFT JOIN LATERAL (
    SELECT touches
    FROM summary r
    WHERE summary.touches && r.touches
    GROUP BY name, touches
  ) sub ON true
  GROUP BY summary.name
  ORDER BY summary.name
)

SELECT DISTINCT my_group, row_number() over() as group_id
FROM grouped
GROUP BY my_group;

Which produces the following:

| my_group  |  group_id |
| {a,b,c,d} |      2    |
| {e,f,g}   |      1    |

SQLFiddle here - http://sqlfiddle.com/#!15/c8a5b/20. I'm a novice with this kind of query, so please let me know if there is a more efficient way to do this!


Without a doubt, I would rule out triggers and strictly stay with transactions.

Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.

Combine this with the fact that proper data validation protocols are not implemented in MySQL's Stored Procedure Language. Business Intelligence is OK to have contained in a database provided the Stored Procedure Language can handle a transactional environment. As a MySQL DBA, I have to honestly say that such is not the case with MySQL. Oracle (PL/SQL), PostgreSQL (PL/pgSQL), and SQL Server (T-SQL) have this edge over MySQL.

Concerning transactions, MySQL has InnoDB as its main ACID-compliant storage engine (Deafult storage engine in MySQL 5.5). It has excellent crash recovery and obeys the ACID compliance protocols.

I would choose transacitons over triggers every single time.


Years ago, I saw versions of MySQL available for certain operating systems. One of them had a version MySQL for IBM DB2.

http://dev.mysql.com/doc/refman/5.1/en/se-db2.html

http://www.redbooks.ibm.com/redbooks/pdfs/sg247705.pdf

http://www.docstoc.com/docs/9314480/IBM-DB2-Storage-Engine-for-MySQL--Presentation

If you install MySQL with DB2 Storage Engine, you could migrate the data you want from DB2 into MyISAM tables or InnoDB tables. Then mysqldump every MyISAM or InnoDB table to SQL Text Files.

I hope this helps !!!

UPDATE : I read the first link page. It says as of MySQL 5.1.54, that storage engine is gone. If you can get a hold of MySQL 5.1.35 - 5.1.53 for any IBM OS, you can proceed from there. At least, you can contact MySQL (eh, Oracle [I hate saying that, it just does not roll off the tongue]) and see if you can get those older binaries.


Response from admin@sqlfiddle.com (originally provided by miracle173 in a comment):

If you change the output format to "plaintext" rather than "tabular" (the default) you'll see the whitespace there like you expect. You can change the format by clicking the down arrow next to "Run SQL".
The reason you aren't seeing it in tabular mode is because by default html ignores more than one whitespace character, and I'm not bothering to do any work to override this default behavior for tabular formatting.


How to restrict deletion of specific row of SQL server table

TRUNCATE TABLE requires ALTER permission on the table.
The best way to prevent truncating a table is to revoke that permission.

What’s more TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

See more on TRUNCATE TABLE (Transact-SQL)


Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR

Rather than focusing on how to improve a query like this, which is what the other answers are doing, I'm going to try to answer the question being asked: why doesn't the optimizer produce a plan like the one you've described (that scans the Users table, and then seeks into the two indexes on the Comments table).

Here's your original query again (note I'm using MAXDOP 2 just to simulate what I saw in your execution plans):

SELECT  *
FROM    Users u
        LEFT JOIN Comments c
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

And the plan:

Scan of dbo.Users with residual predicate to get just the "alex" users
For each of those users, scan the dbo.Comments table and filter matches in the join operator
Estimated cost: 293.161 optimizer units

One attempt to get the plan you want would be to try and force a seek on the dbo.Comments table:

SELECT  *
FROM    Users u
        LEFT JOIN Comments c WITH (FORCESEEK)
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

The plans looks like this:

scan of the dbo.Users table (with a residual predicate to only get users named "alex"),
seek into each of the two indexes to get the requested Id values (which are unioned together)
followed up by a key lookup to get the rest of the columns (since we selected *)
Estimated cost: 5.98731 optimizer units

So the answer is that the optimizer is definitely capable of producing such a plan.  And it doesn't seem to be a cost-based decision (the seek plan looks much cheaper).

My best guess is that this is just some kind of limitation in the optimizer's exploration process - it doesn't seem to favor converting a left join with an or clause into an apply.   This is really unfortunate in this particular case, as performance is dismal in the scan plan (the query takes 45 seconds on my machine)  vs the apply plan (less than 1 second).

Side note: You can override the heuristic that disfavors index union plans with undocumented trace flag 8726. See https://dba.stackexchange.com/a/23779 for additional details on that front!

As Rob Farley helpfully pointed out, using APPLY directly (potentially with a UNION as well) is a better approach to get the plan you're looking for - both of those produce the "better" version of this plan (the FORCESEEK version).  I would say that "OR in a JOIN" is kind of a known anti-pattern, and should be avoided since it doesn't seem like the optimizer has great support for that type of query directly.


Oracle change initialization parameter with "deferred" keyword

I've checked experimentally (Oracle 11.1) that actually ALTER SYSTEM ... DEFERRED SCOPE=SPFILE is allowed, although it seems to me that DEFERRED is ignored in this context. Also SCOPE=BOTH and SCOPE=MEMORY are allowed, obviously.

What you cannot do (the manual that you quote overlooked it) is ALTER SYSTEM ... DEFERRED on the parameter that has (V$PARAMETER.ISSYS_MODIFIABLE, V$PARAMETER.ISSES_MODIFIABLE) = (IMMEDIATE, FALSE). This results in:

ORA-02096: specified initialization parameter is not modifiable with this option


Yes, you will need an existing replication environment since ProxySQL is a proxy service. It can work with Percona XtraDB Cluster, MariaDB Galera cluster or MySQL InnoDB Cluster as well.


Can you upgrade a single SQL Server 2008 Failover Cluster Instance to SQL Server 2012?

This is possible and we successfully completed an upgrade of a single failover cluster instance to SQL 2012 on a cluster with multiple SQL 2008 failover cluster instances.

Perform the upgrade of the instance on the two nodes not hosting the instance first (be sure to select the correct instance when asked as in the screenshot), then finally perform the upgrade on the node hosting the instance.


How to display database structure in phpMyAdmin?

select DB, go to export, select tables that you want the structure (top left),deselect "DATA"(middle-center) and select a name and you will be able to export only the structure.

or go to your "database" then "check all" (just below the tables) and change the option from "With Selected:" to "print view".  it will show the relations too, and it will looks like this:


How to find an entry from filtering with two associated tables

Is this what you're looking for?

select
    a.title as 'attr_title',
    ae.title as 'attr_entry_title',
    a.model as 'attr_model',
    p.id as 'attr_model_id'

from
    products p
inner join
    attributes a on a.model_id = p.id
inner join
    attribute_entries ae on ae.FK_attribute_id = a.id
where
    ae.title in('ford','windstar')

You might be able to modify the where clause like this to exclude unselected terms:

ae.title not in (select title from attribute_entries where title not in ('ford', 'windstar')


Why is XML taking up more storage than VARCHAR(MAX)?

There are two things to know about the XML datatype that together explain what you are experiencing:

As noted in @EvanCarroll's answer, the XML datatype is optimized. Meaning, rather than repeat element and attribute names (which are typically repeated quite a bit and are a large part of why so many people, sometimes rightfully-so, complain about XML documents being so bulky), a dictionary / lookup list is created to store each unique name once, given a numeric ID, and that ID is used to populate the structure of the document. This is why the XML datatype is quite often a better way to store XML documents.
Additionally, the XML datatype uses UTF-16 (Little Endian) to store string values (both element and attribute names as well as any actual string content). This datatype does not use compression, so strings are essentially 2 or 4 bytes per character, with most characters being the 2-byte variety.

Looking at the particular test XML document you are using, and the VARCHAR datatype (1 to 2 bytes per character, most often the 1-byte variety), we can now explain what you are seeing as being a result of:

Each of your elements (root, element1, etc) are used only once, so the only savings of placing the names into the lookup list is to cut the size in exactly half. But, the XML type uses UTF-16 so the size of each string is twice as much, cancelling out the savings of moving the element names into the lookup list. At this point, if only looking at the document structure (i.e. element names) then there is should effectively be no difference between the XML type and the VARCHAR version.
But, the string content in each element (i.e. test) takes up twice the number of bytes: 8 bytes in XML as opposed to 4 bytes in VARCHAR. Given that there are 5 instances of "test" per each row, that is 20 extra bytes per row for the XML type. At 10k rows, that is 200,000 extra bytes of the 600,000 byte difference. The rest is internal overhead of the XML type and the additional page overhead of the additional number of datapages needed to store the same number of rows due to each row being slightly larger.

To better illustrate this behavior, consider the following two variations of XML data: the first being the exact same XML as in the question, and the second being almost the same, but with all elements being the same name. In the second version, all element names are "element1" so that they are the same length as each element in the original version. This is results in the VARCHAR data length being the same in both cases. But the element names being the same in the second version allow the internal optimizations to be more noticeable.

-- Original XML (unique element names -- "element1", "element2", ... "elementN"):
DECLARE @xml XML =  '<root><element1>test</element1><element2>test</element2>
<element3>test</element3><element4>test</element4><element5>test</element5></root>';
SELECT DATALENGTH(@xml) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml)) AS [VarcharBytes];

-- More "typical" XML (repeated element names -- all "element1"):
DECLARE @xml2 XML = '<root><element1>test</element1><element1>test</element1>
<element1>test</element1><element1>test</element1><element1>test</element1></root>';
SELECT DATALENGTH(@xml2) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml2)) AS [VarcharBytes];

Results:

ElementNames    XmlBytes    VarcharBytes
------------    --------    ------------
Unique          197         138
Non-Unique      109         138


Using xp_sendmail I believe the only history you will find is:

Critical events that were severe enough to warrant an entry into SQL Server's error log. This does not include things like bounces, undeliverables, malformed addresses, etc.
The history of the e-mail account that is used to start Outlook on the server. So for example you will see sent items there, and bounces / undeliverables in their inbox (if the account accepts incoming mail). This will depend, of course, on what's backing that account - e.g. Exchange.

There are many reasons this feature was deprecated in favor of Database Mail. Unfortunately you need to upgrade to take advantage of better functionality.


What locking can I expect with DBCC CHECKIDENT(RESEED)

Its always tricky when you try to replicate Identity columns.

You should set NOT FOR REPLICATION option on the identity column when you replicate identity field column.

This way only user inserts cause the value to be incremented at the subscriber/s.

You can even assign chunks of identity values and have them self maintained at subscribers. WHen doing this, you have to be careful as

  During a large batch insert the replication trigger is fired only once, not for each row of the insert. This can lead to a failure of the insert statement if an identity range is exhausted during an large insert, such as an INSERT INTO statement.

You can see what types of locks are generated using sys.dm_tran_locks DMV. Doing a reseed is very instantaneous and is not a big overhead. You just have to thoroughly test your idea for all possible cases to avoid any surprises.

Also, out of curiosity, how is your solution more efficient than T-Rep ? As T-Rep is tested since many many years to be more efficient than any custom/home grown solution.


Read questions on sites like this one, Stackoverflow or other DBMS forums.

Then try to solve those problems.

Solving real world problems is still the best practice you can have.


You seem to be asking the right questions and that seems like a reasonable course of action.

Don't be surprised if this runs for a few weeks.


What is the correct way of filtering a property by multiple features?

It is better to use something like this:

select distinct p.* from Property p
inner join PropertyHasFeature fp on (fp.p_id = p.id)
inner join Feature f on (fp.f_id = f.id)
where f.id in (<ID of first feature>, <ID of second feature>)

In this case you will not need to add the joins when the feature list is getting larger. This will select all the properties having one of the required features.

If you need properties having both features without adding multiple joins, you can try using the following (works for MySQL):

select id, property_text from
(select p.id as id, p.property_text, count(f_id) as list_count from Property p
 inner join PropertyHasFeature fp on (fp.p_id = p.id)
 inner join Feature f on (fp.f_id = f.id)
 where f.id in (<ID of first feature>, <ID of second feature>)
 group by p_id) as sub
where list_count = 2

To expand the list of the required features, you will have to change the list_count value to the length of the list of your required properties.


Getting Ranked Position - SQL - Simple Query

If you are in version 8 (or above), you can use the RANK() function for this purpose:

SELECT
    id,
    average,
    RANK() OVER (ORDER BY average DESC) as rk
FROM
    t;

Note I've ordered the result by average DESC, that assigns rank=1 to the maximum average.

id | average | rk
-: | ------: | -:
 2 |      99 |  1
 1 |      79 |  2
 3 |      22 |  3

db<>fiddle here


SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain )
AS address,COUNT(child.id) AS child_count
FROM messages m
INNER JOIN messages child ON child.parent_id = m.id
LEFT JOIN message_address_fields maf ON maf.message_id = m.id
LEFT JOIN addresses a ON a.id = maf.address_id
WHERE maf.field_id =4 AND m.user_id =1 LIMIT 0 , 30


With your example in the question, you could skip the whole ROW_NUMBER() thing and just use TOP (1):

INSERT INTO @Temp (id, salesID)
SELECT TOP (1) 1, Bama
FROM rusticlines AS rstln
WHERE Active = '1'
ORDER BY Bama;

For more complex queries (where you need to get first row in a partition), you could probably make something useful with TOP (1) WITH TIES.


What's the least risky way to Select on production environment?

The correct answer will depend on a number of things:

The size of your database.
The size of your buffer pool compared to your database.
The volume and nature of the background (ie: not you) query activity.
The nature of the queries you want to run.
What will you bottleneck on first (CPU, I/O, tempdb throughput).
How much extra latency you are willing to tolerate in your normal workload.

You should always get an estimated query plan and look for intensive operations, such as scans of large tables/indexes, or sorts of a large number of rows. One of the most important considerations when working with a live, heavily used database is to avoid driving pages out of the buffer pool by running a query that needs to bring a lot of data into cache.

If you are concerned about your select queries going parallel and consuming excessive CPU, you can limit your query to a single core by adding OPTION (MAXDOP 1) to the end of your query.

If you don't have Read Committed Snapshot enabled on your database, see if you can get that turned on. That will give you snapshot-like behaviour on your selects, so that you will not block writers. However if you are running long ad-hoc queries and there is a lot of write activity, you'll get a lot of data written to tempdb.

You could also investigate using Resource Governor to limit yourself.


How to sort the id users that have the maximum of a property?

To get a single row with a maximum value in MySQL you can simply use LIMIT:

SELECT *
FROM `Angriff`
ORDER BY Metall DESC
LIMIT 1;

if there are multiple rows with the same maximum this will return only one randomly.

To get all rows with the same maximum you can modify your query to use a Correlated Subquery:

SELECT * FROM `Angriff` AS t1
WHERE NOT EXISTS  -- there's no row with a higher `Metall`
  (  SELECT * FROM `Angriff` AS t2
     WHERE t2.Metall > I1.METALL );

A more common way in Standard SQL:

SELECT * FROM `Angriff`
WHERE Metall =
  (  SELECT MAX(Metall) FROM `Angriff` );


The error could say:

  Article 'Quote' failed data validation (rowcount only).

However your error states:

  Article 'Quote' failed data validation (rowcount and checksum).

The validation of the merge performs a binary checksum like this:

select count_big(*), sum (convert(numeric, binary_checksum(*) ) )
from [dbo].[tablename] WITH (TABLOCK HOLDLOCK)

As stated by others, the validation during merge can be a bit buggy and you may find it better to roll your own validation.

You can turn off validation by removing the -Validate x parameter from the merge agent.

Regarding your error: Microsoft recommends trying again later, as if the error is temporary. As for code to validate it, you could try the code I link to, but I think you will need to write your own to validate it based on business rules for your data. There shouldn't be harm in disabling the validation, if there was inherent harm the option wouldn't exist. But I would try it with a temporary/test server first.


How to diagnose what is causing DB timeouts

I struglled with this issue for one week.

A the beginning, I started with a 45 seconds query for which the timeout  error message was relevant. I reduced the execution time to 3 seconds and had still the error.

While seraching the Internet, I found out many cases telling the message itself is not relevant, and for this reason, my further investigations were made in other directions. I tried many workarounds I found in many forums without success.

Within my ASP code, I'm using a ADODB.Command object. This object has a CommandTimeout property expected to have a 30 seconds value. I tried to setup the value to 0, which means an unlimited timeout and I had no more the 80040e31 error. Then I tried a 30 value, and again, it worked.

For some reasons, it looks like the defalut value for the CommandTimeout of the ADODB.Command moved to another value. Setting up this value back to the expected default value solves the issue.

Hope it will work for you too.

Yvzs


creating a database scheme for storing information about an item, which option is better?

With the first version the schema design itself forbids having invalid type-color pairs, so I would choose that one.  With the second version you will need an extra mechanism (a trigger for example) to maintain valid state of the items.

From a querying point of view, it means one more JOIN on quite simple tables, I would not expect that causing performance or maintainability problems.

EDIT

Well, I finally realized what could Emmad mean on 'The first answer is not OK, because type information appears in 2 tables' - I silently considered type_name in your line

  types and colors (ID(PK), type_name (FK), color(FK))

a typo, and a foreign key on types.ID instead of name.  With this modification my suggestion is quite the same as Emmad's second diagram.


installing SQL SERVER 2016 side-by-side SQL Server 2012

Installing SQL Server 2016 side by side with an existing SQL Server 2012 can (usually) be installed without issues and is supported as they state in that article.

  SQL Server supports multiple instances of the Database Engine,
  Analysis Services, and Reporting Services on the same computer.

What you can't do however is using SQL Server Sysprep to install different versions side by side.

SQL Server Sysprep is basically an image based deployment solution like you have Sysprep for windows images.

So as long as you manually run through the installation wizard you are fine, but you wouldn't be able to install a SQL Server image using a prepared image by selecting this option on the advanced tab:


Error attaching database after deleting NDF file

Based on all of the information you've provided so far, I'd have to say that, yes, you've lost all of your data. From what it sounds like, I can only guess that something like this happened:

your disk filled up
you saw this big .ndf file
you tried to delete it (presumably because you didn't realize it was a secondary data file)
you couldn't delete it, because it was in use
you stopped SQL Server (or detached the database)
now you could delete the file - yay?

The problem is, the .ndf file is a crucial part of your database. The fact that you couldn't have possibly deleted it without detaching the database or shutting down SQL Server should have been a clue.

Have you not taken any backups of this database since adding the .ndf file? Preferably from as close to the point that you deleted the file as possible?

Now, if you don't have a database backup (or a simple file copy of the .ndf and all the other files in a consistent state, after a clean detach), it is possible that not ALL hope is lost. You may be able to recover the file using some kind of undelete software or taking it to a data recovery service. Provided that the location on disk where the file used to live has not been zeroed out, formatted or re-occupied. You might get lucky, but odds are what you've done here is essentially the same as dropping a donut in the toilet. You might be able to get part of it back, but you still might not want it.

Unfortunately this is called learning why backups are important, the hard way. And also serves as a good warning to others that they shouldn't be deleting files on their system if they don't know what they are for.


There is no "constraint" in MySQL to achieve your complex constraint.  However, you could probably write a TRIGGER(s) that would check each INSERT and UPDATE to see if your requirements are violated.  The TRIGGER could act like a constraint -- barf on your INSERT/UPDATE.

I suspect (but do not know for a fact) that other DBMS also have no such constraint, yet do have a TRIGGER-like capability.


Running out of space, add new drive (add file to filegroup)

is the above correct?

Sort of.  Adding file and Alter database goes together.  Based on what you said I am assuming you only have one filegroup at this time which is called primary.

what are the scripts to run to add the new file to the filegroup

Sample tsql you need to run (copied from books online)

USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

I highly encourage you to read this:

ALTER DATABASE (Transact-SQL) File and Filegroup Options
Database Files and Filegroups

do i need to tell sql server to stop saving data to the old D drive (as its almost full)

You do NOT need to tell SQL Server to stop saving data to the old D drive. Because of something called Proportional fill algorithm.

Ref: File and Filegroup Fill Strategy

  Filegroups use a proportional fill strategy across all the files
  within each filegroup. As data is written to the filegroup, the SQL
  Server Database Engine writes an amount proportional to the free space
  in the file to each file within the filegroup, instead of writing all
  the data to the first file until full. It then writes to the next
  file. For example, if file f1 has 100 MB free and file f2 has 200 MB
  free, one extent is allocated from file f1, two extents from file f2,
  and so on. In this way, both files become full at about the same time,
  and simple striping is achieved.

Read more about this here.


MySQL keeps shutting down: /usr/sbin/mysqld: unknown variable 'set-variable=key_buffer_size=32M'

in the Module Configuration for mysql you find the path to my.cnf

Edit this  file and remove the line with key_buffer_size=32M and save it.

And then start the server.

Webmin information on mysql


Does PostgreSQL PDPG support Yakkety (Ubuntu 16.10)?

PostgreSQL's PDPG only creates separate packages for LTS releases, however it seems to suggest that it should work in the FAQ

  I am using a non-LTS release of Ubuntu
  Non-LTS releases of Ubuntu are only added to the repository if the packages from the latest LTS release are incompatible with the release in question. Using the latest LTS release instead generally works, unless library dependencies cannot be fulfilled, in which case we will provide updated packages for the non-LTS release.

I'm a little hesitant to spring for generally works, but there you have it.

So do not run the suggested command,

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Instead, run

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Warning, I wasn't not able to get this to work... Installing postgis failed.

 postgresql-9.6-postgis-2.3 : Depends: libgdal1i (>= 1.9.0) but it is not installable
                              Recommends: postgresql-contrib-9.6 but it is not going to be installed

Ubuntu 16.10 does not ship with libgdal1i, it ships with libgdal20 and libgdal-dev which uses 2.1.1. Bug filed here


How to get the difference between two timestamps in minutes?

After hours of searching over the internet, I was able to find a shorter version of the code that I was asking for. After several attempts of deriving the code I found here.

(CAST((SYSTIMESTAMP + interval '35' minute) as date) - CAST(SYSTIMESTAMP as date)) * 1440

Result:

+--------+
| Result |
+--------+
|     35 |
+--------+

This version is faster compared to the version that uses EXTRACT. The only downside of this version is that it doesn't include fractions of a second.


Is there a way to get a count about grouped rows?

You GROUP BY release_year but you count release years so you will get 1s, nothing else. If you want to count them, you can enclose in a derived table and do another aggregation:

SELECT
    COUNT(*) AS release_years_with_200_titles
FROM
  (
    SELECT 1
    FROM films
    GROUP BY release_year
    HAVING COUNT(title) > 200
  ) AS dt ;

It can be done without a derived table but it's not very clear how it works:

SELECT
  COUNT(*) OVER () AS release_years_with_200_titles
FROM films
GROUP BY release_year
HAVING COUNT(title) > 200
LIMIT 1 ;

The COUNT(*) OVER () will count the number of rows (after the GROUP BY and HAVING). We then can use SELECT DISTINCT or even better LIMIT 1 to get a single 13 in the result and not thirteen rows with 13.


Duplicated id error, key is actually NOT duplicated

You're trying to insert the same id twice, because MAX(id) doesn't get incremented until commit. What you want to do is precisely what the DEFAULT does on passport

id integer NOT NULL DEFAULT nextval('passport_id_seq'::regclass),

Hint: nextval(seq)

It'll look like this,

INSERT INTO passport (
    id,
    public,
)
SELECT
    nextval('passport_id_seq'::regclass)
    TRUE,
    ...

But even better because id has a DEFAULT just remove it entirely..

INSERT INTO passport (
    public,
)
SELECT
    TRUE,
    ...


why is my trigger called even when the underlying table didn't change?

This is the expected behaviour of a statement trigger. It executes every time a statement (INSERT in this case) is executed and once (per statement, not per row.) The behaviour is similar in other DBMS (like Oracle.)

The MSDN says it in the first paragraph in the CREATE TRIGGER page:

  These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

I don't think there is a way to change this. There are ways to check though - inside the trigger, once it fires - whether there are rows affected or not and act accordingly.

For example you can use

IF ( @@ROWCOUNT = 0 )
  RETURN;

Or a somewhat more robust alternative in the presence of MERGE

IF NOT EXISTS(SELECT *
              FROM   DELETED)
   AND NOT EXISTS(SELECT *
                  FROM   INSERTED)
  RETURN;


Sybase ASE lock table to disallow users insert values

What operation(s) are you trying to perform on the table? Some DML? Maintenance (eg, reorg, (re)indexing, adding/changing RI)? Modifying DDL?

If just doing DML, consider:

begin tran
lock table <table_name> in exclusive mode
--perform DML--
commit (rollback?) tran

'lock table/exclusive mode' (attempts to obtain an exclusive table-level lock), once completed (may have to wait for other locks to be released before successfully obtaining the exclusive lock), means only your current session has access to the table (minus anyone using dirty reads).

Since the 'lock table' command is transactional in nature you need to wrap it inside a transaction hence the 'begin/commit tran'. [If not wrapped in the 'begin/commit tran' then the command would still obtain the exclusive table lock but then immediately release it.]

If you're looking to perform maintenance and/or DDL operations ... there's no easy way to limit access short of denying/revoking access to the table, database or dataserver.


how to capture the data using DMV's in sql server 2008 r2?

The clue is in the name, DMVs are Dynamic Management Views. The data they return is dynamic and will be the latest values available to the system, so they will be constantly updating themselves. So the best you could do is to sample them at a set interval and then try to extrapolate the intermdiate values if you really need them

Some of the DMVs do capture cumulative statistics (sys.dm_exec_query_stats for example, captures cumulative stats about queries). But they don't offer the sort of access you might be looking for at the individual query level.

Your other option may be to use Extended Events ( http://msdn.microsoft.com/en-us/library/bb630354%28v=sql.105%29.aspx ) to capture the queries that the devs are submitting. Pinal Dave provides a nice example of capturing queries - http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/ - his is based on capturing long running queries, but you could easily reduce the time critreia for capture, or remove it completely in favour of a login match or applictaion match as required.