ERROR: recursive reference to query "najdi_tot" must not appear within its non-recursive term I have a trouble with the following CTE query:

  WITH RECURSIVE najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
    UNION
    SELECT sv.nad FROM najdi_tot n, s_vztah sv
    WHERE sv.pod = n.sj
    UNION
    SELECT sv.pod FROM najdi_tot n, s_vztah sv
    WHERE sv.nad = n.sj
  )
  SELECT array_agg(sj) FROM najdi_tot

Following error occurs:

ERROR:  recursive reference to query "najdi_tot" must not appear within its non-recursive term
LINE 6:     SELECT sv.nad FROM najdi_tot n, s_vztah sv
                               ^

I made a google search ( postgresql "recursive reference to query" "must not appear within its non-recursive term" ) and found nothing but maillists where I failed to find the solution (perhaps it is there, but hidden in discussion).

What should my query do is to find all rows linked from a table connecting two rows in the same table (nad and pod).

The problem was caused by multiplication in the recursive calls. When I deleted one of them, the problem was solved:

  WITH RECURSIVE najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
    UNION
    SELECT sv.nad FROM najdi_tot n, s_vztah sv
    WHERE sv.pod = n.sj
  )
  SELECT array_agg(sj) FROM najdi_tot

Then I realized that the relation is symmetrical - at the first call I get all the linked rows and recursion only adds the row I had in the beginning. So I removed the recursion at all and everything is OK:

  WITH najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
  )
  SELECT array_agg(sj) FROM najdi_tot


Mysql - Load data infile query for Particular Values I'm developing a Java swing application with back end Database MySQL . I've a screen to upload my datas to the Database using the Query like

LOAD DATA local INFILE  'D:\\TEMP\\import\\jan_jun2013.txt'
INTO TABLE slsdata
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Here the datas are like from January to June. But if i want to skip any month data or to upload only a particular month then how will i modify the above query ?

In my database table , the date column is 'SLDATE'.

The Same question i've asked in some other forum's, but i'm not able to get any suggestions there!

Any suggestions please.?

IMHO there is no a perfect solution for such scenario but you can utilize IGNORE clause.

For example if you were to have an auto_increment id column in your table and you already have a row in it with a known id then your query to import only rows for february might look like this

LOAD DATA local INFILE  '/path/to/your/file.txt' IGNORE
INTO TABLE slsdata
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@sldate)
SET id = IF(MID(@sldate, 6, 2) = '02', NULL, 1), sldate = @sldate

What it does it reads values of sldate field in @slfield variable then in SET clause it checks whether it's february and if it is then assigns NULL to id column and if not then assigned predefined existing value 1 violating a UNIQUE constraint and IGNORE clause does exactly that it skips a row in such a case.

That being said I'd recommend another approach:

create a temporary staging table without any constraints
load whole file in it
do all necessary cleansing, validating against ref tables, transformations etc. on required subset of data (e.g. for particular month) utilizing all SQL capabilities
insert required subset of data from staging table into your factual table
truncate or drop staging table


Why CDC records nothing while being enabled? I enabled Change Data Capture on some tables in a Microsoft SQL 2008 database. Both of the following queries return 1:

select top 1 is_cdc_enabled from sys.databases where name = N'<DatabaseName>'

select top 1 T.[is_tracked_by_cdc]
    from sys.tables as T
        left join sys.schemas as S
        on T.[schema_id] = S.[schema_id]
    where S.[name] = N'<SchemaName>' and T.[name] = N'<TableName>'

but when I change the data of a concerned table, cdc.<SchemaName>_<TableName>_CT remains empty.

Why?

Check to make sure the SQL Agent is running, and that there is a capture job created that is enabled and running for the CDC instance, it should show Change Data Capture Collection Agent / executing.


MS Access M:N Relationship query: exclude records with more than the requested values I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here:

Let's assume we have a table called "books", one called "authors" and a link table connecting both.

Some books are written by one author, others are written by multiple authors.

As we can see, book one (book_id = 1) is written by only one person (authorID = 1) while book two (book_id = 2) is written by two people (authorID 1 and 3).
If we now try to get the book which is written by author 1 and author 3 we can go like this:

SELECT book_id, title, year_of_publication, pagecount, cover FROM (book
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 1) AS author1
ON author1.book = book.book_id)
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 3) AS author2
ON author2.book = book.book_id

which leads to this:

I am pretty sure that my solution to this is not the best way to do it but in this case it works... anyway, when we try to get only those books returned that are written by author 1 and nobody else, we have a problem. In code, this selection looks like this:

SELECT book_id, title, year_of_publication, pagecount, cover FROM book
INNER JOIN
link_author_book ON link_author_book.book = book.book_id
WHERE author = 1;

and leads to this recordset:

while it is technically true that authorID = 1 is the author of both of these books, what I really wanted to get was only book one (book_id = 1). So here is my question: How can I exclude records that have more than the queried values linked to them in this kind of m:n relationship? And how can this be done as a SQL statement in Access 2016? Thanks in advance

The key is learning to write queries which directly answer the questions.  Start with getting books with only one author ("and nobody else").  This will require an aggregate query which counts the authors per book:

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) = 1

Now join the results to the other tables to get detail you desire/require.  I personally prefer saving such intermediate queries then referencing them by name in the next query (since you can then use the Access query designer to edit them individually), but to be explicit I'll include it as a subquery:

SELECT book.title, author.lastname
  ((SELECT lab.book, first(lab.author) AS author
    FROM link_author_book AS lab
    GROUP BY lab.book
    HAVING Count(lab.author) = 1)
  AS SingleAuthor
  INNER JOIN book ON SingleAuthor.book = book.book_id)
  INNER JOIN author ON SingleAuthor.author = author.auth_id

Really, you could also write a query which answers your other questions like getting records with "more than the queried values"...

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) <> 1

and then "exclude" such records:

SELECT book.title, author.lastname
  ((book
  LEFT JOIN (SELECT lab.book FROM link_author_book AS lab
  GROUP BY lab.book
  HAVING Count(lab.author) <> 1)
  AS OtherAuthors
  ON book.book_id = OtherAuthors.book)
  INNER JOIN link_author_book AS lab ON book.book_id = lab.book)
  INNER JOIN author ON author.auth_id = lab.author
WHERE OtherAuthors.book Is NULL

This will initially select all records in [books] due to the left join, but will subsequently exclude books with more than 1 author by keeping only records which are NOT in the [OtherAuthors] subquery. But as you see this is an overly complex way to get the same results.  I only included this to show that it is possible to exclude records using other types of joins and appropriate conditions.  This is a useful technique especially when there is no way of selecting the desired records directly. (Sometimes excluding records really is the best approach, but it just isn't necessary in this case with the link table.)


I don't know how to read many to many relationships I'm reading this tutorial, and I have question about it.

Look at this picture:

Looking at Physical Many-to-Many Implementation I read the relationship this way: 'A user can be in zero or more projects'.

But I don't know how to read it in the other way from ProjectEmployee to Employee. How do you read it?

And another question, Why the relationship is one and only one on Employee side?

The many-to-many nature of the relationship is expressed in the Physical diagram by breaking the relationship down into a pair of many-to-one relationships.  A many-to-one relationship can be implemented using a foreign key that references a primary key.

Project-Employee contains a foreign key (presumably EmployeeID) that references the primary key of the Employee table (presumably ID).  That is why there is only one Employee allowed on that end on the upper line.

Note in passing that if you were to implement using some kind of non-relational DB, the diagram on the left would be more useful than the one on the right.

Also note in passing that, in the classical literature on DB design,  the one on the left would have been called the "Conceptual Model" and the one on the right would have been called the "Logical Model".  The physical model would have included DBMS specific features like tablespaces, indexes, and clusters.  Times change, and terminology changes in ways that can be bewildering to those who learned it the original way.


Should I use the PostgreSQL bit string? I've been learning about the bit string data type lately, and I am quite curious about:

At the bottom of this doc page there is the sentence:

  ... plus 5 or 8 bytes overhead depending on the length of the string

How are bit strings handled in other languages such as PHP, Java, C#, C++, etc., through drivers like Npgsql, ODBC, etc.

For question #1, using smallint or bigint will be much more storage efficient, and would perhaps offer a performance gain since integers are supported everywhere. Most programming languages handle bit operations on integers with ease. If that is the case, what is the point of introducing the bit-string data type? Is it Only for cases that need a large amount of bit masks? Bit field indexing maybe? I'm more curious about how bit field indexing is done in PostgreSQL.

For #2, I'm confused, more than curious. For example, what if I store week day bit masks in a bit(7) field, one bit for a day, with lowest bit representing Monday. Then I query for the value in PHP and C++. What will I get? The documentation says I'll have a bit string, however a bit string is not something I can use directly - as with integers. Then in this case, should I give up on bit field?

Can anyone elaborate why and when I should use bit or bit varying?

If you only have a few variables I would consider keeping separate boolean columns.

Indexing is easy. In particular, indexes on expressions are easy.
Conditions for queries and partial indexing are easy to write and read and meaningful.
A boolean column occupies 1 byte. For only a few variables this occupies the least space.
Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage
If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

Occupies 4 bytes on disk.
Very fast indexing for exact matches (= operator).
Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Occupies at least 5 bytes (or 8 for very long strings) plus 1 byte for each group of 8 bits (rounded up).
You can use bit string functions and operators directly.

Examples
For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).
For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.
Further reading

Is there any difference between integer and bit(n) data types for a bitmask?
Calculating and saving space in PostgreSQL
Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?


v$ or rc views to report on RMAN restore database preview Setup:

We take an Incremental level 0 backup weekly using:

RMAN> backup as compressed backupset incremental level 0 database;
RMAN> backup check logical as compressed backupset (archivelog all delete all input);

We take an Incremental level 1 backup nightly (except on the night that we take the incremental level 0 backup) using:

RMAN> backup as compressed backupset incremental level 1 database;
RMAN> backup check logical as compressed backupset (archivelog all delete all input);

