How do you identify database .mdf and .ldf file names? In the DATA folder for SQL Server (Windows Server 2008):

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

There are two files:

xxx.ldf (63.7GB)
xxx.mdf (5.38GB)

(Incidentally why is the size in the info bar at the bottom of the windows explorer screen vastly different to the 'size' column information? The .ldf file size shows a 4GB difference).

There is no corresponding database by that name (xxx).Is there a query on system tables that I can run to show an output of:

DB name | mdf name  | log name
yardi   | xxx.mdf   | xxx.ldf
test    | test.mdf  | test.ldf
prpdb   | prpdb.mdf | prpdb.ldf
etc

I suspect that the yardi DB is related to the xxx .mdf / .ldf files, this particular database we get a .mdf dump that we restore from every morning.

==== EDIT IN RESPONSE TO ANSWERS ====

Two view are mentioned in the answers below (that I only ran in context of the master DB)

sys.databases
sys.master_files

These views are relatable by the field database_id, and surprisingly I had to use both views because the name columns in each were different.

sys.databases.name corresponded to the names I see in SSMS
sys.master_files.name did not correspond to the names I was seeing on the databases

The query that solved my problem is this:

USE master;
GO

SELECT
   ds.[name] as [Database name (as in SSMS)],
   mf.[name] as [master_files name],
   mf.physical_Name as [File name],
   mf.SIZE * 8 / 1024 AS SizeMB
FROM   sys.master_files mf
LEFT OUTER JOIN sys.databases ds ON mf.database_id = ds.database_id
ORDER BY ds.name ASC, SizeMB DESC

==== AND ====

The SizeMB returns a completely different size of the log file compared to what is seen in windows explorer 'size' column, and is also different to the info bar in windows explorer. By several GB. Why?

Querying the attached databases

You should query the sys.master_files table like this:

SELECT * FROM sys.master_files

...or you could restrict the output to only relevant columns:

SELECT NAME,
   physical_Name,
   database_id,
   FILE_ID,
   type_desc,
   state_desc,
   SIZE * 8 / 1024 AS SizeMB
FROM   sys.master_files

This will return a list of database that are attached to your SQL Server instance. This will not list databases that have been detached from your SQL Server instance.

Deprecated View sysaltfiles

You shouldn't be using the sys.sysaltfiles view as this is deprecated and has been added for backwards compatibility only as is mentioned in the reference given by MD Haidar Ali Khan.

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The sys.database_files view

The sys.database_files table as pointed out by SQL_Underworld is only for the database you are currently connected to.

Attaching database files as database

You could attach the database files you have found on your disk with the following command:

USE [master]
GO
CREATE DATABASE [xxx] ON
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.mdf'),
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.ldf')
    FOR ATTACH
GO

This will attach the xxx.mdf and xxx.ldf files to your SQL Server instance as database xxx. You could then rerun the above query to find information about the database.

Detaching a database

After you have found the relevant information you could then detach the database again by running the following query:

USE [master]
GO
EXEC sp_detach_db @dbname = N'xxx'
GO

Size column in sys.master_files

There is a delay in the size reported in sys.master_files according to the MSDN article sys.master_files (Transact-SQL)

  When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.


returning the number of columns in a row that are zero in mysql I have a table that contains amongst other things a datetime column, and 4 other columns that contain either a zero or a number. I would like to return how many columns in a row contain a zero in mysql.

for example

1_datetime      0  2  5  0

2_datetime      5  6  3  0

I would like for each datetime to know how many columns have zeros in

so i want

1_datetime    2

2_datetime    1

thanks

The easiest way is to sum negated values:

SELECT datetime, (!a) + (!b) + (!c) + (!d) AS zero_count
  FROM table
. . . .

Operator ! returns 1 if operand is 0, 0 if operand is non-zero, and NOT NULL if operand is NULL.

Parenthsis added because adding + have higher precedence over negation.


Does Page verification require downtime? So I used sp_blitz and came across  a suggestion to change verification to checksum. What I want to know is, would this step require any downtime?

Nope, no downtime for this change at all.

Here's the explanation of what/when things happen:

  Keep in mind that just changing the setting to CHECKSUM does not
  instantly add CHECKSUMs to your existing data pages in the database.
  In order for this to happen, you have to read each page into memory,
  make some sort of change and then write it back out to the storage
  subsystem. This can happen from normal INSERT/UPDATE/DELETE activity
  over time, or from rebuilding your indexes.

Source: http://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/


Cost of indexed view versus filtered index? I have met the following table design for recording references in non-normalized way:

RecordGUID              uniqueidentifier
DestinationEntity       uniqueidentifier
DestinationRecordGUID   uniqueidentifier

For example for Users table to describe references to Countries, we will have:

 RecordGUID                             DestinationEntity                       DestinationRecordGUID
 79282C80-4474-4617-9C40-C21E97F5AC52   3A462EF8-DAB6-4B98-BA1B-FBD957A1737C    5842E524-8C5D-4DEE-A553-0857FB9C12F4
 B25DA064-8DAF-4EEB-8894-D540B3A929EA   3A462EF8-DAB6-4B98-BA1B-FBD957A1737C    C2B0AA1A-E688-4E2C-BD5F-16309C496774

So, the 3A462EF8-DAB6-4B98-BA1B-FBD957A1737C means Countries, while 5842E524-8C5D-4DEE-A553-0857FB9C12F4 and C2B0AA1A-E688-4E2C-BD5F-16309C496774 are referring records in Counries table.

We are having many relations here - for example, users to languages, companies, etc. And because working with GUID in order to join the tables is not user friendly, we have indexed view for each relation like this:

CREATE VIEW [dbo].[users_countries]
WITH SCHEMABINDING
AS
SELECT [RecordGUID]
      ,[DestinationEntity]
      ,[DestinationRecordGUID]
FROM (the relationship table)
WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C';

