Select most recent row by some other column I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors.  I've seen a lot of people want to do the same thing online, and a lot of different proposed solutions, but most seem overcomplicated, and I can't get anything to work properly or do what I want.

I have data in a table where a particular thing (Room) will have a name, and a most recent status (AnalogValue; it correlates to online status) with a corresponding date/time (LogTimeStanp).

I'm most interested in the current online status for each room, so I just need the most recent row for each room.

There can be a number of rooms, for an example 1 - 5,000 (there's no imposed upper limit).
There is a lot of data that pulls back when I look at the historic online
status.
There is other data in this table that I'm not interested in for this query.
I have to join tables to get a readable room name, otherwise I just have a GUID.
I'm not sure if I need to use MAX(), SELECT DISTINCT, Group by, or something else.  I have not been able to get these to work to my satisfaction.

This code does not work, but should give you an idea of what I want to do:

SELECT r.RoomName, a.AttributeID, a.AnalogValue, max(a.LogTimeStamp)
  FROM CRV_AttributeLog a join CRV_Rooms r on a.RoomID=r.RoomID
  where a.AttributeID like 'online_status'

My intent is to get the AnalogValue with the max (most recent) LogTimeStamp per individual room.

I have gotten a query to run with group by, but it doesn't seem to be grouping it (and certainly not by room name), so I guess I am doing it wrong.

Update 1, 2, 3, 4

Added pics for context.  Also, this query will form the basis of a report, so it has to be entirely referential, that's why I just want "the latest" online status.  Further complicating things, "the latest" may not have happened today, this week, or this month.  But it's in there, somewhere, sometime.

Each row in CRV_AttributeLog will have its own AttributeID and AnalogValue, and the row is made unique by the time stamp (LogTimeStamp).  Otherwise, it's entirely possible for AttributeID and AnalogValue to be matching across rows (and that's OK).  This table is logging information about when a few different somethings change: online status, if there is an error, etc.  ONLINE_STATUS can change from 0-2 (Offline, partially online, online) and will change between them periodically.  The other AttributeIDs' AnalogValues may also change back and forth depending on other conditions.

Desired Output

Room Name          AttributeID     AnalogValue     LogTimeStamp
Conference Room    ONLINE_STATUS   0               2016-06-11 21:21:25:123
Gymnasium          ONLINE_STATUS   1               2016-07-21 20:23:45:456
Boardroom          ONLINE_STATUS   2               2017-02-17 05:15:37:951
Great Hall         ONLINE_STATUS   0               2016-10-23 07:28:54:753
...

If there are more than one AnalogValue, you can get max value for each RoomId and then JOIN with CRV_AttributeLog just to fetch all attributes.

WITH maxTime as
(
    SELECT   RoomId, MAX(LogTimeStamp) AS LogTimeStamp
    FROM     @CRV_AttributeLog
    WHERE    AttributeID LIKE N'online_status'
    GROUP BY RoomId
)
SELECT  r.RoomName,
        a.AttributeId,
        a.AnalogValue,
        m.LogTimeStamp
  FROM  @CRV_AttributeLog a
  JOIN  maxTime m
    ON  a.RoomID = m.RoomID
   AND  a.LogTimeStamp = m.LogTimeStamp
   AND  a.AttributeId LIKE N'online_status'
  JOIN  @CRV_Rooms r
    ON  r.RoomId = a.RoomId;

+-----------------+---------------+-------------+---------------------+
|     RoomName    |  AttributeId  | AnalogValue | LogTimeStamp        |
+-----------------+---------------+-------------+---------------------+
| Great Hall      | online_status |      1      | 01.01.2017 20:21:00 |
| Boardroom       | online_status |      0      | 01.01.2017 21:40:00 |
| Conference room | online_status |      1      | 01.01.2017 20:20:00 |
| Gymnasium       | online_status |      2      | 01.01.2017 18:20:00 |
+-----------------+---------------+-------------+---------------------+

Check it here: http://rextester.com/HISDOM70855


SSIS Create New Folders On The Fly When Writing To A Flat File Connection I have an SSIS package that is meant to write to multiple files, which are stored like

\\server\MainFolder\DynamicField1\DynamicField2\File.csv

where the subfolders DynamicField1 and DynamicField2 are not necessarily in existence, but are coming from the variable i store for the ConnectionString.

When I run the package from my desktop, I get the message:

[File [2]] Warning: The system cannot find the path specified.

The error goes on to say:

[File [2]] Error: Cannot open the datafile "\\server\folder\abcd\efgh\filename_20190606115901.csv".

That looks like a good file name.

I tried replacing the dynamic folders with a dash so that the file name would look like:

\\server\folder\abcd - efgh - filename_20190606115901.csv

and that actually seemed to work. The problem is, there will be hundreds of files and it would be best if they could be organized in two levels of sub folders.

Does this mean that I cannot create folders on the fly?

You need to use a File System Task and set the Operation to Create Directory. Pass it a variable of the file path without the filename and it should create the directory.

See this blog article for a good example of creating folders from dynamic paths.


query to return only one kind of data I was not sure what to put in the title. I have a
SCHOOL table, with:

id,
name,
type_id

columns.

A SCHOOL_STUDENT table with:

school_id,
student_id

columns
 and a SCHOOL_TYPE table with

id,
name

columns.

Students can be enrolled in more than one schools. A school can be of only one type. Given a student I want to check if he/she is enrolled in schools with only type(high shcool), and no other schools.I want to check if the student is enrolled in schools with only type(high shcool), and no other schools. If a student is enrolled in two schools and one of them is a high school and one of them is a technology school then I want some negative indication. If both of those schools are high school , i want positive indication. Let's suppose SCHOOL_TYPE has a row with id:3 and name:"high school".

SAMPLE DATA:

SCHOOL table
id:name:type_id

1:'abc school':3
2:'xyz school':4
3:'HEH school':3

SCHOOL_TYPE table
id:name

1:'junior school'
2:'tech school'
3:'High school'

SCHOOL_STUDENT table:
school_id:student_id

1,123
3,123
6, 56

See studentID:123 is enrolled in two schools, with id:1 and id:3. And both of those schools are of type:3, that means I want a success result. If the same student is enroled in more than one school and those schools belong to other types as well (other than typeID:3) then I want a failure.

Expected output for studentID:123 is YES and for studentID:56 is NO because he is enrolled in two schools and one of them is not a high school.

I am trying this but I know it lacks something but what it lacks, I don't seem to work it out.

select
    bc.customer_id, bt.name, count(bt.[name])
from
    school_type st
    join school s on s.type_id = st.id
    join school_student ss on ss.school_id = s.id
where
    ss.student_id = 1234
group by
    ss.student_id, st.name

May be use HAVING? or CASE?

Question UPDATED:

I will try to answer to you; it's not fully clear to me , but anyway , here is my first version

create table SCHOOL (
     id int,
     name varchar(30),
     type_id int
)

create table  SCHOOL_STUDENT(
     school_id int,
     student_id int
)
create table  SCHOOL_TYPE (
   id int,
   name varchar(30)
)

insert into SCHOOL(id, name , type_id)
values(1,'abc school',3)
       ,(2,'xyz school',4)
       ,(3,'HEH school',3);

insert into SCHOOL_STUDENT (school_id, student_id)
values(1,123),(3,123),(2,56);

insert into  SCHOOL_TYPE (id,name)
values (1,'junior school'),
       (2,'tech school'),
       (3,'High school'),
       (4,'Ultra High school');

Here is my query:

select
   ss.student_id
   ,case when max(isHighSchool) = 1
         and min(isHighSchool) = 1 then 'YES' else 'NO' end as isHighSchool
from
(
    select
        ss.student_id--, st.name
        ,case when st.name = 'High school' then 1 else 0 end as isHighSchool
    from
         school_type st
         inner join school s on s.type_id = st.id
         inner join school_student ss on ss.school_id = s.id
)ss
group by ss.student_id

ouput:

student_id  isHighSchool
56          NO
123         YES

dbfiddle here


SSIS package to connect to HTTP source and download file I'm new to SSIS.  I can see "FTP Task" in the SSIS toolbox in VS2015 Community edition.  Is there an HTTP equivalent?

I have a HTTP url and some user credentials.  Shared via the HTTP url is a .sql file.  I want to login and download the file every Sunday @ 1am.

Can anyone point me in the right direction please?

Your only option is going to be calling a script. Either using PowerShell script via Execute Process task, or using C# in a Script Task to download it.

For the PowerShell script you would use Invoke-WebRequest as it supports passing in credentials. A good example of this can be found here. The example from the article:

$Credentials = Get-Credential
Invoke-WebRequest -Uri "https://www.contoso.com" -OutFile "C:\path\file" -Credential $Credentials

To keep from being prompted you can use various ways to encrypt it, just depends on if the password has to be kept secure. If not just modify it to be something like this:

$pwd = ConvertTo-SecureString "MyT0pSecr3tP@ssw0rd" -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential (“username”, $pwd)
Invoke-WebRequest -Uri "https://www.contoso.com" -OutFile "C:\path\file" -Credential $creds


Generate CSV Email For Each UserID In Table Good day all, I have a unique requirement where I need to generate a .csv file containing data for each userID.  The result set that I have returned from my select statement is

  userID    numOfSales  saleDate    meetMark
  ec12    4   2017-01-02  No
  ec12    3   2017-01-03  No
  ss33    5   2017-01-10  No

So I would want to generate 2 .csv files one for ec12 and one for ss33 and email those files to an email address.

Below is my sample DDL and query to return the above, how can I do a for each in SQL Server to generate a .csv file for each userID in the above select statement?

Create Table #bluebell
(
    userID varchar(100)
    ,numOfSales int
    ,saleDate date
    ,meetMark varchar(10)
)

Insert Into #bluebell (userID, numOfSales, saleDate, meetMark) Values
('ec12', '22', '20170101', 'Yes'),('ec12', '4', '20170102', 'No'),('ec12', '3', '20170103', 'No')
,('er11', '30', '20170101', 'Yes'), ('er11', '22', '20170103', 'Yes'), ('er11', '33', '20170109', 'Yes')
,('ss33', '40', '20170101', 'Yes'), ('ss33', '5', '20170110', 'No')

Select * FROM #bluebell where meetMark = 'No'

DROP TABLE #bluebell

The following will perform an action similar to a FOREACH cycle:

CREATE TABLE #TableOne (
    UserId VARCHAR(5),
    SalesNo INT,
    DateSale DATE,
    MarkMet BIT);

INSERT INTO #TableOne (
    UserId,
    SalesNo,
    DateSale,
    MarkMet) VALUES