In RMAN, I know that I can report on when recovery will start and which SCN will need to be restored in order to clear the datafile fuzziness to bring the database to a consistent state using the following:

RMAN> restore database preview;

Media recovery start SCN is 49928711194
Recovery must be done beyond SCN 49928711196 to clear datafile fuzziness

This tells me that if I issue a restore database; i'll need to recover SCN's 49928711194 to 49928711196 before I can open the database.

From here, I know that I can query which archivelog backups are available and which SCNs these archivelog backups contain using:

SELECT sequence#, first_change#, next_change#, id2, handle from (
  SELECT sequence#, first_change#, next_change#, id2
    FROM v$backup_archivelog_details where 49928711194 between first_change# and next_change#
UNION
  SELECT sequence#, first_change#, next_change#, id2
    FROM v$backup_archivelog_details where 49928711196 between first_change# and next_change#) archivelog
  JOIN v$backup_piece p
    ON archivelog.id2=p.set_count;

SEQUENCE#   FIRST_CHANGE#   NEXT_CHANGE#    ID2     HANDLE
22030       49923438913     49928711521     7940    L:\ORACLE\BACKUPS\ARCH_2_7940_1

My question is: is there a way to query a v$ view or rc view to obtain the start SCN and beyond SCN that RMAN> restore database preview; provides in order to clear the datafile fuzziness for a given backupset?

V$BACKUP_DATAFILE

  V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.

Media recovery start SCN is 7962109213
Recovery must be done beyond SCN 7962156379 to clear datafile fuzziness
Finished restore at 28-JUN-2017 09:27:17

And what I am interested in is ABSOLUTE_FUZZY_CHANGE# and CHECKPOINT_CHANGE#  column of that view.

  CHECKPOINT_CHANGE#: All changes up to the checkpoint change number are included in this backup

  ABSOLUTE_FUZZY_CHANGE#:  Highest change number in this backup

I used the following query to get the same result as given by RMAN restore preview command.

SQL>   select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
                        (select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
                        where  trunc(completion_time) = to_date('JUN-28-2017','MON-DD-YYYY')
                        and file# <> 0
                        order by completion_time desc
                        );

     FUZZ#    CHKPNT#
---------- ----------
7962156379 7962109213


Convert MS access to MySQL DB using php We are creating a system that has a button for converting MS Access DB to MySQL. Is there a way to convert MS Access DB to MySQL using PHP?

I would really appreciate any inputs on this one. Thank you in advance folks!

Use Workbench to migrate the data:

https://dev.mysql.com/doc/workbench/en/wb-migration-database-access.html


How to select maximum date_created value from all tables matching certain pattern? Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each individual table queried.

PROPOSED QUERY

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'MAX(date_create) MaxDateCreated FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    AND table_name LIKE 'ap_form_%'
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

What this does is:

Gathered all tables from the database you ar in that has ap_form_% as a pattern
Create SELECT query for Each ap_form_% table
Concatenate all the Queries into a Single Query
Execute all the Queries as a Single Query

EXAMPLE

This does a table count of all tables in the mysql schema

USE mysql
SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

EXAMPLE EXECUTED

MySQL [mysql]> USE mysql
Database changed
MySQL [mysql]> SET group_concat_max_len = 1024 * 1024 * 10;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
    -> FROM
    -> (
    ->     SELECT
    ->         CONCAT('SELECT ',QUOTE(table_name),' tblname,',
    ->         'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    ->         FROM information_schema.partitions
    ->     WHERE table_schema=DATABASE()
    ->     GROUP BY table_name
    -> ) A;
Query OK, 1 row affected (0.01 sec)

MySQL [mysql]> SELECT @LongQuery\G
*************************** 1. row ***************************
@LongQuery: SELECT 'columns_priv' tblname,COUNT(1) RowCount FROM columns_priv UNION SELECT 'db' tblname,COUNT(1) RowCount FROM db UNION SELECT 'event' tblname,COUNT(1) RowCount FROM event UNION SELECT 'func' tblname,COUNT(1) RowCount FROM func UNION SELECT 'general_log' tblname,COUNT(1) RowCount FROM general_log UNION SELECT 'general_log_backup' tblname,COUNT(1) RowCount FROM general_log_backup UNION SELECT 'help_category' tblname,COUNT(1) RowCount FROM help_category UNION SELECT 'help_keyword' tblname,COUNT(1) RowCount FROM help_keyword UNION SELECT 'help_relation' tblname,COUNT(1) RowCount FROM help_relation UNION SELECT 'help_topic' tblname,COUNT(1) RowCount FROM help_topic UNION SELECT 'host' tblname,COUNT(1) RowCount FROM host UNION SELECT 'innodb_index_stats' tblname,COUNT(1) RowCount FROM innodb_index_stats UNION SELECT 'innodb_table_stats' tblname,COUNT(1) RowCount FROM innodb_table_stats UNION SELECT 'ndb_binlog_index' tblname,COUNT(1) RowCount FROM ndb_binlog_index UNION SELECT 'plugin' tblname,COUNT(1) RowCount FROM plugin UNION SELECT 'proc' tblname,COUNT(1) RowCount FROM proc UNION SELECT 'procs_priv' tblname,COUNT(1) RowCount FROM procs_priv UNION SELECT 'proxies_priv' tblname,COUNT(1) RowCount FROM proxies_priv UNION SELECT 'rds_configuration' tblname,COUNT(1) RowCount FROM rds_configuration UNION SELECT 'rds_global_status_history' tblname,COUNT(1) RowCount FROM rds_global_status_history UNION SELECT 'rds_global_status_history_old' tblname,COUNT(1) RowCount FROM rds_global_status_history_old UNION SELECT 'rds_heartbeat2' tblname,COUNT(1) RowCount FROM rds_heartbeat2 UNION SELECT 'rds_history' tblname,COUNT(1) RowCount FROM rds_history UNION SELECT 'rds_replication_status' tblname,COUNT(1) RowCount FROM rds_replication_status UNION SELECT 'rds_sysinfo' tblname,COUNT(1) RowCount FROM rds_sysinfo UNION SELECT 'servers' tblname,COUNT(1) RowCount FROM servers UNION SELECT 'slave_master_info' tblname,COUNT(1) RowCount FROM slave_master_info UNION SELECT 'slave_relay_log_info' tblname,COUNT(1) RowCount FROM slave_relay_log_info UNION SELECT 'slave_worker_info' tblname,COUNT(1) RowCount FROM slave_worker_info UNION SELECT 'slow_log' tblname,COUNT(1) RowCount FROM slow_log UNION SELECT 'slow_log_backup' tblname,COUNT(1) RowCount FROM slow_log_backup UNION SELECT 'tables_priv' tblname,COUNT(1) RowCount FROM tables_priv UNION SELECT 'time_zone' tblname,COUNT(1) RowCount FROM time_zone UNION SELECT 'time_zone_leap_second' tblname,COUNT(1) RowCount FROM time_zone_leap_second UNION SELECT 'time_zone_name' tblname,COUNT(1) RowCount FROM time_zone_name UNION SELECT 'time_zone_transition' tblname,COUNT(1) RowCount FROM time_zone_transition UNION SELECT 'time_zone_transition_type' tblname,COUNT(1) RowCount FROM time_zone_transition_type UNION SELECT 'user' tblname,COUNT(1) RowCount FROM user
1 row in set (0.00 sec)

MySQL [mysql]> PREPARE qry FROM @LongQuery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MySQL [mysql]> EXECUTE qry;
+-------------------------------+----------+
| tblname                       | RowCount |
+-------------------------------+----------+
| columns_priv                  |        6 |
| db                            |      207 |
| event                         |        2 |
| func                          |        0 |
| general_log                   |        0 |
| general_log_backup            |        0 |
| help_category                 |       38 |
| help_keyword                  |      453 |
| help_relation                 |      992 |
| help_topic                    |      505 |
| host                          |        0 |
| innodb_index_stats            |    32532 |
| innodb_table_stats            |     3392 |
| ndb_binlog_index              |        0 |
| plugin                        |        0 |
| proc                          |       27 |
| procs_priv                    |        0 |
| proxies_priv                  |        1 |
| rds_configuration             |        1 |
| rds_global_status_history     |        0 |
| rds_global_status_history_old |        0 |
| rds_heartbeat2                |        1 |
| rds_history                   |        1 |
| rds_replication_status        |        1 |
| rds_sysinfo                   |        0 |
| servers                       |        0 |
| slave_master_info             |        0 |
| slave_relay_log_info          |        0 |
| slave_worker_info             |        0 |
| slow_log                      |        0 |
| slow_log_backup               |        0 |
| tables_priv                   |       48 |
| time_zone                     |     1768 |
| time_zone_leap_second         |        0 |
| time_zone_name                |     1768 |
| time_zone_transition          |   118021 |
| time_zone_transition_type     |     8611 |
| user                          |      138 |
+-------------------------------+----------+
38 rows in set (0.00 sec)

MySQL [mysql]> DEALLOCATE PREPARE qry;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]>

This should work for you.

GIVE IT A TRY !!!


Problem trying to force the use of a filtered index I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation:

CREATE TABLE IndexTest
(
    ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY,
    Col1 CHAR(1)
)
GO

CREATE INDEX ix_IndexTest ON IndexTest(Col1)
WHERE Col1 IS NOT NULL
GO

INSERT INTO IndexTest VALUES ('A'),('B'),('C'),('D')
INSERT INTO IndexTest VALUES (''),(''),(''),('')
INSERT INTO IndexTest VALUES (NULL),(NULL),(NULL),(NULL)
GO

I then tried to run the following query using a hint to force it to use the filtered index.

SELECT *
FROM IndexTest WITH (INDEX(ix_IndexTest))
GO

But I keep getting the error:

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

It works if I remove the filter but not with it. I even tried changing the filter in various ways such as WHERE Col1 = '' etc. Any time I have a filter I get that error. Can anyone tell me why? Am I doing something wrong?

I tried this on a SQL Server 2012 and 2014 instance and created a sqlfiddle of it here:

http://www.sqlfiddle.com/#!6/4a850/1

Edit: Not sure that it matters but specifically I was trying to see if I could see what rows were actually contained by the index. The idea came from seeing someone use this technique to recover data (using an unfiltered index) from a corrupted table.

Possibly the disconnect is that the filtered index won't automatically filter the results for you - you have to write a WHERE clause that matches the index's filter predicate in order to be able to use the index.

In other words, if you're only trying to get rows WHERE Col1 IS NOT NULL, you still need a WHERE clause to limit the rows. If SQL Server can't use the index you're trying to force to satisfy all rows to be returned by the query, which it can't do by definition of the index holding fewer (or the same*) rows than the table, it can't run.

  * There might be scenarios I haven't tested where this will work; for example, if the column is constrained to NOT NULL (or all rows happen to match the filter predicate), then the filtered index would, by extension, represent rows from the entire table. But that is a pretty contrived and impractical scenario (even if it were to work). And SQL Server couldn't generate a plan for the case where all rows happen to currently match the filter, because a single insert or update could break it.

If you're trying to locate data from the non-clustered index because the underlying table/CI is corrupt, you will probably have better luck with DBCC IND/PAGE or with a hex-editor-wielding Paul Randal impersonation.


Notification when the status on sql server replication is "Not Running" We have a lot of replications set up. Sometimes for some reason, the status of the replication changes to "Not Running/Expired Soon or Expired". Could you please let me know if there is a way we can receive alerts when the status changes on a particular replication.

Thanks

Could you please let me know if there is a way we can receive alerts when the status changes on a particular replication.

You can achieve that using Replication Alerts

e.g. Below will set up alert for Replication: agent retry. Note that you must have database mail enabled and configured as well as sql agent operator configured.

EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent retry',
        @message_id=14152,
        @severity=0,
        @enabled=0,
        @delay_between_responses=0,
        @include_event_description_in=5,
        @category_name=N'Replication',
        @job_id=N'00000000-0000-0000-0000-000000000000'

Refer to : All About Automatically Monitoring Replication Agent Failures


ORA-15081: failed to submit an I/O operation to a disk I have a test environment for Oracle single instance on ASM 11.2.0.3.

I have restored database from tape and during the recovery, the system crashed by some I/O error.

I restarted the server, mounted the database and run validate on archivelog(thinking that the blocks in this archivelog may be corrupted)

RMAN> validate archivelog sequence=2994;

RMAN-03009: failure of validate command on ORA_DISK_1 channel at 02/25/2014 12:24:13
ORA-19501: read error on file "+DATA01/crimesys/archivelog/2014_02_24/thread_1_seq_2994.294.8
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk

And the system crushed again.

Note:  Other validates are successful just this archivelog causes the system crash.

Details:

--Asm disk

ls -la /dev/oracleasm/asm_disk1
brw-rw---- 1 grid asmdba 253, 2 Feb 25 13:13 /dev/oracleasm/asm_disk1

--Trace info

more /u01/app/oracle/diag/rdbms/crimesys/crimesys/trace/crimesys_ora_2696.

WARNING: Read Failed. group:1 disk:0 AU:114682 offset:512 size:1048064
path:/dev/oracleasm/asm_disk1
         incarnation:0xe968a075 asynchronous result:'I/O error'
         subsys:System iop:0x7fd93c64b680 bufp:0x7fd93b64e000 osderr:0x0 osderr1:0x0
WARNING: failed to read mirror side 1 of virtual extent 13 logical extent 0 of file 294 in gr
oup [1.353915012] from disk DATA01_0000  allocation unit 114682 reason error; if possible, wi
ll try another mirror side

--Also when I check the disk of ASM it is successful.

SQL> alter diskgroup data01 check

NOTE: starting check of diskgroup DATA01
Tue Feb 25 13:36:59 2014
GMON checking disk 0 for group 1 at 5 for pid 18, osid 2181
SUCCESS: check of diskgroup DATA01 found no errors
SUCCESS: alter diskgroup data01 check

How to solve? Is the virtual block corrupted? How to check?
Please help.

If your are using multi-path make sure that your O.S. does not point to the file system instead of the disk groups.
  it happened to me 2 times after box reboot.

This is to answer your question:

SQL> set pagesize 800
SQL> select name, label, path from v$asm_disk;
NAME                           LABEL
------------------------------ -------------------------------
PATH

DGER_0000    /dev/oracleasm/disks/DSK5

DGSTBY_0000 /dev/oracleasm/disks/DSKPATHY

run the follwing command :

 /etc/init.d/oracleasm querydisk -d <your disk here>

post the output !


How can I reseed all identity columns? I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to start auto numbering from 1.

So I created a stored procedure that pass in the table name as a parameter, since it seems this will take time as its doing it individually, is there another way to do this for all of the tables at once?

   CREATE PROCEDURE [dbo].[USP_ReseedID]
@table_name varchar(50)
AS
BEGIN
    DBCC CHECKIDENT ( @table_name, RESEED, 0)

END

Instead of delete and then reseed, why not just truncate them? This deletes all the rows (in a slightly more efficient way than DELETE) AND reseeds the IDENTITY columns.

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

SELECT @sql += N'
  TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS -- leave this out if you want to truncate ALL tables