as a result one can easily make a join with this view to the counties table and query it - also, because of using the view (with no expand) and having a index on it, not all records from the original table are read (it's faster).

The system may have hundreds of indexed views and I am worried about the cost of the engine to manage them, compare to filtered indexes on the original table. I mean instead having a view just to create index like this:

CREATE INDEX (index_Name) ON (the relationship table)
(
    [RecordGUID]
   ,[DestinationRecordGUID]
)
WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C'

We are not able to query in a friendly way the table anymore, but will this affect the performance?

I was not able to find any topics about indexed view hurting the performance - but facing such design makes me doubting.

I am wondering can we say that the maintenance of the index of a view and the same index on a table (like the above) is the same?

That design is terrible.  You should have separate tables and real foreign keys.  For a quick fix, use the indexed views.  If there are perf problems, fix the design.


A Dynamic where clause in MS SQL Server The Problem

My SQL Server database contains a view that looks like the following:

+-------------+-----------+------+-------+------+------+
| CAR_ID      | MAKE      |COLOR | Miles | Seats| MODEL|
+-------------+-----------+------+-------+------+------+
| 1           | FORD      | RED  | 100   | 4    | CAR  |
| 2           | TOYOTA    | RED  | 500   | 2    | TRK  |
| 3           | FORD      | BLACK| 10    | 4    | CAR  |
| 4           | HYUNDAI   | BLUE | 150   | 4    | VAN  |
| ...         | ...       | ...  | ...   | ...  | ...  |
| 1000        | TOYOTA    | GRN  | 200   | 8    | CAN  |
+-------------+-----------+------+-------+------+------+

I would like to create custom groupings from this table. My desired output might look like the following-

+-------------+-----------+
| Group       | sum(Miles)|
+-------------+-----------+
| Red Cars    | 500       |
| Red Toyota  | 325       |
| Trucks      | 563       |
| Black Car   | 952       |
| ...         | ...       |
| Black Ford  | 100       |
+-------------+-----------+

Indeed, it looks like a simple "GROUP BY" query. However, in my case, the groups can become rather complex with many logical statements and the number of groups is also very large which makes a "group by" query intractable for me to use. My solution has been to use the WHERE clause to create custom groups. Some may call this Dynamic SQL.

My Solution

I have a created a C# script which I use to store the group definitions and the query itself. The group definitions are stored in a dictionary. I iterate through the dictionary, passing each grouping to the WHERE clause of a select statement, and then I send the SQL statement to the SQL server.

The server returns a Datatable with my custom grouping to my C# application. I then Bulk Insert the Datatable into another table on the SQL server. If I have 50 groupings, I query the SQL server 50 times and bulk insert 50 times. Here is an example:

Dictonary x = {GRP1, (COLOR = BLACK or Miles <50),
               GRP2, (Seats <5 AND MODEL = CAR)};

FOREACH(key in x)
{
  y = "SELECT * FROM CAR_TABLE WHERE " + x[key]
  Datatable dt = QueryServer(y);
  BulkInsertToDB(dt);
}

Question

There must be a better and more efficient way of doing this. I was thinking about throwing my query into a stored procedure on the server, but I would still have to make calls to the stored procedure for each grouping. I then thought perhaps there is a way to store these groupings server size and run all of the queries concurrently in a stored procedure. Any guidance would be helpful.

Clarifications:

One row could belong to many groups. I have sum of miles and sum of age of vehicles. I would use the key of the dictionary as the group name and pass the key to the SQL Server. I would update the datatable in c# with the group name.

There are a number of ways to do this purely in SQL. They will, logically enough, also require dynamic SQL.

First, copy your dictionary into a table in SQL Server - I'll refer to it as Group_Def, with columns Group_Name and Where_Clause.

Option 1: replicate your logic in SQL. For each group, generate a series of SQL statements (in a variable) that will add the group name and the CAR_ID for all cars matching your criteria to a temporary table. Execute the SQL statements in the variable to populate the temp table. Then, join that temporary table to CAR_TABLE on CAR_ID, group by Group_Name, and apply your aggregates.

Option 2: Grab the aggregate values as you go. This time, instead of writing all the matching CAR_ID values into the temp table, write the actual aggregate values. Then, simply select from the temp table.

May be easier to follow if you see the code:

/*
 1           | FORD      | RED  | 100   | 4    | CAR  |
| 2           | TOYOTA    | RED  | 500   | 2    | TRK  |
| 3           | FORD      | BLACK| 10    | 4    | CAR  |
| 4           | HYUNDAI   | BLUE | 150   | 4    | VAN  |
| ...         | ...       | ...  | ...   | ...  | ...  |
| 1000        | TOYOTA    | GRN  | 200   | 8    | CAN  |
*/

IF (OBJECT_ID('tempdb..#CAR_TABLE') IS NOT NULL) DROP TABLE #CAR_TABLE;
CREATE TABLE #CAR_TABLE
 ( CAR_ID int IDENTITY(1,1)
  ,Make   varchar(50)
  ,Color  varchar(50)
  ,Miles  int
  ,Seats  int
  ,Model  varchar(50)
);

INSERT INTO #CAR_TABLE (Make, Color, Miles, Seats, Model)
VALUES ('FORD', 'RED', 100, 4, 'CAR')
      ,('TOYOTA', 'RED', 500, 2, 'TRK')
      ,('FORD', 'BLACK', 10, 4, 'CAR')
      ,('HYUNDAI', 'BLUE', 150, 4, 'VAN')
      ,('TOYOTA', 'GRN', 200, 8, 'CAN')
;

/*
Dictonary x = {GRP1, (COLOR = BLACK or Miles <50),
               GRP2, (Seats <5 AND MODEL = CAR)};

*/

IF (OBJECT_ID('tempdb..#Group_Def') IS NOT NULL) DROP TABLE #Group_Def;
CREATE TABLE #Group_Def (Group_Name nvarchar(128), Where_Clause nvarchar(2000));

INSERT INTO #Group_Def
VALUES (N'GRP1', N'(Color = ''BLACK'' OR Miles < 50)')
      ,(N'GRP2', N'(Seats < 5 AND Model = ''CAR'')')
      ,(N'Red Cars', N'(Color = ''RED'' AND Model = ''CAR'')')
      ,(N'Red Toyotas', N'(color = ''RED'' AND Make = ''TOYOTA'')')
      ,(N'All', N'(1 = 1)')
;

--OPTION 1

IF (OBJECT_ID('tempdb..#cars_by_group') IS NOT NULL) DROP TABLE #cars_by_group;
CREATE TABLE #cars_by_group (Group_Name nvarchar(128), CAR_ID int);

DECLARE @sqlcmd nvarchar(MAX);

SELECT @sqlcmd = N'INSERT INTO #cars_by_group
SELECT NULL, NULL FROM #Group_Def WHERE 1 = 0
';

SELECT @sqlcmd = @sqlcmd + N'UNION ALL
SELECT N''' + Group_Name + N''', CAR_ID FROM #CAR_TABLE WHERE ' + Where_Clause + N'
'
  FROM #Group_Def
;

EXECUTE sp_executesql @sqlcmd;

SELECT t.Group_Name
      ,SUM(c.Miles) as sum_miles
  FROM #cars_by_group t INNER JOIN #CAR_TABLE c ON (t.CAR_ID = c.CAR_ID)
 GROUP BY t.Group_Name
;

--OPTION 2:

IF (OBJECT_ID('tempdb..#group_totals') IS NOT NULL) DROP TABLE #group_totals;
CREATE TABLE #group_totals (Group_Name nvarchar(128), sum_miles int);

DECLARE @sqlcmd2 nvarchar(MAX);

SELECT @sqlcmd2 = N'INSERT INTO #group_totals
SELECT NULL, NULL FROM #Group_Def WHERE 1 = 0
';

SELECT @sqlcmd2 = @sqlcmd2 + N'UNION ALL
SELECT N''' + Group_Name + N''', SUM(Miles) FROM #CAR_TABLE WHERE ' + Where_Clause + N'
'
  FROM #Group_Def
;

EXECUTE sp_executesql @sqlcmd2;

SELECT *
  FROM #group_totals
;

Option 2 may be slightly faster, as you aren't having to calculate the aggregates in the final SELECT. Note, however, that even though I've built @sqlcmd as a single SQL statement, the UNION ALL is joining together functionally separate queries. Performance-wise, you could rewrite this so that each row in #Group_Def was generating a distinct INSERT statement, and performance would not change significantly. You're basically running one statement for each group, regardless.

I can envision a third option that would collect all the aggregates in a single step; however, it's not as scalable, and requires that all your aggregates can be expressed in terms of sums or counts, and still ultimately requires a SELECT for each group.

You would build a single SELECT statement, with columns for each value you want to record for each group. You'd want to add a version of the group name without spaces. To collect the aggregates, you would use SUM and CASE statements.  For example:

SELECT SUM(CASE WHEN (Color = 'RED' and Make = 'TOYOTA' THEN 1 ELSE 0 END) as red_toyotas_count
      ,SUM(CASE WHEN (Color = 'RED' and Make = 'TOYOTA' THEN Miles ELSE 0 END) as red_toyotas_sum_miles
...
  INTO #single_row_total
  FROM CAR_TABLE

(No GROUP BY would be necessary - all output columns would be aggregates).

Then, you build the SELECT statements to pull all the values you need out of the #single_row_total table (again, with dynamic SQL, going through the Group_Def table to build the statement), using UNION ALL to tie them together:

SELECT 'Red Toyotas' as [Group], red_toyotas_sum_miles as sum_miles, red_toyotas_sum_miles * 1.00 / red_toyotas_count as avg_miles FROM #single_row_total
UNION ALL
SELECT 'Red Cars' as [Group], red_cars_sum_miles as sum_miles, red_cars_sum_miles * 1.00 / red_cars_count as avg_miles FROM #single_row_total
UNION ALL
...

This would allow all the aggregates to be collected in a single set-based operation. As noted, breaking the single result into separate rows still requires one SELECT per group; however, these selects, being on a single row, should be quite quick.

This isn't scalable because there's an upper limit to the number of columns you can have in a single table, so a sufficient number of groups, times a sufficient number of aggregates needed per group, could lead to too many columns needed.

Also, while the query pulling the aggregates is a single SQL query, the process of evaluating each row to determine what the results of the CASE statement would be could cause the execution plan to be so hideous, that the one SELECT per group options would perform as well or better.

So, I leave the creation of Option 3 (as well as testing with much larger data sets, to determine the fastest option) up to the OP, or another interested party.

As to the dynamic SQL aspect of this: unless the groups can be defined on an ad hoc basis, you're building your SQL statements with no user-supplied values, so there's no risk of SQL injection. And (I think), if #Group_Def doesn't change, the plan generated might wind up being reusable (not sure, not planning on checking; wasn't part of the original question).


Cassandra in multiple data center - EC2MultiRegionSnitch I was reading article from : http://www.datastax.com/docs/1.0/cluster_architecture/replication#networktopologystrategy and I have a question about - EC2MultiRegionSnitch. I did not understand it well. According to article: if application requests data from node, but that node does not have data, but data is in another node in other data center, will application be able to get it?

In regard to a standard implementation of Cassandra the answer is Yes. So everything in the cluster regardless of the data center would have access.

From the Apache Cassandra site:

  Cassandra's support for replicating across multiple datacenters is
  best-in-class, providing lower latency for your users and the peace of
  mind of knowing that you can survive regional outages.

The site you're referencing is talking about a service they perform called partitioning where they're actually distributing data in a different way based on their partitioning scheme, while still using Cassandra as the database. They do replication based on NetworkTopologyStrategy which depends on the network and the number of datacenters.

In regard to Amazon EC2
They're mixing lingo a little bit which is where the confusion is coming in.

  ... on Amazon EC2 where all nodes in the cluster are within a single
  region. The region is treated as the data center and the availability
  zones are treated as racks within the data center.

So on Amazon's network a region will contain multiple data centers, but they're distributing info like it were local to a data center (probably because their network has plenty of resources and nodes). This document here goes more into the availability zones concept for their network. Basically their Network Topology is a little larger than most networks.

The data should remain in what they're considering a data center, but whether this is the same physical data center or not would depend on the number of racks, the types of machines they're running and the backbones (OC-3,OC-12,OC-48, etc lines) going between them. If you're in a region where they can't have replication across multiple data centers as quickly they'll likely have more redundancy in the physical data centers themselves.


Adding a group of rows in postgresql based on column id I have a table with a sets of rows such as displayed below:

Qty       Equip_ID
21487.92;"Load Center-6"
99272.59;"Load Center-10"
68088.61;"Load Center-11"
21821.5;;"Load Center-6"
102165.2;"Load Center-10"
72973.88;"Load Center-11"
21759.25;"Load Center-6"
102176.9;"Load Center-10"
63559.5;"Load Center-11"

How would I perform an addition operation and get the total of Load Center-6, 10 and 11?
That way I can get the total for each group (6, 10, 11)

I tried using this sql statement based from the answer on this stackoverflow post
https://stackoverflow.com/questions/9600587/how-to-sum-multiple-lines-in-sql
but I get an sql error since WHERE condition can't be placed inside an SUM operation.

SELECT SUM(qty WHERE (equip_id = 'Load Center-6' AND equip_id = 'Load Center-10' AND equip_id = 'Load Center-11'))
FROM td_m2_fact GROUP BY facility_code, period_start;

Trying the SQL statement:

SELECT SUM(qty),Equip_ID FROM table GROUP BY Equip_id

Results:
Qty            Equip_ID
672882459.189999;"Load Center-10"
124347378.953;"Load Center-6"
298342277.620001;"Load Center-11"

What I want:

Qty                                ID
SUM(21487.92,99272.59,68088.61)    Load Center-6,Load Center-10,Load Center-11
SUM(21821.5,102165.2,72973.88)     Load Center-6,Load Center-10,Load Center-11

There is no natural order in a table. You need some indication which rows should go together. As discussed, a group_id per row could do the job.

Then the query becomes simple:

SELECT group_id, SUM(qty) AS qty, string_agg(equip_id, ', ') AS id
FROM   tbl
GROUP  BY 1;

If you already have your table and imported all the data, there is a trick. As long as you have not done anything to the table, yet, especially not updated or deleted any rows, chances are, the physical order of rows is still in the sequence like they were imported.

You could (ab)use the system column ctid as a poor man's id to indicate this sort order and build on this. Assuming there are exactly 3 rows per group:

SELECT (rn + 2)/3, SUM(qty) AS qty, string_agg(equip_id, ', ') AS id
FROM   (SELECT *, row_number() OVER (ORDER BY ctid) AS rn FROM tbl) t
GROUP  BY 1


Postgres update with replace I am doing  select which is:

SELECT replace(field_1, E'

publicidade

', '') FROM test

An that returns what I want, but when I try to update doing the same thing, It doesn't work as expected:

UPDATE test SET field_1 = replace(field_1, E'

publicidade

', '')

It doesn't update the column without the

publicidade

If the SELECT works as expected, so should your UPDATE. More: it should update all columns, which is most probably not what you want.

UPDATE test
SET    field_1 = replace(field_1, $$

publicidade

$$, '')
WHERE  field_1              LIKE $$%

publicidade

%$$

I added a WHERE clause to avoid expensive and pointless empty updates.

Related:

Insert text with single quotes in PostgreSQL
How do I (or can I) SELECT DISTINCT on multiple columns?


Extract from DB2 to SQL Server - where is the data in between the two servers? I am trying to understand what happens when I extract data from DB2 from a SSIS package and load it into SQL Server.
My question is: where is the data when it is between DB2 and SQL Server? On my personal computer? I mean does the data "pass through" the memory on my personal laptop and then gets sent to SQL Server?

It depends where the SSIS Package is running, but yes the machine or server it's running on is a passthrough between your DB2 server and your SQL server. So the data is loaded into memory of the machine where the SSIS Package is running on, and if you're using certain data transformations and variables, those consume additional memory on the SSIS server also.
This article goes a little more in depth and has good information on the memory consumption of SSIS.


Cycles or multiple cascade paths with on delete set null: really? I'm asking this question to check if my reasoning about cascaded delete is correct and if I'm not overlooking anything. I understand the behavior and I'm not asking why it is implemented with the current restrictions.

When we try to create a table containing a self reference like this:

CREATE TABLE [BlogComments] (
    [Id] int NOT NULL IDENTITY,
    [AuthorName] nvarchar(100) NULL,
    [Content] nvarchar(max) NULL,
    [CreatedTime] datetime2 NOT NULL,
    [ReplyToId] int NULL,
    CONSTRAINT [PK_BlogComments] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_BlogComments_BlogComments_ReplyToId] FOREIGN KEY ([ReplyToId])
         REFERENCES [BlogComments] ([Id]) ON DELETE SET NULL -- Not: CASCADE
);

We get the infamous exception

  Introducing FOREIGN KEY constraint 'FK_BlogComments_BlogComments_ReplyToId' on table 'BlogComments' may cause cycles or multiple cascade paths.

I understand that the setting ON DELETE SET CASCADE may actually cause a recursive cascade of deletes and, hence, may be dangerous, or time consuming at best. But ON DELETE SET NULL is different: it will only nullify ReplyToId of direct child records, not of their child records.

So am I right in concluding that SQL Server is overly restrictive when the foreign key is defined with ON DELETE SET NULL? Or am I overlooking some good reasons for this restriction in this particular case?

By the way, I'm aware of a school of thought that contends that there shouldn't be any cascade actions anyway. Let's not get into that.

If this error message did not occur, and ON DELETE SET NULL were to set a column to null, any child-rows referencing the old value would no longer have a valid parent row.  Cascading the update to null for all child rows would be required to meet relational integrity requirements.  However, cascading might result in a never-ending loop, which is the bit that is not supported.  This has nothing to do with a self-reference, and is only about the possibility for SQL Server to follow a never-ending loop.

Consider this setup:

DROP TABLE IF EXISTS dbo.CascadeDeletes;
GO

CREATE TABLE dbo.CascadeDeletes
(
    PrimaryID int NOT NULL
        CONSTRAINT PK_CascadeDeletes
        PRIMARY KEY CLUSTERED
    , SubordinateID int NULL
        CONSTRAINT FK_CascadeDeletes_SubID
        FOREIGN KEY
        REFERENCES dbo.CascadeDeletes (PrimaryID)
) ON [PRIMARY];

Insert some sample data; effectively every row is the parent to the next row.

INSERT INTO dbo.CascadeDeletes (PrimaryID, SubordinateID)
VALUES (1, NULL)
    , (2, 1)
    , (3, 2)
    , (4, 3);

Now, update the first row so it's "parent" is the last row:

UPDATE dbo.CascadeDeletes
SET SubordinateID = 4
WHERE PrimaryID = 1;

Consider that a recursive CTE on this table would enter an endless loop:

;WITH rCTE AS
(
    SELECT cd.PrimaryID, cd.SubordinateID
    FROM dbo.CascadeDeletes cd
    WHERE cd.PrimaryID = 1
    UNION ALL
    SELECT ce.PrimaryID, ce.SubordinateID
    FROM dbo.CascadeDeletes ce
        INNER JOIN rCTE ON ce.SubordinateID = rcte.PrimaryID
)
SELECT *
FROM rCTE

Traversing the tree from primary to subordinate is now a never-ending loop, resulting in this error:

  Msg 530, Level 16, State 1, Line 27
  The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

And deleting any row from the table becomes impossible.

DELETE FROM dbo.CascadeDeletes
WHERE dbo.CascadeDeletes.PrimaryID = 1;

  Msg 547, Level 16, State 0, Line 27
  The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_CascadeDeletes_SubID". The conflict occurred in database "tempdb", table "dbo.CascadeDeletes", column 'SubordinateID'.

If SQL Server would allow us to define the table so that ON DELETE SET NULL was in effect, deleting any row would update other rows referenced by the deleted row to NULL.  Quite clearly, this does not produce an "endless" loop because only a single set of rows will ever be updated.  i.e. if the row with PrimaryID = 2 is deleted, the only other row to be cascade-updated would be the row with PrimaryID = 3.  No endless loop.

Even though in our simple example, there is no endless loop that could be encountered, I expect the CREATE TABLE and ALTER TABLE code-paths generate an error 1785 for every ON UPDATE or ON DELETE action, whenever the pattern looks like it could be cyclical, that is not NO ACTION, simply to err on the side of caution.

One might consider this a bug.

However, luckily, it is pretty easy to work around this bug with the "old way" of enforcing RI; that is with a trigger:

CREATE TRIGGER CascadeDeletes_OnDeleteSetNull
ON dbo.CascadeDeletes
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.CascadeDeletes
    SET SubordinateID = NULL
    FROM dbo.CascadeDeletes cd
        INNER JOIN deleted d ON cd.SubordinateID = d.PrimaryID;
    DELETE
    FROM dbo.CascadeDeletes
    FROM dbo.CascadeDeletes cd
        INNER JOIN deleted d ON cd.PrimaryID = d.PrimaryID;
END

Now, when you delete a row from our table, as in:

DELETE FROM dbo.CascadeDeletes
WHERE dbo.CascadeDeletes.PrimaryID = 1;

The row is deleted, and related rows have their SubordinateID set to NULL:

╔═══════════╦═══════════════╗
║ PrimaryID ║ SubordinateID ║
╠═══════════╬═══════════════╣
║         2 ║ NULL          ║
║         3 ║ 2             ║
║         4 ║ 3             ║
╚═══════════╩═══════════════╝


Why SqlBulkCopy gets slower by table's row count? I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records count become more than 20 millions, the insertion time becomes 2.5 seconds per 10K records. And this time increases with the count of records.

I have only a simple table, with no indexes except an identity clustered primary key.

I thought that this behavior is an append-only behavior and won't be affected by the table size. Like throwing a pile of litter on yet a huge pile of litter. That shouldn't be affected by the size.

What do I miss here?

Update: Table's script created via SSMS is:

CREATE TABLE [pardis].[DeliveryReports](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [PardisId] [bigint] NOT NULL,
    [Status] [bigint] NOT NULL,
    [PartId] [bigint] NOT NULL,
    [ShortCode] [varchar](100) NOT NULL,
    [PhoneNumber] [varchar](100) NOT NULL,
    [DateTimeReceived] [datetime] NOT NULL,
    [PersianDateTimeReceived] [varchar](100) NOT NULL,
    [DateTimeSentToCp] [datetime] NULL,
    [PersianDateTimeSentToCp] [varchar](100) NULL,
    [DeliveringToCpResult] [nvarchar](max) NULL,
    [IsProcessing] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsProcessing_1]  DEFAULT ((0)),
    [IsSentToCp] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsSentToCp]  DEFAULT ((0)),
 CONSTRAINT [PK_DeliveryReports] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Update 2: This is the execution plan for inserting 1 record via SSMS. As you can see, no hidden cost exists in inserting records in the table. Please note that this execution plan is after deleting the primary key index to prevent any index from affecting the performance.

Why you write that you don't make any index ?
What is PK_DeliveryReports in your opinion ?
As for me - the clu of your problem is just rebuilding of PK.
Try to use partitions to your table - or drop this index (PK) and rebuild it after the load - should be quicker (of course if you don't need index during the load for other purposes).


Importing MySQL tables from other database in live site with mysqldump can cause trouble? Scenario: I want to replicate MySQL tables from one database to other database.

Possible best solution: May be to use MySQL Replication feature.
Current solution on what I'm working as workaround (mysqldump) because can't spend time to learn about Replication in current deadline.

So currently I'm using command like this:

mysqldump -u user1 -ppassword1 --single-transaction SourceDb TblName | mysql -u user2 -ppassword2 DestinationDB

Based on some tests, it seems to be working fine.
While running above command, I run ab command with 1000 requests on destination site and tried accessing the site from browser also.

My concern is for destination live site on which we are importing data with whole table (which will internally drop existing table and create new one with new data).

Can I be sure that live site won't break while this process or is there any risk factor?
If yes then can that be resolved?

If they are on the same server, I'd do the table 'copy/replicate' this way:

CREATE TABLE detinationDB.new_table like sourceDB.TblName;
INSERT INTO detinationDB.new_table SELECT * FROM sourceDB.TblName;
RENAME TABLE detinationDB.TblName TO detinationDB.DropMe, detinationDB.new_table TO detinationDB.TblName;
DROP TABLE detinationDB.DropMe;

This way, you avoid dropping the old table before making sure the new table is there.

This is good if you intend to keep booth DBs on the same server.


Designing a Tournament Schema I am designing a Tournament Schema for Scrabble tournaments, including Leagues and Rounds for each Tournament. The following is the initial schema design we have.

A Tournament has many leagues, a league has many rounds, and a round has many games. What we can't work out is where we store the players without generating many-to-many relationships.

We're looking for ways in which we can improve the design from here as I'm not sure this is going to work as is.

Looking at the schema provided, what is the purpose of the PlayerID link between League and Player? If this is to determine if a player is participating in that league you would need to duplicate each League record for every player in that league which would break your primary key. In order to determine if a player is part of a League you would need to join from League through Round and Game to Player.

I believe this link can be removed and the PlayerID column in League dropped. This will resolve any many-to-many relationship issues that I can see.

Also you are storing the players name in both Player and Game, is this required for history or can this be normalised and use the data in the Player table throughout the implementation?


What is the purpose of session pool_mode in pgbouncer? Since in this mode pgbouncer creates outbound connection for each inbound connection it seems to me that pgbouncer doesn't work as pooler.
It's just 1 to 1 mapping.

Where am I wrong?

There would be 1 connection to the server for each connection to pgbouncer, but not necessarily a new connection. When a connection to pgbouncer is closed it doesn't close the connection to the server, just makes that connection available to the pool. Then when a new connection comes in, that pooled connection can be reused.

If the connections to pgbouncer are generally long-lived ones, this might not be of much benefit. But, if there are a lot of short connections, it could result in a major improvement.

There was an excellect blog post published recently about pgbouncer, and the different modes of operation.


TimescaleDB database design - should I use inheritance? I have the following TimescaleDB hypertable:
CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data1 int NOT NULL,
    event_data2 int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    <unique index on event_time, pair_id, entry_id>
)

<Some continuous aggregates>

But when querying this data I would actually never need to just get data across pair_id's such as:
SELECT * FROM data WHERE <some condition on the time>
Instead I would like to query the data such that I get the data joined on the event_time for each pair_id - something like this:

event_time
pair_id1_event_data1
pair_id1_event_data2
pair_id2_event_data1
pair_id2_event_data2
...

Or:
|event_time | pair_id1_continous_agregate1 | pair_id1_continous_agregate2 | pair_id2_continous_agregate1 | pair_id2_continous_agregate2 | ... |
| --- | --- | --- | --- | --- | --- |
Sorry for not writing the actual query, I'm still learning how to do this.
Given I have 1000s of pair_ids, does this database design make sense to have efficient query performance?
The alternative I am considering is to use inheritance like this https://www.postgresql.org/docs/current/ddl-inherit.html:\
I have about 5 types of data stored in this table, some of them have an extra column or two

Create a parent table
For each data_type create a data_type_table inheriting from the parent table
For each pair_id create a table inheriting from the appropriate data_type_table

Questions

A) Is this type of inheritance even supported in TimescaleDB?
B) Would this improve my query performance?
C) Is there another alternative which would be better?

