Insert/Update SQL Server Stored Procedure I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and managed to create a stored procedure using MERGE.  The table looks likes this,

bl   em  number  title email fl name opt

null 101 00659   ABC   null  01 John null

null 102 00660   DEF   null  02 Rosy null

And the procedure:

CREATE PROCEDURE [dbo].[uspInsUpd]
    @bl         char(8),
    @em         char(35),
    @number     char(12),
    @title      varchar(64),
    @email      varchar(50),
    @fl         char(4),
    @name       char(32),
    @opt        char(16),

AS

MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
   AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt])
ON [Target].[em] = [Source].[em]

WHEN MATCHED THEN
     UPDATE SET [bl]      = [Source].[bl],
                [em]      = [Source].[em],
                [number]  = [Source].[number],
                [title]   = [Source].[title],
                [email]   = [Source].[email],
                [fl]      = [Source].[fl],
                [name]    = [Source].[name],
                [opt]     = [Source].[opt],

WHEN NOT MATCHED THEN
     INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
     VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right?

As a very generic answer, if you want to test code of this nature without actually changing the data then it is possible to run your code, query the results, and then undo any changes before they are committed.

-- Begin a new transaction
begin transaction

    -- Show initial data
    select * from [dbo].[employee]

    -- Your statement to change the data
    MERGE [dbo].[employee] AS [Target]
    ....
    ....
    ....

    -- Show the new data
    select * from [dbo].[employee]

-- Rollback the transaction, leaving the data in its original state.
rollback transaction


Indexed view not being used - SQL Server I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and index.

I want to leverage on the advantage of indexed view, wherein if I've a similar query or the exact same query somewhere else, the query optimizer chooses the indexed view so that performance is improved. I tried using the exact same query that was used to create index (with an additional date filter) but query plan doesn't seem to change. Index on the view is still not used even if I explicitly use the view in the query. I could only get the query optimizer to use the index with noexpand hint. Note that I'm using SQL Server enterprise edition.

Any suggestions?

This happens often if the optimiser thinks it can do better with the base tables.

If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it.

Example:

SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
SET STATISTICS TIME ON;
SELECT * FROM MySchema.myIndexedView ACB WITH (NOEXPAND) WHERE ...;
SELECT * FROM MySchema.myIndexedView ACB WHERE ...;

Note that depending on version, you may suffer from predicate pushing issues.

Note that this answer is generic because we don't have code and queries to look at


Tips on using SQL access advisor in Oracle Please share how to use oracle SQL access adviser for single or multiple queries. you can assume that we know the sql ids for the queries we are interested in.

something like:

declare
task varchar2(30);

begin
task:= dbms_sqltune.CREATE_TUNING_TASK ( sql_id => '&sql_id',
plan_hash_value => '&hv' );
dbms_output.put_line (task);
dbms_sqltune.execute_tuning_task(task);
end;
/

to get the job running.
List the tasks:

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
where TRUNC(created) = TRUNC(sysdate)
order by CREATEd desc;

when it is ready, read the report

SELECT DBMS_SQLTUNE.report_tuning_task('&tuning_task') AS recommendations FROM dual;

and if you want, implement recommendations:

DECLARE
-- accept plan profile
  l_sql_tune_task_id  VARCHAR2(20);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                          task_name => '&tuning_task',
                          name      => '&profile');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Reading the packages can help a lot.

For the SQL access advisor take a look at SQL Access Advisor in Oracle Database


SQL dump loads with Error 1064 Looks like this issue is just the wrong version of MySQL, need to go to 5.5

So I have a backup of an offsite source SQL database.  I am not sure how they are preforming the data dump but I am trying to find out.  This database needs to be imported on the new computer.  Every time I try to run the commands below I get the error (at the bottom of the post).  I want to be able to load this data with out an error.  What do I need to do to get the data to load correctly?  So far none of the tables load at all from the dump.

mysql -u username -p database_name < file.sql

I get the error 1064.  I also tried to load this in MySQL with

source \home\user\Desktop\test.sql;

MySQL v12.22 Distrib 4.0.26-nt-max, for Win32 (ia32)
Protocol
version, 10
Client characterset, latin1
Server characterset, latin1
Windows XP SP3
InnoDB is coming up at the type in show create table tablename;

  ERROR 1064: You have an error in your SQL syntax.  Check the manual
  that corresponds to your MySQL version for the right syntax to use
  near 'DEFAULT CHARSET=latin1' at line 1

when trying to add a different table

  ERROR 1074: Too big column length for column 'City' .  Use BLOB instead

ok I kept messing with this, the following code works when I remove, DEFAULT CHATSET=latin1

CREATE TABLE `aaaa` (`MYID` int(11) DEFAULT NULL) ENGINE=MyISAM;

In the commandline tool, use forwardslash, not backslash, for path delimiters:

source /home/user/Desktop/test.sql;


Is splitting a CSV column into a separate table (enforcing 1NF) unnecessary complication? I had very carefully asked the table designer why he had chosen to create CSV columns in multiple tables.

The designer's answer was that splitting CSV columns into separate tables was "unnecessarily complicated".

One CSV column is used in an implicit join to count how many times a value in the multitude of values in the CSV column has been used.

The where cause that binds the two table uses a like % | table_b.csv_column | % operation.

I read and learnt that normalisation is not always required if there is a good reason not to.
However, I don't know what could be good reasons for not normalising.

Is this (avoiding "complication") a good reason for not enforcing first normal form (1NF)?

TABLE: TB_DISASTER_CAUSE_CATE

CATEGORY_ID   CATEGORY_N CATEGORY_L L
------------- ---------- ---------- -
DC006001002   ****                3 Y
DC006001003   ****                3 Y
DC006002001   ****                3 Y
DC007001001   ****                3 Y
DC007002001   ****                3 Y
DC007002002   ****                3 Y
DC007003001   ****                3 Y
DC007003002   ****                3 Y
DC007003003   ****                3 Y

TABLE: TB_DISASTER_HISTORY

SEQ DISASTER_TYPE DISASTER_CAUSE
----- ------------- ------------------------------------------------------------
   32 DT001003002   DC001001004|DC002001002|DC003001001|DC007002001
   33 DT002003007   DC001001004|DC007002001
   34 DT002003009   DC001001003|DC003001002|DC007002001
   16 DT002003001   DC001001004|DC002001002|DC005001003|DC007002001
   17 DT002003001   DC001001004|DC002001002|DC005001003|DC006001003|DC007002001
   18 DT002003007   DC001001003|DC001002002|DC002001001|DC002002002|DC004001001|
                    DC007002001

   19 DT002003007   DC001001003|DC001001004|DC003001002|DC007002001
   20 DT002003007   DC001001003|DC001001004|DC007002001
   21 DT002003007   DC001001003|DC003004001|DC007002001

  SEQ DISASTER_TYPE DISASTER_CAUSE
----- ------------- ------------------------------------------------------------
   22 DT002003007   DC001001003|DC001001004|DC002001002|DC007002001

QUERY

select
          x.category_id as category_id
          ,x.cnt as ccnt
          ,y.category_nm as category_nm
        from(

        select category_id , count(*) cnt from
            TB_DISASTER_CAUSE_CATE  a ,
            tb_disaster_history b

           where last_yn  = 'Y'
             and b.disaster_cause like '%'||a.category_id||'%'
             and b.disaster_type=#disaster_type#
         group by category_id  ) x

         inner join (
         select * from tb_disaster_cause_cate
         where last_yn='Y') y
         on x.category_id = y.category_id;

The answer to this question is the same as the answers to this question.  Both are really about first normal form (1NF).  The linked question had to do with justification for a key column made up of different data elements concatenated via a dash "-".  This question has to do with justification for a non-key column made up of different values of the same data element contented via a pipe "|".  To answer, we must first assess if such a design violates the intent of 1NF, and second, if so, when would it be justified to make that violation of 1NF anyway.
Background
First, a quick re-cap of what constitutes 1NF.  A table is a relational (R-Table) table, and thus normalized (meaning in 1NF by definition) if in its design a discipline is followed that ensures:

Distinct, un-ordered rows
Uniquely named, un-ordered columns
Each column contains a single value from the corresponding domain

Note requirement 3 implies no column can have a missing value (or a NULL marker).  Let us assume for the moment that requirements 1 and 2, and the "no missing value" part of requirement 3, are met.  I say "assume" because we really cannot assess compliance without seeing all the rows in the table to verify there are no missing values, no duplicate rows, and no implicit information encoded in the ordering of columns or rows.  Now if the schema included integrity constraints which implement the business rules from the conceptual model to guarantee no duplicate rows and no missing values, we would not have to examine the all the data to verify compliance with respect to those requirements as we know the DBMS will enforce consistency with those rules in every case.  We would still have to examine all the data however to ensure row or column ordering is not being used to implicitly encode additional information.
Given the assumptions just set forth, we have left to determine if the placement of multiple disaster cause codes into a single column separated by pipes violates requirement 3 for single value columns.  In my answer to the previous question about a key column with multiple data elements separated by a dash I said such a design did violate that requirement, and that under no circumstances would it be justified.  My answer is based upon study of the most current and definitive thinking on 1NF from Fabian Pascal and Chris Date (references to which I will provide at the end of the answer).  Fabian himself contributed to answering that question with a correction to my answer.  He said my assertion was too strict, and that:

If the combination is just for visual purposes and it is guaranteed that it will always be accessed by the DBMS as a single value (emphasis mine here).  The problem is that such guarantees are shaky.