('ec12', 22, '2017-01-01', 1),
('ec12', 4,  '2017-01-02', 0),
('ec12', 3,  '2017-01-03', 0),
('er11', 30, '2017-01-01', 1),
('er11', 22, '2017-01-02', 1),
('er11', 33, '2017-01-03', 1),
('ss33', 40, '2017-01-02', 1),
('ss33', 5,  '2017-01-10', 0);

DECLARE @RowCount INT = (SELECT DISTINCT COUNT(*) UserId FROM #TableOne);
DECLARE @i INT = 1;
DECLARE @UserId VARCHAR(5);
DECLARE @Temp TABLE (id INT, userid VARCHAR(5));

INSERT INTO @Temp (id, userid)
SELECT
    ROW_NUMBER() OVER(ORDER BY t.UserId) AS Id
    ,t.UserId
FROM (
    SELECT DISTINCT
        UserId
    FROM #TableOne) t;

WHILE (@i <= @RowCount)
BEGIN
    SET @UserId = (SELECT t.userid FROM @Temp t WHERE t.id = @i)

    /************************************************/
    /* Build the logic to be iterated per each user */
    /* (generate CSV, Send Mail, etc)               */
    /*                                              */
    /*    eg. SELECT *                              */
    /*        FROM #TableOne                        */
    /*        WHERE UserId = @UserId                */
    /*                                              */
    /************************************************/

    SET @i = @i + 1;
END

First it will create a temp table where to store each unique UserId and will assign them a pseudo-uid based on the ROW_COUNT, then it will iterate through this newly created table to get the UserId's one by one, at which point you'll be able to execute an action per record/UserId.


sqlserver 2008r2 service pack incompletely applied on shared features I have installed sqlserver 2008R2 service pack 3 on my instance.

The installation went through successfully but the instance still shows version number of SP1 in management studio.

When I start the patch setup again, I see that the main database features are patched correctly but that the shared features are "incompletely applied"

Can someone explain me what to do with this ?

Would a reboot help ?

After running an instance repair, the correct version number is now being displayed.

The shared features are still incompletely installed but at least querying the @@version view will now return the correct patch level.


Oracle 11g delete by unique key slow We have been plagued by a deadlock issue for quite some time that relates to deletes and inserts on the same table.  It happens sporadically, but had been increasing in frequency.  It would manifest itself as a DELETE FROM MYTABLE by unique ID statement BLOCKING INSERT INTO MYTABLE statements.

Initially, we could not see the bind variables because we were using Hibernate and we could not turn on show_sql for Hibernate since there is far too much traffic on other Hibernate-managed POJOs in other areas of the application.  We had thought Hibernate might be doing something with its cache at unexpected times.

Recently, I removed Hibernate for the table in question, replaced it with JDBC, and log EVERY touch (every SELECT/INSERT/UPDATE/DELETE) against that table.

After doing that, I've only seen 1 "blocker".  So I don't know if the problem is fixed by virtue of removing Hibernate and doing everything via JDBC.  However, I would be remiss if I didn't attempt to run down that blocker instance.

Here's what I see:

A DELETE FROM MYTABLE by unique ID took almost 10 seconds at one point.
There are no other "touches" on MYTABLE for that Unique ID on or around the time of the DELETE FROM MYTABLE by Unique ID.
That DELETE blocked other INSERT INTO statements.
The "blocker" Delete statement completed in 10 seconds and cleared itself, and after that, everything was fine.
It only happened once, the rest of the day DELETE FROM MYTABLE by Unique ID would complete sub-second, as one would expect.

I verified with our DBAs that:

We do not have any on delete triggers on the table in question.
All FKs in that table are indexed.

Does anyone have any insight/recommendations on what might cause a Delete statement by Unique ID to possibly take 10 seconds, when there is no other touches on that table for that row (no select, update, or insert by that Unique ID)?

I realize that there probably isn't a precise and exact answer to this questions, I'm just hoping someone can share insight or anecdotal information on their own experiences.

Thank you sincerely in advance.

Delete is a DML command and stores the data in redo log till the delete operation is committed.
 This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log.

So may be the instance when your operation took longer large no. of rows were being deleted to many queries might be writing to redo log.


CREATE INDEX for long columns I am looking for alternative to create index on long column:

create table line
(
    field_key   integer not null,
    value       varchar(4000),
    ...
);

create index key_value_idx on line (field_key, value);

results DB2 SQL Error: SQLCODE=-614, SQLSTATE=54008. Documentation says: The sum of the stored lengths of the specified columns must not be greater than 1024. For such cases in MySQL there is syntax:

create index key_value_idx on line (field_key, value(1000));

and HSQLDB just works without any limitations.

What is the analogue for DB2?

Here is the information on the CREATE INDEX statement for DB2. You may also want to check these links on designing indexes and space requirements for indexes as they also deal with your question on what factors into index page space.

Based on the last link it would seem to me that the page size for the index would be your column lengths + the index overhead. This may take you beyond the page size for your table. (Maybe that is what is happening here.) You could also try to place your table in a bigger tablespace. It may place the index in a bigger page size as well.

  EDIT: I have tested this locally as well to see. Using Control Center
  I was able to get a more detailed picture of the error message. Per
  DB2: "Explanation:

  The index could not be created or altered because the sum of the key
  column internal lengths would exceed the index key length limit. Also,
  an index cannot be created using a LONG VARCHAR, LONG VARGRAPHIC, or
  LOB column. If the index is defined on a typed table, there is
  additional index overhead that reduces the maximum length by 4 bytes.
  The index may be altered by an ALTER TABLE or ALTER NICKNAME statement
  that alters the data type of one or more columns.

  An index extension could not be created because the sum of the columns
  returned by the GENERATE KEY function would exceed the index key
  length limit.

  The index key length limit is based on the page size of the tablespace
  used by the index:

Max Key Length  Page size
--------------  ---------
1K              4K
2K              8K
4K              16K
8K              32K

  The statement cannot be processed. The specified index or index
  extension was not created or the table or nickname could not be
  altered.

  User response:

  To modify the index definition or alter column, eliminate one or more
  key columns and reduce the key length to the permitted maximum. For an
  index extension definition, specify a different GENERATE KEY function,
  or redefine the function to reduce the length of the returned row."

  As mentioned in my comment below your key size would be at least 32 +
  (4000 * 2 (for UTF-16)) + 2 (for varchar length identifier) + index
  overhead, so you are already at 8034+ bytes. This means just over 7K.
  I'm guessing you are not defining a tablespace, so it will default to
  USERSPACE1, which is an 8K tablespace), so you have a 2K max key length
  as shown by the table above, plus your error message). In order to make
  this key, you have to do one of two things

  Alter your table definition so it isn't so big (probably reduce your
  varchar size) so you can fit within a 1K max key definition. I'm
  guessing you don't want to do this so then you end doing the next one:
  Since your key space requires 8K key length, you need a 32K page size.
  You can leave the table in a default 4K tablespace, but the index MUST be in a 32K
  tablespace. If you are using
  automatic storage you could easily create a tablespace with the
  following:

create bufferpool BP32K immediate size 250 automatic pagesize 32K;
create large tablespace TS32K pagesize 32K managed by automatic
storage bufferpool BP32K;
create table line (
  field_key   integer not null,
  value       varchar(4000),
  ... ) index in TS32K;
create index key_value_idx on line (field_key, value);

  This will place the index in the 32K tablespace and you
  should be fine.


SQL Server: Getting string before the last occurrence '>' I have column called assocname.

Sample data:

1. FirstParty>Individual:2
2. FirstParty:3>FPRep:2>Individual
3. Incident>Vehicle:2>RegisteredOwner>Individual3

I want to get the string before the last occurrence '>'. Here is the result:

1. FirstParty
2. FirstParty:3>FPRep:2
3. Incident>Vehicle:2>RegisteredOwner

How can I do that ?

SELECT LEFT(assocname, LEN(assocname) - CHARINDEX('>',REVERSE(assocname)))

So we're reversing it, finding the first '>', then subtracting that from the length to find how far that character is from the beginning. Then take all characters up to that point.


Implement AKA in SQL Server Master Data Services I'm working with a bunch of scientists and we're trying to implement a master data solution for the ingredients they use in formulations. The problem with their historic data is Scientist #1 will use Ingredient A but call it 'Alpha 100' while Scientist #2 will use Ingredient A and call it 'A100'. We want to implement Master Data Services to make sure the way the materials are characterized scientifically is consistent and then develop a template that will force scientists to pick ingredients from a drop down. However, Scientist #1 will be looking for 'Alpha 100' and Scientist #2 will be looking for 'A100' in a dropdown, so I'm curious, is there a way to implement an AKA (also known as) solution is SQL Server Master Data Services?
I was thinking it could just be a bunch of extra attributes that can be null and then unpivot them in a view, but that doesn't feel elegant.

You would use a separate entity and a derived hierarchy.
Ingredient(Code, Name, ...)

IngredientAKA(Code, Name, Ingredient)

Where the Ingredient.Name is the "official" or "canonical" name, and IngredientAKA.Name is has all the alternate names.


How to aggregate only unique and not null values from column into a JSON array? Suppose I have a table like this:
| id | linked_id | another_id |
|----|-----------|------------|
| 1  | 1         | 1          |
| 1  | 2         | null       |
| 1  | 3         | 1          |

I would like to aggregate results by id - collect linked_ids and another_ids into a JSON objects. So in the end what I would like to get is this:
id: 1
linked_ids: [{ id: 1 }, { id: 2 }, { id: 3 }]
another_ids: [{ id: 1 }]

Notice how in the case of another_id only unique ids were aggregated and null got ignored. I'm able to achieve this with regular arrays by using:
array_agg(distinct another_id) filter (where another_id is not null).
However I need to get JSON array of objects in the end, not regular one. Any ideas?

You can use jsonb_agg() instead:
select id,
       jsonb_agg(linked_id) as linked_ids,
       jsonb_agg(distinct another_id) filter (where another_id is not null) as other_ids
from the_table
group by id;

If you want a single JSON value for each row (=group) you can use to_jsonb()
select to_jsonb(r)
from (
  select id,
         jsonb_agg(linked_id) as linked_ids,
         jsonb_agg(distinct another_id) filter (where another_id is not null) as other_ids
  from the_table
  group by id
) r

Or using jsonb_build_object()
select jsonb_build_object(
         'id', id,
         'linked_ids', jsonb_agg(linked_id),
         'other_ids', jsonb_agg(distinct another_id) filter (where another_id is not null)
       )
from the_table
group by id