A) No, I don't believe that type of inheritance is supported in
Timescale.
B) This won't help query performance at all, it'd probably
hurt it significantly and be a big mess.
C) I'd recommend just having
each data type as a column in the table and for each pair id filling
in the correct data type. Then you can store which pair ids use which
data types in a second table, and when you're constructing your query
you grab the correct column. You can also do the correct continuous
aggregates, grouped by pair id, with the correct aggregation for each
data type, which should mean only one continuous aggregate.

Finally, you can aggregate and then join like that, but probably not for all of the thousands of columns, there'll be some limit where that gets quite inefficient and also very difficult to deal with...are you actually using thousands of columns in a single calculation? And is it really more efficient to have them in row form? You might consider using array_agg(row(pair_id, data) ORDER BY pair_id) GROUP BY time or the like  to do something like what you want. you could also use JSON or some other thing as your return type if that's better, but I would also see if you can limit your queries to say 10s or hundreds of columns. Or split the whole thing into real tables with related columns and use the composite types to fix some of this...


Difference between return next and return record What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?

While RETURN NEXT is valid plpgsql syntax, RETURN RECORD is non-existent as such. It corresponds to how you declare the return type in the function header. Read the manual about how to return from a function.

In a function declaring RETURNS int you can:

RETURN 1;