Fabian's correction strikes at the heart of the subtlety of the single valued column requirement.  If that column's domain is a relation-valued domain (RVD), and a single table thus contained in that column, it would not be a violation of 1NF.  Before we rejoice in the ability to now justify the practice of your "senior" dev in creating columns with multiple data elements separated by dashes or pipes or whatever else, I want to first point out that no SQL DBMS actually supports RVDs, thus making operating on the column as a single value difficult.  Second, both Date and Pascal point out that while an RVD is not a violation of 1NF, it does introduce additional complexity and asymmetry without (except in a few extreme circumstances) any additional benefit.  So while it may be permissible, it may well not be a good idea.  Please refer to the references for the details on this.
Evaluating the Design
With the background on 1NF in place, let's get down to this situation.  The column in question, disaster cause, I would submit is not a single value column precisely because you give an example query where individual values within the larger string are accessed.  Therefore the table is not normalized.  Given this, is it still justified to design the table in a such a way as to make it not an R-Table simply because the normalized design calls for two tables and is thus perceived to be "unnecessarily complicated?"  I would say absolutely not for two reasons.  First, Einstein said things should be as simple as possible, but no simpler.  This un-normalized design is an example of "too simple" as by having a table that is not an R-Table none of the benefits of an R-Table can be gained.  The references go into detail on this point, but the main point is that guaranteed logical access to each data element is lost.  A more subtle issue is that maintaining the integrity of the disaster cause is much more difficult.  If the disaster cause were a child table, ensuring each disaster cause code can only be assigned once per disaster is guaranteed by the key constraint.  As a multi-value column in the disaster history table an application could easily accidentally concatenate the same cause code twice.  It makes my head hurt even contemplating how to write the procedural trigger that would be necessary to implement this constraint on the multi-value column.  Finally, note I have addressed only the logical design.  I am sure there are many experts in the physical implementation of various SQL DBMS' who would cringe at the resulting optimization of the queries that will be run, like the one provided, to access this data.
Lastly, I would argue that even the rationale that a normalized design with two tables is "unnecessarily complicated" is wrong.  Instead, the multi-value column in the single table is in my opinion "unnecessarily complicated."  The query you provided, which is trying to count up the number of times a given disaster cause contributed to a disaster, is much more complicated to write than it would be if the design simply had a disaster table and a child disaster cause table.  The nesting would be unnecessary and the join would be simple and obvious as to its purpose.  Someone looking at the schema for the first time can easily understand, without even seeing the data, that we have information about disasters, and each disaster can have many causes.  With the multi-value column this simple structure is obscured and the first time user would have to dig into the data values to discover that in fact a disaster can have many causes.
References
Fabian Pascal's Practical Database Foundation Series.  Paper #5 addresses 1NF and RVDs.  If you purchase all the papers (which you really should as they are a complementary set addressing all the fundamentals) two additional papers can be obtained that specifically address 1NF - "What First Normal Form Really Means" by CJ Date, and "What First Normal Form Means NOT" by Fabian Pascal.  Next, CJ Date has written extensively on relational theory.  The most current reference with respect to normalization is Database Design and Relational Theory:  Normal Forms and All That Jazz.  Finally, Fabian Pascal has blogged recently about 1NF and those blogs are excellent reading.


How can I check if my user has CREATE SCHEMA permission? I have a user account in a database that can create tables and stored procedures.
I would like to confirm if this user account can create schemas - without trying to do it first.  (I am part of finance not IT.)
If I can create schemas, then I will ask IT for permission to go ahead and do so.
If I can't then I will just continue to wait for IT to do it on the Service Desk request I put in some time ago (sigh).
Is there a command I can run in SSMS that will tell me this without actually creating a new schema?
I have also taken a couple of screenshots of this user's permissions - maybe they will have the answer?

EDIT:
Having run the SQL suggested by SQLServing below, I get this result:
UserName    is_disabled principal_type_desc class_desc  object_name permission_name permission_state_desc
DWReports   0   SQL_USER    DATABASE    NULL    CONNECT GRANT
DWReports   0   SQL_USER    OBJECT_OR_COLUMN    GetSystemSQLAgentJobList    EXECUTE GRANT

It looks to me like I will not be able to create this schema, is that
  right?

No, it's wrong.
You can create all you want in your database because you are db_owner. You can see it looking at your first picture with membership.

The error in the answer above is that it consider only explicitly granted permissions for given user. But the user may be a member of 1000 roles, and the permissions can be given to these roles.

In your case you are a member of fixed database role db_ownerthat has control on database, so you can do anything within your database.

To see your effective permissions in your database execute this:

select * from sys.fn_my_permissions(null, 'database');


Importing data into Oracle 12 I got a set of files from a client which are, as far as I can tell, copies of a database in Oracle 11.

Each folder has the following structure:

- init<DB>.ora
- orapw<DB>
- arch
  - dbf or dbf.gz files
- bkup
  - gz files

How can I import these into Oracle 12? Or should I try installing Oracle 11?

I'm new to Oracle, please excuse me.

What you're asking here is essentially a crash-course in being a DBA. I'll try to explain this by sequentially explaining how the Oracle Database software comes to life at startup.

An "instance" is the Oracle software running in memory, before it touches any files. It goes away when the power is switched off.

init<DB>.ora is the instance parameter file. (This is a human-readable PFILE, which is one of two kinds of parameter file. The other is a binary SPFILE.) This is what defines the parameters to start the Oracle Database software in memory, such as how much memory to use. Yours may not look like theirs, and it doesn't have to. The most important parameters here are the database name and the location(s) of the controlfile(s).

After the instance starts and allocates memory, it then "mounts" a controlfile. This defines the other files that are available, the state/version level of transactions in the system, and other extremely high-level metadata. Note that we're still not up to the part that considers the user data in the database. Like an SPFILE, a controlfile is stored in a binary format, but also like a PFILE, the controlfile can be backed up "to trace" to generate a human-readable means of recreating it. Note that only the binary version can actually be used.

The parameter file defines the instance, and the controlfile defines the database.

A "database" is the collection of files on the disk. This is where the business data and the logical metadata is stored. After the instance mounts the controlfile, it reads the controlfile and "opens" the database. If it's missing any data files--or they fail consistency checks--it won't open.

The challenging part of answering this properly is that both the instance parameter file and the controlfile(s) will likely need to be recreated on your end if you're going to have any luck accomplishing what you'd like to do.

This is not unlike duplicating a production database to a test environment, detailed here: http://www.dba-oracle.com/oracle_tips_db_copy.htm


How does MySQL resolve schema information? I’ve used MySQL on windows7.

Recently my machine was infected by a virus.

After that SHOW TABLES and SHOW DATABASES statements don’t show any tables and databases but I can see data from tables which I’ve created with SELECT statement.

In addition mysqldump doesn’t work. It makes dump file but there is no data.

mysqldump can’t resolve database and table names.

I think SHOW statements and mysqldump use information_schema table.

Then my question is how does MySQL resolve schema information when it create information_schema table?

There must be some resource that information_schema refer to.

I believe the resource was destroyed by the virus,and I want to fix it.

I found a cause.
All folders under the data directory were hidden attribute.
I changed them then I can see tables and dump data.


Obvious reason Postgres Users can't read a table? Situation: a PG user (non superuser, inherits from parent role) that is a member of a Role/Group cannot read from specific tables even though these Object Privileges have been specified:

DBName - Connect
SELECT - true
INSERT - true
Delete - true
UPDATE - true

I can't figure out what the tablename_tablename_id_seq object does-- there is a 1:1 relationship between all my DB's tables and sequences but not sure how this impacts permissioning.

I tried clicking the checkbox to True (In Navicat) for "Usage" but the user still cannot read from the specified table.

I have attempted to edit these permissions in PG Admin 3&4, Navicat, and Postico...any idea where I am getting stuck?

Connect to the respective database by \c DATABASE_NAME
It should have the Connect permission: GRANT CONNECT ON DATABASE DATABASE_NAME TO USER_NAME;
Grant the permission to use the schema GRANT USAGE ON SCHEMA public TO USER_NAME;
Grant permission to select on all the tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER_NAME;


How to reduce the required frequency of ANALYSE on a postgres table? I have a Postgres database into which about 20 million rows are inserted every day.

The records are inserted via a powershell script that calculates the average time taken to insert each record. It typically takes about 1.5ms per record (including some record parsing and a trigger).

Every now and again (typically after about 3 million rows are added) the processing time jumps up to about 12ms/record (it's a very noticeable step change that happens very quickly)

I've already found the cure. I stop the process that adds the records, do an ANALYSE on the table, and restart the process. The insert time goes back to 1.5ms/record. Although this only take 5-10 minutes, I do end up loosing several thousand records during this downtime.

The database is already shutdown for a brief period at night and all the tables get ANALYSEd regularly, but is there any way I avoid doing additional ANALYSEs during the day?

(note - I've tried simply stopping and restart the INSERT process - it makes no difference. It's only stopping, doing an ANALYSE and then restarting that makes a difference)

Try to use Analyze without stopping the process. I think you already tried this, wny is this not working ?
One option is to Analyze only specific columns , this will take less than analyzing the whole table.
Usually autovacuum is active on a postgres database, having this enabled is allowing the db to to the Analyze in the background.

Check :
https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM

This is the formula when an analyze is run :

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

The params are here :

https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD

https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR

The values can be set on the table level , no need to have the same rule for all tables.

ALTER TABLE a_lot_of_inserts SET ( autovacuum_analyze_threshold = 50 , autautovacuum_analyze_scale_factor = 0.01 )

Try to play with them until you find the right values.


Why is casting a string to DATETIME not accurate to the ms? Why does

SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME)

return:

2014-11-28 15:06:02.167

DateTime has a precision of has a  0.00333(1/300th) seconds, so sql server rounds value to nearest 1/300th.

If you are using SQL Server 2008 above, use DateTime2 which can have a precision of millisecond, even down to 100 nanoseconds and can hold the accurate value.

SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME2(3));


Filtered Index hint rejected by SQL Server I have a filtered non-clustered index that SQL Server is not using. I'm pretty sure the optimizer is doing the right choice but I would like to force it to run with that index so I can compare the plan and see why it's more expensive.

I've removed everything from the query and I'm just selecting the column that is being filtered.

Index definition:

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

The query I'm trying to run is:

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT';

