To Join two tables without using Primary key

You can join on any column combination you like. You can even join on a condition that does not reference a column at all:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.TblA(a_id INT, c1 INT);
GO
INSERT INTO dbo.TblA(a_id,c1)
VALUES(1,345),(2,132);
GO
CREATE TABLE dbo.TblB(b_id INT, c2 INT);
GO
INSERT INTO dbo.TblB(b_id,c2)
VALUES(1,245),(2,456);
GO

Query 1:

DECLARE @some_value INT = 0;

SELECT *
  FROM dbo.TblA AS A
  JOIN dbo.TblB AS B
    ON @some_value = 2;

Results:

[no rows returned]

Query 2:

DECLARE @some_value INT = 2;

SELECT *
  FROM dbo.TblA AS A
  JOIN dbo.TblB AS B
    ON @some_value = 2;

Results:

| A_ID |  C1 | B_ID |  C2 |
|------|-----|------|-----|
|    1 | 345 |    1 | 245 |
|    1 | 345 |    2 | 456 |
|    2 | 132 |    1 | 245 |
|    2 | 132 |    2 | 456 |

In this example you get either the cartesian product of all rows in both tables, or no row at all, depending on the condition being met.

Now, if you have a foreign key declared, joining on those linked columns is called a natural join an that is the most common scenario for a join. But as you have seen, it is not the only possibility.

Independent of declared relational integrity, you want to make sure that your tables are indexed appropriately to support the (join) queries your application is made of. Adding an index on columns used in a join is usually a good idea (but there are exceptions).

For more information about SQL Server joins, check out my blog series from last december: A Join A Day


How PostgresSQL stores duplicated VARCHAR values in a column

It stores what you inserted. Normalize it.

PostgreSQL may store larger text and varchar values out-of-line in a side table, possibly compressed. But not small labels.

BTW, varchar(255) has no effect in postgres, it's the same as text with a length check constraint.


Most likely you are using a different shell version than the db version. That is, the output of db.version() (the server version) and version() (the shell version) should be the same.

For example:

mongos> db.version()
3.4.1
mongos> version()
2.6.12
mongos> config = db.getSiblingDB('config')
config
mongos> config.shards.updateOne({_id:'shard01'},{$set:{maxSize:4194304}})
2016-12-22T13:22:56.154+1100 TypeError: Property 'updateOne' of object config.shards is not a function

Note the identical error message to the one you're seeing. This is because updateOne function doesn't exist in early mongo shell. However, using the correct version:

mongos> db.version()
3.4.1
mongos> version()
3.4.1
mongos> config = db.getSiblingDB('config')
config
mongos> config.shards.updateOne({_id:'shard01'},{$set:{maxSize:4194304}})
{
  "acknowledged": true,
  "matchedCount": 1,
  "modifiedCount": 1
}


SmallDateTime to DateTime Conversion Equality

Converting Smalldatetime to datetime will not cause any issue other than taking double the space for that column.  Look at the the SQL Server Data Type Conversion Chart.

Smalldatetime uses 4 bytes of space compare to datetime uses 8 bytes. Both are fixed length.

From books online:

  When the conversion is to datetime, the smalldatetime value is copied
  to the datetime value. The fractional seconds are set to 0. The
  following code shows the results of converting a smalldatetime value
  to a datetime value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';
DECLARE @datetime datetime = @smalldatetime;

    SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';

    --Result
    --@smalldatetime          datetime
    ------------------------- -----------------------
    --1955-12-13 12:43:00     1955-12-13 12:43:00.000
    --
    --(1 row(s) affected)][2]

I know in some blogs and articles it is mentioned that implicit conversion will cause a scan instead of seek or will not use the index at all. Which is true in many cases but not always.  In this case you will get a seek. See this article by Jonathan Kehayias.


Stop/start the service, nothing else will release the memory back to the OS.

Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way.

Edit following @Nick's comment.

  As James noted, if it reserves more than the minimum you specify, it
  should in theory release it when it doesn't need it and there is
  memory pressure on the machine.

What's your definition of "doesn't need it"? Once the buffer pool has been filled, it isn't going to drop pages out until they become disfavoured by other pages being brought in. If you want the memory back immediately, as is the case for @jrara, you've got to stop/start.


How are "DEFAULT PRIVILEGES" implemented in Postgresql?

information_schema is an ANSI-standard set of read-only views. You can easily learn what that views actual query are by reading the code which is here. The real catalog information is stored in System Catalogs.

As documentation says 'There is no ALTER DEFAULT PRIVILEGES statement in the SQL standard.' So there shouldn't be anything related to information_schema.

Your records should be stored in pg_default_acl which stores initial privileges to be assigned to newly created objects.


How to log ALL queries made by users in SQL Server 2017 Standard?

Common Criteria Compliance is an enterprise feature

In the standard edition, well any edition,  you can create a trace and trace "Audit Schema Object Access Event" that will give you a statement level audit. You can then filter on objects or databases to minimize the logs and create the trace as a server side trace.

The Audit Schema Object Access Event seems not to be accessible using extended events but the server side trace can be set up to stop the server if logging is no longer possible and is detailed enough to be acceptable as a compliant audit for PCI requirements

As I am corrected below, in later versions of SQL Server you can setup a database audit in all editions and audit all statements against a database or specific objects.


Deselect CPU in affinity setting for OS Only use?

Changing CPU affinity was never a common practice but did have it's uses on WindowsNT and later on Windows Server 2000/3,

The main issue was that processor load could be misaligned on multiprocessor systems and this allowed for freeing up resources. This could also be helpful on systems that where not dedicated to running SQL Server. So yes this could be beneficial in some edge cases. Old Small Business Servers come to mind.

On later versions of Windows the OS is more likely to distribute load between processor so for performance reasons this will not help but can be beneficial on servers running multiple instances if you want to limit those to specific CPU or NUMA node


Veryfing pg_restore database with pg_dump database

Thanks to A_horse_with_no_name for a suggestion, i did some of the tables select count(*) and returned identical rows for each table.


If you want the result as rows in instead of as columns as in @a_horse's answer then create the year ranges in a CTE and join the table to it

with years(year_range) as ( values
    (int4range(1900, 1945, '[]')),
    (int4range(1946, 1964, '[]')),
    (int4range(1965, 1974, '[]')),
    (int4range(1975, 1991, '[]')),
    (int4range(1992, 2005, '[]')),
    (int4range(2005, 2014, '[]'))
)
select year_range, count(*)
from
    dataset d
    left join
    years y on d.year <@ y.year_range
group by 1
order by 1

http://www.postgresql.org/docs/current/static/rangetypes.html


How do I ensure I have split my tables without loss of data?

A classical theorem in normalization theory says that you have a lossless join (that is a decomposition of a relation such that when you perform the natural join of the decomposed relations you obtain the original relation) in the case of two relations  if and only if the intersection of the attributes of the relations contains a candidate key for one of the two relations.
This result can be generalized to a decomposition with any number of relations.


InfoBright LOAD DATA INFILE Wrong data or column definition

You can use SET clause of LOAD DATA INFILE to truncate values that are being read from file on the fly

LOAD DATA INFILE '/path/o/your/file.txt'
INTO TABLE table_name
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
(column1, @column2, column3, ...) -- first you read a value into a variable
SET column2 = LEFT(@column2, 100) -- then you assign truncated value to the column


PURGE TABLE test;

or

PURGE RECYCLEBIN;

From docs:

  The following statement removes the table test from the recycle bin.
  If more than one version of test resides in the recycle bin, Oracle
  Database removes the version that has been there the longest:

PURGE TABLE test;

  To determine system-generated name of the table you want removed from
  your recycle bin, issue a SELECT statement on your recycle bin. Using
  that object name, you can remove the table by issuing a statement
  similar to the following statement. (The system-generated name will
  differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

  Remove the Contents of Your Recycle Bin:

  Example  To remove the entire contents of your recycle bin, issue
  the following statement:

PURGE RECYCLEBIN;

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9018.htm


Selecting only from multiple tables in a transaction

"consistent snapshot" and "repeatable reads" consistency seem to do exactly what you are asking, even mysqldump uses it with its --single-transaction option.

Because InnoDB implements MVCC, the DML running in parallel won't bother you when all the selects are non-locking (and from InnoDB tables).

The only way how to get "outside" is using SELECT .. FOR UPDATE which will be executed in the "read commited" isolation mode even when the transaction is set to "repeatable reads". But that should not be needed for extraction of data.


Minimizing/avoiding data repetition and choosing between compound keys and unique indexes

Does making foreign keys lead to data duplication / repetition or is
  it just a reference / pointer to data stored in the parent table ?

Foreign key restricts the possible values in one table's column accordingly to the presence of the same values in another table's column. Values are stored in the both tables so they are duplicated. To avoid (in fact - to reduce) duplications normalization should be used. That's means you have the reference table where all distinct values are stored and all other tables contains only IDs of that values.

  Should compound keys be avoided wherever possible ?

No. Even more, compound keys are the only way to speed up the query with complex conditions that involve multiple columns. Compound keys can be avoided by the price of performance.


oracle decreasing vs increasing column varchar2 performance

Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking?

Correct.

When you decrease the length, Oracle needs to check the values in all rows in the table if any violates the new (reduced) length - it effectively reads all rows in the table.

This isn't necessary when you increase the length - no existing value can exceed that.


Deleting duplicates with group by and count

According to your query, you have fieldno,fieldserial,id,fielddate,fieldsid as a rule for uniqueness in the GROUP BY clause.
You can try this :
CREATE TABLE tbl_fields_unique LIKE tbl_fields;
ALTER TABLE tbl_fields_unique
ADD UNIQUE KEY unq (fieldno,fieldserial,id,fielddate,fieldsid);
INSERT IGNORE INTO tbl_fields_unique
SELECT * FROM tbl_fields;
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

This will filter rows with duplicate fieldno,fieldserial,id,fielddate,fieldsid fields. Look over the new table. Once you are satisfied with the contents of the tbl_fields_unique table, do this:
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

Give it a Try !!!


Should I use begin tran to do an alter table column that increases the column length?

The statement is atomic and is its own transaction, so no, there is no value in surrounding it with BEGIN TRANSACTION; / COMMIT TRANSACTION;. It will either succeed in whole or fail in whole.


How to delete a Database -Error throws the action cant be completed

You can't delete it because it's open in SQL Server.

If you want to delete it, you'll have to stop SQL Server and delete the files.

You could also go on the server and run  DROP DATABASE WSS_UsageApplication;, assuming you have permission, and the database isn't in use. You may need to run ALTER DATABASE WSS_UsageApplication SET SINGLE_USER WITH ROLLBACK IMMEDIATE; first.

But really, deleting a 62 GB database because your 'application is slow' seems like a terrible idea. Since you're here asking why you can't delete a database that's in use by SQL Server, you probably haven't taken steps to tune anything. SharePoint is supported by Microsoft. Have you opened a ticket with them?

62 GB is also absolutely nothing. I'm typing to you from a laptop with two 1 TB SSDs in it. Is this really a concern?

You should at very least take a FULL backup of the database before proceeding.


How can I determine which tables are causing the database to grow over time?

SQL Server doesn't track this information. You'd need some kind of monitoring (whether it be a tool or home-grown) that keeps snapshots of table space over time, going forward. This can be simple or complex:

Viewing the report @Zane highlights in his answer, and saving the file every so often;
Taking your own periodic snapshots of views like sys.dm_db_partition_stats;
Looking at restored copies of older backups, as suggested by @Ali - however who knows how far your backups go, and if disk space is an issue, you might not have the luxury of restoring a bunch of them for side-by-side comparisons anyway.
Using an external tool like PowerShell to automate it, like the article @Kin points out;
Using a full-blown monitoring tool which tracks space, fragmentation, etc. over time (Disclaimer: I work for SQL Sentry).

Short of having already implemented one or more of these methods, or someone inventing a time machine, there's not really any good way to figure out how big a table was 6 months ago, or two weeks ago, or 20 minutes ago.


Only values, not table names nor column names, can be filled in the easy way.

Instead, construct the SQL statement with CONCAT, then PREPARE and EXECUTE the statement.


OLE DB error 0x80004005 using Copy Database Wizard

Community wiki answer:

Use backup and restore instead.

You can write dynamic SQL to do what you said, or just use Copy-DbaDatabase in the PowerShell dbatools to fully automate the task.


Getting the exact edited data from a column in SQL Server

There's a solution for SQL Server. You can create triggers for tracking these changes, tables where the old and new values will be stored and when a trigger is fired, insert the old and new values into the storage tables

You can also use a tool that will track the changes, I use ApexSQL Audit  - doesn't actually store both versions of the complete table, but stores old and new values for the modified column, so you can recreate the old versions. As it stores all changes in database tables, so I've created some custom reports

And to answer your questions directly

  Are there any necessary changes in the tables?

Yes, triggers are added

  What is the query for getting exact edited data of the artContents column?

It doesn't show just the modified characters, but the old and the new column values


Querying sys.master_files over a linked server

This approach works fine for me:

SELECT * FROM [linked_server_name].master.sys.master_files;

What you need to be sure of is that the linked server is running in the context of a user who has enough privileges to see the contents of the DMV. You can check who you're executing as using:

EXEC [linked server name].master.sys.sp_executesql N'SELECT SUSER_SNAME(),
   ORIGINAL_LOGIN();';


How to read an excel file from a network machine using SQL?

The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.

This will create a package for you, which you can schedule via SQL agent job as explained in Scheduling SSIS Package Execution in SQL Server Agent, as per the desired refresh frequency.

Also, refer to this 10 steps package creation for importing data from excel to SQL table as described by Andy Moving Data From Excel to SQL Server - 10 Steps to Follow

This article uses SSIS 2012, the latest version at the time of writing, but you won’t notice many differences if you’re using 2008 or 2005.


Archive old data in a large table in DB2

You may try ADMIN_MOVE_TABLE routine to convert your table to a range partitioning one online. Use COPY_USE_LOAD option to speed up the process and avoid excessive logging.


How to query data of changes in a column in MySQL

Maybe I can update mysql version. If I will update how can I do it?

In MySQL 8+ the query will be

with
cte0 as ( select *,
                 lag(value) over (order by run_date) lg,
                 lead(value) over (order by run_date) ld
          from test),
cte1 as ( select *,
                 row_number() over (order by run_date) rn
          from cte0
          where value = 1
            and (lg = 0 or lg is null)),
cte2 as ( select *,
                 row_number() over (order by run_date) rn
          from cte0
          where value = 1
            and (ld = 0 or ld is null))
select cte1.id `min(run_date)`,
       cte2.id `max(run_date)`,
       cte2.seed - cte1.seed difference
from cte1,
     cte2
where cte1.rn = cte2.rn;

min(run_date) | max(run_date) | difference
------------: | ------------: | ---------:
            3 |             5 |          7
            9 |            13 |         84

db<>fiddle here


You can see how many rows were sampled (among other things) by using the sys.dm_db_stats_properties DMF:

SELECT object_name(s.object_id), s.name,
       sp.last_updated, sp.rows, sp.rows_sampled,
       sp.steps, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE s.object_id = object_id('TableName')
AND s.name = 'IndexName';

You can add some math to do the comparison for rows_sampled vs rows. Given that there will be data movement in the table after you update stats, I wouldn't expect this to be 100%, but you can assume that if a full scan was done, you'll see at least 80% of the current table size:

SELECT object_name(s.object_id), s.name,
       sp.last_updated, sp.rows, sp.rows_sampled,
       --
       sample_percent = (100*sp.rows_sampled/sp.rows),
       --
       sp.steps, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE s.object_id = object_id('TableName')
AND s.name = 'IndexName';

Now, you just have to use some tool to run it against your thousands of databases. I suspect that if you're managing thousands of databases, you already have an established way to do this. As a PSA, I'll mention that sp_MSforeachdb sometimes misses databases, so consider avoiding that stored procedure.


Horizontal and Vertical Fragmentation on Distributed Database

The typical method employed where you need to replicate the data to both servers is to use a NEWSEQUENTIALID() as the default value for the primary key columns.

From the Microsoft Documentation for NEWSEQUENTIALID():

  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

SQL Server uses the UuidCreateSequential function to generate globally unique identifiers.  This function guarantees that no duplicates will be generated when used in a machine with a manufacturer-assigned ethernet MAC address.  If you are using virtual machines with locally generated MAC addresses where those MAC addresses are not guaranteed to be unique there is a slight possibility that the values will not be unique.

An example CREATE TABLE statement for your location table:

CREATE TABLE dbo.Locations
(
    LocationID uniqueidentifier NOT NULL
        CONSTRAINT Locations_PK
        PRIMARY KEY CLUSTERED
        CONSTRAINT Locations_PK_DF
        DEFAULT NEWSEQUENTIALID()
        ROWGUIDCOL
    , LocationName varchar(48) NOT NULL
) ON [PRIMARY];

Inserting location values looks like:

INSERT INTO dbo.Locations (LocationName)
VALUES ('Cydonia')
    , ('Utopia Planitia');

The inserted row now looks something like:

SELECT l.LocationID
    , l.LocationName
FROM dbo.Locations l;

╔══════════════════════════════════════╦═════════════════╗
║              LocationID              ║  LocationName   ║
╠══════════════════════════════════════╬═════════════════╣
║ A6044A1D-E286-E911-9C54-005056807260 ║ Cydonia         ║
║ A7044A1D-E286-E911-9C54-005056807260 ║ Utopia Planitia ║
╚══════════════════════════════════════╩═════════════════╝

I've created the same table structure on a second server; here we're inserting different rows on that server:

INSERT INTO dbo.Locations (LocationName)
VALUES ('Copernicus Crater')
    , ('Mare Imbrium');

SELECT l.LocationID
    , l.LocationName
FROM dbo.Locations l;

╔══════════════════════════════════════╦═══════════════════╗
║              LocationID              ║   LocationName    ║
╠══════════════════════════════════════╬═══════════════════╣
║ A27E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Copernicus Crater ║
║ A37E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Mare Imbrium      ║
╚══════════════════════════════════════╩═══════════════════╝

As you can see in the two sets of output shown above, each row has a unique LocationID value.  The values aren't generated in random order, they are generated sequentially, which is important for insert performance since it will limit fragmentation via page splits.  Be aware, however, those sequential starting points will change whenever the server hosting SQL Server is restarted.

Merge replication, which is a feature included in all versions of SQL Server from Standard to Enterprise (not SQL Server Express), automatically replicates rows between both servers.  The ROWGUIDCOL property included in the CREATE TABLE statement marks the LocationID column for use by Merge Replication.  Directions for setting up Merge Replication are beyond the scope of this answer.

Once Merge Replication completes, both servers will have this in their dbo.Locations table:

╔══════════════════════════════════════╦═══════════════════╗
║              LocationID              ║  LocationName     ║
╠══════════════════════════════════════╬═══════════════════╣
║ A27E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Copernicus Crater ║
║ A37E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Mare Imbrium      ║
║ A6044A1D-E286-E911-9C54-005056807260 ║ Cydonia           ║
║ A7044A1D-E286-E911-9C54-005056807260 ║ Utopia Planitia   ║
╚══════════════════════════════════════╩═══════════════════╝


Something like this:

select *
from (
  select ds.*,
         lag(ds.odometer_end) over (partition by ds.machine order by ds."date") as prev_end
  from daily_summary ds
) t
where odometer_start <> prev_end + 1
order by "date"


What are pros and cons of Ola’s scripts vs using maintenance plan?

I have written here

  Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.

To add more,

Ola's maintenance solution is widely accepted in the community and large organizations.
Its open sourced and issues can be raised at github/issues with a likelyhood of getting it fixed very fast.
Its flexible and scalable (even if you want to deploy it to 100s of servers just use Install-DbaMaintenanceSolution from dbatools.)
Microsoft took almost a decade to fix Maintenance plan GUI which was buggy itself :-)
has extensive documentation and FAQs and is constantly updated to accomodate newer sql server versions.
in preview version, you can even run backups in parallel.
for index maintenance solution, you can even time the process e.g. if it runs more than X amount of time, abort it.