For RETURNS SETOF int you can:

RETURN NEXT 1;

If you declare OUT parameters, you can just:

RETURN;
-- or do nothing: OUT params are returned at the end of the function anyway

Or if you combine OUT parameters with RETURNS SETOF record - effectively the same as RETURNS TABLE (...):

RETURN NEXT;

The current state of all OUT parameters is returned as next row.

Finally, to return a whole set of rows at once:

RETURN QUERY ...

record is a special data type. It's meaning depends on where you use it. Like I posted in my answer to your previous question:

Declare variable of table type in PL/pgSQL

The manual about the record type in PL/pgSQL.


What is the difference between text and pg_catalog.text? I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3).

In order to keep the schemas aligned, I have a periodic schema diff job, which notifies me when the schemas differ.

A few days ago, I started getting many of these diffs:

CREATE TABLE ...
...
<     title pg_catalog.text,
<     subtitle pg_catalog.text,
---
>     title text,
>     subtitle text,

The text fields in staging was somehow converted to pg_catalog.text in prod.

What is the difference between text and pg_catalog.text? How can I equate the two schemas?

They are the same thing in a default installation.

pg_catalog.text is the schema-qualified version of the type name text.

The search_path always contains pg_catalog automatically. Per documentation:

  In addition to public and user-created schemas, each database contains
  a pg_catalog schema, which contains the system tables and all the
  built-in data types, functions, and operators. pg_catalog is always
  effectively part of the search path. If it is not named explicitly in
  the path then it is implicitly searched before searching the path's
  schemas. This ensures that built-in names will always be findable.
  However, you can explicitly place pg_catalog at the end of your search
  path if you prefer to have user-defined names override built-in names.

Bold emphasis mine. If you do such a thing, Postgres will automatically start schema-qualifying names in text-representation, that would otherwise be resolved differently. That's most probably what you see. Check your current search_path with:

SHOW search_path;

Closely related:

How can I fake inet_client_addr() for unit tests in PostgreSQL?
How to create table inside specific schema by default in Postgres?


Question about new behavior of current connection combobox in SQL Server 2012 I have a question about the new behavior of the database selection combobox in SQL Server 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL Server 2008 R2, clicking in the text of the database selected (as opposed to the dropdown arrow) selects the text in the combobox rather than activating the dropdown function.

I find this sort of a nuisance, since the new intellisense isn't active in the combobox. I don't actually want to type out the name of the database since most of the databases have the same prefix in their name; that would be a lot of unnecessary typing.

As such, I would prefer if clicking the text of the combobox would activate the dropdown rather than have it be available for text input. Does anyone know how I can do this?

Also, if I use the Control + U keyboard shortcut to put the focus on the combobox, is there any key I can press to activate the dropdown so I can navigate to the available databases that way? I know pressing up and down will switch from one to another, but is there anyway to actually bring up the list?

Can anyone recommend any options to alter the behavior of the combo box to make it more useful? I was considering using SSMSBoost; does anyone have any experience with it?

I would appreciate any advice anyone can provide.

Press Ctrl-U (focus the combobox) and then Alt+DownArrow - this will make the combobox drop down.


How can I fake inet_client_addr() for unit tests in PostgreSQL? I have a simple stored procedure whose return value depends on the value of inet_client_addr().  How can I override inet_client_addr() for the purpose of unit tests when testing my stored procedure?

The only solution I've come up with so far is to create a wrapper function around inet_client_addr():

CREATE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT inet_client_addr();
$$ LANGUAGE sql;

Then use that in my function:

CREATE local_connection() RETURNS BOOLEAN AS $$
    SELECT my_inet_client_addr() = '127.0.0.1';
$$ LANGUAGE sql;

Then in my unit test, I can re-define my_inet_client_addr():

BEGIN;
SELECT PLAN(2);
REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT '127.0.0.1'::INET;
$$ LANGUAGE sql;

is(local_connection(),TRUE,'Connection from 127.0.0.1 is local');

REPLACE FUNCTION my_inet_client_addr() RETURNS INET AS $$
    SELECT '192.168.1.1'::INET;
$$ LANGUAGE sql;

is(local_connection(),FALSE,'Connection from 192.168.1.1. is not local');

ROLLBACK;

Is there any way to accomplish the same without the wrapper function my_inet_client_addr()?

inet_client_addr() is a system information function.
It is located in the schema pg_catalog like other built-in functions (except for additional modules).

pg_catalog is automatically part of the search_path. Per documentation:

  In addition to public and user-created schemas, each database contains
  a pg_catalog schema, which contains the system tables and all the
  built-in data types, functions, and operators. pg_catalog is always
  effectively part of the search path. If it is not named explicitly in
  the path then it is implicitly searched before searching the path's
  schemas. This ensures that built-in names will always be findable.
  However, you can explicitly place pg_catalog at the end of your search
  path if you prefer to have user-defined names override built-in names.

Bold emphasis mine.
So we create a dedicated schema and place it before pg_catalog in the search_path:

CREATE SCHEMA override;

CREATE OR REPLACE FUNCTION override.inet_client_addr()
  RETURNS inet AS
$func$
SELECT '127.0.0.1'::inet
$func$ language sql STABLE;

SET search_path = override, pg_catalog, public;

Then your call finds your custom override-function first:

SELECT inet_client_addr();

SQL Fiddle

Make sure, unprivileged users cannot create objects in the override schema, or they can play all kinds of tricks on you. That's not the case by default. Per documentation:

  No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

Bold emphasis mine.

Care is needed if the same user should be allowed to create objects in the database.
Per documentation:

  The first schema named in the search path is called the current
  schema. Aside from being the first schema searched, it is also the
  schema in which new tables will be created if the CREATE TABLE command
  does not specify a schema name.

Always specify a schema name for CREATE commands, and disallow crating object to all by default to rule out mistakes.


SQL Server 2014, Volumes in Resource Governor I'm looking at Resource Governor on 2014 and how it relates to volumes.  I can see where to set up MAX|MIN IOPS PER VOLUME on a resource pool.  That's not keyed by volume, however sys.dm_resource_governor_resource_pool_volumes is.  Should I read that as the pool's definition applies to every volume but the DMV reports what actually happened on each?