And SQL Server is returning the following 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.

I've read a lot about the column filtered being included either in the key or as include column but nothing helps.

I can reproduce this in a database with PARAMETERIZATION FORCED.

CREATE DATABASE D1

ALTER DATABASE D1 SET PARAMETERIZATION FORCED

GO

USE D1

CREATE TABLE tbl_test
(
CommentDateTime DATETIME,
name VARCHAR(50),
comment VARCHAR(50),
CommentByType VARCHAR(10)
);

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT'
--OPTION (RECOMPILE)
;

The literal then gets parameterised and it is no longer guaranteed that the filtered index will match

where CommentByType = @0

In which case OPTION (RECOMPILE) allows the hint to succeed.


Failover from Primary to Asyn Secondary Replica In my environment we have 1 Primary 1 Synchornous Secondary  and 1 Asynchronous Secondary Replica (Off site). We are planning to do a manual failover to the Asynchronous Secondary Replica (Off site) as the Primary data center will be down due to maintenance.

My question is will there be any problem if I manually change the Availability Mode to Synchronous mode from Asynchronous mode and do the failover

No. It is not a problem.

Just to it in a low intensive moment. And waith sync is complete. You can check it via interface.

Otherwise you can use this query (checked in SQL 2016):

SELECT ag.NAME
    ,drs.database_id
    ,drs.group_id
    ,drs.replica_id
    ,drs.synchronization_state_desc
    ,drs.end_of_log_lsn
FROM sys.dm_hadr_database_replica_states drs
    ,sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

synchronization_state_desc must be: SYNCHRONIZED.

Then you can switch.


CLOB / VARCHAR(MAX) is causing slow select query I have a transaction table where I am storing buy/sell transactions related information, and this information I am getting is from MQ as an XML and I am also storing XML String as VARCHAR(MAX) in SQL Server database.

The issue is that since I am storing XML String into this table, select query is running very slow.

Is there any way I can increase the performance? Any recommendation will be highly appreciated.

The solution which I can think of is to create another table of 1 -1 mapping with my transaction table to store XML String but not really sure if this is the best idea.

If you can adjust the datatype of the column to XML and your table has a clustered primary key, you can potentially improve performance, but there are a few fundamental changes you'll need to ensure occur.

Ensure your table is Clustered on the Primary Key.  Your table must be clustered and the cluster key must also be your Primary Key.
Strongly type your column as XML.  Storing XML as character data eliminates any possibility to create an XML Index(es) on the column.

If you can satisfy these requirements, you can then take advantage of XML Indexes.  I'm not going to go into the details of how these work or how you set them up, as you can easily find articles online that discuss this in more detail (ref1, ref2, ref3), but used properly these can dramatically improve performance of queries that have to interact with XML data.


PostgreSQL event trigger to force extension installations into particular schema I have a dedicated schema extensions (PostgreSQL v 9.6.3) where I intend to install all extensions for my DB. I would like to somehow disallow (or at least remind) users installing extensions without specify particular schema.

So this should fail

CREATE EXTENSION tablefunc;

but this should succeed:

CREATE EXTENSION tablefunc SCHEMA extensions;

For this purpose, I tried creating event trigger:

CREATE EVENT TRIGGER e010_verify_extension_schema_et
  ON ddl_command_end
  WHEN TAG IN ('CREATE EXTENSION')
  EXECUTE PROCEDURE verify_extension_schema();

and in verify_extension_schema() function I am trying to make use of pg_catalog.pg_event_trigger_ddl_commands().
However, the function does not return anything I can use to check if schema has been specified or which schema for that matter...

Does anyone have any ideas how I could accomplish this?

Make sure you're using ddl_command_end, it can still fail or remind users which is what you want. From the docs,

  Similarly, if a ddl_command_end trigger fails with an error, the effects of the DDL statement will be rolled back, just as they would be in any other case where the containing transaction aborts.

Unfortunately, you have two options after that

Process it possibly making assumptions, but only knowing the place in which the extension was installed. For instance, CREATE EXTENSION x WITH SCHEMA CURRENT_SCHEMA and CREATE EXTENSION x will be conflated. Simple access to the command executed isn't available because some simple commands issue multiple commands (ex, serial being a macro).
Process the internal pg_ddl_command with a C function.

Because the second option sounds like less joy, let's go with the first.

Check out the list under Capturing Changes at Command End which supports objid being returned from the SRF pg_event_trigger_ddl_commands(). Note, schema_name is not what you're after.

CREATE OR REPLACE FUNCTION verify_extension_schema()
RETURNS event_trigger
AS $$
DECLARE
  schemafail bool;
BEGIN
  schemafail = (
    SELECT n.nspname = 'public'
    FROM pg_event_trigger_ddl_commands() AS ev
    INNER JOIN pg_catalog.pg_extension AS e
      ON ev.objid = e.oid
    INNER JOIN pg_catalog.pg_namespace AS n
      ON e.extnamespace = n.oid
  );
  IF schemafail THEN
    RAISE EXCEPTION 'Creating extensions into "public" is disabled';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER nag_my_users
  ON ddl_command_end
  WHEN TAG IN ('CREATE EXTENSION')
  EXECUTE PROCEDURE verify_extension_schema();

Now you can

test=# CREATE EXTENSION intarray ;
ERROR:  Creating extensions into "public" is disabled
test=# CREATE EXTENSION intarray WITH SCHEMA public;
ERROR:  Creating extensions into "public" is disabled
test=# CREATE EXTENSION intarray WITH SCHEMA foo;
CREATE EXTENSION

If you just want a notification, you can change it to RAISE NOTICE


SQL Server : Execute as multiple users simultaneously? I need to query a proprietary database where the software vendor has set tight permissions on each schema (one username per schema), and each schema-user is only allowed to read from their own schema.

I now need to run a query across multiple schemas. Is it possible to run a single query against multiple user accounts? Something like EXEC AS USER 'A' AND 'B'?

Obviously the correct method is to create a user with all required permissions, but I am running the query from a stand-alone utility and will not have admin privileges on my future clients' instances.

No, you can't.

You could create user C that has permissions of A and B...


Oracle Real cluster application? My application is working in Real cluster application(RAC)in production. I am new to RAC and  gone through this video .

My understanding after reading this

RAC uses more than one node only for data processing the data in memory but still uses one node to store data
processing can be like applying index, order by clause, any function etc
So its the load distribution at processing level not at data storage level

Is my understanding correct ?

Data is stored on shared storage. Nodes do not store any data, they just process it.
Yes.
Yes.


Change SSIS package authors We have a job, that contains SSIS packages. It is SQL Server 2005. A dba named xyz created those SSIS packages. It was running fine in the when the job kicks in. However, he left, and the company wanted to change all his ownership from all objects, such as databases, jobs, and maintenance plans ownership to sa.

As we did this, lots of errors came up. Every other job runs fine, only those jobs with SSIS package are failing.

Error is :

  Executed as user: Mycompany\sqlagentserviceAccount. Unable to open Step output file.  The step failed.

However, the SSIS package was created by the xyz DBA. For troubleshooting we did change the job ownership to xyz dba name, but it still failed.

Any help on how to change the SSIS packages back to his name, or any other tips, would be appreciated.

You would need to check the security setting of the package configuration (inside the package). The default for a package is "EncryptSenstiveWithUserKey", which means only that user can modify the package or read the connection manager objects.

You also don't provide how the packages are deployed. If they are deployed to the Package Store or SQL Server then the security setting mentioned above does not apply. If they are using file level deployment who executes the job or owns it can play a role in the issue you are having....meaning you will have to adjust the packages.


querying a linked PostgreSQL database in SQL Server 2008 We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server.  I am exploring options for real-time or near real-time reporting in this environment.

I am curious about linking the Postgres database to our SQL Server environment.  Would I be able to build views against the Postgres database that our SQL Server report writers could then use?  Are there any additional performance considerations beyond the normal ones?  (i.e. views are slower than tables)

If you are OK with the reports being run directly off of the Progress database, look into SQL Server Reporting Services and the Report Builder 2.0 in SQL Server 2008 R2.  If you can deploy Reporting Services 2012 (part of SQL Server 2012) and you've got SharePoint 2010 installed look into using PowerView so allow people to build reports off of data sets which you've pre-defined as part of a data model.

If you need to get the data into SQL Server then you'll probably want to look at building a SQL Server Integration Services package which would then copy the data from the needed tables into the reporting database on a set schedule.


Which factors impact the time for SQL Server Recovery to complete In a recent planning AWS RDS failover we experienced long recovery times of around 20 minutes. AWS provides some hints around why.

large transactions or a lengthy recovery process can increase failover
time.

The database itself doesn't make much use of large transactions, so I don't think this is the culprit. Therefore I'm interested to understand what factors might result in a lengthy recovery process?

Does the SQL Server version have an impact (We are on 2016)
Does the size of the database have an impact?

The recovery process took around 20 minutes and we'd like to take steps to reduce this to around 10 minutes.
Update:
One thing I didn't mention is, due to years of bad design about 50% of the database size is due to storing blobs of files, such as html or XML. Could this have any impact on recovery time?

Since you are not using SQL server 2019, ADR won't be applicable. There are few general guidelines, which should be considered in almost all the cases and they are as below:

Keeping Transactions as tiny as possible.
Control long running transactions liaising with application team or vendor as appropriate.
Take transaction log backup as frequently as possible to reduce Analysis phase.

There is an option of changing target recovery time however that has some down-side also. You should consider them before enabling them.
From MS-site, if you avoid below scenarios, you don't need ADR:
Who should consider accelerated database recovery
The following types of customers should consider enabling ADR:

Customers that have workloads with long running transactions.
Customers that have seen cases where active transactions are causing the transaction log to grow significantly.
Customers that have experienced long periods of database unavailability due to SQL Server long running recovery (such as unexpected SQL Server restart or manual transaction rollback).