PostgreSQL Trigger to override insert on table with inheritance

You could possibly use lastval() or currval() to get the last value from the sequence. or use nextval() to get the id before the insert and then insert that value explicitly (instead of allowing the default value).

The third option is to ignore that column and instead use a natural key.  That you are able to get the ID by doing a select suggests that there is a natural key for this table.

Lastval and currval are private to your connection, so must be issued on the same connection that issued the insert.


How to remove all emoji from SQL Server table

One fairly simple method would be to use Regular Expressions via SQLCLR. For example:

DECLARE @Test NVARCHAR(500),
        @Expression NVARCHAR(500);

SET @Test = N'this' + NCHAR(0x0010) +
            N'$is % a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א---B';

SET @Expression = N'[^\p{IsHebrew}\x20-\x7F]+';

SELECT @Test AS [Original],
       DATALENGTH(@Test) AS [OriginalBytes],
       SQL#.RegEx_Replace4k(@Test, @Expression, N'', -1, 1, NULL) AS [Cleaned],
   DATALENGTH(SQL#.RegEx_Replace4k(@Test, @Expression, N'', -1, 1, NULL)) AS [CleanedBytes];

returns:

this$is % a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א---B        152

this$is % a<>TEST,;to}stripout__ special---chars---א---B                            112

The pattern / expression I used — [^\p{IsHebrew}\x20-\x7F]+ — will match 1 or more characters that are "not in the Hebrew Unicode block or in the range of 0x0000 - 0x007F", and replace them with an empty string.

I did also try a pattern of:

[^\p{IsHebrew}\p{IsBasicLatin}-[\p{Cc}]]

which should have been the same since "IsBasicLatin" is the range of 0x0000 - 0x007F, and then substract the Control Characters which have a range of 0x0000 - 0x001F. But for some reason the character class subtraction was not working.

To "clean" the table, you just put it in an UPDATE statement like so:

DECLARE @Expression NVARCHAR(500);

SET @Expression = N'[^\p{IsHebrew}\x20-\x7F]+';

UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, @Expression, N'', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, @Expression, 1, NULL) = 1;

One difference between this pattern and the one used in my similar answer to Replace special characters in a column with space is the + at the end of the pattern here. That is what indicates "one or more" of whatever matches the thing to the left of it. In the case here, we are removing characters rather than replacing them individually with another character (such as a space, as was used in the other answer), so we can be a little more efficient and have the Regular Expression group several characters in a row to replace with the empty string.

To be fair, this could also be accomplished in pure T-SQL. You could create a UDF that uses PATINDEX to find characters to remove, and use STUFF(@String, position_from_PatIndex, 1, '') to remove them individually. Each time you would get the position from PATINDEX and exit / break if it returns 0. I don't see that being more efficient than the regular expression, especially since PATINDEX, unlike CHARINDEX, can't start at a specified location in the string, so you can't just pick up where you left off; you need to rescan the entire string each time you remove a single character.

I just tried the following and it returns the same result string (I just haven't put it into a UDF). Just add it to the end of the test above and run all of it together:

DECLARE @Position INT,
        @Pattern NVARCHAR(100);

SET @Pattern = N'%[^' + NCHAR(0x0020) + N'-' + NCHAR(0x007F)
                + NCHAR(0x0509) + N'-' + NCHAR(0x05FF) + N']%';
SET @Position = PATINDEX(@Pattern COLLATE Latin1_General_100_BIN2, @Test);

WHILE (@Position > 0)
BEGIN
  SET @Test = STUFF(@Test, @Position, 1, N'');
  SET @Position = PATINDEX(@Pattern COLLATE Latin1_General_100_BIN2, @Test);
END;

SELECT @Test AS [CleanedViaT-SQL],
       DATALENGTH(@Test) AS [CleanedViaT-SQLBytes];

returns:

CleanedViaT-SQL                                                   CleanedViaT-SQLBytes

this$is % a<>TEST,;to}stripout__ special---chars---א---B          112


PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table

I don't expect a big difference between both queries. Details depend on undisclosed information and data distribution.
What actually makes a big difference is to have an index supporting it optimally. One might think that you already have that with your PK:
primary key(action_date, item_num)

But that's not so. The sequence of index expressions matters in this case. Instead make it:
primary key(item_num, action_date)

Rule of thumb: equality first, range later. See:

Multicolumn index and performance

You may want to create an index on (action_date) or even (action_date, item_num) additionally - or vice versa - for those other use cases you mentioned in a comment. But you must have the index on (item_num, action_date) one way or another for optimal performance. See:

Is a composite index also good for queries on the first field?

As long as those other queries have equality predicates on both item_num and action_date, either index is equally good, and you don't need another. See (again):

Multicolumn index and performance

Try to keep the number and size of your indexes to a minimum, as they themselves are a burden on write performance. Rule of thumb: as many as necessary, as few as possible.
Whether you DELETE and INSERT, or UPDATE (UPSERT), you produce a lot of dead tuples either way. So make sure to have appropriate autovacuum settings for the table. Default settings may not be aggressive enough if the table is big and the churn is high. The table needs to be vacuumed to be able to reuse space from dead tuples or do HOT updates. Else, the table, and even more so the index, start to bloat and performance goes down. See:

Aggressive Autovacuum on PostgreSQL
Redundant data in update statements

In extreme cases vertical partitioning may pay. See:

Many columns vs few tables - performance wise


use SUM on foreign keys where it matches rows id?

Join to the aggregate, don't aggregate the join:

SELECT b.id AS building_id,
      (b.document -> 'meta'::text) ->> 'name'::text AS name,
      r.sqft
FROM buildings b
  JOIN (
   SELECT building_id,
          sum(sqft) as sqft
   FROM v_rooms
   GROUP BY building_id
  ) r ON r.building_id = b.id;

Unrelated, but: although Postgres allows it, it's usually a bad idea to include an ORDER BY in a view definition.


Dataguard: preventing logical standby to loose track after flashback database on primary

I would change the whole process of deploying.

Stop SQL Apply.
Create restore point on standby.
Deploy to standby
When testing is complete, flashback standby.
Start SQL Apply.
Safe to deploy on primary.


RMAN Backups - Can more than one media management software solution be used?

Creating the symlink $ORACLE_HOME/lib/libobk.so pointing to the media management library is not a strict requirement. As part of RMAN channel allocation, the path of vendor speficic library can be specified in the SBT_LIBRARY parameter.
I never tried this, but just by allocating the SBT channels with different parameters should make this work.
UPDATE:
For your other question about restoring backup pieces from different media:
RMAN has a feature called Autolocation. The RMAN SET AUTOLOCATE ON command is deprecated since 10.2 because this feature is enabled by default.  RMAN only needs to track that for example the backup of datafile 1 is included in backuppiece vlv6su4u_1_1 with channel type SBT_TAPE. Using this information and the autolocation feature, RMAN is able to recognize which channel it should use from multiple channels allocated with different options, simply by querying all channels for this backuppiece. This feature was invented for handling backups taken on different RAC nodes, but I was able to use it in another scenario as well: for example after a TSM backup server was migrated (e.g full backup from old TSM server + file space, incremental and archivelog backups from new TSM server + file space, channels allocated with different tdpo.opt files pointing to old and new TSM servers).
The same may work with 2 different MMLs as well.


MySQL equivalent of a spatial ST_Union aggregate function?

I found another alternative to perform the union aggregation by using ST_Union. I had some issues cause this Mysql function get only 2 parameters as inputs.

As a solution, I create my own function as follow :

CREATE FUNCTION `fct_create_geom_collection`(ac CHAR(8))
 RETURNS GEOMETRY
BEGIN
                    DECLARE position INT;
                    DECLARE sortie BOOLEAN DEFAULT FALSE;
                    -- get all area_IDs
                    DECLARE curgeom CURSOR FOR
                    SELECT DISTINCT area_ID
                    FROM area_table
                    WHERE area_code= ac ORDER BY area_ID;
                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET sortie = TRUE;

    OPEN curgeom;
                  -- get the first area_id
                   FETCH curgeom INTO position ;
                  -- put the shape into @var_g_0 for this area_id
                   SELECT ST_GeomFromWKB(Shape,27582) INTO @var_g_0
                   FROM area_table
                   WHERE area_ID = position  ;
    LLD: LOOP
    IF sortie THEN
    LEAVE LLD;
    END IF;
                    -- get the second area_id
                    FETCH curgeom INTO position  ;
                    -- put the second  shape into @var_g for this area_id
                    SELECT ST_GeomFromWKB(Shape,27582) INTO @var_g
                    FROM area_table
                    WHERE area_ID    = position   ;

                    -- performing a union operation between @var_g_0 and @var_g
                    SET @geom = ST_UNION(@var_g_0,@var_g); -- enclosed st_union
                    -- put the result into @var_g_0
                    SET @var_g_0 = @geom;

                   END LOOP;
                   CLOSE curgeom;
                   RETURN ST_GeomFromText(ST_ASTEXT(@geom));
                 -- RETURN ST_GeomFromWKB(@geom);

END$$

This function enable an aggregate union over shapes for each "area code" which is what UnionAggregate do and what I need.


why is the Log File is 5 times the database overnight when deleting?

If this is a one off and this isn't production, you could put the database into simple recovery mode, do all the deleting you need to do.

Shrink the log file at this point if you don't want it to be 500GB.

Then put it back into its previous recovery mode.


How to view the table structure in workbench in mysql database without quotes

You can get the DDL via the context menu for an object. So, right click on e.g. your table and let it send the DDL to your current editor or to the clipboard:


How to save response file in DBCA oracle 12c