My understanding is this: say I have three volumes - E, F and G. I set min = 10, max = 300 on the pool.  Then there can be, at most, 300 IOPS against each drive for a max of 900 for the pool. The DMV shows what IO actually happened for each volume.  If the table/filegroup definition were such that the majority of the activity happened on one volume (let's say F) the numbers from the DMV would show large read_bytes_total and write_bytes_total for that volume and smaller numbers for the others.  Is this a correct understanding?

Neither BoL nor any of the blogs I've read make this explicit.

It seems reasonable to assume, per the documentation and indeed the names of the parameters themselves, that MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME control the maximum quantity of I/Os per second per volume.
i.e. as in your question, if you have three volumes in the resource pool, and set MAX_IOPS_PER_VOLUME to 300, then you'd have a maximum total IOPS of 900.
Without source-code-level access to SQL Server, it's impossible to say for certain if that's how it actually works, however the documentation says (emphasis mine):

MINIMUM_IOPS_PER_SECOND:
Specifies the minimum I/O operations per second (IOPS) per disk volume to allow for the resource pool.
MAXIMUM_IOPS_PER_SECOND:
Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool.


Return table based on 1, 2, 3, 4, 5 or 0 selected search options Scenario: Lets say I have several tables for a software ticketing system: Developer, Supervisor, TicketType, TicketStatus and a Ticket table to join them on. Assume I have done my inner joins and now have a complete table listing all assigned Ticket info.

Id|TicketName|TicketStatus|Developer|Supervisor|DateAssigned| DateDue  |
------------------------------------------------------------------------------
         1  |  Report  |   Open     |   Josh  |    Bob   | 20130702   | 20130705
         2  |   App    |   Pending  |   Brian |    Bob   | 20130701   | 20130802
         3  |   App    |   Closed   |   Brian | Hannibal | 20130701   | 2013----

Obviously the SQL for this is pretty basic:

SELECT TicketId, TicketName, TicketStatus ...
FROM Ticket INNER JOIN TicketType ON [Ticket].TicketID = [TicketType].TicketName
     INNER JOIN [Status] ON .... ....

So that's the DEFAULT static portion of the report. This is what initializes first, however the client, customer, manager, etc. wants the option of selecting zero, one, or more fields to sort data on for all fields in the report. For example:

User may choose 'app' from the TicketName category, 'Josh' as Developer, and BETWEEN date1 AND date2, while TicketStatus Remains NULL or '  '. The table should display all 'app' tickets assigned to 'Josh' between selected dates, while not breaking due to TicketStatus not being selected. Any ticket status should appear.

User could choose not to select any values, in which case the report should default to the example above, without breaking. Meaning I cannot use NULL as a place holder bacause it would cause the initial report to not initialize (unless I did something wrong?)

User can basically mix and match any variation of the fields and the table should sort on those selected fields.

Question: How can someone return a table based on all or no options     being selected, without breaking the query?

Additional Info:

Using SQL Server 2008 r2, Business Intelligence Development Studio, T-SQL.

I have tried declaring variables and using LIKE statements, but this requires the user to select an option before the report will return anything. The User may not know what the field values are from memory.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25)
                                 ,@---Name  varchar(25)
                                 .........
                                 ......

FROM [Ticket]
INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID
INNER JOIN dbo.[Status] ON [Ticket].Stat ....
.....

WHERE [TicketType].Name LIKE @Category
   AND [Ticket].Name LIKE @ReportName
   AND [Status].StatusName LIKE @Status
   AND [Phase].PhaseName LIKE @PhaseName
   AND (BA.FullName = @Name1 OR AD.FullName = @Name1)

Dynamic SQL is another option.  I would try both this method and @a1ex07's method and see which runs faster for you.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25)
                                 ,@---Name  varchar(25)
                                 .........
                                 ......

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * ' + CHAR(13) +
        'FROM [Ticket]  ' + CHAR(13) +
        'INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID  ' + CHAR(13) +
        'INNER JOIN dbo.[Status] ON [Ticket].Stat .... ' + CHAR(13) +
        '..... ' + CHAR(13) +
        ' ' + CHAR(13) +
        ' ' + CHAR(13) +
        'WHERE 1=1 '

IF @Category IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [TicketType].Name LIKE @Category '

IF @ReportName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Ticket].Name LIKE @ReportName  '

IF @Status IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Status].StatusName LIKE @Status '

IF @PhaseName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Phase].PhaseName LIKE @PhaseName  '

IF @Name1 IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND (BA.FullName = @Name1 OR AD.FullName = @Name1) '

EXEC sp_executesql @sql, N'@Category varchar(25), @ReportName varchar(25), @Status varchar(25), @PhaseName varchar(25), @Name1 varchar(25)',
                @Category, @ReportName, @Status, @PhaseName, @Name1


Did a SQL Server instance ever participate in replication? How can I tell whether a SQL Server instance ever had replication in use or not? I'm debugging an issue with a 3rd party software. This software (Red Gate SQL Backup) reports the distribution database to be missing. The company has asked to find out whether this server has ever used replication or not. How can I find out?

Replication does not seem to be installed and the distribution database is not present. None of the databases use replication.

I'm not sure if you'll be able to reliable tell if the server has ever used replication or not.  For example, if someone drops replication properly, there will be no trace left behind.


How to send two query into one? Is there any other alternative? So I have this query that gets me the number of failed parts in the order. for example 94 failed. However, I want to see the total amount of parts in that order. For example, 94 failed and 200 total. How can I achieve that in one query? If it's not possible how can I combine two queries in one result table?

SELECT status, COUNT(order)
FROM result
WHERE status='failed' and order = '4373'
GROUP BY status

For MySQL (as the question tagged):

SELECT SUM(status='failed') AS failed_orders, COUNT(1) AS total_orders
FROM result
WHERE order = '4373'
-- GROUP BY status -- ??

For another DBMS (for example, for Microsoft SQL Server):

SELECT COUNT(CASE WHEN status='failed' THEN 1 END) AS failed_orders, COUNT(1) AS total_orders
FROM result
WHERE order = '4373'


More than 15000 sleeping sessions in SQL Server 2014 The CPU utilization is going to 100% regularly, and when I check SP_who2, it is showing around 20000 sessions, most of them are sleeping (also utilizing the CPU).
I think the CPU load is related to the sleeping sessions

The application name is showing as Microsoft JDBC.

Most of the sessions are showing 'SOS_SCHEDULER_YIELD as last_wait_type. After restarting the Windows Server, the CPU utilzation has come down, but the session count is increasing.

The server has 32 cores.

The application team is telling they are using some 'Connection Pool' for the the connection management.

How can I investigate or solve the number of sleeping sessions?

The application probably is leaking connections. I have no idea what they mean by

  The application team is telling they are using some 'Connection Pool'
  for the the connection management.

But it sounds as they are trying to implement their own connection pooling system.

Generally connection leaking happens when you have calls to  .open() without calling  .close() on the same connection.