(
  SELECT 1
    FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

PRINT @sql;
-- EXEC sp_executesql @sql;

You could generate a similar script, in a similar way, that applies the RESEED to each table, but why?

Also, stay away from awful stored procedures like sp_MSforeachtable. They're undocumented and unsupported for a reason. Check out the problems with sp_MSforeachdb:

http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

(I haven't gotten around to proving it yet, but I suspect the procedure for tables - which has the same cursor and overall logic as for databases - can break in a similar way.)

Finally, if you can't truncate because of foreign keys, check out this script (#4):

https://www.sentryone.com/blog/aaronbertrand/grouped-concatenation-use-cases


"Prelogin failure" when attempting to run stored procedure across linked server I'm getting the below error when attempting to run a stored proc across a linked server that is going from Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   to Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64).

The sql instance that I'm linking to is running the database engine successfully, however the sql server agent on the linked server's instance is unable to start with a 'object reference not set to an instance of an object' .net exception being thrown.

This error only started to occur after a scheduled server restart, so I'm thinking maybe there is a config option that was set somewhere that only took effect after the restart.

I've checked firewall and the resources available to the linked server (CPU, RAM and disk) and couldn't see anything causing an issue.

Is there anything else I should be checking? Any config options that may interfere with this that might have taken effect with the server restart?

EDIT: Ok so a server restart fixed the issue. I have no idea why though - can anyone suggest why this could have occurred and how it could've been fixed without a server restart?

OLE DB provider "SQLNCLI10" for linked server "Linkedserver" returned message "Client unable to establish connection due to prelogin failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI10" for linked server "Linkedserver" returned message "Client unable to establish connection".
Msg 26, Level 16, State 1, Line 0
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.

The t-sql to create the linked server is below:

/****** Object:  LinkedServer [LinkedServer]    Script Date: 09/30/2009 12:34:15 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'LinkedServer\InstanceName', @provider=N'SQLNCLI', @datasrc=N'LinkedServer\InstanceName', @catalog=N'Database'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'userid',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'use remote collation', @optvalue=N'true'

I've seen it happen twice after SQL Server service packs were applied - service packs that took a really long time (hours) to finish the upgrade, or broke as they were trying to apply changes to the system databases.

The fact that you mentioned there was a scheduled restart clues me in, too - that means someone had probably staged updates to happen on restart, and they did - but then they caused an outage.

As a result of this, I'm never a big fan of automatically applying updates (Windows or SQL) on SQL Servers.


Replicating a View to a Table. Issues with schema binding, view indexing I am rather new to this.

On my development server, I have 3 views that get live data from another database on a private internal server.  Sample structure:

DevDB
   --> Tables
   --> Views
      --> dbo.Customers
   --> Stored Procedures

On my production server, those same views are set up as tables

ProdDB
   --> Tables
      --> dbo.Customers
   --> Views
   --> Stored Procedures

Up until now, I have just been periodically updating the Customers table on Prod using an import task in SSMS.  I've been wanting to automate this process, and read that you should use Replication for this.

I selected the appropriate views on the publisher end.  I understand that I can have views replicate as tables by setting the article's type to indexed view logbased.

See "Publishing Views" at this link: http://msdn.microsoft.com/en-us/library/ms152559%28SQL.90%29.aspx

However, I am having trouble setting the type property.  When I attempt to set it, I get an error.

exec sp_changearticle @publication = N'MyPub',
    @article = N'MyView',
    @property = N'type',
    @value = N'indexed view logbased',
    @force_invalidate_snapshot = 1

I get this error:

'type' is not a valid property for a schema only article'.

So I go to index the view, but I can't because it's not schemabound.

So then I go to schema bind the view but I can't because the view is attached to a remote database.

Cannot schema bind view 'dbo.MyView' because name 'RemoteDb.dbo.MyTable' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

And it appears it is impossible to set schema binding on a view that queries a remote database.  What the heck are my options here?

Thanks

When you right click on Tasks and use the Import Data/Export Data task, you're building an SSIS package in the background. In the penultimate screen, you're usually greeted with

Instead of Run immediately, check "Save SSIS Package". I generally save to the File System with the Package Protection Level of Do not save sensitive data. Your preference may be different but the important thing is to note where you save this.

You may now use your scheduling tool of choice to invoke the execution of this SSIS package and keep your production environment synchronized with your values in your development environment.


How to copy non clustered indexes in transactional replication I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to subscriber.Based on this answer http://www.sqlservercentral.com/Forums/FindPost1325516.aspx is good idea to script all non clustered indexes on publisher and then execute it on subscriber or is there another way which is better ?

This can be done.

But as far as i know, it will break the replication.You'll have to enable the option and re-initialize the subscriber with a new snapshot. Also, if you have non-clustered indexes with INCLUDED, you'll have to modify the definitions (.IDX files) manually to add INCLUDED to them.

You can enable the option where you can replicate the non clustered indexes as described here.

As mentioned:

  Changing the article options will effectively create indexes on all
  subscribers with only a few clicks. Also, if a new snapshot is taken
  for the publication and applied to the subscribers, any indexes
  created directly on those subscribers will be removed.  If all indexes
  are not needed, alternatively, you can create selective indexes
  directly on a subscriber. However, it reduces administrative overhead
  to change the option directly in the replication settings for the
  article.


Can multiple Oracle databases share a single parameter file? I'm a developer working on an application team that supports a system with one production database and 4 test databases (2 Dev, a SIT and UAT environment).

The prod database exists on it's own Unix server and the 4 test databases all live on a different Unix server. For various reasons, over the years these 6 databases have become quite out sync with regards to their Initialization Parameters. A quick comparison of the V$Parameter table reveals a number of key setting differences that have been causing our application team problems and need to be corrected.

I'm working with our DBA team now to try and correct them but it is a large Enterprise and the process is painful. I'd love to find a way to keep these parameters better in line for those databases that share the same server.

Is there a way to have multiple databases share a parameter file? I reviewed some documentation and saw some references to doing this for a RAC setup but these databases aren't clustered but rather just copies of one another. I know certain parameters like SID and db_name would need to be unique but I was wondering if common settings like global_names and resource_limit could be centralized somehow?

Note I'm currently using 11G but we plan to upgrade to 12 within a month.

No, you would have to use separate parameter files/spfiles for each database.  As you've pointed out, you'll need some settings to differ (locations of files, database name, etc.).  In many environments, you'd want to adjust memory and other performance settings differently because the development environments often don't have nearly as much RAM as the production environment.

Even if you could share a parameter file, though, you wouldn't want to.  Changing a parameter should involve some amount of development and testing just like changing code.  So you would want to make that change in dev, validate it, and promote it up just like a code change.  If you were sharing a parameter file, you'd have to make the change in all the environments simultaneously which somewhat defeats the purpose of having multiple environments.  If you make a change in dev that doesn't work out, you need to back that out just like you would back out a code change.

You could certainly take a backup of the production parameter file and copy that down with whatever changes are necessary to the lower environments.  You could use whatever mechanism you use today to manage code deploys (I'm assuming some sort of source control system and build process) to version those parameter files and to deploy them so that you know what the differences are and why (i.e. dev uses a lower memory_target in general, dev has global_names set to true because you are testing that out for project X but that hasn't been promoted to production yet).


SQL Server taking a lot of memory I have SQL Server Express on my VPS(2GB RAM) with a database size of 1.5 GB that I expect will increase to 15 GB. I have noticed that SQL Server is using 1.5 GB RAM even when only small queries has been run against the database.

Is there any relation between memory and database size? Can I tune my SQL Server so it can handle memory better and not use as much memory as it does now?

Here is the memory status of SQL Server:

VM Reserved,1455488
VM Committed,19320
AWE Allocated,0
Reserved Memory,1024
Reserved Memory In Use,0

SQL Server is known for by default consuming as much memory as the OS allows it to take, so you'll have to reduce it manually.

One way of doing this is described here and here. On the second link, specifically, you will find this example that might be useful for you:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Instead of 4096, you might want to enter something smaller depending on your available RAM.

And, as @outcoldman says in a comment to your question, SQL Server Express doesn't allow 15 GB databases. This answer specifies 4 GB max for SQL Server Express 2005 and 10 GB max for SQL Server Express 2008 & 2012.


How can SQL server AlwaysOn AG can be used as a DR feature? I'm using AlwaysOn ag feature as a HA feature where automatic failover is possible.. I was thinking to log shipping as DR but there is potential data loss in that.. thinking of using ag as a dr(async).. is it possible? how will it work?

AlwaysOn supports multiple replica databases, so you can have one replica as your HA database, and another replica as your DR database. AlwaysOn is great in that one technology potentially provides your HA and your DR.

The HA database would typically be in the same datacentre, and be set to synchronous commit with automatic failover. The DR database would typically be in another datacentre, but because of the latency it would typically be asynchronous as first choice. This means that data-loss is possible. You could experiment to see whether the DR replica, set as synchronous, performs well enough for your application.

Please remember that if you use AlwaysOn as your DR technology, you still need to take database backups like normal - and get them shipped off the server.


count distinct number of records in sql I have table that contain one is event Id ,second is school Id and third is game ID I want to count number of school who has participated in that event with unique game ID (One school can participate in multiple event with different game)

Example

Event Id   school ID   Game ID
1          20          1
3          21          2
1          22          3
2          22          4
3          21          5
1          20          6

I need the output is

Event Id        schoolCount
1               2
3               1
2               1

Just use a simple and standardized GROUP BY and COUNT(DISTINCT ..)

CREATE TABLE foo(a,b,c)
AS VALUES
    ( 1, 20, 1 ),
    ( 3, 21, 2 ),
    ( 1, 22, 3 ),
    ( 2, 22, 4 ),
    ( 3, 21, 5 ),
    ( 1, 20, 6 );

SELECT a, COUNT(DISTINCT b)
FROM foo
GROUP BY a;


Connection to an oracle db from another computer I have installed Oracle 11g to an ubuntu. I can connect to DB using sqlplus from the localhost. But I cannot connect from another computer. I have configured my tnsnames.ora as followed:

ELIF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.58)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = ELIF)
)
)

When I make a tnsping I got the following error:

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ELIF)))
TNS-12541: TNS:no listener

Edit:
lsnrctl status and netstat -an | grep 1521 result:

oracle@cms:~$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-JAN-2012 13:26:16

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                25-JAN-2012 02:33:02
Uptime                    0 days 10 hr. 53 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/cms/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "ELIF" has 1 instance(s).
  Instance "ELIF", status READY, has 1 handler(s) for this service...
Service "ELIFXDB" has 1 instance(s).
  Instance "ELIF", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@cms:~$ netstat -an | grep 1521
tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:32656         127.0.0.1:1521          ESTABLISHED
tcp        0      0 127.0.0.1:1521          127.0.0.1:32656         ESTABLISHED
unix  2      [ ACC ]     STREAM     LISTENING     115265   /var/tmp/.oracle/sEXTPROC1521

Type:

lsnrctl start

On your Unix box. That starts the listener.

If you still can't connect after that it's either a firewall issue or the listener isn't configured.


Access to Server and Real Data for Performance Tuning I'm newbie in performance tuning (PT) in SQL server and I have read some documents about how to do it. It is based on theory from the articles or books.

The next step to is to learn about it by practice and if you are a private person who doesn't have access to a server with lots of data. Let's say that it is a student who wants to learn about PT in practice.

My question is:
How should a student learn about PT in practice when you don't have access to a server with real data?

Is there a company which provides education with a server and real data?

Below is not complete (and longer than a comment)- since this is a very open ended question and will be closed (I am voting it to close as well).

I have laid below down to give you guidance and help you get started.

You have to install sql server. Go for a Developer edition.
download and restore Adventure Works 2014 Sample Databases
If you want to work with lots of data then - Enlarge the AdventureWorks Sample Databases
You can have open source data - e.g. stackexchange data and then import it into sql server.
Highly recommend to read this answer - performance tuning is an art not science .. and you will slowly and gradually master it.
Among many book that I have read, I would highly recommend T-SQL Querying by  Itzik Ben-Gan

  Is there a company who provide education with server and real data?

Stackexchange does provide with data and an interface. Infact, Brent and his team utilizes this data to demonstrate query tuning.

Also, you can play with optimizer to lead it to believe you have a large data (this is very advance and would not recommend to any novice in SQL Server field)

UPDATE STATISTICS table_name WITH ROWCOUNT = 10000000000, pagecount = 100000000000


Why isn't my !include directive working in MySQL? I am experimenting with option files in MySQL.  I have a ~/.my.cnf file which includes only the following:
!include /Users/myself/Workspace/project_time/mysql/foo.cnf

[client]
i_am_a_dummy

And I have a file /Users/myself/Workspace/project_time/mysql/foo.cnf which includes only the following:
[mysqld]
skip_column_names

I have newlines at the end of each file.
I also have a database, new_database, which includes a table t1.
I run MySQL with the command mysql -uroot -p new_database; and type in my password at the prompt, after which I see the MySQL prompt.  When I type select * from t1; at the prompt, I would expect to see the output without headers, like so:
mysql> select * from t1;
+------+------+------+
| 2001 | 1    | 1    |
| 2001 | 1    | 20   |
| 2001 | 1    | 30   |
| 2001 | 2    | 2    |
| 2001 | 2    | 23   |
| 2001 | 2    | 23   |
+------+------+------+
6 rows in set (0.00 sec)

Unfortunately, instead I see the following:
mysql> select * from t1;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2001 |     1 |    1 |
| 2001 |     1 |   20 |
| 2001 |     1 |   30 |
| 2001 |     2 |    2 |
| 2001 |     2 |   23 |
| 2001 |     2 |   23 |
+------+-------+------+
6 rows in set (0.00 sec)

I have already tried to use [client] as the option group in foo.cnf instead of [mysqld], but that did not work.
When I add skip_column_names directly to ~/.my.cnf (just below i_am_a_dummy), it works as expected.
What am I missing?

When I created foo.cnf, I set the permissions to 777.  Apparently world-writable config files are ignored:
~/Workspace/project_time/mysql ()  $ mysql --print-defaults -uroot -p<secret> new_database;
mysql: [Warning] World-writable config file '/Users/myself/Workspace/project_time/mysql/foo.cnf' is ignored.
mysql would have been started with the following arguments:
--i_am_a_dummy -uroot -p<secret> new_database

When I reset the permissions for foo.cnf to 755 and re-run the command, I get the following:
~/Workspace/project_time/mysql ()  $ chmod 755 foo.cnf
~/Workspace/project_time/mysql ()  $ mysql --print-defaults -uroot -p<secret> new_database;
mysql would have been started with the following arguments:
--skip_column_names --i_am_a_dummy -uroot -p<secret> new_database

And when I invoke the aforementioned SELECT statement in the CLI, it appears as expected:
mysql> select * from t1;
+------+------+------+
| 2001 | 1    | 1    |
| 2001 | 1    | 20   |
| 2001 | 1    | 30   |
| 2001 | 2    | 2    |
| 2001 | 2    | 23   |
| 2001 | 2    | 23   |
+------+------+------+
6 rows in set (0.00 sec)

EDIT: The warning is actually printed out even when not passing the --print-defaults flag, I just didn't notice it among the other output in the CLI.  Since --print-defaults results in much less output on start-up, the warning was easier to notice.


Most efficient way to execute DELETE and INSERT operations in the same Table I have the following structure:

Table1              Table2
C11, C12              C21
 1   A                 A
 1   B                 B
 1   E                 W
 2   F
 2   B
 3  ...

I need to execute the following two operations:

  To delete those rows which are in Table1 but not in Table2 and C11 = @Parameter (In this case @Parameter = 1)

  To insert those rows which are not present in Table1 but are present
  in Table2

 Result
 Table1
 C11, C12
  1   A
  1   B
  1   W
  2   F
  2   B
  3  ...

The simplest way to solve this is first deleteing all the records in Table1 and then inserting all records in Table2:

DELETE FROM Table1 WHERE C11 = @Parameter
INSERT INTO Table1 SELECT @Parameter, C21 FROM Table2

Definetely I am looking for something more efficient, so I tried the following:

DELETE t1 from Table1 t1 -- Delete
LEFT JOIN Table2 t2 ON t1.C12 = t2.C21
WHERE t1.T11 = @Parameter AND t2.C21 IS NULL

INSERT INTO Table1
SELECT @Parameter, C21 from Table2 t2 -- INSERT
LEFT JOIN Table1 t1 ON t1.C12 = t2.C21 AND t1.C11 = @Parameter
WHERE t1.C12 IS NULL

I am concern about performance because Table1 has millions of records. I think the join in both querys is a waste of resources.

I did try to create a select which could return both kind of records, those who need to be deleted and those who need to be inserted, but i did not succeed. I also tried using the MERGE operation but I think MERGE does not fit in this specifyc situation

Maybe someone has faced this problem before and can provide some advice.

I will appreciate any suggestion.

Using a merge statement is exactly what you need, but you can make it a little faster by using CTE's for the target and source tables.  Putting an index on dbo.Table1 (C11, C12) should make this pretty fast as well.

;WITH CTE_Target AS
    (
    SELECT T.C11, T.C12
    FROM dbo.Table1 AS T
    WHERE T.C11 = @Parameter
    )
, CTE_Source AS
    (
    SELECT T.C21
    FROM dbo.Table2 AS T
    )
MERGE INTO CTE_Target  AS T
USING CTE_Source  AS S ON S.C21 = T.C12 AND T.C11 = @Parameter
WHEN NOT MATCHED BY TARGET THEN
    INSERT (C11, C12)
    VALUES (@Parameter, S.C21)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;


SQL Server 2019 Performance issue with large INSERT We are converting several ETL processes from SQL Server 2016, Windows 2012 server to a Windows 2019, SQL Server 2019 (CU8) environment.
One process is running longer in SQL Server 2019 than it used to in SQL Server 2016. The process performs an INSERT into B, select x from A, where x is a series of substring and case statements.
Table A basically contains 1 data column, 6,000 bytes long. Table B consists of over 950 data columns.  I bulk insert the data from a flat file into table A, then I parse the data into table B using various substring commands.
Table A contains 4.7 million rows and loads in 47 minutes, I split out 1 million rows for testing purposes and this loads in 10 minutes.
On my SQL Server 2016, Windows 2012 server, a full load runs in 12 - 14 minutes, while the 1 million sampling runs in 1 minute 51 seconds.
For grins, I stalled SQL Server 2016 on my ETL 2019 server and ran the same tests. Full load 7 minutes 14 seconds, whereas the 1 million sampling took 1 minute 32 seconds.
All three instances have a MAXDOP of 8 and a Cost Threshold For Parallelism (CTFP) of 50.
This is a dedicated server running SQL Server 2019 for ETL processing. No other apps running on it:

2 - 3.80Ghz processors with 4 cores per socket and 8 logical processors per socket
1571465 MB of RAM (1.5 TeraBytes)
16,813 GB on-board SSD storage
Very controlled testing, no other processes running at the time of tests

While the actual ETL process is procedure based, I pulled the insert code out and I'm running it in SSMS on the local server as T-SQL commands, for testing.
I understand there are a lot of new "performance" improvements in SQL Server 2019 and I've tried a lot of different combinations (different combinations of MAXDOP and CTFP, turning different SCOPE values on and off), so far, nothing has helped reduce the run times.
Some observations:

reducing the target table to only 100 columns, 1 million rows loaded in 1 minute; 4.7 million loaded in 5 minutes
increasing the column count to 250 and loading 1 million rows took 6 minutes.
I cannot put my finger on it, but something around the substring/case function and how its interacting with SQL Server is causing me some grief.
Multiple executions with different MaxDOP (0,4,8) and CTFP, resulted in no difference, except MaxDop=4, which increased run time.

Snippet of code:
INSERT into Base with (TABLOCK)
SELECT
   CHD_A = substring(importtext,14,28)
  ,CHD_B = getdate()
  ,CHD_C = substring(importtext,14,4)
  ,CHD_D = substring(importtext,18,4)
  ,CHD_E = substring(importtext,22,4)
  ,CHD_F = CASE WHEN substring(importtext,45,6) = ' ' THEN NULL ELSE RTRIM(substring(importtext,45,6)) END
 , and so on and so on for 950+ columns.

P.S. Ran a quick test of this same load on a VM server running SQL Server 2017, with 16GB of ram. Query finished in 1 minute 19 seconds, for 1 million rows.
So, I'm reaching out to SQL Server 2019 guru's for advise and where to look, what to tune. Converting to SSIS at this time is not an option.
Here are the plans;
SQL 2016
SQL 2017
SQL 2019

From comments:
To all intents and purposes, those plans are identical. As such, I'd focus more on differences in the physical set up and server settings. If the query optimizer for 2016 and 2019 are arriving at identical plans, but, behavior is different, chasing optimizer settings, query hints, statistics, cardinality estimation and other plan driven decisions is a waste of time. Since the majority of the performance difference is in that clustered index scan (1 sec vs. 28 seconds) it's likely to be a disk or memory problem. Although the actual insert operation is also twice as long. Same likely issue. - Grant Fritchey


How can I help SQL Server recognize my indexed view column is NOT NULL-able? I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes):

CREATE VIEW dbo.balances
WITH SCHEMABINDING
AS
SELECT
      user_id
    , currency_id

    , SUM(transaction_amount)   AS balance_amount
    , COUNT_BIG(*)              AS transaction_count
FROM dbo.transactions
GROUP BY
      user_id
    , currency_id
;
GO

CREATE UNIQUE CLUSTERED INDEX UQ_balances_user_id_currency_id
ON dbo.balances (
      user_id
    , currency_id
);
GO

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions. However, when I look at the view definition in Management Studio's Object Explorer, it marks both balance_amount and transaction_count as NULL-able columns in the view.

I've taken a look at several discussions, this one being the most relevant of them, that suggest some shuffling of functions may help SQL Server recognize that a view column is always NOT NULL. No such shuffling is possible in my case, though, since expressions on aggregate functions (e.g. an ISNULL() over the SUM()) are not allowed in indexed views.

Is there any way I can help SQL Server recognize that balance_amount and transaction_countare NOT NULL-able?
If not, should I have any concerns about these columns being mistakenly identified as NULL-able?

The two concerns I could think of are:

Any application objects mapped to the balances view are getting an incorrect definition of a balance.
In very limited cases, certain optimizations are not available to the Query Optimizer since it does not have a guarantee from the view that these two columns are NOT NULL.

Is either of these concerns a big deal? Are there any other concerns I should keep in mind?

user_id, currency_id, and transaction_amount are all defined as NOT
  NULL columns in dbo.transactions

It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases:

create table foo(bar integer not null);
select sum(bar) from foo
-- returns 1 row with `null` field

And is also true in the generalized versions of group by like cube

This simpler test case illustrates the point that any aggregate is interpreted as being nullable:

CREATE VIEW dbo.balances
with schemabinding
AS
SELECT
      user_id
    , sum(1)   AS balance_amount
FROM dbo.transactions
GROUP BY
      user_id
;
GO

IMO this is a limitation (albeit a minor one) of SQL Server - some other RDBMSs allow the creation of certain constraints on views that are not enforced and exist only to give clues to the optimizer, though I think 'uniqueness' is more likely to help in generating a good query plan than 'nullability'

If the nullability of the column is important, perhaps for use with an ORM, consider wrapping the indexed view in another view that simply guarantees the non-nullability using ISNULL:

CREATE VIEW dbo.balancesORM
WITH SCHEMABINDING
AS
SELECT
    B.[user_id],
    B.currency_id,
    balance_amount = ISNULL(B.balance_amount, 0),
    transaction_count = ISNULL(B.transaction_count, 0)
FROM dbo.balances AS B;


SQL Server Active Directory login groups with limitation I'm DBA in my organization and use SQL Server 2012 and 2014. I requestd to define multiple group in active directory such as Developer, DBA, supporter and ets... and add my users to this groups. after this a create login from this groups and grant access to DBA, Developer and supporter groups. Change in my organization chart is above, and each time one personnel changed his position, I request to change his group in Active Directory.

My problem: The Admin of Active Directory can add his user or any user to each of this groups and get access to my database without that I get it.

I want to use this groups for grant permission and looked up for a way for solve my problem.

Thanks in advance.

This is a common issue, you want to use groups to keep things simple but you don't want to lose visibility of who has access to your databases. The way I see it you have two options:

If you trust your AD admins (If you don't you have bigger problems) then use AD groups.
If you don't trust your AD admins then you will have to manage access on a per user level by giving everyone an individual SQL Server login.


Trick for AUTO_INCREMENT on composite keys in InnoDB In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table:

CREATE TABLE `ExampleTable` (
     `KeyPartA` mediumint(8) unsigned NOT NULL,
     `KeyPartB` smallint(5) unsigned NOT NULL,
     `Data` text NOT NULL,
     PRIMARY KEY (`KeyPartA`,`KeyPartB`)
) ENGINE=InnoDB;

Now to simulate AUTO_INCREMENT, I learned, it is possible to create the following trigger

DELIMITER $$

CREATE TRIGGER ExampleAutoIncrement BEFORE INSERT ON ExampleTable
FOR EACH ROW BEGIN
        SET NEW.KeyPartB = (
             SELECT IFNULL(MAX(KeyPartB), 0) + 1
             FROM ExampleTable
             WHERE KeyPartA  = NEW.KeyPartA
        );
END $$

DELIMITER ;

Now inserts work correctly, generating auto-incremented values. Unfortunately I can not think of a sensible way to get the newly created key value.

Neither LAST_INSERT_ID() nor its C counterpart return it, as there is no actual AUTO_INCREMENT column. Also it is not possible to set the value with LAST_INSERT_ID(IFNULL(MAX(KeyPartB), 0) + 1) inside the ExampleAutoIncrement trigger, because MySQL resets the value after the trigger finishes.

The only way I thought of was to issue a SELECT MAX(KeyPartB) FROM ExampleTable WHERE KeyPartA = ? after an insert, which defeats the whole purpose of the trigger. I.e. I could just as well do this before the insert and increment manually.

Is there any way to get the incremented key value from the trigger or some other means of simulating the AUTO_INCREMENT I want?

My solution is in migrating from myisam to innodb.  It goes something like:

  BEGIN;
  SELECT @id := IFNULL(MAX(id),0) + 1 FROM foo WHERE other = 123 FOR UPDATE;
  INSERT INTO foo
     (other, id, ...)
     VALUES
     (123, @id, ...);
  COMMIT;

Having a transaction is mandatory to prevent another thread from grabbing the same id.


Meaning of 'In current row format, BLOB prefix of 768 bytes is stored inline.' I encounter below issue on MySQL v5.6.16:

  Row size too large (> 8126). Changing some columns to TEXT or BLOB or
  using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current
  row format, BLOB prefix of 768 bytes is stored inline.

The corresponding table has several columns of type VARCHAR(4000).

I understand that here the record size exceeds the row size limit (8126 bytes), have also read 'Overview of InnoDB Row Storage'; but I do not understand what the following part of the error message means: In current row format, BLOB prefix of 768 bytes is stored inline.

When searching for this error, I see fixes being suggested but I'm interested on understanding what exactly causes this error. I would really appreciate your help in understanding the cause of this error.

Thank you.

If your ROW_FORMAT is REDUNDANT/COMPACT, the prefix (upto 768 bytes) of every blob/varchar/text is stored in the index records and remaining bytes are stored in Off-pages. So there is limitation on No of variable length columns in a table (upto 10 can be created in utf8 file format). Whether in DYNAMIC/COMPRESSED row formats, these fields are fully off-paged (prefix 0 byte). So the limitation is relaxed so that upto ~196 Text/Blob can be created in a single table.

Note :
Verify whether the table row format is DYNAMIC/COMPRESSED

  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESWHERENAME='dbName/tableName'\G

And verify FILE_FORMAT = Barracuda.


Windows authentication only I'm busy installing MSSQL 2016, and strongly considering using Windows Auth only. That can interfere with no local installation, but it has occurred to me that if another machine or process wanted to access this service, it would have to be granted a Windows User credential, that I can then admit to the database. Is this a real problem, and if so, what are steps I can take to solve it?

Using Windows authentication only is typically considered the preferred configuration for SQL Server.  Windows authentication uses a series of encrypted messages to authenticate users, whereas SQL Server logins and passwords are passed across the network, which makes them less secure.

Windows Authentication confers an advantage in that there is a single centralized location for controlling access.

If you have a user who no longer should have access to anything on your network (for instance, an employee leaves the job), you simply disable their account in Active Directory, and voila, the user no longer has access across the board, including any SQL Servers where they have a Windows Authentication account.

If you don't have Active Directory for authentication you would need to create an account on the machine where SQL Server is installed for each user who wants access to SQL Server.

See the following links for further details:

Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

Choose an Authentication Mode

This question shows how you can still use Windows authentication for an application/user from an untrusted domain.

This MSDN topic on Authentication in SQL Server via .Net has some more interesting details.


Index columns with arithmetic operators I commonly make this query on my database
SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;.

Is it possible to create indexes based on Column_a + Column_b < 10 to speed up this lookup. I see it is possible to do it for oracle based db over here (by doing CREATE INDEX Idx ON Example_tab(Column_a + Column_b);).But I can't find it anywhere for postgresql or mysql.

Is it even possible to do it on databases other than oracle ? If yes, is it documented somewhere, what are they called (basically, what should I google to find them) ?

Thanks in advance for any help.

PostgreSQL has indexes on expressions (since version 7 I think!), check the documentation: Indexes on Expressions.

So, you could easily add an index on (column_a + column_b), with almost exactly the same syntax as in Oracle. The only difference would be the extra parentheses around the expression:

CREATE INDEX idx
  ON Example_tab
  ((column_a + column_b)) ;

Note in case the above and the documentation isn't clear:
An expression can be as complicated as one wants and can include any number of operators or function calls.

MySQL does not have such indexes but they have a similar concept that can be used instead. MariaDB introduced VIRTUAL (computed) columns in their 5.2 version and the core MySQL just added them (called GENERATED) in their latest 5.7. See: Generated columns in MySQL.

So - in both MariaDB and MySQL - you can add a computed column and then an index on it. The syntax is similar but there are small differences. For MariaDB, the column can be either VIRTUAL or PERSISTED. If you want it indexed, it has to be PERSISTED:

ALTER TABLE Example_tab
   ADD COLUMN a_plus_b AS
       (column_a + column_b) PERSISTED,
   ADD INDEX idx
       (a_plus_b) ;

For MySQL the column can be defined as either VIRTUAL or STORED. If you want it indexed, for versions before 5.7.8, it has to be STORED. For versions 5.7.8+ in can be either stored or not (in which case, the computed values are only stored once, in the index). See details on Indexes on Virtual columns:

ALTER TABLE Example_tab
   ADD COLUMN a_plus_b AS
       (column_a + column_b),     -- optionally: STORED,
   ADD INDEX idx
       (a_plus_b) ;


DB design for employee working hours constraints I have a simple employee schedule database (I use PostgreSQL):

employees:
 - id
 - name
 - email

shifts:
- start
- stop
- employee_id

Now there is need to apply constraint for working hours. Employee can input when he/she is unavailable to work. It can be specific time period one time, or recurring once (er every 2,3...) days, weeks etc. The table looks like this:

constraints:
- start
- stop
- recurring (boolean)
- recurring_type (string)
- recurring_every_x (integer)
- employee_id

On my website i need to check quickly if employee is available for specific period. These are options I thought about:

Dynamic

When there is need to determine if employee can work within given time window we go through every instance of constraint connected with this employee, and calculate it. We have repeat this calculation every time.

Availabilities table

We create another table, which holds availabilities for each emplyee e.g. 1 year ahead, like this:

availabilities:
- start
- stop
- day
- employee_id

We fill data 1 year ahead with start=beginning of day, end=end of day, and when employee inputs new constraint we cut off time spans (removing some rows (days) from availabilities table or splitting them to two periods).

For example:

availability for 2015-05-05 is at beginning (day: 2015-05-05, start- 2015-05-05 00:00, end: 2015-05-05 23:59)

employee enters constraints "can't work 2015-05-05 10-15". We apply it:
(day: 2015-05-05, start- 2015-05-05 00:00, end: 2015-05-05 9:59)
(day: 2015-05-05, start- 2015-05-05 15:00, end: 2015-05-05 23:59)

This requires complex logic while new constraint is added. Also when removing constraint we have to remove all availabilities and recreate them, because constraints can overlap.

Which solution is best here, or maybe there is another way?

Use RFC 5545 RRules and ExRules to represent recurring events (days/times they can't work)

Use pg_rrule to turn recurring events into actual events. Use a materialized view.


Can I delegate SQL Agent rights without granting sysadmin rights? I'd like to give a user the ability to administer SQL Agent jobs.  According to the doc page, I can do this by granting the "appropriate" roles on msdb.  I've granted all three SQLAgent% roles, but the user gets this error when (as a test) trying to rename a job:

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
(Microsoft SQL Server, Error: 14525)

This question is similar, but no answer there addresses SQL Agent.  Is the sysadmin role the only solution, in MS SQL 2K8?

Unfortunately, administration of Agent jobs can currently only be done by members of the sysadmin role.  The relevant documentation is found on MSDN.  You'll note that the three Agent roles (User, Reader, and Operator) have different levels, but ultimately a user without sysadmin can only edit/manage jobs that user owns and not jobs created by other users.


What happens when one of the filegroup is full within SQL server Just a basic question due to my lack of understanding on how below works:

For one of the databases around 1 TB in size we have 8 file groups with multiple files added to them.

Now for one of the FG say FG1 there are 3 files :

FilesFG1a-has max growth restricted to 150 GB and 130 GB is used with 20 free
FilesFG1b-has max growth restricted to 150 GB and 150GB is used with 0 free
FilesFG1c-has max growth restricted to 150 GB and 150GB is used with 0 free

Now the operation will continue to work as we have 20 GB still out there on FilesFG1a but what happens when all 3 files are full within that FG1 file group?

I know we can increase the max growth restr. value as space is there on disk. But if not, will there be failures or operation tends to move data into mdf files instead of going to ndf files ?

Every table or index has at least one partition.  Each partition is stored on a single filegroup*.  So when that filegroup is full and none of its files can be expanded, any operation that needs to allocate additional space for a partition that is stored on that filegroup will fail.

*Except that a table can be configured to store its blob data on a separate database filegroup, or filesream filegroup.


Split mile limits to the thousandth based on ID I have a table like this in sql server 2014:

ID | Start_mile|End_mile
1  |5.23       |7.464
2  |2.333      |6.124

What I want to do is to split the miles into thousandths per ID like this:

ID |start_mile|end_mile
1  |5.230     |5.231
1  |5.231     |5.232
1  |5.232     |5.233
....
1  |7.463     |7.464
...

Any ideas as to how to go about doing this? I am trying to stay away from Cursors, unless that's the only way to do this.

I have been able to get this query together , but not sure how to incorporate it with the ID, and mile limits I have so that it runs for the whole table without the declared From and To variables:

DECLARE @from decimal(15, 3) = 0.980;
DECLARE @to decimal(15, 3) = 1.024;

;WITH cte AS
    (SELECT @from AS Value
     UNION ALL
     SELECT CONVERT(decimal(15, 3), Value + 0.001)
     FROM cte
     WHERE Value < @to)
SELECT *
FROM cte
option (maxrecursion 0)

If you don't want to use a recursive CTE you could use below query example:

SELECT m.ID,
       a.rn as start_mile,
       a.rn+ CONVERT(decimal(15, 3),0.001) as end_mile
FROM dbo.Miles m
CROSS APPLY
(
    SELECT TOP(CONVERT(int,(( end_mile -  Start_mile)* 1000)))
    CONVERT(decimal(15, 3),(Start_mile + (CONVERT(decimal(15, 3),ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1) / 1000))) as rn
    FROM master..spt_values spt1
    CROSS APPLY master..spt_values spt2
) as a;

This should work up until 6M miles for one id.

The spt values table could be replaced by a numbers table of some sort.

DB<>Fiddle


Sum every X rows with no common group by parent Trying to sum every X rows with no common group by parent

declare @temp table(Row_ID int, amt DECIMAL(18,2))
insert into @temp values(1, 20)
insert into @temp values(2, 24)
insert into @temp values(3, 20)
insert into @temp values(4, 15)
insert into @temp values(5, 15)

If X rows is 2 then desired result is to sum ever 2 rows and place total next to the rows. So first 2 rows total to 44 , next 2 rows to 35 , 1 remaining so total  15

Row_ID  amt     tot
1       20.00    44.00
2       24.00    44.00
3       20.00    35.00
4       15.00    35.00
5       15.00    15.00

If X rows is 3 then desired result

Row_ID  amt     tot
1       20.00    64.00
2       24.00    64.00
3       20.00    64.00
4       15.00    30.00
5       15.00    30.00

If X rows is 1 then desired result

Row_ID  amt     tot
1       20.00    20.00
2       24.00    24.00
3       20.00    20.00
4       15.00    15.00
5       15.00    15.00

One way is to divide the rows into groups/partitions - using simple math (integer division) and group over the partitions. I assume that the Row_ID values are UNIQUE.

If - unlikely but if - there are no gaps in the Row_ID values and they start from 1, then we could use the simple:

select row_id, amt,
       sum = sum(amt) over (partition by (row_id-1)/@GroupSize)
from t ;

But since this is unlikely - the values probably come from an IDENTITY column or a SEQUENCE, which don't guarantee gapless sequences - we have to create a gapless sequence, with ROW_NUMBER():

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / @GroupSize
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;

Tested in dbfiddle.uk:

create table t
(Row_ID int, amt DECIMAL(18,2)
) ;
GO

✓

insert into t values
  (1, 20),
  (2, 24),
  (3, 20),
  (4, 15),
  (5, 15) ;
GO

5 rows affected

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / 2
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;
GO

row_id | amt   | sum
-----: | :---- | :----
     1 | 20.00 | 44.00
     2 | 24.00 | 44.00
     3 | 20.00 | 35.00
     4 | 15.00 | 35.00
     5 | 15.00 | 15.00

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / 3
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;
GO

row_id | amt   | sum
-----: | :---- | :----
     1 | 20.00 | 64.00
     2 | 24.00 | 64.00
     3 | 20.00 | 64.00
     4 | 15.00 | 30.00
     5 | 15.00 | 30.00

In order to make the number of rows parametrisable, you just need to replace the divisor in the grp expression with a parameter/variable reference:

grp = (row_number() over (order by row_id) - 1) / @GroupSize

and, consequently, pass the parameter value when you invoke the query.


How is SQL Server host identity ensured? When I connect to some e-commerce website with my browser I use HTTPS that uses a certificate that (more or less) guarantees that example.com is indeed example.com.

Now I connect to a SQL Server somewhere on the Internet - it might be SQL Azure for example and then I just connect to a URL like abcdef.database.windows.net. There's a chance that there's a fake server there and then I expose my sensitive data. I haven't found any evidence that certificates or anything equivalent is used when connecting to SQL Server.

How is SQL Server host identity ensured?

I'm not familiar with Azure but I can speak to SQL Server in general. SQL has a property called Force Encryption (which is set through SQL's Configuration Manager), which as it sounds will force clients to connect over encrypted sessions. This is done with certificates just like SSL. This guarantees the authenticity of the server as well as providing for encrypted communications.

It's a bit nuanced - you can use a certificate issued by a CA that the clients trust or SQL can use a self signed certificate (less secure). There's also client side settings you can set in the connection string that will influence the connection - depending on ODBC or OLEDB the pertinent properties are something like "Encrypt" and "TrustServerCertificate".


Remove index fillfactor for Azure migration I'm deploying a SQL Server database to Azure. I've tried to export to a .bacpac, but receive an error:

  fill factor set and is not supported when as part of a data package

Using the script in this link I can locate where the fill factors indexes are.  I face 2 issues:

The index property page will not let me set the fill factor to 0. I have read that 100 is the same as 0. This does work.
I have too many indexes to do manually. Is there a script I can use that will reset this value to 0?

Deploy to an Azure V12 server, which does support FILLFACTOR, and use SSMS 2014 or later to perform the Deploy to Azure DB task.

Using SSMS 2014 or later is necessary to generate the correct script, and to perform the right checks. To be clear: SSMS 2014 can be used to deploy a database from an earlier version of SQL Server. SSMS 2012 and earlier fail with the error shown, even when deploying to Azure V12.

I just tested deploying a table with a FILLFACTOR of 95 from a SQL Server 2012 instance to an Azure V12 server using SSMS 2014 and it succeeded.

There are other options, including using SSDT and yes, dropping and recreating all your existing indexes, but this is making the task harder than it needs to be. You can find those other options in this SO answer.


case statement using characters for sum value I have a table that I am trying to pull a single return value on a select saying that either compressions on table is enabled or disabled by using a sum calculator for column value compressed must be No and auto_compressed must equal Disabled.

Here is example data in the table:

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       No
auto_compressed  Disabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname
collate          TBLSpace
dbsnum           1
compressed       No
auto_compressed  Disabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       No
auto_compressed  Enabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       Yes
auto_compressed  Disabled

You can use the below to test:

create table test (
partnum         varchar(255),
dbsname         varchar(255),
owner           varchar(255),
tabname         varchar(255),
collate         varchar(255),
dbsnum          varchar(255),
compressed      varchar(255),
auto_compressed varchar(255));

insert into test values ('1048577','rootdbs','informix','','TBLSpace','1','No','Disabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','No','Disabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','No','Enabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','Yes','Disabled');

I only want to determine at the end of the day if compressed = No or auto_compressed = Disabled for all records in the database then I want a single return saying disabled or enabled.

I put this together, but I am struggling to replace numbers with values:

select
sum(case when Compressed != 'No' OR auto_compressed != 'Disabled' then 'Enabled' else 'Disabled' end) as Condition
from test

I get this error message below:

367: Sums and averages cannot be computed for character columns.

I can replace the values with numbers and it works like below:

select
sum(case when Compressed != 'No' OR auto_compressed != 'Disabled' then 1 else 0 end) as seven
from test

I believe my case statement is incorrect or there is a better way of doing this. I do need assistance as my mind is boggling at this point.

Test

SELECT CASE SUM(CASE WHEN Compressed = 'Yes' AND auto_compressed = 'Compressed'
                     THEN 1
                     ELSE 0
                     END)
       WHEN 0 THEN 'Disabled'
       ELSE        'Enabled'
       END AS Condition
FROM test


Getting Error 10054 after renaming the server When I open SQL Server I have error 10054. Recently I changed the server name and I probably have this problem since. How can I solve it?

See the Microsoft Docs article Rename a Computer that Hosts a Stand-Alone Instance of SQL Server.

You'll need to restart the SQL Server service, and use sp_dropserver / sp_addserver to update the server name within SQL Server.


The state of the MSSQL Options set during query after it ends I am running a transactional query in SQL Server, and I want to turn XACT_ABORT on, so I write: SET XACT_ABORT ON.

First, in such case, would I have overwritten other settings done by another query?  I mean, are those options scoped at the query level or at the connection level? Perhaps at the server level?

If you execute an explicit ad-hoc SET XACT_ABORT ON, the setting remains in effect for the duration of the current session. Other session settings will remain unchanged.

In the case of SET XACT_ABORT within a module (e.g. stored procedure or trigger), the setting is effective only within the scope of module execution and reverts to the original session setting afterwards.

If the application doesn't explicitly execute SET XACT_ABORT ON or SET XACT_ABORT OFF, the server user options configuration value may specify SET XACT_ABORT ON as the server-wide default by turning on bit 16384. The default configuration value is 0.

The SET Statements documentation describes general behavior of SET statements. Other settings (not including XACT_ABORT) can be turned ON (but not OFF) at the database level to determine default session settings for that session database context.


How can I return the full text of a document indexed in SQL Server Full-Text? I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search.  Now I'd like to run some additional processing on the text of those documents, but don't see a reason to have the pipeline redo the work of decoding, extracting, etc. the text from the files.

It seems there should be an obvious solution ... but I've been running in circles without any luck.

So the question is:

How can I query to return the full plaintext of a file in T-SQL?
If that's not possible, can it be done in SSIS or SSAS after the
normal FTS parser has run?
If that's not possible, is there a way to hook into the FTS pipeline
(via a trigger perhaps) so I can split the plain text into another
table?

Alternate solutions are appreciated as well if you've got good examples for me to reference.  The only immediate idea I had was to use a different network share for dropoff, have SSIS pick up the file and extract the text (no idea how to do that), and then to move the file + text to SQL server ... but that seems wonky for a lot of reasons.

[Edited to clarify "why"]

If SQL Server has already pulled out the text in order to chunk it & do the base NLP for the semantic index ... I'd rather use that than reinvent the wheel.  Specific uses I'm looking into are post-processing with other NLP utilities (e.g. NLTK, GenSim, Stanford NLP NER, etc.) so that I can generate extractive document summarizations, store n-gram statistics for my corpus, and include NER for more effective faceted search.

If I have to extract the text out of files before storing them in SQL Server (either using SSIS/.NET so I can keep the iFilters OR by using a different tool altogether) there's limited usefulness in SQL Server's ability to perform that work on FileTables for anything but the most basic tasks.

Consider the number of document formats already supported - and it's a major task to recreate the feature.  Similarly, having to go back to the actual file afterwards and redo that indexing work is inefficient and it'd seem sensible to disable FTS on FileTables, skip using them altogether, or scrap SQL Server for document-based FTS altogether.

I am curious about what you will accomplish by getting the plain text.  You already have the documents in the FileTable and you can open the files when needed using the appropriate tools.

For example: If you are looking at a PDF, a Word document, an Excel spreadsheet, and so forth you probably have the tools to look at the data. Most tools will even allow you to save the 'plain text' manually. But I suppose it depends on how you define "plain text".  Save of Word document to a non-Unicode .TXT file?  Or something more complex?

Of course, I realize that you would likely want a more automated approach, which I comment on further down.

One thing to know is that not all files will necessarily copy to "plain text". (At least I had trouble with plain text from Chinese.)

Regarding the Full Text Indexes:

Although the SQL Server Full Text Index is aware of the relative positions of 'words' in the index so that it can search for phrases, it has no interface to reveal the serial sequence of words in the text. Thus, there is no way (currently) to build plain text from the Full Text Index.

Even if those details were available, the Full Text Index still does not have everything needed to fully represent a document.

If you have any Stop Words configured, then they will not appear in the Full Text Index.
Punctuation is generally not included except when tokenized within a word, such as "pre-apocalypse", "F.B.I." and so forth.
Formatting is lost.
The language being used for Full Text Indexing may affect the results.

The Full Text Indexes are solving a different problem from creating plain text. However, my experience is that large text bases usually benefit from Full Text Indexing anyway, since someone is always trying to find something

I understand that once you get the text you will redundantly store it in a SQL Server table so as to expedite your use of the plain text data.

Automating the Extract and Load

This will require some work.  But, since your documents are currently in a FileTable you should be able to access them from the file system using file tools. The "off-topic" answer to plain text extraction below includes several tools that are being used by others. Perhaps some of these tools would be useful to you.

http://stackoverflow.com/questions/5671988/how-to-extract-just-plain-text-from-doc-docx-files-unix

Since you are using SQL Server 2014, once you have plain text files you should be able to import them in a number of ways: BCP, SSIS, and other tools are available to load data.

Depending on your approach, you might choose to use a staging table to further prepare the results before moving the data into the destination table. And if you are also doing document versioning you will likely need to create some metadata that allows you to track different versions.


Dynamic Pivot with overlapping years I have looked and cannot find how to show the last # months in a pivot table.  Everything I seem to run across codes months statically.  I'm seeking a solution that shows something similar to the below result:

The columns headers are a unique identifier for the month(year*12+monthnum).  This was the only way I could think of for progressing year and month in a linear manner.

 Partnumber 24180   24179   24178   24177   24176

 813453     0       2       4       6       3

Is this possible?  Am I thinking too hard?  Each month the columns would naturally shift, and this is where I can't figure out how to dynamically do this.

Any suggestions or assistance is appreciated.

The table holding the data looks like this:  Of course the actual table hold much more historical data for thousands of parts.

PN      Year    Month   Usage   UID
813453  2014    12      0       24180
813453  2014    11      2       24179
813453  2014    10      4       24178
813453  2014    9       6       24177
813453  2014    8       3       24176

You can use PIVOT with dynamic sql to solve your problem. Below is a simple implementation to solve the issue. The query will support any number of months you may have. Make sure the length of @pivcols is large enough to accommodate the maximum anticipated string length for all the current and future months (povot columns) concatenated.

--Table structure--

Create table #T
(
     PN int
    ,Year int
    ,Month int
    ,Usage int
    ,UID int
);

Insert into #T values(813453,  2014,    12,      0,       24180)
Insert into #T values(813453,  2014,    11,      2,       24179)
Insert into #T values(813453,  2014,    10,      4,       24178)
Insert into #T values(813453,  2014,    9,      6,       24177)
Insert into #T values(813453,  2014,    8,      3,       24176);

--Dynamic pivot--

declare @pivcols as varchar(800);

select  @pivcols = stuff((select '],[' + cast((Year*12 + Month) as varchar(10))
from #T order by Year*12 + Month desc
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500);

set @pivquery = 'Select PN, '+
@pivcols +'
from
(select PN, Usage,
 cast((Year*12 + Month) as varchar(10)) rnk
 from #T
) as d
PIVOT
(
max(Usage)
for rnk in ( '+ @pivcols +' )
) AS pvt ';

EXECUTE(@pivquery);

--Results--

PN      24180   24179   24178   24177   24176
813453  0       2       4       6       3


Alter Index within a Script I've been reading about SQL Server Index Fragmentation and how to rebuild/reorganize. I'm curious on how to write a script about it. I have a script that will generate the table with the table index and display the fragmentation in percent:

SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
ORDER BY
    indexstats.avg_fragmentation_in_percent desc

Can I add ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD to this script or do I have to write a new script.

I found http://sqlfool.com/2011/06/index-defrag-script-v4-1/ but am not sure on how I use it properly.

I'm new to database administration and am trying to setup a maintenance plan that will rebuild indexes when they hit a certain threshold.

If you're new to the DBA role then go to this site:

OLA

What you're trying to do has already been done.

Any DBA should have this site on their list of favourites.


MySQL replication without baseline data import I've been reading about MySQL replication and all the resources I've looked at say that a data import (be it a mysqldump, or a raw data dump import) is needed before replication can start. I'm trying to understand why is that. Would it be possible to start replication from a master, where binary logs were saved from the beginning, by telling the slave to replicate from log position 1 (not sure if it's supposed to be 1 or 0 also).

Thanks in advance

If the binary logs contain absolutely everything that created that master (including grants, create database, create table, INSERTs, UPDATEs, and DELETEs), then yes it is possible.

The log position you need is not 0 or 1. It is 4 (See my post Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB)


Hide Table Structure Is there a way to restrict a user from viewing the table structure? I just want them to have a SELECT, INSERT, UPDATE, DELETE access only on that table.  I want to restrict a specific programmer on the following:
    1.  He is not allowed to see any table definition
    2.  He is not allowed to create Stored Procedure
    3.  Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.
    Overall, for security reason, we don't want that programmer to have any of design of the database.

This seems a strange request but it is possible.

GRANT SELECT ON YourTable TO FOO;
GRANT INSERT ON YourTable TO FOO;
GRANT UPDATE ON YourTable TO FOO;
GRANT DELETE ON YourTable TO FOO;
DENY VIEW DEFINITION ON YourTable TO FOO;

They will of course be able to see the names of the columns by issuing a SELECT * and be able to get the datatypes with a bit more effort by select into a new (possibly #temp) table. They will lose intellisense on that table.

You also say

  Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.

In that case probably they don't need any permissions at all on the table. If the table and stored procedures have the same owner you just need to grant exec permissions on the procedure and the access to the table in the procedure will still succeed through ownership chaining.


Order table by two columns I would like to order this table but I can not find a way to make it work. Can you help me?

I have this table:

I need this:

I have a table where documents are listed, the first field is the id of the document and the second field represents the father, so I must show a list where you can see in an orderly manner that 242 is the first document and 252 and 335 were generated from the 242.

The query:

select * from table_name order by col1 ASC, Col2 ASC

...doesn't work for me. I do this query:

SELECT
    FR1.[report_id],
    FR1.[report_parent]
FROM [FARA_reports] FR1
WHERE
    FR1.[report_is_delete] <> 1
    AND FR1.[report_is_tmp] <> 1
ORDER BY
    FR1.[report_id] asc,
    FR1.[report_parent] desc

and this is my first image.

Your data:

CREATE TABLE dbo.WEIRD_ORDERING
(
ID1 INT NULL,
ID2 INT NULL
);

INSERT INTO dbo.WEIRD_ORDERING VALUES
(242, NULL),
(243, NULL),
(244, NULL),
(252, 242),
(254, NULL),
(255, NULL),
(256, NULL),
(292, NULL),
(308, NULL),
(311, NULL),
(313, 311),
(314, 311),
(323, NULL),
(324, 311),
(335, 242),
(340, NULL),
(341, NULL),
(358, NULL),
(372, NULL),
(373, NULL),
(377, NULL),
(378, 358),
(379, 358),
(380, 358),
(381, 358);

This gives you results in the desired order:

SELECT ID1, ID2
FROM
(
    SELECT ID1, ID2, ID1 AS ORDERING_COLUMN
    FROM dbo.WEIRD_ORDERING
    WHERE ID1 <= ID2 OR ID2 IS NULL

    UNION ALL

    SELECT ID1, ID2, ID2 AS ORDERING_COLUMN
    FROM dbo.WEIRD_ORDERING
    WHERE ID1 > ID2 OR ID1 IS NULL
) q
ORDER BY ORDERING_COLUMN, ID1;

db fiddle