How does SQL Server determine key column order in missing index requests?

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups.  The first set contains all of the recommended columns that are part of an EQUALITY predicate.  The second set contains all of the recommended columns that are part of an INEQUALITY predicate.

Within each set, the columns are ordered by the ordinal position of the columns, based on the table definition.

(Many thanks to Brent Ozar for building a repro script against the Stack Overflow database to prove this!)

1. Create 3 identical tables, but put their columns in different order. (The reason here is to use a variety of column names and datatypes to show that that doesn't impact the column order in the missing index recommendation.)

CREATE TABLE dbo.NumberLetterDate (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fINT INT, fNVARCHAR NVARCHAR(40), fDATE DATETIME, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.LetterDateNumber (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fNVARCHAR NVARCHAR(40), fDATE DATETIME, fINT INT, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.DateNumberLetter (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fDATE DATETIME, fINT INT, fNVARCHAR NVARCHAR(40), AboutMe NVARCHAR(MAX));
GO

2. Populate the tables with the same data. Get 100,000 rows from the Users table with real-world data distribution.

INSERT INTO dbo.NumberLetterDate(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.LetterDateNumber(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.DateNumberLetter(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO

3. Write a query that needs an index. Start with 3 equality filters, filtering for an exact value in all 3 fields. Note that all 3 queries have the same fields in the same order:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

All three tables have exactly the same data, and the queries are identical. The only difference is the field order - and that's the difference in our missing index requests, too:

In the execution plans, the column order in the missing index request exactly matches the column order in the table. For example, in dbo.NumberLetterDate, the number column is first, so it's first in the missing index request, too:

On dbo.NumberLetterDate, the missing index is on fINT (number), fLetter (nvarchar), fDate, the same order of the fields in the table
On dbo.LetterDateNumber, the index order switches to fNVARCHAR, fDATE, fINT
On dbo.DateNumberLetter, the index order switches to fDATE, fINT, fNVARCHAR

For a single-table operation like this, the index field order doesn't appear to depend on selectivity, datatype, or position in the query. (I leave it to other folks to prove this with more complex queries & joins.)

4. Mix in an inequality filter. On the INT field, for example, put in <> 100 as the filter:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

In the execution plans, equality fields go first, then inequality fields - so here, fINT shows up last in all 3 missing index requests because it's an inequality search:

5. Use 3 inequality filters. Use the same search for all fields (<>):

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);
GO

Since there's no equality searches, all 3 fields have the same priority order in the missing index recommendation, and now we're back to sorting purely by field order:


pg_ctl says "no server running" when I'm connected to postgres and running queries

pg_ctl doesn't track your database.  It interacts with a specific directory, either specified by -D, or through the PGDATA env variable.  If neither are set, then it throws an error.
$ unset PGDATA
$ pg_ctl status
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

If your database truly did successfully start up, then it either must be configured to run on a non-default port, or it must be configured not to use a port at all but only a unix domain socket.  Neither of these configurations will be set up for you automatically by initdb, so I think you may be mistake that it started up in the first place.  Or maybe you did something beyond what you have described to us.
The Unix domain socket is named with a leading ., which makes it a hidden file.  You will only see it in /var/run/postgresql with ls if you give the -a option to it.
What pg_ctl start does behind the scenes is call postgres, so the database is always started via postgres.  It might be directly, or indirectly, or even more indirectly, but eventually that is how it is started.
It is not clear what it is you want to do.  If you want to run out of your own directory, then you probably want to disable the start up script that runs the other one automatically.  On the other hand, why not just use the one that is already configured and managed for you by the packaging system?  You can have both running side by side, buy why?


Enforcing distinct number of trailing spaces

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (
    code_type CHAR(4) NOT NULL,
    code VARCHAR(30) NOT NULL,
    PRIMARY KEY (code_type, code)
);

-- fails due to PK error
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;

CREATE TABLE dbo.T226714 (
    code_type CHAR(4) NOT NULL,
    code VARCHAR(30) NOT NULL,
    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))
);

CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);

-- works
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
INSERT INTO dbo.T226714 VALUES ('cha', '');

-- can't insert this as a third row due to the unique index:
INSERT INTO dbo.T226714 VALUES ('cha', ' ');


Entering command 'sqlplus' results in 'sqlplus: command not found'

The Oracle Installation Guide contains a list of packages that need to be installed prior to Oracle. A common cause of this problem might also be if you are trying to install a 32bit version of Oracle on a 64bit version of Linux.


To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)?

I'm getting a bit nervous.

First, the upside: I've used the MariaDB JDBC driver in an "always on" service in production with a database connection pool and it is running fine for a couple of months now. The service only uses basic JDBC functions with simple queries (e.g. no joins, no blobs). I'm about to release another "always on" service that uses the MariaDB JDBC driver in a similar fashion.
I decided to use the MariaDB JDBC driver after (re)viewing the source code: compared to the Oracle MySQL JDBC driver source code the the MariaDB JDBC driver source code is a lot more readable and understandable.

But I get the impression (9/2014) development has slowed down (link is to a graph showing bugs created vs. resolved). If I browse through the open bugs I see many serious bugs open for far too long (issues with Hibernate and also time-zones/dates). Some of these may not be bugs but then they should have been closed by now. I also encountered one bug (via STASH-4012) that should be re-opened: bug CONJ-72 is closed but the last comment (from 4/2014) indicates the problem is not resolved for Hibernate.

All in all I do not consider the MariaDB JDBC driver a perfect replacement for the Oracle MySQL JDBC driver. I'm happy to continue to use it for the simple stuff (where all queries are known beforehand and tested) for now, but I might have to switch back to the Oracle MySQL JDBC driver in the future.


In WebLogic console, if you are selected XA Driver then it need additional drivers  specific library files like sqljdbc.dll etc.
Better to choose Oracle non XA driver(second item from the dropdown list) while you are configuring your datasource.

HTH


Try this:

SELECT City, prize, count( prize ) AS qty
FROM `my_table`
WHERE prize IS NOT NULL
GROUP BY city, prize
ORDER BY count(prize) DESC


Generally your current code shouldn't allow two concurrent transactions to process the same @Sbs_Type simultaneously.

Two concurrent threads can't both acquire U locks on the rows matching WHERE Sbs_Trf_Type = @Sbs_Type.

If two threads simultaneously call GetSubscribeNo with the same @Sbs_Type when there are no matching rows whilst meanwhile a third transaction inserts a row for that @Sbs_Type I can see how this would fail however.

One or both of the GetSubscribeNo calls would need to get past the UPDATE statement before the insert and thus fail to lock anything that would block the other one.

A better way of doing this anyway would be with the OUTPUT clause.

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      DECLARE @Out TABLE (Subscribe_No INT);

      SET XACT_ABORT ON;
      BEGIN TRAN T1;

      WITH T
           AS ((SELECT TOP(1) *
                FROM   UNIT_SBS_NOS WITH (ROWLOCK, READPAST)
                WHERE  Sbs_Trf_Type = @Sbs_Type))
      DELETE FROM T
      OUTPUT DELETED.FREE_SBS_NO INTO @Out;

      /*Default to -1 if no rows*/
      SELECT @subscribeNo = ISNULL(MAX(Subscribe_No), -1)
      FROM   @Out

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END


Can't start Oracle after Virtual Machine restore

And that is why Veeam should not be used to backup a database while it is online.
Just delete the database, create a new emtpy one then proceed with the rest of the steps:
"schemas to be recreated and then the actual database backup dmp files to be reimported".


You've granted CREATE, CONNECT, and TEMPORARY privileges on the database to myuser but you haven't granted SELECT and INSERT table privileges yet. You'll need something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from sequences. Generally, the USAGE privilege is be enough for INSERT operations to work, but since you asked to "give all rights":

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

Details on GRANT in the manual.


Best way to move functions from postgresql to another

You can use a custom PostgreSQL extension created with CREATE EXTENSION.

Put all of your extension code in a build system and have your developers work on that. Then you can install the extensions easily and version control them, roll them back, upgrade them, etc.

Here is a tutorial to get started.


Default Postgres privileges not showing on \ddp

According to the documentation, PostgreSQL does the following for PUBLIC:

  PostgreSQL grants privileges on some types of objects to PUBLIC by
  default when the objects are created. No privileges are granted to
  PUBLIC by default on tables, table columns, sequences, foreign data
  wrappers, foreign servers, large objects, schemas, or tablespaces. For
  other types of objects, the default privileges granted to PUBLIC are
  as follows: CONNECT and TEMPORARY (create temporary tables) privileges
  for databases; EXECUTE privilege for functions and procedures; and
  USAGE privilege for languages and data types (including domains).

This implies that to prevent a new user to be able to connect to any database following must be run for each database:

revoke connect on database <db_name> from public;


Does any SQL engine have navigation support in CHECK clauses?

It appears you are looking for a SQL database that implements the SQL Standard CREATE ASSERTION statement. AFAIK there is no mainstream SQL database that actually supports this.

Therefore, the more complex data integrity rules that you wish to implement must be done so programmatically. This can be done using triggers in the DBMS or procedurally either using stored procedures in the database or within the external application.

However it is implemented, careful consideration needs to be made to ensure the ACID properties are followed to ensure the reliability of the database transactions. In particular this may mean explicit locking mechanisms are employed wherever the constraints are validated.

There is generally also the requirement to ensure that the constraints are enforced as efficiently as possible.

For an in-depth examination of the implementation of complex integrity constraints using DBMS triggers I would suggest reading Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars.


Compare The length of two string variables inside an Expression task in SSIS

I found the answer
I Created a variable @[User::Process] with Boolean data type and
I changed the Expression to this

@[User::Process] = (DT_BOOL)((DT_I4) LEN( @[User::FileFormat] )==LEN( @[User::ExcelName] ) ? 1: 0)

And Changed the precedence constraint of the expression task to This


As long as the traveling user will be adding, updating, and deleting records and can guarantee that no one else is touching in the master, there is something you can try.

What you want to do is setup MySQL Circular Replication (Master/Master) between Master and LapTop. Once you have this esatblished and the time has come for the traveling user (TVU) to leave for the week, here is what to do:

Step 1 : Run STOP SLAVE; on the Master

Step 2 : Run STOP SLAVE; on the LapTop

Step 3 : TVU inserts, updates, and deletes on the LapTop

Step 4 : Make sure TVU does not delete or update data that the Master will have worked on during the week.

Step 5 : When the TVU returns, run START SLAVE; on the Master to reconnect the Master to the LapTop. All inserts, updates, and deletes that occurred on the LapTop during the week  will transmit over to the Master. Run SHOW SLAVE STATUS\G every couple of minutes on the Master and look for Seconds_Behind_Master going to 0. When it does, the data from the LapTop will be sync'd over to the Master.

Step 6 : (OPTIONAL) Run START SLAVE; on the LapTop to bring all the inserts, updates, and deletes the Master did during the week. Run SHOW SLAVE STATUS\G every couple of minutes on the LapTop and look for Seconds_Behind_Master going to 0. When it does, the data from the Master will be sync'd over to the LapTop.

The only real danger is if you delete or update data on the LapTop that still needs to be on the Master. If you forget that, step 5 will make that data change or disappear unintentionally. As long as you work with a new set of data on the LapTop only, there should be no problem.

You should test this between a DevServer and a LapTop you are using for Development only.

UPDATE 2011-07-24 21:42 EDT

Here is another thing you can try:

Step 1 : Setup mysql on the LapTop with no initial data on the laptop and no binary logging

Step 2 : mysqldump the data out of the Master and into the LapTop

Step 3 : Add this to my.ini on the LapTop

[mysqld]
log-bin=mysql-bin

Step 4 : Activate binary logging without a mysql restart on the LapTop

SET GLOBAL SQL_LOG_BIN = 0;

or just restart mysql

Step 5 : Perform inserts, updates, and deletes on the LapTop (Make sure they do not conflict with needed data on the Master)

Step 6 : run this

mysqlbinlog mysql-bin.0* > ThisWeeksChanges.sql

Step 7 : Run this on the Master

mysql> source ThisWeeksChanges.sql


Replace nested "select with count" with outer join? is this possible?

select c.classid,
       c.classname,
       count(case when s.status = 'failed' then 1 end) as no_of_failed,
       count(case when s.status = 'passed' then 1 end) as no_of_passed
from class c
  left join students s on c.classid = s.classid
where c.schedule = 'MWF'
group by c.classiid, c.classname;

This exploits the fact that aggregate functions will ignore null values.

But depending on your DBMS and the size of your tables, your original query might actually be faster. But this can only be found out if you show us the execution plan.


max_connections set to 500, Max_used_connections never go over 151

Tell us more about the load test.  What probably happened...
The test succeeded in launching 151 threads at the 'same' time.  Those threads then fought with each other for CPU, disk, network, etc.  They were each given "equal" access to everything.
What does that lead to?  Well, imagine letting 151 customers into a small grocery store.  Each would be pushing there cart but bumping into other carts.  It would take 'forever' to finish one aisle and get to the next.
The net effect is everyone takes an abnormally long time to finish the task.  Have you learned anything about the load?  No.  The grocery store was overloaded.
Run the load test again, but with the "max threads" set to something like twice the number of CPUs.
Is it possible to have 500 connections?  Yes.  But only if they are not doing what the load test is designed to do:  pound on the database and do nothing else.
That is not realistic.  A web page, for example, will grab some data, build some HTML, wait for the user to input something, etc.  That is, a realistic load involves more than just hitting the database.
What was the goal of your load test?  Maybe we can help you get some real news instead of this fake news.


Query over SSRS/RPC:Completed a lot slower than SSMS

This makes me think it is a parameter sniffing problem, I would recommend reading

Slow in the Application, Fast in SSMS? Understanding Performance MysteriesSQL text by Erland Sommarskog, SQL Server MVP. It talks about the problems you can run into when things run almost instantly in SSMS but not in the application.


SQL Server: How to export database with linked servers

Linked servers are server-level objects--they are not contained within the database. Therefore you will need to move them separately.

In Management Studio, in the Object Explorer, Navigate to Server Objects --> Linked Servers --> [the linked server you want].

Then just right-click --> Script Linked Server as... --> CREATE to --> [pick a destination]. This will not script out passwords.

If the linked server has stored credentials, you will need to re-input the password. You can either edit the call to sp_addlinkedsrvlogin in the generated script (replace "########" with the real password), or edit the linked server from Management Studio after creating it with the wrong password. Saving the script with the password embedded is not a good security practice, so I generally opt to either edit the script at runtime, or edit the authentication settings after the fact.


How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys?

Generating UUIDs (or any values, for that matter -- e.g. new sequence values) and inserting whatever into tables are two distinct and unrelated actions.

The database engine cannot know for what purpose the ID is generated -- it simply returns that ID value to the application, which uses it as it sees fit.

When the application inserts something, not necessarily a UUID it may have obtained earlier, into a column on which a unique constraint is defined, the database engine will verify the value uniqueness according to the constraint rules and return an error to the application if validation fails. It's the application's job to deal with the error as it sees appropriate. It can request a new UUID from the database, manipulate the value it already has, or simply fail.


Can't overwrite an existing Azure SQL Managed Instance database during restore

Yes, you will have to DROP the prior database before RESTORE as the REPLACE syntax isn't supported on Managed Instance.

Doing a RESTORE on Managed Instance (MI) will do some other work behind the scenes, too. In the General Purpose tier, you get one Azure Premium disk per data file, and that is handled completely by MI. This manifests in theRESTORE syntax by not allowing you to use MOVE (because the service essentially does that for you).

REPLACE is similarly disallowed right now, and I suspect it's also related to the magic of allocating storage and moving files on restore.


Only specific variations of LIMIT are working for me?

Fixed it finally.

Was an issue with the I was using GROUP BY, ORDER BY and LIMIT, at least I think.

I changed it to the following and it's working smoothly now:

SELECT DISTINCT `name`, `id`, `viewsperweek`, `totalviews` FROM `cards` GROUP BY `name` ORDER BY `viewsperweek` DESC LIMIT 10

I ran into this issue before with my Database and forgot about it. There are some cards that are duplicates so I needed to remove them through the SQL code. In my original code it was hitting some duplicates after the 5th and breaking.
Honestly not sure why (as the code worked in my back-end db), but it's working now.


The use of multiple foreign keys on same column in SQL Server

SQL Server allows you to do a lot of silly things.

You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.

One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.

(As in example below)

CREATE TABLE T1(
    T1_Id INT PRIMARY KEY CLUSTERED  NOT NULL,
    Filler CHAR(4000) NULL,
)

INSERT INTO T1 VALUES (1, '');

CREATE TABLE T2(
    T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
    Filler CHAR(4000) NULL,
)

ALTER TABLE T1 ADD CONSTRAINT
    UQ_T1 UNIQUE NONCLUSTERED(T1_Id)

/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)