Since this is generally a client issue I think the only thing you can do from the database side (short of killing them every once in a while but I wouldn't suggest doing that) is use the information from sys.dm_exec_sessions to find the application leaking the connections and then talk to your developers or vendor so they can look for the offending code.

For example this query (taken from here:

select count(*) as sessions,
     s.host_name,
     s.host_process_id,
     s.program_name,
     db_name(s.database_id) as database_name
from sys.dm_exec_sessions s
where is_user_process = 1
group by host_name, host_process_id, program_name, database_id
order by count(*) desc;

will show you the number of connections per host per process. This should be enough to identify the offending application.

You could also create a job logging this information over time to chart out if the connections are really always increasing so you can "prove" to your application team there really is a leak.


Is it possible to recover from a LOST_EVENTS incident from an RDS read replica? I was setting up a read replica of an RDS database for a reporting project, and issued an incorrect GRANT statement when configuring a user.

My RDS read replica stopped, with an error message on the slave:

The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

It seems that the recovery solution is to:

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

Except that the "root" user for an RDS read replica doesn't have rights to issue the stop slave command.

Is there a way to recover without deleting & recreating the read replica?

In this specific case (a failed grant statement) stopping the slave, skipping the event, and restarting the slave is likely a safe operation... you just have to do it the RDS way.

mysql> CALL mysql.rds_skip_repl_error;

This is one of several stored procedures installed on every RDS instance that partially make up for the lack of SUPER privilege.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html


A Script to insert dummy Data in all tables of Database I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ability to run queries and Test Apps.

There are lots of cases that @AmmarR's solution doesn't handle - ROWVERSION/TIMESTAMP, computed columns, columns with check constraints, foreign keys, UNIQUEIDENTIFIER columns that default to NEWSEQUENTIALID(), date/time columns that default to GETDATE(), sparse columns, etc. etc.

Why reinvent the wheel? There are tools out there that can generate data for you and they've probably already thought about all of these restrictions. For example DTM Data Generator (not free, have not tested it) and Red-Gate SQL Data Generator (not free, have tested it and recommend it - also reviewed here).


Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data.  I know nothing really about InnoDB but have been doing regular MySQL database stuff for a while now.

In the below examples there should be a table named corporate_subscription_flag_list with

A Primary Key: subscription_id
A Foreign Key: sflid

mysqldump shows a table named `corporate_subscription_flag_list, like:

DROP TABLE IF EXISTS 'corporate_subscription_flag_list';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'corporate_subscription_flag_list' (
    'sflid' int(10) unsigned NOT NULL AUTO_INCREMENT
    'subscription_id' int(11) NOT NULL
    'subscription_flag_id' int(11) NOT NULL
    PRIMARY KEY ('subscription_id')
    KEY 'sflid' ('sflid')
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

However sflid only exists in the above table - am I wrong, or is the way it's been done wrong?  sflid isn't a primary key in any other table nor is it a column in any other table in the database.

Looking at the other tables that supposedly use subscription_id as a foreign key to reference corporate_subscription_flag_list it seems, to me, that there is also a problem.

Comments seem to suggest (as does a strange diagram I have found) that corporate_promolist_data (another table) uses subscription_id as a foreign key referencing corporate_subscription_flag_list.

However, unless I'm missing something there is no foreign key being defined, unless KEY 'pludid' ('pludid') is defining a foreign key?

DROP TABLE IF EXISTS 'corporate_promolist_user_data';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'corporate_promolist_user_data' (
    'pludid' int(10) unsigned NOT NULL AUTO_INCREMENT
    'subscription_id' int(11) NOT NULL
    'title' varchar(255) DEFAULT NULL
    'first_name' varchar(255) DEFAULT NULL
    'last_name' varchar(255) DEFAULT NULL
    'email_address' varchar(255) DEFAULT NULL
    'organisation' varchar(255) DEFAULT NULL
    'job_title' varchar(255) DEFAULT NULL
    'department' varchar(255) DEFAULT NULL
    'address_line_1' varchar(255) DEFAULT NULL
    'address_line_2' varchar(255) DEFAULT NULL
    'town_city' varchar(255) DEFAULT NULL
    'county_state' varchar(255) DEFAULT NULL
    'post_code' varchar(255) DEFAULT NULL
    'country_id' varchar(255) DEFAULT NULL
    PRIMARY KEY ('pludid')
    KEY 'pludid' ('pludid')
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

The answer is yes, they are lacking foreign keys, the previous dba was lacking all referential integrity and was using an application stage enforcement of the dba logic (as discussed here: http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html). The previous dba must have been inexperienced and possibly used this method:

  For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table

However as me joining the project has proven - it is not necessarily a good way of doing it.

Given the sensitive nature of this project and the later desire to have all kinds of distributed systems accessing the db it has been decided that the overhead of having foreign key constraints is worth it for security when others may be programming and accessing the db.

Whilst my comment on my question shows that it is possible for mysqldump to strip out important stuff and leave one with a misunderstanding, in this case there was no indexing and no foreign key relationships (someone else showed me, I will include how they found the answer if I can catch and get them to tell me).

From what I've been reading and seeing, Drupal does not really commonly use foreign keys, but that's another story altogether.


Conceptual Design of a data warehouse (declaring the grain) I am trying to design a data warehouse using this example scrubbed data. I need to declare the "grain". I know its the lowest level of detail in the fact table, but I am having a hard time determining it. Any help would be appreciated. (Sorry about the table column alignments - i tried my best to get them aligned)

TABLE: Banks

Bank_ID  Bank_Name  ABA_Code        Bank_City   Bank_State   Recip_Zip
1       Bank of NY  102938475    Albany        NY       12204
2       Bank of NJ  283746591    Englewood  NJ      07631
3       NY Federal  564738290    Albany     NY      12204
4       NJ Federal  123987475    Englewood  NJ      07631
5       NY Credit   654321789    Rochester  NY      14602

Banks consist of one or more accounts.

TABLE: Accounts

Acct_ID Acct_No          Bank_Id    Title
1            102938458  1   Subscriber Payment Acct
2            918273645  1   Provider Payment Acct
3            228374659  2   Zero Dollar Acct
4            374658291  2   BCB Provider Acct
5            234567891  3   BCB Subscriber Acct

Accounts are where the payment checks o electronic payments for drawn from.

TABLE: Recipients

Recip_ID   Recip_Type   Recip_No    Recip_Title   Recip_City    Recip_State   Recip_Zip
1         P          100001 Art Gains     Albany    NY       12204
2         S          220202 Mick Tean     Englewood NJ       07631
3         S          103003 Jake Adam     Binghamton    NY       13905
4         P          404004 Jane Doe         Rochester  NY       14602
5         P          977005 Jac Angel     Belvidere NJ       07823

Recipients (of a payment) can be Providers (P) or Subscribers (S)

TABLE: Payments

Payment_ID  Acct_ID Check_No    Check_Amt   Check_Date  Recip_No
1   1   102938475   6919.70 06/17/2011  100001
2   1   102938476   23.50   09/09/2011  220202
3   2   459287654   3.12    01/21/2012  404004
4   2   459287655   17345.01    07/07/2012  977005
5   3   102938476   300.47  02/11/2013  103003

Payments are master records and are the highest level of transaction. They consist of one or more claims.

TABLE: Claims

Claim_ID    Payment_Id  Claim_Amt   Claim_Date  Claim_Source
1   1   3459.85 06/01/2011  AA
2   1   3459.85 06/15/2011  AA
3   2   23.50   01/02/2012  AA
4   3   100.00  01/28/2012  BB
5   3   200.47  02/01/2013  BB

Claims consist of one or more Line items (services rendered by a provider)

TABLE: Claim Line Items

Item_ID Claim_ID    Start_Date  End_Date    Proc_Code   Proc_Desc   Billed_Amt
1   1   05/01/2011  05/01/2011  30.01   CRM NO  1230.25
2   1   05/01/2011  05/01/2011  30.02   ARB DD  1950.00
3   1   05/01/2011  05/01/2011  30.12   DSJ 01  279.60
4   2   12/15/2011  12/15/2011  99.98   LLL SS  729.95
5   2   12/15/2011  12/15/2011  99.99   IT2 GG  2729.90

Line Items are (services provided) the lowest transactional level detailing the procedure and cost.

To quote Ralph Kimball, "declaring the grain" is:

  saying exactly what a fact table record represents

You need to choose which entity from you list above represents the grain. You could choose any of them, however claim_line_items looks the best candidate to me as this is at the lowest level (it is the only table with no children).

If you make claims your grain, then it won't be possible to report on what the individual line items are. This may be what the business wants to track (and they don't care about line items). However, it'll be very difficult to change the grain to line items at some point in the future if they change their mind. Whereas you can make the grain line items and provide aggregations which show summaries at the claim level.

For a more detailed discussion about this (again by Kimball) go here.


SQL Server column level security I think I need some pointers on security in SQL Server.   I'm trying to restrict some of our end users from getting access to certain columns (i.e. SSN) on a table.

I thought I could just use column level security to restrict access to the columns.  That successfully prevented users from accessing the table directly, but I was surprised that they could still get to those columns through a view that accessed that table.

I followed the tips here: http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/   Those were very helpful, but when I created a view at the end, the intern was able to access that column by default

I've read that views are the best way to accomplish this, but I really don't want to go through and change all of the views and the legacy front-end application.  I would rather just restrict it once on the table and if a view tries to access that column it would just fail.

Is that possible or am I misunderstanding how security works in SQL Server?

Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.


SSAS / MDX Query - Need row total I would like to add an additional row that will display the Total order count for all the different Customers in the Customer Dimension. Below is the query that I have been trying to modify.

SELECT NON EMPTY { [Measures].[Order Count] } ON COLUMNS
    ,NON EMPTY {([Date].[Year].[Year].ALLMEMBERS
       * [Date].[Quarter].[Quarter].ALLMEMBERS
       * [Order].[Customer].[Customer].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION
    ,MEMBER_UNIQUE_NAME ON ROWS FROM [Order] CELL PROPERTIES VALUE

Currently it is displaying as :

                                               | Order Count
Calendar 2012   |Quarter 1, 2012    |D         |31

What i need is below:

                                               | Order Count
Calendar 2012   |Quarter 1, 2012    |D         |31

Total                                          |122011

Try adding the All member into the set on rows:

SELECT
  NON EMPTY
    {[Measures].[Order Count]} ON 0
 ,NON EMPTY
    {
        [Date].[Year].[Year].ALLMEMBERS*
        [Date].[Quarter].[Quarter].ALLMEMBERS*
        {
          [Order].[Customer].[Customer].ALLMEMBERS
         ,[Order].[Customer].[All]
        }
    }
  DIMENSION PROPERTIES
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON 1
FROM [Order]
CELL PROPERTIES VALUE


MySQL use both FULLTEXT and normal index I have a table like this:

    id  type    idElem  name      date  content  lang     state
------  ------  ------  ------  ------  -------  ------  --------

When I run this query against it:

SELECT
  `contents`.*
FROM
  `contents`
WHERE `contents`.type = "files"
  AND `contents`.name = "url"
  AND `contents`.state = 2
  AND MATCH(`contents`.content) AGAINST ("somewords")

Only the FULLTEXT index on content is used, not the one on type, idElem, name, state :

    id  select_type  table     partitions  type      possible_keys  key     key_len  ref       rows  filtered  Extra
------  -----------  --------  ----------  --------  -------------  ------  -------  ------  ------  --------  -------------------------------
     1  SIMPLE       contents  (NULL)      fulltext  type,idx       idx     0        const        1      5.00  Using where; Ft_hints: sorted

Why ?

Here's my table schema (with more than 300k rows):

CREATE TABLE `contents` (
  `id` int(11) NOT NULL,
  `type` varchar(35) NOT NULL,
  `idElem` int(11) NOT NULL,
  `name` varchar(35) NOT NULL,
  `date` int(11) NOT NULL,
  `content` longtext NOT NULL,
  `lang` varchar(3) DEFAULT NULL,
  `state` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `contents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `type` (`type`,`idElem`,`name`,`state`);
ALTER TABLE `contents` ADD FULLTEXT KEY `idx` (`content`);

ALTER TABLE `contents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

MySQL very rarely use more than 1 index per table in a query, and never intersect a FULLTEXT index with any other.

Your plan show - for some reasons it choose FT as best choice for this query.

  Index Merge is not applicable to full-text indexes. We plan to extend
  it to cover these in a future MySQL release.

merge optimisation

edited after question:
You can change index

KEY type (type,idElem,name,state);

to the -

KEY type (type,name,state);

and compare - MySQL could start use it, and depending from Your data it could be better (could be not). If MySQL continue use FT index only, You can make tests with index hints - FORCE INDEX ('type').

Remark for comment from Rick James:
not always, in case of range - yes (at least I have not other examples) and agree with You,

but in case (AND = AND = AND =) as in original question (or search by PK) it could take index for direct search.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `text` varchar(2048) DEFAULT NULL,
  KEY `idx` (`id`,`value`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

populate and simple test:

select * from t1 where (id >2 and id < 4) and MATCH (text) AGAINST ('very')

as predicted by You:

1   SIMPLE  t1      fulltext    idx,text    text    0   const   1   11.11   Using where; Ft_hints: sorted

but:

select * from t1 where id =3  and value = '3' and MATCH (text) AGAINST ('very')

and it take other index:

1   SIMPLE  t1      ref idx,text    idx 262 const,const 1   11.11   Using where

so it always need - make and test


Difference in Size of SQL Server 2008 R2 backup on Local disk and on Remote Network Share I wanted to take backups of all SQL Server 2008 R2 databases on a network share, I already  have a backup schedule for backup on local disk. I have tried two methods:

using SQLBackupAndFTP Software
using SQL Server 2008 R2 maintenance Job

Using both above methods for the backup on network drive, there is hell a lot of difference of the size of data as compared to backup on local disk. Please see details below

Backup of data using SQL Server 2008 R2 Scheduler:

When I took the backup of the data using the SQL Server 2008 R2 scheduler

The size of the data was almost 10.5 GB.
The number of files that were backup were 30 items/files

Backup of data using SQLBackupAndFTP software:

When I took the backup of the data using the SQL Server 2008 R2 scheduler

The size of the data was almost 822 MB.
The number of files that were backup were 28 items/files

I really wonder what has been done, the backup files in both cases have extension .bak and no compression is done.

Backup of data using SQL Sserver 2008 R2 on Local Disk and on Network Share:

I then configured two jobs:

One for backup on the local disk drive
One for the backup on the network share

To my surprise, there is still difference of Size in data and files, please see screen shots below. I really wonder what is happening and why is there so much difference in the size of data.

Any body has any idea, please feel free to comment and share your knowledge.

You say you are "using SQLDATABACKUPANDFTP Software" if this software is from http://sqlbackupandftp.com/ then it says it "Zips, Encrypts and FTPs the database backups" which would explain much of the difference.

I'm afraid your screenshots didn't appear so I can't comment on the other difference.


avg_page_space_used_in_percent and fill factor sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent.

How does this fit in with the fill factor of an index?

For example:

I have index idx_HeaderInfo

I create the index with a fill factor of 80%.

If I then immediately look at avg_page_space_used_in_percent do I see:

100
80

I ask because I need a way of rebuilding or reorganising based on internal fragmentation rather than external and I don't want to create logic to rebuild/reorg if a avg_page_space_used_in_percent < 75 if the fill factor in the first place is 70 for instance.

avg_page_space_used_in_percent does not care about fill factor.

Therefore if you set an index to have fill factor of 40, your avg_page_space_used_in_percent will straight away be 40.


Can't update trigger because of activity Trying to add a line to a trigger that is attached to a table that is constantly in use

ALTER TRIGGER [dbo].[name_of_trigger] ON [dbo].[name_of_table] AFTER UPDATE
BEGIN

   IF ORIGINAL_LOGIN() in ('username')   RETURN

....somecode

END

Getting deadlocked and the only solution appears to be retrying every couple of seconds, which so far hasn't worked. Is there any other way to get my if condition in other than hitting F5 and hoping it works?

Thanks,

Craig

When you issue an ALTER TRIGGER statement, that process will attempt to acquire a SCH-M (schema modification) lock on the table object as well as the trigger object.  This is an extremely low-concurrency lock and will cause the expected blocking that you're running into.

See this reference for a chart on lock compatibility.  For referencing sake, here is the chart linked directly from that document:

As you can see, that SCH-M lock will conflict and cause a concurrency blocker.

Much like anything that can cause an interruption, or be blocked itself, I'll have to agree with the comment above (from David Crowell).  You simply need to find a time when there is little to no activity on these objects.


Aurora PostgreSQL CPU usage stuck at 100% for 8 hours One of our Aurora PostgreSQL database's(48 cores) CPU usage stuck at  100% for nearly 8 hours. These situation happened 3 times in the past 4 months. We are using AWS's business support plan, in the past few month, we worked closely with the support team to diagnose the issue, but still haven't found what caused the problem. Thus I post this problem in the forum, any advice is very much welcome.

The Problems

Problem 1: As mentioned above, the most obvious appearance of these problem is database's CPU stuck at 100% for a vary long time, then suddenly back to normal without intervention, as we can see from the two screenshot below(the second image is a screenshot from the database's Performance Insight panel):

Problem 2: When this incident happens, several empty tables that server as FIFO queues in our service, takes enormous time (roughly 500 ms) to execute a simple query:{code}select * from  order by id limit 1;{code}
in normal circumstances, a query like might just take several ms. At that time, I check the auto_vacuum execution about these tables, seemed that auto_vacuum service run normally, but every time when it ran, failed to retrieve table's dead_tuple. Then I executed +vacuum + manually, still failed to retrieve dead_tuple.

What we have done

The vacuum problem

The vacuum problem is relatively easier, as Laurenz kindly pointed out, the reason why vacuum not working is there're long running queries.

The CPU problem

After the situation occurred, we check the database's log, didn't found any dead lock or slow query, also during or before that 8 hours, the traffic to our service remain as normal.

From the second image above, we can see that an +update+ SQL take a lot database's resource. AWS's support suspected that this SQL is what responsible for high CPU usage, as it might locked the table. We disagreed, for reasons that:

this SQL only locks a row
the transaction this SQL belongs to normally takes 3ms to 10 ms to execute, given no huge traffic and the databases uses 48 cores, it's hard to believe this amount of traffic can have the database stuck for such a long time

Also, CPU became busier way before the vacuum situation occurred, the images below show the relationship between max transaction ID and CPU usage(the first image used UTC time zone, the second used UTC+8, which I added according UTC time below, I'll use UTC in this question):

as it shows, the CPU usage steadily grown since 10/12 20:00(a small peak around 10/12 16:00 was we running a background job):

from 10/12 20:00 to 10/13 00:00, CPU usage grows from 40% to nearly 100%, however, during that time, auto_vacuum works fine, the max transaction ID never exceeded 200 million
after 10/13 00:00, auto_vacuum stopped working, and since then CPU stuck at 100%. But CPU usage back to normal at around 10/13 04:00 (we didn't intervene at that time)
after 10/13 4:00, max transaction ID kept growing, now we assume, as mentioned above, is because there're long running transactions.
at about 10/13 14:30, max transaction ID back to normal, after we reboot the database

What's next

After few months' effort but still didn't found the root cause, AWS's support team give us following advices:

change database's log_statement to ALL, so that when the incident happens again, can trace back what SQLs were executing
clean up long queries regularly
avoids uncommitted transactions
monitoring database's XID

There is a long running query that keeps your CPU busy and keeps autovacuum from cleaning up dead tuples. A long queries implies a long transaction.

The query takes long because of all the dead tuples.

Kill the long running query with pg_terminate_backend, and things will go back to normal.

To prevent long running queries, set statement_timeout.


Why can't I use NEWSEQUENTIALID() as the default value for my column? I'm trying to create a  table in Management Studio and after reading about the new (from SQL 2005 on) NEWSEQUENTIALID() function, thought I'd give it a go.

This is what I'm doing:

But it's not letting me. The error message I get is:

'FormTemplate (Forms)' table - Error validating the default for column 'FormTemplateId'.

Am I missing a trick here? I'm definitely running SQL Server 2008 R2.

Use CREATE TABLE, not the GUI.

This is a bug in SSMS, already reported here as well as here on Connect.


mongodb heartbeat - unicast, broadcast or multicast? I can't find any good doc or article (except http://docs.mongodb.org/manual/core/replication-internals/) about this in the internet. Anyone knows how mongodb heart beat signal operates?

The reason this question is asked is because I need to find out the bandwidth that mongodb uses for its operation, so we can calculate the optimum network bandwidth if we have a cluster of 3 to 5 instances.

Bottom line, is this a necessary point to consider when mongodb are used in production?

Bottom line, is this a necessary point to consider when mongodb are
  used in production?

Is it worth figuring out your bandwidth requirements? Yes

Is it worth including the heartbeat in that calculation? No

The heartbeats between the nodes in a set are basically TCP based and unicast - it is done by background threads that monitor the other members of the set from each of the constituent node and fire every ~2 seconds with minimal amounts of data.  If you are not doing enough traffic in terms of data to make that an insignificant amount of traffic, then you probably don't need to bother modeling for traffic at all.

The real thing you need to determine is the data traffic - each secondary will be replicating data from a source.  Your primary will need to both ingest that data from whatever application or other client is feeding it as well as replicate the data to at least one secondary (other secondaries can be "chained" off the first, but at least one must replicate from the primary).

Then, of course, for most non-archival databases, something is going to be consuming data as well as writing it.  Again, this can all be from the primary, or you might be OK with eventual consistency and go for secondary reads also.  In read heavy applications, this can dwarf write traffic and replication traffic.

If you are talking about a sharded environment, then you get some extra overhead - each shard is usually a replica set and you will get some communication between them as chunks are balanced.  But again, this is usually dwarfed by the application reads and writes.

To summarize, unless you are going to be doing something out of the ordinary, the amount of data you read and write will be your main source of bandwidth usage and hence what you have to model.


Generate permutations and combination for name column I have the following sample data for permutations and combination.

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');

I want to generate permutations and combinations for the name column in the table tbltest and store into temp table.

For an example expected result:

name
----------------
John Mak Sam
John Sam Mak
Mak John Sam
Mak Sam John
Sam John Mak
Sam Mak John
....
....

Tried with following query: Source

Note: Following query works as expected for single record but when I tried on table using cursor its running more than 35 min and still keeps running.

DECLARE @inputStr VARCHAR(MAX)= 'Sam Mak John';
DECLARE @ValueStr VARCHAR(100);
DECLARE @Count INT, @Loop INT= 1, @totalSum INT;
DECLARE @Query1 VARCHAR(1000), @Query2 VARCHAR(1000), @Query3 VARCHAR(1000), @Query4 VARCHAR(1000), @Query5 VARCHAR(1000), @Query6 VARCHAR(1000), @Query VARCHAR(4000), @Combination VARCHAR(1000);

--Temporary table to capture all the words separately
CREATE TABLE #tmpvalues
(intIndex INT IDENTITY(1, 1),
 intProc  INT,
 subStr   VARCHAR(100)
);

--Temporary table to store all the possible combinations
CREATE TABLE #tmpCombinations
(subCombStr VARCHAR(1000)
);

--get the sub-strings(words) from input statement into a temp table
WHILE LEN(@inputStr) > 0
    BEGIN
        SET @ValueStr = LEFT(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr) - 1, -1), LEN(@inputStr)));
        SET @inputStr = SUBSTRING(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr), 0), LEN(@inputStr)) + 1, LEN(@inputStr));
        INSERT INTO #tmpvalues
        VALUES
        (@Loop,
         @ValueStr
        );
        SET @Loop = @Loop + 1;
    END;
SELECT @Count = MAX(intINDEX)
FROM #tmpvalues;
SET @Loop = 1;

--Set an integer values for each words
--This will be used to filter the combinations in which any two words are repating
DECLARE @tempIntAdd INT;--Addition factor
SET @tempIntAdd = @Loop * @Count;
WHILE @Loop <= (@Count - 1)
    BEGIN
        DECLARE @tempIntProc INT;
        SELECT @tempIntProc = intProc
        FROM #tmpvalues
        WHERE intIndex = @Loop;
        UPDATE #tmpvalues
          SET
              intProc = @tempIntProc + @tempIntAdd
        WHERE intIndex = @Loop + 1;
        SET @Loop = @Loop + 1;
        SET @tempIntAdd = @tempIntAdd * 2;
    END;
--
SET @Loop = 1;
SET @Query1 = 'INSERT INTO #tmpCombinations SELECT DISTINCT ';
SET @Query2 = 'ALL_COMBINATIONS FROM';
SET @Query3 = ' ';
SET @Query4 = ' WHERE';
SET @Query5 = '(';
SET @Query6 = ')';

-- Generate the dynamic query to get permutations and combination of individual words
WHILE @Loop <= @Count
    BEGIN
        SELECT @ValueStr = subStr
        FROM #tmpvalues
        WHERE intIndex = @Loop;
        SET @Query1 = @Query1 + 'T' + CAST(@Loop AS VARCHAR) + '.subStr ';
        IF(@Loop < @Count)
            SET @Query1 = @Query1 + '+ '' '' + ';
        SET @Query3 = @Query3 + '#tmpvalues ' + 'T' + CAST(@Loop AS VARCHAR);
        IF(@Loop < @Count)
            SET @Query3 = @Query3 + ', ';
        SET @Query5 = @Query5 + 'T' + CAST(@Loop AS VARCHAR) + '.intProc';
        IF(@Loop < @Count)
            SET @Query5 = @Query5 + ' + ';
        SET @Loop = @Loop + 1;
    END;
SELECT @totalSum = SUM(intProc)
FROM #tmpvalues;

--Create final query
SET @Query = @Query1 + @Query2 + @Query3 + @Query4 + @Query5 + @Query6 + ' =' + CAST(@totalSum AS VARCHAR);

--Execute the dynamic Query
EXECUTE (@Query);

SELECT subCombStr from  #tmpCombinations

If you split each name into a separate table of the following structure,

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

(where ID marks name parts that belong to the same name, to avoid permutation of parts of different names) – then you can use a recursive CTE to produce the permutations:

WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  FullNameVariation
FROM
  permutations
WHERE
  Level = 1
ORDER BY
  ID,
  FullNameVariation
;

Applying the query to this data sample:

INSERT INTO dbo.NameParts
VALUES
  (1, 'Sam'), (1, 'Mak'), (1, 'John'),
  (2, 'Donatella'), (2, 'Nobatti'),
  (3, 'Sam'), (3, 'Buca'),
  (4, 'Juan'), (4, 'Soponatime'),
  (5, 'Aaron'), (5, 'Spacemuseum')
;

produces the following output:

ID  FullNameVariation
--  -----------------
1   John Mak Sam
1   John Sam Mak
1   Mak John Sam
1   Mak Sam John
1   Sam John Mak
1   Sam Mak John
2   Donatella Nobatti
2   Nobatti Donatella
3   Buca Sam
3   Sam Buca
4   Juan Soponatime
4   Soponatime Juan
5   Aaron Spacemuseum
5   Spacemuseum Aaron

You can play with this solution using a live demo at db<>fiddle.uk.


Accelerated Database Recovery - Should I turn it on for system databases I've just upgraded to SQL Server 2019 and I'm turning on ADR for all the user databases, but my question is, should I also be doing the system databases?

Nevermind. Tried it on the model database and got this answer:
Msg 12122, Level 16, State 1, Line 25
Accelerated Database Recovery cannot be enabled/disabled on system databases.


DB2 start tuning needs privilege When I run "start tuning" by db2 data studio it gives me the below message. This still happens when I run it under user "db2inst", the default user which I configured when I installed db2 on RedHat 7.2

@ Mark Barinstein many thanks to you because you made it easier for me

This worked with me with below two steps :-

1- [db2inst@localhost ~]$ db2 connect to TESTDB

2- [db2inst@localhost ~]$ db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', '', current_user)"


financial transactions with and without commissions I'm building a gambling website. All financial transactions from and to external sources (credit cards,  money transfer systems) are subject to commission. Amount of the commission depends on the source selected. And moreover, it changes periodically. These external commissions go to the payment systems.

Also some inner transactions are subject to commission. Inner commissions depend on the type of the game user playing. These internal commissions go to gambling website, it is the income.

I'm looking for the best way to store the history of transactions and commissions. Should they be stored in one table TRANSACTIONS (with a connection between payment and commission) or should I have separate table COMMISSIONS.

The purpose of storing is to represent the history of transactions to the user and of course for internal bookkeeping (in order to always know how much have we paid to payment systems and how much have we earned ourselves).

Any advice greatly appreciated.

For any application where you are tracking money changing hands, you should always use a Double Entry Accounting system.  Double entry accounting has been the standard for tracking money for over 500 years.  There is no good reason to use any other method.

This entails a TRANSACTION table and a TRANSACTION_DETAIL table.  The transaction table has one record per transaction that includes header information, like the date, the total amount, any categorization attributes or relationships, etc.

The transaction detail has two or more records per transaction.  These records break down where the money comes from and goes to.  In your case, you might have three records per transaction.  One shows the money coming in from the payment service.  The other two show the money going out to a commission account and to an income account.


Shrink log with TRUNCATEONLY but there's nothing to truncate? An external company has a job set up which shrinks the log with truncate only - I checked the default trace and for example last night DBCC SHRINKLOG WITH TRUNCATEONLY happened 300 times. If I also check autogrowth events I see that the log auto grew by 2GB only once - I thought there job may be causing the log to expand and contract hundreds of times each night, causing performance issues, but it seems their job is causing the log to expand once, then they shrink it back. I know this is still bad, but not as bad as I hoped!! (So I could present this overwhelming evidence to them and get them to stop the shrinks).

Are these SHRINKLOG WITH TRUNCATEONLY events, even if they have nothing to truncate, still causing performance issues?

Thanks

Are these SHRINKLOG WITH TRUNCATEONLY events, even if they have nothing to truncate, still causing performance issues?

How could this not cause performance issues?  Even if there is nothing to shrink it takes work to figure that out.

I would ask them why they are using SHRINKLOG WITH TRUNCATEONLY I expect they don't know why or even that they are doing this.  Even if they know why, there is almost no chance it is for a good reason.  If they have a good reason, you should post it, I doubt anyone has ever seen a good reason for this.

If the system is in FULL Recovery, the ONLY time you can shrink free space is after the log backup has run.  In the very extreme case when there is a good reason (almost never) do it once after the log backup.

If the system is in SIMPLE recovery, the VLF files can only empty when when all the inflight work has completed. And you can only truncate the very last VLF, and then the one before that, etc.  Truncating more then once an hour or maybe at the end of the job is the only way you might actually shrink something. See an in depth view in this related Why are virtual log files not always allocated in order?

Depending on what version of SQL you are running and the current size of the log file, a 2GB grow will create between 1 and 16 VLF files.  The smallest VLF in this scenario is 125MB, so at the very best the smallest amount a shrink could remove is 125MB, see more in this post

Even if they have a really good reason for shrinking (again I doubt they do) there is not a good reason to do it as often as they are.


What is the alternative of the combination of two pattern matching conditions? So what i want to do is to search in my table data for elements that in their name exists the word first and the word second. These two words can be seperated or attached to each others. A translation of that would be the following :

SELECT name FROM data WHERE name ILIKE '%first%' AND name ILIKE '%second%'

I searched a bit for an alternative for this query and how i can combine the condition in a regular expression but no luck.

My concern is performance problems if this query were to be performed over a large table. I already created the following index (which sped up considerably the search) :

CREATE INDEX pattern_matching_index ON data USING GIN (name gin_trgm_ops);

Is there a better way to perform this kind of search ? or is this as good as it's going to get ?

If first and second can be any value and could be embedded in other words without any kind of delimiter (eg, foofirstbar), then yes, using trigrams is probably as good as you're going to get. If there are a limited number of values for first and second, you could create an expression index of the name column passed through a regexp_replace to add spaces around your target words, and then use the built-in full-text-search functionality:

testdb=# create table data(t text);
CREATE TABLE
testdb=# insert into data select 'foofirstbarsecondbaz';
INSERT 0 1
testdb=# insert into data select 'foobarsecondbaz';
INSERT 0 1
testdb=# insert into data select 'foosecondbarfirstbaz';
INSERT 0 1
testdb=# select t from data where 'first & second'::tsquery @@ regexp_replace(t, '(first|second)', ' \1 ', 'g')::tsvector;
          t
----------------------
 foofirstbarsecondbaz
 foosecondbarfirstbaz
(2 rows)


SQL Server : Changing Database Username I have an odd situation and I hope someone can help me get out of this.

The situation:

I'm running an Azure SQL 2012 SP2 VM with Windows Server 2012. Since it's creation I promoted the server to be a domain controller and everything is fine. I've also installed an application (Dynamics CRM) which is pretty heavy on its database use (multiple databases, a few scheduled jobs).

I'm getting an error on these scheduled jobs saying that they can't validate whether computername\my.username has access.

  SQL Server Scheduled Job 'MSCRM_CONFIG.SiteWideCleanup' (0x1D1BA70291EB7E44810A6121F9045F17) - Status: Failed - Invoked on: 2015-01-14 00:30:00 - Message: The job failed.  Unable to determine if the owner (SYSTEMNAME\my.username) of job MSCRM_CONFIG.SiteWideCleanup has server access (reason: Could not obtain information about Windows NT group/user 'SYSTEMNAME\my.username', error code 0x534. [SQLSTATE 42000] (Error 15404)).

It looks like, when I promoted my server to a domain controller, my SYSTEMNAME\my.username object got changed to DOMAINNAME\my.username. I can log in to SQL with my domain username and have all the appropriate privileges (DBOwner), but when looking at the "Login" section I still see the reference to SYSTEMNAME\my.username.

I've been searching for a way to change the user in SQL around, but haven't been able to find an answer (I'm guessing this is a very odd situation that shouldn't happen when someone sets things up properly) and hope to find one here:

How can I switch the SQL Server login SYSTEMNAME\my.username over to DOMAINNAME\my.username without losing all the things that this username is attached to?

Note: this is a development server that I set up just for my own fiddling purposes, and I can do whatever I'd need to even if it's not best practice under normal circumstances.

Windows logins are tied to Windows users/groups based on the SID. In other words, you're still able to log in since the Windows user's SID matches, even though the login name still indicates the system name rather than the domain name. If you're worried about the mismatch, you can drop and recreate the login. Since it'll have the same SID, it should map to all the existing database users, and privileges will be preserved. Also, Management Studio seems to think you can hit F2 to rename a login, but I don't have a test system handy to confirm that works.

Of course, all the database users will still have the old name too. You can either script/recreate them, or try the F2 method.

After you've done all this, you'll probably need to change the owner on the jobs. I usually keep all the jobs owned by SQL Server logins (typically sa) so that they don't all fail if there's a temporary problem with the DC.


MongoDB moveChunk (chunk migration) chooses secondary with larger ping time for writeconcern majority We have a sharded cluster using replica sets with local secondaries (pingMs = 0), and remote secondaries (pingMs = 40).  So, each replica set has a local secondary and a remote secondary.  Chunk migration used to choose the local secondary when moving a chunk to satisfy its "majority" writeconcern used by Mongo's chunk migration.  And our transfer speed was reasonably fast.

Today we added another shard using a replica set configured identically to the other shards.  And, now when it does chunk migration to that new shard, it's using the remote secondary instead of the local secondary to satisfy the "majority" writeconcern and transfers are very slow due the network latency??  Am I correct in thinking that Mongo should be choosing the local secondary to satisfy the "majority" writeconcern because it has a much lower ping time?

I've compared rs.config() between the new shard and one of the older shards and they are identically configured.  We're using MongoDB 3.2.6.

Actually mongodb don't "choose" neither of those secondaries. Write concern majority is fulfilled when first secondary has replicated that change successfully and reported that information back to the primary. In this situation, somehow your "local" secondary was lagging with opLog replication, reason for that may or may not be found if you analyze that nodes mongod.log file.