From the comment and new addition to your question about using blob type in your database, yes having these data types can significantly delay any checkpoint by database. In stead, you should use CAS storage type as explained well by Mr. Brent Ozar:
https://www.brentozar.com/archive/2015/03/no-more-blobs/
Hope this helps you in taking right decision.


Converting columns from NVARCHAR to VARCHAR I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns, so I wanted to look into:

Changing many of these columns over from NVARCHAR to VARCHAR, and also
Resizing these columns to more appropriate sizes based on some data profiling I have done.  I know for example that the max length of the data in some of these columns are only around 30 characters so NVARCHAR(MAX) seems like overkill.

I am also trying to limit implicit conversions.

What is an easy way to determine before doing this if each column does indeed only have VARCHAR data in it and won't have any issues with being converted away from NVARCHAR?

This database is only for US use and I don't think there are any foreign characters or letters in any of the columns so I wanted to look into ... Changing many of these columns over from NVARCHAR to VARCHAR

Before you go any further you need to research / determine the actual business requirements of these columns. If it's true that one or more of these columns are not currently holding any characters that wouldn't just as easily fit into VARCHAR, that doesn't mean that someone won't try to enter Unicode-only characters tomorrow, and that doing so would be outside of the intended design of the system. If end users / sales people / support people / the documentation believe that Unicode-only characters can be  entered into the system, then the system needs to allow for that, even if nobody has made use of that functionality yet. If this is the case and you feel it should be changed because you don't think there is a need to enter such characters, then you need to bring that up to a manager / Product Owner / etc.

Also, just because the intended user base is in the US doesn't mean that Unicode-only characters won't be used. At the very least in "name" columns, "description" columns, etc there could easily be a variety of accented characters, or who knows what. The discussion that you should have with the Product Owner and/or team should revolve around what is required for each of these columns / data points.

  I know for example that the max length of the data in some of these columns are only around 30 characters so NVARCHAR(MAX) seems like overkill.

Similar to the points made above regarding what types of characters should be allowed in these columns, you first need to determine how many characters the system allows people to send in. If a description field is capped at 500 - 1000 characters in the UI, and other parts of the code, and even the documentation (one can always hope, right?), agree, then absolutely yes, NVARCHAR(MAX) is absolutely overkill. BUT, if the column needs to store more than 4000 characters of a variety that is not represented by an 8-bit code page, then NVARCHAR(MAX) is not overkill (though you might need to consider a better design than storing too many of them in a single table). Either way, the fact that there are only 30 characters max in a particular NVARCHAR(MAX) column is good information to provide to a Product Owner / team so that a more informed decision can be made regarding the fate of that column. Perhaps a field that currently allows for 1000 characters but has a max record length of 30 - 50 should be updated to only allow maybe 75 - 100. But that needs to be a coordinated effort.

All of that said, if you want to determine if there are any characters in an NVARCHAR / NCHAR / NTEXT column that cannot be converted to VARCHAR, you need to convert to VARCHAR using the _BIN2 variation of the collation being used for that particular column. For example, if a particular column is using Albanian_100_CI_AS, then you would specify Albanian_100_BIN2 for the test. The reason for using a _BIN2 collation is that non-binary collations will only find instances where there is at least one character that does not have any mapping at all in the code page and is thus converted into ?. But, non-binary collations do not catch instances where there are characters that don't have a direct mapping into the code page, but instead have a "best fit" mapping. For example, the superscript 2 character, ², has a direct mapping in code page 1252, so definitely no problem there. On the other hand, it doesn't have a direct mapping in code page 1250 (used by the Albanian collations), but it does have a "best fit" mapping which converts it into a regular 2. The problem with the non-binary collation is that 2 will equate to ² and so it won't register as row that can't convert to VARCHAR. For example:

SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE French_100_CI_AS); -- Code Page 1252
-- ²
SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_CI_AS); -- Code Page 1250
-- 2

SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_CI_AS)
WHERE  N'²' <> CONVERT(NVARCHAR(MAX),
                       CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_CI_AS));
-- (no rows returned)

SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_BIN2)
WHERE  N'²' <> CONVERT(NVARCHAR(MAX),
                       CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_BIN2));
-- 2

Ideally you would convert back to NVARCHAR explicitly for the code to be clear on what it's doing, though not doing this will still implicitly convert back to NVARCHAR, so the behavior is the same either way.


Does the existence of a college course depend on the department in which it is studied? I have a Department entity along with a Course entity, does the course entity depend on the department or is it a strong entity by itself since it has an ID that can distinguish it from other entities?

The only sure answer to this question is "it depends".  This is a business rule and the organization will set this business rule.

Here is how you can go about thinking about what the business rule might be and how you can determine the best way to model this relationship.

Think first about cardinality.  Is course related to only a single department?  If a course can be multidisciplinary and offered jointly by two departments then you know right away that course is a strong entity.

Second, think about functional dependencies.  What are your candidate keys for course?  Do you have a candidate key that does not make reference to department?  If so, then you have good reason to presume that course is a strong entity.  Do all of the candidate keys of course reference the department that offers it?  That may be a strong indication that course is a weak entity.

Third, think about the future.  Let's assume that you determine from the above that course seems to be a weak entity.  Is it even remotely conceivable that the factors that led you to this conclusion might change?  If so, you might want to consider promoting course to a strong entity and inventing a candidate key that does not rely on department.  This is a matter of opinion and people may have different philosophies about this.  On the one hand, some people feel that making key changes in the database is a big, painful process to be avoided if at all possible.  On the other hand, some people feel strongly that your data model should reflect your current business rules as closely as possible.


Extracting a field from RESTORE HEADERONLY I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made.

The command:

RESTORE HEADERONLY FROM DISK = '<path to .bak file>'

works fine in Query Analyzer and gives a resultset with something like 50 columns.

The problem is actually accessing this from code.

I can get this into a temp table by declaring every single one of the 50:ish columns, inserting into it with exec and getting the value I want from there.

The problem is that I really want to avoid having to declare the entire resultset as a temp table as it seems like a very brittle solution if they ever add columns to it in future versions.

Is there any way to just get a single column out of this resultset without declaring all the columns?

This works for me.

SELECT BackupStartDate
FROM OPENROWSET('SQLNCLI',
                'Server=MARTINPC\MSSQL2008;Trusted_Connection=yes;',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC(''
RESTORE HEADERONLY
FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')'
)

The ad hoc distributed queries Option needs to be enabled. Or if you don't want to do that you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLOLEDB', @datasrc = @@servername

SELECT BackupStartDate
FROM OPENQUERY(LOCALSERVER,
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')


PostgreSQL copy text type column to text[] array type column I have two columns, material_size (text type) and material_size_temp (text[] array type).

When I ran UPDATE parts SET material_size_temp[0] = material_size; I would get values like [0:0]={".021 x 2.450"} instead of {".021 x 2.450"}.

How do I clone the text type column to text[] array type column?

I was close, unlike JavaScript and PHP it seems PostgreSQL array key numbers start with 1, not 0.

UPDATE parts SET material_size_temp[1] = material_size;


Concatenate 2-3 table field values in 1 table field in access I have a project that in Access. The problem is I am a newbie in this application and I really couldn't figure out what to do, I have checked out several ways but am still stuck.

I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year.

My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.

It would be better to attach the file but I couldn't find a way. Here's the layout I would like to have:

From my table, field 1 will be where it concatenates the other fields. Year as field 2, Month(field 3), Customer Code (field 4) and so on for 10 surveyed products.

I need to concatenate field 2, 3 & 4 in this way I can see if the customer was entered on that particular month and year and I can arrange a warning if already exist within my form.

You shouldn't need to concatenate the fields at all. You can find out whether a customer has filled out the survey for a given month and year by using all 3 columns:

SELECT * FROM Surveys
WHERE CustomerID = 1
AND SurveyMonth = Month(Date())
AND SurveyYear = Year(Date())

The Date() function returns the current system date. The Month() and Year() functions return the month and year, respectively, of the date passed to them.

A suggestion: store the entire date a survey was taken as a Date type in the database rather than using separate columns for month and year. This will simplify lookups and storage, making the query above look more like:

SELECT * FROM Surveys
WHERE CustomerID = 1
AND Month(SurveyDate) = Month(Date())
AND Year(SurveyDate) = Year(Date())

where SurveyDate is a column of Date type.


Finding matches where GROUP BY COUNT(*) is 1 Say I have tables

organisation:

id | name
---------
1    ACME
3    MGM

org_type:

type_id | org_id
----------------
1         1
2         1
2         3

How do I query a list of org names where there type is 2, and no other type is linked with that organisation? In other words, a query that returns only organisation with org_id of 3 from above data.

I'm trying queries like

SELECT o.name
FROM organisation o
JOIN org_type ot ON o.id=ot.org_id
WHERE ot.type_id = 2
GROUP BY ot.org_id
HAVING COUNT(*) = 1

But obviously that just returns the list of ALL orgs where a link to type_id 2 exists. I only want those where no other link exists.

This query should help you out.

It works with an delivered table named org_type what filters out double org_id.

That delivered table will be matched with where filter org_type.type_id = 2 to make sure that type_id = 2

SELECT
   org_type.type_id
 , org_type.org_id
 , organisation.name
FROM (
 SELECT
     type_id
   , org_id
 FROM
   org_type
 GROUP BY
   org_id
 HAVING
   COUNT(*) = 1
) AS
  org_type
INNER JOIN
  organisation
ON
  org_type.org_id = organisation.id
WHERE
  org_type.type_id = 2


How to match on a longer word using MySQL Full Text Search? I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter)

When I search something, it will first search in the company table and then search the query in the tags field.

I wrote the query but I have a problem. When I search "emlak", everything is fine but if I search "emlakçı" it can't find anything. Do I have to enter "emlakçı" as a keyword in tags field or is it possible to solve this with an another way?

Here is my query :

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC

Here is your original query
SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC

You may want to try running the fulltext searching before doing the LEFT JOIN
SELECT
    SQL_CALC_FOUND_ROWS company.*,
    company.nameScore,category.tagScore
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
ORDER BY nameScore DESC;

As for the search string emlakçı, try using a wildcard character. In other words,
MATCH(tags) AGAINST ('emlak*' IN BOOLEAN MODE)

This may pick up more occurrences but should get emlak and similar strings
UPDATE 2012-07-30 18:01 EDT
I don't know your table structure. Assuming name is a field in category, try this:
SELECT
    company.name,category.name,COUNT(1) CategoryCount
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
GROUP BY company.name,category.name;


Logical operators OR AND in condition and order of conditions in WHERE Let's examine these two statements:

IF (CONDITION 1) OR (CONDITION 2)
...

IF (CONDITION 3) AND (CONDITION 4)
...

If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will CONDITION 4 be checked?

What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?

ADDED:

Thank to Jack for link, surprise from t-sql code:

IF  1/0 = 1 OR 1 = 1
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

IF  1/0 = 1 AND 1 = 0
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

There is not raise a Divide by zero exception in this case.

CONCLUSION:

If C++/C#/VB has short-circuiting why can't SQL Server have it?

  To truly answer this let's take a look at how both work with
  conditions. C++/C#/VB all have short circuiting defined in the
  language specifications to speed up code execution. Why bother
  evaluating  N OR conditions when the first one is already true or M
  AND conditions when the first one is already false.

  We as developers have to be aware that SQL Server works differently.
  It is a cost based system. To get the optimal execution plan for our
  query the query processor has to evaluate every where condition and
  assign it a cost. These costs are then evaluated as a whole to form a
  threshold that must be lower than the defined threshold SQL Server has
  for a good plan. If the cost is lower than the defined threshold the
  plan is used, if not the whole process is repeated again with a
  different mix of condition costs. Cost here is either a scan or a seek
  or a merge join or a hash join etc... Because of this the
  short-circuiting as is available in C++/C#/VB simply isn't possible.
  You might think that forcing use of index on a column counts as short
  circuiting but it doesn't. It only forces the use of that index and
  with that shortens the list of possible execution plans. The system is
  still cost based.

  As a developer you must be aware that SQL Server does not do
  short-circuiting like it is done in other programming languages and
  there's nothing you can do to force it to.

There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause. The single expression that allows statement short-circuiting is CASE-WHEN. The following is from an answer I posted on Stackoverflow:
How SQL Server short-circuits WHERE condition evaluation

It does when it feels like it, but not in the way you immediately think of.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

For further details check the first link in the above blog entry, which is leading to another blog:
Does SQL Server Short-Circuit?

The final verdict?  Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression.  With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.


Getting Identity values to use as FK in an INSTEAD OF trigger I have a series of updateable views we are exposing to end users as the interface for a back end process.

One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and INSERTs.

The structure of the tables is (greatly simplified):

Claim
(DataRowID bigint IDENTITY PRIMARY KEY
,<bunch of claim data>)

ClaimExtended
(ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL
,<bunch of other claim data>)

My original plan was to do this in the trigger like so:

CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyView]
INSTEAD OF INSERT
AS