ALTER TABLE T2  WITH CHECK ADD  CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)

ALTER TABLE T2 DROP CONSTRAINT FK

/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)

DROP TABLE  T2, T1;

As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.


Find first-time purchases with certain category

My educated guess:

SELECT *
FROM (
   SELECT DISTINCT ON (o.guest_email)
          o.guest_email AS "Email", o.date_purchased AS "First Purchase", mc.name AS "Category"
   FROM   order_order               o
   JOIN   order_line                l  ON l.order_id = o.id
   JOIN   catalogue_productcategory p  ON p.product_id = l.product_id
   JOIN   catalogue_category        c  ON c.id = p.category_id
   JOIN   catalogue_category        mc ON mc.path = left(c.path, 4)
   ORDER  BY o.guest_email, o.date_purchased
   ) sub
WHERE  "Category" = 'Computer';

Or, if all those joins never exclude rows (referential integrity enforced with FK constraints), you can exclude irrelevant rows early:

SELECT o.guest_email AS "Email", o.date_purchased AS "First Purchase", mc.name AS "Category"
FROM  (
   SELECT DISTINCT ON (guest_email)
          id, guest_email, date_purchased  -- more columns?
   FROM   order_order
   ORDER  BY guest_email, date_purchased
   ) o
JOIN   order_line                l  ON l.order_id = o.id
JOIN   catalogue_productcategory p  ON p.product_id = l.product_id
JOIN   catalogue_category        c  ON c.id = p.category_id
JOIN   catalogue_category        mc ON mc.path = left(c.path, 4)
WHERE  mc.name = 'Computer';