The above documentations refers to OUI (Oracle Universal Installer), not DBCA.
Saving response files in DBCA is available starting with version 12.2.

See the below example (Save Response File in bottom right corner):


changing the connection string in SQL Server

After SSIS package deployed to new environment still it may use old connection string.

  how to change the connection string i.e initial catalog or server name
  parameter from Agent job > configuration > connection manger .Is there
  a possibility to do from here ?

Yes, It's possible when you are sysadmin and you can change manually by following:

  Right click on AgentJob -> Properties -> Job step -> Configuration ->
  Connection Managers

  Below Connection Managers -> select Name of database then on
  properties ->

  Connectionstring -> Data Source=yourServerName;InitialCatalog=Databasename;Provider=SQLNCLI11.1;IntegratedSecurity
  = SSPI;Auto Translate=False; (you can edit Data source and intial catalog manually)

  and

  Servername = 'yourservername'


Building slowly changing dimension on a Fact/Dimension Star Schema

Usually, dimension tables contain a single valid time (start and end date) for all fields and SCD2 would apply to the complete record. It is good practice to use an non-null end value ahead in time to mark currently valid records as this simplifies queries. An end date in the past would signify deletion or any other semantic you define (like person left country or is not employed anymore). Also add surrogate keys to your dimension tables to uniquely identify records.

Fact tables usually contain "measures" like sales or cost or signify events like a placed call or durations of these calls. One would usually use aggregates on these columns in reports.

A star-schema is a way to model a sparsely populated "cube", where each axis of the coordinate system is given by one of the dimension tables. "Slice and dice" operations and "drill up / drill down" operations in reports translate nicely into SQL using this model.

In your cars and people example, I would use two dimension tables, one for cars and one for people, each historized (according to SCD2), and a factless fact table comprising foreign keys to the dimension tables, referencing the respective identifier (entity identifier), and valid time columns (SCD2). You would not add a record according to SCD2 rules in the fact table, if one of the dimension tables changes, in this design.

This way you can model changes in each entity, like name changes in people, color changes in cars and the relationship between cars and people, for example ownership. Each table would use non-overlapping valid times (start and end values) for each business key, recording the history of these entities independently. The fact table would in this model basically be a m:n linkage table, for which a separate history of valid times is kept.

You would identify the current and past lists by using x between start and end on each table for as-of now (or past) queries (answers your (1) and (2) - ignoring if the intervall is right open or left open).

Summary statistics like how many cars do we have in some city with full history (assuming city is part of the people dimension table), can now be answered using temporal joins and "sequenced" queries, which are sometimes also called "coalesce" queries, see Snodgrass, chapter 6.3ff


Performance considerations for using MySQL table as a queue?

My first encounter with "using MySQL as a queue" ended in disaster.  The team was very wedded to tossing things into a table, then pulling it out to work on.  They were limited to how many things the could achieve per hour.

After studying not only the queuing mechanism but the enqueue and dequeue API and the worker threads, I decided on:

  "Don't queue it, just do it".

I estimated (for their case) that they could increase the throughput 10-fold by removing the queue.

Here are some random lessons:

If the task to perform is fast enough, you may be spending more time on enqueuing/dequeuing than the task.
If the tasks arrive in a steady stream, the "buffering" that queuing gives you is unnecessary.  On the other hand, if the tasks are "bursty", queuing may be beneficial.
Replication (HA was a requirement in the case) significantly complicates the queuing code.
Using AUTO_INCREMENT for queuing with replication adds a complication because the ids don't always arrive in the Slave in order!  This happens randomly (but not very frequently) with InnoDB.
Deletion of queue items leads to fragmentation.  This was especially bad with MyISAM.
Note how switching Engines solves one problem but creates another?
UUIDs are terrible for performance if the table gets bigger than can be cached.
Transactional integrity gets messy -- the item in the queue is mostly independent of the task it represents.  Issues to resolve: requeuing a task that fails; crashing in the middle of a task; etc.
Sure, the queue will have very few rows at any normal moment.  But there will come a time when something hiccups and there is a million tasks queued up.  The system will croak for any of several reasons that you failed to plan for.  You have a crisis on your hands and the looming job of analyzing and planning for such eventualities.
Based on my previous comment, do you really want to leave off any index?  No.  The dequeuing mechanism will get slower and slower, further exasperating the crisis.
If a queued task is assigned to a worker, but that worker crashes, then the item needs to stick around somewhere (in the queue or some other place) and some separate task needs to eventually discover an unfinished task after some timeout.  More messy code to write.

An alternative (that may or may not apply to your situation):  Leave information around and have a continuously running job (not a cron) that looks for the info and acts on it.  This can be handy if the items arrive rapidly and they can be processed in batches.


Automatically purging binlogs after successful replication

Server is responsible to purge their own file. Guess what happens if your master has two slaves and one of it catches up faster deleting logs required for second?!!

So yes expire_logs_days is the way to go. Set that value wisely and let master do its business.

That said why do you want to purge logs? You have purge binary logs to command but I'd still let purging upto master unless disk is critical.

If you want it, then you can script out the manual purging logic:
Check all slaves have caught up to latest
Purge a logs before the exec_master_log_file.

In my opinion this is absolutely unnecessary unless there some odd requirement that u just can't think about...


Repartition Primary Key Index online in Oracle

After much thought, I think I've managed it. Only drawback is that you'll need double the space of the underlying table to do so...

Don't think there's any other way to do it as you can't redefine PKs without dropping and recreating (correct me if I'm wrong).

PHIL@PHILL11G2 > alter table bigtable add constraint bigtable_pk primary key (object_id);

Table altered.

PHIL@PHILL11G2 > commit;

Commit complete.

PHIL@PHILL11G2 > create table bigtable_interim as ( select * from bigtable where 1=0 );

Table created.

PHIL@PHILL11G2 > EXEC dbms_redefinition.can_redef_table('PHIL', 'BIGTABLE');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'BIGTABLE',int_table => 'BIGTABLE_INTERIM');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > create unique index bigtable_int_hash on bigtable_interim (object_id) global partition by hash (object_id) (partition p1 tablespace users, partition p2 tablespace users) online;

Index created.

PHIL@PHILL11G2 > exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'BIGTABLE',int_table => 'BIGTABLE_INTERIM');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from bigtable_interim;

  COUNT(*)
----------
   2300640

PHIL@PHILL11G2 > select count(*) from bigtable;

  COUNT(*)
----------
   2300640

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > alter table bigtable add constraint bigtable_hashed_pk primary key (object_id) ;

Table altered.

PHIL@PHILL11G2 > -- that reused the hashed index i created

Feel free to correct any mistakes I've made :)

Phil


No, there's no magic or hand-waving here. It'd be great if synonyms, say, applied to types, but that is not the case. If you want to make these columns first-class citizens, you'll need to change the table. You can automate this to some degree, though I won't post code to help with this unless you specify what you mean exactly by "manually" and why you think this will be a significant burden you want to avoid.

To automate this, you could say:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  + ' ALTER COLUMN ' + QUOTENAME(name) + ' NVARCHAR(MAX)'
  + CASE is_nullable WHEN 0 THEN ' NOT NULL;' ELSE ';' END
FROM sys.columns
WHERE system_type_id = 99;

PRINT @sql;

-- EXEC sp_executesql @sql;

That said, you probably don't want to use these columns with distinct / group by anyway.


MySQL Count rows from another table for each record in table

You are just missing the GROUP BY

YOUR QUERY WITH GROUP BY

SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name`;

SAMPLE DATA

DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
USE alishaikh
CREATE TABLE student
(
  StudentID CHAR(8) NOT NULL,
  Name VARCHAR(40),
  PRIMARY KEY (StudentID)
);
INSERT INTO student (StudentID,Name) VALUES
('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
('k1052299','Troy Fulton');
CREATE TABLE attendance
(
  AttendanceID INT NOT NULL AUTO_INCREMENT,
  StudentID CHAR(8) NOT NULL,
  PRIMARY KEY (AttendanceID),
  KEY (StudentID)
);
INSERT INTO attendance (StudentID) VALUES
('k1052280'),('k1052280'),('k1052287'),('k1052287'),
('k1052288'),('k1052295'),('k1052295'),('k1052295');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
Query OK, 2 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
mysql> USE alishaikh
Database changed
mysql> CREATE TABLE student
    -> (
    ->   StudentID CHAR(8) NOT NULL,
    ->   Name VARCHAR(40),
    ->   PRIMARY KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO student (StudentID,Name) VALUES
    -> ('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
    -> ('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
    -> ('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
    -> ('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
    -> ('k1052299','Troy Fulton');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE attendance
    -> (
    ->   AttendanceID INT NOT NULL AUTO_INCREMENT,
    ->   StudentID CHAR(8) NOT NULL,
    ->   PRIMARY KEY (AttendanceID),
    ->   KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO attendance (StudentID) VALUES
    -> ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
    -> ('k1052288'),('k1052295'),('k1052295'),('k1052295');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

YOUR QUERY WITH GROUP BY EXECUTED

mysql> SELECT
    ->   student.StudentID,
    ->   student.`Name`,
    ->   COUNT(attendance.AttendanceID) AS Total
    -> FROM
    ->   student
    -> LEFT JOIN attendance ON student.StudentID = attendance.StudentID
    -> GROUP BY student.StudentID,student.`Name`;
+-----------+-------------------+-------+
| StudentID | Name              | Total |
+-----------+-------------------+-------+
| k1052280  | Ali Shaikh        |     2 |
| k1052287  | McKenzie Roth     |     2 |
| k1052288  | Dacey Sullivan    |     1 |
| k1052294  | Zelda Cantu       |     0 |
| k1052295  | Kimberly Melton   |     3 |
| k1052296  | Tatianna Cantrell |     0 |
| k1052297  | Morgan Thornton   |     0 |
| k1052298  | Allistair Barlow  |     0 |
| k1052299  | Troy Fulton       |     0 |
+-----------+-------------------+-------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Where does SQL Server store the SA password and SQL Server password policy?

The sa user is a SQL Server login and its password is encrypted and stored in the DMV sys.sql_logins (Database Management View) in the master database.
Reference: sys.sql_logins (Transact-SQL)
You might notice that these views can only be found in the following branch:
Server | Databases | master | Views | System Views | ....

The DMVs reference some system base tables, which can be accessed (but shouldn't):
Reference: System Base Tables
The policy you are looking for is built in to the code of SQL Server and is set per default for each new account. When creating a SQL Server Login you can decide to turn off the defaults:

To enforce password policy options for complexity and enforcement, select Enforce password policy. For more information, see Password Policy. This is a default option when SQL Server authentication is selected.

... via CHECK_POLICY=OFF. See the full syntax:
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

<option_list1> ::=
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=
    SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL = credential_name

<sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name

<windows_options> ::=
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

Reference: CREATE LOGIN (Transact-SQL)
If the SQL Server is a member of a Windows Domain, then it will retrieve the password policy from Active Directory. Otherwise the defaults are:

Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:

The password does not contain the account name of the user.
The password is at least eight characters long.
The password contains characters from three of the following four categories:
- Latin uppercase letters (A through Z)
- Latin lowercase letters (a through z)
- Base 10 digits (0 through 9)
- Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).
Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.

Reference: Password Policy


SSRS 2017 reports having issues with IE 11?

try add

<meta http-equiv="X-UA-Compatible" content="IE=EDGE">

tag in the ReportServer\Pages\reportviewer.aspx file.

https://stackoverflow.com/questions/6771258/what-does-meta-http-equiv-x-ua-compatible-content-ie-edge-do


It'd help more if you could be more specific about what you're modeling, as it's hard to picture at the moment, for me at least.

For tree structures, in SQL, any database that supports Recursive Common Table Expressions is a good choice (any major one other than MySQL).

For historical data, the History Table pattern has proven to be effective. For table foo, make a table foo_history and copy-on-write from foo to foo_history. An example.

IBM's DB2 supports SQL:2011 Temporal, which lets you have foreign keys based on time, and is the only db that supports it that I know of.


You need to UNPIVOT the current result.

I've set up the next example:

create table if not exists questions (question_id int, question varchar(20));
create table if not exists answers (student_id int, answer_id int, ans_question_1 varchar(20), ans_question_2 varchar(29));

insert into questions values (1,'Gender'),(2,'Area code');
insert into answers values (11,1,'Male','23'),(12,2,'Female','21');

This solution uses a CROSS JOIN with the questions table and it assumes that exist one question_id for each ans_question_xx.

Important

  It must exists a question_id = 1 and one ans_question_1 column, that belong s to this question.

select     student_id, aq.question,
           case aq.question_id
                when 1 then ans_question_1
                when 2 then ans_question_2
           end Answer
from       answers
cross join (select question_id, question from questions) aq
order by   student_id, aq.question_id;

| student_id | question  | Answer |
|------------|-----------|--------|
| 11         | Gender    | Male   |
| 11         | Area code | 23     |
| 12         | Gender    | Female |
| 12         | Area code | 21     |

Rextester here

Using a dynamic query

First build the whole 'when xx then ans_question_xx' using group_concat().

select @caseWhen := group_concat(concat('when ',
                                        cast(question_id as char(20)),
                                        ' then ans_question_',
                                        cast(question_id as char(20))) separator ' ')
from questions;

when 1 then ans_question_1 when 2 then ans_question_2

Then build the sql query sentence:

set @sql = concat('select student_id, aq.question, case aq.question_id ',
                  @caseWhen,
                  ' end Answer from   answers
                    cross join (select question_id, question from questions) aq
                    order by student_id, aq.question_id;');

Finally execute it:

PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

The result is the same:

| student_id | question  | Answer |
|------------|-----------|--------|
| 11         | Gender    | Male   |
| 11         | Area code | 23     |
| 12         | Gender    | Female |
| 12         | Area code | 21     |

Rextester here

Using an stored procedure

You can use previous dynamic query within an stored procedure:

create procedure getStudentAnswers(StudentId int)
begin

    set @caseWhen := (select group_concat(concat('when ',
                                        cast(question_id as char(20)),
                                        ' then ans_question_',
                                        cast(question_id as char(20))) separator ' ')
                     from questions);

    set @sql = concat('select student_id, aq.question, case aq.question_id ',
                      @caseWhen,
                      ' end Answer from   answers
                        cross join (select question_id, question from questions) aq
                        where student_id = ', cast(StudentId as char(20)),
                       ' order by student_id, aq.question_id;');

    PREPARE dynamic_statement FROM @sql;
    EXECUTE dynamic_statement;
    set @sql := null;
    set @caseWhen := null;
    DEALLOCATE PREPARE dynamic_statement;

end \\

call getStudentAnswers(11);

call getStudentAnswers(12);

Rextester here


Slow query when having 'contains' and '=' together in where clause

Your execution plan

When looking at the query plan, we can see that one index is touched to serve two filter operations.

Very simply put, due to the TOP operator, a row goal was set.
Much more information & prerequisites on row goals can be found here

From that same source:

  A row goal strategy generally means favouring non-blocking
  navigational operations (for example, nested loops joins, index seeks,
  and lookups) over blocking, set-based operations like sorting and
  hashing. This can be useful whenever the client can benefit from a
  quick start-up and steady stream of rows (with perhaps a longer
  overall execution time – see Rob Farley's post above). There are also
  the more obvious and traditional uses e.g. in presenting results a
  page at a time.

The entire table gets probed into the filters with the use of a left semi join that has a row goal set, hoping to return the 5 rows as fast and efficient as possible.

This does not happen, resulting in many iterations over the .Fulltextmatch TVF.

Recreating

Based on your plan, I was able to somewhat recreate your problem:

CREATE TABLE dbo.Person(id int not null,lastname varchar(max));

CREATE UNIQUE INDEX ui_id ON  dbo.Person(id)
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Person(lastname)
   KEY INDEX ui_id
   WITH STOPLIST = SYSTEM;
GO

INSERT INTO dbo.Person(id,lastname)
SELECT top(12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
REPLICATE(CAST('A' as nvarchar(max)),80000)+ CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as varchar(10))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
CREATE CLUSTERED INDEX cx_Id on dbo.Person(id);

Running the query

SELECT TOP (5) *
FROM dbo.Person
WHERE "id" = 1 OR contains("lastName", '"B*"');

Results into a query plan comparable to yours:

In the above example, B does not exist in the fulltext index. As a result it depends on the parameter & data how efficient the query plan can be.

A better explanation of this can be found in Row Goals, Part 2: Semi Joins by Paul White

  ...In other words, on each iteration of an apply, we can stop looking at
  input B as soon as the first match is found, using the pushed-down
  join predicate. This is exactly the sort of thing a row goal is good
  for: generating part of a plan optimized to return the first n
  matching rows quickly (where n = 1 here).

For example, changing the predicate so the results are found way sooner (at the beginning of the scan).

select top (5) *
from dbo.Person
where "id" = 124
or contains("lastName", '"A*"');

the where "id" = 124 gets eliminated due to the fulltext index predicate already returning 5 rows, satisfying the TOP() predicate.

The results show this as well

id lastname
1  'AAA...'
2  'AAA...'
3  'AAA...'
4  'AAA...'
5  'AAA...'

And the TVF executions:

Inserting some new rows

INSERT INTO dbo.Person
SELECT 12001, REPLICATE(CAST('B' as nvarchar(max)),80000);
INSERT INTO dbo.Person
SELECT 12002, REPLICATE(CAST('B' as nvarchar(max)),80000);

Running the query to find these previous inserted rows

SELECT TOP (2) *
from dbo.Person
where "id" = 1
or contains("lastName", '"B*"');

This again results in too many iterations over almost all of the rows to return the last but one value found.

id   lastname
1     'AAA...'
12001 'BBB...'

Resolving

When removing the row goal by using traceflag 4138

SELECT TOP (5) *
FROM dbo.Person
WHERE "id" = 124
OR contains("lastName", '"B*"')
OPTION(QUERYTRACEON 4138 );

The optimizer uses a join pattern closer to implementing a UNION, in our case this is favourable as it pushes the predicates down to their respective clustered index seeks, and does not use the row goaled left semi join operator.

Another way to write this, without using the above mentioned traceflag:

SELECT top (5) *
FROM
(
SELECT *
FROM dbo.Person
WHERE "id" = 1
UNION
SELECT *
FROM dbo.Person
WHERE contains("lastName", '"B*"')
 ) as A;

With the resulting query plan:

where the fulltext function is applied directly

As a sidenote, for op, the query optimizer hotfix traceflag 4199 resolved his problem. He implemented this by adding OPTION(QUERYTRACEON(4199)) to the query. I was not able to reproduce that behaviour on my end. This hotfix does contain a semi join optimization:

  Trace Flag: 4102 Function: SQL 9 - Query performance is slow if the
  execution plan of the query contains semi join operators Typically,
  semi join operators are generated when the query contains the IN
  keyword or the EXISTS keyword. Enable flag 4102 and 4118 to overcome
  this.

Source

Extra

During cost based optimization, the optimizer could also add an index spool to the execution plan, implemented by LogOp_Spool Index on fly Eager  (or the physical counterpart)

It does this with my dataset for TOP(3) but not for TOP(2)

SELECT TOP (3) *
from dbo.Physician
where "id" = 1
or contains("lastName", '"B*"')

  On the first execution, an eager spool reads and stores the entire
  input before returning the subset of rows that is requested by the
  Predicate Later executions read and return the same or a different
  subset of rows from the worktable, without ever having to execute the
  child nodes again.

Source

With the seek predicate applied to this index eager spool:


Does surrogate key assignment for a fact table require that the source data has natural keys?

I'm not sure that you need "natural" keys, but you probably do need to maintain a key mapping of sorts. So you need to understand what relationships map between your source and target systems, identify the keys for those relationships and build your key mappings from there.

I had a previous question on this called "What is the best practice for mapping from natural keys to integer-based keys? (ETL)".

  EDIT: So far I am seeing at least three, if not four mappings.

CustomersToDim_Customers (customer_id, dim_customer_id)
ProductsToDim_Products (product_id, dim_product_id)
OrderDatesToDim_Date (order_date, date_id) or (map_id,order_date,date_id) if you want to use a key to map.

  And lastly, I see the order_id as your key to the fact table. So I
  would go

OrdersToFactOrders (order_id,dim_date_id,dim_customer_id,dim_product_id)

  In my case I renamed the fields for the mart with dim_field_id because
  I didn't want name collision within my tables or confusion as to which
  Id they pointed to. Your ETL would have to know that
  CustomersToDim_Customers.dim_customer_id really maps to
  Dim_Customers.customer_id and that
  CustomersToDim_Customers.customer_id really maps to
  Customers.customer_id.

  I would also be half-inclined to include the order_number in the
  OrdersToFactOrders mapping table, but that is because I like to have
  tracking data for audit purposes. Makes my life easier. But, based on
  what you told me, order_number and order_id are one-to-one so the
  inclusion of order_number would be redundant then and only necessary
  if you have a perfectionist paranoia to make sure your data is correct
  on both sides (I really like to make sure that A on side A and B on
  side B are really correct after the ETL is done.).


Changing data type from DATE to CHAR(10) with a query

A piece of code that does what you want is:

declare @sql nvarchar(max), @crlf char(2) = CHAR(13)+CHAR(10);

select @sql = isnull(@sql, '') + 'alter table ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' alter column ' + c.COLUMN_NAME + ' CHAR(10)' +
    + case when c.IS_NULLABLE = 'YES' then ' NULL;' else ';' end + @crlf
from INFORMATION_SCHEMA.COLUMNS c
where DATA_TYPE = 'date'

print @sql;
-- exec sp_executesql @sql --execute only after inspection!

But I would like to stress that you shouldn't normally do it on a system (except for dev/test purposes), because if it is Date information that you're using, you should be using the proper data type and avoid any other unnecessary cast.


You create database mydatabase and then you grant on database.*
The correct is:

grant all privileges on mydatabase.* to 'host'@'%';


Ola Hallengren Transaction Log Backups of System Databases

Per this Microsoft documentation:

For backwards compatibility with earlier versions of Microsoft SQL
Server, the recovery model of master can be set to FULL or
BULK_LOGGED. However, BACKUP LOG is not supported for master.
Therefore, even if the recovery model of master is changed to full or
bulk-logged, the database continues to operate as if it were using the
simple recovery model.

So that's why you can't get a transaction log backup of Master.
From that same document:

Best practice:  We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.

While that doesn't explicitly say that SQL will prevent a transaction log of Model from happening, it certainly recommends against it.
Note: That's an old version (2008 R2) of the documentation. New versions (I checked 2017) don't repeat the detail about Master, but they also don't contradict it.


Read system_health event_file instead of ring_buffer for deadlocks extended events

This seems to work:

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
   from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where object_name = 'xml_deadlock_report'
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc


How does LIKE '%123456789%' benefit from indexing?

Only a little bit. The query processor can scan the whole nonclustered index looking for matches instead of the entire table (the clustered index). Nonclustered indexes are generally smaller than the table they are built on, so scanning the nonclustered index may be faster.

The downside, is that any columns needed by the query that are not included in the nonclustered index definition must be looked up in the base table, per row.

The optimizer makes a decision between scanning the table (clustered index) and scanning the nonclustered index with lookups, based on cost estimates. The estimated costs depend to a great extent on how many rows the optimizer expects your LIKE or CHARINDEX predicate to select.

  Why do the listed articles state that it will not improve performance?

For a LIKE condition that does not start with a wildcard, SQL Server can perform a partial scan of the index instead of scanning the whole thing. For example, LIKE 'A% can be correctly evaluated by testing only index records >= 'A' and < 'B' (the exact boundary values depend on collation).

This sort of query can use the seeking ability of b-tree indexes: we can go straight to the first record >= 'A' using the b-tree, then scan forward in index key order until we reach a record that fails the < 'B' test. Since we only need to apply the LIKE test to a smaller number of rows, performance is generally better.

By contrast, LIKE '%A cannot be turned into a partial scan because we don't know where to start or end; any record could end in 'A', so we cannot improve on scanning the whole index and testing every row individually.

  I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?

The query optimizer has the same choice between scanning the table (clustered index) and scanning the nonclustered index (with lookups) in both cases.

The choice is made between the two based on cost estimation. It so happens that SQL Server may produce a different estimate for the two methods. For the LIKE form of the query, the estimate may be able to use special string statistics to produce a reasonably accurate estimate. The CHARINDEX > 0 form produces an estimate based on a guess.

The different estimates are enough to make the optimizer choose a Clustered Index Scan for CHARINDEX and a NonClustered Index Scan with Lookups for the LIKE. If you force the CHARINDEX query to use the nonclustered index with a hint, you will get the same plan as for LIKE, and performance will be about the same:

SELECT
    [Customer name],
    [Sl_No],
    [Id]
FROM dbo.customer WITH (INDEX (f))
WHERE
    CHARINDEX('9000413237', [Phone no]) >0;

The number of rows processed at runtime will be the same for both methods, it's just that the LIKE form produces a more accurate estimation in this case, so the query optimizer chooses a better plan.

If you find yourself needing LIKE %thing% searches often, you might want to consider a technique I wrote about in Trigram Wildcard String Search in SQL Server.


Multiple databases or tables with prefix?

It's not simply a question of plugging values into an equation and, hey presto, you have an answer (as @dbdemon also points out - no standard solution).

You need to put considerable thought into this - it could be a choice which affects you for a considerable time.

A few things you should do before you start coding:

Gather detailled requirements (as kindly suggested by @Peter)
Check out the threads and a few of my answers on multi-tenancy here. Also, as suggested by @Colin'tHart, do a search on this site for other posts related to multi-tenancy.
You could also download a few Open Source apps - use your search engine of choice - duckduckgo got me a few links.
Take a look at databaseanswers and their schemas - something there might be of interest/help.
Do a bit of prototyping - see what "fits" with your company/clients/stakeholders requirements and/or your company's skillset in the coding/database areas.


How to do split each quantity and grouping by child customer order in one table

select item, no_po, sum(qty) qty_total,
    sum(case when parent_customer='Customer B' and child_customer='ha' then qty else 0 end) as customer_b_ha,
    sum(case when parent_customer='Customer B' and child_customer='he' then qty else 0 end as customer_b_he,
    sum(case when parent_customer='Customer B' and child_customer='hi' then qty else 0 end as customer_b_hi,
    sum(case when parent_customer='Customer B' and child_customer='ho' then qty else 0 end as customer_b_ho,
    sum(case when parent_customer='Customer B' and child_customer='hu' then qty else 0 end as customer_b_hu,
    sum(case when parent_customer='Customer C' and child_customer='ha' then qty else 0 end as customer_c_ha,
    sum(case when parent_customer='Customer C' and child_customer='he' then qty else 0 end as customer_c_he,
    sum(case when parent_customer='Customer C' and child_customer='hi' then qty else 0 end as customer_c_hi,
    sum(case when parent_customer='Customer C' and child_customer='ho' then qty else 0 end as customer_c_ho,
    sum(case when parent_customer='Customer C' and child_customer='hu' then qty else 0 end as customer_c_hu
from detail_order
where received_date='2018-06-27'
group by item, no_po
order by item asc, no_po


Can't modify FormatString on cube ( SSAS )

What you are seeing is that the new cube browser control in Data Tools doesn't support format strings.

OLAP clients are free to respect those formats or not, so some clients just don't respect the format string.

Testing in Excel should however display the formatted values correctly.


What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?

SQLCLR is the ability to run .NET code within SQL Server.

When people speak of SQLCLR they are usually referring to the ability to write custom .NET code (Stored Procedures, Functions, Triggers, User-Defined Types, and User-Defined Aggregates). In this case, this ability can be turned on and off via the server option of "clr enabled" within sp_configure, and I believe it's called "CLR Integration" in the Surface Area Configuration GUI. Adding .NET functionality requires using CREATE ASSEMBLY to load the compiled .NET code into SQL Server.

However, even with the "clr enabled" option set to 0 / "off" / "disabled", CLR functionality is still used for internal functionality and some built-in functions and will always be there, unless the "use Windows fibers" / "lightweight pooling" option is enabled.

For a very detailed description of what SQLCLR is, is not, and what it can do, please see the article that I wrote on this topic on SQL Server Central: Stairway to SQLCLR Level 1: What is SQLCLR? (free registration is required to view content on that site). A stripped-down version of that article is available in the following StackOverflow answer: When do we need a CLR function in SQL Server?.

If you want to know whether or not the instance is allowing custom .NET code to be added to databases, run the following and check the "run_value" field:

EXEC sp_configure 'clr enabled';

With regards to the meaning of the SQLCLR wait type in Resource Monitor, please see the following blog post:

How It Works: What is behind the SQLCLR wait category in SQL Server 2008 Activity Monitor

and I believe the pertinent line (as it pertains to the SQLCLR category you are looking at) from that post is:

  What I found was that several of the wait_types should be ignored as they are expected waits.


PostgreSQL memory spike upgrade from 8.2 to 9.1

In a case like this where nothing significant has changed I think you are best off bringing this up on the PostgreSQL email lists.  Not only will you get help with your issue in terms of workarounds but if there is a planner bug somewhere the development team will be given notice so they can fix it.

This may be one of those cases where the level of help you can expect there is far better than you can expect here.


sys.sysprocesses still contains information that is either very cumbersome or impossible to get from the DMVs, since they weren't complete (they are a bit better with each new release, but not 100% yet). Two standout examples:

database_id for a non-active request (e.g. there is a row in sys.dm_exec_sessions but not in sys.dm_exec_requests)
open transaction count

I suspect this backward compatibility view will remain around until a few versions after they supply the information directly through the DMVs.


"key violation" when trying to import data into MS Access

Perhaps some of the data was successfully imported, and on your subsequent attempts it tries to reimport the whole file, and collides with the existing data?  More generally, are there any records in the target table with which it could be colliding?

If that's not the case, try cutting the file in half.  Can you import the first half?  If so, the problem is in the second half.  Recurse to isolate the problem record(s).


Tracking SQL queries and rows operated on for documentation/audit

Oracle provides in-built auditing tools - consider using them instead of hand-rolling out these things.

Oracle Base provides some basic info as to how to get started on Auditing:

  Auditing can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

To enable auditing to database audit trail enable auditing to db

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

System altered.

Shutdown & restart the db

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Now to audit SELECTS, INSERTS, UPDATES, DELETES by user cube do this:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY cube BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY cube BY ACCESS;

The audited logs can be brought up by querying DBA_AUDIT_TRAIL

Further reading:

Auditing
Fine grained auditing
Configuring and administering auditing


Is it possible to find the values of MySQL variables if I do not have SELECT privileges on mysql.user?

You could do things like these:

SHOW VARIABLES;

SHOW GRANTS;

Show status;


Finding and listing all columns in a SQL Server database with identity

Using the script from the Question as a starting point, you might be better off using the following query:

SELECT
  N'DBCC CHECKIDENT (''' +
  SCHEMA_NAME(tbl.[schema_id]) +
  N'.' +
  tbl.name +
  N''', RESEED); -- WITH NO_INFOMSGS;'
   , clmn.seed_value
   , clmn.increment_value
   , clmn.last_value
FROM       sys.tables tbl
INNER JOIN sys.identity_columns clmn
        ON clmn.[object_id] = tbl.[object_id]
WHERE  tbl.[object_id] <> OBJECT_ID(N'dbo.sysdiagrams') -- skip "dbo.sysdiagrams"
ORDER BY SCHEMA_NAME(tbl.[schema_id]), tbl.name;

The main difference is rather than determining the IDENTITY field by matching the column name found in sys.identity_columns, this query replaces the JOIN to sys.columns with sys.identity_columns and removes it from the WHERE clause. The effect of this is to implicitly filter the rows (since only tables with IDENTITY columns would be in sys.identity_columns) and to now have access to the IDENTITY meta-data: seed_value, increment_value, and last_value.

Matching on the column name only, you run the risk of selecting tables that might not have an IDENTITY column but do have a column that has the same name as one of the columns found in sys.identity_columns.


MySQL - Error code 2013. Lost connection to MySQL server during query

Nerver mind. I found solution.
The server crashed on errors, changing any preference would not help.
you gota press WINDOWS + R
and type in Services.msc
Find MySQL80
right click to reboot the server.


For Oracle, we mount the instance to the database or mount the database to the instance?

We are mounting a database to an instance. See the concepts guide:

  The instance mounts a database to
  associate the database with that
  instance.

One way I've seen it explained (probably here or on SO) is that the instance is the memory structures and processes, and the database is the data on disk. The instance has to have a database associated with it to be able to do anything useful.


If you need to check if there is any project that has task, use something like

SELECT a.id_task,
Case WHEN b.id_task IS NULL then 0 else 1 end as HasProject
from TASK a
LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task)
;
-- if you need number of tasks for a project
Select  a.id_task,
COUNT(b.id_project) as num_projects
from TASK a
LEFT join PROJECT_HAS_TASK b on a.id_task = b.id_task
GROUP BY a.id_task;


Issues installing SQL Server 2008 R2 or any other version on Windows 10

That looks like serious damage to your installation.

Have you considered uninstalling all SQL Server and Visual Studio (especially VS2010) software? If you have database files, you can probably leave them in place (but I would prefer to also make copies elsewhere).

Then delete the folders that may still have some SQL Server components from previous installs so as to have a 'clean' slate to work from. Then Install from the installation media for a brand new install.  Attach or restore the files and see if that resolves your problem.


OK, here is the answer to my particular problem.  While the commenters above were very helpful and had some good suggestions, like manually adding the SPN, there was one final part missing. You know, one of those that has to be completed on a dark stormy third Thursday of the month, only when the moon is waxing half way...

After a few days after this posting, I basically posted the same question on the Microsoft SQL forums to get a fresh prospective. Long story short, I had to have my network admin run adsiedit.msc and have him edit the service's domain account user. The userAccountControl attribute, which is a number that represents a bitmap fields had to be updated. I had to take a single existing number, 0x10200 (NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD) and OR in 0x80000 (TRUSTED_FOR_DELEGATION) and OR in 0x1000000 (TRUSTED_TO_AUTH_FOR_DELEGATION) for a grand total of 0x1090200!

Now, isn't that user friendly! I honestly don't know why that when we setup the account in the GUI for delegation that it did not set these bits too, but it didn't.

I now have Bulk Insert running correctly for a trusted connection (Windows Authentication) and impersonating correctly on two out of three of my SQL Servers. Not sure why it is not one the one, but that is for a different day...


converting varchar to number while passing to IN clause

If you want to go down this route (be aware there's a limit of 4000 values per IN, and all of the individual SQL statements are going to flood your shared pool, potentially causing performance problems), you can alter your code to use execute immediate and concatenate the IN list into the query.

You'd be better off combining the 2 queries in your code into a single query, and avoiding this completely.


Where are tempory tables or record sets located?

It depends on mode used (buffered/unbuffered), as explained in the PHP manual:

  Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and is then kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology "store result" is also used for buffered mode, as the whole result set is stored at once.

Further reading

http://php.net/manual/en/mysqlinfo.concepts.buffering.php
http://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.concepts.buffering.html

This is answer to the "body" of your question. The title of that question asks about temporary tables, which are a different thing, internal to MySQL. If you are interested in that too, it is covered at https://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Simply said, MySQL stores small and simpler temporary results in memory and bigger ones and/or ones used in some more complex operations are stored on disk usually in temp folder.


Mongo slow startup after upgrade to 4.2.5

I tried with changing following 3 WiredTiger default eviction parameters:

storage.wiredTiger.engineConfig.configString:
"eviction_dirty_target=60,
eviction_dirty_trigger=80,eviction=(threads_min=4,threads_max=4)"

Now mongo is starting immediately.
Is it safe to set eviction_dirty_target and eviction_dirty_trigger values like this? Default is : eviction_dirty_target (default 5%) and eviction_dirty_trigger (default 20%). Thanks.


Unable to select the records from specific partition in mysql?

The syntax you are proposing (being able to explicitly select from a partition list) does not exist until MySQL 5.6.2:

  Starting in MySQL 5.6.2, SELECT supports explicit partition selection using the PARTITION keyword with a list of partitions or subpartitions (or both)... [src]

And indeed, your example comes from the MySQL 5.6 manual. I am assuming you are running MySQL 5.5 or even 5.1, but not 5.6.


Is it worth adding or rebuilding an index on a table already involved in an active query

It is not possible as the rebuild operation would be blocked due to the actively running query holding a lock on the table, and therefore it wouldn't complete until after the running query completed. Note this is to ensure data consistency, as some of the data pages of the index could contain a mix of data that was processed and data that was not yet processed by your query, and moving that data around could result in repeated reads of data for your query, resulting in invalid results, etc.
A couple other things I'd like to note, I'm not sure why a SHRINK was recently run on your database, but just a heads up that rebuilding your indexes will cause the database to regrow again. (I'm not saying you shouldn't rebuild your indexes, as 95% fragmentation is high, but just a heads up on how SHRINK is usually a wasted operation.)
On that note, 95% fragmentation is not usual unless your tables don't have clustered indexes on them. Just wanted to make sure you do have clustered indexes on your tables (as it is only in rare edge cases one would not use them)?
Also, there's unfortunately no way to determine exactly how long a rollback will take. It's possible the rollback could be anywhere between instantaneous to longer than the query has been running for. So if you want to try killing it, it is possible you get lucky and it rolls back quicker than the remainder it would've taken to complete. But it's hard to say. (I've rolled back hours long running queries in seconds before, but it's no guarantee.)
Finally, even with 95% index fragmentation, 12+ hours for a query to run is also an unusual amount of time. I'd be curious how big your tables are, and what kind of query it is that's running so slowly, if you'd be willing to share. I'd bet it can be sped up a lot more, even after rebuilding your indexes (as that only helps so much).


For InnoDB,

FULLTEXT(title,body) -- useful only for MATCH(title, body) AGAINST ...

FULLTEXT(title) -- useful only for MATCH(title) AGAINST ...

So, if you are doing both types of MATCH, you need both indexes.


I was able to add the following parameter to the server-side connection string and it worked without having to downgrade the driver version:

jdbc:mysql://localhost:3306/?allowLoadLocalInfile=true


After piping a file with SQL statements to mysql, how can I keep the command line session open?

Start an interactive shell, then just do:

source in.sql

... at the mysql prompt. The session will still be open after the file is executed, unless the file has a command in it that causes the mysql shell to exit.


Too many open connections with MongoDB 2.6 + PyMongo 2.7.2

So after a lot of struggle and a lot of load testing, we solved the problem by upgrading PyMongo to 2.8.1. PyMongo 2.7.2 is the first version to support MongoDB 2.6 and it sure does have some problems handling connections. Upgrading to PyMongo 2.8.1 helped us resolve the issue. With the same load, the connections do not exceed 250-300.


From "BOSPROD9", try to connect with the mongoshell to the other serves:

$ mongo --host 10.235.96.12 --port 27017

$ mongo --host 10.235.96.13 --port 27017

(Telnet is not the same.) If this doesn't work, it might be firewall or BindIP.

Check bind_ip (should be 0.0.0.0, change in mongodb.conf is it's 127.0.0.1):

$ netstat -nap | grep :27017 | grep LISTEN
tcp        0      0 0.0.0.0:27018           0.0.0.0:*               LISTEN      -

Try to look at the log-files on 10.235.96.12 and 10.235.96.13, why they are stuck. Did they receive the configuration?

Try to reconfigure this way:

mongo> var cfg = {_id:"ReplicaSet1",members:[{_id:0, host:"BOSPROD9:27017"},{_id:1, host:"10.235.96.12:27017"},{_id:2, host:"10.235.96.13:27017",arbiterOnly:true}]};
mongo> rs.reconfig(cfg);

Solution:

Use hostnames instead of ip's: give all servers a hostname, update the hosts-files and use hostnames in the rs-configuration. It seems that mongodb advices not to use ip addresses, but hostnames.


Per Mike Rose's answer on the Microsoft Social site, I believe this should work:
DBCC FREESYSTEMCACHE ('ALL');

DBCC FREEPROCCACHE;

There will be some performance hit for this as SQL Server will recreate Query Plans, but it should allow you to shrink TEMPDB.


MySQL: DELETE didn't shrink DB size, nor table size

There are several ways to delete 'most' of a table.  Perhaps the best is
SET @@innodb_file_per_table = ON; -- if this is not already on  (optional)

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE ... -- the rows you want to _keep_;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

More techiques:  http://mysql.rjweb.org/doc.php/deletebig
If this will be a recurring task, see the partitioning suggestion in that link.


After an upgrade /patch of your SQL Server, the databases usually the system databases undergo an upgrade as well via scripts. If the upgrade encountered any issues, it is possible that you could corrupt or script upgrade would not be successful.

Couple of things you can do:

Perform a repair of your SQL Server since you are using SQL Server 2012
SQL Server 2012 does come with template system databases when you first install.

  C:\Program Files\Microsoft SQL
  Server\MSSQL11.[instance_name]\MSSQL\Binn\Templates

This is the location where the system databases are stored. Remember these are templates from your first time installing so it will not have any custom configurations, logins, jobs, and more. You will have to copy it to the location of your corrupted system database and replace it.

Option 3 is the use of a trace flag 902 which will bypass the script upgrade during SQL Server startup. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.


Exclusion With Many to Many Relationships

I'm more of a MS SQL guy so please excuse me if my syntax is a bit off for mysql, but I would probably go with something like this.

SELECT topic_id, topic_name
FROM topic T
WHERE NOT EXISTS (
  SELECT TOP 1 1
  FROM book_has_topic BHT
  WHERE BHT.topic_topic_id = topic_id
    AND BHT.book_book_id = [bookid])


Simple, start your replica sets nodes with --replSet xxx (where xxx is your replica set name) -parameter. If you use config files, then replication.replSetName: xxx

Read here how to configure/deploy replica set.


All versions prior to 2016 (currently in CTP/beta) doesn't allow you to easily lock DBAs from the data. Column encryption that you employed stores the key in the DB and secures it using the key hierarchy within the database which roots itself in the service master key. That means you can't lock it away from DBAs. The only way out is to use extensible key management (ekm) to store your encryption keys outside of the database. That allows you to cut DBAs out of the loop but it implies your users are either technically savvy or you have done a great job integrating the user's application with the ekm provider. You can read up more about ekm and implementation options in the links below

https://msdn.microsoft.com/en-us/library/bb895340(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/dn198405(v=sql.110).aspx
https://www.thales-esecurity.com/knowledge-base/solution-briefs/thales-microsoft-sql-server-ekm
http://www.safenet-inc.com/data-protection/database-encryption/sql-encryption/

If you can upgrade to SQL Server 2016 (still in CTP/pre-release but by the time you test, upgrade, test, modify app, test it'll probably be released), the new Always Encrypted feature makes this a lot easier. Encryption takes place on the client (app) side and the master key is stored client side. Server will only ever receive cipher text so DBAs are essentially blocked even if they run server side or network level trace. However, that's only in 2016 and there's no indication it will be backported to earlier versions (rarely ever happens).


It's kind of a barebone question, but...

@DateToUse is the date you are searching for.

@StringOutput is the cto column.

I'm using the ID field as a tie-breaker in order to protect against duplicate or overlapping ranges in from/to fields.

DECLARE @DateToUse DATE
DECLARE @StringOutput VARCHAR(50)

SET @DateToUse = '3/2/2019'

SET @StringOutput = (SELECT TOP (1) cto FROM dbo.MyTable WHERE @DateToUse BETWEEN from AND to ORDER BY ID)

PRINT @StringOutput


Is it possible with SQL Server 2008 to have a Partioned Tables with an Identity Column?

I would never tell you it is a not a good idea to use an IDENTITY column on a partitioned table.

Including an IDENTITY column in a partitioned table is supported.


Please post the tail end of your MySQL error log.  Usually, it will be in /var/log/syslog or /var/log/mysql/mysql.log (depending on your setup).

Without having some details, it is hard to say, but it almost sounds like you have network connectivity problems.  Please ensure that the necessary ports are open on the third node to properly allow for all Galera traffic.


Downgrading from SQL Server 2008 to 2005

You could BCP the data from one SQL Server instance to another instance. That would be the fastest way to copy the data from one version to another. Depending on the volume of data, it might take a long time.


mysql selecting a distinct summed column while having

If I understand correctly and you only want DISTINCT b values, you just need to remove the a column from the SELECT list and use SELECT DISTINCT:

SELECT DISTINCT
    t1.b - sum(t2.b) AS b
FROM
    t1 INNER JOIN t2 USING (id)
GROUP BY
    t1.id, t1.a, t1.b
HAVING
    t1.a - sum(t2.a) > 0
ORDER BY
    b ASC
LIMIT
    15 ;


Postgres 9.2 - Create View Different Column Names

addr:housename is an illegal column name to begin with. You could have only created that if you used a quoted identifier (see the manual for details)

To create a view and rename the column, simply provide a column alias in the view's query:

create or replace view v_some_view
as
select ... other columns ...
       "addr:housename" as addr_housename
from some_table;


Backup on both secondaries or a dedicated backup secondary

Unless you are actively archiving those LVM snapshots and copying them off the servers, having two live nodes doing snapshotting is not overkill. The alternative would be having a single node that would hold all of your snapshots, meaning you would not be able to restore to a previous state if anything happened to that machine.

One important thing to consider with this setup is that there is always the possibility of the replica set electing one of the secondaries to become a new primary, which means there is a risk of all writes/reads hitting the LVM volume that is being regularly snapshotted. That performance hit might not be severe, but it could impact the clients using the database.

The most common practise I have come across is to dedicate a hidden secondary with priority 0 to do the backups. If your replica set spans multiple geographical locations, it might make sense to have one of those dedicated backup nodes in each location.

By setting priority to 0, you know that the dedicated backup node will never step up and become a primary and by keeping it hidden it will not be advertised to any database clients, so no reads would reach it even if reads from secondaries were allowed.

If you choose to go down that route, make sure to keep the number of votes uneven, for example by making the dedicated backup node have zero votes if you already have three members in the replica set.


Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)?

Generally How much time it would take to delete such no of records ?

Impossible for us to guess - way too many factors involved here, including performance of your data and log drives (a factor of which level of service you chose), your log growth rate and number of auto-growth events that occur, concurrent activity, number of indexes, existence of foreign keys, indexed views, etc.

  If there is any Index on that table then is it gonna affect the delete statement?

Yes. With the exception of filtered indexes, a delete from a table will also delete from each index. This is not free. If you generate an actual plan using the free SentryOne Plan Explorer, it tries to make it very obvious that the cost involves other indexes too:

In Management Studio, there is no visual cue that you're not just deleting from a single (clustered) index, but the information is in the plan XML - so you can also dig for it in the properties:

  are there any common bottle-necks that may cause delete to take long time than it should take?

Yes, mentioned above and in the other answer. You need to find the right balance of number of rows to delete, and we can't guess that for you without a lot more information. Please read this post:

Break large delete operations into chunks

One of the key things you'll see there - and I mention it in an answer to another question of yours - is to use transactions to break the looped deletes up into separate atomic operations.

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
    SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
    IF @counter % 10 = 0
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
END

COMMIT TRANSACTION;

Again, you'll need to find the right balance here. Maybe it's committing 500 rows at a time, or 1,000, or 5,000. Impossible for us to know.

Another common bottleneck is logging activity. This may be resolved by using smaller transactions but could still be an issue. Unfortunately, in SQL Azure I'm not sure how exactly you would find out if that is the case (don't believe the default trace is an option for you).

If you are deleting the majority of the table, another option is to move the rows you want to keep into a new table, then drop the old table, and rename the new one.


Disable password policy for partially-contained database

Since the Syntax section of the CREATE USER doc doesn't include the CHECK_POLICY option, I assume it's not a possibility for the CREATE USER command.
It seems the only way to achieve it is a workaround. You should create a normal SQL Server login with CHECK_POLICY = OFF and migrate it with sp_migrate_user_to_contained.
CREATE DATABASE Test;
GO

EXECUTE sp_configure 'contained database authentication', 1;
RECONFIGURE;
GO

ALTER DATABASE Test
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE Test
SET containment=partial;
GO

ALTER DATABASE Test
SET MULTI_USER;
GO

USE Test;
GO

CREATE LOGIN userWeakPWD
WITH PASSWORD='pwd', CHECK_POLICY = OFF;
GO

CREATE USER userWeakPWD
FOR LOGIN userWeakPWD;
GO

EXEC sp_migrate_user_to_contained
    @username = N'userWeakPWD',
    @rename = N'keep_name',
    @disablelogin = N'disable_login';
GO

I couldn't find any doc from Microsoft stating the existing restriction for creating a new contained user with CHECK_POLICY = OFF, but I had no lucky finding one explaining officially how to achieve it too.

Source: CHECK_POLICY = OFF Contained CREATE USER Statement (Credits to Shulei Chen)


One database listening on two database names

I see two options.

SQL Alias as mentioned in the comment by McNets.  But I am not sure if it will work in your scenario, when it does work it is a great tool.
Use SQL Replication to create a copy of the database DatabaseOne named DatabaseOne_Report on the same sever.  Depending on a few variables, the two versions of the database will be completely in sync to delayed a bit.