DECLARE @IDLink TABLE
    (RowID int
    ,ClaimDataRowID bigint)

DECLARE @Inserted TABLE
    (RowID int identity (1,1) NOT NULL
    ,<all the columns from the view>)

INSERT INTO
    @Inserted
    (<View columns>)
SELECT
    (<View columns>)
FROM
    Inserted

INSERT INTO
    Claim
    (<Columns>)
OUTPUT
    I.RowID
    ,inserted.ClaimDataRowID
    INTO
        @IDLink (RowID, ClaimDataRowID)
SELECT
    (<Columns>)
FROM
    @Inserted I

INSERT INTO
    ClaimExtended
    (ClaimDataRowID,
    <Columns>)
SELECT
    C.ClaimDataRowID,
    <Columns>
FROM
    @Inserted I
INNER JOIN
    @IDLink C
        ON C.RowID = I.RowID

The OUTPUT clause here is not working, however (Multi-part identifier I.RowID could not be bound) I'm assuming because I can't reference the source table in an INSERT OUTPUT clause.

What other method could I use here besides making the view a table?  For other reasons this needs to be a VIEW and the underlying tables are pretty much set in stone.

Tables:

CREATE TABLE dbo.Claim
(
    DataRowID   bigint IDENTITY NOT NULL,
    ClaimColumn integer NOT NULL,

    CONSTRAINT PK_Claim
        PRIMARY KEY CLUSTERED (DataRowID)
);
GO
CREATE TABLE dbo.ClaimExtended
(
    ClaimDataRowID  bigint NOT NULL,
    ExtendedColumn  integer NOT NULL,

    CONSTRAINT PK_ClaimExtended
        PRIMARY KEY CLUSTERED (ClaimDataRowID),

    CONSTRAINT FK_ClaimExtended_Claim
        FOREIGN KEY (ClaimDataRowID)
        REFERENCES dbo.Claim (DataRowID)
);

View:

CREATE VIEW dbo.MyView
WITH SCHEMABINDING
AS
    SELECT
        c.DataRowID,
        c.ClaimColumn,
        ce.ExtendedColumn
    FROM dbo.Claim AS c
    JOIN dbo.ClaimExtended AS ce ON
        ce.ClaimDataRowID = c.DataRowID;

Instead-of trigger:

CREATE TRIGGER trgMyView_IOI
ON dbo.MyView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET ROWCOUNT 0;

    DECLARE @ExtendedRows AS TABLE
    (
        ClaimDataRowID  bigint PRIMARY KEY,
        ExtendedColumn  integer NOT NULL
    );

    MERGE dbo.Claim AS c
    USING INSERTED AS ins ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (ClaimColumn)
        VALUES (ins.ClaimColumn)
    OUTPUT
        INSERTED.DataRowID,
        ins.ExtendedColumn
    INTO @ExtendedRows
        (
            ClaimDataRowID,
            ExtendedColumn
        );

    INSERT dbo.ClaimExtended
        (ClaimDataRowID, ExtendedColumn)
    SELECT
        er.ClaimDataRowID,
        er.ExtendedColumn
    FROM @ExtendedRows AS er;
END;

Example usage:

INSERT dbo.MyView
    (ClaimColumn, ExtendedColumn)
VALUES
    (1000, 2000),
    (1001, 2001);
GO
SELECT
    mv.DataRowID,
    mv.ClaimColumn,
    mv.ExtendedColumn
FROM dbo.MyView AS mv;

Output:


How to know what database user I'm using in SQL Server? I'm learning SQL with SQL Server and I've see how to create users, I've created without login:

CREATE USER Luis_Gerente WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Mario_Supervisor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Ana_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Martin_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Carlos_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo

But I don't know what user I'm currently using, and with what user I've created those other users.

How can I know that, and how can I change users depending on the person who is using the database server?

I do authentication at server level but at the database level I currently do not see any effect of having users at database level. When I use the database where I created the users I don't have changes or effects.

When I start to use the database I'm not getting any prompt or form needing a specific database user to use the database with the specific privileges of that user.

But I don't know what user I'm currently using

CURRENT_USER will return the current username. See https://docs.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql for more detail, and links to other similar functions.

As you can see there are a couple of ways to get the current username:
SELECT CURRENT_USER, USER_NAME(), USER_NAME(USER_ID())
should return your name three times.

  How can I change users depending on the person who's using the database server?

I think you are confusing database users with server logins, which is easy to do. You have explicitly created those users WITHOUT LOGIN. To let someone login as a specific account you need to create a login then create a database user for that login. Something like:

USE [master]
GO
CREATE LOGIN [NewLoginName] WITH PASSWORD=N'PasswordForLogin'
GO
USE [YourDatabaseName]
GO
CREATE USER [NewUserName] FOR LOGIN [NewLoginName] WITH DEFAULT_SCHEMA=[dbo]
GO

in SSMS. There are numerous other options when creating logins (for things like password policy and so forth) and users. See here and here for MS's documentation.


Defragmenting Indexes Bloats Log Files I'm having difficulty "optimizing" indexes as a minimally logged operation.  Before the index maintenance is performed, the database recovery model is switched from FULL to BULK LOGGED.  Depending on the fragmentation percent, each index is the recipient of a REBUILD or a REORGANIZE (or no action is taken).  After index maintenance is finished, the recovery model is reverted to FULL.

One database in particular is causing me some pain.  The datafiles are about 64GB (including any free space).  A defrag operation bloated the log file to 38GB, until it filled the logical drive.  Then the level-17 alerts started rolling in.

I tried duplicating this in a test environment.  Numerous attempts were made with different recovery models, index REORG vs REBUILD, different transaction isolation levels, read committed snapshot ON vs OFF, different index fragmentation levels, etc.  Index REBUILDs on DB’s in the FULL recovery model always bloated the t-logs.  No other testing variations did, however.  This was frustrating because I could not duplicate what was happening in production.

What am I missing?  How can I optimize indexes without bloating the log files?

UPDATE 09/23/2015
Not surprisingly, Ola Hallengren's name came up soon after I posted my question.  Although I don't use his scripts, I am somewhat familiar with them.  For those who are interested, there is a wonderful PASS session video--Ola is the presenter.  Near the 48 min mark, an audience member asks a question similar to mine.

You can clear the log during the REORG process as its not done as a single transaction. The log bloating  depends on index fragmentation level and amount of work done.

Again, REORG is a fully logged operation on all recovery models.
https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


SELECT COUNT of multiple column under the same table Im blowing my brain on this one. I think I am making this harder than I need to. Im trying to run a mySQL query to SELECT a COUNT of members which have any values in any column. my example table is as such:

So far, I have got

SELECT COUNT(members)
FROM table_1
WHERE '1' IN column_1,
OR column_2,
OR column_3,
OR column_4

Please help, I am returning the craziest results

My results should look like

Member----------3