I largely rewrote the query and removed some cruft.

The subquery retrieves the first order per email.
The outer query filters first-timers who bought a 'Computer'.

About DISTINCT ON:

Select first row in each GROUP BY group?

Depending on your complete situation, there may be faster query techniques:

Optimize GROUP BY query to retrieve latest record per user


Does PostgreSQL have a built in option for converting multi-line text to single line?

To get row and columns from key=value;key=value;key=value string use , ; acts as newline in this case.

select unnest(string_to_array('key=value ; key2=value2', ';' ));

returns :

column1
 "key=value "
 " key2=value2"

select (string_to_array(a.r, '=' ))[1] as  k , (string_to_array(a.r, '=' ))[2] as v  from (select unnest(string_to_array('key=value ; key2=value2', ';' )) as r  ) as a

returns:

k , v
"key";"value "
" key2";"value2"

To other way around (table test has k and v colums)

select string_agg(k || '=' ||v, ';') from test

if you have table like 'conf_file', 'key', 'value' you can use group by clause in string_agg


Creating non-clustered Index on Partitioned Table that already has an existing Primary Key

Since the TABLE_A is partitioned on COL_1, the new Primary Key being added must include COL_1 as part of it.

So the PK ends up being created as so:

-- Add the MY_KEY column
ALTER TABLE dbo.TABLEA
ADD [MY_KEY] UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWSEQUENTIALID() NOT NULL

-- Create the PK constraint
ALTER TABLE dbo.TABLEA
ADD CONSTRAINT PK_TABLEA PRIMARY KEY CLUSTERED (MY_KEY,COL_1)


How to REVOKE ALL ON db.table FROM user after GRANT ALL db.*

Alas, I'm pretty sure that it is not directly possible.  REVOKE seems to work only with the identical GRANT.

A messy workaround is to build GRANT statements for all the other tables with the aid if information_schema.tables, then execute them.

Suggest filing a bug at bugs.mysql.com.  (But first, check to see there are already feature requests for it.  Then click the 'me to' flag.)


Uniq join 3 tables with relationship table not uniq

There are many ways. Here is one (should be a fast variant to retrieve all rows):

SELECT t1.*, t2.*
FROM   table1 t1
JOIN  (
    SELECT DISTINCT table_1_id, table_2_id
    FROM   table1_2
    ) t1_2 ON t1_2.table_1_id = t1.id
JOIN   table2 t2 ON t2.id = t1_2.table_2_id;

Why not remove the duplicates permanently? And add a UNIQUE constraint ...


Which columns (and order) to choose for a clustered index to maximize query performance

Generally, you want to order the columns in the index based on cardinality. That is, the most unique column first, then the second most unique, etc. So, you need to answer whether parameterid or measuretime will have the least duplicate values. Example: if Measuretime has fewer duplicate values than parameterid use option 1. Then, for optimal performance you need to edit your queries to reference the columns in the correct order.

Something to consider, however, is the pattern of writes to this table. Is the data inserted in the order of Measuretime? Will a new ParameterID always be a higher increment of a previously added one? Are there lots of updates and deletes to this table?

The advantage of having a surrogate key, like your ID column above, is that it ensures ordered inserts into the table/clustered index. Among other benefits, this avoids page splits on inserts.

Also, if the combination of ParameterID and Measuretime will always be unique, you could consider a composite primary key. This has its downsides, but is a valid option.

Here's a good explanation of using composite primary keys: http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx


Definitive list of steps for SQL Server baseline testing?

First, I'd step back and ask what measurements you plan to collect during the test.  If you're counting logical reads by query, for example, then you don't need to free the cache.  I'm a big fan of using logical reads because it's independent of whether the data is cached or on disk - and in production, it's hard to guess whether a query's data will be cached or not (unless you cache the entire database in memory).  If you tune to minimize logical reads, then the app will go faster whether the data's in cache or not.

Next, I'd question what's changing between runs.  For example, by running EXEC SP_UPDATESTATS in each database as you've suggested, you're going to resample stats for tables that have been updated.  However, unless you're updating statistics with fullscan, you're getting random rows from the table - that's not too repeatable, and I don't think you really want to do that.  Instead, you might want to restore the databases between each run so that you're always testing exactly the same data.  If your tests are doing inserts/updates/deletes, they might have different performance profiles on each run if you're not restoring the database (because they're adding/changing data, plus changing statistics on the data) - and even worse, you could have queries fail if they try to insert repeated data for the same values.


Can I shrink the transaction log file on a mirror database?

The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database.  Here's some proof.

Create a sample database on the principal:

create database MirroredDb;
go

Create the same database from a backup with NORECOVERY:

restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go

Setup your mirroring session however which way you choose.

On the principal database look at the database file sizes:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like this following:

name            size
MirroredDb      392
MirroredDb_log  104

On the mirror database, create a snapshot and look at the same information:

create database MirroredDbss
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;

use MirroredDbss;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  104

Now grow the transaction log file on the principal database (I brought it to 1 GB):

alter database MirroredDb
modify file
(
    name = MirroredDb_log,
    size = 1GB
);
go

Looking at the principal database's transaction log size, we now see the adjusted size:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Create another snapshot on the mirrored database, and look at the transaction log file size there:

create database MirroredDbss2
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;

use MirroredDbss2;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Now do the DBCC SHRINKFILE on the principal:

use MirroredDb;
go

dbcc shrinkfile('MirroredDb_log', 0);
go

select
    name,
    size
from sys.database_files;

My result set is the following:

name            size
MirroredDb      392
MirroredDb_log  104

Create a third and final snapshot on the mirrored database, and look at the size:

create database MirroredDbss3
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;

use MirroredDbss3;
go

select
    name,
    size
from sys.database_files;

And I get the following result set:

name            size
MirroredDb      392
MirroredDb_log  104

So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.


ORA-39142: incompatible version number 3.1 error work-around

Here's an interesting blog entry from Burleson computing that appears potentially useful for your circumstance.

From the site:

  There are two cases where it is possible to import into an earlier
  release:

  1 - Using the version parameter in Data Pump.  See my notes on
  importing between different releases of Oracle with Data Pump
  export/import

  2 - Using a exp dump file with a Data Pump impdp utility.


How can I add a user and specify a password for a in IBM Data Studio?

Eventually I found that DB2 UDB does not have its own repository for users, unlike other database servers. DB2 relies on the OS for user management. In Windows, for instance, after creating a UDB user, one has to go into Windows Control Panel/User Accounts and add that same user. Then UDB will allow it to log in with the password, supplied on the OS level. I presume that in Linux the procedure is similar: add a user in UDB, then run CLI commands to add a user and to change its password.


Insert All Columns from one table into another table MYSQL

have you tried this?

CREATE TABLE t_cross_result AS
SELECT *
FROM 't_modul' CROSS JOIN 'sensors'


In general you should be able to remove Index2 without any major issues.  Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).

If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.


Can adding both Asc and Desc indexes improve performance?

The leaf pages of the index form a doubly linked list with pointers to the next and previous pages. This means indexes can be scanned both forward and backwards.

There is a slight difference in that currently only scans in the forward direction can be parallelised but it is extremely unlikely to justify the cost of maintaining both just for that edge case.

  could it allow SQL to search any faster by utilizing both indexes at
  the same time?

No SQL Server won't split a single index access into a multiple index access in some sort of attempt to parallelise the work.


How to download only the newer and updated entries, and avoid
  downloading same entry once again?

Use a database synchronization tool like SymmetricDS or Daffodil Replicator.

You'd have a central database server, like PostgreSQL, with SymmetricDS running on it.
Each client, such as an Android smartphone, runs SQLite and SymmetricDS.
The SymmetricDS clients and server communicate with each other over HTTP(S), sending changesets back and forth.

  How to provide edit entry possibility to db? Is it better to create
  new table with proposal entries and after verification, move them to
  main table? Or maybe should I create additional columns, which would
  be proposals for new values?

I would add an is_approved or similar column that needs to be true for items to show up to regular users. Editor and admins can see unapproved items.

  What if I would get lots of changes proposals? Is it better to store
  only the last one? Or maybe block edit possibility if a proposal is
  already set?

Use History Tables (copy-on-write to a table similar to your main tables) to capture each change. That way you can rollback to earlier or better versions if you need to. Use Optimistic Concurrency Control to prevent updates if data changed.

  How to avoid cheaters and scammers? Some bored people may want to try
  make a mess, editing multiple entries with fake data. How to avoid
  such kind of events? Grant edit access only to verified people?

Like this site, add "user abilities" after users have proven themselves. Block bad users. Use machine learning to filter spam.

  If I would like to store images of items, is it better to store it in
  db or use some kind of flicker or instagram instead?

Usually database is better, if you can cache heavily on web server, and can do incremental backup.

  Is it better to save new entry in SQLite instant after edit? Or just
  post it to MySQL and add it to SQLite only after verification and only
  via SQLite and MySQL sync?

Saving to SQLite locally first allows for offline usage, and is probably more responsive. http://offlinefirst.org/

  Is it good idea to set key value to EAN code? At least it is constant
  and connected to only one product. There will be no items containing
  no EAN.

No, I would use a meaningless key for product_id and a unique nullable column for EAN.