SQL query for matrix multiplication I am facing a problem in writing a sql query for multiplication of two matrices.
Consider that i have a 3x3 matrices A and B, having the following schema:

A(i,j,val) and B(i,j,val) where

i int: Row index

j int: Column Index

val int: Cell value

The product of matrix A and matrix B is matrix C having cell at row i and column j. I want a single sql query to get the matrix product of A and B.

Input (A, then B):

set pages 12

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
select A.i, A.j, A.val from A union all
select B.i, B.j, B.val from B
;

         I          J        VAL
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          1          4
         2          2          5
         2          3          6
         3          1          7
         3          2          8
         3          3          9

         I          J        VAL
---------- ---------- ----------
         1          1         11
         1          2         10
         1          3          9
         2          1          8
         2          2          7
         2          3          6
         3          1          5
         3          2          4
         3          3          3

And their product (AB):

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
----------------------------------------------------------------
select
  A.i as i,
  B.j as j,
  sum (A.val * B.val) as val
from
  A, B
where A.j = B.i
group by A.i, B.j
order by
  1, 2
;

         I          J        VAL
---------- ---------- ----------
         1          1         42
         1          2         36
         1          3         30
         2          1        114
         2          2         99
         2          3         84
         3          1        186
         3          2        162
         3          3        138


get all the count of sub category from the product table in single query with mysql optimization Table structure:

    CREATE TABLE some_table (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `Product_model` varchar(255) NOT NULL,
  `product_discount` double(10,2) NOT NULL,
  `product_price` double(10,2) NOT NULL,
  `product_price_with_discount` double(10,2) NOT NULL,
  `product_quantity` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `subcat_id` int(11) NOT NULL,
  `product_color` varchar(255) NOT NULL,
  `product_short_description` text NOT NULL,
  `product_large_description` text NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  `meta_description` text NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '0=>inactive,1=>active,2=>deleted',
  `publish` tinyint(11) NOT NULL COMMENT '1=published,0=unpbulished',
  `product_feature_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1=>set_feature,0=>unset_feature',
  `product_pre_order` tinyint(4) NOT NULL COMMENT '0=>no,1=?yes',
  `pre_order_description` text NOT NULL,
  `availability` tinyint(4) NOT NULL COMMENT '0=>out of stock,1=>in stock',
  `childcat_id` varchar(100) DEFAULT NULL,
  `title` varchar(256) NOT NULL,
  `sold_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`),
  KEY `Product_model_3` (`Product_model`),
  KEY `product_name_7` (`product_name`),
  FULLTEXT KEY `product_name` (`product_name`),
  FULLTEXT KEY `product_name_2` (`product_name`,`Product_model`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `product_name_3` (`product_name`),
  FULLTEXT KEY `product_name_4` (`product_name`,`Product_model`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `Product_model_2` (`Product_model`),
  FULLTEXT KEY `Product_model_4` (`Product_model`),
  FULLTEXT KEY `product_name_5` (`product_name`),
  FULLTEXT KEY `Product_model_5` (`Product_model`),
  FULLTEXT KEY `Product_model_6` (`Product_model`),
  FULLTEXT KEY `Product_model_7` (`Product_model`),
  FULLTEXT KEY `product_name_6` (`product_name`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `Product_model` (`Product_model`),
  FULLTEXT KEY `product_short_description` (`product_short_description`,`product_large_description`)
) ENGINE=InnoDB

Query executing :

SELECT * FROM some_table where status = 1 and publish = 1 and subcat_id in(1,2,3,4,33,47)

Desired result: To get all the count of product per sub category.

Trying to avoid:

Joining the table itself as the number of rows are too much to join as execution time is too much.

Edit:

SELECT count(product_id) FROM some_table where status = 1 and publish = 1 and subcat_id in(1)

Output
114

This query works properly for the single subcat_id but how to implement for multiple subcat_id

Seems you want a simple GROUP BY:

SELECT subcat_id, COUNT(*) AS number_of_products
FROM some_table
WHERE status = 1
  AND publish = 1
  AND subcat_id IN (1,2,3,4,33,47)
GROUP BY subcat_id ;

An index on (status, publish, subcat_id) would improve efficiency of the query.


Stored procedure using cursors I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as

emp (eid, ename, age, salary)
works (eid, did, pct_time)
dept (did, dname, budget, managerid)

The procedure is compiling with errors. What am I doing wrong? Please see the procedure definition below.

create or replace procedure get_avgSalary(ddept_id in dept.did%type)
is
  cursor c1 is
    select avg(e.salary)
    from emp e, works w, dept d
    where e.eid=w.eid and d.did = w.did
    group by w.did;
  avg_sal c1%rowtype;
begin
  for avg_sal in c1 loop
    dbms_output.put_line('Average salary of the employees in department ' || ddept_id ||' is '|| avg_sal.salary);
  end loop;
end;
/

I have to use a cursor. It is part of my assignment.

You are trying to output avg_sal.salary which is not defined. Use alias in the cursor body :

.... cursor c1 is  select avg(e.salary)  AS salary ...

Side notes.
 It seems that parameter passed (ddept_id ) is not used anywhere inside the procedure except printing. Also, it's been long time since JOIN syntax was introduced and it's much more readable compared to SQL89 style (and recommended by Oracle as well). Finally, using cursor in this case seems quite questionable to me...


Helpme undestand MDX exists function After spend almost decade in OLTP world I got the courage to try to build my first CUBE.
I installed VS2012 and created Business Intelligence -> Analysis Services.
I did build Data Source Views, Cube and few Dimensions.
Everything  was perfect in EXCEL until I get requests from users to do OUTER JOIN or EXISTS filtering in my cube.
I start googling and find that there is MDX Function EXISTS which cloud do the job for me.
Since this is MDX expression I have no clue where should I write that.
In cube Measures, In dimensions attributes ,Calculated Member or in Data Source View ?

Here is my business problem .
My DSV looks like

*Business case is to make EXCEL users be able to filter OSR_DUG_IMV Dimensions and Attributes, measuring table NABAVA , for members that do not EXISTS at table IZLAZI. *

If you are browsing in Excel and want to be able to see dimension attribute members that do not have corresponding rows in a fact table (measure returns nothing), there is a pivot table setting you can use.

Access the PivotTable Options dialog box by right clicking on the pivot table and choosing PivotTable Options. On the Display tab, check the box next to Show items with no data on rows. This will show all values of whatever hierarchy is in your rows regardless of if the measure in the Values section of your pivot table returns a non-blank result.

You can do the same thing for columns by checking the item underneath the one indicated in the image. This option must be set for each pivot table and will apply to all fields in the rows (or columns) when you check the box.


How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017? SQL Server 2017 has a couple new stored procedures:

sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261), @rgCode int
sp_refresh_snapshot_views – input param for @rgCode int

And new entries in sys.messages:

10149 – Index that has SNAPSHOT_MATERIALIZATION cannot be created on view ‘%.*ls’ because view definition contains memory-optimized table(s).
10642 – SNAPSHOT_MATERIALIZATION cannot be set for index ‘%.*ls’ on ‘%.*ls’ because it is only applicable to indexes on views.
10643 – SNAPSHOT_MATERIALIZATION cannot be set for ‘%.*ls’ on ‘%.*ls’ because it is only applicable to clustered indexes on views.
10648 – SNAPSHOT_MATERIALIZATION cannot be set for partitioned index ‘%.*ls’ on ‘%.*ls’.
10649 – Nonclustered index ‘%.*ls’ cannot be created on ‘%.*ls’ that has clustered index ‘%.*ls’ with SNAPSHOT_MATERIALIZATION.
10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
3760 – Cannot drop index ‘%.*ls’ on view ‘%.*ls’ that has SNAPSHOT_MATERIALIZATION.
4524 – Cannot alter view ‘%.*ls’ because it has snapshot materialization.
4525 – Cannot use hint ‘%ls’ on view ‘%.*ls’ that has snapshot materialization before the view is refreshed.

And new Extended Events:

So how can we create a snapshot-materialized view? (Microsoft hasn't documented it yet, obviously.) Here's a gist with things I've tried so far that haven't worked.

You can't. The feature is disabled in 2017 RTM.

That said, you can...

Using AdventureWorks:

CREATE VIEW dbo.TH
WITH SCHEMABINDING
AS
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
GROUP BY P.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

Changes to the underlying tables are not immediately reflected in the view (as is normally the case with SQL Server). Likewise, data modifications against the underlying tables do not have to maintain the snapshot indexed view.

To refresh the view contents, one needs to call one of the new stored procedures:

EXECUTE sys.sp_refresh_single_snapshot_view
    @view_name = N'dbo.TH',
    @rgCode = 0; -- don't know what this is for yet

This produces the execution plan:

This likely won't work for you, because either an undocumented trace flag is needed, or you need to do the particularly nasty thing I did: writing to the memory location holding the feature flag (using a debugger) to enable this feature.

If you're curious, the feature flag is the byte at sqllang!g_featureSwitchesLangSvc+0x10f. It is checked during sqllang!SpRefreshSingleSnapshotView.

If you want to play along, and are fully prepared to accept the consequences of hacking about in SQL Server's code while it is running, and using a feature that Microsoft does not think is ready yet:

Attach a debugger to the SQL Server 2017 process. I use WinDbg.
Set a breakpoint:

bp sqllang!SpRefreshSingleSnapshotView

Resume SQL Server using the Go command (g)
Create the view above, but not the unique clustered index yet
Run the sys.sp_refresh_single_snapshot_view command above
When the breakpoint is hit, step through until you see the code line:

cmp byte ptr [sqllang!g_featureSwitchesLangSvc+0x10f (00007fff`328dfbcf)],0

The offset may be different in other builds, for example in 2017 RTM CU3 it is sqllang!g_featureSwitchesLangSvc+0x114
The memory address inside the parentheses may be different. Use the one you see.
Use the display memory command to see the current value at the memory address you found:

db 00007fff`328dfbcf L1

This should show a zero, indicating that the feature is disabled.
Change the zero to a one, using the enter values command (again with your memory address):

eb 00007fff`328dfbcf 1

Disable the breakpoint and resume running SQL Server.
The feature is now enabled.
Build the unique clustered index on the view.
Play around.

Note SNAPSHOT_MATERIALIZATION allows us to materialize a snapshot of a query specification that ordinarily could not be indexed, for example the below uses MAX:

CREATE VIEW dbo.TH2
WITH SCHEMABINDING
AS
SELECT TH.ProductID, MaxTransactionID = MAX(TH.TransactionID)
FROM Production.TransactionHistory AS TH
GROUP BY TH.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH2 (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

Result:

Commands completed successfully.


SQL Server on SAN, same LUN: one logical drive vs multiple First, let me start by saying that I did notice there are multiple similar questions to this, but neither of them is exactly what I want to ask, and not one of them has a definite answer.

Second, let me confirm that I do understand, that it's recommended to use different LUNs/spindles for log/data even on SAN/virtualized environment.

Now the question:

IF there is just one single LUN assigned to a SQL Server virtual machine, is there a performance (NOT management, security, or any other) difference between following configurations:

One virtual disk file (vhd, vmdk, whatever) with one virtual controller with one logical partition in virtual OS, and tempdb/data/log in that partition
One virtual disk file with one virtual controller but multiple logical partitions/drive letters for tempdb/data/log
Multiple virtual disk files one separate virtual controller with separate partitions for tempdb/data/log

So far I have heard the following answers:

There is no or negligible performance difference, as in the end, its still just same spindle with same IO capacity
There is a performance difference in some scenarios because virtual controllers have separate (virtual) IO queues and scheduling in the guest OS

If important, let's assume the workload is very large number of threads with very small requests (so deep small queue).

I would like to get this settled once and for all, so I would like to ask to stay on topic of performance on one LUN, and refrain from suggestions for optimizing the layout.

In your question you say "one LUN assigned to a SQL Server VM" - what you want is one LUN assigned to the hypervisor machine that can have multiple virtual machine disks for the SQL Server stored on it.  This allows the VM to use a much larger number of threads to access the disk.
In order to get the best queue depth out of VMware, specifically, you'll need to use as many separate virtual controllers, and drives as possible.  Make sure you use the para-virtualized controller, if at all possible.  Refer to this blog from VMware.
I'd put tempdb on 2 controllers (with 4 files, spread over the two controllers/LUNs).
I'd put data on a controller, and tempdb on a controller.
Virtual controllers have their own queues (just like hardware); typically at most 32.  Pretty clearly, 32 is not a lot, so having more controllers actually increases the effective queue depth.
Clearly, if you're going after performance, there are also a lot of other settings, such as CPU and Memory reservations.
The standard warning applies here; do what you have tested as being the best solution for your system.  Since we have no idea about your actual load, I/O requirements, etc, etc, it's pretty hard for us to give you a hard-and-fast rule to follow for every installation.

VMware whitepaper on pushing I/O performance is here (warning, PDF).


What is the best type of account to use for SQL services? We run stand alone instances all virtual. SQL 2012 - SQL 2017
Backups are performed by a third party tool so the server has no need to access anything on the network.

Agent jobs are all run via proxy accounts.

I've started to use virtual service accounts since i don't see the need to setup AD accounts and also Microsoft indicates to use a VSA or MSA (if network access is needed).

I just noticed today that, in my sp_Blitz results, the lowest priority issue indicates AD accounts are preferred.  Now of course it is the lowest priority, which to me is a non issue, but I'd really like to know why there is a preference (at least from Brent) to use AD accounts.

I generally start with AD accounts because someday, someone's going to want to join this server into an Availability Group. Then, they're going to wonder why they have problems with Kerberos authentication. Books Online states:

  All server instances that host an availability replica for the availability group must use the same SQL Server service account.

So I just head that off at the pass by using an AD account for any SQL Server, and then if I end up needing to add it to an AG later, all I have to do is set up the other replica with the same service account.


Slow inserts in mariadb columnstore I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with 8GB RAM. This is the table, a trigger and the timings:
MariaDB [test]> show create table analytics_test\G
*************************** 1. row ***************************
       Table: analytics_test
Create Table: CREATE TABLE `analytics_test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(50) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

MariaDB [test]> show create trigger test_trg\G
*************************** 1. row ***************************
               Trigger: test_trg
              sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg before insert on analytics_test for each row
begin
  set new.str=concat('Value: ',new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2021-01-07 11:14:28.81
1 row in set (0.000 sec)

MariaDB [test]> insert into analytics_test set id=1;
Query OK, 1 row affected (0.817 sec)

MariaDB [test]> insert into analytics_test set id=2;
Query OK, 1 row affected (0.560 sec)

MariaDB [test]> insert into analytics_test set id=3;
Query OK, 1 row affected (0.611 sec)

MariaDB [test]> select * from analytics_test;
+------+----------+
| id   | str      |
+------+----------+
|    1 | Value: 1 |
|    2 | Value: 2 |
|    3 | Value: 3 |
+------+----------+
3 rows in set (0.085 sec)

I think .5 sec for a simple insert is very slow - compare to the same table in innodb:
MariaDB [test]> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

MariaDB [test]> show create trigger test_trg1\G
*************************** 1. row ***************************
               Trigger: test_trg1
              sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg1 before insert on test for each row
begin
  set new.str=concat('Value: ',new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2021-01-07 11:45:07.85
1 row in set (0.000 sec)

MariaDB [test]> insert into test set id=1;
Query OK, 1 row affected (0.010 sec)

Is there anything I need to do in order to make inserts perform better?

Column-oriented database engines, not just MariaDB ColumnStore, are specifically optimized for read operations on large volumes of data. For this they use auxiliary data structures (like ColumnStore extent maps), query parallelism, compression etc. This comes at the cost of increased overhead for standard DML operations. This is often explicitly stated in various technical documents, like this quote from the InfiniDB technical overview:

Note that because InfiniDB is a column-oriented database, insert and delete statements will not normally run as fast on InfiniDB as they will on row-oriented databases (more blocks must be touched).

or this from the MariaDB architecture presentation (see slide 22):

DML Writes

Slow compared to other engines

INSERT is very slow compared to cpimport

Vendors often provide special tools for bulk loading data into columnar tables (e.g. the cpimport utility mentioned above) to address this problem. This fits well into the typical columnar engine use case, that of an OLAP system with (relatively) infrequent mass updates and heavy read-only workloads.
In other words, you should not judge a column-oriented database by its DML performance, because that's not what it is designed for.


How to use an array as argument to a VARIADIC function in PostgreSQL? I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.

I would like this to be a simple wrapper around the built-in function, the only difference being that it accepts citext as the first parameter instead of json. I want this to be a straight pass-through to the native implementation, just with a type conversion beforehand. Here's what I have so far:

create extension citext;

create or replace function json_extract_path_text ( string citext, variadic params text[]) RETURNS text IMMUTABLE AS
$$
    BEGIN
        SELECT json_extract_path_text(string::json, params);
    END;
$$
LANGUAGE 'plpgsql';

This doesn't work properly, however, because of the type mis-match:

ERROR:  function json_extract_path_text(json, text[]) does not exist
LINE 1: SELECT json_extract_path_text(string::json, params)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT json_extract_path_text(string::json, params)
CONTEXT:  PL/pgSQL function json_extract_path_text(citext,text[]) line 3 at SQL statement

I've tried hacking around a solution using dynamic string construction and EXECUTE, but this is a real hassle and I feel like there must be a cleaner way to pass VARIADIC params through to the inner function. I can't see any obvious way to do so, however. How can I accomplish that?

Pass the array as is using the VARIADIC key word in the call:

CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[])
  RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT json_extract_path_text(string::json, VARIADIC params)';

Call:

SELECT json_extract_path_text('{"f1":{"f2":1,"f3":"foo"}}', VARIADIC '{f1, f3}');

 json_extract_path_text
----------------------
 foo

The manual on VARIADIC:

  Sometimes it is useful to be able to pass an already-constructed array
  to a variadic function; this is particularly handy when one variadic
  function wants to pass on its array parameter to another one. You can
  do that by specifying VARIADIC in the call:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

More details:

Select rows such that names match elements of input array for pgsql function

To be clear: Inside the function, the VARIADIC parameter is just another array, nothing special about it. VARIADIC being used in two separate function definitions makes the case a bit confusing. The solution is to use the same keyword VARIADIC for a related but different purpose in the function call. Don't get confused even more.

Aside: Do not quote the language name, it's an identifier.

Case-insensitive version with citext?

  I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.

While the above function works, it's not case-insensitive at all. citext preserves original input, which is restored with the cast to text (or json) and can be mixed-case.

I would not use citext to begin with. Various corner case problems:

Index on column with data type citext not used

For your purpose I suggest calling the built-in json_extract_path_text() with lower(citext_value), which returns lower-case text, and lower-case the 2nd parameter ("path elements") as well, to make it actually case-insensitive:

SELECT json_extract_path_text(lower('{"F1":{"f2":1,"f3":"foo"}}'::citext)::json
                            , VARIADIC lower('{f1, F3}')::text[]);

Note the cast to text[] after lower().


How can I tune MySQL to behave better under load We have a typical master-slave set up. When we find ourselves under considerable load, the master grinds to a halt. The slave continues to march along all happy like.

So during a recent slow-down, I took a VERY simple query (no joins, nicely indexed, not many results) and ran it on the slave and master, with profiling turned on.

Notice how different the results of the profiling are.

Master

Slave

I fear someone might tell me: "Of course your database will perform worse when it's under heavy load." And to them, I say: "You're right!"

But I am really curious if the results from the profile would suggest anything like:

Your DB server needs more memory, or
It's writing to disk too much, or
Change this setting and everything will be fixed, or
...

EDIT 1
The following graph shows how queries slow down when we're having a problem. The gold line is average write time and the green line is average read time. As you can see writes get really slow. The average write peaks at around 500 ms. When things are running smoothly, average writes peaks are down around 2ms.

If you stay with MySQL 5.1
Since you cannot upgrade at this time, you need to install the InnoDB Plugin. It was available since MySQL 5.1.38 (See the Release Notes). I wrote a post about it : MySQL - Installing InnoDB Plugin
Surprising fact: Percona already ahd the InnoDB in Percona Server 5.0.45 long before
Once you do, you need to set the following

Set innodb_read_io_threads
Set innodb_write_io_threads
Set innodb_thread_concurrency to 0

You can increase the innodb_buffer_pool_size, but do not exceed half the installed RAM. Set it to 20G.
When you upgrade to MySQL 5.6
#1: Tune Read and Write I/O
InnoDB has new options to engage multiple CPUs. I wrote about this over the past 3 years

Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
May 07, 2012 : MySQL Server Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

#2: Partition the InnoDB Buffer Pool
Since MySQL 5.5, you can partition the InnoDB Buffer Pool to reduce I/O thread contention and swapping

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

#3: You can snapshot the InnoDB Buffer Pool to Disk
You can use the following

innodb_buffer_pool_dump_at_shutdown : MySQL dumps the map of the Buffer Pool on Shutdown
innodb_buffer_pool_load_at_startup : MySQL load the Buffer Pool on Startup
Setup a script to run SET GLOBAL innodb_buffer_pool_dump_now= 1; in a crontab job to dump the Buffer Pool map every 5 minutes or so. See innodb_buffer_pool_dump_now

There is so much more I could write, but I'll stop here.
CAVEAT : Why is this tuning necessary ?
See my post Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
I have mentioned the Plugin for InnoDB Before : Performance settings for MyISAM tables - keep everything in memory
GIVE IT A TRY !!!


Scripting drop/create of dependencies when altering view with schemabinding In SQL Server 2017 I have a view, "ParentView" which is referenced by other views "ChildView1", "ChildView2", etc.  Those views are referenced by other views "GrandChildView1", "GrandChildView2", etc. and so on and so forth.  I make sure to setup all of my views to have "WITH SCHEMABINDING".  When I want to make a change to "ParentView" I'm required to drop and create all of the dependent views.  This, of course, makes perfect sense because I have "WITH SCHEMABINDING" on and I could be making a change that could break one of the dependent views.  However, doing all of this is tedious through the user interface so I was wondering...

How can I get a script that takes a view or any object as input and creates a drop/create statement based on dependencies?

For example:

DROP VIEW [dbo].[GrandChild]
GO
DROP VIEW [dbo].[Child]
GO
ALTER VIEW [dbo].[Parent]
...
GO
CREATE VIEW [dbo].[Child]
...
GO
CREATE VIEW [dbo].[GrandChild]
...
GO

Notes

This doesn't have to be specific to views.  I also have dependent functions and stored procedures as well.
None of my views are indexed views.

I've found a way to do this by using a couple of different scripts.

First, I needed to setup the "PrintString" stored procedure from Greg M. Lucas in order to print all of my statements without them being truncated.

CREATE PROCEDURE [dbo].[PrintString]
(
  @InputString     nvarchar(max)  = NULL
, @MaxPrintLength  int            = 4000
)
AS
BEGIN
    SET NOCOUNT ON
    --! CONSTANTS (keep it SQL2005 compatible)
    DECLARE @LF          char ( 1 ); SET @LF         = CHAR(10);
    DECLARE @CR          char ( 1 ); SET @CR         = CHAR(13);
    DECLARE @CRLF        char ( 2 ); SET @CRLF       = CHAR(13) + CHAR(10);
    DECLARE @LINE_BREAK  char ( 3 ); SET @LINE_BREAK = '%' + @LF + '%';
    --! Working Values
    DECLARE @WorkingLength    bigint
    DECLARE @WorkingString    nvarchar  (  max )
    DECLARE @SubString        nvarchar  (  max )
    DECLARE @SubStringLength  bigint
    --! Validate/correct inputs
    SET @MaxPrintLength = COALESCE(NULLIF(@MaxPrintLength, 0), 4000)
    IF @MaxPrintLength > 4000
        BEGIN
            RAISERROR('The @MaxPrintLength value of %d is greater than the maximum length supported by PRINT for nvarchar strings (4000)', 17, 1, @MaxPrintLength);
            RETURN(60000);
        END
    --! Working variables
    DECLARE @InputLength bigint = LEN(@InputString)
    IF @InputLength = 0
        GOTO OnComplete;
    --!
    --! Our input string may contain either carriage returns, line feeds or both
    --! to separate printing lines so we need to standardise on one of these (LF)
    --!
    SET @WorkingString = REPLACE(REPLACE(@InputString, @CRLF, @LF), @CR, @LF);
    --!
    --! If there are line feeds we use those to break down the text
    --! into individual printed lines, otherwise we print it in
    --! bite-size chunks suitable for consumption by PRINT
    --!
    IF PATINDEX(@LINE_BREAK, @InputString) > 0
        BEGIN --[BREAK_BY_LINE_FEED]
            --! Add a line feed on the end so the final iteration works as expected
            SET @WorkingString  = @WorkingString + @LF;
            SET @WorkingLength  = LEN(@WorkingString);
            DECLARE @LineFeedPos    bigint    = 0
            WHILE @WorkingLength > 0
                BEGIN
                    --!
                    --! Get the position of the next line feed
                    --!
                    SET @LineFeedPos = PATINDEX(@LINE_BREAK, @WorkingString);
                    IF @LineFeedPos > 0
                        BEGIN
                            SET @SubString        = SUBSTRING(@WorkingString, 1, @LineFeedPos - 1);
                            SET @SubStringLength  = LEN(@SubString);
                            --!
                            --! If this string is too long for a single PRINT, we pass it back
                            --! to PrintString which will process the string in suitably sized chunks
                            --!
                            IF LEN(@SubString) > @MaxPrintLength
                                EXEC [PrintString] @InputString = @SubString
                            ELSE
                                PRINT @SubString;
                            --! Remove the text we've just processed
                            SET @WorkingLength    = @WorkingLength - @LineFeedPos;
                            SET @WorkingString    = SUBSTRING(@WorkingString, @LineFeedPos + 1, @WorkingLength);
                        END
                END
        END --[BREAK_BY_LINE_FEED]
    ELSE
        BEGIN --[BREAK_BY_LENGTH]
            --!
            --! If there are no line feeds we may have to break it down
            --! into smaller bit size chunks suitable for PRINT
            --!
            IF @InputLength > @MaxPrintLength
                BEGIN
                    SET @WorkingString    = @InputString;
                    SET @WorkingLength    = LEN(@WorkingString);
                    SET @SubStringLength  = @MaxPrintLength;
                    WHILE @WorkingLength > 0
                        BEGIN
                            SET @SubString        = SUBSTRING(@WorkingString, 1, @SubStringLength);
                            SET @SubStringLength  = LEN(@SubString)
                            --!
                            --! If we still have text to process, set working values
                            --!
                            IF (@WorkingLength - @SubStringLength + 1) > 0
                                BEGIN
                                    PRINT @SubString;
                                    --! Remove the text we've just processed
                                    SET @WorkingString    = SUBSTRING(@WorkingString, @SubStringLength + 1, @WorkingLength);
                                    SET @WorkingLength    = LEN(@WorkingString);
                                END
                        END
                END
            ELSE
                PRINT @InputString;
        END --[BREAK_BY_LENGTH]
--/////////////////////////////////////////////////////////////////////////////////////////////////
OnComplete:
--/////////////////////////////////////////////////////////////////////////////////////////////////
    SET NOCOUNT OFF
    RETURN
END
GO

Second, I created a temporary table to store all of the dependent entities so I can use it later to print my script.  Included in the temporary table is an "Order" column for doing descending order on the drop statements and ascending order on the create statements.

CREATE TABLE #DependentEntitiesInformation
(
    ObjectId INT,
    SchemaName NVARCHAR(MAX),
    EntityName NVARCHAR(MAX),
    ObjectDefinition NVARCHAR(MAX),
    [Type] NVARCHAR(MAX),
    [Order] NVARCHAR(100)
)
GO

Third, I fill my temporary table using a recursive CTE.  In this example my parent view is called 'dbo.Project_Expanded'.

SET NOCOUNT ON;

DECLARE @ObjectSchemaAndEntity NVARCHAR(MAX) = 'dbo.Project_Expanded';

WITH DependentEntities (ObjectId, SchemaName, EntityName, ObjectDefinition, [Type],[Order])
AS
(
    SELECT
        e.referencing_id,
        e.referencing_schema_name,
        e.referencing_entity_name,
        OBJECT_DEFINITION (e.referencing_id),
        ao.[type],
        CAST(ROW_NUMBER() OVER(ORDER BY e.referencing_id) AS NVARCHAR(100))
    FROM
        sys.dm_sql_referencing_entities (@ObjectSchemaAndEntity, 'OBJECT') e INNER JOIN
        sys.all_objects ao ON ao.[object_id] = e.referencing_id
    UNION ALL
    SELECT
        e.referencing_id,
        e.referencing_schema_name,
        e.referencing_entity_name,
        OBJECT_DEFINITION (e.referencing_id),
        ao.[type],
        CAST(de.[Order] + '.' + CAST(ROW_NUMBER() OVER(ORDER BY e.referencing_id) AS NVARCHAR(100)) AS NVARCHAR(100))
    FROM
        DependentEntities de OUTER APPLY
        sys.dm_sql_referencing_entities (de.SchemaName + '.' + de.EntityName, 'OBJECT') e INNER JOIN
        sys.all_objects ao ON ao.[object_id] = e.referencing_id
)
INSERT INTO
    #DependentEntitiesInformation
SELECT
    ObjectId,
    SchemaName,
    EntityName,
    ObjectDefinition,
    [Type],
    [Order]
FROM
    DependentEntities
GO

Fourth, I create my drop statements and then my create statements using my temporary table and I copy out the results.  Note that my version only handles inline table-valued functions, procedures, and views.  You will need to update it if you need to handle more object types.

DECLARE @StatementEnd NVARCHAR(4000) = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);

DECLARE @DropStatements NVARCHAR(MAX) =
    (
        SELECT
            STRING_AGG(
                'DROP ' +
                CASE
                    WHEN [Type] = 'IF' THEN 'FUNCTION'
                    WHEN [Type] = 'P' THEN 'PROCEDURE'
                    WHEN [Type] = 'V' THEN 'VIEW'
                END +
                ' [' + SchemaName + '].[' + EntityName + ']',
                @StatementEnd
            ) WITHIN GROUP (ORDER BY [Order] DESC)
        FROM
            #DependentEntitiesInformation
    ) + @StatementEnd;

EXEC @DropStatements = [dbo].[PrintString] @DropStatements, 4000

DECLARE @CreateStatements NVARCHAR(MAX) =
    (
        SELECT
            STRING_AGG(ObjectDefinition, @StatementEnd) WITHIN GROUP (ORDER BY [Order])
        FROM
            #DependentEntitiesInformation
    );

EXEC @CreateStatements = [dbo].[PrintString] @CreateStatements, 4000

DECLARE @Statement NVARCHAR(MAX) = @DropStatements + @CreateStatements;

EXEC [dbo].[PrintString] @Statement, 4000
GO

Finally, I cleanup the temporary table and drop the "PrintString" procedure:

DROP TABLE #DependentEntitiesInformation
GO

DROP PROCEDURE [dbo].[PrintString]
GO


Reducing CREATE TABLE overhead? I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s.

When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM table;, the query now takes 18s.

I could not see any notable difference between the plan.

What are the steps I can take to minimize this 8s overhead? I have already tried using UNLOGGED keyword.

I could not see any notable difference between the plan.

That's because there isn't any.
Table Scanning is Table Scanning.  As Lewis Carroll's King of Wonderland said:

  Begin at the beginning and go on till you come to the end: then stop.

The difference is in what's being done with the data:

reading the data

... takes 10 seconds.

Creating a new table based on the structure of the first table,
reading the data, and
writing the data into the new table

... takes 18 seconds.

Spotted the difference?

Reading stuff in a database is [usually] quick.  It's often just floating around in the Buffer Cache and can be quickly sent on its way.

Writing stuff in a database is slower.  It [usually] requires logging into the Transaction log first, then writing to the Buffer Cache and flushing out to disk (at some point).  It all takes time.

Consider:

Why are you creating a copy of the table at all?  If the second table contains everything in the first one, then why not just use the first one?
Do you really want all the rows?  If you're reading everything, then there's not much you can do to speed things up.  No amount of indexing can speed up a sequential Table Scan.
Do you really want all the columns?  If you're only interested in three, little, columns but are retrieving a hundred columns, full of massive BLOB data, then things will get a bit slow.


How can I find out that a publication has "replicated schema changes" enabled? Where can I find out (preferably using T-SQL) within a published databases, if it would replicate the schema changes?

here is how to create a publication

This topic describes how to create a publication in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

Replicate Schema Changes

Run this stored procedure at the Publisher:

sp_helpmergepublication

And check this column:

replicate_ddl

A value of 1 means that schema changes are replicated


Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11) The ORACLE SQL manual (Page 2-58 of the main 11.1 SQL Manual) states:

  The total length of a datetime format model cannot exceed 22 characters.

However, I've been able to use a format string for TO_CHAR with many more characters, almost all of which are text "literals".

Is this safe (ORACLE 11)? (Perhaps the limit does not include non-date literals?)

For example:

SELECT TO_CHAR(sysdate,
       '"SOMESTRING_"YYYYMMDD"_SOME LOTS MORE STRINGS OF A FILENAME PREFIX"')
FROM DUAL;

Result:

SOMESTRING_20200226_SOME LOTS MORE STRINGS OF A FILENAME PREFIX

It also works with dates from tables.

The reason I want to do this is to build a filename template in a database column; otherwise I will need to put the date format in a separate field.

P.S. Please tell me if this should be asked in a different community. I've never asked a question before, so I have no reputation to lose :-). Be nice.

Just because you can jump off a cliff, doesn't mean you should.

Use REPLACE() with a token:

select REPLACE('SOMESTRING_##DATE##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX',
               '##DATE##',
               TO_CHAR(sysdate,'YYYYMMDD'))
from DUAL;

You could take this a step further to allow different format strings:

with mytable as (
select 'SOMESTRING_##YYYYMMDD##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX' as format_string
from DUAL
)
select REGEXP_REPLACE(format_string,'##(.*)##',TO_CHAR(sysdate,replace(
                    regexp_substr( format_string, '##(.*)##', 1, 1),'##','')))
  from mytable;

Note that there have been parsing bugs with long TO_CHAR identifiers that have caused ORA-600 errors & session crashes.


Referencing two documents to each other Is it good practice and or recommended to reference 2 documents to each other using ref? Like this:

const FirstDocumentId = mongoose.model(
    'FirstDocumentId',
    new mongoose.Schema({
        coolField: String,
        secondDocumentId: {type: Mongoose.Schema.Types.ObjectId, ref: 'secondDocumentId'}
    })
);

const SecondDocumentId = mongoose.model(
    'SecondDocumentId',
    new mongoose.Schema({
        someField: String,
        firstDocumentId: {type: Mongoose.Schema.Types.ObjectId, ref: 'firstDocumentId'}
    })
);

All depends on their relationship. However, the way you describe it implies that this is a 1-to-1 relationship, since each schema refers to the other. Therefore, why not just combine the schemas?


Can moving tables to another MySQL database help performance? I want to know if I using different databases can help performance.

I have one big table support_chat with more than 5 million rows. I need to scan this all the time (3s with ajax) to check new messages on support.

If I move this table to another database in the same computer, can it help anything? (let users, sells, comments table faster)

For example:

db1

users table
sells table
comments table
support_chat table // move this table to db2

db2

support_chat table

They want to keep old things due legal reasons, to have a user support history.

Generally, no. A few tangentially related things to note though:

You could use a symlink to move the /<datadir>/db2/ directory to another storage/block device. Or you could use the CREATE TABLE ... DATADIR=/../ option to move only the support_chat table to another storage/block device. That could make a big difference, assuming that you're frequently disk bound.
With MySQL 5.6, using multiple databases/schemas can help to improve slave throughput (assuming you're using replication) because the parallelization was at the schema level:

Replication Slave Options and Variables --slave-parallel-workers

So with three databases/schemas, you would benefit from setting slave-parallel-workers=3.

For more info on the multi-threaded slave work in MySQL 5.6:

MySQL Replication High Performance: Multi-Threaded Slaves and
Group Commit (pdf)

Just FYI, the multi-threaded slave behavior has been greatly improved in MySQL 5.7 too, the biggest example being that you can now instead use (Lamport) logical clock based parallelization:

Replication Slave Options and Variables --slave-parallel-type


The service broker is administratively disabled I have just restored some databases what use service broker from LIVE to staging.

it is not working and when I put a profile trace to run it only shows messages related to database id 1 and 4, something is wrong but I am struggling to identity what is causing this behaviour.

I got it fixed after reviewing and resetting all broker related database IDs.

First thing I did was to see what we have (on both source and target servers):

--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled]
 ,[service_broker_guid]
FROM [sys].[databases]
WHERE 1=1
  AND is_broker_enabled = 1
ORDER BY  NAME
GO

SELECT name,is_broker_enabled,service_broker_guid, is_trustworthy_on FROM sys.databases
order by 2 desc, 3 desc,  1

I saw that the broker id needed changed:

--CD718202-CB34-4DCD-BADC-7800C5F2FF3D
  ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
  ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
  ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate

I am sorry for the trustworthy, this is how it has been set up and I never changed it.

on the target server:

on the source server:

so i have changed inside the routers the service broker ids.

--targer server
USE [cola]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')
DROP ROUTE [rou_ORCARoute]

CREATE ROUTE [rou_ORCARoute]
WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,
BROKER_INSTANCE  = N'CD718202-CB34-4DCD-BADC-7800C5F2FF3D' ,  --BROKER ID OF ORCASTG
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server
GO

--source server
USE [ORCASTG]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_CAUKRoute')
DROP ROUTE [rou_CAUKRoute]

CREATE ROUTE [rou_CAUKRoute]
       WITH  SERVICE_NAME  = N'svc_receiveStatusChangeService' ,
       BROKER_INSTANCE  = N'452711F3-60CD-47E6-BFA4-3BD8C9D6AB56' ,   --BROKER_ID OF COLA
       ADDRESS  = N'TCP://mytargetserver.mycompany.com:4022' --IP address of my target server
GO

even after putting the correct boker_ids for the databases in the the routers above, still I was not getting any communication going through.

I had to review the the routers, I noticed I was using ip addresses in place of the server names, and that was working fine, but we did some changes on our domains and that was no longer working, so using the test below, I first identified it was not longer working (the connectivity), then changed it accordingly using the server names.

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection 200.1.0.198.:4022'
 EXEC xp_cmdshell @sql

After those changes, all started to work fine.


MySQL syntax for order by not accepted in SQL Server I'm working through the SQL snippets for nested sets in this article by Mike Hillyer.

The article is using MySQL, but I'm using SQL Server.  I get the following error reported when attempting to execute the query that should return the immediate subordinates of a node.

  The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

What do I need to change to get this query to work on MSSQL?

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

Your [sub_tree] derived table is using an ORDER BY with no TOP operator.

Try something like the following:

    SELECT TOP (1) node.name
    [...]
    ORDER BY node.lft
) AS sub_tree

Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use INNER JOIN, LEFT JOIN, etc rather than FROM a,b,c WHERE a.id =b.id.


Shrink SQL Server DB By xgb I'm trying to free up some space in a database, firstly by swapping out and archiving partitions. The DB is massive though, and i want to start freeing up some space on the drive, but I don't want to completely shrink the DB as it will take weeks.

Is it possible to shrink a SQL Server DB by say 10gb?

Thanks all!

CM

Yes, you can, but it is not a good idea unless you are 100% certain the database won't grow again. If it will, then shrinking the file to free up space is like vacuuming while confetti is still falling on your carpet, and you should really read one of the many posts out there on how futile it is to shrink files that are just going to grow again. Nothing is gained except your free space % looks a little better for a little while. Big whoop! Do you get a bonus for how much free space is on your disk, even if you can't sustain it?

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Why Does the Transaction Log Keep Growing or Run Out of Space?

Now, if you really, really, really want to shrink your files, even after reading all of the above material and refusing to heed our warnings, keep reading.

If you want to shrink the data file by 10GB, look at the size it is now, and then use DBCC SHRINKFILE (not DBCC SHRINKDATABASE) and set a size 10GB smaller than that. Look at the size column from sp_helpfile - let's say the file is now 41 GB, and you want it to shrink to 31 GB, then you would say:

DBCC SHRINKFILE(filename, 31000); -- 31,000 MB

You can probably do better math than that (I know 10,000 MB is not exactly 10 GB but you get the idea).


MongoDB: Grant roles to multiple databases based on a pattern Is it possible to grant database access to a user based on a prefix?

For example in MYSQL you can do the following:

GRANT ALL PRIVILEGES ON  `username\_%` . * TO  'username'@'localhost';

In my use-case I would like to grant the readWrite role a specific user on a database of the same name -- but also grant the ability to create a database matching this pattern, and give read/write access to it.

So for example, myuser would have the readWrite role on myuser database, but also have the ability to create myuser-otherdatabase.

Is this possible without giving server-wide roles (readWriteAnyDatabase) to this user for this purpose?

The application using this user account needs to be able to create prefixed databases to segment out data, and it should be able to do this on the fly, rather than having to have an administrator grant roles to the additional databases.

Any thoughts?

Resource document for the latest mongo has db and test attributes which can be only fixed strings.

I have tried with various wildchar: *, ., % also escaping but with no luck.


How to script XML Schema collection in SQL Server 2012? I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string.

I need to do this to compare current schema collections with the new schema collections after it is regenerated.

I have no idea where to start!

If I understand the Question correctly, you need to use the XML_SCHEMA_NAMESPACE built-in function to extract the Schema Collection, and you can find the list of those in the sys.xml_schema_collections system catalog view. You can use something along the lines of:

SELECT CONVERT(NVARCHAR(MAX), XML_SCHEMA_NAMESPACE(sch.[name], xsc.[name])) AS [XSD]
FROM   sys.xml_schema_collections xsc
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = xsc.[schema_id]
WHERE  xsc.[xml_collection_id] <> 1;

I put the WHERE  xsc.[xml_collection_id] <> 1 filter in there to avoid getting the following error:

  Msg 6314, Level 16, State 1, Line 1
  Collection specified does not exist in metadata : 'sys'


MySQL settings useful to speed up a mysqldump import Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server.  The import took around 7 hours on a mono-core CPU with 1 Gb of RAM.

Someone else tested the import on a MySQL server which has, amongst others, the following settings:

innodb_buffer_pool_size = 8G
query_cache_size = 300M

I'm a bit skeptical about the relevancy of these settings (and yes, I even think that setting such a large query cache is bad). Would that make a difference? Aren't these settings used only when querying the database, and hence irrelevant for an import?

If yes, which settings should be set to speed up the import of a large dump file?

According to the official documentation these values should be set temporarily:

unique_checks = 0
foreign_key_checks = 0

I've read here that it should be set also

innodb_flush_log_at_trx_commit = 2

but I don't think it would help, because autocommit mode (flushing logs to disk for every insert) is already disabled by default in the mysqldump command (--opt option).

SUGGESTION #1

No need to run unique_checks = 0 and foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump / reload (ASPECT #2 shows a standard header of a mysqldump. Lines 13 and 14 handle the disabling of those checks for you)

SUGGESTION #2

Please note the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

If you want to reload a MySQL Instance you should temporarily disable the Double Write Buffer.

STEP #1

Login to the Target Server and run

SET GLOBAL innodb_fast_shutdown = 0;

STEP #2

Restart mysqld by setting to innodb_doublewrite to OFF

service mysql restart --innodb-doublewrite=OFF --innodb-fast-shutdown=0

STEP #3

Load the mysqldump into the Target Server

STEP #4

Restart mysqld normally (Double Write buffer will be enabled again)

service mysql restart

Since the name "Double Write Buffer" implies two writes, InnoDB will only write data and indexes straight to the table files and bypass writing to the Double Write Buffer within ibdata1. Maybe this will double the import time (pun intended)

SUGGESTION #3

The default innodb_log_buffer_size is 8M. You need a bigger Log Buffer.

Please add this line to my.cnf under the [mysqld] group header

[mysqld]
innodb_log_buffer_size = 128M

Then, restart mysqld before the reload of the mysqldump.

GIVE IT A TRY !!!


Is it better to have one instance of a continuously running agent job or to fire off the job multiple times throughout the day? I have a SQL query I need to run every second for about 12 hours a day.
It's a pretty simple query that logs some data to an isolated table.
Right now I've been leaving it running in my local SSMS client inside a WHILE loop and I use WAITFOR DELAY '00:00:01' to pause between each iteration for 1 second.

If I want to move this into an agent job, is it better to schedule the job to run every 1 second over those 12 hours, or keep the query as is and have the job fire once in the morning, so that the query manages how often it runs.
(One thing to keep in mind, is usually the query is fast enough such that it's runtime is sub-second, but rarely it can take a few seconds to finish executing.)

I guess the root question is, is it better to have a job that is continuously running for 12 hours, or a job that runs about 43,000 times (once per second for 12 hours)?

There is a certain amount of overhead each time a job starts--reads and writes on msdb to track the state and schedule calculation, create a session, etc.  So from an efficiency standpoint, it would be better to run it in a loop.  This will also make it run more closely to every second, because when a job starts, it takes some time for the job to actually get to the point of executing code.  So if anything results in a random .25 second delay in the job actually getting to the point of executing the code, then you may have a significant gap between executions of the code.  I think (someone please correct me if I'm wrong) that it has to go through the entire login process, so a delay could be caused in the authentication process due to network issues.

The downside of running it in a loop is that you'll have to handle any errors in the code.  If the job runs every second, you can configure a failure notification on the job, and if there are problems you'll get an email.  If it runs in a loop, you'll need handle errors and notification in the code, and also monitor the job to ensure that it is running.


What to do when your Always On cluster loses quorum? I was reviewing our company DR procedures and when I looked online for solutions to an Always On Cluster losing quorum, to compare to.  I was three pages into google results before finding the first SE post on the subject Clustering vs. transactional replication vs. availability groups which only lightly touches on the subject of lost quorum.

While everyone agrees the losing quorum is bad, and there are some suggestions for decreasing the potential, it can still happen.  I am looking for a good peer reviewed answer to the best path to recovery from an Always On cluster loss of quorum.

AGs are based on Windows Clustering. The WSFC procedures for quorum loss apply.

WSFC Disaster Recovery through Forced Quorum
Force a WSFC Cluster to Start Without a Quorum

Once the WSFC is running, you can then force AG, if needed. Perform a Forced Manual Failover of an Availability Group:

  After forcing quorum on the WSFC cluster (forced quorum), you need to force failover each availability group (with possible data loss). Forcing failover is required because the real state of the WSFC cluster values might have been lost. However, you can avoid data loss, if are able to force failover on the server instance that was hosting the replica that was the primary replica before you forced quorum or to a secondary replica that was synchronized before you forced quorum. For more information, see Potential Ways to Avoid Data Loss After Quorum is Forced.


Forcing a hash join on a temporary table generates error I have the following query:

select
    afp.period_start
    , afp.period_end
    , COUNT(*)
from services s
    Left join
        (
            select entityId, resolutionDate
            from ...
        ) SI on si.entityId = s.Id
    left join #aff_periods afp -- or left hash join #aff_periods afp
        on isnull(si.reslutionDate, GETDATE()) <= afp.period_end
group by afp.period_start
    , afp.period_end
option (hash join); -- or this

Whenever adding either the query-level hint or join-level hint I get the following error.

  Msg 8622, Level 16, State 1, Line 50

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I've read some posts online suggesting that this might be because of a index on the table, so I dropped the non-clustered index I had created on that table and tried to re-run the query but I still get the error.

Any suggestions what I could try or why this is happening for a temp table?

What you're running into isn't because of the temp table, it's because of the type of join you've chosen with the hint you're forcing.

Both Merge and Hash joins require at least one equality predicate to run.

For more information, check out these old (but awesome) blog posts from Craig Freedman.

Introduction to Joins

Nested Loops Join

Merge Join

Hash Join


Microsoft SQL Server -> Postgres Data Migration I have data in a Microsoft SQL Server that needs to be migrated to Postgres. The problem is that the SQL Server gets continuous updates and is hosted on our local net, while the Postgres instance is in the cloud.

What is the best way to manage this ETL process? My current approach is to just transfer over the tables we need (about 30) and then re-initialize the materialized views. I am willing to pay for a migration tool if it can fully automate the process, otherwise I'll go with the simplest solution our bandwidth can support.

Thanks!

There's no need for a commercial product. You could make use of for example Pentaho Data Integration to do the task.

Back to the main question, here are the steps you could do to feed your data without recreating the tables every time:

Create a capture data change mechanism (it works as a table), to log changes on tables (which table has been inserted with which record (id) etc)
Update your destination data with changes logged in CDC mechanism.

This kind of process is broadly implemented in Data Warehousing to migrate changes.

If this seems a bit like an overkill for you, then a slower solution might come in handy. That'd be comparing the snapshot in your destination with current data in source and based on differences take proper actions (insert, update, delete rows from destination). This method would require you to scan whole tables each time you need to synchronize.


Stored Procedure error “Must declare the scalar variable” I am trying to create a stored procedure with dynamic SQL inside of it.
The proc receives 5 variables, 3 of which are names of columns and fields and another 2 are for OFFSET and FETCH NEXT part.
I keep getting this error on the 2 last variables:

Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@numOfOffset".
Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@numOfRows".

I will appreciate some help, please

IF (SELECT 1 FROM sys.procedures WHERE [name] = 'sp_targil1') = 1
    DROP PROCEDURE sp_targil1;
GO

CREATE PROCEDURE sp_targil1 (@tableName varchar(30), @fieldNameAggBy varchar(30), @fieldNameToAggOn varchar(30)
    ,  @numOfOffset int, @numOfRows int
    )
AS
BEGIN
    DECLARE @query1 varchar(500)
    DECLARE @qaeryMain varchar(500)
    DECLARE @maxNumberOfRows int

    SET @qaeryMain =
    '
    SELECT ' + @fieldNameAggBy + ', SUM(' + @fieldNameToAggOn + ') AS ' + @fieldNameToAggOn + '_Sum_By_' + @fieldNameAggBy +
    ' FROM ' + @tableName +
    ' GROUP BY ' + @fieldNameAggBy +
    ' ORDER BY SUM(' + @fieldNameToAggOn + ') DESC OFFSET @numOfOffset - 1 ROWS FETCH NEXT @numOfRows ROWS ONLY'
    PRINT @qaeryMain
    EXECUTE (@qaeryMain)
END

You're not declaring either @numOfOffset or @numOfRows variables within the context of your Dynamic SQL statement.  If you copy the output of your PRINT @qaeryMain statement, you'll notice the distinct lack of any DECLARE statements, this is the reason why you're getting the error.

If you just change your code to the following, it should resolve your issue as it will specify literals within your DSQL statement instead of undefined variables.

IF (SELECT 1 FROM sys.procedures WHERE [name] = 'sp_targil1') = 1
    DROP PROCEDURE sp_targil1;
GO

CREATE PROCEDURE sp_targil1 (@tableName varchar(30), @fieldNameAggBy varchar(30), @fieldNameToAggOn varchar(30)
    ,  @numOfOffset int, @numOfRows int
    )
AS
BEGIN
    DECLARE @query1 varchar(500)
    DECLARE @qaeryMain varchar(500)
    DECLARE @maxNumberOfRows int

    SET @qaeryMain =
    '
    SELECT ' + @fieldNameAggBy + ', SUM(' + @fieldNameToAggOn + ') AS ' + @fieldNameToAggOn + '_Sum_By_' + @fieldNameAggBy +
    ' FROM ' + @tableName +
    ' GROUP BY ' + @fieldNameAggBy +
    ' ORDER BY SUM(' + @fieldNameToAggOn + ') DESC OFFSET ' + CAST(@numOfOffset AS VARHCAR(50)) + ' - 1 ROWS FETCH NEXT ' + CAST(@numOfRowsAS VARHCAR(50)) + ' ROWS ONLY'
    PRINT @qaeryMain
    EXECUTE (@qaeryMain)
END


Is a meta table useful Lets say a lot of my tables will have the same few columns for example a name, description, created timestamp, last updated timestamp.

When is it appropriate to put these common columns in a separate table compared to keeping them in their individual tables.

Just to keep track of it better here is a quick schema.

Books                  Movies
---------------------  ---------------------
|ID|Name|Description|  |ID|Name|Description|
|  |    |           |  |  |    |           |

or

Books         Movies        Meta
------------  ------------  ---------------------
|ID|Meta_ID|  |ID|Meta_ID|  |ID|Name|Description|
|  |       |  |  |       |  |  |    |           |

Obviously this isn't the best example but it gives you the idea.

This is Vertical Partioning, as Michael Green states. I think it really depends on how frequently you need the Description field for Books and Movies. If it's rarely accessed/requested, then split it out as you described above. You would have two additional tables though, like BooksMeta and MoviesMeta, instead of the one Meta table. Then add your keys to the meta table.


How to get Mysql Select Query? single question multiple answers Table =A [qa_request]

 sno | userno | name | uquestion | subject
-----+--------+------+-----------+---------
  1  |  xxxxx |  yyy |    q1     |   s1

Table =B [qa_answer]

 qa_id | answer |    a_subject
-------+--------+-----------------
   1   |   a1   |  adminsubject-1
   1   |   a2   |  adminsubject-2
   1   |   a3   |  adminsubject-3

MY SQL Query :

select distinct
       a.sno, a.userno, a.name, a.uquestion, a.subject,
       b.answer, b.a_subject
  from qa_request as a
         inner join qa_answer as b ON a.sno=b.qa_id

Sample Output:

 sno | userno | name | uquestion | subject | answer |    a_subject
-----+--------+------+-----------+---------+--------+----------------
  1  |  xxxxx |  yyy |     q1    |    s1   |   a1   | adminsubject-1
     |        |      |           |         |   a2   | adminsubject-2
     |        |      |           |         |   a3   | adminsubject-3

In other words, I want to show the actual values from the question only on the first line with the first answer, and leave them blank on the following lines, with the second (etc.) answer(s).

How do I do this?

As noted by Tom V, this can easily be done in the presentation layer, instead of in the query itself. Trying to do it in the query means that the query will take longer to run, and in most cases in a web application (which I assume is what you're working with here), you can have several webservers all interacting with one database server - offloading tasks from the DB server to the web servers will frequently make sense.

However, it's also true that you'd (probably) be sending more data from the database server to the web server, and there's a cost in that bandwidth as well. And, of course, smaller websites might have just one webserver, in which case it might actually make sense to use more resources on the DB server.

So, the following query should give you what you want. We use a variable in the query to check if the current row has the same sno as the previous row; if it doesn't, we don't show the columns from qa_request. The process creates an extra column (aliased to dummy); the main query is put into a subquery simply so we can leave that dummy column out.

CREATE TABLE qa_request (
  sno       int,
  userno    varchar(20),
  name      varchar(20),
  uquestion varchar(5),
  subject   varchar(20)
  );

INSERT INTO qa_request
VALUES (1,'xxxxx','yyy','q1','s1')
      ,(2,'zzzzz','qqq','q7','s8')
;

CREATE TABLE qa_answer (
  qa_id      int,
  answer     varchar(5),
  a_subject  varchar(20)
  );

INSERT INTO qa_answer
VALUES (1,'a1','adminsubject-1')
      ,(1,'a2','adminsubject-2')
      ,(1,'a3','adminsubject-3')
      ,(2,'a37','adminsubject_37')
;

SELECT sno
      ,userno
      ,name
      ,uquestion
      ,subject
      ,answer
      ,a_subject
  FROM (SELECT CASE WHEN @last_sno <> a.sno THEN CONCAT('',a.sno) ELSE '' END as sno
              ,CASE WHEN @last_sno <> a.sno THEN a.userno ELSE '' END as userno
              ,CASE WHEN @last_sno <> a.sno THEN a.name ELSE '' END as name
              ,CASE WHEN @last_sno <> a.sno THEN a.uquestion ELSE '' END as uquestion
              ,CASE WHEN @last_sno <> a.sno THEN a.subject ELSE '' END as subject
              ,b.answer
              ,b.a_subject
              ,@last_sno := sno as dummy
          from qa_request as a
                 inner join qa_answer as b ON a.sno=b.qa_id
                 CROSS JOIN (SELECT @last_sno := -1) init
         ORDER BY a.sno, b.answer
       ) sq
;

db-fiddle here


Is it possible to query "NOT IN" in NO SQL? I asked the same thing about a specific NoSql engine (Big Table) on SO.
It seems that Big Table doesn't support NOT IN queries?

Is that possible in any other NoSQL databases?
Will it ever be possible on NoSQL databases?

The answer to your question is "no."  And no, I don't think it will ever be possible at least in any helpful way.

Relational databases are designed to operate over sets of tuples.  Non-relational databases are not.  This means that one of the key tradeoffs is being able to have very fast access (with "good enough" consistency controls) to single pieces of data in NoSQL vs high-end consistency controls and set-based math on the relational side.

So the answer is "no, no, and no."

If you want relational math, pick a relational database.


How do I adopt the PostgreSQL naming convention in legacy database? I realize I'll need to update the queries, but if I have a database that has camelCase or spaces in it, you have to use double quotes on the identifiers (viz. schema, table, column). How do I migrate away from taking into account that I can't have capital letters, nor spaces in my identifiers. I need to normalize all of them to snake_case.

Moving to PostgreSQL convention

In PostgreSQL by convention and with good reason, we neither use spaces nor capital letters in our identifiers (columns, tables, schemas, etc). The use of _ is purely style though. In this example, we

Transliterate all ' ' to '_'
Migrate from camelCase to snake_case

This code changes nothing, but outputs the respective ALTER commands to update the schema (including the schema, table, and column names)

-- Columns first.
SELECT FORMAT(
  'ALTER TABLE %I.%I.%I RENAME COLUMN %I TO %I;',
  table_catalog,
  table_schema,
  table_name,
  column_name,
  lower(
    -- replace all spaces with _, xX and Xx becomes x_x
    regexp_replace(
      -- First, replace spaces with an _
      replace(column_name, ' ', '_'),
      '([[:lower:]])([[:upper:]])',
      '\1_\2',
      'g'
    )
  )
)
FROM information_schema.columns
WHERE column_name ~ ' |[[:lower:]][[:upper:]]'

-- Tables
UNION ALL
  SELECT FORMAT (
    'ALTER TABLE %I.%I.%I RENAME TO %I;',
    table_catalog,
    table_schema,
    table_name,
    lower(
      regexp_replace(
        replace(table_name, ' ', '_'),
        '([[:lower:]])([[:upper:]])',
        '\1_\2',
        'g'
      )
    )
  )
  FROM information_schema.tables
  WHERE table_name ~ ' |[[:lower:]][[:upper:]]'

-- Schemas
UNION ALL
  SELECT FORMAT (
    'ALTER SCHEMA %I RENAME TO %I;',
    schema_name,
    lower(
      regexp_replace(
        replace(schema_name, ' ', '_'),
        '([[:lower:]])([[:upper:]])',
        '\1_\2',
        'g'
      )
    )
  )
  FROM information_schema.schemata
  WHERE schema_name ~ ' |[[:lower:]][[:upper:]]';

From there you can edit the commands to be run, delete the ones you don't want, or simply run \gexec if you're using psql and they will execute.

Testing

If you wish you can test the above

CREATE SCHEMA "myFoo bar";
CREATE TABLE "myFoo bar"."foo bar myBaz" ( "my foo" int, "myBar" int, "MyBaz" text, "myFooBarBaz" int, "MyFoo Bar Baz" int, "myTestQ" uuid );

                                            format
-----------------------------------------------------------------------------------------------
 ALTER TABLE test.my_foo_bar.foo_bar_my_baz RENAME COLUMN "MyFoo Bar Baz" TO my_foo_bar_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "my foo" TO my_foo;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "myBar" TO my_bar;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "MyBaz" TO my_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "myFooBarBaz" TO my_foo_bar_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "MyFoo Bar Baz" TO my_foo_bar_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "myTestQ" TO my_test_q;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME TO foo_bar_my_baz;
 ALTER SCHEMA "myFoo bar" RENAME TO my_foo_bar;
(9 rows)

Note because there is a collision in the above to my_foo_bar_baz, you'll see

  ERROR:  column "my_foo_bar_baz" of relation "foo bar myBaz" already exists

That just confirms nothing catastrophic happens if you happen to run into that. highly unlikely


TRY/CATCH only reports the last (and most useless) error (This is SQL Server 2016 SP2-CU12.)
When running this command interactively, I see the quite useful Msg 3035, Level 16, State 1.
BACKUP DATABASE @DB TO DISK = @targetfile
WITH DIFFERENTIAL, RETAINDAYS = 2, COMPRESSION,
NAME = @buname, STATS = 4

Msg 3035, Level 16, State 1, Line 4
Cannot perform a differential backup for database "ReportServer", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally

But when running it within a TRY/CATCH block, all I get is the Generic Msg 3013, Level 16, State 1.  How do I see the specific and useful message Msg 3035, Level 16, State 1?  The goal of my task is to test the ERROR_NUMBER(), ERROR_SEVERITY() and ERROR_STATE() values and programmatically perform a full backup when Msg 3035, Level 16, State 1 occurs, but not when  Msg 911, Level 16, State 11 Database 'XXX does not exist. and Msg 3201, Level 16, State 1, Line 4 Cannot open backup device 'xxxx'. Operating system error 3(The system cannot find the path specified.). errors happen.
begin try
    BACKUP DATABASE @DB TO DISK = @targetfile
    WITH DIFFERENTIAL, RETAINDAYS = 2, COMPRESSION,
    STATS = 4
end try
begin catch
    SELECT ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState
            , ERROR_MESSAGE() as ErrorMsg;
end catch

ErrorNumber ErrorSeverity ErrorState  ErrorMsg
----------- ------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3013        16            1           BACKUP DATABASE is terminating abnormally.

(1 row affected)

You can try using THROW to get the full exception raised:

If the THROW statement is specified without parameters, it must appear
inside a CATCH block. This causes the caught exception to be raised.

begin try
    BACKUP DATABASE @DB TO DISK = @targetfile
    WITH DIFFERENTIAL, RETAINDAYS = 2, COMPRESSION,
    STATS = 4
end try
begin catch
    SELECT ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState
            , ERROR_MESSAGE() as ErrorMsg;
    THROW;
end catch

Result

Msg 3035, Level 16, State 1, Line 8 Cannot perform a differential
backup for database "...", because a current database backup does
not exist. Perform a full database backup by reissuing BACKUP
DATABASE, omitting the WITH DIFFERENTIAL option. Msg 3013, Level 16,
State 1, Line 8 BACKUP DATABASE is terminating abnormally.