The use of COALESCE() will do what you ask for very nicely, but I would also question if you might want to reconsider your schema. It looks like what you're doing is tracking individuals' membership in various groups (column_1 ... column_X).

You would probably fare far better to simply make a membership table with two columns: member_id and group_id. You could then more easily add/remove groups from the groups table and counting memberships would have been as simple as a COUNT() on rows matching the member_id in the membership table. This would also set you up better for varying types of members (additional field on membership). Just a thought on a better way to plan for future needs here.


Duplicate record inserted - why trigger is not preventing it? I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional business logic and updates state of in parent table DebitIUVs. The idea is to prevent inserting new row in the RPTs table when the state is changed to be different from 118001 (while changes are in transaction in the trigger). Calls are executed via WEB Service.

For unknown reason we have 2 calls in the same time which cases 2 rows inserted in RPTs table. The code seems logically correct. Also we have heavy load in the database (sometimes it reaches 100000 transactions per day).

Any idea why this can happened?

Simplified code:

CREATE TABLE [dbo].[DebitIUVs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDIUVState] [int] NOT NULL,
    CONSTRAINT [PK_DebitIUVs_ID] PRIMARY KEY NONCLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[RPTs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDDebitIUV] [int] NOT NULL,
    [IDRPTState] [int] NOT NULL CONSTRAINT [DF_RPTs_IDRPTState] DEFAULT ((120001)),
    [PaymentRequestID] [varchar](35) NULL,
    CONSTRAINT [PK_RPTs_ID] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [UK_RPTs_PaymentRequestID] UNIQUE NONCLUSTERED ([PaymentRequestID] ASC)
);
GO

CREATE TRIGGER dbo.trg_aiu_RPTs_RI
    ON dbo.RPTs
    FOR INSERT, UPDATE
AS
BEGIN
    --if no rows are affected - return
    IF ISNULL((SELECT COUNT(ID) FROM inserted), 0) = 0 RETURN;

    DECLARE @dtNow DATETIME = GetDate();

    SET NOCOUNT ON;
    UPDATE RPT SET
        PaymentRequestID =
            CONVERT(VARCHAR(10), @dtNow, 112) + REPLACE(CONVERT(VARCHAR(10), @dtNow, 108), ':', '') +
            RIGHT(REPLICATE('0', 10) + CAST(ins.ID AS VARCHAR(10)), 10)
    FROM inserted ins
        INNER JOIN dbo.RPTs RPT ON
            (RPT.ID = ins.ID)
    WHERE ins.PaymentRequestID IS NULL;

    IF UPDATE(IDRPTState)
    BEGIN
        UPDATE DIUV SET
            DIUV.IDIUVState =
                CASE
                    WHEN ins.IDRPTState = 120101 THEN 118002
                    WHEN ins.IDRPTState = 120201 THEN 118003
                    ELSE DIUV.IDIUVState
                END
        FROM
            inserted ins
            LEFT JOIN deleted del ON del.ID = ins.ID
            INNER JOIN dbo.DebitIUVs AS DIUV ON DIUV.ID = ins.IDDebitIUV
        WHERE (del.ID IS NULL) OR (del.IDRPTState <> ins.IDRPTState);
    END;
END;
GO

CREATE PROCEDURE dbo.sproc_1
    @IDIUV INT,
    @IDIUVState INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION InsertDebit;
    INSERT INTO dbo.RPTs (IDRPTState, IDDebitIUV)
    SELECT @IDIUVState, IUV.ID
    FROM dbo.DebitIUVs IUV
    WHERE
        (IUV.ID = @IDIUV) AND
        (IUV.IDIUVState <> 118002);
    COMMIT TRANSACTION InsertDebit;
END;
GO

INSERT INTO DebitIUVs (IDIUVState) VALUES (118001);
EXEC dbo.sproc_1 1, 120101;

If you suspect this to be as a result of 2 transactions occurring nearly simultaneously, then it would appear to perhaps be them overlapping and the first not having committed before the second queries the RPTs table.

This will be down to your Transaction Isolation Levels for the queries. Top validate this you could include SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in the Stored Procedure to force the second to wait until the first commits before operating.


How to make performance tests for DML I created two multi-column indexes on a big table (~2,5 millions rows 370Mb and 621Mb with indexes).

SELECT queries are very fast.

Know I want to check if we have a regression for INSERT/UPDATE queries.

Is there any script to perform EXPLAIN ANALYZE INSERT/UPDATE without write anything in the table (with BEGIN, ROLLBACK statements)?

I want to run the script without indexes and with indexes to compare execution times.

I am using Postgresql v9.5

You can run EXPLAIN ANALYZE inside a transaction and then roll back the transaction.  This will write to the table and indexes, but the writes will not become visible to other users, other then through locks and side-channels (e.g. table and index bloat).

If you already have a script which runs the updates you want, adding BEGIN and ROLLBACK (or changing COMMIT to ROLLBACK if your script already does explicit transactions) should be incredibly trivial; so it is not clear what you are asking.

But you should clone your database to a test environment, and then run your tests in the test environment in a realistic manner.  Running aborted updates in a tight loop might not have the same performance as running committed updates in a tight loop.


Alter role shows role does not exists Running alter role db_ddladmin add member sqlloginname

gives the error message:

  Cannot alter the role 'db_ddladmin' because it does not exist or you
  do not have permission.

This statement I am executing in master db from Server admin login in azure sql db. I am not able to find any reason of that.

You do not have permission to add users to the db_ddladmin role in the master database.
Since
The Server admin and Azure AD admin accounts have the following characteristics:

Do not enter the master database as the dbo user, and have limited
permissions in master.
Are not members of the standard SQL Server sysadmin fixed server role,
which is not available in SQL database.
Can create, alter, and drop databases, logins, users in master, and server-level IP firewall rules.

Source
When querying the sys.database_role_members system view in the master database, you will also see that only dbo is part of the db_owner role. For most or all other roles, no members show up.
DatabaseRoleName         DatabaseUserName
db_accessadmin           No members
db_backupoperator        No members
db_datareader            No members
db_datawriter            No members
db_ddladmin              No members
db_denydatareader        No members
db_denydatawriter        No members
db_owner                 dbo
db_securityadmin         No members
dbmanager                No members
loginmanager             No members
public                   No members

This means that specific permissions are granted to the user connecting to the master database, adding users to the db_ddladmin role in master is not one of them.
As documented, you can add users to the dbmanager & loginmanager db roles in master.
alter role [dbmanager] add member sqlloginname;
alter role [loginmanager] add member sqlloginname;

You can add users to the db_ddladmin role in user databases when connecting with the server admin since you will have dbo permissions there.
Remember that these are database roles, and that you will have to add the user per database.


Best way to store correlation data for searches This is a long post where I outline a problem I faced recently and the steps I went to solving it. I ask for feedback on the solution I came up with and for recommendations for improvements.
Using an FP-growth algorithm in pyspark I created a dataset of correlated keywords for cities around the US, this would look something like
java&javascript, fort collins, python, .85
java, los angeles, c++, .65
python&php&mysql, New York, java, .4
This means in job postings in fort collins when both java and javascript appeared, python was likely to also appear with a correlation value of .85, and for jobs in los angeles that had java, c++ was likely to appear.
The problem I had with storing this data was how to search it in a table, The following table schema
id, keyword_id, city_id, correlated_keyword_id, value
Does not work as the number of keyword ids can range from 1 to N (where N is the total number of keywords) Having a table like
id, keyword1_id, keyword2_id ... keywordn_id
(where anything after keyword1 would be null by default) seems like an improper solution and not very scalable.
Another idea I had was to split it into multiple tables
id, cityid, correlated_keyword, value
id, correlation_id, keyword_id
(where correlation_id is a foreign key of the id column in the above table)
While this would work if I wanted to do a search on what was correlated with java&javascript&c++
I feel like I would need to do 3 seperate queries on the second table then do a join to find which coorrelation_ids appeared in all 3 searches.
While this seems slow it also fails to take into account if the user wanted  to find the correlated keywords to the above 3 only in a certain city, as what is correlated to java&javascript&c++ varies in each city.
The last solution is to store the keywords I would search on in varchar, which would allow me to search on just java or also java&javascript&...
Again this seems like a poor solution
The idea I settled on solved all of the problems of the previous two examples but only worked because the number of keywords we had to search on was less than 64.
I gave each keyword an id of a power of 2
1, 2, 4, 8, 16 ...
then the table I used to store the correlation data was
id, keyword_sum, city_id, coorelated_keyword_id, value
Then if a user wanted to search on
java&javscript&c++ I could sum those 3 keywords as that would be a unique value that no other combination of keywords would ever equal.
Also adding a multi column index on keyword_sum and city_id allowed me to do fast searches if I wanted to filter on city as well.
What are your thoughts on this solution, is this the only optimized way to allow users to search on a variable number of keywords and city? Or is there another solution I did not consider, perhaps a relational database is a poor DB to use, in that case what would be better? NOSQL, Mongo?

There are (at least) two ways of doing this, but only one will be shown here (see edits for old version of query):
N correlated keywords (where N is arbitrary).
I constructed a table as follows (complete DDL and DML are at the bottom of this post and also on the fiddles - PostgreSQL & MySQL):
CREATE TABLE city_lang_jp_combo
(

  c_l_combo_set INTEGER NOT NULL,

  c_id      INTEGER NOT NULL,
  l_id      INTEGER NOT NULL,

  c_l_combo_jp DOUBlE PRECISION NOT NULL,

  CONSTRAINT c_l_combo_id_pk PRIMARY KEY (c_l_combo_set, c_id, l_id),

  CONSTRAINT c_l_combo__jp_exists_fk FOREIGN KEY (c_id, l_id)
    REFERENCES city_language (c_id, l_id)

);

and it's populated as follows:
INSERT INTO city_lang_jp_combo
VALUES
(1, 101, 1111, 0.61),
(1, 101, 2222, 0.61),
(1, 101, 3333, 0.61),

