Leaving database modeling issues aside. I think you can try SELECT * FROM STUDENTS WHERE ISNUMERIC(STUDENTID) = 0 But ISNUMERIC returns 1 for any value that seems numeric including things like -1.0e5 If you want to exclude digit-only studentids, try something like SELECT * FROM STUDENTS WHERE STUDENTID LIKE ‘%[^0-9]%’
Yes, it’s remarkably easy in fact: WHERE YEAR(A.[reimbursementDate]) = YEAR(GETDATE())
If your dates are no more than 2047 days apart: declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = ‘P’ ) n where dateadd(day, number, @dt) < @dtEnd I updated my answer after several requests to do … Read more
ALTER TABLE table1 ADD newcolumn int NULL GO should not take that long… What takes a long time is to insert columns in the middle of other columns… b/c then the engine needs to create a new table and copy the data to the new table.
SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE objectproperty(object_id,’IsProcedure’) = 1 AND definition like ‘%Foo%’
GUIDs are problematic as clustered keys because of the high randomness. This issue was addressed by Paul Randal in the last Technet Magazine Q&A column: I’d like to use a GUID as the clustered index key, but the others are arguing that it can lead to performance issues with indexes. Is this true and, if … Read more
If you are on SQL Server 2016+ You can use the new STRING_SPLIT function, which I’ve blogged about here, and Brent Ozar has blogged about here. SELECT s.[message-id], f.value FROM dbo.SourceData AS s CROSS APPLY STRING_SPLIT(s.[recipient-address], ‘;’) as f; If you are still on a version prior to SQL Server 2016 Create a split function. … Read more
You can use SELECT INTO. From MSDN: The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data … Read more
The reason you aren’t finding easy examples for how to do this in T-SQL is that it is generally considered bad practice to implement formatting logic in SQL code. RDBMS’s simply are not designed for presentation. While it is possible to do some limited formatting, it is almost always better to let the application or … Read more
Calling DELETE FROM TableName will do the entire delete in one large transaction. This is expensive. Here is another option which will delete rows in batches : deleteMore: DELETE TOP(10000) Sales WHERE toDelete=”1″ IF @@ROWCOUNT != 0 goto deleteMore