MongoDB storage of multi-dimensional arrays

MongoDB can efficiently store these images provided their total size does not exceed the 16MB document limit. If you are likely going to this, you should consider redesigning your schema, otherwise your schema design will be fine.


Tools to visualize adhoc multi dimentional data

What are you trying to achieve with more than two axes? If at the end you just want those fields/values to be dumped into a tabular result, you can rewrite your query to use crossjoins.

Select [Measures].[Internet Average Sales Amount] on 0,
{[Product].[Category].children * Product.[Model Name].children} on 1
from [Adventure Works]


selecting different categories of values from one column using limit

You could do union on three queries, each selecting only 5 records of choosen complexity :

(select * from question_bank where complexity='high' limit 5)
union
(select * from question_bank where complexity='medium'limit 5)

....

This way, you "sum" the records of all your queries (union works only if every request fetch the same columns if I'm not wrong).

You have to be careful to put select statement between parenthesis when using limit and union together (thanks to this answer : https://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query ), if you fail to do so, your limit won't apply to all select statements.


Postgresql - Error in function that returns table

You don't need plpgsql for this. A plain sql function will do:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
   returns table(colunas character varying)
as
$$
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'Main'
    AND table_name   = tbl_name;
$$
language sql;

Additionally: the language name is an identifier. Don't put it in single quotes.


Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'

Mysql run with autocommit feature , it look like your application code forcefully commit the each DML.


Erin Stellato's Stairway to Extended Events is a good starting point here (it's behind a registration wall, but it's worth it.) She explains how you can use the SSMS GUI to create an Extended Events session that will trigger a dump, for example.

Understand that taking an action like creating a dump will be very performance-intensive.


Mysterious merge replication problems creating a subscription

I think you should check that in your database you have some ddl trigger which can switch off 'ANSI_PADDING'. If in the trigger body you will find something
like i.e. SETANSI_NULLS OFF it could be your error.

If it is i.e. trigger which is monitoring that users are removing or adding i.e. indices.
That will be possible it is acting when you creating your pull subscription and replication internals are trying to add replication triggers on your subscriber database.

In this case you need to disable trigger, create pull subscription and enable trigger.

More you can find on this link. It is a bug in ms sql server 2005

http://connect.microsoft.com/SQLServer/feedback/details/334194/ansi-padding-options-in-replication-snapshot

J


Any correct procedure for couchbase backup and restore?

Some recommendations from the professional services team at Couchbase:

Use cbbackupmgr to take differential backups (according to your recover point objectives).
Merge backups to the number of restore points that you want.
Compact the backup set after each backup operation

Some references for you:

cbbackupmgr tutorial
cbbackupmgr strategies


How to modify multiple databases structure simultaneously?

It's the job of your app. Database Schema Migration frameworks help you with that.

For Java based Apps Flyway is an example tool. For PHP based Apps Laravel Database: Migrations

  Migrations are like version control for your database, allowing your
  team to easily modify and share the application's database schema.
  Migrations are typically paired with Laravel's schema builder to
  easily build your application's database schema. If you have ever had
  to tell a teammate to manually add a column to their local database
  schema, you've faced the problem that database migrations solve.

such Frameworks exists for every language and is the state of the art way to DDL statements in an automated and versioned fashion.

simple example with Flyway and Grails howto deploy a procedure:

path .../grails-app/conf/db/migration/V1_0_7__remove_service_ref_from_service_instance.sql

DELIMITER //

CREATE PROCEDURE normalize_service_instance()
  BEGIN

    SET @col_exists = 0;
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'service_instance'
          AND column_name = 'service_id'
          AND table_schema = database()
    INTO @col_exists;

    IF @col_exists = 1
    THEN
      ALTER TABLE service_instance
        DROP FOREIGN KEY FK_c2o824f4t90lp1v2df5dxp2r8;
      ALTER TABLE service_instance
        DROP COLUMN service_id;
    END IF;

  END//

Other popular tool for Java land is Liquibase

  Liquibase is an open source database-independent library for tracking,
  managing and applying database schema changes. It was started in 2006
  to allow easier tracking of database changes, especially in an agile
  software development environment.


Thanks to @Akine I did this:

SELECT DISTINCT Designation, Model,
COUNT(CASE WHEN ((ei.IssueLocationID IS NULL) OR (ei.IssueLocationID IS NOT NULL AND ei.ReturnLocationID IS NOT NULL )) THEN e.EquipmentID ELSE NULL END) AS Total
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
INNER JOIN Equipment e ON e.ItemID = i.ItemID
INNER JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation = 'Issue'
AND ed.ArmoryID  IN 2
GROUP BY i.ItemID


Availability Group Read Only Routing Not Working for Azure App Service that connects over Hybrid Connection

Microsoft Support engineer replied below on my case:

  I (Support engineer) have reached my technical leads to have an urgent update on this and I was able to get it.
  Unfortunately, the Product Group confirms my initial suspicions about the setup you want to achieve not being possible due to Hybrid Connections limitations therefore not supporting this. It’s a by-design behavior and currently Hybrid Connections does not support this scenario. The Product Group engineers confirmed me that this feature is being considered however they have no ETA to share.

Hybrid Connections do not support AG read-only routing - as simple as that

I have created feature request on Azure website, please login and up-vote it if you can
https://feedback.azure.com/forums/169385-web-apps/suggestions/38917648-enable-hybrid-connections-to-work-with-read-only-r


Copy database to a remote server on daily basis

I just want to avoid to spare disk space if possible. I'm searching
  for a way to "shrink on the fly" and then create the bak.. but seems
  to be impossible

Well, general answer here will be NO, there is no easy/simple way to get this done seeing the amount of free space , which is 50GB compared to the database (200GB) whose MDF is 80 GB.

Shrinking on FLY, No again. You have to shrink the file on prod before backing it up or later once restore (which is not the case here, as destination dev is only 50 GB).

It better to go for the option of more space , rather than going a very tedious and complex way ( will be OK, if we can be assured if it will work).

Also, refer to some suggestions/ answered at How to restore a SQL Server database and shrink its files at the same time?


Oracle newly created DB is filled with datatables

Just to give you an analogy. When you install Windows OS or Linux OS, would you have expected zero files on your filesystem? The same applies for the Oracle Database. After creation of an Oracle Database, there will be tons of objects (tables,functions,procedures,indices,packages,etc..) constitute your Oracle Database. All these objects are owned by SYS,SYSTEM,.. (there are many more).

These owners and objects come into play when you utilize different features of the database. Remember Oracle Database has been developed for +40 years and there is a vast amount of features!

Now that you want to create an empty database. Let's apply the OS analogy again. You would have created a new user... /home/myuser. This is exactly what you do in an Oracle Database as well.

An Oracle Database can handle thousands of users and schemas. (schema = owner of objects, user = manipulate already created objects (DML - select,insert,update,delete)).

-- *nix (example: export TWO_TASK=localhost:1521/SERVICE_NAME)
export TWO_TASK=MY_DB_SERVICE
-- Windows
set LOCAL=MY_DB_SERVICE

sqlplus system/password

-- this user is the schema. The owner of your objects.
create user myschema identified by &password
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS;

-- grant privs to the schema
grant create session, resource to myschema;

-- yes, we need to take security into account.
create role myschema_ro;
create role myschema_rw;

-- lets login as myschema
sqlplus myschema/password

-- your database or schema is empty, now create all objects...
create table t (x int);

-- grant access to object
grant select on t to myschema_rw;

-- we are done with the creation of the schema

sqlplus system/password

-- no one is going to login as schema owner.
-- you only unlock this account when you do schema changes
alter user myschema account lock;

-- time to create your app-user-account who is going to access
-- the database/schema
create user myapp identified by &password;

-- grant privileges for your app-user
grant create session, myschema_rw to myapp;

-- login
sqlplus myapp/password

-- you will see your schema objects are listed
select object_name from all_objects;

-- now, if you want to avoid schema-prefixing (select * from myschema.t) there are some options
-- the first thing you do in your app
alter session set current_schema = MYSCHEMA;

-- now you can do: t is owned by myschema
select * from t;

-- you can create a trigger (log in as myschema)
create or replace trigger after_logon_trg
after logon on myschema.schema
begin
  execute immediate 'alter session set current_schema = MYSCHEMA';
end;
/

You are now ready to create an empty database ( = schema in Oracle terms) on your Oracle Database server (instance) where your objects are secured.

Just to clarify: A database is a set of files on disk with one or many schemas (apart from sys/system), an instance is a running system which is composed of the files on disk plus the in-memory structures which define a working piece of software.

Best of luck!


Like with the results of a subquery (multiple values)

Rewrite it into a JOIN

SELECT t1.field1, t1.field2, t1.field3
FROM
    table1 t1
    JOIN
    (SELECT distinct field AS fieldx from table2) t2
    ON t1.field3 like CONCAT('%',fieldx,'%')


You can join the table with itself:

SELECT c1.CustomerID, c1.Status, c2.CustomerID, c2.Status
  FROM Customers AS c1
  INNER JOIN Customers AS c2
    ON c1.Address1   =  c2.Address1
  AND (c1.Address2   =  c2.Address2 OR (c1.Address2 IS NULL AND
                                        c2.Address2 IS NULL))
   AND c1.Status     != c2.Status
   AND c1.CustomerID <  c2.CustomerID

The last line makes sure you don't get duplicate rows (unless there are more than two customers on the same address).

As suggested in the comments by Nick Roz, some fields could be NULL. Since NULL != NULL, you need to check specifically for those cases. In the query, I assumed CustomerID, Status and Address1 are never NULL, but Address2 can be.


If you're designing an OLTP database, you should really design it in third normal form initially.  If, once you've done that, you find that you can make measurable performance improvements by denormalizing something and there is no other way to get those improvements, it makes sense to denormalize selectively.  It does not make sense, however, to denormalize to first normal form proactively.

I don't really understand why you expect that adding a mapping table would be problematic.  Yes, you'll need to join to it.  But that's what a relational database was built to do-- it joins tables very very efficiently.  Plus, you won't have to constantly parse the delimited string to figure out the T2 keys.  And you will be able to create a foreign key that ensures that the T2 keys in the mapping table are valid and that you don't have keys in your delimited string that don't map to a valid row in T2.  I don't really see the downside.

If you have a separate mapping table and you want to return a single row of data to the client with the T1 data and the T2 keys (which seems awfully suspicious-- the T2 keys are only useful if you are going to subsequently use them to query T2 in which case you almost always want to just let the database join to T2 in the initial query) you can always pivot the data in the mapping table to return a delimited string of T2 keys.  I certainly wouldn't recommend that but it can be done.


Preparing also include parsing and compilation of query into native inner code. Replace direct queries by stored routines that compiled once at creation time.


Does this computed CHECKSUM() index design make sense?

You are right, this is pointless.

Two (of many) reasons that I see it's wrong

it isn't guaranteed unique (CHECKSUM gives int) whereas the GUID is (over the range of GUID). It's a small chance of duplicate but quite possible: like the "birthday problem" somewhat
it's still random order. The main reason IDENTITY is better then GUID for a clustered index is that IDENTITY is monotonically increasing. CHECKSUM(someGUID) is random order too

I'd add a new IDENTITY column, and then start changing dependencies to use this only.


Database design for assign ticket or task

With most designs, there is some kind of trade-off. One down side with what you suggested is that it is not possible to find a candidate key in ASSIGNED. Consider what it would mean if someone added:

INSERT INTO ASSIGNED (ID, TICKET_ID, TASK_ID, ASSIGNED_TO)
VALUES (5, 1, NULL, 25);

From my perspective, you would be better off with two tables:

CREATE TABLE ASSIGNED_TICKETS
( USER_ID ... NOT NULL REFERENCES USERS (USER_ID)
, TICKET_ID ... NOT NULL REFERENCES TICKETS (TICKED_ID)
,    PRIMARY KEY (USER_ID, TICKET_ID) );

If a ticket cannot be assigned to several users, make TICKET_ID the primary key.

A similar table can be used for ASSIGNED_TASKS.

An alternative design is to use a super table, I'll call that WORK_UNIT for lack of a better word.

CREATE TABLE WORK_UNIT
( WORK_UNIT_ID ... NOT NULL PRIMARY KEY
, WORK_UNIT_TYPE CHAR(4) NOT NULL
,   CHECK (WORK_UNIT_TYPE IN ('TICK', 'TASK'))
, ... );

The relationship between TICKET and TASK can be represented by a separate table:

CREATE TABLE TICKES_FOR_TASKS
( TASK_ID ... NOT NULL
      REFERENCES WORK_UNIT (WORK_UNIT_ID)
, TICKET_ID ... NOT NULL
      REFERENCES WORK_UNIT (WORK_UNIT_ID)
,   PRIMARY KEY (TASK_ID, TICKET_ID) );

If you want to enforce the types in that relation (for example so that a TICKET can't consist of other TICKETS), you can add a UNIQUE constraint to WORK_UNIT:

ALTER TABLE WORK_UNIT ADD CONSTRAINT ...
    UNIQUE (WORK_UNIT_ID, WORK_UNIT_TYPE)

and "inherit" WORK_UNIT_TYPE in TICKES_FOR_TASKS:

CREATE TABLE TICKES_FOR_TASKS
( TASK_ID ... NOT NULL
, TASK_TYPE CHAR(4) NOT NULL
,     CHECK ( TASK_TYPE = 'TASK' )
,     FOREIGN KEY ... (TASK_ID, TASK_TYPE)
      REFERENCES WORK_UNIT (WORK_UNIT_ID, WORK_UNIT_TYPE)
, TICKET_ID ... NOT NULL
, TICKET_TYPE CHR(4) NOT NULL
,     CHECK ( TICKET_TYPE = 'TICK' )
,     FOREIGN KEY ... (TICKET_ID, TICKET_TYPE)
      REFERENCES WORK_UNIT (WORK_UNIT_ID, WORK_UNIT_TYPE)
,   PRIMARY KEY (TASK_ID, TICKET_ID) );

I've heard of (but never used myself) DBMS that support CHECK constraints with selects. You could then avoid "inheriting" the type attributes. Another way to avoid this is to use triggers that throws an exception if a ticket or task is of the wrong type.

In this scenario you only need one table for ASSIGNMENTS:

CREATE TABLE ASSIGNED_WORK_UNITS
( USER_ID ... NOT NULL REFERENCES USERS (USER_ID)
, WORK_UNIT_ID ... NOT NULL REFERENCES WORK_UNIT (WORK_UNIT_ID)
, ... -- additional attributes
,    PRIMARY KEY (USER_ID, WORK_UNIT_ID) );

As, before. If a WORK_UNIT_ID can't be shared between USERS, make WORK_UNIT_ID the primary key.

No design is without flaws so you have to carefully consider for and against each possible one. Hopefully, his will give you some ideas on what to consider.


Setting up a table with a calculated column

Why not just a smallint to represent "minutes"?
This is easily translated or formatted (in client code) or SUMmed etc

The actual conversion to minutes can be done in client code or as a DATEDIFF between start and end. I wouldn't use time personally for this because it won't process well (eg can't have > 23:59:59.9999999 if you want to see the monthly time


How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

It is not possible to have an INCLUDE column for an index that enforces a constraint.  This has been brought up, but marked as "won't fix".

If the benefits of having a covering index are that great, consider revising the indexing strategy that you currently have on that table to maximize query performance.


I would make the following changes, to solve the issue and fix other minor problems with the query:

Solving the issue:

use LEFT instead of INNER join, in order to get results for skus that don't have orders.
remove the HAVING clause as it would essentially cancel the previous change.
use COALESCE() or IFNULL() to get 0 instead of NULL for these skus.

GROUP BY issue:

use SUM() or MIN() or some other aggregate for all columns in the SELECT that are not in GROUP BY.

If you use IFNULL(tbl_sku_units_order.units_ordered, '0') AS 'unit_order' for example, when the group by is GROUP BY tbl_list_sku.sku, you may have many rows - with different units_ordered values - in the orders table. Which one should be shown?

Now MySQL allows that (in versions before 5.7) but the result is indeterminate (any of those values may be shown). The bug/feature has been fixed in version 5.7 but it's good to use correct code, first to get sane results and second so it won't break when you upgrade.

Easier to read code:

use aliases for table names
use back-quotes or double-quotes for column aliases, or just not put spaces in names/aliases so everything can be typed without quotes.

The query:

SELECT
    ls.sku                                AS list_sku,
    COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku,
    COALESCE( SUM(suo.units_ordered), 0 ) AS today_sold
FROM   tbl_list_sku AS ls
       LEFT JOIN tbl_sku_units_order AS suo
              ON suo.sku = ls.sku
GROUP  BY ls.sku
ORDER  BY today_sold DESC ;


Those commands do two things:

Clear the page cache, which stores data pages that have already been retrieved from disk (normally the biggest factor in time in a query is disk access)
Clear the query plan cache, which means the server needs to create a new query plan.  This is normally not significant except for very high transaction volumes.

You are basically getting a time that is equivalent to the "worst case" scenario - you just rebooted the server and nothing is in memory.  Subsequent runs don't need to pay the cost to pull the data from disk since those pages are already loaded into memory.

This is similar to a real world situation - your first user to run a particular query will likely have to wait longer than on subsequent runs, assuming you are checking the same data.

A good method that I like to use is running multiple times and taking an average.  This is especially helpful in a shared environment since you don't have full control over shared resources like tempdb.

You can also use these commands to get more information on what is actually happening behind the scenes:

SET STATISTICS IO ON
SET STATISTICS TIME ON

These will give you detailed information about page reads from disk (per object), logical page reads, time spent compiling a plan, and time spent executing a query.


Is it OK to use SQL Server availability groups on VMware installations?

Sure, you can use Always On Availability Groups inside VMware virtual machines (or Hyper-V, or Xen, or cloud provider VMs, etc.)

They're actually a great fit for high availability and disaster recovery for virtual machines because each VM has its own copy of the database.

Keep in mind that if you're trying to avoid single points of failure, then don't put all replicas on the same shared storage, along with your backups. As the great philosophers once said, you gotta eep 'em separated.


Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration?

As the comments indicate Oracle Global temporary tables work differently than some might expect.  They are intended to be permanent structures.  The settings allow for data to be retained when a session is closed or deleted.

The key reason to use them is that each user can only access data for that user.

Your developers can all use the same table and insert/update/delete without interfering with other users.

There is no need to drop a GTT after using it.


Trigger working on more than 50 million records

The row being updated by the trigger will be locked until the end of the transaction.  If the same combination of s,c,p,m,e is inserted by two concurrent transactions, one will block until the other commits or rolls back.  If concurrent transactions never touch the same rows, they will not block each other.

  via a pgsql loop

What is that?  Are you talking about PL/pgSQL?  All in one transaction?  If two concurrent transactions are inserting 50,000 rows each, it is almost certain that there is at least one overlap between them.

  run that file in 1 batch

Again, what is that?  psql -f?  Is it just a list of insert, or is it wrapped in BEGIN, COMMIT statements?


Use predicates with name tests like this instead of "simple" node steps:

/*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a']


Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL

its okay, and sorry for the bad explanation.

i got it to work as shown below:

CREATE OR REPLACE FUNCTION tf_xy_point()
  RETURNS trigger AS
$BODY$BEGIN

IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN

    new.easting := ST_X(new.wkb_geometry);

    new.northing := ST_Y(new.wkb_geometry);

RETURN NEW;

END IF;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tf_xy_point() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO public;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO postgres;

This now allows for the trigger to used as generic for any other table wishing to find the eastings and northings on UPDATE.


Cassandra: Request did not complete within rpc_timeout

From the EOFException I take it that your problem is not the rpc_timeout but a corrupt data file. And it seams that Cassandra is not properly handling the exception and never returns the error to the piece of server code that "holds" your client connection and waits for data to send back to the client. Thus the timeout.

You could try and run nodetool repair <your_keyspace>. Maybe it is able to fix the corrupted data file. If not, try to drop the keyspace and restore it again.

To answer your question. The timeouts are set in the cassandra.yaml file in the config directory of your cassandra installation on each node.


Scheduling MySQL Workbench to execute and export on a daily basis

Use the Windows Task Scheduler or if you want to use the command line, then an at job should do the trick. On Window 7, the schtasks (see also here) should be of use.

MySQL Workbench is not for doing this, except if you have the Enterprise Edition.


Append on Trusted_Connection=True to the end of your connection string in your config file:

server=sunny-lptp\sqlexpress;database=SchoolsDB;Trusted_Connection=True

Great resource for connection strings


Get statistics of open connection to linked servers

SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.

I would start by running my usual "find any connection that is waiting" query:

SELECT
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
    (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
        ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
  LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
  LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
  OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where  blocking_session_id != 0)
)
ORDER BY es.session_id

I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.


Find the remote userid causing updates on my table

You cannot, if you are using a generic username to log in to SQL Server. Host name is as close as you can get. It shouldn't be hard to translate it to a user name with a query in Active Directory.


How to convert SQL Server database file to MongoDB?

You could also make use of the OOP paradigm; you could then write a simple program, that will basically do the following.

create Classes for each of your SQL Server relations
create your MongoDB structure acording to those models
load the data into objects
insert it into Mongo

This solution could be feasable or not, depending on your DB's size/structure. That should do it. A straight-forward one-click solution I don't think there's available.


Try filtering returned objects based on the message text.

Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddMinutes(-15)) | Where-Object {$_.message -Like "*invoked by User domain\serviceaccount*"}


SQL Server sp_addrolemember in stored procedure for different database

EXEC('USE mydb;ALTER ROLE myRole ADD MEMBER myMember;');

But I am not quite sure what you are trying to achieve. After restoring a database to a different server, you need to fix the link between users and logins. For that you can execute this in your database:

ALTER USER myUser WITH LOGIN myLogin;

Or use the same EXEC('USE...') method shown above with that ALTER USER statement. That should get you where you need to be. All the database related permissions are stored in the database itself, so they get restored with the database.


HQL pagination returns same result on different pages - with SQL

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

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

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


Trigger to automatically calculate area when adding polygon in PostGIS

This is not a very good idea, and there is no need to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this:

SELECT * ON table WHERE ST_Area(geom) > x;

will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row your trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs (emphasis added):

  The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.


How to make a test Database grow in size by running transactions to test autogrowth factor?

In order to grow a DB you do not need to produce "real" data. Anything that uses space on disk will suffice.

SQL Server does IO in 8kB chunks - a page. By making a table with a row size over half this (i.e. > 4kB) we ensure a single INSERT uses 8kB of disk. I'd propose adding a new table to your test instance. Define it thus:

create table dbo.SpaceFiller
(
    spacefiller_id  int  identity(1, 1) primary key clustered,
    data            char(4100) not null
);

Column data is what consumes disk. Note it is char not varchar. This ensures the whole 4100 bytes is allocated. The size of 4100 is somewhat arbitrary. It just needs to be over half a page in size.  The primary key column is there to make post hoc analysis a little easier, and because adding a surrogate key is a hard habit to break!

Now add rows:

insert dbo.SpaceFiller(data)
values ('');

The column data is defined as fixed length. Therefore, no matter what is actually written to it, it will occupy the defined 4100 bytes. So, to save typing, I just write an empty string. It may seem odd but, because it is a fixed length column, the same amount of space will be used. This is just how SQL Server stores fixed length columns.

Wrap this in a WHILE, or whatever your preferred mechanism is. As each iteration will consume 8k you can do the math to find how many cycles to perform. If the DB already has allocated but unused space that space will be used first. You can force it to use new, dedicated space by putting dbo.SpaceFiller in its own filegroup.

You can see how things are going:

exec sp_spaceused 'dbo.SpaceFiller';

If you want to capture the actual moment of growth you could start an Extended Event session, or use Profiler, or monitor the OS action.

To re-test, drop the table and shrink the DB. Better still, restore a backup.


Connecting to an 11g Standard Database (Local)

Ok, you have the database software installed. Here is the 11.2 Installation Quick Guide for Windows Here is the 11.2 documentation home. Now you need to install a database instance and start a listener. Then you need to create a connection in SQL Developer to the database instance.

Some essential tools during this are SQLPlus on the server. Enterprise Manager Database Control http://host.domain:1158/em/ You will need to Remote Desktop into the server to perform database setup tasks as the SYS user. On the server DOS window:

sqlplus /nolog
SQL>connect sys@myinstance AS SYSDBA
enter your password.

If you did not create a database instance during the install, use Database Configuration Assistant (DBCA). A universal approach for client connectivity is to use a tnsnames.ora file located in $ORACLE_HOME/network/admin. The parameters for this become the connection parameters in SQL Developer.

In a DOS window on the server, run the listener control with

lsnrctl help
lsnrctl status   tells you which instances are running.

Once you have the instance created, create a new tablespace to hold your tables. Then create a user that will be the schema owner of the tables associated with that tablespace. It is a good practice to create a separate user for each person in addition to the schema owner. Then create tables, views, stored procedures, etc.

Hope this is enough to get you started. The links are good reads.


Mysql 5.6 Replication Lag fluctuating between 0 and n

Seconds_behind_master bouncing between 0 and some 'large' value?

I have seen that scenario for 16 years.  I have never found the cause or cure.  The problem usually goes away after a day or two.

Bottom line:  Ignore it.


How do I Add Integration Services to existing SQL Server 2012 Install that has no internet access

You have to create an administrative install point:
SSDTSetup.exe /layout <destination>
then copy the files to your isolated instance.
Check out https://blogs.msdn.microsoft.com/ssdt/2014/07/23/creating-an-administrative-install-of-ssdt-update-for-visual-studio-2013/ for details.


Ok I solved it: in the following line:

SET search_path = car, pg_catalog;

apparantly, car is the name of the schema. As that schema didn't exist, it tried to create a table in the pg_catalog schema. I manually created a schema called car, and, tada..
Thanks to everybody for their support.


select query to fetch json column internal objects

You can use jsonb_array_elements() for that:

with sample_data (json_col) as (
  values ('{"disks":{"value":[{"Type":"NA"},{"Type":"NA"}]}}'::jsonb)
)
select x.t ->> 'Type' as type
from sample_data, jsonb_array_elements(json_col -> 'disks' -> 'value') as x(t);

returns

type
----
NA
NA


Calculate the growth of deltas in SQL Server

There are plenty of posts on the Internet with various solutions for this.  A quick search of 'Sql Server Track Database Growth' found this one at the very top.  I haven't vetted it, so due diligence applies.  To summarize:

First create a table to keep the size information of each database with a date on which the information is collected:

CREATE TABLE DatabaseFileSize
(
[database_id] INT,
[file_id] INT,
[file_type_desc] NVARCHAR(120),
[name] NVARCHAR(128),
[physical_name] NVARCHAR(520),
[state_desc] NVARCHAR(120),
[size] INT,
[max_size] INT,
[growth] INT,
[is_sparse] BIT,
[is_percent_growth] BIT,
[collect_date] DATETIME
)
GO

Create a stored procedure to populate this table with the current database size information:

CREATE PROC usp_util_CollectDatabaseSize
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DatabaseFileSize
(
[database_id],
[file_id],
[file_type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
[collect_date]
)
SELECT
[database_id],
[file_id],
[type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
GETDATE()
FROM sys.master_files
SET NOCOUNT OFF
END
GO

Now we need to populate the table over a period of time, so we need to create a SQL Agent job to execute this stored procedure and schedule this job to run everyday.

USE [msdb]
GO
/****** Object:  Job [Collect_Database_Size_Info]    Script Date: 02/13/2013 23:47:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 02/13/2013 23:47:43 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect_Database_Size_Info',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Domain\User', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DBSize]    Script Date: 02/13/2013 23:47:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBSize',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC usp_util_CollectDatabaseSize',
@database_name=N'AuditDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyDBSize',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130213,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'445fb175-517b-4221-b6b8-6c6877dc7c24'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Now we have the information over the period of time and we can run our below query to make a report out of this data.

--DB Size Growth Trend
SELECT
DB_NAME(database_id) AS DatabaseName,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS DBSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,collect_date
ORDER BY DatabaseName,collect_date

--DB File Size Growth Trend
SELECT DB_NAME(database_id) AS DatabaseName,
(CASE file_type_desc
WHEN 'ROWS' THEN 'Data'
WHEN 'LOG' THEN 'Log'
END) AS FileType,
physical_name AS PhysicalPath,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS FileSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,physical_name,file_type_desc,collect_date
ORDER BY DatabaseName,collect_date

If you want to look back in time for database growth, you can use the information in Erin Stellato's blog post Trending Database Growth From Backups

This technique relies on information in msdb.dbo.backupset which is pruned by various maintenance plans and possibly other processes, so you may only be able to go back a few months. Here's the main query from that post:

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);


Count consecutive null rows from a joined table

This is essentially a gaps-and-islands problem. And when I have my SQL Server hat on, I often solve this kind of problem with two ROW_NUMBER() calls. Sadly, MySQL, unlike many other major SQL products, does not support ROW_NUMBER(), nor any other ranking function. To make up for that, however, you can use variable assignment in SELECTs, which MySQL does support (unlike many other major SQL products).

Below is a solution followed by an explanation:

SELECT
  member_id,
  member_name,
  event_id,
  COUNT(*) AS consecutive_times_missed,
  MIN(event_date) AS first_date_missed,
  MAX(event_date) AS last_date_missed
FROM (
  SELECT
    member_id,
    member_name,
    event_id,
    event_date,
    is_missed,
    @occ_ranking := (event_id = @last_event) * (member_id = @last_member) * @occ_ranking + 1,
    @att_ranking := (event_id = @last_event) * (member_id = @last_member)
                                             * (is_missed = @last_missed) * @att_ranking + 1,
    @occ_ranking - @att_ranking AS grp,
    @last_member := member_id,
    @last_event  := event_id,
    @last_missed := is_missed
  FROM (
    SELECT
      m.member_id,
      m.member_name,
      e.event_id,
      e.event_date,
      (a.attendance_date IS NULL) AS is_missed
    FROM       members     m
    INNER JOIN event_dates e ON m.member_join_date <= e.event_date
    LEFT  JOIN attendance  a ON m.member_id = a.member_id
                            AND e.event_id = a.event_id
                            AND e.event_date = a.attendance_date,
    (
      SELECT
        @occ_ranking := 0,
        @att_ranking := 0,
        @last_member := 0,
        @last_event  := 0,
        @last_missed := 0
    ) v
    ORDER BY
      m.member_id,
      e.event_date
  ) s
) s
WHERE
  is_missed = 1
GROUP BY
  member_id,
  member_name,
  event_id,
  grp
HAVING
  COUNT(*) >= 3
;

Basically, you start with joining members and event_dates to get all the event occurrences the members could have attended based on their membership dates. Then you throw in the attendance table (via a left join) to flag the missed occurrences. Here's an example of what you get by this time:

member_id  event_id  event_date  is_missed
---------  --------  ----------  ---------
1          1         2012-07-10  0
2          1         2012-07-10  1
1          1         2012-07-14  0
2          1         2012-07-14  1
…          …         …           …

At this point, the resulting set needs to be sorted by member_id, event_id, because that is crucial to the subsequent rankings calculation.

Two different rankings are produced for every row. One is a ranking within the row's partition of (member_id, event_id) (it is reset as soon as a new event or a new member is encountered). The other is a ranking within the specific group of consecutive event occurrences, either attended or missed, that the row belongs to (this ranking, in addition to being reset upon coming across a new member or event, also gets reset whenever the other group is encountered). And so you get something like this:

member_id  event_id  event_date  is_missed  ranking1  ranking2
---------  --------  ----------  ---------  --------  --------
…          …         …           …          …         …
1          1         2012-07-27  0          4         4
1          1         2012-07-28  0          5         5
1          1         2012-07-29  1          6         1
2          1         2012-07-10  1          1         1
2          1         2012-07-14  1          2         2
2          1         2012-07-25  1          3         3
2          1         2012-07-27  0          4         1
…          …         …           …          …         …

As you may have noticed, the difference between the two ranking numbers is constant throughout the particular group of consecutive event occurrences of the same kind ("attended" or "missed") and is also unique for that group within its partition. Therefore, every such group can be identified by member_id, event_id and the just mentioned difference.

And it now remains simply to filter the events leaving just the missed ones, group the rows and get the necessary aggregated data, like the number of rows and, possibly, as in the query above, the dates of the first and the last event occurrence in the group. The number of rows is also used in an additional filter condition to omit groups with fewer rows than required.


How to return several arrays of doubles as a table in C# SQLCLR function

If you are wanting to return the 3 arrays as 3 separate result sets, that is not possible for a function, whether SQLCLR or T-SQL. You would need to create a stored procedure instead in order to return multiple result sets.
If the 3 arrays represent 3 columns of a single result set such that they will all have the same number of items and the index value of one is conceptually associated with the same index value of the others (i.e. array1[x] is associated with array2[x] and array3[x], while array1[y] is associated with array2[y] and array3[y], and so on), then a simple array is the wrong type of collection. You can only return a single collection where each item/element in the collection represents a row in the result set (or at least enough info to construct the desired row). That singular collection, when returned from the SqlFunction method, is iterated over, calling the FillRowMethod for each item/element. One item/element is passed into the FillRowMethod which constructs the final result set structure and values and passes them back (hence you have the opportunity to create and/or translate values from the original item before passing them back).
In this latter case, you would need to create a class similar to:
private class volatility
{
    public double impliedVol;
    public double maturity;
    public double strike;
}

Then, create a generic list of those in your getStrippedCapletVolatilitiesFromCapVolatilityCurve method, add a new item to that collection for every row to return, and then return that list/collection. Your FillRowMethod will be called with the first parameter (as object) being of type volatility. That is where you will populate the out params from those properties of volatility. For example:
private static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
    volatility row = (volatility)obj;

    impliedVol = new SqlDouble(row.impliedVol);
    maturity = new SqlDouble(row.maturity);
    strike = new SqlDouble(row.strike);
}

Now, it might be possible to handle this as a two-dimensional array (i.e. double[][]) that you return from the main SqlFunction, but then the FillRow method would be sent a double[] since the first dimension is broken out into individual calls to the FillRow method. I've never tried this specific approach, but it should work as follows:
private static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
    double[] row = (double[])obj;

    impliedVol = new SqlDouble(row[0]);
    maturity = new SqlDouble(row[1]);
    strike = new SqlDouble(row[2]);
}

ALSO:
It just occurred to me that you could even forgo the generic list / collection and stream the contents of the double[][] array out to the result set, one item / row at a time. Go ahead and try this:
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(...)
{
    //omitted code above this line.
    CapletStipping thisCapletStripping =
       new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
    double[][] theseStrippedCapletVols =
       thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

    // THIS PART IS DIFFERENT -- begin
    capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode();

    for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
    {
        thisCapletVolatilityNode.impliedVol = theseStrippedCapletVols[0][i];
        thisCapletVolatilityNode.maturity = theseStrippedCapletVols[1][i];
        thisCapletVolatilityNode.strike = theseStrippedCapletVols[2][i];

        yield return thisCapletVolatilityNode; // return rows individually
    }

    return; // cannot return anything when using "yield return"
    // THIS PART IS DIFFERENT -- end
}

private class capletVolatilityNode
{
    public double impliedVol;
    public double maturity;
    public double strike;
}

There are some restrictions when using yield return, but if your process allows for this construct, then this will not only be faster, but also take up less memory. These benefits are due to this code skipping the step of copying the results of the getCapletCurveForGivenStrike() method to a separate collection (i.e the Generic List) just to return it to T-SQL (in which case you need to wait while it copies the collection and consumes more memory).

On a related note: use the Sql* types for the input parameters instead of standard .NET types. Meaning, use SqlString instead of string and SqlDouble instead of double. You can then easily get the .NET native type from those via the Value property that all of the Sql* types have (e.g. SqlString.Value passes back a string, etc).
For more info on working with SQLCLR in general, please visit: SQLCLR Info


How to prevent redundant relationships in data?

It sounds like you are describing a mutual relationship such that there is no difference between (1,2) and (2,1).  In this case you should design your code to always insert the lower userId as the first value and the higher as the second.  Then you can use AlexKuznetsov's suggestion and add a CHECK constraint of (userId<relatedUserId).  The code can then catch the duplicate exceptions and ignore them since the relationship already exists.

Note: I flipped the comparison from > to < because the lower number being first makes more sense to me.  It works either way and may make more sense the other way in your environment.


how do I check the last time dbreindex or an index defrag was executed

Try one of the queries below and see if that is what you are looking for:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_system_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YourDb')
AND OBJECT_ID=OBJECT_ID('YourTable')

select name as index_name,
         stats_date( object_id, index_id) as stats_update_date
from  sys.indexes
where object_id = object_id('YourTable');

Select *

From sys.dm_db_index_usage_stats


How can I create different sets of sequential values based on another column in a table?

You could use partition by on the note type, which allows a calculation to be reset by groups.  For example:

create table OldNotes (
       OldNoteRef           varchar (20) not null
      ,OldNoteType          varchar (20)
      -- Other data goes here
)
go

insert OldNotes (OldNoteRef, OldNoteType) values ('CABC123', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC456', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC789', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ001', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ034', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('XZYZ100', 'D')
go

select OldNoteRef
      ,OldNoteType
      ,row_number() over (
          partition by OldNoteType
          order by OldNoteRef
      ) as NewNoteID
  from OldNotes
go

Would produce the following results, which will give you a sequence within the note type.

╔════════════╦═════════════╦═══════════╗
║ OldNoteRef ║ OldNoteType ║ NewNoteID ║
╠════════════╬═════════════╬═══════════╣
║ CABC123    ║ C           ║         1 ║
║ CABC456    ║ C           ║         2 ║
║ CABC789    ║ C           ║         3 ║
║ DXYZ001    ║ D           ║         1 ║
║ DXYZ034    ║ D           ║         2 ║
║ XZYZ100    ║ D           ║         3 ║
╚════════════╩═════════════╩═══════════╝

BTW, table produced by http://www.sensefulsolutions.com/2010/10/format-text-as-table.html


Table that is a relation of one field to every other similar field

Solved this after some clever googling.

The Query is

INSERT INTO tblSeen(UserID, ProfileId)
SELECT 'some_user', pmkID FROM tblOwners

This will create as many rows as it can and fill it out, so I just have to do this when the user goes to the page.


How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?

varchar doesn't support ≥: unicode only

This includes the literal '≥' which is varchar too

Collation here doesn't matter: this is sorting and comparison only never seen this before!

Some more SQL to use your table

SELECT ASCII('≥'), CHAR(61), '≥'
GO
insert into char_test values ('≥');
GO
select item, ASCII(item) from  char_test;
GO
select replace(item, '≥', '>='), replace(item, '=', '>=') from  char_test;
GO

create table nchar_test(
    item varchar(10) collate SQL_Latin1_General_CP1_CI_AS,
    Nitem nvarchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
insert into nchar_test values ('≥', N'≥');
GO
select item, ASCII(item), Nitem, UNICODE(Nitem) from nchar_test;
GO

Edit: after more playing

If you have a value then use CP 437

select CAST(Nitem COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar) from nchar_test;
GO
select CAST(N'≥' COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar)
GO

Note: the ASCII is 242 but this won't give either :-)

SELECT
   ASCII(CAST(N'≥' COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar)),
   CHAR(242),
   CHAR(242) COLLATE SQL_Latin1_General_CP437_CI_AS

You may be able to control this with database collation but frankly use unicode...


Is it unwise to run replication on same physical server?

For redundancy in terms of system reliability and data safety running a slave on the same machine as the master offers you nothing (or close to). If something bad enough to bring down the master happens it will probably bring down the slave too.

For purely segregating users for access rights reasons, a good RDBMS will offer more effective ways of doing that.

Running the two databases on the same machine will require more RAM to run at the same efficiency as the two databases will compete for space to keep they various buffers and caches. There might be a performance benefit though via IO-load segregation, if the datafiles for the slave are on different physical drives than the master. In this case you can run complex reports that required many disk reads against the slave without competing for the master for drive IO bandwidth.

Edit: as DTest mentions in his comment below, one other possible benefit of a slave DB (even if on the same drives as the master) is that complex long-running queries in the slave that might otherwise cause locking issues for the day-to-day-running queries on the master are safer. Though you are still better off having the slave on different drives as such significant queries are likely to cause IO contention issues.


ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xx.xx' (10060)

I had a similar issue. I believe there are two ways to resolve it (though only one worked for me).

The first way (that did not work for me) would be to create a client certificate via the Cloud Platform -> SQL -> [Instance] -> Access Control tab -> SSL tab -> Create Client Certificate button.

It will give you instructions for downloading the client certificate to your machine and using it to connect via SSL.

The second way (which did work for me) was to allow non-SSL connections. You can find the button in the the "SSL Connections" section under the same SSL tab (under Access Control for the instance). Then try connecting with gcloud sql connect [instance-name] --user=root. This will automatically send a request to allow your IP address to connect directly to the database, and then attempt to connect via the mysql tool.


Get ID which has two rows with specific values for one column

WITH cte AS ( SELECT id, type, COUNT(DISTINCT type) OVER (PARTITION BY id) cnt
              FROM table
              WHERE type IN ('A', 'B')
            )
SELECT id, type
FROM cte
WHERE cnt = 2


What I would do is to create a table with the stock_id (that can be the alphanumeric code or a integer), the timestamp of the measurement and the current value. That is your entry data, 3 columns.

From that point you can add columns for calculations (the difference absolute or percent) with the previous value. Having all in the same table will simplify the model and ease your queries. Try to create a date (not timestamp) column and create a partition by it. It may lighten a bit the access to the table as long as you set it in your queries.


SQL Server 2019: InitPersistentVersionStore failed after installing CU2

Eventually, I got an official answer from Microsoft Support which I am glad to share here:

  Hello Martin,   Hope you are fine.   Thanks for call yesterday. As
  validated from Product Group:

  This is only a info message and not a bug. All system databases that
  come from SQL17 to SQL19 will not have persisted version store
  initialized because of the way we did upgrade step in SQL17. But this
  should not affect customer’s databases.”   But we probably will not
  fix the message on next releases as it’s harmful – that’s why was not
  fixed until now.   The question we have discussed, as this is
  something that harmful but should not be there, so you will not be
  charged for this case.   I will close the case and mark it as product
  issue.

It's a bit contradictory on whether the message will be fixed or not....last statement on the phone was that in a problematic case it typically arises as a followup error due to database recovery problems. In my case that is not true and it's totally unproblematic...I even don't use ADR.

A big recommendation for everyone upgrading from SQL 2017:
Do not perform an in-place-upgrade but rather do a fresh install of SQL 2019 to avoid seeing this error message.


Oracle insert through dblink performance issue

The driving_site hint, as suggested by Alex Poole would be an interesting solution to consider/investigate. I'm not sure how/if it works for remote inserts though.

If the hint doesn't work, you can use a local temporary table. Insert the data locally into your temporary table. Then INSERT INTO tab@remote FROM temp should perform well.


It wraps around to FirstNormalTransactionId (which is 3), rather than to zero.  And then it continues to increment and be stored into xmin and xmax like normal.


Linking first and second level composite primary keys

You need 2 columns in the foreign key constraint, to refer to a table with  2 columns in the primary key:

-- Second higher level combining
CREATE TABLE full_b_c(
    b_ID INT NOT NULL,
    c_ID INT NOT NULL,
    a_ID INT NOT NULL references a ON UPDATE CASCADE,
    PRIMARY KEY (b_ID, c_ID, a_ID),
    FOREIGN KEY (b_ID, c_ID)
      references b_c (b_ID, c_ID) ON UPDATE CASCADE
);


Reading SQL Server 2008 transaction logs

Logs are proprietary and those solutions are expensive because it is not simple and the average person does not know how to do it. Since you're using SQL Server 2008, have you considered some other more accessible options, such as:

SQL Audit
Server-side trace
Change Tracking
Change Data Capture
Taking snapshots of the DMV sys.dm_db_index_usage_stats
Forcing access through a stored procedure and employing custom logging

I wrote about some of these here (before 2008 was released):

https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed


Explain how this real injection attempt works

The classic SQL injection attack attempts to extract sensitive data through the user interface. This, however, seems to be poisioning your data by appending valid HTML to it. The attacker's assumption is that you trust data coming from the DB so are likely to embed it directly into your web app's output and thus display to your users links to their site.


Index pages (type 2) hold the non-leaf level(s) of the clustered index b-tree. The leaf level of the clustered index is just the underlying object's data pages themselves.

In the special case where the whole object (table in your case) fits in a single page, SQL Server does not create a separate index page, because there is no need.

In your example, the first index page is created when the page split results in the table consisting of more than one data page for the first time.

Index pages are also used for all levels of nonclustered b-tree indexes.

Index pages can also be inspected with DBCC PAGE. Dump style 3 is the most informative, as it shows the child file and page pointers for index key ranges. This is the information needed to navigate down (potentially multiple levels of) the b-tree index.

As the number of rows in the index increases, the number of levels in the index will also increase.

See:

Clustered Index Structures
Nonclustered Index Structures
Heap Structures
Table and Index Organization

Comprehensive information about table and index internals can be found in the Microsoft SQL Server Internals books by Kalen Delaney et al.

A new index page will be created whenever an existing index page needs to split. If that page was the root page (top of the index tree), the index gains a new level as a result. Index pages can split if the index key is widened, or if another entry is needed on that page (e.g. to refer to a new child page) and there isn't enough room.