(2, 102, 1111, 0.62),
(2, 102, 2222, 0.62),
(2, 102, 3333, 0.62),

...
... snipped ~ 35 rows for brevity
...

The first set of three records means that for a set 1 (1 is arbitrarily assigned, could be any INT which doesn't occur elsewhere in the table), for city 101 (Dublin) and languages 1111, 2222 & 3333 (Java, C & C++) that  the probability of the three of them occurring together is 0.61 in job postings.
I also have a 4 language combination and a 2 language combination at the bottom as follows:
(13, 101, 1111, 0.25),
(13, 101, 2222, 0.25),
(13, 101, 3333, 0.25),
(13, 101, 4444, 0.25),  <=== Python

and also a two language combination:
(14, 101, 5555, 0.05),  <=== Nim
(14, 101, 6666, 0.05);  <=== Clojure

So, first I run this SQL:
SELECT
  c.city_name AS cname,
  cljc.c_l_combo_set AS combo,
  cljc.c_id AS cid,
  l.lang_name AS ln,
  cljc.c_l_combo_jp AS prob
FROM  city_lang_jp_combo cljc
JOIN language l
  ON l.lang_id = cljc.l_id
JOIN city c
  ON c.city_id = cljc.c_id
ORDER BY cname, combo;

Result:
cname   combo   cid     ln         prob
Dublin      1   101     C++        0.61
Dublin      1   101     Java       0.61
Dublin      1   101     C          0.61
Dublin      5   101     Python     0.51
Dublin      5   101     C          0.51
Dublin      5   101     Java       0.51
Dublin      9   101     Python     0.41
Dublin      9   101     C++        0.41
Dublin      9   101     C          0.41

Dublin     13   101     Java       0.25  <<-- 4 language combo, Java,
Dublin     13   101     C          0.25  <<-- C, C++ & Python - low p-Value
Dublin     13   101     C++        0.25
Dublin     13   101     Python     0.25

Dublin     14   101     Nim        0.05  <<-- two language combo - Nim  - v. low p-Value
Dublin     14   101     Clojure    0.05  <<-- and Clojure

Madrid      4   104     C          0.64
...
... snipped
...

And, using this query, I perform a STRING_AGG (GROUP_CONCAT in MySQL) using the above as a subquery as follows:
SELECT
  t.cname AS "City name",
  STRING_AGG(t.ln, ', ' ORDER BY t.cname, t.combo, ln) AS "Languages",
  t.prob AS "Probability"
FROM
(
  SELECT
    c.city_name AS cname,
    cljc.c_l_combo_set AS combo,
    cljc.c_id AS cid,
    l.lang_name AS ln,
    cljc.c_l_combo_jp AS prob
  FROM  city_lang_jp_combo cljc
  JOIN language l
    ON l.lang_id = cljc.l_id
  JOIN city c
    ON c.city_id = cljc.c_id
  -- ORDER BY cname, combo
) AS t
GROUP BY t.cname, t.combo, t.prob
ORDER BY "City name", "Languages";

Result:
City name   Languages           Probability
Dublin  C, C++, Java                   0.61
Dublin  C, C++, Java, Python           0.25
Dublin  C, C++, Python                 0.41
Dublin  C, Java, Python                0.51
Dublin  Clojure, Nim                   0.05
Madrid  C, C++, Java                   0.64
Madrid  C, C++, Python                 0.44
Madrid  C, Java, Python                0.54
Paris   C, C++, Java                   0.62
Paris   C, C++, Python                 0.42
Paris   C, Java, Python                0.52
Rome    C, C++, Java                   0.63
Rome    C, C++, Python                 0.43
Rome    C, Java, Python                0.53

I did a performance analysis on both the PostgreSQL and the MySQL queries - they are pretty similar and the new query takes (on average) 20% - 25% as long as my first query (see the fiddle).
There's no need to use an MD5 hash or anything else to ensure uniqueness - the language names are unique by virtue of the UNIQUE index on the language table so a combination of these in lang_id or lang_name order is guaranteed to be unique - and the lang_name strings are human-readable - never underestimate the value of this factor!
Your main problem is going to be enforcing UNIQUEness - there's nothing stopping the records
Dublin     999  101     Nim        0.23  <<-- two language combo - Nim  - v. low p-Value
Dublin     999  101     Clojure    0.23  <<-- and Clojure

being inserted - and obviously only 1 of the two sets of records can be correct, but we can't enforce uniqueness over sets of records. You could create a TEMPORARY table with fields the same as the resultset above and a UNIQUE constraint on "City name" and "Languages". Then try to INSERT the query result into that table and see if it fails on the constraint... Of course, there are all sorts of ways of doing this programmatically and/or through the use of TRIGGERs.
Another problem is that there is no way of ensuring that the p-Values are realistic - i.e. if p(C & Python) is 0.6 (say), then p(C & Python & C++) can be at most 0.6 (every job posting with the first two also has C++). This will have to be enforced in code...
p.s. welcome to the forum!

Full DDL and DML...
CREATE TABLE city
(
  city_id   INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  city_name TEXT NOT NULL,
  city_pop  INTEGER NOT NULL,
  --
  --  more fields
  --
  CONSTRAINT city_pk PRIMARY KEY (city_id),
  CONSTRAINT city_name_uq UNIQUE (city_name) -- might not always be the case,
    -- just have 'Dublin (Ire.)' and ('Dublin (Cal.)') for example
);

Populate:
INSERT INTO city (city_name, city_pop) VALUES
('Dublin', 500000), ('Paris', 2100000), ('Rome', 3000000), ('Madrid', 3250000);

The population might be useful for calculating confidence intervals and the like...
CREATE TABLE language
(
  lang_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
  lang_name  TEXT NOT NULL,

  CONSTRAINT language_pk PRIMARY KEY (lang_id),
  CONSTRAINT lang_name_uq UNIQUE (lang_name)
);

Populate:
INSERT INTO language (lang_id, lang_name) VALUES
(1111, 'Java'), (2222, 'C'), (3333, 'C++'), (4444, 'Python'), (5555, 'Nim'), (6666, 'Clojure');

The language_name's UNIQUE constraint means that the result of query will be unique for any combination of languages when ordered by l.lang_name (or lang_id separated by a comma - must have the comma for numbers because 1 || 11 || 111 is identical to 11 || 11 || 11).
CREATE TABLE city_language
(
  c_id     INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  l_id     INTEGER NOT NULL,
  c_l_prob DOUBLE PRECISION NOT NULL,

  CONSTRAINT city_language_pk PRIMARY KEY (c_id, l_id)

);

This table is a sort of "insurance policy" - the c_id, l_id combination act as a FOREIGN KEY for the table below (combinations of langauges) - how can you have combinations of languages if you don't have at least have 1  of them?
However, as discussed above, we have no way of knowing if the p-Value for 1 language (per city) - say C - is <= p (2 languages) - (say C & C++) for that same city.
Populate city_language:
INSERT INTO city_language (c_id, l_id, c_l_prob) VALUES

(101, 1111, 0.91),  (101, 2222, 0.92),  (101, 3333, 0.93),  (101, 4444, 0.94),
(102, 1111, 0.81),  (102, 2222, 0.82),  (102, 3333, 0.83),  (102, 4444, 0.84),
(103, 1111, 0.751), (103, 2222, 0.752), (103, 3333, 0.753), (103, 4444, 0.754),
(104, 1111, 0.701), (104, 2222, 0.702), (104, 3333, 0.703), (104, 4444, 0.704),
(101, 5555, 0.2), (101, 6666, 0.10);

Final table (this is the main table):
CREATE TABLE city_lang_jp_combo
(

  c_l_combo_set INTEGER NOT NULL,

  c_id      INTEGER NOT NULL,
  l_id      INTEGER NOT NULL,

  c_l_combo_jp DOUBlE PRECISION NOT NULL,

  CONSTRAINT c_l_combo_id_pk PRIMARY KEY (c_l_combo_set, c_id, l_id),

  CONSTRAINT c_l_combo__jp_exists_fk FOREIGN KEY (c_id, l_id)
    REFERENCES city_language (c_id, l_id)

    -- if this doesn't exist, then how can you have a triple combination?
);

Populate it:
INSERT INTO city_lang_jp_combo VALUES

 (1, 101, 1111, 0.61),  -- check back to city_language p !> in there...
 (1, 101, 2222, 0.61),  -- ensure that there are at least 3 records?
 (1, 101, 3333, 0.61),

 (2, 102, 1111, 0.62),
 (2, 102, 2222, 0.62),
 (2, 102, 3333, 0.62),

 (3, 103, 1111, 0.63),
 (3, 103, 2222, 0.63),
 (3, 103, 3333, 0.63),

 (4, 104, 1111, 0.64),
 (4, 104, 2222, 0.64),
 (4, 104, 3333, 0.64),

 (5, 101, 1111, 0.51),
 (5, 101, 2222, 0.51),
 (5, 101, 4444, 0.51),

 (6, 102, 1111, 0.52),
 (6, 102, 2222, 0.52),
 (6, 102, 4444, 0.52),

 (7, 103, 1111, 0.53),
 (7, 103, 2222, 0.53),
 (7, 103, 4444, 0.53),

 (8, 104, 1111, 0.54),
 (8, 104, 2222, 0.54),
 (8, 104, 4444, 0.54),

 (9, 101, 2222, 0.41),
 (9, 101, 3333, 0.41),
 (9, 101, 4444, 0.41),

(10, 102, 2222, 0.42),
(10, 102, 3333, 0.42),
(10, 102, 4444, 0.42),

(11, 103, 2222, 0.43),
(11, 103, 3333, 0.43),
(11, 103, 4444, 0.43),

(12, 104, 2222, 0.44),
(12, 104, 3333, 0.44),
(12, 104, 4444, 0.44),

(13, 101, 1111, 0.25),
(13, 101, 2222, 0.25),
(13, 101, 3333, 0.25),
(13, 101, 4444, 0.25),

(14, 101, 5555, 0.05),
(14, 101, 6666, 0.05);

Discussion (also in fiddle):
--
-- Now, consider cl_combo_set = 777 below - it's
-- essentially a duplicate of the cl_combo_set = 1
--
-- But the problem is, how does one enforce uniqueness over a set of
-- records?
--

-- (777, 101, 1111, 0.33),
-- (777, 101, 2222, 0.33),
-- (777, 101, 3333, 0.33),

--
-- BUT, if I add a 4th
-- record to the set, the UNIQUE constraint no longer applies because
-- we've added a 4th language---
--
-- say, like this:
--
-- (888, 101, 1111, 0.15),
-- (888, 101, 2222, 0.15),
-- (888, 101, 3333, 0.15),
-- (888, 101, 5555, 0.15)
--
--
-- The p-Value of a combination of languages must be **AT MOST** <= the
-- p-Value of the lowest p-Value for any of the languages - how to enforce?
--
--
-- For example, SQL (appears to me anyway) to be "tacked on" to virtually
-- every job ad - so, you might have (for Dublin - city code 101) -
-- SQL code say
--
-- Now, this is a valid set of records for the 3 languages Java, C & C++
-- (999, 101, 1111, 0.33),
-- (999, 101, 2222, 0.33),
-- (999, 101, 3333, 0.33);
--
-- and say, SQL was language code 7777, one could have:
--
-- (1000, 101, 1111, 0.33),
-- (1000, 101, 2222, 0.33),
-- (1000, 101, 3333, 0.33),
-- (1000, 101, 7777, 0.33);
--
-- this would **_ALSO_** be a valid set of records for Dublin - difficlt
-- to enforce uniqueness constraints for this and also the CHECK that the
-- p-Value is less than or equal to (at most) the p-Value for any of them.


SSMS - Openrowset maximum file size Apologies if this has been answered elsewhere but I couldn't find anything here or online.

Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if certain characters exist within the file and if they do I'd have to skip to the next section of the stored procedure. So far I've simply bulk inserted the entire file into a one column table then did:

IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%}%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%~%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%#%')>0 GOTO NEXTSECTION

