Number of full table scans I would like to see information on how many times has a table been fully scanned.
I was hoping for something like sys.dm_db_index_usage_stats but for table scans.
There is a method described at the blog keepitsimleandfast, but I would rather get all scans since server started.

What I am looking for is also similiar to pg_stat_all_tables.seq_scan in PostgreSQL.

Well, that table will give you the answer you are looking for.  Just join onto sys.indexes to determine the type of index.  Tables are either Heaps or Clustered indexes, so a scan on that index type is a scan on the table.  See the example below, add any additional columns you are looking for.

SELECT S.name AS SchemaName
    , O.name AS ObjectName
    , I.name AS IndexName
    , I.type AS IndexType
    , I.type_desc AS IndexType_Desc
    , IUS.user_scans
FROM sys.objects AS O
    INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
    INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    INNER JOIN sys.dm_db_index_usage_stats AS IUS ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id
WHERE I.type IN (0,1) -- 0=HEAP, 1=CLUSTERED (table)


Digital content eCommerce schema advice? I don't have much experience designing databases, I'm looking for some advice on my design from more seasoned designers/developers. I'm developing a digital content eCommerce store which will be desktop based and mobile based similar to iTunes.

Schema

The store will most work like iTunes, it's just that it's focused on music at the moment. I'm not sure if I should explain each table responsibility. Users can reload monetary credits to their profiles from an external application.

Please advice if this design will work and is sufficient. Thank You.

Get yourself a good data model pattern book.
Accept the fact that most relationships are many-to-many.
Read up on table inheritance and the Party/Party Role model.

Some starting points:

An order has many line items, which belong to a product
A product will probably belong to multiple categories
A product will likely have more than one artist


Need help to optimize this slow performance Query Need help to refactor a query. I need to get a result from four tables and structure looks like this

Resource table

Id | Leader | Title | Author |

ResourceField Table

Id | ResourceId | Position | ResourceFieldValueId |

ResourceFieldValue Table

Id | Tag | Ind1 | Ind2 | Normalized | Subfields | SpecialTag |

ResourceHolding Table

Id | Barcode | ResourceId | LocationId | CallNumber | ResourceCategoryId | ResourceStatusId | Cost |
Relation between tables:
These tables are to hold library's resource information. Resource represents book, ResourceHolding represents individual copies of a book, ResourceFieldValue represents properties of books and ResourceField holds relation between Resource and ResourceFieldValue. So,

A resource can have multiple Resource Holding records
A resource can have multiple ResourceFieldValues in ResourceField table

Note: In ResourceFieldValue table, each property of a book/resource represents by three digit numeric value (saved as a string) in Tag column and some of the Tags are grouped into one special group. For example these tags represents ('100', '110', '700', '800', '920') Author group, Values in these tags represent ('130', '222', '242', '245', '246', '730', '830') Title of a resource/book.
Query I am having issue with:
I need to get x number of unique holdings with Author's name and Title from ResourceFieldValue.. I have Title and Author in Resource table but I can't use that.
This the query so far I have come up with but it is still slow.. I have around 40,000 holdings and 360k+ ResourceFieldValue records. With this query, right now it takes around 30 second to get results.
SELECT DISTINCT
h.ResourceId,
h.Id AS HoldingId,
h.Barcode AS [BAR],
h.Call AS [CAL],
h.ResourceCategoryId as CategoryId,
fvAUT.Normalized fAUT,
fvAUT.Subfields sf_AUT,
fvTIT.Subfields sf_TIT
FROM ResourceHolding h
OUTER APPLY (SELECT TOP 1 Subfields, Normalized FROM ResourceField rfAUT INNER JOIN ResourceFieldValue rfvAUT ON rfAUT.ResourceFieldValueId = rfvAUT.Id AND rfvAUT.Tag IN ('100', '110', '700', '800', '920') AND ResourceId = h.ResourceId)  fvAUT
OUTER APPLY (SELECT TOP 1 Subfields, Normalized FROM ResourceField rfTIT INNER JOIN ResourceFieldValue rfvTIT ON rfTIT.ResourceFieldValueId = rfvTIT.Id AND rfvTIT.Tag IN ('130', '222', '242', '245', '246', '730', '830') AND ResourceId = h.ResourceId)  fvTIT
 WHERE h.ResourceStatusId != 7
ORDER BY fAUT asc
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY

Let me know if you need more info to understand table structure. Any help would be greatly appreciated.
Thanks

we need to know more information as Justin mentioned in the comment to answer you better, however meanwhile try this method, it improve performance a little bit better:
with cte_ as (
select ResourceId
    , max (case when rfvAUT.Tag IN ('100', '110', '700', '800', '920')  then Subfields else null end) as sub1
    , max (case when rfvAUT.Tag IN ('100', '110', '700', '800', '920')  then Normalized else null end)  as Norm1
    , max (case when rfvAUT.Tag IN ('130', '222', '242', '245', '246', '730', '830') then Subfields else null end) as sub2
    , max (case when rfvAUT.Tag IN ('130', '222', '242', '245', '246', '730', '830') then Normalized else null end)  as Norm2
FROM
    ResourceField rfAUT
    INNER JOIN ResourceFieldValue rfvAUT
        ON rfAUT.ResourceFieldValueId = rfvAUT.Id
    group by
        rfvAUT.ResourceId
)

SELECT DISTINCT
    h.ResourceId,
    h.Id AS HoldingId,
    h.Barcode AS [BAR],
    h.Call AS [CAL],
    h.ResourceCategoryId as CategoryId,
    cte_.norm1 fAUT,
    cte_.sub1 sf_AUT,
    cte_.sub2 sf_TIT
FROM ResourceHolding h
left join cte_
 on cte_.ResourceId = h.ResourceId
WHERE h.ResourceStatusId != 7
ORDER BY fAUT asc
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY


How can I return column names in dot notation in MySQL? I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'.

I did:

INSERT INTO columns(name)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='contacts' or table_name='companies';

But as expected I get id from the first table showing up with the same field name.

How do I get it in the form: contacts.id and companies.id?

You first try was very close. Just prepend the table_name and a dot:

INSERT INTO columns(name)
SELECT CONCAT(TABLE_NAME,'.',COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='contacts' or table_name='companies';

Give it a Try !!!


Recommended Database(s) for Selecting Random Rows I'm familiar with selecting random rows from a table in PostgreSQL. It's really slow. I've looked into some other databases and they all seem to be bad at it. Mostly because it's not a normal use case for any of them.

Is there any type of database that can select a random row quickly? The only hard requirement is that it be installable on Linux.

So far I have a table with 500 million rows and anticipate it growing to a few billion in the near future and would like to be able to select a random item. I'd also like to be able to delete the same row a short time later, so it'll need some sort of key/index for that.

Right now my psql schema has 3 columns: a text field, a date, and an integer sequence id. The date is not important and doesn't need to be in the new database.

I was using one of two ways to get a random row:

SELECT * FROM mytable ORDER BY RANDOM();

and

SELECT * FROM mytable LIMIT 1 OFFSET <random number>;

The offset method was faster for low offset numbers, otherwise super slow. An offset of 1 million took ~600 msec, while 100 million was 60 seconds (average query time for midpoint offset was 2.5 minutes). For order by random, all queries were ~5 minutes.

I ended up finding a good solution, similar to Mat's comment, but I don't need to keep track of how many rows there are.

I added an integer column called "randval" with a btree index. When a record is saved, I generate a random number between 1 and 2 billion. It took a while to migrate existing data (about half a day on slow hardware), but now random selects are super fast, typically around 1 millisecond using this query:

SELECT * FROM mytable WHERE randval >= <random number> ORDER BY randval LIMIT 1;

With 500 million rows, not every random number has a value, so the order by with limit gives us the next closest one and the index makes it quick, with a 150,000x speedup for what was an average 2.5-minute query.

Databases don't like doing random things, but they sure do well at specific things that just happen to have random values.


mongodb limit server memory consumption I'm testing running monbodb on the kubernetes platform where I can limit the resources used by the running container.

Say I set the memory limit to 256Mb. The problem is that for example while making backup memory consumption increases to the limit and container gets restarted by kubernetes.

So the question is is there a way to limit mongodb memory consumption for my case so that it would not cause the crush by exeeding memory limit set by platform.

I could of course increase the limit but I'm interested in a principal solution and would like to understand this process better because I don't really now how memory consumed by mongodb and container os. Is it possible to tune mongodb/underlying linux os to work inside existing limits.

In normal situation, mongodb uses always all available memory!!

However, you can limit mongo's memory using cgroup.

Here is presentation what may help too.


Two foreign keys referring to one primary key I'd to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like:

| userID        |    name       |      email       |   balance  |
| ------------- |---------------|------------------|------------|
| 101           | alpha         | alpha@mail.com   |   1000     |
| 102           | bravo         | bravo@mail.com   |    500     |
| 103           | charlie       | charlie@mail.com |   2000     |

And the transaction table should look like:

| transactionID |  from_user    |   to_user        | transfer_amount  |
| ------------- |---------------|------------------|------------------|
|   1           | 101           |       103        |   100            |
|   2           | 102           |       101        |   150            |
|   3           | 102           |       103        |   200            |

I tried to draw a schema with Vertableo which doesn't seem have one to zero or many relationship option. But a user might not send or receive points at all, so the relationship should be one to zero or many. However, this schema on Vertableo throws the error reference name must be unique.

Could someone give hints to draw a schema or provide SQL code?

Example SQL code (create with MySQL Workbench):

CREATE TABLE `test`.`user` (
  `iduser` INT NOT NULL,
  `username` VARCHAR(45) NULL,
  PRIMARY KEY (`iduser`));

CREATE TABLE `test`.`transfer` (
  `transactionID` INT NOT NULL,
  `from_user` INT NULL,
  `to_user` INT NULL,
  `transfer_amount` FLOAT NULL,
  PRIMARY KEY (`transactionID`),
  INDEX `from_user_key_idx` (`from_user` ASC),
  INDEX `to_user_key_idx` (`to_user` ASC),
  CONSTRAINT `from_user_key`
    FOREIGN KEY (`from_user`)
    REFERENCES `test`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `to_user_key`
    FOREIGN KEY (`to_user`)
    REFERENCES `test`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

This works. However if you want to query the 'transfer'-table and want to substitute the IDs for usernames it gets a bit more complicated. Heres an example query:

select a.username as from_user, b.username as to_user, transfer_amount
from transfer
inner join user a
on transfer.from_user = a.iduser
inner join user b
on transfer.to_user = b.iduser;


What does it mean that a mongodb query has a waitingForLatch value of ReplicationCoordinatorImpl::_mutex? Some queries that are normally fast sometimes take ages to complete. I checked currentOp() and the queries that are taking so long are waiting for the ReplicationCoordinatorImpl::_mutex latch. None of those queries are inserts, updates or deletes. What does it mean?

The replication coordinator mutex is used for nearly anything even tangentially related to replication.  This is usually held for sub-microsecond periods when everything is running right.
One possibility is the queries are taking the mutex in order to check the current status of the replica set member to make sure it is primary/secondary.  If there is something like an election or sync source change going on it may hold the mutex.  Even then I wouldn't expect that to take more than a few seconds, and any such activity should cause a REPL entry in the log.
If this occurs frequently, you might try increasing the default logging verbosity to see if the debug log messages provide any clues.


AlwaysOn-- SQL Jobs Understanding I am new to AOAGs and Can Anyone please help me get out of below confusion.

I have 3 Replica AOAG.
I have a Archival Job in 3 Replicas scheduled at same time which will run every 15 minutes.

AlwaysOn Job logically will always look for the Primary replica.Is it correct ?
If the same job exists in all the AOAG replicas(Primaries and Secondaries) and scheduled at same time and why it is succeeding still with any failure ?Same job it runs in Primary and secondary without any conflict?can you please help me understanding this logic ?
Which do you recommend ,scheduling the job at same time or with a difference of time around 5 minutes in secondaries.
Can you hint me on what if same time and what if different time scheduled?
Same Job if it runs in Primary will it run even in secondaries also as they are scheduled at same time ?

Are all replicas configured to become primary in case of failover?
In case YES, you need to create the same job in all instances, scheduled at the same time. Keep in mind that each instance has a different MSDB, which means different jobs/schedules,etc..
So you will need to create a extra "check" in your jobs, to make sure it's running in the primary replica.
Here you can find a complete post about it.


mysql v5.5 index performance On a 5.5.23 server (that needs to go to 5.7 at least, I know) I have an InnoDB table with 3 columns (there are a few more that are not relevant here):

TimeC (bigint, unix timestamp)
id (integer)
value (integer).

The table currently has an index on  id. The table contains ~15mil rows. The three columns above all allow NULL values (I suspect that's a first clue here).

The query in question is like this:

select id, sum(value) as TotalV
from table_name
where timeC >=154013600000  and timeC < 1540742400000
group by id
order by TotalV desc;

This query takes about 280sec on our server.

I decided to add an index on the date-related column TimeC.

The query now takes about 260sec, i.e. the improvement is very small.

The "explain" utility shows,  without the new index:

select_type: SIMPLE
table: TableName
type: index
possible_keys: NULL
key: TableName_CC_IDX
key_len: 768
ref: NULL
rows: 14830632
Extra: Using where; Using temporary; Using filesort

...and WITH the new index:

select_type: SIMPLE
table: TableName
type: range
possible_keys: TableName_TimeC
key: TableName_TimeC
key_len: 9
ref: NULL
rows: 540694
Extra: Using where; Using temporary; Using filesort

So my question is, why is it not using BOTH indexes in the latter case when both indexes exist and when both are relevant to the specific query? Generally, what could I do to speed up this query?

(from Comment)

CREATE TABLE TableName (
    id varchar(255) DEFAULT NULL,
    timeC bigint(20) DEFAULT NULL,
    value decimal(42,2) DEFAULT NULL,
    KEY TableName (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show indexes from TableName\G
        Table: TableName
   Non_unique: 1
     Key_name: TableName_CC_IDX
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 29
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: Index_comment:

You need a multicolumn index (id, timeC, value) or (timeC, id, value) because existing timeC and defailt id indexes are not sufficient.

The common rule is to create the index containing all the columns used by JOIN, GROUP BY, ORDER BY and WHERE clauses. The order columns should be listed in the index can vary due to the exact queries, columns cardinality and even the amount of data.


SQL Job Agent Service Account - Access is denied to run Powershell I am trying to run a PowerShell script via the command line via SQL Job.

"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\Test\TestScript.ps1"

I am running the SQL Job from a SQL Server 2008 R2 server. Which permissions does the SQL Job Agent Service Account need to run a PowerShell script? Or is this a windows permission level, not a db permission?

EDIT:
This is the error I am getting
Executed as user: ****.

  The process could not be created for step 3 of job ## (reason: Access
  is denied). The step failed.

You have to call powershell.exe instead of the foldername

Change this

"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\test\testscript.ps1"

To this

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "C:\test\testscript.ps1"

Testing by creating the same jobstep

The original command

"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\test\testscript.ps1"

The error

  Executed as user: ServiceAcc. The process could not be created for
  step 1 of job .. (reason: Access is denied).  The step failed.

Changing the command and running the job again

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "C:\test\testscript.ps1"

Great success


sql server Group by with calculated values I have two tables: costumer table and invoice table. How to calculate sum(value) for each costumer (id) when I have calculated variables in group by statement - without puting code for calculated variables in group by statement (in group by should stay only name of the variable)?
Please find me the solution wrom which total sql code is as small as possible.

select
       p.id,
       p.name,
       case when
        (p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 end as validity_name,
       p.surname,
       case when
        (p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%')) then 0 else 1 end as validity_surname,
       p.adress,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id)
group by id, name, validity_name, surname, validity_surname, adress

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once.

When do you want the validity_name to equal 1?

Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column.

Using AdventureWorks as an example, this works like so:

USE AdventureWorks;
GO

SELECT
  FirstName,
  ValidName
FROM Person.Person AS p
CROSS APPLY (VALUES(CASE WHEN FirstName = 'Kim' THEN 1 ELSE 0 END)) AS a(ValidName)
GROUP BY FirstName, ValidName

When applied to your query, it should be something like the following (untested, use the example above to build your query):

select
       p.id,
       p.name,
       p.surname,
       p.adress,
       a.validity_name,
       b.validity_surname,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id)
        CROSS APPLY (VALUES(case when(p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 END)) AS a(validity_name)
        CROSS APPLY (VALUES(CASE WHEN p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%') then 0 else 1 END)) as b(validity_surname)
group by id, name, validity_name, surname, validity_surname, adress


Should I replace my varchar primary key with an integer primary key? I have a table that has a varchar primary key that's associated with "Base Product Codes".  They rarely change, and if they do, it's usually just being deleted or added.  I figured it would be better to index part of the product code so I could perform searches on the data based on a partial product code.

As an example,  "Base Product Codes" are in the following format:

ABC-AB12

The table stores the relationship between a product and accessory, and there can be multiple relationships for each product or accessory:

Id          |     Prod     |    Acc    | Val
---------------------------------------------
ABC-AB12_1  |    ABC-AB12  |   ABC1    |  1
ABC-AB12_2  |    ABC-AB12  |   DEF1    |  2
ABC-AB12_3  |    ABC-AB12  |   GHI1    |  A

I'm storing the id for each relationship as the product base code with an integer tacked onto the end of it.

I figured if I needed to to a search for all of the relationships for a particular product that it would be faster to use the primary key and search than it would be to search on the Prod column.

Is that an incorrect assumption?  Should I change the Id to be an auto incrementing integer instead?

You should have an autoincrement PRIMARY KEY. Since I do not know the storage engine of the table, I will try answer based on both.
InnoDB
In general, a smaller primary key is always better than a bigger one. The PRIMARY KEY for an InnoDB table is stored in the Clustered Index (known within InnoDB as the gen_clust_index). Since an InnoDB Page is 16K, smaller keys will make more keys fit inside an index page.
What should be noted is the fact that for each entry in a Secondary Index, there is Primary Key. Thus, not only will a smaller PRIMARY KEY benefit the table, but all non-unique Indexes will corresponding shrink as well.
MyISAM
Similar principles apply to MyISAM in terms of key sizes and indexes. Additionally, there is an added bonus in your particular case that is not often discussed when it comes to MyISAM.
MyISAM allows you to have an auto_increment key per column value. What do I mean?
Look at the table in your question with additional rows:
Id          |     Prod     |    Acc    | Val
---------------------------------------------
ABC-AB12_1  |    ABC-AB12  |   ABC1    |  1
ABC-AB12_2  |    ABC-AB12  |   DEF1    |  2
ABC-AB12_3  |    ABC-AB12  |   GHI1    |  A
DEF-AB12_1  |    DEF-AB12  |   ABC1    |  1
DEF-AB12_2  |    DEF-AB12  |   DEF1    |  2
DEF-AB12_3  |    DEF-AB12  |   GHI1    |  A
GHI-AB12_1  |    GHI-AB12  |   ABC1    |  1
GHI-AB12_2  |    GHI-AB12  |   DEF1    |  2
GHI-AB12_3  |    GHI-AB12  |   GHI1    |  A

You could replace the Id with an auoincrement value and end up with this:
Id |     Prod     |    Acc    | Val
----------------------------------------------
1  |    ABC-AB12  |   ABC1    |  1
2  |    ABC-AB12  |   DEF1    |  2
3  |    ABC-AB12  |   GHI1    |  A
4  |    DEF-AB12  |   ABC1    |  1
5  |    DEF-AB12  |   DEF1    |  2
6  |    DEF-AB12  |   GHI1    |  A
7  |    GHI-AB12  |   ABC1    |  1
8  |    GHI-AB12  |   DEF1    |  2
9  |    GHI-AB12  |   GHI1    |  A

This you would do if the Id looks likr this:
PRIMARY KEY (Id)

OK, great. Now here is the added bonus: If you make the PRIMARY KEY look like this:
PRIMARY KEY (Prod,Id)

the data can be stored like this:
Id |     Prod     |    Acc    | Val
----------------------------------------------
1  |    ABC-AB12  |   ABC1    |  1
2  |    ABC-AB12  |   DEF1    |  2
3  |    ABC-AB12  |   GHI1    |  A
1  |    DEF-AB12  |   ABC1    |  1
2  |    DEF-AB12  |   DEF1    |  2
3  |    DEF-AB12  |   GHI1    |  A
1  |    GHI-AB12  |   ABC1    |  1
2  |    GHI-AB12  |   DEF1    |  2
3  |    GHI-AB12  |   GHI1    |  A

How is that possible? Only the MyISAM Storage Engine Has This Mechanism Built In !!!
I have discussed this before:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin

One more thing: Why have PRIMARY KEY (Prod,Id) as a PRIMARY KEY? This would allow you to sequence each Product ID. Thus, you can look for sequence 3 of one product and sequence 3 of another product.
EPILOGUE
Whichever way you decide to go, using a smaller autoincrement PRIMARY KEY (4 bytes) make more sense for performance and diskspace than a larger PRIMARY KEY (more than 4 bytes).
Give it a Try !!!


How to return 0 for record count when nothing returns for that hour using datepart? In my sample query below, I am pulling the count by hour correctly, but I need it to return all hours of the day and display 0 for the hours that have no data. Is there an easy way for me to do this?

Here is my SQL:

IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL DROP TABLE #Temp;

CREATE TABLE #Temp (TempNumber NVARCHAR(50),TempDateTime DATETIME2)

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233600','2019-08-20 08:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233601','2019-08-20 08:32:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233602','2019-08-20 10:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233603','2019-08-20 12:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233604','2019-08-20 16:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233605','2019-08-20 23:27:08.047')

SELECT DATEPART(hh,TempDateTime) AS TempDatePart,
COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
GROUP BY DATEPART(hh, TempDateTime)

Here is what I'm looking for:

You can use a table constructor of all hours (0-23) and join to that from your DATEPART function.

DB FIDDLE

SELECT
   h.h as DatePartHour,
   --DATEPART(hour,TempDateTime) AS TempDatePart,
   COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
   full join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0)) h(h)
   on h.h = DATEPART(hour,TempDateTime)
GROUP BY  h.h


SQL query to get output I have two queries.

First query:

select files.originalfilename
from files
inner join digitalfiles on files.fileid = digitalfiles.fileid
where digitalfiles.path in (
        select *
        from Staple
        )

This will get the orginialfilename with extensions. Like below:

originalfilename

abc.doc

cool.bmp

1342.png

at.docx

Now the second query:

select ft.Extension
    ,COUNT(f.OriginalFileName) as [Count]
    ,sum(df.Length) / 1024 as [Size KB]
    ,ft.FileTypeId
    ,cast(count(f.OriginalFileName) * 100.0 / sum(count(*)) over () as decimal(18, 6)) as [Pct]
from Files f
inner join DigitalFiles df on f.FileId = df.FileId
left join FileTypes ft on df.FileTypeId = ft.FileTypeId
where f.Deleted = 1
group by ft.Extension
    ,ft.FileTypeId

This query will give me output as:
Extension, Count, Size KB, File ID, Percentage

I want to use the result of first query in second query and get the result.

I might be misunderstanding the question, but assuming that I do understand it, it seems that you could fairly easily take the where clause from the first

where digitalfiles.path in (
        select *
        from Staple
        )

And make it a part of the second query.

There are a number of ways you could do this (CTE, derived table, etc...) but it seems like just adding that to the where clause is probably as easy as anything.

select ft.Extension
    ,COUNT(f.OriginalFileName) as [Count]
    ,sum(df.Length) / 1024 as [Size KB]
    ,ft.FileTypeId
    ,cast(count(f.OriginalFileName) * 100.0 / sum(count(*)) over () as decimal(18, 6)) as [Pct]
from Files f
inner join DigitalFiles df on f.FileId = df.FileId
left join FileTypes ft on df.FileTypeId = ft.FileTypeId
where f.Deleted = 1
AND df.path in (select * from Staple)
group by ft.Extension
    ,ft.FileTypeId;


How do I uninstall only SSMS and Profiler for 2008R2? I'm running SQL Server 2008R2 locally on my dev workstation, and I use SSMS and Profiler quite often.  I recently installed the July 2016 version of those tools, and I'd like to remove the 2008R2 versions in order to free-up some disk space.

What is the proper way to remove ONLY those management tools?  (I do not want to remove/break my core 2008R2 install)

Go to Programs and Features, select SQL Server 2008 R2, and then the Remove option (people tend to panic that this will just remove everything without further input, but this merely launches a wizard):

After this, you will be guided through a wizard much like installation. On the Select Instance screen, select the "remove shared features only" option:

Then you will get to this screen, where you can select just the management tools:

Though, I don't know why you don't just leave them there. They don't take up an inordinate amount of space, and you never know when you may have to use them (e.g. to manage an SSIS package or maintenance plan from your 2008 R2 instance).

(Also, the astute observer will note that these screen shots all came from different operating systems - I swiped them from the Internet.)


how to publish user defined table type as an article in Transactional replication I am new to replication and trying to use transactional replication. I am trying to publish all data and schema. My stored procedure takes user defined table type as input.

CREATE type TableBParam as table
(
    Id Bigint,
    TableAId Bigint not null,
    FieldB1 nvarchar(50)
)

--Deadlock was observed on the save query
go
CREATE PROC SaveTableB
(
 @val [dbo].[TableBParam] READONLY
)
AS
BEGIN
SET NOCOUNT ON;

MERGE [dbo].[TableB] AS T
USING (SELECT * FROM @val) AS S
  ON ( T.Id = S.Id)
WHEN MATCHED THEN
    update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN
    insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);

END
go

when the snapshot agent runs it gives me an error "Script failed for user defined table type TableBParam"

I couldn't find an option to specify user defined table types in the article dialog when we setup Local publish. I have also explore the article properties to which didn't help me.

Appreciate your suggestions.

UDTs are not articles that can be added to a publication directly. They are often used in stored procedures, so when you publish a stored procedure that depends on a UDT, the table type is scripted with the procedure itself.

In this case, it looks like the snapshot agent is failing while scripting the UDT. You could investigate the issue by changing the -OutputVerboseLevel switch to 2 and look for meaningful error messages in the output.

Another option is to remove the offending procedure from the publication and script it manually, along with its UDT.


How do I tell the actual IOPS used by Oracle? This is hopefully a simple question. We have a large DB deployment that we need to scale up over the next year. For scaling we have been relying on our vendor, but recent developments have caused us to doubt their ability to provide good information about the DB. So, what I'd like to do is go into oracle and see what actual IO numbers look like. Is there any easy way to do this?

There is no short answer to this question, the ways to monitor the behavior of an Oracle database depend on the tools available, your database edition, your database version, do you use ASM or a filesytem, etc.

A general approach that works for any database edition and version (that is from Oracle 8 on) is to install statspack (see http://www.orafaq.com/wiki/Statspack or the documentation in $ORACLE_HOME/rdbms/admin) and take snapshots.
The snapshots will be taken every hour by default so the data is aggregated over this period of time.

This will give you an overview of the behavior of the database (e.g. amount of physical reads).

This is just a starting point as you may want to check, if your SAN partitions are correctly aligned, do your FS block size match the DB block size (8k by default) and so on.


How to update field with leading sign I have a table where I need to insert a '*' sign before a lot of names. My table looks like this now:

id,name

1,cake
2,car
3,mountain

And it should be:

id,name

1,*cake
2,car
3,*mountain

The name column is nvarchar. I have the ids in a csv file. I thought I could make the update statement with concat and a where clause with the IDs, but I can't get it to work.

I tried

UPDATE table
SET    NAME = concat('*', NAME)
WHERE  id = '1'
        OR id = '2'

But got

  Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized
  built-in function name

Begin Tran
    Create Table #TEMP(id INT,name NVARCHAR(50))

        INSERT INTO #TEMP
        SELECT 1,'cake' UNION ALL
        SELECT 2,'car' UNION ALL
        SELECT 3,'mountain'

        UPDATE #TEMP
        SET    NAME = +'*'+ NAME
        WHERE  id = '1' OR id = '2'

        Select * from #TEMP

ROLLBACK TRAN


Is there any benefit for natively compiled TVFs when used in non native modules? I have some schema-bound inline table valued functions that I could natively compile.

However, my workflow does not use store procedures and it would only be invoked from entity framework or ado.net directly.

As the functions must be inlined into our queries, I fail to see a point in marking my functions as natively compiled. However, a colleague of mine wants to use it because it's shiny and new.

From some brief testing there seemed to be no actual benefit. Am I mistaken or could there beneficial scenarios?

it is usually much better to use inline table value functions instead of multi statement TVFs or scalar functions, even if they are natively compiled, so you should better try to rewrite them as inline (single SELECT statement).

Gail Shaw made a performance test with scalar functions. The native compiled version was about 10 % faster (3352 instead of 3814 millseconds, while the inline table value function only took 300 ms). So I would not waste time to optimize something for 10 % when I could reach 1266%...

Edit:
I made another test by myself comparing native compiled inline table value fuctions with common inline table value functions (none of them accessing a table, so they are only CPU bound).

The most time the native compiled version is about 10 % faster, sometimes slower (as usual, since the CPU metric fluctuates usually).

use tempdb
go
CREATE FUNCTION [dbo].[f_test]
(
   @nr int
)
RETURNS TABLE
WITH SCHEMABINDING,
     NATIVE_COMPILATION
AS
     RETURN
select L1.n n1, L2.n n2, L3.n n3,
       calc.total

  from (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
  where calc.total = @nr
go
CREATE FUNCTION [dbo].[f_test2]
(
   @nr int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
     RETURN
select L1.n n1, L2.n n2, L3.n n3,
       calc.total

  from (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
  where calc.total = @nr
go
create view v_test as
select L1.n n1, L2.n n2, L3.n n3,
       calc.total
  from (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
go
set statistics time on
create table #t (total int, n1 int, n2 int, n3 int)
go
insert into #t
select f.total, f.n1, f.n2, f.n3
  from dbo.v_test as v
 cross join dbo.v_test as v2
 cross join dbo.v_test as v3
 cross apply dbo.f_test(v.total) as f
 where v3.total <= 20

go
insert into #t
select f.total, f.n1, f.n2, f.n3
  from dbo.v_test as v
 cross join dbo.v_test as v2
 cross join dbo.v_test as v3
 cross apply dbo.f_test2(v.total) as f
 where v3.total <= 20
 GO
 truncate table #t


Suggestion for Tooth Chart Data Structure Hello I'm not sure if this is an OK question here but here goes, I'm quite confused on how am I going to make the data structure of my tooth chart database.  I had first this thought of making a data structure like this

teeth_table
teeth_id (Primary key)
teeth_1 (These are 32 tooth)
until teeth_32 (These are 32 tooth)
patient_id (Foreign key connected to the patient table)

my second plan is to make an individual table for each tooth (which will take a lot of time I guess?)

tooth01_table
tooth_id
tooth_name
tooth_condition
tooth_recommendation
tooth_treatment
patient_id

and that's all I thought,
my plan is to make a table where it would show the following when a patient selects a tooth from a dropdown list

Tooth | Condition | Recommendation | Treatment

So what do you guys think is a good design for a tooth chart database?

a tooth chart is like these

  http://www.mouthandteeth.com/img/FDI-tooth-numbering-system.gif

I would so something like this.  I presume that your patients will visit more than once and you will need a data set for each visit.  Of course, you will need to add more columns, but this structure should allow for flexibility for reporting as well as data entry.

CREATE TABLE Teeth
(
ToothID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, ToothPosition INT NOT NULL
, ToothType VARCHAR(10) NOT NULL
)

CREATE TABLE Patient
(
PatientID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, PatientName VARCHAR(100)
)

CREATE TABLE PatientVisit
(
PatientVisitID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, PatientID INT NOT NULL REFERENCES Patient(PatientID)
, DateVisit DATETIME2 NOT NULL
)

CREATE TABLE PatientTeeth
(
PatientTeethID INT PRIMARY KEY NOT NULL IDENTITY(1,1)
, PatientVisitID INT NOT NULL REFERENCES PatientVisit(PatientVisitID)
, ToothID INT NOT NULL REFERENCES Teeth(ToothID)
, ToothCondition VARCHAR(10) NULL
, ToothRecommendation VARCHAR(10) NULL
)


Good Resources on Operating/Administering MongoDB What are the best resources for learning how to operate and administer MongoDB?

There are plenty of resources on developing against it - which actually creates a problem when you've got plenty of development competence and really need to filter out that noise. For developers who can't afford to hire a DBA yet, we need to get reasonably good at keeping our Mongo cluster stable, performant, etc. Beyond the FAQs and quickstart guides, are there any other good repositories of info? Good blogs to follow, etc.?

The best resource has to be the MongoDB docs themselves ...

http://www.mongodb.org/display/DOCS/Home

I'm constantly finding something new there.

Secondly, I'd say you should run the "try MongoDB" in browser tutorial available here, it's awesome. You can try out querying and everything without installing anything.

http://try.mongodb.org/

Also, I'd personally recommend very highly these two tools:

MongoVUE (Windows MongoDB GUI)
RockMongo (PHP MongoDB interface, beats the pants off the others.)

You can also see my stackoverflow answer here for more details on MongoDB gui's also there are lots of videos available here: http://www.10gen.com/video

Lastly, you can check out http://learnmongo.com for a lot of beginners resources (full disclosure, I founded the site.)


PostgreSQL custom domain with trigger Is it possible to create custom domain / data type and associate some trigger with it, for example hash password?

I will have for example password domain and if I set some value to this column, it will automatically hash that, so I don't have to create trigger for each table with password column

You cannot do that.

As the documentation says:

  CREATE TRIGGER creates a new trigger. The trigger will be associated
  with the specified table or view

This clearly excludes types and domains.  (Very logically: triggers are normally fired upon changing something - here you want to change a column, not the type.)

You should instead use a stored procedure to take care of your password column(s).  And personally I think the less places password are stored at, the better.


What happens when Core Cluster Group is failed over to another node? Will it take the whole cluster down? Single point of failure? "Cluster group" fail-over will cause cluster go offline. Really? Single point of failure? Am I missing something? What happens when Core Cluster resources (Cluster Group) are failed over from one node to another? Will it take whole cluster down? Thanks

When you failover a cluster resource group from a node to different node SQL Server instance and other resources which are part of this resource group is stopped on current node and then ownership is changed to node which you are failing over to. After ownership is changed SQL Server is restarted with owner as new node. During failover SQL Server might go in crash recovery mode so any Inflight transactions( transactions which were running) will be rolled back. SQL Server goes through process of recovery to rollback and rollforward transactions so as to bring database in consistent state as it was just at time when failover happened.

Cluster disk, netwrok name and MSDTC are also part of resource group so they also move to different node.


Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id I have a table like this one, that shows payments from each client, for different dates:
|  stream_datetime | customer_id | order_status | rn |
|:----------------:|:-----------:|:------------:|:--:|
| 04/06/20 11:19AM |      1      |   completed  |  1 |
| 05/06/20 10:54AM |      1      |   completed  |  2 |
| 06/06/20 10:59AM |      1      |   completed  |  3 |
| 08/06/20 09:27AM |      2      |    failed    |  1 |
| 09/06/20 11:02AM |      2      |    failed    |  2 |
| 01/11/20 05:59PM |      3      |   completed  |  1 |
| 02/11/20 05:59PM |      3      |   completed  |  2 |
| 03/11/20 10:01AM |      3      |   cancelled  |  3 |
| 04/11/20 09:20AM |      3      |   completed  |  4 |
| 05/11/20 10:25AM |      3      |   completed  |  5 |
| 01/13/20 03:29PM |      4      |   completed  |  1 |
| 02/13/20 03:29PM |      4      |   completed  |  2 |
| 03/13/20 03:29PM |      4      |   cancelled  |  3 |
| 04/13/20 03:29PM |      4      |   completed  |  4 |
| 05/13/20 03:29PM |      4      |   completed  |  5 |
| 06/13/20 03:29PM |      4      |   completed  |  6 |
| 07/13/20 03:29PM |      4      |   completed  |  7 |
| 08/13/20 03:29PM |      4      |   cancelled  |  8 |
| 06/20/20 03:29PM |      5      |    failed    |  1 |
| 07/20/20 03:29PM |      5      |   completed  |  2 |
| 08/20/20 03:29PM |      5      |   completed  |  3 |
| 09/20/20 03:29PM |      5      |    failed    |  4 |
| 10/20/20 03:29PM |      5      |   completed  |  5 |

I want to calculate the difference of days a client canceled his plan.
The challenge here is the fact that the client can cancel more than one time, so client 4 must
be counted as a churned client two times, but client 3 would be counted as a churned client
only one time.
I want to consider only clients that has an order_status = completed followed by (not necessarily in the next month) an order_status = cancelled.
I want too to create a column called purchase_day that keeps the day of the payment.
Obs.: the column rn means row number for the specific client.

EDITED:
Sorry. I made some mistakes and writing the question.
Maybe the order_status = cancelled come first than order_status = completed. This is due a mistake in the business, but it can happen. If this happen, so we cannot consider as a churned client.
So this is my expected result (now this is ok):
|   purchase_day   | customer_id | lifetime|
|:----------------:|:-----------:|:-------:|
| 01/11/20 05:59PM |      3      |   60    |
| 01/13/20 03:29PM |      4      |   60    |
| 04/13/20 03:29PM |      4      |   122   |

As you can see:

client 1 never cancelled (so he doesn't need to be in the result)
client 2 never cancelled (so he doesn't need to be in the result)
client 3 cancelled one time (his lifetime is equal 60 days)
client 4 cancelled two times (one lifetime of 60 days and another lifetime of 122 days)
client 5 never cancelled (so he doesn't need to be in the result)

Okay, had to do this in three steps:

To mark which records we want to keep (first record for each customer, records where the status changed)
To get the prior row's stream_datetime and calculate the the difference between
Select only the rows where the order_status was 'cancelled'.

SELECT
  customer_id
 ,purchase_day
 ,lifetime
FROM
(
  SELECT
    customer_id
   ,order_status
   ,LAG(stream_datetime) OVER (PARTITION BY customer_id ORDER BY stream_datetime) AS purchase_day
   ,CAST(stream_datetime AS DATE) - LAG(CAST(stream_datetime AS DATE)) OVER (PARTITION BY customer_id ORDER BY stream_datetime) AS lifetime
  FROM
    (
      SELECT
        customer_id
       ,stream_datetime
       ,order_status
       ,CASE
          WHEN customer_id <> LAG(customer_id,1,-1) OVER (PARTITION BY customer_id ORDER BY stream_datetime) THEN 'Y'
          WHEN order_status <> LAG(order_status,1,'') OVER (PARTITION BY customer_id ORDER BY stream_datetime) THEN 'Y'
          ELSE 'N'
        END AS change_ind
      FROM
        Payment
      WHERE
        order_status IN ('completed','cancelled')
    ) payment_summary
  WHERE
    change_ind = 'Y'
) payment_change
WHERE
  order_status = 'cancelled'
    AND purchase_day IS NOT NULL

Results in:
customer_id     purchase_day            lifetime
3               2020-01-11T17:59:00Z    60
4               2020-01-13T15:29:00Z    60
4               2020-04-13T15:29:00Z    122

You can test with this Fiddle.
I'd recommend running the subqueries by themselves to get a feel for how each step works and so you can be confident in the results.

Your query worked pretty well!. But I forgot to tell something: maybe
the order_status = cancelled come first than order_status = completed.
This is due a mistake in the business, but it can happen. If this
happen, so we cannot consider as a churned client.

Ok, this is easy enough to fix given how things are set up.  If the cancel has been entered erroneously, purchase_day will be NULL (we already ignore instances with two consecutive statuses), so we can just change the final WHERE to:
WHERE
  order_status = 'cancelled'
    AND purchase_day IS NOT NULL

Updated fiddle for you to test.


What is the instance name of the box I'm logged into? I'm at the linux prompt of a computer running Oracle 11.

Without connecting to the database, how can I determine what is the instance name of the Oracle server running on this box?

If the DB is running, you can look for the pmon process:

ps auxww | grep pmon | grep -v grep

You'll see one pmon process for each database that is up. For example ora_pmon_YOURDB, where YOURDB is the instance name.

You can also ask the listener which services it is serving connections for, with:

lsnrctl status


How do I find the creation date of a user in MongoDB? How do I find the creation date of a user in MongoDB?  As background, I'm used to doing this in relational databases like Oracle or MSSQL.

I've checked the admin.system.users table, as I thought I might be able to decode the _id in case it were a generated ObjectID, but no luck there, as it uses the {db}.{username} as the _id.

I could probably stuff the current timestamp into the customData field at user creation, but that would not be as reliable as the data dictionary queries I'm used to from the RDBMS cousins.  I would need to make sure all other DBAs follow the practice (not too hard, but prone to human error), but also make sure any 3rd party tools that do user administration (IDM) functionality follow suit (not easy).

As at MongoDB 3.2, you are correct that the admin.system.users collection does not maintain metadata such as created or modified timestamps.

You can add metadata manually using customData, but as you noted this requires coordination with any applications or tools that are allowed to create users in your MongoDB deployment:

use admin
db.createUser({
     user: "bobbytables",
     pwd: "CorrectHorseBatteryStaple",
     customData: { created: new Date() },
     roles: [
        { role: "readWrite", db: "accounts" }
     ]
  })

Ideally this sort of metadata would be maintained by the database server in a standard format that tools could also choose to interpret. There is a relevant open feature request that you can watch/upvote: SERVER-10021: Add timestamps to user documents.

If you are using MongoDB Enterprise there are a few extra features that may help you with a workaround in the interim:

You can use an external authentication mechanism that maintains metadata for users, such as Kerberos or LDAP.
You could potentially enable auditing to capture createUser and updateUser events, and write an admin script to update the customData accordingly.


MySQL: How to write a recursive function to show menu tree? I try to build a menu tree inside the SQL with this function:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT)
  RETURNS TEXT
BEGIN
    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    SELECT parent_type_id, title INTO _parentId, _typeContent
    FROM fcom_product_type WHERE id = _typeId;

    RETURN CONCAT( IF(_parentId IS NOT NULL,' / ', 'not found'), _typeContent);
END;
$$
DELIMITER ;

If I want call my function again in case it finds a "parent", MySQL says the follow error to me.

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT)
  RETURNS TEXT
BEGIN
    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    SELECT parent_type_id, title INTO _parentId, _typeContent
    FROM fcom_product_type WHERE id = _typeId;

    RETURN CONCAT(
      IF(_parentId IS NOT NULL,
         hello_world(_parentId, CONCAT(_typeContent, ' / ') ),
         'not found'),
      _typeContent, ' - ', _parantId);
END;
$$
DELIMITER ;

Error: Recursive stored functions and triggers are not allowed.

MySql doesn't support recursive functions. It supports recursive stored procedures but this feature is disabled by default. See manual.

Recursion uses stack space to store contexts and tends to put memory pressure into server. I strongly discourage to use stored procedures to do this kind of calculation, better to do them into the application code. Anyway to achieve your goal you must unroll recursion into an equivalent iterative loop. I tried to figure out what was the output of the function. I renamed function from hello_world to build_path:

DELIMITER $$
CREATE FUNCTION build_path(_typeId INT)
  RETURNS TEXT
BEGIN

    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    set @ret = '';
    while _typeId is not null do

      SELECT parent_type_id, title
      INTO  _parentId,  _typeContent
      FROM fcom_product_type
      WHERE id = _typeId;

      IF(found_rows() != 1) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'id not found in table fcom_product_type';
      END IF;

      set @ret := CONCAT('/', _typeContent, @ret);
      set _typeId := _parentId;

    end while;

    RETURN @ret;
END;
$$
DELIMITER ;

So you can call:

call build_path(2)

to get for example:

/root/child1/child2


Unable to enable publishing on restored database We have a production database (2008 R2) that is enabled for replication as a publisher and subscriber with a remote distributor (distributorA).

We restored a copy of this database onto a new 2016 server. Now we want to configure replication on the restored copy as a publisher to our new distributor (distributorB).

We configured distributorB with a new distribution database and configured the new 2016 server as a publisher to distributorB.

Back on the new 2016 server, we ran sp_adddistributor to point it to distributorB. Everything works as expected so far.

Now we run the following command on the restored db to enable replication sp_replicationdboption @dbname = 'restoreddb', @optname = 'publish', @value = 'true'. That completed successfully.

This is where the problem starts to manifest itself. When I tried to create a new publication now, at the end of the publishing wizard, it errored out saying it can't find dbo.syspublications.

I check the system tables on the restoredb and find that some of the tables required for replication have not been created (syspublications, sysarticles, syssubscriptions and etc), but some have (sysreplservers and MSPeer*). I can, however, see that this database has 'transactional' checked in publication databases under publisher properties. When I try to uncheck it and hit OK, it gives me the following error

Invalid object name 'dbo.syssubscriptions'.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

I tried using sp_removedbreplication to cleanup any remnants of replication settings from the database to start afresh, but that didn't seem to help at all.

I think the problem here is somehow the remnant replication settings from the database's 'former self' is interfering with setting up replication again with a different distributor.

So my question is why some of the replication system tables were not created? How do we remedy this?

After some further investigation through profiling, we found the issue.

We traced the whole process of enabling publishing on the database by running

sp_replicationdboption @dbname = 'restoreddb', @optname = 'publish', @value = 'true'

and zeroed in on the part that creates the sys tables:

EXEC @retcode = sys.sp_MScreate_pub_tables

This is where it gets interesting:

EventClass  TextData
44  IF not exists (select * from sys.objects where name = 'sysarticles')
45  IF not exists (select * from sys.objects where name = 'sysarticles')
44  IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
45  IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
44  IF not exists (select * from sys.objects where name = 'sysschemaarticles')
45  IF not exists (select * from sys.objects where name = 'sysschemaarticles')
44  IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
45  IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
44  IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
45  IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
44  IF not exists (select * from sys.objects where name = 'syssubscriptions')
45  IF not exists (select * from sys.objects where name = 'syssubscriptions')
44  IF not exists (select * from sys.objects where name = 'sysarticleupdates')
45  IF not exists (select * from sys.objects where name = 'sysarticleupdates')
44  IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
45  IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
44  IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
45  IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
44  CREATE TABLE dbo.systranschemas
            (
                tabid int not null,
                startlsn binary(10) not null,
                endlsn binary(10) not null,             typeid int not null default 52
            )

45  CREATE TABLE dbo.systranschemas
            (
                tabid int not null,
                startlsn binary(10) not null,
                endlsn binary(10) not null,             typeid int not null default 52
            )

With eventclass 44 = SPStmtStarting and 45 = SPStmtCompleted.

So what's happening here? It looks like the 'IF NOT EXISTS' statements were returning false for all the sys tables EXCEPT systranschemas, but we checked that these tables don't exists at all under System Tables.

So we picked a select from there and ran it

select * from sys.objects where name = 'sysarticles'

We found that there is a SYNONYM with the same object name but under a different schema in the database. This is a remnant of a migration project we did a few months ago where we moved all the tables from another database to this one under a different schema. The synonyms were created as the first step - instead of just creating them for user tables, we created them for the system tables as well.

Regardless, I am a little appalled at how lazy the coding is in this system procedure - it does not check for the 'is_ms_shipped' flag in the sys.objects, nor does it check for the schema - strangly except for 'systranschemas' where it does check for the schema and thus was able to create the table without any issues.


optimize big sql query My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish and product (can be different).

SELECT count(*) as daySum,
    p_name,
    SUM(pp_price) as daySumPrice,
    o_day,
    o_time,
    s_id,
    s_name,
    t_name,
    t_id,
    SUM(ust10) as ust10,
    SUM(ust20) as ust20,
    SUM(sdUst10) as sdUst10,
    SUM(sdUst20) as sdUst20 ,
    fk_p_id,
    u_name
FROM
(
    SELECT tbl_orders.o_id,
        tbl_orders.o_comment,
        DATE_FORMAT( $dateField,  '$sqlDateFormat' ) AS o_day,
        tbl_orders.o_time,
        tbl_orders.o_t_name AS t_name,
        tbl_orders.fk_t_id AS t_id,
        tbl_orders.fk_u_id AS u_id,
        tbl_orders.fk_s_id AS s_id,
        tbl_orders.o_s_name AS s_name,
        tbl_orders.o_u_name AS u_name,
        tbl_orders.fk_p_id,
        CONCAT(tbl_orders.o_p_name,IF(sideDish.p_name IS NULL,'',' ('),IFNULL(GROUP_CONCAT(DISTINCT(sideDish.p_name) SEPARATOR ', '),''),IF(sideDish.p_name IS NULL,'',')'),IF(tbl_orders.o_comment='','',' - ".getLanguageKey('label_comment').": '),tbl_orders.o_comment,IF(tbl_orders.o_comment='','','')) as p_name,
        ROUND(IFNULL(tbl_orders.o_p_price,0)+IFNULL(sideDishPrice.pp_price,0),2) as pp_price,
        IF(tbl_orders.o_p_ust=10,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust10,
        IF(tbl_orders.o_p_ust=20,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust20,
        sideDishPrice.ust10 AS sdUst10,
        sideDishPrice.ust20 AS sdUst20
    FROM tbl_orders
    INNER JOIN tbl_dailyReport dr1
        ON dr1.dr_id = tbl_orders.fk_dr_id
    LEFT JOIN
    (
        SELECT  o2.o_id,
            SUM(IFNULL(o2.o_p_price,0)) as pp_price,o2.o_p_name as p_name,o2.o_parent AS o_parent,
            SUM(IF(o2.o_p_ust=10,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust10,
            SUM(IF(o2.o_p_ust=20,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust20
        FROM tbl_orders o2
        GROUP BY o_parent
    ) sideDishPrice
        ON tbl_orders.o_id = sideDishPrice.o_parent
    LEFT JOIN
    (
        SELECT o_id,o_p_name AS p_name,o_parent
        FROM tbl_orders
        GROUP BY o_id

        ) sideDish
            ON tbl_orders.o_id = sideDish.o_parent
WHERE
                    tbl_orders.fk_c_id = '$c_id'
                    $stornoNull

                    AND tbl_orders.o_parent IS NULL
                    $onlyDayClosingData
                    $whereClauseTime
                    $whereClauseID
                    $whereClauseStorno
                        GROUP BY  tbl_orders.o_id,tbl_orders.o_p_name,sideDish.o_parent

                    ) products  $groupBy $orderBy;

The query is working and it gets the right numbers but it takes too long.

rows in database: 7000 orders in march: 3500 time to print 6 of these queries with different group by's (date, waiter, table, products, payment method, and cancellations,...): about 30-40 secs.

Imagine how long it would take if we have 10000000 of rows (which could be realistic in a few years?)

Is there any way to improve this performance?

EDIT:
I already solved it using a second table.
As the table tbl_orders is used for the orders itself (recursive, for orders with sidedishes) i just put it joined into a new table tbl_report. There it is possible now for me to group things like i want them with good speed :)

Thank you for your advices. Some of them were helpful though :)

how should i mark my question? solved?

All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

Use views and stored procedures instead of heavy-duty queries.
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to use UNION ALL statement instead of UNION

For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html

Thanks n regard


MySQL generic trigger to save identification data for later accessing of the changed row I am pretty inexperienced with this.

I need a generic trigger, able to create and save in a fixed table some sort of identification data for a changed row from generic (any) table. The identification data should be used later to SELECT the changed item in the given table.

Can be this done without previously knowing the table structure?

The only idea I had, but it's way too inefficient in my opinion, also requires previous knowledge of the table column names, is to save a hash by:

MD5(concat(NEW.field1, NEW.field2, NEW.field3, ...))

then

SELECT * FROM chaged_table WHERE hash = MD5(concat(field1, field2, field3, ...))

to identify the changed row in the table which triggered the change.

I would greatly appreciate any help or suggestions!

I've used this setup before, controlled by triggers.  It works for any table with a primary key, and can be queried easily.

CREATE TABLE `auditLog` (
  `db_user` varchar(255) default NULL,
  `table_name` varchar(255) default NULL,
  `row_id` int(11) default NULL,
  `field_name` varchar(255) default NULL,
  `old_value` blob,
  `new_value` blob,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP
);


how to use "mysqld -O thread_stack=#" I keep getting this error whether I run my query from the terminal or from within Java. Anyone has some instructions how to resolve this?

Thread stack overrun:  5768 bytes used of a 131072 byte stack, and 128000 bytes needed.
Use 'mysqld -O thread_stack=#' to specify a bigger stack.

BTW: I am using mac osx lion

I don't think Rolando thought about what this variable does before answering. Don't set the thread_stack to 32M. It's is allocated per connection and 100 connections is going use 3.2GB.

thread_stack = 192K

Setting it to the fairly standard 192k should be fine.


Need a way to redesign tables such that there is minimal redundancy I am new to this so please guide me if I may have missed standard practice in asking question.

I have two tables. These are used together and practically there is no use of them individually. I have attached sample structure in the images here.

First image contains the queries that will provide value of the field when executed.
Second image contains the table that provides positional and format info which will be used when the label is printed.

In short, first table gets the value and second gives the info about where to put the value on page when printed.

PROBLEM:
The first table has around 350 records. Meaning 350 different fields whose value will needed to be fetched every time someone hits "print".

Second table has over 100K records (350 fields x 300 different label templates). So there is a redundancy in second table. I am storing same type of info for same fields again and again for 300 times.

Is there any way I can avoid this? I am hesitant to create too many tables and I also want to avoid this type of redundancy.

Use "Normal Forms" to resolve your tables into a precise table which will contain both table data information, there are many Normal Forms, so please read about Normal Forms based on your requirements. Other way is that you can have a foreign key from first table in second table, so that you may not require to store first table data again in second table, while accessing in query u can put condition on that foreign key and access both table data rather than using first table data again in second table. I hope you got it, if not please let me know!!


How can I detect what's changing my scoped configurations? I'm noticing that I have a database that keeps resetting the scoped configuration , namely maxdop.

Is there any logs that show who or what process is causing these configuration changes?
I'm on Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4

Well, you won't have any history, but you can catch this in the act in the future, since SQL Server Audit can do this, using DATABASE_OBJECT_CHANGE_GROUP:

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\path\to\audit\folder\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

Now, for each database:

USE database_name;
GO

CREATE DATABASE AUDIT SPECIFICATION CatchDBConfigChangers
  FOR SERVER AUDIT ServerAudit
  ADD (DATABASE_OBJECT_CHANGE_GROUP)
  WITH (STATE = ON);
GO

You'll have to create an audit specification per database, and this will collect more than just scoped configuration changes, but once you have set up your databases, you can start checking for details immediately:

SELECT
  [when]      = event_time,
  [app]       = application_name,
  [host]      = host_name,
  [ip]        = client_ip,
  [login]     = server_principal_name,
  [statement]
FROM sys.fn_get_audit_file
  ('C:\path\to\audit\folder\ServerAudit*', NULL, NULL)
WHERE LOWER([statement]) LIKE N'%scoped%configuration%'
-- AND database_name = N'database_name'
;

My results after one change to MAXDOP (click to enlarge):


Constrain array values to an allowed set of values I would like to set up a CHECK constraint that makes sure that a text[] column's elements contain only certain values.

Setting up an example:

CREATE TABLE foo(
  countries text[]
);

INSERT INTO foo VALUES ('{"Morocco", "Mali", "Indonesia"}');
INSERT INTO foo VALUES ('{"Sokovia", "Mali"}');

Now only "Morocco", "Mali" and "Indonesia" should be allowed, so the second row should get rejected by the constraint.

I have a "working" solution:

CHECK (array_length(
  array_remove(
    array_remove(
      array_remove(
        countries,
        'Indonesia'
      ), 'Mali'
    ), 'Morocco'
  ), 1) IS NULL)

But this is not very readable.

I tried this as well:

CHECK ((
  SELECT unnest(countries)
  EXCEPT
  SELECT unnest(array['Morocco', 'Mali', 'Indonesia'])
) IS NULL)

but:

  ERROR cannot use subquery in check constraint

Use the “is contained in” operator in your check constraint:

CHECK (countries <@ ARRAY['Morocco', 'Mali', 'Indonesia'])


HQL pagination returns same result on different pages - with SQL We have the following query in the server (using sql):

Query query = this.entityManager.createQuery(
           "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC");

The type field is not unique.

In order to implement pagination, we use the following code:

query.setFirstResult(currentPage * pageSize).setMaxResults(pageSize);

Meaning that if we want a page size of 10, and we have 15 results overall, the client sends currentPage = 0, pageSize = 10 to display the first page, and currentPage = 1, pageSize = 10 to display the second page.

It seems that in cases where the total results > page size we get the same results on different pages, and some result lines are not displayed at all (on any page).
Our guess is that this issue has to do with the fact that the sort field is not unique, because if we use the following query, it does not occur:

"SELECT type, id, date, amount FROM Transaction ORDER BY type ASC, id ASC"

Does anyone know why this happens? And how to solve it? (Other that just adding the id as a sort field as well).

If you do not specify any order, by SQL definition databases can return the rows ordered any way they want.

You make it order by nonunique values so each "group" with the same value can be returned in any order - usually this "random" ordering is quite stable but that is mostly incidental as your case shows.

The workaround you found is a good one, use it - if your main ordering is by non-unique column, add a unique column to make the ordering well defined. Usually the ID/PK column is a good one to add, but any other unique column is
good enough from definition standpoint, only the PK may have some performance benefit in many case (InnoDB implicit clustering as an example) but really what is important is that the combination of columns used for ordering is unique, so even adding a non-unique column may be enough if the resulting ordering is unique.


Designing a database structure for huge sets of form data I am converting a form that takes up 35 pages of A4 size to a web based version, and will need to retain the data for each form submission in a MySQL database.

The form is complex and we could be looking at over 500 individual fields for each form submission.

I've been reading about the EAV data approach, which seems like it might be a good option. The info I was reading for EAV however suggested it was only a good choice for sparse matrix data, which I imagine this will not be.

500 columns seems like bad design, what do you think?

Have 'real' columns for a few of the fields -- the ones that you are likely to search or filter on.  Throw the rest (or, simply, all) of the fields into a big JSON string as another column.  Perhaps MEDIUMTEXT.


Risks associated with automatically shrinking TempDB as a SQL agent job? I have a situation where I have a constraint on TempDB to not exceed 350 GB in size. I'm using autogrowth 10 (percent) on TempDB, as I've read that it is supposed to be best practice on databases exceeding 500 MB.

When I used

 dbcc checkdb with estimateonly

on every database, I've noticed that the estimated requirement on TempDB is much lower than I currently have. Summing the values returned by dbcc checkdb with estimateonly gives a value of about 75 GB.

TempDB's size is currently 300 GB. I can't increase the permitted size of TempDB any further.

I've heard that our Company previously had a SQL job which shrinked TempDB automatically if it exceeded some value, but it is not used in our new environment. I am conflicted however, that being too liberal with shrinking TempDB may cause issues.

Using the information from this post, is there any major risks associated with autoshrinking TempDB to the size of, say, 200 GB if the current size exceeds 200 GB or so, seeing that dbcc checkdb with estimateonly returned an estimated minimum size of 75 GB which would lead this to a rather high relative buffer?

I've read that is not the optimal practice to shrink TempDB, but may it be more "legimate" in this instance in reference to the estimated minimum size of TempDB?

  EDIT

I've made the following Query which shows, for instance, internal object MB space, internal object dealloc MB space, statement text (the Query), total elapsed time, is_user_process:

WITH task_space_usage AS (
    SELECT dmv_tsu.session_id,
           dmv_tsu.request_id,
           SUM(dmv_tsu.internal_objects_alloc_page_count) AS alloc_pages,
           SUM(dmv_tsu.internal_objects_dealloc_page_count) AS dealloc_pages,
           dmv_es.login_name,
           dmv_es.host_name,
           dmv_es.program_name,
           dmv_es.is_user_process,
           dmv_es.total_elapsed_time,
           er.start_time,
           er.estimated_completion_time,
           er.command
    FROM sys.dm_db_task_space_usage dmv_tsu WITH (NOLOCK)
    INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
    INNER JOIN sys.dm_exec_requests er ON dmv_tsu.session_id = er.session_id and dmv_tsu.request_id = er.request_id
    WHERE dmv_tsu.session_id <> @@SPID
    GROUP BY dmv_tsu.session_id, dmv_tsu.request_id,dmv_es.login_name,dmv_es.host_name,dmv_es.program_name,dmv_es.is_user_process,
           dmv_es.total_elapsed_time,er.start_time,
           er.estimated_completion_time,
           er.command
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan,
       TSU.host_name,
       TSU.login_name,
       TSU.program_name,
       TSU.is_user_process,
       TSU.total_elapsed_time,
       TSU.start_time,
           TSU.estimated_completion_time,
           TSU.command
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 10 DESC;

My question is:

Is it more beneficial to have a Query like this, in combination with a SQL agent job which says: `

If TempDB > maxSize then CheckTheQueryPosted, terminate all queries which is a user process

I know this is not code, but I meant something in the lines of this concept?

There are a lot of common misconceptions when it comes to tempdb and shrinking databases.

For starters, if tempdb grows beyond 350 GB, it's because it needs to. With that kind of size growth, I'd say there's an ETL job or manual batches that aren't behaving well. You could fix that in a number of ways, the best of which are (matter of my opinion)

Fix the queries that are eating up your tempdb: look for huge Hash Match or Sort operations without proper indexes or even row versioning if you're doing Read Committed Snapshot Isolation.
Add more disk space. In most environments, this is just a matter of giving your SAN admin a call.

Normally, I would recommend putting tempdb on its own disk(s) and size the database from the start, so you'll avoid the autogrow thing entirely. Autogrowing by 10 percent is often really bad practice for two reasons:

When the database is small, autogrowth can be very frequent, and also not aligned with your physical storage sectors
With larger databases, the files will grow by quite a lot once they do.

Another issue with shrinking tempdb is that SQL Server keeps a lot of stuff in there, preventing it from shrinking. You'll find that even if you try to shrink tempdb, it just won't, unless you pretty much restart the server (either literally or by clearing all sorts of buffers, both of which can have a dramatic impact on your production environment).

In summary

Don't autoshrink, make sure there's enough space from the start.
Consider sizing tempdb from the start so it won't have to autogrow.
See if you can tune those queries that are filling your tempdb. They'll probably also be the ones that take the most time to run.


PostgreSQL user permissions I have a PostgreSQL 9.3 database with the PostGIS extension backing a web application and GeoServer. I have created three users.

A read only user for accessing the data in the reference data schema.

A read/write user for accessing and updating data in the schema that holds the application state.

A read/write/create user for the mapping data being accessed by GeoServer.

The first two work fine. The third one I'm having an issue with. Logged in as the user GeoServer can create new tables just fine. But subsequently cannot read from them. I have to go in specifically and manually apply the permissions. This is untenable for the application. It doesn't make a lot of sense to me that a user could create a table they can't read from. So I figure there is a non-obvious permission setting I missed. My Google searching led me to plenty of instructions for oddities of setting up a read only user, but not for this.

This should work (as superuser):

CREATE ROLE rwc;    -- read/write/create user
CREATE SCHEMA foo;
ALTER  SCHEMA foo OWNER TO rwc;
-- GRANT ALL ON SCHEMA foo TO rwc;  -- alternatively

CREATE ROLE r;      -- read user
GRANT USAGE ON SCHEMA foo TO r;

CREATE ROLE rw;     -- read/write user

GRANT r TO rw;
GRANT rw TO rwc;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT SELECT ON TABLES TO r;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT USAGE ON SEQUENCES TO r;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT INSERT, UPDATE, DELETE ON TABLES TO rw;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT SELECT, UPDATE ON SEQUENCES TO rw;

You probably missed one of these steps.
You may need do adjust existing objects additionally since default privileges are only granted to new objects.


MySQL: need help making a query faster I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering):

SELECT * FROM (
    SELECT discovery.id, discovery.resource AS url, discovery.event_datetime, whitelisted.keyword
    FROM discovery
    INNER JOIN whitelisted
    ON discovery.`resource` LIKE concat('%', whitelisted.`keyword`, '%')
    LEFT JOIN `logs`
    ON `logs`.discovery_id = discovery.id
    WHERE
    discovery.event_datetime >= NOW()
    AND
    discovery.provider = 'XXX'
    AND
    `logs`.id IS NULL
    AND
    discovery.resource NOT LIKE '%SOMETHING-TO-EXCLUDE%'
    LIMIT 0, 20
) logless_resources
GROUP BY logless_resources.url
ORDER BY logless_resources.event_datetime ASC

discovery table: 143k rows

id UNSIGNED INT AI PRIMARY
resource VARCHAR(1024) INDEX
provider ENUM
event_datetime DATETIME
created_at DATETIME

whitelisted table: 535 rows (not thousands, just 0.5k)

id UNSIGNED INT AI PRIMARY
keyword VARCHAR(128) INDEX
provider ENUM
created_at DATETIME

logs: 585k rows

id UNSIGNED INT AI PRIMARY
discovery_id UNSIGNED INT FK to discovery.id

Right now this takes 90 seconds - can it be made faster?

When you have a query that has multiple criteria on the same table a compound index is needed. MySQL will only use a single index for a table (or alias) so make sure its the right one.

First look at discovery since its on the left of the join. Start with items referenced and then ranges. So:

provider
event_datetime

Elements like resource because of the LIKE %x% expression can't use and index. For a small table we relay that (provider,event_datetime) restruct the table small enough to be be significant about including resource in the index.

So:

CREATE INDEX provider_event_datetime ON discovery (provider,event_datetime);

Note: is whitelisted.provider need to be the same as the discovery_provider?

A limit on the inner query is quite unpredictable without an ORDER BY.

The remaining join on logs is looking it up by the PK. No additional indexing is required there.

Look at EXPLAIN {query} to examine what indexes it is using and how.


How to clone a user in SQL Server 2008 R2? Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?

Note : The below script doesn't actually set the permissions on anything, it just creates the script that can be copied and pasted to a new query, which can then be edited before being executed.

Below script will help you copy/clone permissions of one user to another:

--- To copy permissions of one user/role to another user/role.

USE database_name -- Use the database from which you want to extract the permissions
GO

SET NOCOUNT ON
DECLARE @OldUser sysname, @NewUser sysname

SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from
SET @NewUser = 'userNEW'  --The user or role to which to copy the permissions to

SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

SELECT  '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'

SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC

SELECT  CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.objects AS obj
    ON perm.major_id = obj.[object_id]
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
    sys.columns AS cl
    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE   usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT  CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
WHERE   usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC


AlwaysOn Shared network location change requrest We have successfully setup the Alwayson with different subnets and it was working fine.
But unfortunately the existing NAS storage had a problem. Now our storage team provide new storage IP.

We need to replace the existing storage configuration with new one in Shared network location wizard . Is there any way without removing the existing setup due we already configured around 500 database.

Please find the below screen shot and do the needful.

The shared network location in that wizard is only used in the wizard and only used as a temporary location to backup the databases to and restore from that location to any of the replicas that could host that database.

Apart from that, it has no use and is not used internally anywhere in availability groups.

You can easily change the value to whatever new value your storage team has given you, but you'll want to double check that all of the replicas have access to said shared location.


Consultants using old version of Hibernate ORM for new application - should I be concerned? My company currently has consultants developing a Java-based application. They are using Hibernate ORM 3.5 (released 5 1/2 years ago in 2010) in this application. The back-end is SQL Server 2012. Should my company be concerned that they are using such an old version of Hibernate? Browsing the Hibernate change logs, I see various performance fixes over the years, but I don't know how much of a difference these make, and if it's worth putting pressure on the vendor to update to a newer version. As a production DBA, my main concern is performance. What do you think?

There could be several reason they continue to use an older version. It may be they've run into issues with newer versions and know to stay away from them. It could also be that they know the version they're on and can be much more efficient working in it which translates in to dollars.

I see later versions lost support for sharding and had an overhaul to services management. These may be features they lost that they like to use.

I would ask them to explain their reasoning for using the older version and then decide on what to do.

This question might get better answers on Stack Overflow since ORMs are used by software developers more than DBAs.


Create a trigger to update table data on another Server's database I am creating a trigger in MySQL and I need a little help.

I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2.

These databases have the same tables names.

I want both the user data on both the websites to be the same.

So if one user registers on S1, then that user registration information should be passed to S2.

If a user registration information is updated on S1, the same information should be updated on S2.

And the same applies for S2.

How can I create a trigger so that every time there is an insert / update / delete in database on S1, then the user table on S2 also gets automatically updated.

And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated.

Is this possible? Could you provide some examples?

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a CONTINUE HANDLER to suppress errors (isolation tolerance).

But here is an extremely simplified example.

Each server would have the identical configuration.

The local user table:

CREATE TABLE user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A local table that is federated to the user table on the other server.

CREATE TABLE remote_user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:pass@the_other_host:port/schema/user';

Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.

So, we create triggers do accomplish the purpose of updating the distance server.  They are written as BEFORE triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here... as opposed to creating a user here with what would be a conflicting username.  This is, of course, one of the tradeoff decisions you'll need to make.

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user
     SET username = NEW.username,
         password = NEW.password
   WHERE username = OLD.username;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
  DELETE FROM remote_user
   WHERE username = OLD.username;
END $$

DELIMITER ;

This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.

I use the FEDERATED engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem.  Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.

An alternative would be to configure your two servers in master/master replication.  For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other.  In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover.  Configuration would look something like this:

database_a database for site A
database_b database for site B
database_c database for only the shared table(s)

Then, in database_a and database_b:

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW user AS SELECT * FROM c.user;

MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration).  If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.

Configure the two servers to replicate everything, but set auto_increment_increment and auto_increment_offset appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment.  (Note, the documentation says that these variables are for NDB tables only, but that's not accurate).

An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.


sqlagent job - using system folder path with CmdExec I'm trying to setup a SQL Server agent job of type CmdExec for running an executable inside %programdata%\my company folder.

If I use %programdata%\my company\myexec.exe, the job fails because it apparently can't find the file.

But it works with the absolute path:

C:\ProgramData\my company\myexec.exe

How can I run the job successfully using the special folder alias?

SQL Agent doesn't shell out the command like that.  You'll need to write a batch file with the system variable in it, then call the batch file from the SQL Agent job.  The system variables don't get resolved if they are actually in the agent job step.


SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps SQL Server 2008R2
PowerShell 2.1

I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I create a CmdExec step and point to a PowerShell script file (.ps1) like this:

powershell.exe "c:\MyPSFile.ps1"

the job executes successfully (or at least gets far enough to only encounter logic or other syntax issues).

This approach won't work for a final solution, because there is a requirement to keep the PowerShell script internal to SQL. So I have a different CmdExec step that embeds the PowerShell script like so:

powershell.exe "import-module sqlps –DisableNameChecking

$server_name = "localhost"
$backup_location = "C:\BackupsGoHere"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($server_name)

# Generate an array of databases to be backed up
foreach ($database in ($server.get_Databases() | Where {$_.EstimatedSize -gt 0 -and $_.Cubes.Count -gt 0})) {
    $directory_path = $backup_location + "\" + $database.Name
    if (!(Test-Path -Path $directory_path)) {
        New-Item $directory_path -type directory | out-null
    }
    [string] $timestamp = date
    $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')
    $database.Backup("$directory_path\$database-$timestamp.abf")
}
$server.disconnect()"

However, when executed with the embedded script, the job errors out quickly with the following response:

  The specified module 'sqlps' was not loaded because no valid module
  file was found in any module directory.

Why can't I reference the module from an embedded script, but doing so in a ps1 file works just fine?

Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example:

With your code I believe you can simply configure the step as shown below:


Should I repeat UNIQUE constraint on similar multi-index? The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns.

I have MySQL/MariaDB in mind but my questions certainly applies to other DBMS.

If I have a table T with two columns id1, id2 and two index on this two columns:

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

and I want to declare to the DBMS that the multi-set on all records of the table T of all couples (value(id1), value(id2)), or equivalently all couples (value(id2), value(id1)), is "UNIQUE" (this multi-set is a set),

I can change

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

for

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

But I can also declare :

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

Is there some DBMS that deduce from

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

that the second index is also unique?

Is there some DBMS that deduce from

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

that they don't have to do twice the unicity check on inserting/updating value in the table ?

From a declarative point of view, I prefer

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

but however there may be a performance penalty on insert/update.

If there is a performance penalty for declaring UNIQUE on many indexes, will it be significant ?

Is there any (significant) performance boost for some queries with the second UNIQUE qualifiant added ?

Case 1  If you need to avoid both (1,2) and (2,1), then nothing in the Question will prevent that form of "dup".

To do that, always store the LESSER() value in id1 and the GREATER() in id2.  Then UNIQUE(id1, id2) prevents dups.

Meanwhile, do a similar sort of the two values when doing a lookup.

Then you have only the one UNIQUE key -- which may as well be the PRIMARY KEY.  Now you have

Uniqueness constraint that works
Fewer indexes
Only need to do one lookup, not two

etc

Case 2  If (1,2) and (2,1) can coexist, then the order of the ids presented to the table is important.  Probably all you need is

PRIMARY KEY(id1, id2)

If you have something like WHERE id1 BETWEEN .. AND .. AND id2 = 7, then you would also need

INDEX(id2, id1)

My Cookbook covers most of these simply possibilities.