It's reliable but very slow when dealing with large files (sometimes over 10GB). I'm thinking the below query would be quicker for large files

DECLARE @FILE NVARCHAR(MAX)
select @FILE = BULKCOLUMN from (
select * from openrowset(BULK N'filpath', single_clob) [a]
)a

IF(SELECT IIF(@FILE LIKE '%{%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%}%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%~%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%@%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%£%',1,0)) = 1 GOTO NEXTSECTION

But as I say Bulk insert is reliable and I'd hate to risk the file being truncated when using OPENROWSET if there is some sort of file size limit.

Any advice on the matter would be appreciated.

I know it's not your main question, but you mentioned slow performance. When checking if a certain character or substring exists within a larger string, you should try using CHARINDEX('x', strColumn) instead of LIKE '%x%'. This will boost your performance drastically. Using LIKE with % surrounding your search-term is very slow and costly.


Why disabling a clustered index makes the table inaccessible? When an index is disabled, the definition remains in the system catalog but is no longer used.  SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to satisfy queries. If a Clustered Index is disabled, the entire table becomes inaccessible.

Why isn't it possible to access the data directly from the table discarding the B-tree? (most likely by scanning the table row by row) Wouldn't that be more appropriate than making the data completely inaccessible?

It's a purely theoretical question - I would never actually do that. It's not a scenario, nor a to-do thing, I just want to know why thing goes that way, consider it an internals question.

why isn't it possible to access the data directly from the table discarding the B-tree?
(most likely by scanning the table row by row)
wouldn't that be more appropriate than inaccessible data at all?

To answer your question, Indexing basics comes more handy -- An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom. For more details refer here.

Also, as many people have described, Clustered Indexes == Original tables which are physically ordered with one or more keys or columns. So, when a clustered Index is disabled, its data rows cannot be accessed. You wont be able to Insert any data (for Non Clustered Index the Insert will succeed -- but that is not entirely related to this post -- as here the discussion is of Clustered Index) as well or neither Reorganize operation will work.

Below will explain you in detail :

we will use Adventureworks database to see the effect of disabling the CLUSTERED Index.

Now check the row count in the table:

Now disable the Clustered Index

Now select the row count from the table. This time it will error out with below message:

Even the reorganize operation does not work !!

Now rebuild the Clustered Index and it should work fine.

Select the table to see if we can access the data

So the bottom line is that, if we disable the Clustered Index, then Data in the table still exists, but will not be accessible for anything other than Drop or REBUILD operations. All related Non-clustered Indexes and views will be unavailable as well as Foreign Keys referencing the table will be disabled and there by leading the FAILURE for all the queries that are referencing the table.

Note: There is no option to ENABLE the Index. You have to REBUILD it.


Should I Link a Fact to Hierarchical Dimension at ALL Levels or only the most granular? Related question about the same project here.

I'm looking at two approaches to model a hierarchical relationship between my fact and dimension tables in a data warehouse for storing IT infrastructure data.

Consider as an example:

FACT_Folder

Contains facts about a file system folder

Dim_Drive

A folder links to a single DRIVE entry

Dim_Array

A drive links to a single array

Dim_Server

An array links to a single server

Dim_Farm

A Server links to a single Farm

For our purposes we don't need to worry about a folder moving to a different drive.

We want to be able to get metrics at all the levels of this hierarchy.

Assuming I will eventually have millions of Folder entities (with daily data), and hundreds or thousands of drives, arrays, etc; which design pattern would you recommend and why?

Normalized

A Folder links to a Drive, and the rest of the hierarchy is between dimensions

Denormalized I

A Folder links to a Drive, but the Drive dimension contains the whole hierarchy for each Drive entry for every row

Denormalized II

A Folder links directly to all levels of the hierarchy

??????

I would link at all/most levels.  This denormalized star means that yes, the data is redundant, but it typically makes the reporting and analysis a lot easier.  Note that this is very different from OLTP normalization, and you don't typically have to worry about redundant data getting out of sync because in a DW scenario data never changes.  New facts get added and dimensions get expired and new ones created.

I don't see a Dim_Folder.  I would assume that the actual path of the folder would be an attribute of the Dim_Folder.  Only the numeric quantity and any degenerate dimensions (http://en.wikipedia.org/wiki/Degenerate_dimension) would be in the fact table.  I wouldn't think of the folder path as a degenerate dimension because it keeps coming back in each snapshot (an a folder isn't a transaction).

So you could do something like this:

SELECT AVG(bytes_on_disk)
FROM FACT_Folder
INNER JOIN DIM_Folder
    ON FACT_Folder.FolderDimID = DIM_Folder.DimID
INNER JOIN DIM_Date
    ON FACT_Folder.SnapshotDateID = DIM_Date.DateID
WHERE DIM_Date.Date BETWEEN '20120101' AND '20121231'
    AND DIM_Folder.FolderPath = '/usr/bin/'

See how the DIM_Folder usage makes the set of dim ids small and then, we're assuming some kind of index on snapshot date and then folder dim id (or vice versa).

See how you also now don't need to join on folder at all if you just want the data at a higher level.  Since you usually know all this at ETL time, there is a different motivation than in OLTP systems where you want everything to move together when something is changed (leg bone connected to the thigh bone, etc.).  In DW scenario, you really don't want anything to move.

So, bam! - total Farm usage analysis:

SELECT DIM_Farm.Farm_Name, SUM(bytes_on_disk)
FROM FACT_Folder
INNER JOIN DIM_Farm
    ON FACT_Folder.FarmDimID = DIM_Farm.DimID
INNER JOIN DIM_Date
    ON FACT_Folder.SnapshotDateID = DIM_Date.DateID
WHERE DIM_Date.Date BETWEEN '20120101' AND '20121231'
GROUP BY DIM_Farm.Farm_Name

Remember stars are really simple for analysis.  You NEVER need to worry about inadvertent cross joins in a single non-snowflaked star.  When linking different stars, you DO have to watch out.  So queries in MOST cases are MUCH simpler in star-schemas.  No network traversal and worrying about many-many relationships like in a normalized model.


What extra work is required to restore a SQL Server instance and its databases included in a Windows Server snapshot backup? We are migrating our SQL Server databases (various versions) from source Windows servers to new Windows servers. These are to be done by taking snapshot backups of of the servers. Does this server backup method take in a consistent copy of a SQL Server instance and its databases?
Is there any work that will be required to complete restoration of the SQL Server databases or does everything get restored as is on the source server?
I do not have experience in this area, what I know is to backup and restore the database, I would appreciate some guidance on this method of migration please.

A backup of the entire server will be inclusive of the SQL Server instance and the database files as a snapshot in time at that moment. This is assuming that the database files live on the server itself that you're taking a backup of, as it is possible to setup the database's such that their files live on remote shares.
You can verify the location of your databases' files in SQL Server Management Studio, by right clicking on a specific database, clicking Properties, and then navigating to the Files page, then looking at the Path and File Name properties.
For example:

In the above, my Test database's files are stored in the default SQL instance folder. Please ensure the drives that your database files live on are part of the server / snapshot being taken of it, as even mapped drives can have drive letters but point to remote shares.
There shouldn't be any additional work on the destination server, but you may want to make sure the appropriate SQL services (in the Windows Services) are started and running just the same as on the source server.
For example:

The above is my test instance which probably has a lot more components of SQL Server installed than yours will, so don't take this as a definitive list, rather it's an example. Compare against your source server to the destination server.
The only other thing I can think of is to ensure all the same Windows permissions are setup to the correct accounts on your destination server as its respective source server. For example, if you're using the SQL Agent, or a feature like Replication, those might leverage Windows accounts that currently have special permissions setup on your source server which need to be replicated on your destination server. (This is a little outside the scope of DBA.StackExchange though, and might be more of a ServerFault question, if you need help with that.)